10.1 Basics

К оглавлению 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. 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 $11,350 investment in plant and equipment, is

depreciated using the straight-line method over seven years, and there is a salvage value of $1,400 in year

7. The project is forecast to generate sales of 2,000 units in year 1, rising to 7,400 units in year 5,

declining to 1,800 units in year 7, and dropping to zero in year 8. The inflation rate is forecast to be 2.0%

in year 1, rising to 4.0% in year 5, and then leveling off. The real cost of capital is forecast to be 11.0% in

year 1, rising to 12.2% in year 7. The tax rate is forecast to be a constant 35.0%. Sales revenue per unit is

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

year 1 and then grow with inflation. Cash fixed costs are forecast to be $5,280 in year 1 and then grow

with inflation. What is the project NPV?

Solution Strategy. Forecast key assumptions, discounting, sales revenue per unit, variable costs per unit,

and fixed costs over the seven year horizon. Then, forecast the project income and expense items.

Calculate the net cash flows. Discount each cash flow back to the present and sum to get the NPV.

Modeling Issue. The inflation rate is forecast separately and explicitly enters into the calculation of: (1)

the discount rate (= cost of capital) and (2) price or cost / unit items. This guarantees that we are

consistent in the way we are treating the inflation component of cash flows in the numerator of the NPV

calculation and the inflation component of the discount rate in the denominator of the NPV calculation.

This avoids a common error in practice that people often treat the cash flows and discount rates as if they

were unrelated to each other and thus they are inconsistent in way that they implicitly treat the inflation

component of each.

FIGURE 10.1 Spreadsheet for Project NPV - Basics.

How To Build Your Own Spreadsheet Model.

1. Set-up Titles and Freeze Panes. Enter column titles, such as 2001, 2002, etc. in row 2 and Year

0, Year 1, etc. in row 3. Then, place the cursor in cell B4 and click on Window | Freeze Panes.

This freezes the top three rows to provide column titles and freezes the first column to provide

row titles.

2. Inputs. Enter the key assumptions in the range C5:I8, the year 1 price and cost inputs in the

range C15:C17, the year 0 investment in plant and equipment (as a negative number) in cell B35,

and the year 7 salvage value in cell I35.

3. Discounting. Calculate the (nominal) discount rate, which is the (nominal) cost of capital. Then

calculate the cumulative discount rate.

o Discount Rate = Cost of Capital. The formula for the (Nominal) Discount Rate = (1 +

Inflation Rate) * (1 + Real Discount Rate) - 1. Enter =(1+C6)*(1+C7)-1 in cell C11 and

copy it across.

o Cumulative Discount Factor. This is the product of the year-by-year discount factors

cumulated to a given date. Enter 0.0% in cell B12. The rest are calculated as (This Year's

Cumulative Discount Factor) = (1 + Last Year's Cumulative Discount Factor) * (1 + This

Year's Discount Rate) - 1. Enter =(1+B12)*(1+C11)-1 in cell C12 and copy across.

4. Forecast Price and Cost Items. The price and cost items are projected by growing the item at

the inflation rate. This Year's Price/Cost = (Last Year's Price/Cost) * (1 + This Year's Inflation

Rate). Enter =C15*(1+D$6) in cell D15 and copy the it to the range D15:I17. The $ signs in D$6

locks in row 6, which the inflation rate row.

5. Cash Flow Forecasts. Forecast each of the cash flow items as appropriate.

o Sales Revenue = (Sales Revenue / Unit) * (Units sold). Enter =C5*C15 in cell C20 and

copy across.

o Variable Costs = (Variable Costs / Unit) * (Units sold). Enter =C5*C16 in cell C21 and

copy across.

o Gross Margin = Sales Revenue - Variable Costs. Enter =C20-C21 in cell C22 and copy

across.

o Cash Fixed Costs = Cash Fixed Costs. Enter =C17 in cell C24 and copy across.

o Depreciation = -(Investment in Plant and Equipment) / (Number of years to fully

depreciate). Depreciation is held constant each year, because the straight-line method is

being used. Enter =-$B$35/7 in cell C25 and copy across.

o Total Fixed Costs = Cash Fixed Costs + Depreciation. Enter =C24+C25 in cell C26 and

copy across.

o Operating Profit = Gross Margin - Total Fixed Costs. Enter =C22-C26 in cell C28 and

copy across.

o Taxes = Operating Profit * Tax Rate. Enter =C28*C8 in cell C29 and copy across.

o Net Profit = Operating Profit - Taxes. Enter =C28-C29 in cell C30 and copy across.

o Add Back Depreciation = Depreciation. Enter =C25 in cell C32 and copy across.

o Operating Cash Flow = Net Profit + Add Back Depreciation. Enter =C30+C32 in cell

C33 and copy across.

o Cash Flows = Operating Cash Flow + Investment in Plant and Equipment. Enter

=B33+B35 in cell B36 and copy across.

6. Present Value and NPV. Discount the forecasted cash flows back to the present as follows:

o Present Value of Each Cash Flow = (Cash Flow) / (1 + Cumulative Discount Factor).

Enter =B36/(1+B12) in cell B37 and copy across.

o Net Present Value = Sum of Present Value of the Cash Flows. Enter =SUM(B37:I37) in

cell B38.

The Net Present Value of the project is $6,117. The project should be accepted.