Goal Seek

What is the Goal Seek Excel Function?

 Goal Seek is a tool built into Microsoft Excel. Also called What-If-Analysis, it allows the user to find answers to various what-if scenarios using data in Excel. It is a powerful tool. It calculates a specific input value that generates an expected or pre-determined output value for a particular what-if scenario.

The Goal Seek function helps perform sensitivity analyses for different financial metrics in financial modeling. 

Requirement For The Goal Seek Function

The function is pretty simple to use. It executes all the calculations on its own without your assistance. However, it needs three basic parameters from you to get started:

• A Formula Cell(Set cell) - The cell representing our target is the set cell. 

• A Target/ desired value(To value) - The value that we want to achieve will be the target value.

• The cell changes to achieve the target value - The value depends on the target value.

Let’s go over a brief example.

Assume that you take a loan of $500,000 for 30 years at 5.5% interest. You pay $2839 each month for this loan.

However, after 12 months and getting a pay raise at work, you could probably make higher monthly payments to pay this loan more quickly. 
Here is the scene after a year:

Before using the function, ensure that you have the formula =PMT(C7/12, C6, C4) used in cell C5. Since the ‘set cell’ parameter accepts only formulas, you will get an error if you reference cells with numerical values in the goal seek function. 

• ‘Set cell’ values should be the target cell, i.e., cell C5

• ‘To value’ is our monthly payment which we can afford to pay, i.e., $3,500

• ‘By changing cell’ is equal to cell C6 in our spreadsheet.  

When you click the OK button, Excel starts its calculations. After a couple of minutes, it gives you the result:

If you pay $3500 monthly to the bank, you can probably pay the loan off in 17 years and two months. However, if the ‘Set cell’ does not contain a formula, Excel will give you an error after you run the Goal Seek function.

How to use Goal Seek in Excel

By now, you probably understand the basics of the function. So we will demonstrate the steps you must follow to perform a what-if-analysis.

Assume that we(WallStreetOasis) introduce a new course called 'Credit Analyst' in the market.

For this, we invest $5,000(not the actual cost but a nominal figure for this example), representing the expenses towards the investment experts team and other miscellaneous costs. 

The price is set as $27 by the accounting team, considering that the course covers all the investment costs in one year by serving a minimum of 25 clients per month.

However, the finance and marketing team predicts that the course might do better than estimated, and SEO reports suggest that the course may have more than 35 clients per month.

The chief monkey doesn't want to take advantage of the business opportunity and asks his team to focus on recovering the investment cost to invest in another course for the ape army rather than increase the course price. So how does the team identify when the money will be recouped?

The steps you need to follow are:

• Head to the Data Tab, where you will find the What-If-Analysis under the forecast section. Click on it to open a drop-down and select the function. Alternatively, you can use the keyboard shortcut Alt + A + W + G to open the Goal Seek window.

• The value of 'Set cell' will be cell C4(since it only takes formulas), the 'To value' will be our estimated clients, which is 35, while the final reference value is which we want to change the value of.

• When you click OK, Excel starts iterating over the values that satisfy our conditions. Finally, we get the result:

As you can see, if the estimated clients per month for the Credit Analyst Course are around 35, the investment will be recovered in just 5-6 months instead of 12 months. So keep hitting those forums with demand for different courses so we can reinvest the money for you! 

Examples of using Goal Seek in Excel

We will see a few more examples of scenarios where you can use the function.

A) Example #1

Assume that the two candidates contest the student council elections in ABC school. The candidate must receive 75% of the votes to win the elections. The total number of students in the school is 10,000, and 8458 votes were cast at the end of the day, where candidate A received 4341 votes, and candidate B received 4117 votes.

The data looks as below:

Since it was a stalemate between both the candidates, how would you determine which candidate needed x number of votes to win the election? Here, we use the function to determine the votes that candidates A or B should have received to win the elections by the 75% benchmark.

Firstly, write the total votes cast in cell C3 and the votes received by candidate A in cell C4.

The value of cell C5 will be based on the formula =C3-C4 to give the result as 4117.

Next, we calculate the percentage of votes received by both candidates A & B using the formula =C4/C3 & =C5/C3, respectively.

The final step is to run the Goal Seek function with the below parameters:

The values you have provided to Excel will look for the number of votes candidate A should have received to win the election by 75% of the votes. You will get the result:

You will get the same result if you try to find the votes needed by candidate B to win the elections. The only significant difference you will make in the spreadsheet is hardcoding the value 4117 in cell C5 and using the formula = C3-C5 in cell C4 before running the Goal Seek function.

B) Example #2

Suppose the following data shows your weekly grocery expenses. It would help to determine how much you can spend on Day 7(Sunday), considering that you do not cross the average weekly amount of $25 spent on groceries.

As you can see, the current average expenses are $27.71, which is higher than our cutoff value of $25.00. Hence, we will use the function to determine how much you can spend on Day 7. Our parameters for the Goal Seek function are:

The ‘Current Average Expenses’ value is our ‘Set Cell’ reference to be used in the function, i.e., cell N8, while our ‘to value’ is the expected weekly average expense of $25.00.

The final value ‘by changing cell’ is represented by cell N10, our cost for Day 7 (expendable amount). When you click on Ok, the function iterates through the values to give you the result:

So, if you spend just $8.73 on day 7, you can stay within your average weekly expense limit. You might have had a question - How did we make this model? Firstly, the total of each day is calculated using the SUM function such that C10 = SUM(C3:C8).

Next and most important is using the AVERAGE formula in cell N8, i.e.,

=AVERAGE(C10,G10,K10,C21,G21,K21,N10).

Don't forget to include the cost for Day 7 represented by cell N10 in your AVERAGE formula; otherwise, you won't get the correct result using the function.

Important points to remember

Some of the key points to remember are:

• The function 'Set cell' requires you to input a formula-based cell; otherwise, the function won't accept the cell reference.

• The value of 'By changing cell' must be related directly or indirectly to the 'Set cell' value to reflect the result.

• You can't use circular references in the formulas while using the function. For example, assume the votes received by candidate A as 80 and the votes received by candidate B as 'Total' - 80. The total number of votes that were cast was 100.

• Now for experimentation's sake, you use the formula to calculate candidate A's votes, i.e., 'Total' - 20(Votes received by candidate B). This will result in a circular reference(meaning both values create an infinite loop of calculation) and show the values in the spreadsheet with blue arrows as:

• Sometimes Goal-Seek can't find a perfect solution for your What-If Scenario. If this happens, it will converge on a solution that closely fits your criteria and display a warning message: "Goal Seek may not have found a solution."

To conclude, this function will help you find an expected output value for a what-if scenario by using a specific input value. The most important thing to remember before using the goal seek function is to use a formula for the 'set cell' argument and then reference the data in the function.

Based on the assumptions and formulas your input, you can use the function for various applications, including finding the loan period when you can afford $XXXX of monthly payments for the loan.

Researched & Authored by Akash

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• Best Excel Courses
• Excel for Finance
• Excel VBA
• Power Query
• Waterfall Charts