What is GOAL SEEK in Excel
We are going to cover how to use Goal Seek in Excel located in the What-If Analysis section. This is a very powerful tool that is misunderstood by most people. By using this feature, you are essentially performing a What-If analysis.
Your probably wondering, I have heard of a what-if analysis, but am not quite sure how it is applied to Excel. Put simply, a what-if analysis is the process of changing a value located in a specified cell to manipulate a desired outcome. A good definition can be found over a Microsoft.
The easiest way to understand how to use Goal Seek is to review the examples below. We are going to cover several real world examples; sales, election, school grade, and financial examples. The best way to follow along is to download the demonstration file.
Sales Example using Goal Seek on Excel
Let’s get started with the following sales example. Quickly review the following data set. We have sales figures for the first three quarters of the year as well as an annual sales target. What we want to determine are the sales dollars needed in the 4Q to reach our annual sales target.
Let’s begin by launching Goal Seek. First, go to the Data Tab. Under What-If Analysis, select Goal Seek.
Set the Goal Seek window to the following parameters.
- Set Cell: $C$11 – This is the cell you want to change.
- To Value: 4500000 (Unfortunately Excel does not allow you to link a cell)
- By Changing Cell: $C$9
Let’s look at the returned solution.
In this next example, we are going to visit a Presidential Election where a 60% is required to become president.
Based on the election results, the 60% majority was not met. We are going to determine how many more Yes votes were required to get to 60%.
- Set Cell: D8 – This is the cell you want to change.
- To Value: 60% (Election majority)
- By Changing Cell: $C$8
Let’s look at the result. Based on our outcome, we the Presidential candidate would have needed a total of 94,419,132 votes to get a 60% majority.
In this example, we are using Goal Seek to determine what grade is required on the the final exam in order to achieve an “A” in the class.
To begin, let’s review the following data set. In order for the calculation to work, Goal Seek requires a formula in cell C13. We will be using the AVERAGE function. Select the cell C13 and enter the following formula.
Now, we need to enter the Goal Seek analysis information.
- Set Cell: $C$13
- To Value: 93
- By Changing Cell: $C$11
The more calculations, the longer it will take Excel to cycle through potential solutions. In our example, we will need to get a score of 98.6 on the last Exam in order to get an “A.” Probably need to settle for a “B” 🙂
Let’s buy a motorcycle! Here at Excelbuddy, we love real world examples, not just explanations. Therefore, we are going to walk through the process of determining how much motorcycle we can afford based on a target monthly payment of $300. Modify these rates to fit your application.
To begin, enter the following formula in cell C10. The PMT Function is used to calculate our outgoing payment and is used to determine our monthly target.
First, let’s enter the following information in the GOAL Seek setup box.
- Set Cell: $C$10
- To Value: -300 : Set to negative since it’s an outgoing payment.
- By Changing Cell: $C$6
Excel returns the result of $9,575 based on our target monthly payment of $300.00.