70.2K

Verified Solution

Link Copied!

1. Download the attached Excel file for the total ownership model that was used to compare gasoline-powered vs. hybrid automobile costs discussed during class. (attached reference below)
2. Update the model to compare the total ownership cost (TOC) of a hybrid (not a plug-in hybrid) and an otherwise comparable all-electric automobile, both of which are of the same model year, and no more than five years old, for which you can find data. (Note: An otherwise comparable all-electric auto will have a higher Manufacturers Suggested Retail Price than a hybrid auto.)
3. Using your model, determine the cost of a gallon of gasoline at which the hybrid and all-electric automobiles would have the same TOC over a 10-year period (within $100) with a representative number of miles driven per year, with the all-electric vehicle charging at prevailing electricity rates and with a reasonable mix of at-home charging and on-the-road fast charging. Assume that the home does not already have a 220-volt electrical outlet that is accessible to the home auto parking location. (Note: There is no single "correct" answer.)
4. Show the updated model in the first tab of the file with the data entries that produced the break-even TOC.
5. In the second tab of the file, list the sources of all data for the elements of TOC for which you and find data. (Provide links to all sources of data).
6. In the third tab of the file, list any assumptions you made in the model and its input data, including for all input data values for which you could not find data.
7. In the fourth tab, provide a brief (2-3 paragraphs) narrative describing your approach to the solution(s).
8. Grading will be based on the detail and mathematical correctness of the model, the documentation of the data sources, listing of all assumptions made, and the description of the logic used in the approach.
9. To get your thinking started, read the article at Filling a Gas-powered Vehicle Can Still be Cheaper Than Charging an Electric One. You can find other articles related to this topic in a quick internet search.

Referenced Initial TOC Model Excel Sheet

1. Download the attached Excel file for the total ownership model that was used to compare gasoline-powered vs. hybrid automobile costs discussed during class. (attached reference below)
2. Update the model to compare the total ownership cost (TOC) of a hybrid (not a plug-in hybrid) and an otherwise comparable all-electric automobile, both of which are of the same model year, and no more than five years old, for which you can find data. (Note: An otherwise comparable all-electric auto will have a higher Manufacturers Suggested Retail Price than a hybrid auto.)
3. Using your model, determine the cost of a gallon of gasoline at which the hybrid and all-electric automobiles would have the same TOC over a 10-year period (within $100) with a representative number of miles driven per year, with the all-electric vehicle charging at prevailing electricity rates and with a reasonable mix of at-home charging and on-the-road fast charging. Assume that the home does not already have a 220-volt electrical outlet that is accessible to the home auto parking location. (Note: There is no single "correct" answer.)
4. Show the updated model in the first tab of the file with the data entries that produced the break-even TOC.
5. In the second tab of the file, list the sources of all data for the elements of TOC for which you and find data. (Provide links to all sources of data).
6. In the third tab of the file, list any assumptions you made in the model and its input data, including for all input data values for which you could not find data.
7. In the fourth tab, provide a brief (2-3 paragraphs) narrative describing your approach to the solution(s).
8. Grading will be based on the detail and mathematical correctness of the model, the documentation of the data sources, listing of all assumptions made, and the description of the logic used in the approach.
9. To get your thinking started, read the article at Filling a Gas-powered Vehicle Can Still be Cheaper Than Charging an Electric One. You can find other articles related to this topic in a quick internet search.

Referenced Initial TOC Model Excel Sheet

Total Ownership Cost (TOC) Comparison, Gasoline-Powered vs. Hybrid Vehicle
Common factors
Length of vehicle ownership (years)	10
Gasoline cost per gallon	$3.00
Miles driven per year	12,000
Auto make/model/year	Nissan Altima 2009 2.5L 4 cyl (2.5S vs. hybrid)
	Gasoline	Hybrid
Estimated miles per gallon	26	34		EPA combined
Estimated annual maintenance cost	$500	$500
Estimated annual insurance cost	$1,200	$1,200
Estimated retained value % at 5 years	55.15%	55.37%
Hybrid vehicle battery life (years)	N/A	8
Hybrid vehicle battery replacement cost	N/A	$4,000
Government tax credit	$0	$2,350
MSRP	$21,040	$26,650
Net cost	$21,040	$24,300
	Purchase	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10	Total
Retained value profile percentage	100%	85%	74%	64%	57%	50%	43%	36%	29%	22%	15%
Gasoline-powered
Retained value percentage		86.55%	76.68%	67.71%	61.43%	55.15%	48.87%	42.59%	36.31%	30.03%	23.76%
Depreciation amount		$2,831	$2,076	$1,887	$1,321	$1,321	$1,321	$1,321	$1,321	$1,321	$1,321	$16,042
Maintenance		$500	$500	$500	$500	$500	$500	$500	$500	$500	$500	$5,000
Insurance		$1,200	$1,200	$1,200	$1,200	$1,200	$1,200	$1,200	$1,200	$1,200	$1,200	$12,000
Government tax credit		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0
Battery depreciation		$0	$0	$0	$0	$0	$0	$0	$0	$0	$0	$0
Gasoline cost		$1,385	$1,385	$1,385	$1,385	$1,385	$1,385	$1,385	$1,385	$1,385	$1,385	$13,846
Total annual ownership cost		$5,916	$5,161	$4,972	$4,406	$4,406	$4,406	$4,406	$4,406	$4,406	$4,406
Total cumulative ownership cost		$5,916	$11,076	$16,048	$20,454	$24,860	$29,265	$33,671	$38,077	$42,482	$46,888
Hybrid
Retained value percentage		86.61%	76.79%	67.87%	61.62%	55.37%	49.12%	42.87%	36.63%	30.38%	24.13%
Depreciation amount		$3,568	$2,617	$2,379	$1,665	$1,665	$1,665	$1,665	$1,665	$1,665	$1,665	$20,220
Maintenance		$500	$500	$500	$500	$500	$500	$500	$500	$500	$500	$5,000
Insurance		$1,200	$1,200	$1,200	$1,200	$1,200	$1,200	$1,200	$1,200	$1,200	$1,200	$12,000
Government tax credit		-$2,350	$0	$0	$0	$0	$0	$0	$0	$0	$0	-$2,350
Battery replacement		$0	$0	$0	$0	$0	$0	$0	$4,000	$0	$0	$4,000
Gasoline cost		$1,059	$1,059	$1,059	$1,059	$1,059	$1,059	$1,059	$1,059	$1,059	$1,059	$10,588
Total annual ownership cost		$3,977	$5,375	$5,138	$4,424	$4,424	$4,424	$4,424	$8,424	$4,424	$4,424
Total cumulative ownership cost		$3,977	$9,352	$14,490	$18,914	$23,338	$27,762	$32,186	$40,610	$45,034	$49,458

Answer & Explanation Solved by verified expert

See Answer