The Stereo Warehouse in Georgetown sells stereo sets, which it orders from Fuji Electronics in Japan. Because of shipping and handling costs, each order must be for five stereos. Because of the time it takes to receive an order, the warehouse places an order every time the present stock drops to five or below. It costs $100 to place an order. It costs the warehouse $400 in lost sales when a customer asks for a stereo and the warehouse is out of stock (if a customer cannot purchase a stereo when they want it, the customer will not wait until one comes in, but will instead go to a competitor). It costs $40 per month to keep each stereo stored in the warehouse. The following probability distributions for demand and order lead time have been determined:
Demand/Month Prob. Lead Time Prob.
0 .04 1 .60
1 .08 2 .30
2 .28 3 .10
3 .40
4 .16
5 .02
6 .02
The warehouse presently has five stereos in stock. Orders are always received at the beginning of the month.
Build an Excel simulation model for this problem.
Simulate the Stereo Warehouse’s ordering and sales policy for 24 months, and do 1000 replications and put your results into a column data table on a separate sheet.
Compute and display the average, minimum, and maximum monthly cost.
Construct a 90% confidence interval for average month cost (Excel has tools to help you do this).
Construct an output distribution (frequency diagram) based on the output data using Excel’s chart feature.