A local E-Z Mart sells popular grocery items to their customers. One of the most popular items that are purchased is low-fat milk. Currently, the store sells a gallon of low-fat milk to their customers for $3.00/gallon. Currently, the manager orders 62 jugs of low-fat milk per week from the local distributor at a cost of $2.00/gallon. The demand of 1-gallon jugs of low-fat milk has varied between 60 and 70 jugs per week at a local EâZ Mart convenience store. The frequency distribution of this demand is shown below in the table. Demand for Milk (jugs) Demand Number of Weeks
60 5
61 7
62 15
63 32
64 48
65 34
66 23
67 20
68 14
69 6
70 4
Recently, a few customers have complained to the manager because on a number of occasions they have not been able to purchase low-fat milk because the store was out of stock. If the store runs out of low-fat milk, the store applies a stock out cost of $0.50/gallon. In addition, any milk that is unsold at the end of the week is scrapped. Unsold milk at the end of the week is a total loss for the store and the manager is afraid to order too much milk per week. However, the manager aims to please his customers and wants to use simulation to determine whether he should increase his weekly order from his distributor.
1. Use the frequency table of weekly demand of gallons of low-fat milk to compute the percentage and the lower and upper bounds for the probability distribution.
2. Create a simulation model for 10 weeks of operation. Generate random values of demand based on the probability distribution. For the remaining calculations that are needed, you are free to use formulas such as IF, MIN, and MAX.
3. After you have completed the first run of the simulation, summarize the first replicate by computing the desired descriptive statistics.
4. Based on the first run of the simulation, create a Data Table to generate 200 replicates for the simulation.
5. Create a histogram for total profit.
6. After you have generated 200 replicates of the simulation, summarize the 200 replicate by computing the desired descriptive statistics.
7. Setup the Scenario Manager and vary the Fixed Order Quantity from 60 gallons of low-fat milk to 70 gallons of low-fat milk per week so that the change in total profit can be observed based on the 200 replicates.
8. Based on the results generated by the Scenario Manager, is there sufficient evidence that the E-Z Mart store manager should increase the weekly fixed order of low-fat milk? If so, how much should the manager order from his distributor each week?