Question

Create a retain/replace analysis in EXCEL for a model 3 tesla and a model 3 BMW

1. Compute the difference in fuel costs:

• Assume that you drive 12,000 miles/year equally split b/w highway and city driving

• Assume the average price of gas is $3.00/gallon

• Use EXCEL to compute the fuel cost for three years in your “current” car

• Use EXCEL to compute the fuel cost for three years in the “new” car

• Use EXCEL to compute the difference in the fuel cost over three years b/w the cars

3. Decide whether to retain or replace: Using EXCEL as much as possible, (and considering fuel costs as the only quantitative costs) show whether you should retain your current gas powered car, or purchase the new fuel-efficient car. Assume you’ve owned your current car for 3 years, have driven 36,000 miles in it and will continue to drive 12,000 miles/year equally split b/w highway and city driving. Further, assume you will drive whichever car you choose for the next 3 years.

4. Address qualitative factors, report your decision, list sources of info in a TEXT BOX

Your grade will be based on:  

Part 2: 2 pts (Overall format & ease of reading: 1 pt, Proper use of formulas, etc. in EXCEL: 1 pt)  

Part 3: 2 pts (Overall format & ease of reading: 1 pt, Proper use of formulas, etc. in EXCEL: 1 pt)  

Part 4: 2 pts (Decision consistent w/ results, address qualitative factors, etc.)

Answer 1

Please check the excel file including formulas....

RETAIN/REPLACE ANALYSIS BMW AND TELSA CARS
CAR NAME	Telsa	BMW	difference
Total price of vehicle:	27000	42750	=C3-B3
Model	3	3	=C4-B4
Selling price after ownership:	18000	29000	=C6-B6
Car miles per gallon in city	31	23
Car miles per gallon in Highway	40	34
Price of a gallon of fuel:	3	3
Gas cost/year:	=(B14*B10/B8)+(B15*B10/B9)	=(C14*C10/C8)+(C15*C10/C9)	=C11-B11
Maintenance costs per year	850	300	=C12-B12
Insurance costs/year:	750	990	=C13-B13
Miles driven per year in city	3500	3500
Miles driven per year in Highway	8500	8500
Years to hold the vehicle before selling:	10	10
Total 3 years of fuel cost	=B16*B11	=C16*C11	=C18-B18
Total cost of ownership	=(B11*B16)+(B12*B16)+(B16*B13)	=(C11*C16)+(C12*C16)+(C16*C13)	=C20-B20
Total cost per year of car ownership:	=B20/B16	=C20/C16	=C22-B22
Total cost of ownership after selling car:	=B20-B6	=C20-C6	=C24-B24
Factors
Total costs of ownership is less campared to my old car telsa
After selling old car will get only 18000.So my of cost ownership is higher than the selling price.

Result of the comparison is attached below

RETAIN/REPLACE ANALYSIS BMW AND TELSA CARS
CAR NAME	Telsa	BMW	difference
Total price of vehicle:	$27,000.00	$42,750.00	15,750.00
Model	3	3	0.00
Selling price after ownership:	$18,000.00	$29,000.00	11,000.00
Car miles per gallon in city	31	23
Car miles per gallon in Highway	40	34
Price of a gallon of fuel:	$3.00	$3.00
Gas cost/year:	$976.21	$1,206.52	230.31
Maintenance costs per year	$850.00	$300.00	-550.00
Insurance costs/year:	$750.00	$990.00	240.00
Miles driven per year in city	3500	3500
Miles driven per year in Highway	8500	8500
Years to hold the vehicle before selling:	10	10
Total 3 years of fuel cost	$9,762.10	$12,065.22	2,303.12
Total cost of ownership	$25,762.10	$24,965.22	-796.88
Total cost per year of car ownership:	$2,576.21	$2,496.52	-79.69
Total cost of ownership after selling car:	$7,762.10	-$4,034.78	-11,796.88
Factors
Total costs of ownership is less campared to my old car telsa
After selling old car will get only 18000.So my of cost ownership is higher than the selling price.