Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition

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.

FIGURE 17.1 The Car Loan Model

The rest of the table is set up to the right of the varying rates column. ...