Goal Seek in Excel (Table of Contents)
Goal Seek in Excel
A Goal Seek is a tool which 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 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.
Example #1
Let us take the 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 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:
Example #2
Let us take the example of Aryan Ltd. Trading with generators. 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 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 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 period 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 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 him to 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 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 the rate of interest in cell B4 changes from 6.50% to 20.11% keeping the number of years and the initial investment as unchanged.
- Then press OK to accept the changes.
The Result is:
Pros 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.
Cons of Goal Seek in Excel
- 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 cell should be the cell which 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 –
