11.2 Sensitivity Analysis

К оглавлению 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
85 86 87 88 89 90 91 92 93 94 95 96 97

Problem. For the same cost-reducing project as the previous section, analyze the sensitivity of the Project

NPV to the assumed With Investment Labor Costs.

Solution Strategy. Create a Data Table using With Investment Labor Costs as the input variable and

Project NPV as the output variable. Then graph the relationship.

FIGURE 11.2 Spreadsheet for Cost-Reducing Project - Sensitivity Analysis.

How To Build Your Own Spreadsheet Model.

1. Open the Basics Spreadsheet. Open the spreadsheet that you created for Cost-Reducing Project

NPV - Basics and immediately save the spreadsheet under a new name using the File | Save As

command.

2. Data Table. Create a list of input values for With Investment Labor Costs ($900, $1,100, $1,300,

etc.) in the range C53:G53. Create an output formula that references the Project Net Present

Value by entering the formula =B49 in cell B54. Select the range B53:G54 for the One-Variable

Data Table. This range includes both the input values on the top of the range and the output

formula on the left side of the range. Then choose Data | Table from the main menu and a Table

dialog box pops up. Enter the cell address C31 (With Investment Labor Costs) in the Row Input

Cell and click on OK.

3. Graph. Highlight the interior of the data table (excluding the top or side) in the range C54:G54

and then choose Insert | Chart from the main menu. Select the XY (Scatter) chart type and

make other selections to complete the Chart Wizard.

The sensitivity analysis indicates that the Project NPV is not very sensitive to a wide range of values of

With Investment Labor Costs. It all cases the project has a positive NPV. This provides confidence that

the project's positive NPV is robust to any reasonable error in estimating the labor cost savings.

Problems

Skill-Building Problems.

1. Suppose a firm is considering a labor-saving investment. In year 0, the project requires a $11,700

investment in equipment (all figures are in thousands of dollars). This investment is depreciated

using the straight-line method over five years and there is salvage value in year 5 of $4,500. With

or without the cost-reducing investment, all cash flows start in year 1 and end in year 5. The

inflation rate is 2.6% in year 2 and declines to 1.4% in year 5. The real growth rate is 21.3% in

year 2 and declines to 9.5% in year 5. The tax rate is 41.0% in all years. The real cost of capital is

8.7% in year 1 and declines to 7.5% in year 5. Without the cost-reducing investment, the firm's

existing investments will generate year 1 revenue, labor costs, other cash expenses, and

depreciation of $15,200, $4,100, $5,300, and $3,300, respectively. With the cost-reducing

investment, the firm's year 1 labor costs will be $1,600 and revenues and other cash expenses will

remain the same. What is the cost-reducing project NPV?

2. For the same cost-reducing project as problem 1, analyze the sensitivity of the Project NPV to the

assumed With Investment Labor Costs.

Live In-class Problems.

3. Given the partial Basics spreadsheet CostbasZ.xls, do steps 5 Forecast With Investment Cash

Flows and 6 Difference Due to Investment and NPV.

4. Given the partial Sensitivity Analysis spreadsheet CostsenZ.xls, do step 2 Data Table.