11.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 a labor-saving investment. In year 0, the project requires a

$6,300 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 $1,200. With or

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

3.0% in year 2 and declines to 2.0% in year 5. The real growth rate is 16.0% in year 2 and declines to

7.0% in year 5. The tax rate is 38.0% in all years. The real cost of capital is 9.5% in year 1 and declines to

8.9% 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 $11,500, $3,200, $4,500, and $1,800,

respectively. With the cost-reducing investment, the firm's year 1 labor costs will be $1,300 and revenues

and other cash expenses will remain the same. What is the cost-reducing project NPV?

Solution Strategy. Forecast revenues and expenses both without the cost-reducing investment and with

it. Calculate the Net Cash Flow both without and with the cost-reducing investment. Subtract one from

the other to obtain the incremental Difference Due to Investment. Discount the project net cash flows

back to the present and determine the NPV.

FIGURE 11.1 Spreadsheet for Cost-Reducing Project - Basics.

How To Build Your Own Spreadsheet Model.

1. Set-up Row and Column Titles. 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 two rows as column titles and freezes the first column as row titles.

2. Inputs. Enter the key assumptions in the range C5:G8. Enter the year 1 revenues and expenses

without the investment into the range C15:C17. Enter the existing depreciation in cell C20. Enter

the year 1 revenues and expenses with the investment into the range C30:C32. Enter the year 0

investment as a negative cash flow in cell B46 and the year 5 salvage value as a positive cash

flow in cell G46.

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+C5)*(1+C6)-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 Without Investment Cash Flows. Forecast each item of the Cash Flows spreadsheet as

appropriate.

o Revenue This Year = (Revenue Last Year) * (1 + Inflation Rate) * (1 + Real Growth

Rate). Enter =C15*(1+D$5)*(1+D$7) in cell D15. Labor Costs and Other Cash

Expenses are forecast similarly, so copy cell D15 to the range D15:G17.

o Gross Margin = Revenue - (Labor Costs) - (Other Cash Expenses). Enter =C15-C16-

C17 in cell C18 and copy it across.

o Depreciation is constant over time due to the use of the straight line method. Enter =C20

in cell D20 and copy it across.

o Pretax Profit = (Gross Margin) - Depreciation. Enter =C18-C20 in cell C21 and copy it

across.

o Income Taxes = Operating Income * Tax Rate. Enter =C21*C$8 in cell C23 and copy it

across.

o After Tax Profit = Pretax Profit - Income Taxes. Enter =C21-C23 in cell C24 and copy

it across.

o Add Back Depreciation = Depreciation. Enter =C20 in cell C26 and copy it across.

o Cash Flows = After-tax Profit + Add Back Depreciation. Enter =C24+C26 in cell C27.

5. Forecast With Investment Cash Flows. The With Investment formulas are identical to the

Without Investment formulas with two exception. Start by copying the range A15:G27 to the cell

A30. The first exception is that the With Investment Labor Cost input needs to be reset to

$1,300 in cell C31. The second exception is the With Investment Depreciation needs to pick up

the addition depreciation from the new investment. With Investment Depreciation = Without

Investment Depreciation -(New Investment) / (Number of years to fully depreciate). The new

investment is subtracted in order offset the negative sign on the New Investment. Enter =C20-

$B$46/5 in cell C35 and copy it across.

6. Difference Due to Investment and NPV. The Difference Due to Investment = With Investment

Net Cash Flows - Without Investment Net Cash Flows. Enter =C42-C27 in cell C45 and copy it

across. Project Net Cash Flows = Difference Due To Investment + Investment and Salvage

Value. Enter =B45+B46 in cell B47 and copy it across.

7. Present Value and NPV. Discount each cash flow back to the present and calculate the NPV as

follows:

o Present Value of Each Cash Flow = (Project Cash Flows) / (1 + Cumulative Discount

Factor). Enter =B47/(1+B12) in cell B48 and copy across.

o Project Net Present Value = Sum of Present Value of the Cash Flows. Enter

=SUM(B48:I48) in cell B49.

The Net Present Value of this Cost-reducing Project is $1,678. The project should be accepted.