Setting up the model
To demonstrate how to use Scenario Manager, let’s apply it to a simple loan calculation model. The theory behind loan calculations is quite complex, but fortunately, Excel handles loans quite easily.In the example shown below, you’ll see an interest rate calculator upon which you can test the sensitivity of monthly repayments to changes in interest rates and loan terms. Follow these steps:
- Download File 0801.xlsx, open it, and select the tab labeled 8-14, or simply set up the model with hard-coded input assumptions.
Setting up the PMT function to calculate monthly loan repayments. - In cell B11, type =PMT( and press Ctrl+A.
The Function Arguments dialog box appears.
The PMT function requires the following inputs:
- Rate: The interest rate.
- Nper: The number of periods over the life of the loan.
- Pv: The present value of the loan (the amount borrowed).
- Fv: The amount left at the end of the loan period. (In most cases, you want to pay the entire amount back during the loan period, so you can leave this blank.)
- Type: Whether you want the payments to occur at the beginning or the end of the period. (You can leave this blank for the purposes of this exercise.)
- Link the fields in the Function Arguments dialog box to the inputs in your model.
The PMT function returns the annual repayment amount. Because you want to calculate the monthly repayment amount, you could simply divide the entire formula by 12, but because the interest is compounding, it’s more accurate to divide each field by 12 within the formula. So, the rate in the first field is converted to a monthly rate, and the number of periods in the second field is also converted to a monthly rate.
- Click OK.
The formula is =PMT(B7/12,B9*12,B5).
This function returns a negative value because this is an expense. For your purposes here, change it to a positive by preceding the function with the minus sign.
Applying Scenario Manager
Now you can use Scenario Manager to add some scenarios. You want to know the impact of changes in inputs on your monthly repayments. Follow these steps:- On the Data tab, in the Forecast section of the Ribbon, click the What-if Analysis icon, and select Scenario Manager from the drop-down list.
The Scenario Manager dialog box appears.
- Click the Add button to create a new scenario.
The Add Scenario dialog box appears.
Building the scenario using Scenario Manager. - Enter a name for the first scenario in the Scenario Name box (for example, Scenario One).
- Enter the cell references for the variable cells in the Changing Cells box.
Separate each reference with a comma (if there is more than one), but don’t use spaces. You can also hold down the Ctrl key and click each cell in the spreadsheet to insert the references into the box.
- Click OK.
The Scenario Values dialog box appears with the existing values (0.045 for the interest rate and 25 for the years).
- Click OK to accept these values as Scenario One.
- Click Add to add another scenario.
The Add Scenario dialog box appears again.
- Enter a name for the second scenario in the Scenario Name box (for example, Scenario Two).
- Click OK.
The Scenario Values dialog box appears again.
- Enter the variables’ values for this scenario (for example, 0.05 for the interest rate and 30 for the years).
Entering scenario values using Scenario Manager. - Click OK.
You’re returned to the Scenario Manager dialog box.
- Follow Steps 7 through 9 again to create additional scenarios.
- After you’ve created all the scenarios, you can use the Scenario Manager to view each scenario by clicking the Show button at the bottom.
The inputs are automatically changed to show the scenarios.
Scenarios are sheet-specific, meaning they only exist in the sheet where you created them. So when you’re looking for the scenarios you’ve created, you have to select the correct sheet in the model.
