Question

E-Z Mart 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. a. Apply Data Bar conditional formatting (i.e. Solid Fill Blue) for Frequency of gallons of low-fat milk being sold per week. 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?

Answer 1

1.

The percentage of number of week is obtained by using the formula,

And the maximum bound is obtained using the excel function =MAX() and the minimum bound is obtained using the excel function =MIN() .

Demand	Number of Weeks	percentage
60	5	2.40%
61	7	3.37%
62	15	7.21%
63	32	15.38%
64	48	23.08%
65	34	16.35%
66	23	11.06%
67	20	9.62%
68	14	6.73%
69	6	2.88%
70	4	1.92%
Total	208	100.00%
Minimum		1.92%
Maximum		23.08%

a.

Now, select the Number of Weeks column then follow the step shown below,

Home tab > Conditional Formatting > Data Bars. The screenshot is shown below,

2.

The simulation for 10 week is generated in excel by following these steps,

Step 1: Calculate the probability by dividing the number of week by total number of weeks. the screenshot is shown below,

Step 2: DATA > Data Analysis > Random Number Generation > OK. The screenshot is shown below,

Step 3: Insert Number of Variables: 1, Number of Random Numbers: 10, Distribution: Discrete, Value and probability input range: select the Demand and probability column. The screenshot is shown below,

The 10 weeks simulation is generated as shown below,

Week	Demand
1	67
2	63
3	65
4	64
5	65
6	64
7	62
8	68
9	64
10	65

3.

The descriptive statistic is obtained in excel by following step,

DATA > Data analysis > Descriptive Statistic > OK. Then Input > Input Range > Demand column, grouped By: Columns. The screenshot is shown below,

The result is obtained,

Demand
Mean	64.7
Standard Error	0.558768
Median	64.5
Mode	65
Standard Deviation	1.766981
Sample Variance	3.122222
Kurtosis	0.335916
Skewness	0.567951
Range	6
Minimum	62
Maximum	68
Sum	647
Count	10

4.

The simulation for 200 replicate can be obtained by repeating the part 3. by selecting the Number of Random Numbers: 200 in step 3. The screenshot is shown below,

6.

The descriptive statistics for 200 replicates is shown below,

Demand
Mean	64.535
Standard Error	0.145602
Median	64
Mode	64
Standard Deviation	2.05912
Sample Variance	4.239975
Kurtosis	-0.2043
Skewness	0.198921
Range	10
Minimum	60
Maximum	70
Sum	12907
Count	200