PLEASE ANSWER IN FULL WITH PICTURES AND FORMULAS!!!

In August of the current year, a car dealer is trying to determine how many cars of the next model year to order. Each car ordered in August costs $20,000. The demand for the dealer's next year models has the probability distribution shown in the file HW_10_S15_12.xlsx. Each car sells for $25,000. If demand for the next year's cars exceeds the number of cars ordered in August, the dealer must reorder at a cost of $22,000 per car. Excess cars can be disposed of at $17,000 per car.

Please use simulation to determine how many cars to order in August. For your optimal order quantity, find a 95% confidence interval for the order quantity of 30.

More specifications:

1. Please show your working process instead of the final answer.
2. In order to test whether your data table works or not, please use the recalculate function. 3. In this homework, the proposed order quantity is 25, and the repetition for the data table is 500
   

B C D E Simulation of a car dealer Cost data unit cost of initial order $20,000.00 unit cost of reorder $22,000.00 Demand Distribution unit price $25,000.00 Demand Probability disposal value $17,000.00 20 0.30 25 0.15 30 0.15 10 Order Quantity 25 35 0.20 11 40 0.20 12 13 14 Simulation 15 Demand 16 Fullprice sales revenue Disposal sales revenue Initial order cost 19 Reorder cost Profit 22 23 Summary measures from data table below 24 Order quantity 20 25 30 40 25 mean profit 26 Stdev of profit 95% confidence from mean profit for the optimal order quantity 29 Lower limit 30 Upper limit 32 33 Data table for replication of profit (replication along side, order quantity along top) 20 25 30 40 35 36 OUT A W NH 88 7 9 10 11 12 14 15 16