Microsoft Office Tutorials and References
In Depth Information
Exhibit 9.15 Creating scenarios
of interest in the initial year. For example, if we begin payment in the month of
September, the start_period is 1 and the end_period is 4, indicating that we accu-
mulated interest payments for four months, September through December. At the
bottom of Exhibit 9.14 are the values of the six scenarios, A through F, for Padcha’s
So how do we create a scenario? The process of creating scenarios is shown in
Exhibit 9.15a, 9.15b, and 9.15c and is described as follows:
1. We begin by engaging the What-If Analysis tools in the Data Ribbon and Data
Tools Group. Section a of Exhibit 9.15, Scenario Manager ,showstheﬁrst
dialogue box encountered. As you can see, no scenarios are currently deﬁned.
2. In section b of Exhibit 9.15 we depress the Add button, and the Edit Scenario
dialogue box becomes available. Here we name scenarios and identify Changing
Cells : the cells that contain the data inputs for the calculations of interest.
3. Next, the Scenarios Values dialogue box permits entry of the individual values
for the cells, as shown in section c of Exhibit 9.15. Note that Excel recog-
nizes the cell name for C10:C12—C10 as IntRate , C11 as NumPeriods , and
C12 as Principal . The cell ranges were named in the spreadsheet for ease of
4. The process is repeated for each scenario by selecting the Add buttononthe
Scenarios Values dialogue box.
5. When you return to the Scenario Manager dialogue box by selecting OK ,the
named scenarios will appear in the window.
6. Finally, we are able to select the Summary button to generate a report, as either
a Scenario summary or a Scenario PivotTable report , as shown in the Scenario
Summary dialogue box in section c of Exhibit 9.15.
The resulting Scenario summary report is shown in Exhibit 9.16. In this report,
I also have named the results cells: (1) MntlyPmt is monthly payment for the mort-
gage, (2) CumIntyr1 and CumIntyr2 are cumulative interest payments in years 1 and
2, respectively, and (3) SumIntyr1_2 is the sum of year 1 and 2 cumulative inter-
est. The report provides a convenient format for presenting comparative results. If