Vertex42 The Excel Nexus
Before we leave Blooper and its magnoosium project, we should cover a few extra wrinkles.
modified accelerated cost recovery system (macrs) Depreciation method that allows higher tax deductions in early years and lower deductions later.
A Further Note on Depreciation. We warned you earlier not to assume that all cash flows are likely to increase with inflation. The depreciation tax shield is a case in point, because the Internal Revenue Service lets companies depreciate only the amount of the original investment. For example, if you go back to the IRS to explain that inflation mushroomed since you made the investment and you should be allowed to depreciate more, the IRS won't listen. The nominal amount of depreciation is fixed, and therefore the higher the rate of inflation, the lower the real value of the depreciation that you can claim.
We assumed in our calculations that Blooper could depreciate its investment in mining equipment by $2 million a year. That produced an annual tax shield of $2 million x .35 = $.70 million per year for 5 years. These tax shields increase cash flows from operations and therefore increase present value. So if Blooper could get those tax shields sooner, they would be worth more, right? Fortunately for corporations, tax law allows them to do just that. It allows accelerated depreciation.
The rate at which firms are permitted to depreciate equipment is known as the Modified Accelerated Cost Recovery System, or MACRS. MACRS places assets into one of six classes, each of which has an assumed life. Table 7.4 shows the rate of depreciation that the company can use for each of these classes. Most industrial equipment falls into the 5- and 7-year classes. To keep life simple, we will assume that all of Blooper's mining equipment goes into 5-year assets. Thus Blooper can depreciate 20 percent of its $10 million investment in Year 1. In the second year it could deduct depreciation of .32 x 10 = $3.2 million, and so on.6
How does use of MACRS depreciation affect the value of the depreciation tax shield for the magnoosium project? Table 7.5 gives the answer. Notice that it does not affect the total amount of depreciation that is claimed. This remains at $10 million just as before. But MACRS allows companies to get the depreciation deduction earlier, which increases the present value of the depreciation tax shield from $2,523,000 to $2,583,000, an increase of $60,000. Before we recognized MACRS depreciation, we calculated project NPV as $3,564,000. When we recognize MACRS, we should increase that figure by $60,000.
5 Financial managers sometimes assume cash flows arrive in the middle of the calendar year, that is, at the end of June. This makes NPV also a midyear number. If you are standing at the start of the year, the NPV must be discounted for a further half-year. To do this, divide the midyear NPV by the square root of (1 + r).
This midyear convention is roughly equivalent to assuming cash flows are distributed evenly throughout the year. This is a bad assumption for some industries. In retailing, for example, most of the cash flow comes late in the year, as the holiday season approaches.
6 You might wonder why the 5-year asset class provides a depreciation deduction in Years 1 through 6. This is because the tax authorities assume that the assets are in service for only 6 months of the first year and 6 months of the last year. The total project life is 5 years, but that 5-year life spans parts of 6 calendar years. This assumption also explains why the depreciation is lower in the first year than it is in the second.
A |
B |
c |
D |
E |
F |
I |
H | ||
1 |
Year: |
0 |
1 |
2 |
3 |
4 |
5 |
6 | |
2 |
Capital investment |
10000 | |||||||
3 |
=0.15*C6+2/12*B5 |
=0.15*D6+2/12*C5 |
=0.15*E6+2/12*D5 |
= 0.15*F6+2/12*E5 |
=0.15'G6+2/12*F5 |
=0.15*H6+2/12'G5 |
= 0.1 5*16+2/12*H5 | ||
4 |
Change in Wk Capital |
1 500 |
-C3-B3 |
= D3-C3 |
=E3-D3 |
=F3-E3 |
=G3-F3 |
=H3-G3 | |
5 |
Revenues |
1 5000 |
=C5*1.05 |
= D5*1.05 |
= E5*1.05 |
= F5*1.05 | |||
6 |
Expenses |
10000 |
=C6*1.05 |
= D6*1.05 |
= E6*1.05 |
= F6*1.05 | |||
7 |
Depreciation |
=10000/5 |
=10000/5 |
= 10000/5 |
= 10000/5 |
=10000/5 | |||
8 |
Pretax profit |
=C5-C6-C7 |
= D5-D6-D7 |
=E5-E6-E7 |
=F5-F6-F7 |
=G5-G6-G7 | |||
9 |
Tax |
=C8*0.35 |
= D8*0.35 |
=E8*0.35 |
= F8'0.35 |
=G8'0.35 | |||
1 0 |
Profit after tax |
=C8-C9 |
= D8-D9 |
=E8-E9 |
=F8-F9 |
=G8-G9 | |||
1 1 | |||||||||
1 2 |
Cash flow |
=-B2-B4 |
=-C2-C4+C 10+C7 |
=-D2-D4+D10+D7 |
=-E2-E4+E10+E7 |
=-F2-F4+F10+F7 |
=-G2-G4+G10+G7 |
=-H2-H4+H10+H7 | |
1 3 |
PV of Cash flow |
= B12/(1.1 2)AB1 |
—012/(1.12)AC 1 |
= D 12/( 1.12)AD 1 |
= E1 2/(1.12)AE1 |
= F12/(1.12)AF1 |
= G 12/( 1.12)AG1 |
=H 1 2/(1.12)AH 1 | |
1 4 |
Net present value |
-SUM(B13:H13) |
You might have guessed that discounted cash-flow analysis such as that of the Blooper case is tailor-made for spreadsheets. The worksheet directly above shows the formulas from the Excel spreadsheet that we used to generate the Blooper example. The spreadsheet on the facing page shows the resulting values, which appear in the text in Tables 7.1 through 7.3.
The assumed values are the capital investment (cell B2), the initial level of revenues (cell C5), and expenses (cell C6). Rows 5 and 6 show that each entry for revenues and expenses equals the previous value times (1 + inflation rate), or 1.05. Row 3, which is the amount of working capital, is the sum of inventories and accounts receivable. To capture the fact that inventories tend to rise with production, we set working capital equal to .15 times the following year's expenses. Similarly, accounts receivables rise with sales, so we assumed that accounts receivable would be 1/6 times the current year's revenues. Each entry in row 3 is the sum of these two quantities.1 Net investment in working capital (row 4) is the increase in working capital from one year to the next. Cash flow (row 12) is capital investment plus change in working capital plus profit after tax plus depreciation. In row 13 we discount cash flow at a 12 percent discount rate and in cell B14 we add the present value of each cash flow to find project NPV.
Once the spreadsheet is up and running it is easy to do various sorts of "what if" analysis. Here are a few questions to try your hand.
1. What happens to cash flow in each year and the NPV of the project if the firm uses MACRS depreciation assuming a 3-year recovery period? Assume that Year 1 is the first year that depreciation is taken.
2. Suppose the firm can economize on working capital by managing inventories more efficiently. If the firm can reduce inventories from 15 percent to 10 percent of next year's cost of goods sold, what will be the effect on project NPV?
1 For convenience we assume that Blooper pays all its bills immediately and therefore accounts payable equals zero. If it didn't, working capital would be reduced by the amount of the payables.
Brealey-Myers: Fundamentals of Corporate Finance, Third Edition
II. Value
Brealey-Myers: Fundamentals of Corporate Finance, Third Edition
7. Using Discounted Cash-Flow Analysis to Make Investment excel spreadsheet
A Spreadsheet Model for Blooper*
© The McGraw-Hill Companies, 2001
A |
B |
c |
D |
E |
F |
G |
H | |
1 |
Year: |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
2 |
Capital investment |
10,000 | ||||||
3 |
Working capital |
1,500 |
4,075 |
4,279 |
4,493 |
4,717 |
3,039 |
0 |
4 |
Change In Wk Capital |
1,500 |
2,575 |
204 |
214 |
225 |
-1,679 |
-3,039 |
5 |
Revenues |
15,000 |
15,750 |
16,538 |
17,364 |
18,233 | ||
6 |
Expenses |
10,000 |
10,500 |
11,025 |
11 ,576 |
12,155 | ||
7 |
Depreciation |
2,000 |
2,000 |
2.000 |
2.000 |
2,000 | ||
8 |
Pretax profit |
3,000 |
3,250 |
3,513 |
3,788 |
4,078 | ||
9 |
Tax |
1.050 |
1.138 |
1.229 |
1.326 |
1.427 | ||
1 0 |
Profit after tax |
1,950 |
2,1 13 |
2,283 |
2,462 |
2,650 | ||
1 1 | ||||||||
1 2 |
Cash flow |
-11,500 |
1,375 |
3,909 |
4,069 |
4,238 |
6,329 |
3,039 |
1 3 |
PV of Cash flow |
-11,500 |
1,228 |
3,1 16 |
2,896 |
2,693 |
3,591 |
1,540 |
1 4 |
Net present value |
3,564 |
* Some entries in this table may differ from those in Tables 7.1 or 7.2 because of rounding error.
* Some entries in this table may differ from those in Tables 7.1 or 7.2 because of rounding error.
3. What happens to NPV if the inflation rate falls from 5 percent to zero and the discount rate falls from 12 percent to 7 percent? Given that the real discount rate is almost unchanged, why does project NPV increase?
Brealey-Myers: Fundamentals of Corporate Finance, Third Edition
II. Value
7. Using Discounted Cash-Flow Analysis to Make Investment Decisions
© The McGraw-Hill Companies, 2001
218 part two Value table 7.4
Tax depreciation allowed under the Modified Accelerated Cost Recovery System (figures in percent of depreciable investment)
Year(s) |
Recovery Period Class | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
3-Year |
5-Year |
7-Year |
10-Year |
15-Year |
20-Year | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
1 |
33.33 |
20.00 |
14.29 |
10.00 |
5.00 |
3.75 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
2 |
44.45 |
32.00 |
24.49 |
18.00 |
9.50 |
7.22 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
3 |
14.81 |
19.20 |
17.49 |
14.40 |
8.55 |
6.68 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
4 |
7.41 |
11.52 |
12.49 |
11.52 |
7.70 |
6.18 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
5 |
11.52 |
8.93 |
9.22 |
6.93 |
5.71 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
6 |
5.76 |
8.93 |
7.37 |
6.23 |
5.28 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
7 |
8.93 |
6.55 |
5.90 |
4.89 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
8 |
4.45 |
6.55 |
5.90 |
4.52 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
9 |
6.55 |
5.90 |
4.46 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
10 |
6.55 |
5.90 |
4.46 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
11 |
3.29 |
5.90 |
4.46 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
12 |
5.90 |
4.46 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
13 |
5.90 |
4.46 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
14 |
5.90 |
4.46 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
15 |
5.90 |
4.46 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
16 |
2.99 |
4.46 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
17-20 |
4.46 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
21 |
1. Tax depreciation is lower in the first year because assets are assumed to be in service for 6 months. 2. Real property is depreciated straight-line over 27.5 years for residential property and 39 years for nonresidential property. Notes: 1. Tax depreciation is lower in the first year because assets are assumed to be in service for 6 months. 2. Real property is depreciated straight-line over 27.5 years for residential property and 39 years for nonresidential property. All large corporations keep two sets of books, one for stockholders and one for the Internal Revenue Service. It is common to use straight-line depreciation on the stockholder books and MACRS depreciation on the tax books. Only the tax books are relevant in capital budgeting. table 7.5 The switch from straight-line to MACRS depreciation increases the value of Blooper's depreciation tax shield from $2,523,000 to $2,583,000 (figures in thousands of dollars)
Note: Column sums subject to rounding error. Note: Column sums subject to rounding error. Brealey-Myers: I II. Value I 7. Using Discounted I I © The McGraw-Hill Fundamentals of Corporate Cash-Flow Analysis to Companies, 2001 Finance, Third Edition Make Investment Decisions chapter 7 Using Discounted Cash-Flow Analysis to Make Investment Decisions 219 y self-test 7.5 Suppose that Blooper's mining equipment could be put in the 3-year recovery period class. What is the present value of the depreciation tax shield? Confirm that the change in the value of the depreciation tax shield equals the increase in project NPV from question 1 of the spreadsheet exercises in the Excel box (pages 216-217). What to Do about Salvage Value. We assumed earlier that the mining equipment would be worthless when the magnoosium mine closed. But suppose that it can be sold for $2 million in Year 6. (The $2 million forecast salvage value recognizes inflation.) You recorded the initial $10 million investment as a negative cash flow. Now in Year 6 you have a forecast return of $2 million of that investment. That is a positive cash flow. When you sell the equipment, the IRS will check its books and see that you have already claimed depreciation of $10 million.7 So the value of your investment in Blooper's tax books will be zero. Any difference between the sale price ($2 million) and the value in the tax books (zero) is treated as a taxable gain. So your sale of the equipment will also land you with an additional tax bill in Year 6 of .35 x ($2 million - 0) = $.70 million. The extra cash flow in Year 6 is Salvage value - tax on gain = $2 million - $.70 million When discounted back to Year 0, this adds $.659 million, or $659,000, to the value of the project. How should the cash flows properly attributable to a proposed new project be calculated? Here is a checklist to bear in mind when forecasting a project's cash flows: • Discount cash flows, not profits. • Estimate the project's incremental cash flows—that is, the difference between the cash flows with the project and those without the project. • Include all indirect effects of the project, such as its impact on the sales of the firm's other products. • Include opportunity costs, such as the value of land which you could otherwise sell. • Beware of allocated overhead charges for heat, light, and so on. These may not reflect the incremental effects of the project on these costs. • Remember the investment in working capital. As sales increase, the firm may need to make additional investments in working capital and, as the project finally comes to an end, it will recover these investments. 7 The MACRS tax depreciation schedules assume zero salvage value at the end of assets' depreciable lives. For reports to shareholders, however, positive expected salvage values are often recognized. For example, Blooper's financial statements might assume that its $10 million investment in mining equipment would be worth $2 million in Year 6. In this case, the depreciation reported to shareholders would be based on the difference between investment and salvage value, that is, $8 million. Straight-line depreciation would be $1.6 million per year. Summary Brealey-Myers: Fundamentals of Corporate Finance, Third Edition II. Value 7. Using Discounted Cash-Flow Analysis to Make Investment Decisions © The McGraw-Hill Companies, 2001 220 part two Value• Do not include debt interest or the cost of repaying a loan. When calculating NPV assume that the project is financed entirely by the shareholders and that they receive all the cash flows. This isolates the investment decision from the financing decision. How can the cash flows of a project be computed from standard financial statements? Project cash flow does not equal profit. You must allow for changes in working capital as well as noncash expenses such as depreciation. Also, if you use a nominal cost of capital, consistency requires that you forecast nominal cash flows—that is, cash flows that recognize the effect of inflation. How is the company's tax bill affected by depreciation and how does this affect project value? Depreciation is not a cash flow. However, because depreciation reduces taxable income, it reduces taxes. This tax reduction is called the depreciation tax shield. Modified Accelerated Cost Recovery System (MACRS) depreciation schedules allow more of the depreciation allowance to be taken in early years than under straight-line depreciation. This increases the present value of the tax shield. How do changes in working capital affect project cash flows? Increases in net working capital such as accounts receivable or inventory are investments, and therefore use cash—that is, they reduce the net cash flow provided by the project in that period. When working capital is run down, cash is freed up, so cash flow increases. Related web Links www-ec.njit.edu/~mathis/interactive/FCCalcBase4.html A net present value calculator from Professor Roswell Mathis www.4pm.com/articles/palette.html Try the on-line demonstration here to see how good business judgment is used to formulate cash-flow projections www.irs.ustreas.gov/prod/bus_info/index.html Tax rules affecting project cash flows can be found here Key Terms opportunity cost net working capital depreciation tax shield Modified Accelerated Cost Recovery System (MACRS) straight-line depreciation Quiz 1. Cash Flows. A new project will generate sales of $74 million, costs of $42 million, and depreciation expense of $10 million in the coming year. The firm's tax rate is 35 percent. Calculate cash flow for the year using all three methods discussed in the chapter and confirm that they are equal. 2. Cash Flows. Canyon Tours showed the following components of working capital last year: Beginning End of Year Accounts receivable $24,000 $22,500 Inventory 12,000 13,000 Accounts payable 14,500 16,500 Brealey-Myers: I II. Value I 7. Using Discounted I I © The McGraw-Hill Fundamentals of Corporate Cash-Flow Analysis to Companies, 2001 Finance, Third Edition Make Investment chapter 7 Using Discounted Cash-Flow Analysis to Make Investment Decisions 221 a. What was the change in net working capital during the year? b. If sales were $36,000 and costs were $24,000, what was cash flow for the year? Ignore taxes. 3. Cash Flows. Tubby Toys estimates that its new line of rubber ducks will generate sales of $7 million, operating costs of $4 million, and a depreciation expense of $1 million. If the tax rate is 40 percent, what is the firm's operating cash flow? Show that you get the same answer using all three methods to calculate operating cash flow. 4. Cash Flows. We've emphasized that the firm should pay attention only to cash flows when assessing the net present value of proposed projects. Depreciation is a noncash expense. Why then does it matter whether we assume straight-line or MACRS depreciation when we assess project NPV? 5. Proper Cash Flows. Quick Computing currently sells 10 million computer chips each year at a price of $20 per chip. It is about to introduce a new chip, and it forecasts annual sales of 12 million of these improved chips at a price of $25 each. However, demand for the old chip will decrease, and sales of the old chip are expected to fall to 3 million per year. The old chip costs $6 each to manufacture, and the new ones will cost $8 each. What is the proper cash flow to use to evaluate the present value of the introduction of the new chip? 6. Calculating Net Income. The owner of a bicycle repair shop forecasts revenues of $160,000 a year. Variable costs will be $45,000, and rental costs for the shop are $35,000 a year. Depreciation on the repair tools will be $10,000. Prepare an income statement for the shop based on these estimates. The tax rate is 35 percent. 7. Cash Flows. Calculate the operating cash flow for the repair shop in the previous problem using all three methods suggested in the chapter: (a) net income plus depreciation; (b) cash inflow/cash outflow analysis; and (c) the depreciation tax shield approach. Confirm that all three approaches result in the same value for cash flow. 8. Cash Flows and Working Capital. A house painting business had revenues of $16,000 and expenses of $9,000. There were no depreciation expenses. However, the business reported the following changes in various components of working capital: Beginning End Accounts receivable $1,200 $4,500 Accounts payable 600 200 Calculate net cash flow for the business for this period. 9. Incremental Cash Flows. A corporation donates a valuable painting from its private collection to an art museum. Which of the following are incremental cash flows associated with the donation? a. The price the firm paid for the painting. b. The current market value of the painting. c. The deduction from income that it declares for its charitable gift. d. The reduction in taxes due to its declared tax deduction. 0 10. Operating Cash Flows. Laurel's Lawn Care, Ltd., has a new mower line that can generate revenues of $120,000 per year. Direct production costs are $40,000 and the fixed costs of maintaining the lawn mower factory are $15,000 a year. The factory originally cost $1 million and is being depreciated for tax purposes over 25 years using straight-line depreciation. Calculate the operating cash flows of the project if the firm's tax bracket is 35 percent. Brealey-Myers: II. Value 7. Using Discounted © The McGraw-Hill Fundamentals of Corporate Cash-Flow Analysis to Companies, 2001 Finance, Third Edition Make Investment 222 part two ValuePRACTICE 11. Operating Cash Flows. Talia's Tutus bought a new sewing machine for $40,000 that will be depreciated using the MACRS depreciation schedule for a 5-year recovery period. |
Was this article helpful?
The study of what it takes to be a successful entrepreneur is fascinating because to be successful requires so many qualities that at times even seem to be at odds with each other. This is a collection of 3 great guides.