Excel 2019 (Part 28): What-if Analysis

Excel includes powerful tools for performing complex mathematical calculations, including What-if Analysis. This feature can help you experiment and answer questions with your data, even if the data is incomplete. In this article, you will learn how to use an analysis tool called Goal Seek.

Goal Seek

Whenever you create a formula or function in Excel, you have to combine the different parts to calculate the result. Goal Seek works the opposite way: It allows you to start with the desired result and the tool will calculate the input value giving you that result. The article will use a few examples to show how to use Goal Seek.

Example 1 on how to use Goal Seek

Let’s say you’re enrolled in a class. You currently have 65 points and you need to score at least 70 to pass this course. Luckily, you have one last exercise that can help raise your grade point average. You can use Goal Seek to find out what score you need on the final exercise to pass the subject.

In the image below you can see that the scores for the first 4 exercises are 58, 70, 72 and 60. Although the score for the 5th exercise is unknown, you can write a formula. – or function – to calculate the final score to be achieved. In this case, each exercise is weighted the same, so all we have to do is average the top 5 scores by entering =AVERAGE(B2:B6). When using Goal Seek, cell B6 will show the minimum score required for that final exercise.

1. Select the cell whose value you want to change. Whenever you use Goal Seek, you’ll need to select a cell that already contains a formula or function. The example will select cell B7 because it contains the formula =AVERAGE(B2:B6).

2. From tab Dataclick command What-If Analysisthen select Goal Seek from the drop-down menu.

3. A dialog box will appear with three fields. First school, Set cells:, will contain the desired result. In the example, cell B7 was selected.

Second school, To value:, is the desired result. The example will enter 70 because it takes at least that number to pass the class.

Third school, By changing cell:, is the cell where Goal Seek will place its answer. The example will select the cell B6 because we want to determine the score to be achieved in the last exercise.

4. When you’re done, click OK.

5. The dialog box will tell you if Goal Seek can find the answer or not. Press OK.

6. The result will appear in the specified cell. In the example, Goal Seek calculated that it would take at least 90 points on the final assignment to pass the course.

Example 2 on how to use Goal Seek

Let’s say you’re planning an event and want to invite as many people as possible without going over your $500 budget. You can use Goal Seek to figure out how many people to invite. In the example below, cell B5 contains the formula =B2+B3*B4 to calculate the total cost of the reservation plus the cost per person.

1. Select the cell whose value you want to change. The example will select cell B5.

2. From tab Dataclick command What-If Analysisthen select Goal Seek from the drop-down menu.

3. A dialog box will appear with three fields. First school, Set cells:, will contain the desired result. In the example, cell B5 was selected.

Second school, To value:, is the desired result. The example will enter 500 because only want to spend $500.

Third school, By changing cell:, is the cell where Goal Seek will place its answer. For example, I will select cell B4 because I want to know how many guests can be invited without spending more than $500.

4. When you’re done, click OK.

5. The dialog box will tell you if Goal Seek can find the answer or not. Press OK.

6. The result will appear in the specified cell. In the example, Goal Seek calculated the answer to be about 18.62. In this case, the final answer needs to be an integer, so it should be rounded up or down. Since rounding up would be over budget, the example would round down to 18 guest.

As you can see in the example above, some situations will require the answer to be an integer. If Goal Seek gives you a decimal, you’ll need to round up or down, depending on the situation.

Other types of what-if analysis

For more advanced projects, you may want to consider other types of hypothetical analysis: Scenarios and data tables. Instead of starting from the desired result and working backwards, as with Goal Seek, these options allow you to test multiple values ​​and see how the results change.

Scenarios allow you to substitute values ​​for multiple cells (up to 32) at once. You can create as many scenarios as you want and then compare them without manually changing the values. The example below is using scenarios to compare different locations for an upcoming event.

Data tables allow you to take one or two variables in a formula and replace them with as many different values ​​as you want, then view the results in a table. This option is especially powerful because it shows multiple results at once, unlike scenarios or Goal Seek. In the example below, 24 possible outcomes can be viewed for a car loan.