Question

1. In August 2017, a car dealer is trying to determine how many 2018 cars to order. Each car ordered in August 2017 costs $16,000. The demand for the dealer’s 2018 models has the probability distribution shown in the table below. Each car sells for $21,000. If the demand for 2018 cars exceeds the number of cars ordered in August 2017, the dealer must reorder at a cost of $18,000 per car. Excess cars can be disposed of at $13,000 per car.

Cars Demanded	Probability
25	.25
30	.20
35	.15
40	.20
45	.20

1. Use simulation to determine how many cars the dealer should order in August, 2017 to maximize his expected profit.

For the optimal order quantity, find a 95% confidence interval for the expected profit.

Answer 1

a)

Simulation model is following:

EXCEL FORMULAS:

Simulation Table
Month	Cars Demanded	Cars Sold	Shortage	Excess	Profit
1	=LOOKUP(RAND(),$C$2:$C$6,$A$2:$A$6)	=MIN(B13,$C$9)	=B13-C13	=$C$9-C13	=B13*$G$3+E13*$G$5-$C$9*$G$2-D13*$G$4
2	=LOOKUP(RAND(),$C$2:$C$6,$A$2:$A$6)	=MIN(B14,$C$9)	=B14-C14	=$C$9-C14	=B14*$G$3+E14*$G$5-$C$9*$G$2-D14*$G$4
3	=LOOKUP(RAND(),$C$2:$C$6,$A$2:$A$6)	=MIN(B15,$C$9)	=B15-C15	=$C$9-C15	=B15*$G$3+E15*$G$5-$C$9*$G$2-D15*$G$4

Formulas of Simulation Table are copied down to 1000 rows for 1000 simulation trials.

Simulation Output
Expected profit	=AVERAGE(F13:F1012)
Standard deviation of profit	=STDEV(F13:F1012)
95% confidence interval (CI) for expected profit
z-value for 95% CI	=NORMSINV(1-(1-0.95)/2)
Lower limit	=I13-I19*I15
Upper limit	=I13+I19*I15

In order to find the optimal order quantity,

Change the number of cars ordered in cell C9 to order quantity 25, 30, 35, 40, 45 and note down expected profit for each order quantity.

Based on simulation of expected profit for different order quantities, the summary is following:

Order quantity	Expected Profit
25	153005
30	157220
35	155700
40	151935
45	142640

Highest expected profit is 157,220 for order quantity 30

Therefore, optimal order quantity = 30 cars

95% confidence interval for expected profit for each order quantity is computed in respective worksheet and is shown .