PLEASE HELP CALCULATE D,E, F, G IN EXCEL AND SHOW WORK!

A bicycle manufacturer currently produces 218,000 units a year and expects output levels to remain steady in the future. It buys chains from an outside supplier at a price of $1.90 a chain. The plant manager believes that it would be cheaper to make these chains rather than buy them. Direct in-house production costs are estimated to be only $1.50 per chain. The necessary machinery would cost $250,000 and would be obsolete after ten years. This investment could be depreciated to zero for tax purposes using a ten-year straight-line depreciation schedule. The plant manager estimates that the operation would require $35,000 of inventory and other working capital upfront (year 0), but argues that this sum can be ignored since it is recoverable at the end of the ten years. Expected proceeds from scrapping the machinery after ten years are $18,750. If the company pays tax at a rate of 20% and the opportunity cost of capital is 15%, what is the net present value of the decision to produce the chains in-house instead of purchasing them from the supplier? Project the annual free cash flows (FCF) of buying the chains. The annual free cash flows for years 1 to 10 of buying the chains is $ (Round to the nearest dollar. Enter a free cash outflow as a negative number.) 218,000 1.90 Information Given Number of Units Produced Cost Per Chain (Outsourced) $ Cost Per Chain (In-house) $ Cost for Machinery $ Machinery Life (Years) Inventory, Upfront Costs $ Scaps after year 10 of machine $ Tax Rate Opportunity Cost of Capital 1.50 (240,000) 10 (35,000) 18,750 20% 15% A) If we purchase from outside compnay (NOTE: Expenses are deductable) Cash Flow per year (FCF) $ (331,360.00) From Year 1 to 10 B) NPV = PV of all cash flows at 15% discount rate NPV $ (1,663,019.17) C) Initial Cash Flow From Producing Chains Purchase Price of Machine $ Operation/ Inventory Costs $ Total $ (240,000) (35,000) (275,000) Cash Flow Calculation Porduction Cost $ Depreciation (Straight Line = Cost/Life $ Profit Before Taxes $ Taxes (20%) $ Net Income $ Depreciation $ Cash Flow Calculation $ (327,000.00 (24,000) (351,000.00 70,200.00 (421,200.00) 24,000 (397,200.00 D) Cash Flow for Year 1 to Year 9 $ (397,200.00) E) Cash Flow in Year 10 = Annual Cash Flow + After Tax Salvage Value + Recovery of Work Capital Cash Flow in Year 10 F) NPV = PV of all Cashflows - Initial Cash Out Flow NPV G) Difference