Question

Please show in Excel

7.4 Using the following weekly demand data for a new soft drink, determine the upper and lower control limits that can be used in recognizing a change in demand patterns. Use ±3σ control limits. Week Demand (six packs) 3500 4100 3750 4300 4000 3650 4

Answer 1

To solve this problem,we'll first compute the mean and stabdard deviation of the given data through average and and STDEV function in Ms Excel respectively.

Mean of the data = 3883.3

(Process mean= sum of all the demands of the week/no of week=23300/6= 3883.3) we can also find that

Demand 3500.0 4100.0 3750.0 4300.0 4000.0 3650.0 2 Week 1 Week 2 Week 3 Week 4 Week 5 Week 6

Average = 3883.3

Standard deviation =274.9

[(Variance=sum of (average-demand of week)²/6

Std deviation of the demand= sqrt(variance)]

Given that the required sigma levels =3

Now

UCL =Mean+3SD= 3883.3+3(274.9)=4708

LCL=Mean-3SD= 3883.3-3(274.9)=3058.6