Ps Allowed Details Use This Excel Template To Work Either Individually Or In Your Group Of Two On Two Following Problems 1 (37.45 KiB) Viewed 35 times
Ps Allowed Details Use This Excel Template To Work Either Individually Or In Your Group Of Two On Two Following Problems 2 (24.88 KiB) Viewed 35 times
ps Allowed Details Use this Excel Template to work either individually or in your group of two on two following problems. Submit ONE EXCEL file to be graded. If you work in group, make sure to clearly include the name of both group members (you may leave a comment in your submission). ********* ***** ******* (Q1) 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 first tab of the Excel Template. Each car sells for $25,000. If demand for 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. (a) Use simulation to determine how many cars to order in August, if the goal is to maximize mean profit. Narrow your search from 20 to 40 at increments of 5 cars, i.e., choose from 20, 25, 30, 35, and 40. (b) For your optimal order quantity, find a 95% confidence interval for the expected profit. Hint: In order to check whether you develop the right simulation model, the final distribution of the mean profit for different order quantities (20 25.30. 35,40) should have a similar distribution/pattern as below (Note: you will not get the exact numbers shown in the following plot due to the random number generation); ************
101 CumProb 6 7 8 Average 9 Stdev 10 95% Lower Limit 11 95% Upper Limit 12 13 Scenario 14 15 16 17 18 19 20 21 22 23 24 25 25822=2 32 2 3 4 5 6 7 8: 9 10 91 02 11 12 13 14 15 16 17 18 19 25000 Demand Probability 20 0.30 Sales Unit Price Order Unit Cost Reorder Unit Cost Disposal Price 25 0.15 30 0.15 35 0.20 40 0.20 Order Quantity Order Cost Reorder Cost Disposal Value Sales Value Demand D 525,000 $20,000 $22,000 517,000 225 Profit H Order Quantity mean profe 20 25 30 35 40 12 1 CE GA 04 02 C → M mean profit N O
Join a community of subject matter experts. Register for FREE to view solutions, replies, and use search function. Request answer by replying!