Goal Seek in Excel
Goal Seek in Excel
The Goal Seek Excel function in Excel facilitates an automatic solving approach by adjusting a few pointers to reach a required output. Goal seeking is used when you have a value in mind for your dependent variable (which is formula-based), and you want to change the value of one input variable to achieve that value. This function automates the solving by using the trial and error method and solves the problem by spinning multiple guesses until the desired output arrives.
This function is beneficial for performing sensitivity analysis in financial modeling. It computes the result by using What-If-Analysis on a particular set of values. This feature can only be used if you know the result you want from a formula find the input value that produces the result. One of the main disadvantages of ‘Goal Seek’ is that it can be used to change only one input variable, something which can be overcome by the ‘Solver’ method.
Steps to perform Goal Seek
In the below sheet, we have four exams, out of which three exams have already been conducted. So, if we want to have the final grade as 7 CGPA we need to score good marks in exam 4.
Let’s compute with the help of Goal Seek the minimum marks we need to score in Exam 4:
- Under the Data ribbon tab, select Forecast -> What -if analysis -> Goal seek
- The ‘Goal Seek’ dialog box will pop up. Choose the appropriate values for the fields and click on ok. It comprises three fields:
- Set Cell: It represents the cell whose value you have already set. Here we have selected the G9 cell.
- To Value: It represents the value you want to set in the ‘Set Cell’ field. In the below, we have entered the value 7.
- By Changing Cells: It represents the cells whose value the ‘Goal Seek’ method will compute.
- It will calculate all the possible values. Once done, the ‘Goal Seek Status’ will appear, showing up the output status. Click on ok.
- The output for Exam 4 has been automated by Excel.
Goal Seek Complex Problem
Let’s imagine a situation where ABC bought a car had in his mind that he will not be paying more than $1,400 installment per month – to achieve this, either he will buy a car which is less costly, or he will pay more down-payment, or he will increase the term of the loan, or he will look for a better deal on the interest rates.
Let’s say ABC is comfortable with the price of the car, loan term and interest rate being offered on loan but wants to change the down payment so that he can have a monthly EMI payment of $1,200. Let’s see the step by step method to compute the goal seek result:
- Go to ‘Goal Seek’ by clicking on ‘What-If Analysis’
- A ‘Goal Seek’ prompt pops-up – first section from Row 1 to Row 10 has been kept for easy reference to see the difference after using ‘Goal Seek’
- Since we want to change ‘Monthly Payments’ to $1,200 from current $1,400, we select B22 in ‘Set Cell’ and mention 1200 in ‘To Value’
- Since we want to change the down-payment only, we select B17 and click on ‘OK’
- The results are shown in the picture given below. You can compare the top 2 tables with the below 2 tables to see the change due to ‘Goal Seek’ -
Because ABC wanted an EMI of $1,200, keeping purchase price, loan term, and interest rate constant, Goal Seeking has changed Down Payment to 31% from the previous 20%.
Goal Seek is a handy tool that can quickly fix the answers of different problems in different situations.