Goal Seek in Excel (Table of Contents)
Introduction to Goal Seek in Excel
A Goal Seek is a tool that is used to find an unknown value from a set of known values. It comes under the What-If Analysis feature of Microsoft Excel, which is useful to find out the value that will give the desired result as a requirement. This function instantly calculates the output when the value is changed in the cell. You have to mention the result you want the formula to generate and then determine the set of input values that will generate the result.
How to Implement Goal Seek?
The Goal Seek is implemented by using the Goal Seek dialog box, as shown in the following figure:
As shown in the figure, the Goal Seek dialog box accepts three values:
- Set Cell: It specifies the cell whose value will be changed to the desired value after the result of the Goal Seek operation.
- To Value: It specifies the value that you want as a result of the Goal Seek tool.
- By Changing Cell: It specifies the cell whose value needs to be changed to achieve the desired result.
How to Use Goal Seek in Excel?
Goal Seek in Excel is very simple and easy to create. Let understand the working of Goal Seek in Excel by Some Examples.
Goal Seek in Excel – Example #1
Let us take a simple example to understand the concept.
Let us take the example of multiplication between two numbers A and B
In the above figure, there are two numbers, A and B, with the value of 9 and 6, respectively.
A product is done using the function =PRODUCT(B1,B2), resulting to 54.
If A is 9, what will be the second number for B to get the result, 72.
Following are the steps:
- Click on Data Tab
- Under Data tools group
- Click on the What-if Analysis drop-down menu
- Click on Goal Seek
- In the Goal Seek Dialog Box, select B3 in the ‘ Set Cell.’
- Enter 72 in the ‘ To Value ‘
- Select B2 in ‘ By Changing Cell ‘
- Then press OK
The Result is:
Goal Seek in Excel – Example #2
Let us take the example of Aryan Ltd. Trading with generators. The price of each generator is Rs 18000, and the quantity sold is 100 nos.
We can see that the company is suffering a loss of 13.8 lacs. It is identified that the maximum price for which a generator can be sold is Rs. 18000. Now, It is required to identify the no. of generators that can be sold, which will return the break-even value (No Profit No Loss). So the Profit value (Revenue – Fixed Cost + Variable cost) needs to be zero to attain break-even value.
Following are the steps:
- Click on Data Tab
- Under Data tools group
- Click on the What-if Analysis drop-down menu
- Click on Goal Seek
- Select C8 in the ‘ Set Cell ‘
- Enter 0 in the ‘ To Value ‘
- Select C3 in ‘ By Changing Cell ‘
- Then press OK
The Result is:
Example #3
In the above figure, a person has taken a loan amount of Rs 18000. He has been told that the loan amount will be sanctioned at an interest rate of 10% per annum for a period of 45 months, which makes the repayment of Rs 481.32 per month. When he calculated the total amount, which he will be repaid in 45 months, he got Rs 21659.47 as a result.
Now he wants to increase the number of repayment periods as he would not be able to pay Rs 481.32 per month. However, he does not want to increase the total amount of repayment by more than Rs 25000.
So, to achieve this, the person needs to go for Goal Seek.
Following are the steps:
- Click on Data Tab
- Under Data tools group
- Click on What if Analysis, drop-down menu
- Click on Goal Seek
- In the Goal Seek Dialog Box, select B6 in the ‘ Set Cell.’
- Enter 25000 in the ‘ To Value ‘
- Select B4 in By ‘ Changing Cell ‘
- Then press OK
- Goal Seek lowers the monthly payment, the number of payments in cell B4 changes from 45 to 82.90. As a result, the Equated Monthly Instalment (EMI) decreased to Rs. 301.56.
- Then press OK to accept the changes.
The Result is:
That’s how easy a Goal seek is. Now let us take another example
Example #4
In the above figure, a person wants to invest a lump sum amount in his bank for a certain period of time.
The bank employee suggested he open a Fixed Deposit Account. The rate of interest is 6.5 %, and the number of years is for 5 years. So to find out the return, the person uses the FV function (procedure of calculation is shown in the above figure).
The person now wants to increase the return amount to $ 1500000, but he does not want to increase the time period of investment as well as an initial investment amount. He wants to find the interest rate which will help him to attain the desired return.
Following are the steps:
- Click on Data Tab
- Under Data tools group
- Click on the What-if Analysis drop-down menu
- Click on Goal Seek
- In the Goal Seek Dialog Box, select B6 in the ‘ Set Cell.’
- Enter 1500000 in the ‘ To Value ‘
- Select B4 in ‘ By Changing Cell ‘ to change the rate of interest
- Then press OK
- Goal Seek increases interest rate in cell B4 changes from 6.50% to 20.11%, keeping the number of years and the initial investment unchanged.
- Then press OK to accept the changes.
The Result is:
Pros & Cons of Goal Seek in Excel
- The Goal Seek will allow the user to find out the accurate data by back calculating the resulting cell by giving a specific value to it.
- Goal Seek Feature can be used with the Scenario Manager feature.
- Data must contain a formula to work.
Things to Remember
- Goal Seek is based on the Dependent and Independent cells.
- The set cell should always be the resulting cell.
- To value should always have a numeric value.
- By changing the cell should be the cell that needs to be changed.
Recommended Articles
This has been a guide to a Goal Seek in Excel. Here we discuss its uses and how to use Goal Seek in Excel with excel examples and downloadable excel templates. You may also look at these useful functions in Excel –
