Please help me figure out what I'm doing wrong. I'm not sure how to do the very first four (i.e bookvalue...) and towards the end I'm unsure how to work IRR. Any help would be great!

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 $2233 including installation. It will be fully depreciated in 5 yrs.(straight-line) to zero and generate $106 after-tax gain at the end of the projected period (year 6). The initial working captital will be $370 and will be $522 in year one and increase each year thereafter by 5 percent.

Assume that at year 0, there is no change in working capital. Revenues generated from the egg-sorter are expected to be $760 in year one, and increase by five percent each year. Expenses are ten percent of revenues. Diltz Farms' cost of capital is 6.5% Using the discounted cash-flow analysis, should Diltz Farms invest in the machinery? What is the NPV of the egg-sorter project?"

fully depreciated in 5 yrs.(straight-line) to zero and generate $106 after-tax gain at the end of the projected period (year 6)

cost $2233 including installation

initial working captital will be $370

and will be $522 in year one and increase each year thereafter by 5 percent.

cost of capital is 6.5%

NOTE: I37, C18, & C37 ARE CORRECT

A B C D M N O 1 E F G H Discounted Cash-Flow Analysis Estimating depreciation of an asset, calculating cash flows, and estimating NPV. Problem OWN Diltz Farms is considering investing in an automated ege-sorting system to increase production for international (web-based) sales of Diltz Fams products. The new system will cost $2233 including installation. It will be fully depreciated in 5 yrs. (straight-line) to zero and generate 5106 after-tax gain at the end of the projected period (year 6). The initial working captital will 8 te $370 and will be $552 in year we and increase cach year thietcalier luy 5 percent. Assume that at you there is no change m working capital Revenues generated from the egy-souterraine UTIC 9 expected to be $760 in year one, and increase by five percent each year. Expenses are ten percent of revenues. Dilt: Farm' cost of capital is 6.5%. Using the discounted cash-flow Analysis 10 should Diltz Farms invest in the machinery? What is the NPV of the eg2-sorter project? 11 12 13 14 Cost of the Asset S2,233 15 Life of the Assel in Years Identify, the values from the questions for cells CH4, C15, C16. C17, C19. C20 and 21. 16 Book Value of the Asset afles 5 years 0 17 Depreciable Basis 2127 Depreciable Basis Cost of the Asset - Book Value at the end of its life 18 Yearly depreciation 4226 Sometimes, if we do not have information about Book Value of an asset at the end of its useful life, we 19 After tax Salvage Value in year 6 106 miglat use Salvage Value in those cases to find depreciation 20 Cost of capital 6.5% 21 Tax rate 22 Year: 0 3 23 luitial Investment S 2.233.000 In cell C23 enter: =C14 24 Salvage Value $ 106.000 In cell 124 cutcr: C19 25 Working capital 370.000 S 552.000 S 579.600 $ 608.580 $639.009 $ 670.959 In cell C25 enter the initial working capital from the problem 26 Change in Wk Cap S S 182.000'S 27.600 $ 28.980 $ 30.429 $ 31.950$ (670.959) In cell D25 enter the working capital for year 1 frust the problet 27 Revenues S 760.000 S 798.000 $ 837.900 $879.795 $ 923 785 In cell E25 enter : =1.05*D25 and then drag it till H25 28 Expenses S 76.000'S 79.800 $ 83.790 $ 87.980 $ 92.378 In cell C26 enter 0 29 Depreciation 42260% 42260% 42260% 42260% In cell D26 enter: =125-C25 and then drag it till 126 30 Prelax mult 29560% 33151% 36922% 40881% In cell D27 enter the revenue for year 1 from the problem 31 Tax (35%) 0% 0% 0% 0% 0% In cell E27 enter: =D27*1.05 and then drag it till H27 32 Profit after tax 26140% 29560% 33151% 36922% 40881% In ocll D28 cuter. D27*0.1 33 Cash Flow In cell E28 enter: =D28 1.05 and then dragit till H28 34 CT from capital investments S (2.233.000) 11 In cell D29 cuter. SCS18 and then drag it till 1129 35 CF from working capital 0.00% -18200.00% -2760.00% -2898.00%-3042.90% -3195 05% 77695.95% In cell D30 enter:=D27-D28-D29 and then dragit till H30 36 CF fiesta operations 684.000 718.200 $ 754.110 $791.816 $ 831.106 M In cell D31 ener: =SC$21*130 mud then dragit till H31 37 Total caslillows 2.233.000 SO2.000 690.600 2.130 $761.38 999 156 36959 In cell D32 enter: =D30-D31 and then drag it till H32 38 Discount factor S 1.000'S 0.939'S 0.882 $ 0.828 $ 0.777 $ 0.730$ $ 0.685 In cell C34 cater: -C23 39 PV of cash flow S S 12.233.000) 471.362 'S 608.874 $ 600.298 $591.843 $ 583.507$ 532.477 In cell C35 enter: --C26 and drag it till H35 40 In cell 135 cutcr: -126/124 S 4226097 26140%, ML S Tax (35%) Profit after tax 0% 29560% 0% 33151% 0% 36922% 0% 40881% $ CF from capital investments CF from working capital CF from operations Total cash flows Discount factor PV of cash flow 0% 26140% Cash Flow: (2,233.000) 0.00% -18200.00% $ 684.000 $ (2.233.000$ 502.000$ 1.000 $ 0.939 $ (2,233.000 $ 471.362 $ -2760.00% -2898.00% -3042.90% -3195.05% 77695.95% 718.200 $ 754.110 $791.816 $ 831.406 690.600$ 725.130 $761.387 $ 799.456$ 776.959 0.882$ 0.828 $ 0.777 $ 0.730 $ 0.685 608.874 $ 600.298 $591.843 $ 583.507 $ 532.477 $ $ In cell E27 enter: =D27*1.05 and then drag it till H27 In cell D28 enter: = D27*0.1 In cell E28 enter: =D28*1.05 and then drag it till H28 In cell D29 enter: =$C$18 and then drag it till H29 In cell D30 enter: =D27-D28-D29 and then drag it till H30 In cell D31 enter: =$C$21*D30 and then drag it till H31 In cell D32 enter: =D30-D31 and then drag it till H32 In cell C34 enter: =-C23 In cell C35 enter: =-C26 and drag it till H35 In cell 135 enter: =-126+124 In cell D36 enter: =D32+D29 and drag it till H36 In cell D37 enter: =sum(C34,C35,C36) and drag it till 137 In cell C38 enter: =1/(1+$C$20) C22 and then drag it till H38 In cell C39 enter: =C37*C38 and then drag it till 139 In cell C42 enter: =sum(C39:139) Net present value using Discount factor IRR using formula 1155.36 If you directly use NPV formula, you will not get points. If you use GoalSeek for IRR, you will not get points. Score for problem 1 A B C D M N O 1 E F G H Discounted Cash-Flow Analysis Estimating depreciation of an asset, calculating cash flows, and estimating NPV. Problem OWN Diltz Farms is considering investing in an automated ege-sorting system to increase production for international (web-based) sales of Diltz Fams products. The new system will cost $2233 including installation. It will be fully depreciated in 5 yrs. (straight-line) to zero and generate 5106 after-tax gain at the end of the projected period (year 6). The initial working captital will 8 te $370 and will be $552 in year we and increase cach year thietcalier luy 5 percent. Assume that at you there is no change m working capital Revenues generated from the egy-souterraine UTIC 9 expected to be $760 in year one, and increase by five percent each year. Expenses are ten percent of revenues. Dilt: Farm' cost of capital is 6.5%. Using the discounted cash-flow Analysis 10 should Diltz Farms invest in the machinery? What is the NPV of the eg2-sorter project? 11 12 13 14 Cost of the Asset S2,233 15 Life of the Assel in Years Identify, the values from the questions for cells CH4, C15, C16. C17, C19. C20 and 21. 16 Book Value of the Asset afles 5 years 0 17 Depreciable Basis 2127 Depreciable Basis Cost of the Asset - Book Value at the end of its life 18 Yearly depreciation 4226 Sometimes, if we do not have information about Book Value of an asset at the end of its useful life, we 19 After tax Salvage Value in year 6 106 miglat use Salvage Value in those cases to find depreciation 20 Cost of capital 6.5% 21 Tax rate 22 Year: 0 3 23 luitial Investment S 2.233.000 In cell C23 enter: =C14 24 Salvage Value $ 106.000 In cell 124 cutcr: C19 25 Working capital 370.000 S 552.000 S 579.600 $ 608.580 $639.009 $ 670.959 In cell C25 enter the initial working capital from the problem 26 Change in Wk Cap S S 182.000'S 27.600 $ 28.980 $ 30.429 $ 31.950$ (670.959) In cell D25 enter the working capital for year 1 frust the problet 27 Revenues S 760.000 S 798.000 $ 837.900 $879.795 $ 923 785 In cell E25 enter : =1.05*D25 and then drag it till H25 28 Expenses S 76.000'S 79.800 $ 83.790 $ 87.980 $ 92.378 In cell C26 enter 0 29 Depreciation 42260% 42260% 42260% 42260% In cell D26 enter: =125-C25 and then drag it till 126 30 Prelax mult 29560% 33151% 36922% 40881% In cell D27 enter the revenue for year 1 from the problem 31 Tax (35%) 0% 0% 0% 0% 0% In cell E27 enter: =D27*1.05 and then drag it till H27 32 Profit after tax 26140% 29560% 33151% 36922% 40881% In ocll D28 cuter. D27*0.1 33 Cash Flow In cell E28 enter: =D28 1.05 and then dragit till H28 34 CT from capital investments S (2.233.000) 11 In cell D29 cuter. SCS18 and then drag it till 1129 35 CF from working capital 0.00% -18200.00% -2760.00% -2898.00%-3042.90% -3195 05% 77695.95% In cell D30 enter:=D27-D28-D29 and then dragit till H30 36 CF fiesta operations 684.000 718.200 $ 754.110 $791.816 $ 831.106 M In cell D31 ener: =SC$21*130 mud then dragit till H31 37 Total caslillows 2.233.000 SO2.000 690.600 2.130 $761.38 999 156 36959 In cell D32 enter: =D30-D31 and then drag it till H32 38 Discount factor S 1.000'S 0.939'S 0.882 $ 0.828 $ 0.777 $ 0.730$ $ 0.685 In cell C34 cater: -C23 39 PV of cash flow S S 12.233.000) 471.362 'S 608.874 $ 600.298 $591.843 $ 583.507$ 532.477 In cell C35 enter: --C26 and drag it till H35 40 In cell 135 cutcr: -126/124 S 4226097 26140%, ML S Tax (35%) Profit after tax 0% 29560% 0% 33151% 0% 36922% 0% 40881% $ CF from capital investments CF from working capital CF from operations Total cash flows Discount factor PV of cash flow 0% 26140% Cash Flow: (2,233.000) 0.00% -18200.00% $ 684.000 $ (2.233.000$ 502.000$ 1.000 $ 0.939 $ (2,233.000 $ 471.362 $ -2760.00% -2898.00% -3042.90% -3195.05% 77695.95% 718.200 $ 754.110 $791.816 $ 831.406 690.600$ 725.130 $761.387 $ 799.456$ 776.959 0.882$ 0.828 $ 0.777 $ 0.730 $ 0.685 608.874 $ 600.298 $591.843 $ 583.507 $ 532.477 $ $ In cell E27 enter: =D27*1.05 and then drag it till H27 In cell D28 enter: = D27*0.1 In cell E28 enter: =D28*1.05 and then drag it till H28 In cell D29 enter: =$C$18 and then drag it till H29 In cell D30 enter: =D27-D28-D29 and then drag it till H30 In cell D31 enter: =$C$21*D30 and then drag it till H31 In cell D32 enter: =D30-D31 and then drag it till H32 In cell C34 enter: =-C23 In cell C35 enter: =-C26 and drag it till H35 In cell 135 enter: =-126+124 In cell D36 enter: =D32+D29 and drag it till H36 In cell D37 enter: =sum(C34,C35,C36) and drag it till 137 In cell C38 enter: =1/(1+$C$20) C22 and then drag it till H38 In cell C39 enter: =C37*C38 and then drag it till 139 In cell C42 enter: =sum(C39:139) Net present value using Discount factor IRR using formula 1155.36 If you directly use NPV formula, you will not get points. If you use GoalSeek for IRR, you will not get points. Score for problem 1