I think I am doing something wrong, help!

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-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 Value) / number of years Hint: Salvage Value for depreciation =0 in cell E22. Cost of Asset $3,000 1. Enter values from the problem in cells E20, E21, and E22 Life of Asset in Years 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 I $600 5. Salvage Value for year 6 = +150 [from problem] 5 Step 2: Creating a table to calculate the After-Tax Cash Flows and NPV (in millions): O 3,000 1 2 3 300 500 608 525 25 945 579 28 200 29 -608 900 551 26 992 109 600 1,042 90 99 120 1,094 132 6002 362 600 6001 600 210 246 322 74 86 283 99 184 113 YEAR: Initial Investment Salvage Value Working capital Change in Wk Cap Revenues Expenses Depreciation Pretax profit Tax (35%) 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 127 137 160 209 235 -3,000 737 760 784 809 935 -3.000 0 -25 -26 -28 -29 758 -200 737 537 1 | 537 760 735 835 821 -3,000 758 784 758 1 593 809 782 1 564 624 546 -2,765 563.906 465 Discount rate 0.085 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-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 Value) / number of years Hint: Salvage Value for depreciation =0 in cell E22. Cost of Asset $3,000 1. Enter values from the problem in cells E20, E21, and E22 Life of Asset in Years 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 I $600 5. Salvage Value for year 6 = +150 [from problem] 5 Step 2: Creating a table to calculate the After-Tax Cash Flows and NPV (in millions): O 3,000 1 2 3 300 500 608 525 25 945 579 28 200 29 -608 900 551 26 992 109 600 1,042 90 99 120 1,094 132 6002 362 600 6001 600 210 246 322 74 86 283 99 184 113 YEAR: Initial Investment Salvage Value Working capital Change in Wk Cap Revenues Expenses Depreciation Pretax profit Tax (35%) 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 127 137 160 209 235 -3,000 737 760 784 809 935 -3.000 0 -25 -26 -28 -29 758 -200 737 537 1 | 537 760 735 835 821 -3,000 758 784 758 1 593 809 782 1 564 624 546 -2,765 563.906 465 Discount rate 0.085