E40 1 x fx D E F H K 7 8 10 12 13 14 15 16 Problem 1: Ditz Forms is considering investing in an automated egg-sorting system to increase production for international (web-based) sales of Dilte Farts products. The new syster will cost $3,000 including installation. It will be fully depreciated in 5 yrs.(straight-line) to zero and generate Siso after-tax gain at the end of the projected period (year 6). The initial working captital will be $300 and will be $500 in your one and increase each year thereafter by 5 percent. Revenues generated from the ele-sorter are expected to be $900 in year one, and increase by five percent each year. Expenses are ten peromt of revenues. Diltz Farme opportunity cost of capital is 8,5%. Using the discounted cash-flow analysis, should Diltz 9 Farms invest in the machinery? What is the NPV of the egg-sorter project? Finance Concept: When making capital investment decisions we must consider the effect of a change 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). Step 1: 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 Valse) mumber of years 19 Hint: Salvage Value for depreciation in cell 22. Cost of Asset $3,000 1. Enter values from the problem in cells E20, E21, and E22. 21 Life of Asset in Years 2. In cell E23 enter: - E20-E22 22 Salvage Value 3. In cell E24 enter: -E21/E21 [yearly depreciation) Depreciable Basis 4. Yearly depreciation should be $3,000/5 - $600. Yearly depreciation 5. Salvage Value for year 6 +150 [from problern) 18 20 5 5900 $2,100 S600 23 24 Step 2: Creating a table to calculate the After-Tax Cash Flows and NPV (In millions): YEAR: 0 3.00 3,000 3.000 300 1 3.000 2 500 2 3.000 1.00 525 3.00 1.23 551 3 OKO 600 578 3.000 150 630 606 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Initial Investment Salvage Value Working capital Change in Wk Cup Revenues Expenses Depreciation Pretax profit Tax (35%) Profit after tax CF from operations Cash Flow: CF from capital investments CF from working capital DCF + Ready Step 2: Creating a table to calculate the After-Tax Cash Flows and NPV (In millions): 3.000 ISO YEAR: 2 Initial Investment 3,000 3.X 22 2000 3,000 Salvage Value 3.000 24400 1,800 1200 600 Working capital 300 500 525 551 578 606 436 Change in Wk Cap SOO Revenues Expenses Depreciation Pretax profit Tax (35%) Profit after tax 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 5 PV of cash flow Net present value 18 Discount rate 50 Completing the spreadsheet is a simple use of Excel mathematical functions. You might want to print the instructions first 51 Cash Flow From Operations 52 1. In year zero enter the initial investment, the initial wkg capital, and the change in wkg cap (0) 2. In year cell F30) enter the new wkg cap (500), and calculate the change in wkg cap by subtracting year from year 1. In cell F31 enter:=F30-E30 3. In year two, enter year one's we carros (G30, F30*1.05] Copy the formula through year 5. Also copy chg in wkg cap formula to years 2-6. 55 4. In year one enter revenues in year two enter year one 1.05 (-F32"1.05) and copy to your 5 56 5. In year one enter expenses from problem-F32*10%) and copy across 57 6. Enter depreciation in year one from the depreciation table and copy (remember 5). 58 7. Yearly pretax profit in revenues minus expenses and depreciation. (F35-F32-F33-F34) Copy across 59 3. Calculate the tax by multiplying preta profit by the tax rate. (035) [F30-F35*35) copy across 9. Subtract the tax from the pretax profit to get the after tax protit, (F37-735-736) copy across 10. To obtain CF from operations in year 0-initial investment-ch wig cap will be a negative number 11. For years 1-5:add Profit Aner Tax to Depreciation (F37 F34). Copy across Year 6 is blank Cawang DCF 6 19 53 54 F 60 61 62 63 + B ! B D E F G H J K 2. In year 1cell F30) enter the new wkg cap (500), and calculate the change in wkg cap by subtracting year from year 1. In cell F31 enter: -F30-F30. 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 wig 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(=F32*10%) 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.38) [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-S: 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 E4l and copy across to J41. 3. Cash Flow from operations: in cell F42 enter -F38 and copy across to 342. In cell K41 enter: --K31+K29. 4. Total cash flows: in cell E43 enter =SUM(E40:542) and copy across to K43. 5. Discount rate: enter the required retum (085) in E48 6. Discount factor: in cell E44 enter: =1/((1+SES48YO) 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(EAS:K45) Some check numbers: Year 6 change in working capital --608 Change from wkg cap (K41)= 758 Total cash flows for year 5 (343) = 821 With a required return of 8.5%, should Dilt: 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 if the required return is 9%? Problem 2: our firm recently purchased an industrial machine costing 5525,000. It is classified as a seven-year property under MACRS. What are the annual preciation allowances and end-of-the-year book values for this machine? E40 1 x fx D E F H K 7 8 10 12 13 14 15 16 Problem 1: Ditz Forms is considering investing in an automated egg-sorting system to increase production for international (web-based) sales of Dilte Farts products. The new syster will cost $3,000 including installation. It will be fully depreciated in 5 yrs.(straight-line) to zero and generate Siso after-tax gain at the end of the projected period (year 6). The initial working captital will be $300 and will be $500 in your one and increase each year thereafter by 5 percent. Revenues generated from the ele-sorter are expected to be $900 in year one, and increase by five percent each year. Expenses are ten peromt of revenues. Diltz Farme opportunity cost of capital is 8,5%. Using the discounted cash-flow analysis, should Diltz 9 Farms invest in the machinery? What is the NPV of the egg-sorter project? Finance Concept: When making capital investment decisions we must consider the effect of a change 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). Step 1: 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 Valse) mumber of years 19 Hint: Salvage Value for depreciation in cell 22. Cost of Asset $3,000 1. Enter values from the problem in cells E20, E21, and E22. 21 Life of Asset in Years 2. In cell E23 enter: - E20-E22 22 Salvage Value 3. In cell E24 enter: -E21/E21 [yearly depreciation) Depreciable Basis 4. Yearly depreciation should be $3,000/5 - $600. Yearly depreciation 5. Salvage Value for year 6 +150 [from problern) 18 20 5 5900 $2,100 S600 23 24 Step 2: Creating a table to calculate the After-Tax Cash Flows and NPV (In millions): YEAR: 0 3.00 3,000 3.000 300 1 3.000 2 500 2 3.000 1.00 525 3.00 1.23 551 3 OKO 600 578 3.000 150 630 606 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Initial Investment Salvage Value Working capital Change in Wk Cup Revenues Expenses Depreciation Pretax profit Tax (35%) Profit after tax CF from operations Cash Flow: CF from capital investments CF from working capital DCF + Ready Step 2: Creating a table to calculate the After-Tax Cash Flows and NPV (In millions): 3.000 ISO YEAR: 2 Initial Investment 3,000 3.X 22 2000 3,000 Salvage Value 3.000 24400 1,800 1200 600 Working capital 300 500 525 551 578 606 436 Change in Wk Cap SOO Revenues Expenses Depreciation Pretax profit Tax (35%) Profit after tax 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 5 PV of cash flow Net present value 18 Discount rate 50 Completing the spreadsheet is a simple use of Excel mathematical functions. You might want to print the instructions first 51 Cash Flow From Operations 52 1. In year zero enter the initial investment, the initial wkg capital, and the change in wkg cap (0) 2. In year cell F30) enter the new wkg cap (500), and calculate the change in wkg cap by subtracting year from year 1. In cell F31 enter:=F30-E30 3. In year two, enter year one's we carros (G30, F30*1.05] Copy the formula through year 5. Also copy chg in wkg cap formula to years 2-6. 55 4. In year one enter revenues in year two enter year one 1.05 (-F32"1.05) and copy to your 5 56 5. In year one enter expenses from problem-F32*10%) and copy across 57 6. Enter depreciation in year one from the depreciation table and copy (remember 5). 58 7. Yearly pretax profit in revenues minus expenses and depreciation. (F35-F32-F33-F34) Copy across 59 3. Calculate the tax by multiplying preta profit by the tax rate. (035) [F30-F35*35) copy across 9. Subtract the tax from the pretax profit to get the after tax protit, (F37-735-736) copy across 10. To obtain CF from operations in year 0-initial investment-ch wig cap will be a negative number 11. For years 1-5:add Profit Aner Tax to Depreciation (F37 F34). Copy across Year 6 is blank Cawang DCF 6 19 53 54 F 60 61 62 63 + B ! B D E F G H J K 2. In year 1cell F30) enter the new wkg cap (500), and calculate the change in wkg cap by subtracting year from year 1. In cell F31 enter: -F30-F30. 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 wig 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(=F32*10%) 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.38) [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-S: 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 E4l and copy across to J41. 3. Cash Flow from operations: in cell F42 enter -F38 and copy across to 342. In cell K41 enter: --K31+K29. 4. Total cash flows: in cell E43 enter =SUM(E40:542) and copy across to K43. 5. Discount rate: enter the required retum (085) in E48 6. Discount factor: in cell E44 enter: =1/((1+SES48YO) 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(EAS:K45) Some check numbers: Year 6 change in working capital --608 Change from wkg cap (K41)= 758 Total cash flows for year 5 (343) = 821 With a required return of 8.5%, should Dilt: 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 if the required return is 9%? Problem 2: our firm recently purchased an industrial machine costing 5525,000. It is classified as a seven-year property under MACRS. What are the annual preciation allowances and end-of-the-year book values for this machine