Problem 4: Simulation Modeling Information: Cost $ 18,000.00 Price $ 23,000.00 Reorder cost $ 21,000.00 Dispose...

Transcribed Image Text:

Problem 4: Simulation Modeling Information: Cost $ 18,000.00 Price $ 23,000.00 Reorder cost $ 21,000.00 Dispose value $ 15,000.00 Order 30 Iteration: Cumulative Prob Cars demanded Probability 0.25 25 0.25 0.45 30 0.2 0.65 35 0.15 0.85 40 0.2 1.05 45 0.2 In September 2012, a car dealer is trying to determine how many 2013 cars to order. Each car ordered in September 2012 costs $18,000. The demand for the dealer's 2013 models has the probability distribution shown in the table below. Each car sells for $23,000. If the demand for 2013 cars exceeds the number of cars ordered in September 2012, the dealer must reorder at a cost of $21,000 per car. Excess cars can be disposed of at $15,000 per car. (A) Fill out the cumulative probabilities column (highlighted yellow D3:D7) (B) Use the discrete probability model given to general a demand. You should you appropriate Excel function(s) to generate a random demand value in cell B10. (C) Copy the work in row 10 down to 1000 iterations. Do not freez the data. (D) Compute the expected profit based on 1000 observations. Display the answer in cell 118 (E) Use simulation and the data table to determine how many cars the dealer should order to maximize his expected profit. Highlight that value in table H17:134. DO NOT FREEZE THE DATA! Demand 1 0.25 Reorder Cost $0.00 Dispose value Profit $446,250.00 -$88,000.00 2 0.65 3 0.25 4 #N/A 5 0.65 6 0.65 7 #N/A 8 #N/A Order Expected Profit 9 0.65 20 10 0.85 22 11 #N/A 24 12 #N/A 26 13 0.45 28 14 0.85 30 15 0.45 32 16 0.45 34 17 0.65 36 18 0.65 38 19 0.65 40 20 0.45 42 21 0.45 44 22 #N/A 46 23 #N/A 48 24 0.45 50 25 0.25 26 0.65 27 0.65 28 #N/A 29 0.85 30 0.65 31 0.45 32 0.45 Problem 4: Simulation Modeling Information: Cost $ 18,000.00 Price $ 23,000.00 Reorder cost $ 21,000.00 Dispose value $ 15,000.00 Order 30 Iteration: Cumulative Prob Cars demanded Probability 0.25 25 0.25 0.45 30 0.2 0.65 35 0.15 0.85 40 0.2 1.05 45 0.2 In September 2012, a car dealer is trying to determine how many 2013 cars to order. Each car ordered in September 2012 costs $18,000. The demand for the dealer's 2013 models has the probability distribution shown in the table below. Each car sells for $23,000. If the demand for 2013 cars exceeds the number of cars ordered in September 2012, the dealer must reorder at a cost of $21,000 per car. Excess cars can be disposed of at $15,000 per car. (A) Fill out the cumulative probabilities column (highlighted yellow D3:D7) (B) Use the discrete probability model given to general a demand. You should you appropriate Excel function(s) to generate a random demand value in cell B10. (C) Copy the work in row 10 down to 1000 iterations. Do not freez the data. (D) Compute the expected profit based on 1000 observations. Display the answer in cell 118 (E) Use simulation and the data table to determine how many cars the dealer should order to maximize his expected profit. Highlight that value in table H17:134. DO NOT FREEZE THE DATA! Demand 1 0.25 Reorder Cost $0.00 Dispose value Profit $446,250.00 -$88,000.00 2 0.65 3 0.25 4 #N/A 5 0.65 6 0.65 7 #N/A 8 #N/A Order Expected Profit 9 0.65 20 10 0.85 22 11 #N/A 24 12 #N/A 26 13 0.45 28 14 0.85 30 15 0.45 32 16 0.45 34 17 0.65 36 18 0.65 38 19 0.65 40 20 0.45 42 21 0.45 44 22 #N/A 46 23 #N/A 48 24 0.45 50 25 0.25 26 0.65 27 0.65 28 #N/A 29 0.85 30 0.65 31 0.45 32 0.45