Goal Seek: Finding the Perfect Number

Have you ever wondered, "I wonder what X needs to be in order for Y to be 50%?" Good news! Excel thought of that as well! If you know what a certain results needs to be (i.e. profit margin needs to be 35%) but don't know what input value (i.e. Sales) will get you there, then use Goal Seek to find it for you. It sure beats trying to guessing 100 iterations in order get there yourself. Here is how it works.

You will notice on my income statement below that my profit margin (Net Income/Sales) is 26%.

I now want to know what sales need to be in order for my profit margin to be 35% (holding everything else constant). On the Data tab, you will see What-If Analysis. Click on that and select Goal seek.

A box will now appear. In our example we are going to Set Cell C12 (our profit margin), To Value 35%, By Changing Cell C3 (our sales).

Note that the cell you choose for By changing cell needs to be a hard coded value (no formulas). After I press OK, I see that I need to have sales of $623,049 to get to a profit margin of 35%. Pretty neat!

After you show your boss Goal Seek, you are sure to get a promotion, pay raise, and have all of your hopes and dreams fulfilled. Stay tuned for next week's Excel tip!