Below is the (very detailed!) question. I also included my work but something seems very wrong and I can't figure out where the issue is.

Below is the (very detailed!) question. I also included my work but something seems very wrong and I can't figure out where the issue is. I think I've included all the necessary information but please let me know if something is missing.

image text in transcribed

After looking at the projections of the HomeNet project, you decide that they are not realistic. It is unlikely that sales will be constant over the four-year life of the project. Furthermore, other companies are likely to offer competing products, so the assumption that the sales price will remain constant is also likely to be optimistic. Finally, as production ramps up, you anticipate lower per unit production costs resulting from economies of scale. Therefore, you decide to redo the projections under the following assumptions: - Sales of 50,000 units in year 1 increasing by 50,000 units per year over the life of the project, a year 1 sales price of $260/unit, decreasing by 10% annually and a year 1 cost of $120/unit decreasing by 20% annually. . In addition, new tax laws allow 100% bonus depreciation (all the depreciation expense occurs when the asset is put into use, in this case in Year O). Each year 20% of sales comes from customers who would have purchased an existing Cisco router for $100/unit and that this router costs $60/unit to manufacture. The existing router's price will decrease by 10% annually and its cost will decrease by 20% annually as well. The used equipment that Cisco purchased for $7.5 in Year O is sold in Year 5 for a salvage value of $0.5 million. Other assumptions remain the same, including lost rental and 4% inflation in SG&A expenses. a. Keeping the other assumptions that underlie Table 8.3 the same, recalculate unlevered net income (that is, reproduce Table 8.3 under the new assumptions. Please note that cannibalization and lost rent must be included. (1 point) b. Calculate HomeNet's net working capital requirements under the new assumptions. (1 point) C. Calculate HomeNet's FCF under the new assumptions. (1 point) d. Should the project be taken or rejected based on NPV rule under the assumptions? Please show me your calculation. Simply answering yes or no will earn 0 credit. (1 point) e. Should the project be still taken if the unit sales is 20% lower than originally expected under the new assumptions? Please show me your calculation. Simply answering yes or no will earn 0 credit. (1 point) Please show your work and put your answers in the blank Excel sheet attached here. If your process is right but calculations are wrong, partial credit will be given. Your assignment should be submitted in the Excel sheet on Blackboard. Table 8.3 Spreadsheet Calculation of HomeNet's Free Cash Flow (Including Cannibalization and Lost Rent) 3 4 5 23,500 23,500 (9,500) (9,500) 14,000 14,000 (3,000) (3,000) Year 0 1 2 Incremental Earnings Forecast ($000s) 1 Sales 23,500 23,500 2 Cost of Goods Sold (9,500) (9,500) 3 Gross Profit 14,000 14,000 4 Selling, General, and Administrative (3,000) (3,000) 5 Research and Development (15,000) 6 Depreciation (1,500) (1,500) 7 EBIT (15,000) 9,500 9,500 8 Income Tax at 20% 3,000 (1,900) (1,900) 9 Unlevered Net Income (12,000) 7,600 7,600 Free Cash Flow ($000s) 10 Plus: Depreciation 1,500 1,500 11 Less: Capital Expenditures (7,500) 12 Less: Increases in NWC (2,100) 13 Free Cash Flow (19,500) 7,000 9,100 (1,500) (1,500) (1,500) 9,500 9,500 (1,500) (1,900) (1,900) 300 7,600 7,600 (1,200) 1,500 1,500 1,500 9,100 2,100 9,100 2,400 B D E F H Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 50,000 260 (120) 20% 100 (60) 100,000 234 (96) 20% 90 (48) 150,000 211 (77) 20% 81 (38) 200,000 190 (61) 20% 73 (31) 250,000 171 (49) 20% 66 (25) (3,000,000) (3,120,000) (3,244,800) (3,374,592) (3,509,576) (15,000,000) (7,500,000) 0% 20% 100% 20% 15% 15% 12% 0% 20% 15% 15% 0% 20% 15% 15% 0% 20% 15% 15% 0% 20% 15% 15% 1 Key Assumptions 2 Revenue and Costs 3 Homenet Units Sold 4 HomeNet Ave. Price/Unit 5 HomeNet Cost/Unit 6 Cannibalization Rate 7 Old product Ave. Price/Unit 8 Old Product Cost/Unit 9 10 Operating Expenses 11 SG&A (including lost rent) 12 R&D (expensed) 13 Lab Equipment (capitalized) 14 15 Other Assumptions 16 Depreciation schedule 17 Corporate tax rate 18 Receivables (% of sales) 19 Payables (% of COGS) 20 Cost of Capital 21 22 23 Sales 24 COGS 25 Gross Profit 26 SG&A 27 R&D 28 Depreciation (less $.500m salvage value) 29 EBIT 30 Income tax @ 20% 31 Unlevered NI 32 Operating Cash Flows 33 34 Capital Expenditures 35 36 Working Capital 37 Change in Working Capital 38 Cash Flow from Working Capital 39 40 41 Free Cash Flow 42 Plus: Depreciation 43 Less: Capital Expenditures 44 Less: Increases in NWC 45 Free Cash Flow 46 47 NPV 48 IRR 12,000,000 (5,400,000) 6,600,000 (3,000,000) 21,600,000 (8,640,000) 12,960,000 (3,120,000) 29,160,000 (10,368,000) 18,792,000 (3,244,800) 34,992,000 (11,059,200) 23,932,800 (3,374,592) (15,000,000) (7,000,000) (22,000,000) 4,400,000 (17,600,000) (10,600,000) 10,200,000 2,040,000 12,240,000 12,240,000 22,800,000 4,560,000 27,360,000 27,360,000 34,339,200 6,867,840 41,207,040 41,207,040 44,491,008 8,898,202 53,389,210 53,389,210 (7,500,000) 0 0 0 0 0 990,000 990,000 990,000 1,944,000 954,000 (954,000) 2,818,800 874,800 (874,800) 3,589,920 771,120 (771,120) (3,589,920) 3,589,920 (7,000,000) (7,500,000) 0 0 0 0 990,000 13,230,000 0 3,589,920 3,589,920 (18,100,000) 26,406,000 40,332,240 52,618,090 78,947,658 117%