Have you ever heard about the feature called the What-If Goal Seek Analysis in Excel? Probably no. The What-If Goal Seek analysis is one of the three What-If Analyses that you can do in Excel. Before starting with the Goal Seek analysis, let me first define the What-If Analysis in a few words. It is nothing but a tool that projects (helps in making projections). Let me take one simple example to make you understand what you would achieve after you use this unrecognized Excel tool.
Let’s say, you are are doing the contribution margin forecast for a company and for this purpose you have prepared one Excel template where when you enter the sales quantity in an input cell, the excel file automatically calculates the contribution margin (by deducting the total variable cost from the total sales using this input quantity sold). So the formula is in the contribution margin cell.
Now, using the same excel template, suppose you want to know what quantity should be sold to get the contribution of Rs. 10,00,000. This means you know the contribution amount you desire to earn then this tool can help you to work reverse and find out the quantity to be sold.
You might be thinking of entering a formula in the quantity sold cell. Isn’t it. But this would not work because excel does not ideally allow circular references. We have a special blog on Circular Reference. Click on the below link to learn more about Circular Reference in Excel:
Also Read: What-If Analysis – Scenario Manager in Excel
All About Circular Reference in Excel
But, the What-if Analysis tool will solve this problem. There are many more things that you can do with this Excel tool.
What is a What-If Analysis Tool?
What-If Analysis tool helps us to find the impact and outcome on the formula if any change is made in one of the cells in Excel. There are three kinds of What-If Analysis that you can do using this feature in Excel:
- Goal Seek
- Data Table
- Scenario Manager
The Data Tables and Scenario tools take the input value and predict or provide the result of the formula. Whereas, the Goal Seek analysis works exactly the reverse of the other two. It takes the final result and predicts the input value. Now you can understand that the example about which I was talking about in the introduction paragraph was nothing but an example on the Goal Seek What-If Analysis.
Where To Find This Tool?
The Goal Seek tool in Excel can be found using the following path.
Under the ribbon options, click on the tab ’Data’. There you would find the option called ‘What-If Analysis’ under the group ‘Forecast’.
Also Read: What-If Analysis – Data Table in Excel
Just click on the ‘What-If Analysis’ button and the three What-If Analysis tools (Data Tables, Scenario Manager, Goal Seek) can be seen over there in the drop-down list.
In this blog, we would only go through the ‘Goal Seek’ Analysis tool in Excel with practical examples. There are exclusive blogs on the other two functions – Scenario Manager and the Data Table What-If Analysis.
Let us now take one small example to understand it.
Sample Example
As you can see in the below screenshot, the cell B3 contains the formula =(B3*B4)-(B3*B5) which calculates the contribution margin based on the units sold, selling price, and variable cost per unit.
Let us now learn how to use the What-If Goal Seek analysis to find the quantity sold if you want the total contribution of let’s say $16,000.
Using the Goal Seek What-If Analysis Tool
The Goal Seek Analysis does the reverse calculation where it provides us with the one variable input value based on the desired output value (which is a formula).
In the present example, the input values are units sold, selling price, and variable cost per unit. And the output value is the contribution amount.
To find units sold to earn the desired contribution of $16,000, follow the below steps:
Click on the option ‘Goal Seek’ (Data tab > Forecast > What-If Analysis)
The small ‘Goal Seek’ dialog box would appear on your screen.
In this dialog box, there are three options – ‘Set cell’, ‘To Value’, and ‘By Changing Cell’.
In the ‘Set Cell’ input box, we need to enter the cell for which you want to the desired value As in our case cell B6 denotes the desired contribution value, hence, enter $B$6 or select the cell B6 in the ‘Set Cell’ input box.
The ‘To Value’ indicates the desired value for the set cell. As we want the desired value for total contribution (set cell) as $16,000, hence enter 16000 in the input box ‘To Value’.
In the ‘By changing cell’ input box, we need to provide the cell reference of the input value cell which in our case is the quantity sold cell. Therefore, enter $B$3 in this input box.
Finally, just click on the ‘OK’ button.
Just check the excel data. You would find that excel updates the contribution margin and quantity accordingly. Therefore, you can conclude that to earn a contribution of $16,000, you need to at least sell 2,667 units.
Press OK to confirm the change (and the original data changes) or cancel to return.
Practical Example- Finding Loan Amount
The Goal Seek functionality of the What-If Analysis can also be used to find like at what rate should you borrow the funds to pay a certain amount of installment amount.
Refer to the below example screenshot:
The above screenshot shows that if you borrow an amount of Rs. 25,00,000 at 10% per annum for 15 years, then each year’s installment amount (including interest) will be $3,28,684. We have used the =PMT() formula to calculate the value in cell B6.
Now, suppose we want to pay only $2,00,000 each year. We can use the goal seek tool to find the loan amount, or borrowing rate, or the number of installments.
Let us, for example, find the loan amount if we want to pay an installment of $2,00,000 (for 15 years @ 10% per annum rate of interest).
Follow the below steps:
Navigate to Data > Forecast > What-If Analysis > Goal Seek.
In the ‘Goal Seek’ dialog box, enter the following :
Click ‘OK’ and you would notice that the respective values change accordingly.$15,21,216.
Limitations of Goal Seek Functionality
There are two basic limitations to this functionality. The first one is that the Goal Seek can only provide one input value (based on the required result). Taking the above example, you can either find the loan amount or the borrowing rate or the number of installments. ou cannot find all the inputs at the same time.
The second limitation of this functionality is that you can not give the reference to the cell for ‘To Value’ input value in the dialog box. You need to enter this manually.
This brings us to the end of this blog. Share your views and comments in the comment section below.