Question

Jase Hansen is interested in leasing a sports-utility vehicle and has contacted three automobile dealers for pricing information. Each dealer offered Jase a 24-month lease with no down payment due at the time of signing. Each lease includes a monthly cost, mileage allowances, and the cost for additional miles. The details are given in the below table.

Dealer	Monthly Cost ($)	Mileage Allowances	Cost per Additional Mile ($)
True Vehicle	300	40,000	0.30
FCO	360	46,000	0.35
Jack’s Auto	410	50,000	0.15

Jase decided to choose the lease option that will minimize his total 24-month cost. He is not sure how many miles he will drive in the next two years. Hence, for the purpose of decision, assume that Jase wants to evaluate options of driving 20,000 miles per year, 23,000 miles per year, and 25,000 miles per year.

a. What is the decision, and what is the chance event?
b. Construct a payoff table for Jase’s problem.

ANSWER:  

a. The decision faced by Jase is to select the best lease option from three alternatives (True Vehicle, FCO, and Jack’s Auto). The chance event is the number of miles Jase will drive.
b. The payoff for any combination of alternative and chance event is the sum of the total monthly charges and total additional mileage cost, i.e.,

for the True Vehicle lease option:
40,000 miles (20,000 miles for 2 years): 24($300) + $0.30(40000 – 40000) = $7,200
46,000 miles (23,000 miles for 2 years): 24($300) + $0.30(46000 – 40000) = $9,000
50,000 miles (25,000 miles for 2 years): 24($300) + $0.30(50000 – 40000) = $10,200

for the FCO lease option:
40,000 miles (20,000 miles for 2 years): 24($360) + $0.35*Max((40000 – 46000),0) = $8,640
46,000 miles (23,000 miles for 2 years): 24($360) + $0.35*Max((46000 – 46000),0) = $8,640
50,000 miles (25,000 miles for 2 years): 24($360) + $0.35*Max((50000 – 46000),0) = $10,040

for the Jack’ Auto lease option:
40,000 miles (20,000 miles for 2 years): 24($410) + $0.15*Max((40000 – 50000),0) = $9,840
46,000 miles (23,000 miles for 2 years): 24($410) + $0.15*Max((46000 – 50000),0) = $9,840
50,000 miles (25,000 miles for 2 years): 24($410) + $0.15*Max((50000 – 50000),0) = $9,840

Below is the payoff table for Jase’s problem:

		Actual Miles Driven Annually
Dealer	20,000	23,000	25,000
True Vehicle	$7,200	$9,000	$10,200
FCO	$8,640	$8,640	$10,040
Jack’s Auto	$9,840	$9,840	$9,840

I need a step buy step guide on how to get to the provided answer using excel 2016

Answer 1

Payoff- calculation

Pay-off Matrix

Decision Tree: