What-If Analysis

What-If Analysis

Introduction

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.

Example

You need a loan to buy a new car. You know how much money you want to borrow, how long of a period you want to take to pay off the loan (the term), and what payment you can afford to make each month. But what you need to know is what interest rate you need to qualify for to make the payment $400 a month. In the image below, you can see that if you didn’t have interest and just divided this $20,000 into 60 monthly payments, you would pay $333.33 a month. The What-If Analysis tool will allow you to easily calculate the interest rate.

Where Did the Formula Come From?

The formula that appears in cell B5 in the example image is a function. It isn't part of the What-if Analysis tool, so you will need to understand functions thoroughly before you use What-if Analysis. For the example scenario described above, you need a formula that will calculate the monthly payment. Instead of writing the formula yourself, you can insert a function to do the calculation for you.

To Insert a Payment Function:

• Select the Formula tab.
• Click the Insert Function command. A dialog box appears.
• Select PMT.
• Click OK. A dialog box appears.
• Insert your cursor in the first field. A description about the needed information appears at the bottom of the dialog box.

• Select the cell in the spreadsheet with the needed information.
• Insert your cursor in the next field. A description about the needed information appears at the bottom of the dialog box.
• Select the cell in the spreadsheet with the needed information.
• Repeat the last two steps until all the necessary information is entered in the dialog box.
• Click OK.

What-If Analysis Tools

There are three What-If analysis tools that you can use. To access these, select the Data tab, and locate the What-If Analysis command. If you click this command, a menu with three options appears.

Goal seek is useful if you know the needed result, but need to find the input value that will give you the desired result. In this example, we know the desired result (a $400 monthly payment), and are seeking the input value (the interest rate).

Goal Seek

To Use Goal Seek to Determine an Interest Rate:

• Select the Data tab.
• Locate the Data Tools group.
• Click the What-If Analysis command. A list of three options appears.

• Select Goal Seek. A small dialog box appears.
• Select the cell that you what to set to a specific value. In this example, we want to set B5, the Payment cell.

• Insert the cursor in the next field.
• Enter a value in the value field. In this example, type -$400. Since we’re making a payment that will be subtracted from our loan amount, we have to enter the payment as a negative number.

• Insert the cursor in the next field.
• Select the cell that you want to change. This will be the cell that tries various input values. In this example, select cell B4, which is the interest rate.

• Click OK.
• Then, click OK again. The interest rate appears in the cell. This indicates that a 7% 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.

Scenarios in Excel

Scenarios come under the heading of "What-If Analysis" in Excel. They are similar to tables in that you are changing values to get new results. For example, What if I reduce the amount I'm spending on food? How much will I have left then? Scenarios can be saved, so that you can apply them with a quick click of the mouse.

An example of a scenario you might want to create is a family budget. You can then make changes to individual amounts, like food, clothes, or fuel, and see how these changes effect your overall budget.

We'll see how they work now, as we tackle a family budget. So, create the spreadsheet below:

In B12, it is just a SUM function, and is your total debts. The figure in D3 is how much you have to spend each month. The figure in D13 is how much you have left after you deduct all your debts.

With only 46/- spending money left each month, clearly some changes have to be made. We'll create a scenario to see what effect the various budgets cuts have.

· From the top of Excel click the Data menu

· On the Data menu, locate the Data Tools panel

· Click on the What if Analysis item, and select Scenario Manager from the menu:

When you click Scenario Manager, you should the following dialogue box:

We want to create a new scenario. So click the Add button. You'll then get another dialogue box popping up:

The J22 in the image is just whatever cell you had selected when you brought up the dialogue boxes. We'll change this. First, type a Name for your Scenario in the Scenario Name box. Call it Original Budget.

Excel now needs you to enter which cells in your spreadsheet will be changing. In this first scenario, nothing will be changing (because it's our original). But we still need to specify which cells will be changing. Let's try to reduce the Food , the Clothes l, and the Phone expenditure. These are in cells B7 to B9 in our spreadsheet. So in the Changing Cells box, enter B7:B9

Don't forget to include the colon in the middle! But your Add Scenario box should look like this:

Click OK and Excel will ask you for some values:

We don't want any values to change in this first scenario, so just click OK. You will be taken back to the Scenario Manager box. It should now look like this:

Now that we have one scenario set up, we can add a second one. This is where we'll enter some new values - our savings.

Click the Add button again. You'll get the Add Scenario dialogue box back up. Type a new Name, something like Budget Two. The Changing Cells area should already say B7:B9. So just click OK.

You will be taken to the Scenario Values dialogue box again. This time, we do want to change the values. Enter the same ones as in the image below:

These are the new values for our Budget. Click OK and you'll be taken back to the Scenario Manager. This time, you'll have two scenarios to view:

As you can see, we have our Original Budget, and Budget Two. With Budget Two selected, click the Show button at the bottom. The values in your spreadsheet will change, and the new budget will be calculated. The image below shows what it looks like in the spreadsheet:

Click on the Original Budget to highlight it. Then click the Show button. The first values will be displayed!

Click the Close button on the dialogue box when you're done.

So a Scenario offers you different ways to view a set of figures, and allows you to switch between them quite easily.