370 Part 4 Investing in Long-Term Assets: Capital Budgeting
three zeros to streamline the presentation. Also note that we used Excel to make
Table 12-1. We could have used a calculator and plain paper, but Excel is much
better when dealing with arithmetic. You don’t need to know Excel to understand
the discussion; but if you plan to work in " nance—or in almost any business
function—you should learn something about it.
The column headers in the table, the A through I, and the row headers, 1
through 38, designate cells, which contain the data. For example, the equip-
ment needed for Project S will cost $900, and that number is shown in Cell E4
as a negative. The equipment is expected to have a salvage value of $50 at the
end of the project’s 4-year life; this is shown in Cell I19.^1 The new project will
require $100 of working capital; this is shown in Cell E5 as a negative number
because it is a cost and then as a positive number in Cell I21 because it is recov-
ered at the end of Year 4. The total investment at Time 0 is $1,000, which is
shown in Cell E22.
Unit sales of Project S are shown on Row 7; they are expected to decline
somewhat over the project’s 4-year life. The sales price, a constant $10, is shown
on Row 8. The projected variable cost per unit is given on Row 9; it generally
increases over time due to expected increases in materials and labor. Sales rev-
enue, which is calculated as units multiplied by price, is given on Row 10. Vari-
able costs, equal to units multiplied by VC/unit, are given on Row 11; and
" xed costs excluding depreciation, which are a constant $2,000, are shown on
Row 12.
Depreciation is found as the annual rate allowed by the IRS times the depre-
ciable basis. As noted in Chapter 3, Congress sets the depreciation rates that can be
used for tax purposes and these are the tax rates used in capital budgeting.
Congress permits " rms to depreciate assets by the straight-line method or by an
accelerated method. As we will see, pro" table " rms are better off using accelerated
depreciation. We discuss depreciation more fully in Appendix 12A; but to simplify
things for this chapter, we assume that the applicable accelerated rates for a project
with a 4-year life are as given on Row 24 of the depreciation section of the table
and that straight-line rates are as given on Row 27. Thus, we assume that if the
" rm uses accelerated depreciation, it will write off 33% of the basis during Year 1,
another 45% in Year 2, and so forth. These are the rates used to obtain the cash
! ows shown in the table.
The depreciable basis is the cost of the equipment including any shipping or
installation costs, or $900 as shown in Cells E4, C24, and C27. The total deprecia-
tion over the 4 years equals the cost of the equipment.
If for some reason the " rm decided to use straight-line depreciation, it could
write off a constant $225 per year. Its total cash! ows over the entire 4 years would
be the same as under accelerated depreciation; but under straight line, those cash
! ows would come in a bit slower because the " rm would have higher tax pay-
ments in the early years and lower tax payments later on.
We calculate the annual cash! ows for Project S over the 4 years in Columns F,
G, H, and I, ending with the net cash! ows shown on Row 22. The numbers in
Cells E22 through I22 amount to a cash! ow time line, and they are the same num-
bers used in Chapter 11 for Project S. Since the numbers are the same, the NPV,
IRR, MIRR, and payback shown in Cells C31 through C34 are the same as those we
calculated in Chapter 11.
The Excel model used to make Table 12-1 is part of the chapter Excel model
available on the text’s web site. We recommend that anyone with a computer and(^1) The equipment will be fully depreciated after 4 years. Therefore, the $50 estimated salvage value will exceed the
book value, which will be zero. This $50 gain is classi! ed as a recapture of depreciation, and it is taxed at the same
rate as ordinary income.