Excel 2010 Using What-If Analysis

Author: mety Labels:: Excel 2010 Using What-If Analysis

Excel 2010

Using What-If Analysis

The real power in Excel comes in its ability to perform multiple mathematical calculations for you. One of the tools in Excel that you can use to perform these calculations is a Data tool called What-If Analysis. What-If analysis allows you to see the effect that different values have in formulas. Have you ever thought, "What interest rate do I need to qualify for to have a car payment of $400 on the car I want?" This question can be answered using What-If Analysis.

In this lesson, you will learn how to use a What-If Analysis tool called Goal Seek.

What-If Analysis

Watch the video (4:08). Need help?

In many worksheets, there may be some cells whose values areunknown, or you may just want to change certain cells to see what the outcome is. What-if analysis is perfect for these situations. It allows you to experiment and answer questions with your data, even when the data is incomplete.

Watch the video to learn about using what-if analysis.

Goal Seek

Goal Seek is a type of what-if analysis that is useful if you know the desired result, but need to find the input valuethat will give you that result. For example, suppose you need a loan to buy a new car. You already know that 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 paymentof no more than $400 a month. However, you're not sure yet what the interest rate is going to be.

In the image below, you can see that Interest Rate is left blank, and Payment is $333.33. That's 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).

Function calculating the monthly payment

If we typed different values into the empty Interest Rate cell, we could eventually find the value that causesPayment 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.

You'll need to understand how functions work before you use what-if analysis. If you want, you can review functions in our Working with Basic Functions lesson.

To Insert the Payment Function:

Select the cell where you want the function to be.
From the Formula tab, select the Financial command.
The Financial command
A drop-down menu will appear showing all finance-related functions. Scroll down and select the PMT function.
Selecting the PMT function
A dialog box will appear.
Enter the desired values and/or cell references into the different fields. In this example, we're only usingRate, Nper (the number of payments), and Pv (the loan amount).
Entering values into the necessary fields
Click OK. The result will appear in the selected cell. Note that this is not our final result, as we still don't know what the interest rate will be.
The monthly payment, not including interest
To Use Goal Seek to Find the Interest Rate:
1. From the Data tab, click the What-If Analysis command.
2. Select Goal Seek.
  Selecting 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. Since we're making a payment that will besubtracted 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.
  Entering values into the Goal Seek fields
4. When you're done, click OK. The dialog box will tell you whether or not 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-a-month payment on a $20,000 loan that is paid off over 5 years, or 60 months.
  Solution found by Goal Seek
  Other Types of What-If Analysis
  
  For more advanced projects, you may want to look at the other two types of what-if analysis: scenarios and data tables. Rather than starting from the desired result and working backward, like Goal Seek, these options allow you to test multiple values and see how the result changes.
  
  Below is an introduction to some of the things you can do with scenarios and data tables.
  - Scenarios let you substitute values for multiple cells (up to 32) at the same time. It is especially well-suited to showing best-case and worst-case scenarios. You can create as many scenarios as you want, and then compare them without having to manually change all of the values. 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.
    Selecting a scenario
  For more information about scenarios, check out this article on the Microsoft site.
  - Data Tables allow you to take one or two variables in a formula and replace them with as many different values as you want, and then view the results in a table. This option is especially powerful because it showsmultiple results at the same time, unlike Scenarios or Goal Seek. In the example below, 24 possible resultsare shown in the table; doing the same task with Scenarios would take much longer.
    Using a data table to compare different terms and interest rates
  For more information about data tables, check out this article on the Microsoft site.