Microsoft Office Tutorials and References
In Depth Information
Chapter 17: Sensitivity Analysis—One-and Two-Way Data Tables
Sensitivity Analysis—One- and
Two-Way Data Tables
A data table is a range of cells that shows how changing certain inputs in your
model affect the outcome. A data table provides a tool for recalculating multiple
options in one operation while showing them together on your worksheet. A
data table is also referred to by analysts and decision makers as a Sensitivity Analysis
table. Sensitivity analysis is a way to investigate the impact of changes in the input of
the decision-making models.
Using the same car loan example used in the last two chapters, you will explore the
impact on the results of changing some of the inputs in the model. The inputs may be
the price, the number of years of the loan, the interest rate, and so on. The results/
output, in this example, may be the payment, the total payments, or the total interest.
The first example will investigate the use of a data table to demonstrate our
sensitivity analysis using the interest rate. The initial model has a rate of 8.00 percent.
I want to try a range of rates from 3 percent to 9 percent
see Figure 17.1. First, I
must set up the table. In this example, the range of rates is placed down the column
to the left of the planned table. I am going to investigate the impact of changes in the
rate on the last three output items on the sheet in cells B7:B9, the payment, total
payments, and the total interest.
The rest of the table is set up to the right of the varying rates column. Starting
with cell F2, type
and select cell B7 (now called Payment since it was named
previously); then click Enter to transfer the information to that cell. See Figure 17.2.
Repeat this procedure for the values in cells B8 and B9. In cell G2: type
select B8 (or Total Payments value). In cell H2: type
and select Total Interest value
(B9). The results are shown in Figure 17.3.
FIGURE 17.1 The Car Loan Model