USE @RISK EXCEL, part e is the question I'm having trouble with

FX, Inc. is a volume manufacturer of high technology automotive mirrors (including cell link and voice activation). FX is looking to expand their operations to add a second product line capable of producing 1.5 Million units per year. The equipment investment cost for this new operation is $30 Million. The project falls under a 7 year MACRS class life and the company estimates that the salvage value will be $3 Million at the end of the 6 year project. The average selling price for each mirror is $100 per unit. The annual expected sales shown below: Year Volume (000) 2021 600 2022 750 2023 1000 2024 1200 2025 1200 2026 1200 The material cost for each mirror is $25 (with 25 % of the material imported from Canada and 40% from Mexico). The labor to produce each mirror is $20 with additional variable cost of manufacturing at $15 per unit. The fixed cost of manufacturing operations is $15 Million per year. FX maintains 1 month of raw materials and 1 month of WIP and finished goods combined to balance overall automotive demand. Assume that FX has a federal tax rate of 25% and a state tax rate of 5%. Also assume that FX uses a MARR of 15% for all economic analyses. a) What is the NPV of the investment? b) If the company could borrow $15 Million of the $30 Million needed at 10%, how would this change the NPV calculation? c) If inflation is estimated at 2% and the pricing is locked for the six year period, how does your NPV change? Assume that the company borrowed $10 Million of the $30 Million needed at 10%. d) Perform sensitive analysis on +/- 10% changes in volume, selling price, and material cost from part c. Graph your answers e). If the material costs can fluctuate over the next 6 years relative to exchange rates similar to the last 6 years, and the inflation rate is similar to the last 6 years for base material, labor and burden, put together a model that reflects these possibilities and run a simulation for the NPV for the project. (I will let you define the distributions and input parameters based on your review of the data of inflation and exchange rate fluctuations. Everyone's models will be somewhat different but justify your assumptions). FX, Inc. is a volume manufacturer of high technology automotive mirrors (including cell link and voice activation). FX is looking to expand their operations to add a second product line capable of producing 1.5 Million units per year. The equipment investment cost for this new operation is $30 Million. The project falls under a 7 year MACRS class life and the company estimates that the salvage value will be $3 Million at the end of the 6 year project. The average selling price for each mirror is $100 per unit. The annual expected sales shown below: Year Volume (000) 2021 600 2022 750 2023 1000 2024 1200 2025 1200 2026 1200 The material cost for each mirror is $25 (with 25 % of the material imported from Canada and 40% from Mexico). The labor to produce each mirror is $20 with additional variable cost of manufacturing at $15 per unit. The fixed cost of manufacturing operations is $15 Million per year. FX maintains 1 month of raw materials and 1 month of WIP and finished goods combined to balance overall automotive demand. Assume that FX has a federal tax rate of 25% and a state tax rate of 5%. Also assume that FX uses a MARR of 15% for all economic analyses. a) What is the NPV of the investment? b) If the company could borrow $15 Million of the $30 Million needed at 10%, how would this change the NPV calculation? c) If inflation is estimated at 2% and the pricing is locked for the six year period, how does your NPV change? Assume that the company borrowed $10 Million of the $30 Million needed at 10%. d) Perform sensitive analysis on +/- 10% changes in volume, selling price, and material cost from part c. Graph your answers e). If the material costs can fluctuate over the next 6 years relative to exchange rates similar to the last 6 years, and the inflation rate is similar to the last 6 years for base material, labor and burden, put together a model that reflects these possibilities and run a simulation for the NPV for the project. (I will let you define the distributions and input parameters based on your review of the data of inflation and exchange rate fluctuations. Everyone's models will be somewhat different but justify your assumptions)