(Need Answers in Excel)

A car dealer needs to determine the number of cars of a specific type to order for next year. Each car he

orders before the end of the year will cost $20,000 (Dealer price). The car sells for $25,000 (Sales price).

However, if the order quantity exceeds demand, the extra cars will be disposed for $17,000 each at the

end of the next year (Salvage price). For example, if he orders 75 cars (just the average of the demand)

and demand happens to be 70, he sells only 70 cars and disposes 5 cars, resulting in the profit of:

-75*$20,000 + 70*$25,000 + 5*$17,000 = $335,000

He is quite sure that the demand follows uniform distribution between 50 and 100. To incorporate

randomness of future demand, the dealer asked Prof. Lee to simulate 500 random demands. He knows

how to obtain the expected profit for a certain size of order. By repeating the above profit calculation with

500 random demands, he can obtain 500 values for profit. Then, he can average all 500 values to obtain

the expected profit for one order size, e.g., 75. Also, he understands that if he can compute the expected

profit for one particular order size, he can compute the expected profits for other order sizes in principle.

However, he is somewhat overwhelmed by the worksheet size and asks your help.

15. He feels that the optimal order size would be between 70 and 90. Compute the expected profit for

each order from 70 to 90 in Simulation 1 worksheet. Report a range (e.g., B510:Q510) having

the expected profits. (6 points)

Focus on the simulation result with the order size of 75 for Question 16 to 18.

16. What is the expected profit? (2 points)

17. Report the 95% confidence interval of the expected profit. (4 points)

18. What is the probability of earning less than or equal to $300,000? (3 points)

Consider the simulation result with all order sizes.

19. What is the maximum value of the expected profits? (2 points)

20. What is the optimal order size that maximize the expected profits? (2 points)

21. What is the probability of earning less than or equal to $300,000 with the order size in Q20? (3

points)

After helping the dealer with pre-generated random demand, you wonder if your suggestion in Q20 is

robust. You decided to validate the suggestion by building your own simulation model. Recall that the

demand is integer and uniformly distributed between 50 and 100.

22. Construct a simulation model and replicate it 1,000 times and show the result in Simulation 2

worksheet. (6 points)

23. What is the expected profit with the order size in Q20? (2 points)