A B Enter your name in this box D E F Brian Barker G H K 3 4 5 8 - Tutorial 9 Discounted Cash-Flow Analysis Estimating depreciation of an asset, calculating cash flows, and estimating NPV. Problem 1: Diltz Farms is considering investing in an automated egg-sorting system to increase production for international (web-based) sales of Diltz Farms' products. The new system will cost $3,000 including installation. It will be fully depreciated in 5 yrs.(straight- line) to zero and generate $150 after-tax gain at the end of the projected period (year 6). The initial working captital will be $300 and will be $500 in year one and increase each year thereafter by 5 percent. Revenues generated from the egg-sorter are expected to be $900 in year one, and increase by five percent each year. Expenses are ten percent of revenues. Diltz Farms' opportunity cost of capital is 8.5%. Using the discounted cash-flow analysis, should Diltz Farms invest in the machinery? What is the NPV of the egg- 9 sorter project? Finance Concept: When making capital investment decisions we must consider the effect of a change 10 11 12 13 14 15 Step 1: 16 18 in cash flows. The Excel spreadsheet is extremely useful in calculating cash flows allowing us to compare different assets or depreciation methods (what-if analysis). Find the accounting yearly depreciation with the straight line method: Depreciable Basis = Cost of the Asset - Salvage Value SL (Straight-Line) Depreciation = (Initial Cost - Salvage Value) / number of years Hint: Salvage Value for depreciation =0 in cell E22. P22222 20222 Cost of Asset $3,000 1. Enter values from the problem in cells E20, E21, and E22. Life of Asset in Years 5 2. In cell E23 enter: E20-E22 Salvage Value $0 3. In cell E24 enter: =E23/E21 [yearly depreciation] Depreciable Basis $3,000 4. Yearly depreciation should be $3,000/5 = $600. Yearly depreciation $600 Step 2: 5. Salvage Value for year 6 = +150 [from problem] Creating a table to calculate the After-Tax Cash Flows and NPV (in millions): Initial Investment YEAR: 0 1 3,000 2 3 4 5 6 Salvage Value 150 30 Working capital 300 500 525 551 579 608 31 Change in Wk Cap 0 200 25 26 28 29 -608 32 Revenues 900 945 992 1,042 1,094 33 Expenses 90 95 99 104 109 34 Depreciation 600 600 600 600 600 35 Pretax profit 210 251 293 338 385 36 Tax (35%) 74 88 103 118 135 37 Profit after tax 137 163 190 219 250 38 CF from operations -3,300 737 763 790 819 850 39 Cash Flow: 40 CF from capital investments -3,000 41 CF from working capital 0 -200 -25 -26 -28 -29 758 42 CF from operations 737 763 790 819 850 43 Total cash flows -3,000 537 738 764 792 821 758 44 Discount factor 1.000 0.922 0.849 0.783 0.722 0.665 0.613 45 PV of cash flow -3,000 494 627 598 571 546 464 46 Net present value 301.44 48 Discount rate 0.085 49 50 Completing the spreadsheet is a simple use of Excel mathematical functions. You might want to print the instructions first. A 51 52 B Cash Flow From Operations D E F H 1. In year zero enter the initial investment, the initial wkg capital, and the change in wkg cap (0) K 2. In year 1[cell F30] enter the new wkg cap [500], and calculate the change in wkg cap by subtracting year 0 from year 1. In cell F31 enter: F30-E30. 3. In year two, enter year one's wkg cap*1.05 [G30; =F30*1.05]. Copy the formula through year 5. Also copy chg in wkg cap formula to years 2-6. 4. In year one enter revenues. In year two enter year one*1.05 [=F32*1.05] and copy to year 5. 5. In year one enter expenses from problem; in year two multiply year one by 1.1 and copy across. 6. Enter depreciation in year one from the depreciation table and copy (remember $). 7. Yearly pretax profit is revenues minus expenses and depreciation. [F35=F32-F33-F34] Copy across. 8. Calculate the tax by multiplying pretax profit by the tax rate. (0.35) [F36= F35*.35] copy across. 9. Subtract the tax from the pretax profit to get the after tax profit. [F37=F35-F36] copy across. 10. To obtain CF from operations: in year 0: = -initial investment-ch wkg cap [wil be a negative number] 11. For years 1-5: add Profit After Tax to Depreciation (F37+F34). Copy across. Year 6 is blank. Calculating the NPV 1. Cash Flow from capital investments: enter -E28 in cell E40. 2. Cash Flow from working capital: enter -E31in cell E41 and copy across to J41. 3. Cash Flow from operations: in cell F42 enter =F38 and copy across to J42. In cell K41 enter: =-K31+K29. 4. Total cash flows: in cell E43 enter =SUM(E40:E42) and copy across to K43. 5. Discount rate: enter the required return (.085) in E48 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 2.8574% 81 82 What is the NPV if the required return is 9%? -74.172 83 84 85 86 Problem 2: 6. Discount factor: in cell E44 enter: =1/((1+$E$48)^0) and copy across. Change the exponent in yrs 1-6. 7. PV of cash flow: in cell E45 enter =E44*E43 and copy across. 8. Net present value: in cell E46 enter = SUM(E45:K45) Some check numbers: Year 6 change in working capital = -608 Change from wkg cap (K41) = 758 Total cash flows for year 5 (J43) = 821 -22.955 With a required return 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? No 101 | |a| c|||||||ge| Your firm recently purchased an industrial machine costing $525,000. It is classified as a seven-year property under MACRS. What 87 are the annual depreciation allowances and end-of-the-year book values for this machine? 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. Year Beginning Book Value 7 Year MACRS Depreciation Allowance Ending Book Value 1 525,000 14.29% 75,023 449,978 2 449,978 24.49% 128,573 321,405 3 321,405 17.49% 91,823 229,583 4 229,583 12.49% 65,573 164,010 5 164,010 8.93% 46,883 117,128 6 117,128 8.93% 46,883 70.245 7 70,245 8.93% 46,883 23,363 100 8 23,363 4.45% 23,363 A B 102 103 Solution: 104 105 106 107 108 109 110 E F H 1 Enter the Beginning Book Value (cost of the machine) in cell E93. 2 Calculate the remaining Beginning Book Values: enter =193 in cell E94 and copy down the column. 3 Calculate the Depreciation Allowance by multiplying $E$93 (Beg Bk V) * F93 (MACRS %). 4 Find the Ending Book Value by subtracting the depreciation from the Beginning Book Value. 5 Copy all your formulas down the columns. 6 Year 8 Ending Book Value should equal zero, and beg bk val = depreciation in year 8.. K TIT 112 Problem 3: Peggy's Peaches has developed a new product, the Bruiseless Peach, which always stays peachy fresh. Peggy's paid $85,000 to a |marketing firm to survey the bruiseless peach market. The potential sales were estimated at $250,000 per year. New equipment will be necessary to carefully handle the peaches. It cost $200,000 and will have fixed costs of $70,000 per year, and variable costs will be 25% of sales. The new anti-bruise machine will be depreciated straight-line for the four years of it's life and is the only initial cost for the new "Peggy's Peaches, the UnBruised Ones". Peggy's pays 34% tax and has a required return of 8%. Calculate the NPV and 113 IRR. 115 116 117 Solution: Enter numbers and formulas to solve this problem. Use the Excel NPV and IRR functions to solve NPV and IRR.. Step 1. Find the Net Income for years 1-4. 118 Depreciation = (Initial cost - Salvage Value)/ years Assume Salvage Value = 0. [119] 120 Net Income Year 1-4 121 Sales from problem 122 Variable Costs 25% of Sales 123 Fixed Costs 124 Depreciation 125 EBIT 126 Taxes 127 Net Income 128 129 Operating Cash Flows from problem from H18 EBIT = Sales-costs-dep Taxes EBIT * tax rate NI EBIT-taxes 130 131 EBIT + Depreciation 132 - Taxes 133 After-Tax Cash Flow 134 135 136 137 138 139 140 141 Years 0 1 2 3 4 CF After-Tax Cash Flows Cost of machine as a negative # Equal Cash Flows from cell F133 142 143 Required Return B G K T 2 Enter your name in this box ===> 3 4 5 - Tutorial 9 Discounted Cash-Flow Analysis 6 T 8 Estimating depreciation of an asset, calculating cash flows, and estimating NPV. Problem 1: Diltz Farms is considering investing in an automated egg-sorting system to increase production for international (web-based) sales of Diltz Farms' products. The new system will cost $3,000 including installation. It will be fully depreciated in 5 yrs.(straight- line) to zero and generate $150 after-tax gain at the end of the projected period (year 6). The initial working captital will be $300 and will be $500 in year one and increase each year thereafter by 5 percent. Revenues generated from the egg-sorter are expected to be $900 in year one, and increase by five percent each year. Expenses are ten percent of revenues. Diltz Farms' opportunity cost of capital is 8.5%. Using the discounted cash-flow analysis, should Diltz Farms invest in the machinery? What is the NPV of the egg- 9 sorter project? Finance Concept: When making capital investment decisions we must consider the effect of a change 10 11 12 13 14 15 Step 1: 16 18 19 20 21 22 23 24 in cash flows. The Excel spreadsheet is extremely useful in calculating cash flows allowing us to compare different assets or depreciation methods (what-if analysis). Find the accounting yearly depreciation with the straight line method: Depreciable Basis = Cost of the Asset - Salvage Value SL (Straight-Line) Depreciation = (Initial Cost - Salvage Value) / number of years Cost of Asset Life of Asset in Years Salvage Value Hint: Salvage Value for depreciation =0 in cell E22. 1. Enter values from the problem in cells E20, E21, and E22. 2. In cell E23 enter: = E20-E22 3. In cell E24 enter: =E23/E21 [yearly depreciation] Depreciable Basis Yearly depreciation 4. Yearly depreciation should be $3,000/5 = $600. 5. Salvage Value for year 6 = +150 [from problem] Creating a table to calculate the After-Tax Cash Flows and NPV (in millions): 25 Step 2: 20 27 28 Initial Investment 29 Salvage Value 30 Working capital 31 Change in Wk Cap 32 Revenues 33 Expenses 34 Depreciation 35 Pretax profit 36 Tax (35%) 37 Profit after tax 38 CF from operations 39 Cash Flow: 40 CF from capital investments 41 CF from working capital 42 CF from operations 43 Total cash flows 44 Discount factor 45 PV of cash flow 46 48 YEAR: 0 1 2 3 4 5 6 Gitz Net present value Discount rate 50 Completing the spreadsheet is a simple use of Excel mathematical functions. You might want to print the instructions first. A B K 60 58 6 5886888 61 62 63 64 65 66 67 Cash Flow From Operations 1. In year zero enter the initial investment, the initial wkg capital, and the change in wkg cap (0) 2. In year 1[cell F30] enter the new wkg cap [500], and calculate the change in wkg cap by subtracting year 0 from year 1. In cell F31 enter: F30-E30. 3. In year two, enter year one's wkg cap*1.05 [G30; =F30*1.05]. Copy the formula through year 5. Also in wkg cap formula to years 2-6. 4. In year one enter revenues. In year two enter year one*1.05 [=F32*1.05] and copy to year 5. 5. In year one enter expenses from problem; in year two multiply year one by 1.1 and copy across. 6. Enter depreciation in year one from the depreciation table and copy (remember $). 7. Yearly pretax profit is revenues minus expenses and depreciation. [F35-F32-F33-F34] Copy across. 8. Calculate the tax by multiplying pretax profit by the tax rate. (0.35) [F36= F35*.35] copy across. 9. Subtract the tax from the pretax profit to get the after tax profit. [F37=F35-F36] copy across. 10. To obtain CF from operations: in year 0: -initial investment-ch wkg cap [wil be a negative number] 11. For years 1-5: add Profit After Tax to Depreciation (F37+F34). Copy across. Year 6 is blank. Calculating the NPV = 1. Cash Flow from capital investments: enter -E28 in cell E40. 2. Cash Flow from working capital: enter -E31 in cell E41 and copy across to J41. copy chg 3. Cash Flow from operations: in cell F42 enter =F38 and copy across to J42. In cell K41 enter: =-K31+K29. 4. Total cash flows: in cell E43 enter =SUM(E40:E42) and copy across to K43. 68 5. Discount rate: enter the required return (.085) in E48 69 70 6. Discount factor: in cell E44 enter: =1/((1+$E$48) 0) and copy across. Change the exponent in 7. PV of cash flow: in cell E45 enter E44*E43 and copy across. yrs 1-6. 71 72 73 74 75 76 8. Net present value: in cell E46 enter = SUM(E45:K45) Some check numbers: Year 6 change in working capital = -608 Change from wkg cap (K41) = 758 Total cash flows for year 5 (J43) = 821 77 78 79 With a required return of 8.5%, should Diltz Farms go ahead with the new egg-sorter? What is the NPV of the egg-sorter project? 80 What is the IRR of the project? 81 82 What is the NPV if the required return is 9%? 83 84 85 86 Problem 2: Your firm recently purchased an industrial machine costing $525,000. It is classified as a seven-year property under MACRS. What 87 are the annual depreciation allowances and end-of-the-year book values for this machine? 88 89 90 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. Beginning 7 Year 92 Year Book Value MACRS Depreciation Allowance Ending Book Value 93 1 14.29% 94 2 24.49% 95 3 17.49% 96 4 12.49% 97 5 8.93% 98 6 8.93% 99 7 8.93% 100 8 4.45% 101 B 102 103 Solution: 104 105 106 107 108 1 Enter the Beginning Book Value (cost of the machine) in cell E93. 2 Calculate the remaining Beginning Book Values: enter =193 in cell E94 and copy down the column. 3 Calculate the Depreciation Allowance by multiplying $E$93 (Beg Bk V) * F93 (MACRS %). 4 Find the Ending Book Value by subtracting the depreciation from the Beginning Book Value. 5 Copy all your formulas down the columns. 6 Year 8 Ending Book Value should equal zero, and beg bk val depreciation in year 8.. K 109 110 TTT 112 Problem 3: Peggy's Peaches has developed a new product, the Bruiseless Peach, which always stays peachy fresh. Peggy's paid $85,000 to a marketing firm to survey the bruiseless peach market. The potential sales were estimated at $250,000 per year. New equipment will be necessary to carefully handle the peaches. It cost $200,000 and will have fixed costs of $70,000 per year, and variable costs will be 25% of sales. The new anti-bruise machine will be depreciated straight-line for the four years of it's life and is the only initial cost for the new "Peggy's Peaches, the UnBruised Ones". Peggy's pays 34% tax and has a required return of 8%. Calculate the NPV and 113 IRR. 115 116 Solution: Enter numbers and formulas to solve this problem. Use the Excel NPV and IRR functions to solve NPV and IRR.. Step 1. Find the Net Income for years 1-4. 117 118 119 120 121 Sales 122 Depreciation = (Initial cost - Salvage Value)/ years Assume Salvage Value = 0. Net Income Year 1-4 from problem Variable Costs 123 Fixed Costs 124 Depreciation 125 EBIT 126 Taxes 127 Net Income 128 129 Operating Cash Flows 25% of Sales from problem from H18 = EBIT Sales-costs-dep Taxes = EBIT tax rate NI EBIT taxes 130 131 132 - Taxes 133 EBIT + Depreciation After-Tax Cash Flow 134 135 After-Tax Cash Flows 136 137 138 139 Years 0 1 2 CF Cost of machine as a negative # Equal Cash Flows from cell F133 140 3 141 4 142 143 144 145 Required Return Net Present Value Internal Rate of Return