Today is January 1, 2022; 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 (2022-2027). 100 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 2022, it will spend $5,000,000 on replacement cars (to replace badly damaged cars), $10,000,000 on preventive maintenance, and $7,000,000 on unexpected repairs. These costs are expected to increase by 5%, 3%, and 10% annually, respectively. In addition, there is the cost of running the rental locations (including cost of office space, employee compensation, utilities etc.) totaling $10,000,000 for year 2022 with an annual increase of 10%. Rental cars are expected to rent for $55 per day on average in 2022, and the per-day rental price is expected to decrease by 2% per year from 2023 onwards. Enterprise estimates a total of 1,000,000 daily rentals per year for the year 2022, with an annual decline of 1% in the number of total daily rentals from year 2023 onwards.

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 for a one-time revenue of $25,000,000 - all three of these would be one-time events. Once bought, the electric cars will incur an annual preventive maintenance cost of $5,000,000, annual unexpected repair cost of $2,000,000, and an annual replacement car purchase cost of $10,000,000 in the first year of ownership (i.e. in 2022). These three costs will decline annually by 1%, 2%, and 3% respectively from 2023 onwards. Enterprise estimates that the cost of running the rental locations ($20,000,000 in 2022) will experience an annual decline of $250,000 from year 2023 onwards. Electric cars are expected to rent for $60 per day on average. Enterprise estimates a total of 900,000 daily rentals per year for year 2022, with an annual increase of 3% in the number of total daily rentals (given the rising popularity of electric cars, and an increase in the number of superchargers available across the country) from year 2023 onwards.

Create a spreadsheet model (using MS-Excel) that clearly shows assumptions / inputs, intermediate calculations, and final result.

Perform a sensitivity analysis by creating a tornado chart covering +/- 10% change in the value of any four input variables only for the second option (replace current fleet with electric cars):

excel link, https://docs.google.com/spreadsheets/d/1-cABGGsgNkPcQ1SDHqC00ruVTYF2Cyh6/edit?usp=sharing&ouid=100502821518610241596&rtpof=true&sd=true

Inputs Keep using Gasoline-powered Cars Fleet Liquidation Revenue in 2022 No. of Daily Rentals in 2022 Annual Increase in no. of Daily Rentals Per-day Rental Price Cost of New Cars in 2022 Cost of Replacement cars in 2022 Annual Increase in Replacement Car Cost Cost of Preventive Maintenance in 2022 Annual Increase in Preventive Maintenance Cost Cost of Unexpected Repairs in 2022 Annual Increase in Unexpected Repair Cost Cost of Rental Locations in 2022 Annual Increase in Rental Location Cost Get rid of Gasoline-Powered Cars Fleet Liquidation Revenue in 2022 No. of Daily Rentals in 2022 Annual Increase in no. of Daily Rentals Per-day Rental Price Cost of New Electric Cars in 2022 Cost of Electric Car Chargers in 2022 Cost of Replacement cars in 2022 Annual Increase in Replacement Car Cost Cost of Preventive Maintenance in 2022 Annual Increase in Preventive Maintenance Cost Cost of Unexpected Repairs in 2022 Annual Increase in Unexpected Repair Cost Cost of Rental Locations in 2022 Annual Increase in Rental Location Cost for 2023 Outputs Cumulative 6-yr Cash Flow - Gasoline-powered Cars Cumulative 6-yr Cash Flow - Electric Cars Update as necessary, but no formulas Must have a formula OR leave blank Calculations Keep using Gasoline-pov 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 Get rid of Gasoline-Pover 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 2022 2022 2023 2023 2024 2024 2025 2025 2026 2026 2027 2027 Parameter Fleet Liquidation Revenue in 2022 No. of Daily Rentals in 2022 Annual Increase in no. of Daily Rentals Per-day Rental Price Cost of New Electric Cars in 2022 Cost of Electric Car Chargers in 2022 Cost of Replacement cars in 2022 Annual Increase in Replacement Car Cost Cost of Preventive Maintenance in 2022 Annual Increase in Preventive Maintenance Cost Cost of Unexpected Repairs in 2022 Annual Increase in Unexpected Repair Cost Cost of Rental Locations in 2022 Annual Increase in Rental Location Cost Current Value Change Impact of change on Delta Inputs Keep using Gasoline-powered Cars Fleet Liquidation Revenue in 2022 No. of Daily Rentals in 2022 Annual Increase in no. of Daily Rentals Per-day Rental Price Cost of New Cars in 2022 Cost of Replacement cars in 2022 Annual Increase in Replacement Car Cost Cost of Preventive Maintenance in 2022 Annual Increase in Preventive Maintenance Cost Cost of Unexpected Repairs in 2022 Annual Increase in Unexpected Repair Cost Cost of Rental Locations in 2022 Annual Increase in Rental Location Cost Get rid of Gasoline-Powered Cars Fleet Liquidation Revenue in 2022 No. of Daily Rentals in 2022 Annual Increase in no. of Daily Rentals Per-day Rental Price Cost of New Electric Cars in 2022 Cost of Electric Car Chargers in 2022 Cost of Replacement cars in 2022 Annual Increase in Replacement Car Cost Cost of Preventive Maintenance in 2022 Annual Increase in Preventive Maintenance Cost Cost of Unexpected Repairs in 2022 Annual Increase in Unexpected Repair Cost Cost of Rental Locations in 2022 Annual Increase in Rental Location Cost for 2023 Outputs Cumulative 6-yr Cash Flow - Gasoline-powered Cars Cumulative 6-yr Cash Flow - Electric Cars Update as necessary, but no formulas Must have a formula OR leave blank Calculations Keep using Gasoline-pov 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 Get rid of Gasoline-Pover 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 2022 2022 2023 2023 2024 2024 2025 2025 2026 2026 2027 2027 Parameter Fleet Liquidation Revenue in 2022 No. of Daily Rentals in 2022 Annual Increase in no. of Daily Rentals Per-day Rental Price Cost of New Electric Cars in 2022 Cost of Electric Car Chargers in 2022 Cost of Replacement cars in 2022 Annual Increase in Replacement Car Cost Cost of Preventive Maintenance in 2022 Annual Increase in Preventive Maintenance Cost Cost of Unexpected Repairs in 2022 Annual Increase in Unexpected Repair Cost Cost of Rental Locations in 2022 Annual Increase in Rental Location Cost Current Value Change Impact of change on Delta