What-If Analysis - Goal Seek

What if you are trying to reach a specified result for a formula, but you do not know what the input value for one of the variables should be? Use Goal Seek to find the input value of the variable.

Example: You are trying to project net income for your business. You have projected sales revenue and expenses, but you would like to see if you can cut costs somewhere to increase net income (see income statement below).

What would administration expenses have to be cut down to in order to have net income of $100,000?

Use Goal Seek:

• On the Data tab, select What-If Analysis and then Goal Seek...
• Set cell: $C$7
  (we want to make net income our fixed result)
• To value: 100000
  (the fixed result value)
• By changing cell: $B$4
  (the unknown variable)
• OK

Excel now plugs different numbers into cell B3 (Admin. Expenses) until C6 (net income) equals $100,000.

In order for this to work, the set cell and changing cell have to be linked either directly or indirectly through formulas or functions. In this example, Total Expenses is the sum of the three expenses/costs, and Net Income is the difference between Sales Revenue and Total Expenses. Also, Goal Seek only works when you are trying to find the input value of one variable.

Sign up for our free Excel class on data analysis to learn more about Goal Seek and other awesome tools! Click here to register.