This is part 1 of the 4 part series on What if analysis. This blog will cover the Goal Seek feature.
What is the Goal Seek feature?
Goal seek enables you to see how a change in one value in a model can change the final result.
Instead of guessing what impact a value will have on the bottom line…use the Goal-Seek to test it.
For example, what would be your income growth rate to achieve $ 15,000 by December? How many units do you need to sell to break even?
Goal seek requires only 3 things…The current output value, the Target/desired output value, and the cell to change in order to achieve the target.
How to access Goal Seek
Go to Data Tab then under the forecast group…click What-if analysis
Determining Stock Weighted Average Cost
If you want the weighted average cost of your stock to be $ 520, how much would you pay for the 4th purchase?
Determining a Future Income Goal Seek
In the example below, geometric growth from the 2 months is 35%. With that growth, the income in December will be $ 12,550.
What growth rate will give you the desired income of $ 15,000 by December?
Read More on How to autofill Geometric growth data
Determining Total Interest on Loan payment using Goal Seek
It is a good financial decision to pay your loan as fast as possible to avoid high interest on loan cost
For example, what if your desire is to pay only a $ 40, 000 interest rate on the below loan?
Bonus Sharing Using Goal Seek
Assume you are an HR manager tasked to share a $ 10, 000 bonus among your staff (See below image).
What percentage would you increase each staff salary?
Determining a Break-even Quantity using Goal Seek
How long would it take you to determine the correct amount to sell in order to break even (see the below image)?
Points to Note on Goal Seek
Goal seek returns approximate results. If you need more precise results Go to File â–º Options â–º Formulas and change your precision level
Sometimes goal seek returns below not found error when it reaches the maximum iterations and or precision level. Change these first and try again
The other reason why you may not get the solution is Set cell refers to the cell containing a formula that depends directly or indirectly on the changing cell.
Goal Seek requires a single input cell and a single output (formula) cell. For multiple input and output cells, check out part 2 of this article when we shall be looking at the Solver feature.
More Resources;
Recent Comments