H G K F E D A B 3 7 8 Estimating depreciation of an asset, calculating cash flows, and estimating NPV. Problem: Dilz Farm is considering investing in an automated egg-sorting system so increase production for international (web-based) sales of Diltz Farms' products. The new system will cost $3,000 including installation. It will be fully deprecated in 5yrs. (straight-line) to zero and generate $150 after-tax pain at the end of the projected period (yet). The initial working captial will be $300 and will be $500 in your one and increase each year thereafter by 5 percent. Revenues generated from the egg-sorte e expected to be 5900 in yok one, and increase by five percent each yeur. Expenses are ten percent of revenues. Diltz Farmes opportunity cost of capital is 8.5%. Using the discounted cash-flow analysis, should Die 9 Farms invest in the machinery? What is the NPV of the eggsorter project? 10 11 Finance Concept: When making capital investment decisions we est consider the effect of a change 12 in cash flow. The Excel spreadsheet is extremely useful in calculating cash flows allowing us to compare different assets or depreciation methods (what-ifanalysis) Step 1: Find the accounting yearly depreciation with the straat line method: 16 Depreciable Basis-Cost of the Asset-Salvage Value SL (Straight-Line) Depreciation - Ininal Cost - Salvage Vale/smber of years 19 Hint Salvage Value for depreciation=1 in cell E22 20 Cost of Asset 1. Enter values from the problem in cells E20, E21, and E22. Life of Asset in Years 2. In cel E21 E20-22 22 Salvage Value 3. In cell E24 enter =23/E21 yeatly depreciation) 23 Depreciable Basis 4. Yearly depreciation should be $3,000/5 - 5600 24 Yearly depreciation S. Salvage Value for year 6 - +150 [from problem 13 14 15 18 21 Step 2: Creating a table to calculate the After-Tax Cash Flows and NPV (in millions): YEAR: 25 20 27 28 29 30 31 32 33 34 Initial Investment Salvage Value Working capital Change in Wk Cap Revenues Expenses Depreciation DCF + Ready M H F G E D A B C SI (Straight-Line) Deprecation (Initial Cost-Salvage Vale) number of years Hint: Salvage Valse for depreciation in cell E22. Cost of Asset 1. Hinter values from the problem in cells E20, E21, and E22 Life of Asset in Years 2. In cell E23 enter: - E20-E22 Salvage Value 3. In cell E24 eter: -E23E21 (yearly depreciation) Depreciable Basis 4. Yearly depreciation should be $3,000/5 - 5600 Yearly depreciation 5. Salvage Value for year 6 = +150 (from probleen) Step 2: Creating a table to calculate the After-Tax Cash Flows and NPV (In milions): YEAR: 2 3 4 Initial Investment Salvage Value Working capital Change in Wk Cap Revenues Expenses Depreciation Pretax profit Tax (35%) Profit after tax 3 CF from operations Cash Flow: CF from capital investments 1 CF from working capital 2 CF from operations 3 Total cash flows 4 Discount factor 15 PV of cash flow 36 Net present value 38 Discount rate 50 Completing the spreadsheet is a simple use of Excel mathematical function. You might want to print the instructions first 51 Cash Flow From Operasion DCF Review View Data Formulas Page Layout Number Wrap Text A e Insert Xat Copy 10 Times New... $ - % Merge & Center B 1 U- Format L M x x G H F E D B A Profit after tax CF from operations Cash Flow: CF from capital investments CF from working capital CF from operations Total cash flows Discount factor PV of cash flow Net present value Discount rate Completing the spreadsheet is a simple use of Excel mathematical functions. You might want to print the instructions first. Cash Flow From Operations 1. In year zero ter the initial investment, the initial wkg capital and the change in wag cap (0) 2. In your cell F30 enter the new wkg cap (500), and calculate the change in wkg cap by subtracting year from year 1. In cell F31 ter-F30-F30. 3. In year two, enter year one's wkg cap 1.05 (G30, F30*1.05). Copy the formula through year 5. Ako copy chgin wig cap formula to years 2-6. 4. In year one mer revenues In year two enter year on 1.05 (-F32*1.05) and copy to year 5. 5. In year one enter expenses from probleml=F32.10%) and copy across 6. Enter depreciation in year one from the depreciation table and copy (remember 5). 7. Yearly pretat profit is revenues minus expenses and depreciation. (F35=F32-F33-F34) Copy across. 8. Calculate the tax by multiplying pretak profit by the tax rate (0.35) [F36EF35* 35) copy across 0 9. Subtract the tax from the pretax profit to get the after tax profit (F37-F35-F36) copy across 1 10. To obtain CF from operations in year initial investment.ch wg cap will be a negative number 52 11. For years 1-5 add Profit After Tax to Depreciation (F32-F34) Copy across Year 6 is blank 53 Calculating the NPV 1. Cash Flow from capital investments her E28 in cell 40. 65 2. Cash Flow from working capitalenter - Elin cell 4land copy across to 1413 66 3. Cash Flow from operations in cell F42 enter-35 and copy across 10 342. In cell 41 anter:-- 31+K29. 67 4. Total cash flows: in cell 43 te SUM(E40 342) and copy across to K43. 68 5. Discount rate: enter the required retum (085) in 148 69 6. Discount factor in cell E44 enter-ACHSE$48) 0) and copy across Change the exponent in yrs 16. 70 7. IV of cash flow in cell E45 enter-144"E43 and copy Cross DCF + F Review View Data Formulas Page Layout General Wrap Text 10 Times New... A- A Home Insert X Cut Copy Paste Format $ % Marge & Centar - B 1 U 95 4 x fx K F G H E D B Calculating the NPV 1. Cash Flow from capital investments: enter-E28 in cell E40. 2. Cash Flow from working capital: enter-E3Iin cell E4land copy across to J41. 3. Cash Flow from operations in cell F42 enter F38 and copy across to J42. In cell K41 enter: 31+K 29. 4. Total cash flows: in cell E43 enter SUM(E40:142) and copy across to K43, 5. Discount rate: enter the required return (085) in 548 6. Discount factor in cell E44 enter: =1/(1+$E$480) and copy across Change the exponent in yrs 1-6. 7. PV of cash flow: in cell E45 enter -E44E43 and copy across 8. Net present value in cell E46 enter - SUM(E4S:K45) 3 4 35 56 37 58 89 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 Some check numbers Year 6 change in working capital --608 Change from wkg cap (K41) - 758 Total cash flows for year 5 (143) - 821 With a required retum of 8.5%, should Diltz Farms go ahead with the new egg-sorter? What is the NPV of the egg-sorter project? What is the IRR of the project? What is the NPV ir the required return is 947 Problem 2: Your firm recently purchased an industrial machine costing 5525,000. It is classified as a seven-year property under MACRS. What are the annual 87 depreciation allowances and end-of-the-year book values for this machine? 88 89 Finance Concept: For tax purposes, the depreciation expense is computed under MACRS, which was enacted as part of the Tax Reform Act of 1986. The depreciation is larger at the beginning 90 Beginning Book Value Ending Book Valie Year Depreciation Allowance 92 93 94 95 1 2 3 7 Year MACRS 14.29% 24.49% 17.49% DCF Ready