Complete model with info given on excel, inputs are given

Show complete model and also show all formulas used please

No hard coding

Today is January 1, 2020; and Enterprise (a leading rental car company) is thinking about replacing its entire fleet of gasoline-powered cars with electric cars. Enterprise has asked you to create an excel model that can help it make a decision that maximizes their NET PRESENT VALUE of the cash flow over the next six years (2020-2025). 45 points If Enterprise decides to keep its current fleet: It estimates that the net present value of the cash flow over the next six years (2020-2025) will be -$1,214,632. If Enterprise decides to replace its current fleet with electric cars: Enterprise will need to spend $100,000,000 on buying new electric cars, $5,000,000 on the installation of electric car chargers, and dispose of its current fleet of gasoline-powered cars. It estimates that it can sell off its current fleet of gasoline-powered cars for $25,000,000. Once bought, the electric cars will incur an annual preventive maintenance cost of $2,000,000, annual unexpected repair cost of $5,000,000, and an annual replacement car purchase cost of $11,000,000 in the first year of ownership. These three costs will decline annually by 3%, 2%, and 1% respectively since wider adoption of electric cars is expected to drive down the cost of maintenance and replacement purchases. Since electric cars are easier to handle and maintain, Enterprise estimates that the cost of running the rental locations will drop from $20,000,000 in 2020 by 5% per year from year 2021 onwards. Electric cars rent for $55 per day on average, and Enterprise estimates a total of 900,000 daily rentals for year 2020 with an annual increase of 2% in the number of total daily rentals (given the rising popularity of these cars). Annual discount rate for NPV calculation is 10%. a. Create a spreadsheet model (using MS-Excel) that clearly shows assumptions / inputs, intermediate calculations, and final result. b. Perform a sensitivity analysis for the "Replace current fleet with electric cars" scenario by creating a tornado chart covering the impact of +/- 10% change in the values of the following three variables on the value of NPV of six-year cash flows: a) Fleet Liquidation Revenue in 2020 b) Per-day Rental Price c) Cost of Rental Locations in 2020 | Keep using Gasoline-powered Cars NPV of Six-Year Cash Flow - Gasoline Cars 2020 2021 2022 2023 2024 2025 Get rid of Gasoline Cars Fleet Liquidation Revenue in 2020 No. of Daily Rentals in 2020 Annual Increase in no. of Daily Rentals Per-day Rental Price Get rid of Gasoline-Powered Cars Number of Daily Rentals Rental Revenue Fleet Liquidation Revenue Total Revenue ILL Cost of New Electric Cars in 2020 Cost of Electric Car Chargers in 2020 Cost of Replacement cars in 2020 Annual Increase in Replacement Car Cost Cost of Preventive Maintenance in 2020 Annual Increase in Preventive Maintenance Cost Cost of Unexpected Repairs in 2020 Annual Increase in Unexpected Repair Cost Cost of Rental Locations in 2020 Annual Increase in Rental Location Cost Cost of New Electric Cars Cost of Electric Car Chargers Cost of Replacement Cars Cost of Preventive Maintenance Cost of Unexpected Repairs Cost of Rental Locations Total Cost Net Annual Cash Flow Outputs NPV of Six-Year Cash Flow - Gasoline Cars NPV of six-Year Cash Flow - Electric Cars Sheet1 Sheet2 + Today is January 1, 2020; and Enterprise (a leading rental car company) is thinking about replacing its entire fleet of gasoline-powered cars with electric cars. Enterprise has asked you to create an excel model that can help it make a decision that maximizes their NET PRESENT VALUE of the cash flow over the next six years (2020-2025). 45 points If Enterprise decides to keep its current fleet: It estimates that the net present value of the cash flow over the next six years (2020-2025) will be -$1,214,632. If Enterprise decides to replace its current fleet with electric cars: Enterprise will need to spend $100,000,000 on buying new electric cars, $5,000,000 on the installation of electric car chargers, and dispose of its current fleet of gasoline-powered cars. It estimates that it can sell off its current fleet of gasoline-powered cars for $25,000,000. Once bought, the electric cars will incur an annual preventive maintenance cost of $2,000,000, annual unexpected repair cost of $5,000,000, and an annual replacement car purchase cost of $11,000,000 in the first year of ownership. These three costs will decline annually by 3%, 2%, and 1% respectively since wider adoption of electric cars is expected to drive down the cost of maintenance and replacement purchases. Since electric cars are easier to handle and maintain, Enterprise estimates that the cost of running the rental locations will drop from $20,000,000 in 2020 by 5% per year from year 2021 onwards. Electric cars rent for $55 per day on average, and Enterprise estimates a total of 900,000 daily rentals for year 2020 with an annual increase of 2% in the number of total daily rentals (given the rising popularity of these cars). Annual discount rate for NPV calculation is 10%. a. Create a spreadsheet model (using MS-Excel) that clearly shows assumptions / inputs, intermediate calculations, and final result. b. Perform a sensitivity analysis for the "Replace current fleet with electric cars" scenario by creating a tornado chart covering the impact of +/- 10% change in the values of the following three variables on the value of NPV of six-year cash flows: a) Fleet Liquidation Revenue in 2020 b) Per-day Rental Price c) Cost of Rental Locations in 2020 | Keep using Gasoline-powered Cars NPV of Six-Year Cash Flow - Gasoline Cars 2020 2021 2022 2023 2024 2025 Get rid of Gasoline Cars Fleet Liquidation Revenue in 2020 No. of Daily Rentals in 2020 Annual Increase in no. of Daily Rentals Per-day Rental Price Get rid of Gasoline-Powered Cars Number of Daily Rentals Rental Revenue Fleet Liquidation Revenue Total Revenue ILL Cost of New Electric Cars in 2020 Cost of Electric Car Chargers in 2020 Cost of Replacement cars in 2020 Annual Increase in Replacement Car Cost Cost of Preventive Maintenance in 2020 Annual Increase in Preventive Maintenance Cost Cost of Unexpected Repairs in 2020 Annual Increase in Unexpected Repair Cost Cost of Rental Locations in 2020 Annual Increase in Rental Location Cost Cost of New Electric Cars Cost of Electric Car Chargers Cost of Replacement Cars Cost of Preventive Maintenance Cost of Unexpected Repairs Cost of Rental Locations Total Cost Net Annual Cash Flow Outputs NPV of Six-Year Cash Flow - Gasoline Cars NPV of six-Year Cash Flow - Electric Cars Sheet1 Sheet2 +