Using What-If Analysis Excel 2010

 

Introduction

Let's say you're trying to solve a complicated problem with Excel, like calculating an unknown value. You could try solving it on your own, plugging in different numbers until you find the right answer. However, this method could take a lot of time and effort.

Instead of calculating the answer by yourself, you could use a powerful Excel tool called what-if analysis. This feature makes it easier to experiment with your data. In this lesson, we'll show you how to use what-if analysis to answer different types of questions.

What-if analysis

Video: Using What-If Analysis in Excel 2010

Excel includes many powerful tools to perform complex mathematical calculations, including what-if analysis. This feature can help you experiment and answer questions with your data, even when the data is incomplete. In this lesson, you'll learn how to use a what-if analysis tool called Goal Seek.

Optional: You can download this example for extra practice.

Using Goal Seek

When you create a formula or function in Excel, you put various parts together to calculate a result. Goal Seek works in the opposite way: It lets you start with the desired result, and it calculates the input value that will give you that result. We'll use a few examples to show how to use Goal Seek.

To use Goal Seek (Example 1):

Let's say you're enrolled in a class. You currently have a grade of 65, and you need at least a 70 to pass the class. Luckily, you have one final assignment that might be able to raise your average. You can use Goal Seek to find out what grade you need on the final assignment to pass the class.

In the image below, you can see that the grades on the first four assignments are 58, 70, 72, and 60. Even though we don't know what the fifth grade will be, we can go ahead and write a formula or function that calculates the final grade. In this case, each assignment is weighted equally, so all we have to do is average all five grades by typing =AVERAGE(B2:B6). Once we use Goal Seek, cell B6 will show us the minimum grade we'll need to make on the final assignment.

1. Select the cell containing the value you want to change. When you use Goal Seek, you'll need to select a cell that already contains a formula or function. In our example, we'll select cell B7 because it contains the formula =AVERAGE(B2:B6).
   
2. From the Data tab, click the What-If Analysis command, then select Goal Seek from the drop-down menu.
   
3. A dialog box will appear with three fields:
   • Set cell: This is the cell that will contain the desired result. In our example, cell B7 is already selected.
   • To value: This is the desired result. In our example, we'll enter 70 because we need to earn at least that to pass the class.
   • By changing cell: This is the cell where Goal Seek will place its answer. In our example, we'll select cell B6 because we want to determine the grade we need to earn on the final assignment.
4. When you're done, click OK.
   
5. The dialog box will tell you if Goal Seek was able to find a solution. Click OK.
   
6. The result will appear in the specified cell. In our example, Goal Seek calculated that we will need to score at least a 90 on the final assignment to earn a passing grade.
   
   

To use Goal Seek (Example 2):

Let's say you need a loan to buy a new car. You already know you want a loan amount of $20,000, a 60-month term—the length of time it takes to pay off the loan—and a payment of no more than $400 per month. However, you're not sure yet what the interest rate will be.

In the image below, you can see that Interest Rate is left blank and Payment is $333.33. This is because the payment is being calculated by a specialized function called the PMT (Payment) function, and $333.33 is what the monthly payment would be if there were no interest ($20,000 divided by 60 monthly payments).

If we typed different values into the empty Interest Rate cell, we could eventually find the value that causes Payment to be $400, and that would be the highest interest rate that we could afford. However, Goal Seek can do this automatically by starting with the result and working backward.

To insert the PMT function:

1. Select the cell where you want the function to be.
2. From the Formula tab, select the Financial command.
   
3. A drop-down menu will appear showing all financial-related functions. Scroll down and select the PMT function.
   
4. A dialog box will appear.
5. Enter the desired values and/or cell references into the different fields. In this example, we're only using Rate, Nper (the number of payments), and Pv (the loan amount).
   
6. Click OK. The result will appear in the selected cell. Note that this is not our final result because we still don't know what the interest rate will be.
   

To use Goal Seek to find the interest rate:

Now that we've added the PMT function, we can use Goal Seek to find the interest rate we'll need.

1. From the Data tab, click the What-If Analysis command.
2. Select Goal Seek.
   
3. A dialog box will appear containing three fields:
   • Set cell: This is the cell that will contain the desired result (in this case, the monthly payment). In this example, we will set it to B5 (it doesn't matter whether it's an absolute or relative reference).
   • To value: This is the desired result. We'll set it to -400. Because we're making a payment that will be subtracted from our loan amount, we have to enter the payment as a negative number.
   • By changing cell: This is the cell where Goal Seek will place its answer (in this case, the interest rate). We'll set it to B4.
   
4. When you're done, click OK. The dialog box will tell you whether Goal Seek was able to find a solution. In this example, the solution is 7.42%, and it has been placed in cell B4. This tells us that a 7.42% interest rate will give us a $400-per-month payment on a $20,000 loan that is paid off over five years, or 60 months.
   

Other types of what-if analysis

For more advanced projects, you may want to consider the other types of what-if analysis: scenarios and data tables. Rather than start from the desired result and working backward like with Goal Seek, you can use these options to test multiple values and see how the results change.

• Scenarios let you substitute values for multiple cells (up to 32) at the same time. You can create as many scenarios as you want and then compare them without changing the values manually. In the example below, each scenario contains a term and an interest rate. When each scenario is selected, it will replace the values in the spreadsheet with its own values, and the result will be recalculated.
  

For more information on scenarios, check out this article from Microsoft.

• 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 the same time, unlike scenarios or Goal Seek. In the example below, we can view 24 possible results for a car loan.
  
  

For more information on data tables, check out this article from Microsoft.