please include cell references

G H C The Shome Corporation is considering a new project that involves the introduction of a new product. The firm in in the 34 percent marginal tax bracket and has a 15 perceut required rate of return or discount rate for new investments. This project is expected to last five years, and then, because this is somewhat of a fad project, it will be terminated. Given the following information, determine the net cash flows associated with the project and the project's NPV, profitability index, and internal rate of returm. Apply the appropriate decision criteria. Cost of new plamt and equipement: $6.900,000 Shipping and installation costs: Sales price per unit: $250/unit in Years 1-4, $200 unit in Year 5 Variable cost per unit: $130 unit Annual fixed costs: $300,000 Working capital requirements: There will be an initial working capital requirement of $100,000 just to get production started For cach year, the total investment in net working capital will be equal to 10 percent of the dollar value of sales for that year. Thus, the investment in working capital will increase during Years I through 3, then decrease in Year 4. Finally, all working capital is liquidated at the termisation of the project at the end of Year 5. The depreciation method: Use the simplified straight-line method over five years. It is assumed that the plant and equipment will have no salvage value after five years S 100,000 Complete the steps below using cell references to given data or previous calculations. Is some cases, a simple cell reference is all you need. To copy/paste a formala acros a row or down a columa, an absolate cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that fanction. Do not type is umerical data into a cell or fanction. Instead, make a reference to the cell in which the data is found. Make your compatations only in the green cells higblighted below. Is all cases, anless otherwise directed, use the earliest appearance of the data in your fermalas, usually the Ghven Data section. Given data: Initial capital expenditire Shipping and installation costs Life of the initial expenditure Marginal tax rate Discount rate Net working capital (% of Sales) Net working capital investment (0) Fised costs per year (56,900,000.00) (S100,000.00 34.00% 15.00% 10.00%% 12 (6100,000.00) (S300,000.00e) Sales price (1-4) Sales price (5) Variable cost of peoduct $250.00 5200.00 ($130.00) Year 0 Year 1 Year 2 Year 3 Usits sold Revenses Vaiable cos Year 4 Year 5 100,000 $25,000,000.00 (513,000,000.0 s0,000 538,00 (510,400,000.00) 120,000 530,000,000.00 70,000 S17,500,000.00 (59,100,000 0e) 70,000 S14.000.000.00 12-24 (S15,600,000.00) (59, .00 NT Enable Editing es from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View PROTECTED V A1 G H D C Sales price (5) Variable cost of product $200.00 15 ($130.00) 16 17 Year 5 Year 2 Year 3 Year 4 Year 0 Year 1 10 100,000 120,000 70,000 70,000 $14,000,000.00 (S9,100,000.00) Units sold 80,000 19 $17,500,000.00 $25,000,000.00 $30,000,000.00 ($15,600,000.00) (S300,000.00) $14,100,000.00 $20.000,000,00 Revemes 20 21 ($10,400,000.00) (S9,100,000.00) Variable costs ($13,000,000.00) Fixed costs ($300,000.00) (S300,000.00) $11,700,000.00 ($300,000.00) ($300,000.00) 22 Gross profit Depreciation Net opearting income Income tases $9,300,000,00 $8,100,000.00 S4,600,000.00 23 (S1,400,000,00) $7.900.000,.00 ($1,400,000,00) S6,700,000.00 ($2,278,000.00) 24 (S1,400,000.00) (S1,400,000.00) ($1,400,000.00) 25 $12,700,000.00 ($4,318,000.00) s10,300,000.00 $3,200,000.00 ($1,088,000.00) ($2,686,000.00) 26 (S3,502.000.00) Net income Cash flow 23 S5,214.000,00 S6.798.000,00 $8,382.000,00 S4,422,000.00 $2,112,000.00 $3,512,000.00 28 $6,614,000.00 S8,198,000,00 $9,782,000.00 $5,822.000,00 29 Additional net working capital Capital expenditure 31 32 Free cash flow NPV Profitability Index IRR 40 Requirements 1 Start Excel. n cells D30 13e do the following 41 In celn D30, by usire a cell reference, determine the new projccts additional net weeking capital for Year 0 In cell E30, by using cell references, calculate the sew projects additional net wworking capital for Year 1. 1 cell F30, bry using relative and sbsohute cell neferences, calkulate the new peojersadditical set working capital for Year 2 Copy the fomla hom cell F30 across colemns G-1. In cell 130, by using cell referesces, calculate the sew peojects additional set working capital for Year 5 12-24 Seak