According to the data given in the written paragraphs, need to fill out both tables with formulas and how the final number was obtained even if its already given and then create a tornado chart covering + and - 10% change in the value of each input variable for the second option, I believe there are 14 inputs.

A B D E F G H 1 J K L 1 Change 20% Current Value 20% Decrease 20% Increase Impact of change on X X at -20% X at +20% 2 Parameter Base X Delta 3 4 5 6 7 8 9 10 Update Must have a formula Insert / delete rows or columns as needed 11 12 13 14 15 16 17 18 A B D E F G H 1 J L 2022 2023 2024 2025 2026 2021 1,000,000 Calculations Keep using Gasoline-powered Cars Number of Daily Rentals Rental Revenue Fleet Liquidation Revenue Total Revenue Cost of New Cars Cost of Replacement Cars Cost of Preventive Maintenance Cost of Unexpected Repairs Cost of Rental Locations Total Cost Net Annual Cash Flow Cumulative Cash Flow 2021 2022 2023 2024 2025 2026 4 Inputs 5 Keep using Gasoline-powered Cars 6 Fleet Liquidation Revenue in 2021 7 No. of Daily Rentals in 2021 8 Annual Increase in no. of Daily Rentals 9 9 Per-day Rental Price 10 11 Cost of New Cars in 2021 12 Cost of Replacement cars in 2021 13 Annual Increase in Replacement Car Cost 14 Cost of Preventive Maintenance in 2021 15 Annual increase in Preventive Maintenance Cost 16 Cost of Unexpected Repairs in 2021 12 17 Annual Increase in Unexpected Repair Cost 18 Cost of Rental Locations in 2021 19 Annual Increase in Rental Location Cost 20 21 Get rid of Gasoline-Powered Cars 22 Fleet Liquidation Revenue in 2021 23 No. of Daily Rentals in 2021 23 24 Annual Increase in no. of Daily Rentals 25 Per-day Rental Price 26 27 Cost of New Electric Cars in 2021 28 Cost of Electric Car Chargers in 2021 29 Cost of Replacement cars in 2021 30 Annual Increase in Replacement Car Cost 24 31 Cost of Preventive Maintenance in 2021 32 Annual Increase in Preventive Maintenance Cost 33 Cost of Unexpected Repairs in 2021 34 | Annual Increase in Unexpected Repair Cost 35 Cost of Rental Locations in 2021 36 Annual Increase in Rental Location Cost for 2022 37 20 38 Outputs 39 Cumulative Cash Flow - Gasoline-powered Cars 40 Cumulative Cash Flow - Electric Cars 41 42 Get rid of Gasoline-Powered Cars Number of Daily Rentals Rental Revenue Fleet Liquidation Revenue Total Revenue 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 Cumulative Cash Flow Today is January 1, 2021; 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 cumulative cash flow (ignoring the time value of money) over the coming six years (2021-2026). 45 points Rental car business involves three annual expenses: cost of replacement cars (to replace badly damaged cars), cost of preventive maintenance, and cost of unexpected repairs. If Enterprise decides today to keep its current fleet for the next six years: Enterprise estimates that in year 2021, it will spend $5,000,000 on replacement cars (to replace badly damaged cars), $15,000,000 on preventive maintenance, and $8,000,000 on unexpected repairs. These costs are expected to increase by 10%, 5%, and 3% annually, respectively. In addition, there is the cost of running the rental locations (including cost of office space, employee compensation, utilities etc.) totaling $12,000,000 for year 2021 with an annual increase of $1,000,000. Rental cars rent for $50 per day on average, and Enterprise estimates a total of 1,000,000 daily rentals per year for the year 2021 with an annual decline of 2% in the number of total daily rentals. If Enterprise decides today to immediately replace its entire current fleet with electric cars: Enterprise will need to spend $100,000,000 on buying an entire fleet of new electric cars, spend $5,000,000 on the installation of electric car chargers, and dispose of its current fleet of gasoline- powered cars (all three would be one-time events) for a one-time revenue of $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 $10,000,000 in the first year of ownership (.e. in 2021). These three costs will decline annually by 1%, 2%, and 3% respectively since wider adoption of electric cars is expected to drive down the cost of maintenance and replacement purchases. Once the staff learns how to handle electric car rentals, Enterprise estimates that the cost of running the rental locations ($20,000,000 in 2021) will experience a one- time drop of 5% in year 2022 and then neither increase or decrease in subsequent years. Electric cars rent for $55 per day on average, and Enterprise estimates a total of 900,000 daily rentals for year 2021 with an annual increase of 2% in the number of total daily rentals (given the rising popularity of electric cars). Tasks Needed: 1) Create a spreadsheet model (using MS-Excel) that clearly shows assumptions / inputs, intermediate calculations, and final result. 2) Perform a sensitivity analysis by creating a tornado chart covering +/- 10% change in the value of each & every input variable only for the second option (replace current fleet with electric cars): A B D E F G H 1 J K L 1 Change 20% Current Value 20% Decrease 20% Increase Impact of change on X X at -20% X at +20% 2 Parameter Base X Delta 3 4 5 6 7 8 9 10 Update Must have a formula Insert / delete rows or columns as needed 11 12 13 14 15 16 17 18 A B D E F G H 1 J L 2022 2023 2024 2025 2026 2021 1,000,000 Calculations Keep using Gasoline-powered Cars Number of Daily Rentals Rental Revenue Fleet Liquidation Revenue Total Revenue Cost of New Cars Cost of Replacement Cars Cost of Preventive Maintenance Cost of Unexpected Repairs Cost of Rental Locations Total Cost Net Annual Cash Flow Cumulative Cash Flow 2021 2022 2023 2024 2025 2026 4 Inputs 5 Keep using Gasoline-powered Cars 6 Fleet Liquidation Revenue in 2021 7 No. of Daily Rentals in 2021 8 Annual Increase in no. of Daily Rentals 9 9 Per-day Rental Price 10 11 Cost of New Cars in 2021 12 Cost of Replacement cars in 2021 13 Annual Increase in Replacement Car Cost 14 Cost of Preventive Maintenance in 2021 15 Annual increase in Preventive Maintenance Cost 16 Cost of Unexpected Repairs in 2021 12 17 Annual Increase in Unexpected Repair Cost 18 Cost of Rental Locations in 2021 19 Annual Increase in Rental Location Cost 20 21 Get rid of Gasoline-Powered Cars 22 Fleet Liquidation Revenue in 2021 23 No. of Daily Rentals in 2021 23 24 Annual Increase in no. of Daily Rentals 25 Per-day Rental Price 26 27 Cost of New Electric Cars in 2021 28 Cost of Electric Car Chargers in 2021 29 Cost of Replacement cars in 2021 30 Annual Increase in Replacement Car Cost 24 31 Cost of Preventive Maintenance in 2021 32 Annual Increase in Preventive Maintenance Cost 33 Cost of Unexpected Repairs in 2021 34 | Annual Increase in Unexpected Repair Cost 35 Cost of Rental Locations in 2021 36 Annual Increase in Rental Location Cost for 2022 37 20 38 Outputs 39 Cumulative Cash Flow - Gasoline-powered Cars 40 Cumulative Cash Flow - Electric Cars 41 42 Get rid of Gasoline-Powered Cars Number of Daily Rentals Rental Revenue Fleet Liquidation Revenue Total Revenue 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 Cumulative Cash Flow Today is January 1, 2021; 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 cumulative cash flow (ignoring the time value of money) over the coming six years (2021-2026). 45 points Rental car business involves three annual expenses: cost of replacement cars (to replace badly damaged cars), cost of preventive maintenance, and cost of unexpected repairs. If Enterprise decides today to keep its current fleet for the next six years: Enterprise estimates that in year 2021, it will spend $5,000,000 on replacement cars (to replace badly damaged cars), $15,000,000 on preventive maintenance, and $8,000,000 on unexpected repairs. These costs are expected to increase by 10%, 5%, and 3% annually, respectively. In addition, there is the cost of running the rental locations (including cost of office space, employee compensation, utilities etc.) totaling $12,000,000 for year 2021 with an annual increase of $1,000,000. Rental cars rent for $50 per day on average, and Enterprise estimates a total of 1,000,000 daily rentals per year for the year 2021 with an annual decline of 2% in the number of total daily rentals. If Enterprise decides today to immediately replace its entire current fleet with electric cars: Enterprise will need to spend $100,000,000 on buying an entire fleet of new electric cars, spend $5,000,000 on the installation of electric car chargers, and dispose of its current fleet of gasoline- powered cars (all three would be one-time events) for a one-time revenue of $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 $10,000,000 in the first year of ownership (.e. in 2021). These three costs will decline annually by 1%, 2%, and 3% respectively since wider adoption of electric cars is expected to drive down the cost of maintenance and replacement purchases. Once the staff learns how to handle electric car rentals, Enterprise estimates that the cost of running the rental locations ($20,000,000 in 2021) will experience a one- time drop of 5% in year 2022 and then neither increase or decrease in subsequent years. Electric cars rent for $55 per day on average, and Enterprise estimates a total of 900,000 daily rentals for year 2021 with an annual increase of 2% in the number of total daily rentals (given the rising popularity of electric cars). Tasks Needed: 1) Create a spreadsheet model (using MS-Excel) that clearly shows assumptions / inputs, intermediate calculations, and final result. 2) Perform a sensitivity analysis by creating a tornado chart covering +/- 10% change in the value of each & every input variable only for the second option (replace current fleet with electric cars)