10.4 Sensitivity Analysis

Problem. Consider the same project as Project NPV - Working Capital. Assume that the product lifecycle

of seven years is viewed as a safe bet, but that the scale of demand for the product is highly

uncertain. Analyze the sensitivity of the project NPV to the units sales scale factor and to the cost of

capital.

Solution Strategy. Copy the pattern of unit sales in the base case to a new location and multiply this

pattern by a scale factor to get the new unit sales scenario. Assume that the real cost of capital is constant.

Thus, forecast the future cost of capital by taking the year 1 cost of capital and adding the change in the

inflation rate. Create a two-way data table using a range of input values for units sales scale factor and a

range of input values for the year 1 cost of capital. Using the data table results, create a 3-D surface chart.

FIGURE 10.6 Spreadsheet for Two-Way Data Table and 3-D Surface Chart.

How To Build Your Own Spreadsheet Model.

1. Open the Working Capital Spreadsheet and Insert Rows. Open the spreadsheet that you

created for Project NPV - Working Capital and immediately save the spreadsheet under a new

name using the File | Save As command. Select the range A5:A6 and click on Insert | Row.

Select the cell A9 and click on Insert | Row.

FIGURE 10.7 Spreadsheet showing modified Key Assumptions.

2. Unit Sales. Save the base case pattern and multiply it by a scale factor to determine unit sales.

o Base Case Unit Sales = the original sales pattern. Copy the range C7:I7 to C5.

o Unit Sales Scale Factor. Enter 100.0% in C6.

o Unit Sales = (Base Case Unit Sales) * (Unit Sales Scale Factor). Enter =C5*$C$6 in C7

and copy it across.

3. Real Cost of Capital. Save the base case changes as a set of increments and add the increments

to the date 0 real cost of capital to determine the current real cost of capital.

o Real Cost of Capital Increment. Enter input increments in the range D9:I9.

o Real Cost of Capital on date t = (Date 0 Real Cost of Capital) + (Increment on date t).

Enter =$C$10+D9 in D10 and copy it across.

4. Two-Way Data Table. Create a list of input values for Unit Sales Scale Factor (80%, 90%,

100%, etc.) in the range C64:G64. Create a list of input values for Cost of Capital (9.0%,

11.0%, 13.0%, etc.) in the range B65:B69. Create an output formula that references the product

NPV by entering the formula =B60 in cell B64. Select the range B64:G69 for the Data Table.

This range includes both the input values at the top of the data table and on the left of the data

table. Then choose Data | Table from the main menu and a Table dialog box pops up. Enter the

cell address C6 (for Unit Sales Scale Factor) in the Row Input Cell, the cell address C10 (for

the Date 0 Real Cost of Capital) in the Column Input Cell, and click on OK.

5. 3D Graph of the Sensitivity Analysis. Highlight the range C65:G69 and then choose Insert |

Chart from the main menu. Select a Surface chart type and make other selections to complete

the Chart Wizard. To label the x-axis and y-axis, right-click on the chart, select Source Data ...

from the pop-up menu, enter C64:G64 in the Category (X) axis labels text box, select Series1 in

the Series pick list, enter B65 in the Name text box, select Series2 in the Series pick list, enter

B66 in the Name text box, and so on until every series has a label.

The sensitivity analysis shows that the Project NPV is highly sensitive to the Unit Sales Scale Factor and

the Cost of Capital. If the sales forecast is overly optimistic and/or cost of capital estimate is too low, then

the project might actually have a negative NPV. Hence, it is worth spending extra resources to verify the

accuracy of the sales forecast and the cost of capital estimate.

Problems

Skill-Building Problems.

1. Suppose a firm is considering the following project, where all of the dollar figures are in

thousands of dollars. In year 0, the project requires $37,500 investment in plant and equipment, is

depreciated using the straight-line method over seven years, and there is a salvage value of

$5,600 in year 7. The project is forecast to generate sales of 5,700 units in year 1, rising to 24,100

units in year 5, declining to 8,200 units in year 7, and dropping to zero in year 8. The inflation

rate is forecast to be 1.5% in year 1, rising to 2.8% in year 5, and then leveling off. The real cost

of capital is forecast to be 9.3% in year 1, rising to 10.6% in year 7. The tax rate is forecast to be

a constant 42.0%. Sales revenue per unit is forecast to be $15.30 in year 1 and then grow with

inflation. Variable cost per unit is forecast to be $9.20 in year 1 and then grow with inflation.

Cash fixed costs are forecast to be $7,940 in year 1 and then grow with inflation. What is the

project NPV?

2. Consider the same project as problem 1, but modify it as follows. Suppose that Direct Labor,

Materials, Selling Expenses, and Other Variable Costs are forecast to be $5.20, $3.70, $2.30, and

$0.80, respectively, in year 1 and then grow with inflation. Lease Payment, Property Taxes,

Administration, Advertising, and Other cash fixed costs are forecast to be $4,100, $730, $680,

$1,120, and $730, respectively, in year 1 and then grow with inflation. What is the Total Variable

Cost / Unit, the Total Cash Fixed Costs, and the project NPV?

3. Consider the same project as problem 2, but modify it as follows. Suppose we add that the project

will require working capital in the amount of $1.23 in year 0 for every unit of next year's

forecasted sales and this amount will grow with inflation going forward. What is the project

NPV?

4. Consider the same project as problem 3. Assume that the product life-cycle of seven years is

viewed as a safe bet, but that the scale of demand for the product is highly uncertain. Analyze the

sensitivity of the project NPV to the units sales scale factor and to the cost of capital.

Live In-class Problems.

5. Given the partial Basics spreadsheet ProjbasZ.xls, do steps 5 Cash Flow Forecasts and 6

Present Value and NPV.

6. Given the partial Forecasting Cash Flows spreadsheet ProjforZ.xls, complete steps 2 Inputs, 3

Forecast The Detail Items, and 4 Totals.

7. Given the partial Working Capital spreadsheet ProjworZ.xls, complete steps 2 Forecast Work

Capital / Next Year’s Unit Sales, 3 Forecast Working Capital, and 4 Cash Flows.

8. Given the partial Sensitivity Analysis spreadsheet ProjsenZ.xls, complete step 4 Two-Way Data

Table.

Problem. Consider the same project as Project NPV - Working Capital. Assume that the product lifecycle

of seven years is viewed as a safe bet, but that the scale of demand for the product is highly

uncertain. Analyze the sensitivity of the project NPV to the units sales scale factor and to the cost of

capital.

Solution Strategy. Copy the pattern of unit sales in the base case to a new location and multiply this

pattern by a scale factor to get the new unit sales scenario. Assume that the real cost of capital is constant.

Thus, forecast the future cost of capital by taking the year 1 cost of capital and adding the change in the

inflation rate. Create a two-way data table using a range of input values for units sales scale factor and a

range of input values for the year 1 cost of capital. Using the data table results, create a 3-D surface chart.

FIGURE 10.6 Spreadsheet for Two-Way Data Table and 3-D Surface Chart.

How To Build Your Own Spreadsheet Model.

1. Open the Working Capital Spreadsheet and Insert Rows. Open the spreadsheet that you

created for Project NPV - Working Capital and immediately save the spreadsheet under a new

name using the File | Save As command. Select the range A5:A6 and click on Insert | Row.

Select the cell A9 and click on Insert | Row.

FIGURE 10.7 Spreadsheet showing modified Key Assumptions.

2. Unit Sales. Save the base case pattern and multiply it by a scale factor to determine unit sales.

o Base Case Unit Sales = the original sales pattern. Copy the range C7:I7 to C5.

o Unit Sales Scale Factor. Enter 100.0% in C6.

o Unit Sales = (Base Case Unit Sales) * (Unit Sales Scale Factor). Enter =C5*$C$6 in C7

and copy it across.

3. Real Cost of Capital. Save the base case changes as a set of increments and add the increments

to the date 0 real cost of capital to determine the current real cost of capital.

o Real Cost of Capital Increment. Enter input increments in the range D9:I9.

o Real Cost of Capital on date t = (Date 0 Real Cost of Capital) + (Increment on date t).

Enter =$C$10+D9 in D10 and copy it across.

4. Two-Way Data Table. Create a list of input values for Unit Sales Scale Factor (80%, 90%,

100%, etc.) in the range C64:G64. Create a list of input values for Cost of Capital (9.0%,

11.0%, 13.0%, etc.) in the range B65:B69. Create an output formula that references the product

NPV by entering the formula =B60 in cell B64. Select the range B64:G69 for the Data Table.

This range includes both the input values at the top of the data table and on the left of the data

table. Then choose Data | Table from the main menu and a Table dialog box pops up. Enter the

cell address C6 (for Unit Sales Scale Factor) in the Row Input Cell, the cell address C10 (for

the Date 0 Real Cost of Capital) in the Column Input Cell, and click on OK.

5. 3D Graph of the Sensitivity Analysis. Highlight the range C65:G69 and then choose Insert |

Chart from the main menu. Select a Surface chart type and make other selections to complete

the Chart Wizard. To label the x-axis and y-axis, right-click on the chart, select Source Data ...

from the pop-up menu, enter C64:G64 in the Category (X) axis labels text box, select Series1 in

the Series pick list, enter B65 in the Name text box, select Series2 in the Series pick list, enter

B66 in the Name text box, and so on until every series has a label.

The sensitivity analysis shows that the Project NPV is highly sensitive to the Unit Sales Scale Factor and

the Cost of Capital. If the sales forecast is overly optimistic and/or cost of capital estimate is too low, then

the project might actually have a negative NPV. Hence, it is worth spending extra resources to verify the

accuracy of the sales forecast and the cost of capital estimate.

Problems

Skill-Building Problems.

1. Suppose a firm is considering the following project, where all of the dollar figures are in

thousands of dollars. In year 0, the project requires $37,500 investment in plant and equipment, is

depreciated using the straight-line method over seven years, and there is a salvage value of

$5,600 in year 7. The project is forecast to generate sales of 5,700 units in year 1, rising to 24,100

units in year 5, declining to 8,200 units in year 7, and dropping to zero in year 8. The inflation

rate is forecast to be 1.5% in year 1, rising to 2.8% in year 5, and then leveling off. The real cost

of capital is forecast to be 9.3% in year 1, rising to 10.6% in year 7. The tax rate is forecast to be

a constant 42.0%. Sales revenue per unit is forecast to be $15.30 in year 1 and then grow with

inflation. Variable cost per unit is forecast to be $9.20 in year 1 and then grow with inflation.

Cash fixed costs are forecast to be $7,940 in year 1 and then grow with inflation. What is the

project NPV?

2. Consider the same project as problem 1, but modify it as follows. Suppose that Direct Labor,

Materials, Selling Expenses, and Other Variable Costs are forecast to be $5.20, $3.70, $2.30, and

$0.80, respectively, in year 1 and then grow with inflation. Lease Payment, Property Taxes,

Administration, Advertising, and Other cash fixed costs are forecast to be $4,100, $730, $680,

$1,120, and $730, respectively, in year 1 and then grow with inflation. What is the Total Variable

Cost / Unit, the Total Cash Fixed Costs, and the project NPV?

3. Consider the same project as problem 2, but modify it as follows. Suppose we add that the project

will require working capital in the amount of $1.23 in year 0 for every unit of next year's

forecasted sales and this amount will grow with inflation going forward. What is the project

NPV?

4. Consider the same project as problem 3. Assume that the product life-cycle of seven years is

viewed as a safe bet, but that the scale of demand for the product is highly uncertain. Analyze the

sensitivity of the project NPV to the units sales scale factor and to the cost of capital.

Live In-class Problems.

5. Given the partial Basics spreadsheet ProjbasZ.xls, do steps 5 Cash Flow Forecasts and 6

Present Value and NPV.

6. Given the partial Forecasting Cash Flows spreadsheet ProjforZ.xls, complete steps 2 Inputs, 3

Forecast The Detail Items, and 4 Totals.

7. Given the partial Working Capital spreadsheet ProjworZ.xls, complete steps 2 Forecast Work

Capital / Next Year’s Unit Sales, 3 Forecast Working Capital, and 4 Cash Flows.

8. Given the partial Sensitivity Analysis spreadsheet ProjsenZ.xls, complete step 4 Two-Way Data

Table.