Microsoft Office Tutorials and References

In Depth Information

**Chapter 17: Sensitivity Analysis—One-and Two-Way Data Tables**

CHAPTER
17

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

“¼”

¼

and

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