Quality Control & Supply Chain Management Assignment: Heizer & Render

Problem

The ABC Floral Shop sold the following number of geraniums during the last 2 weeks:

Develop a spreadsheet to answer the following questions.

Calculate a forecast of the above demand using a 3- and 5-period moving average.

Graph these forecasts and the original data using Excel. What does the graph show?

Which of the above forecasts is best? Why?

Day

Demand

Day

Demand

1

200

8

150

2

134

9

182

3

157

10

197

4

165

11

136

5

177

12

163

6

125

13

157

7

146

14

169

Solution

Introduction

A business manager regularly employs various forecasting techniques to determine the growth and expected sales in the upcoming period. Forecasting is not restricted to sales only, but it is also used to determine other important variables which is dependent upon certain factors and expected to change (increase or decrease) over time. Forecasting is done at various levels and help optimize the decision making process for the management. One of the primary purposes of forecasting sales is to formulate the operational strategy accordingly.

Moving Averages

Although, there are many methods for determining forecasted sales, one of the common and reliable method is Moving averages. The notion behind this technique of forecasting is that subsequent period of sales will be more or less identical to the past few periods. Here, in our floral shop case, we can observe that sales are very close to the average. Moving average is similar to normal average since same method is adopted to determine the mean value. However, the primary difference is that moving averages are determined for specific periods of time, not the whole period, for example, 3 periods moving average calculate average of three months for first three months, and so on. Similar is the case for 5 period moving averages.

Following table shows the calculation of 3 period and 5 period moving averages:

Day

Demand

3 period Moving average

5 period Moving average

 

a

b

a-b

(a-b)^2

c

a-c

(a-c)^2

1

200

 

 

 

 

2

134

 

 

 

 

3

157

 

 

 

 

4

165

163.67

1.33

1.78

 

 

5

177

152.00

25.00

625.00

 

 

6

125

166.33

-41.33

1708.44

166.60

166.33

27666.78

7

146

155.67

-9.67

93.44

151.60

155.67

24232.11

8

150

149.33

0.67

0.44

154.00

149.33

22300.44

9

182

140.33

41.67

1736.11

152.60

140.33

19693.44

10

197

159.33

37.67

1418.78

156.00

159.33

25387.11

11

136

176.33

-40.33

1626.78

160.00

176.33

31093.44

12

163

171.67

-8.67

75.11

162.20

171.67

29469.44

13

157

165.33

-8.33

69.44

165.60

165.33

27335.11

14

169

152.00

17.00

289.00

167.00

152.00

23104.00

*15

 

163.00

 

 

164.40

 

 

*forecasted

MSD

694.94

MSD

25586.88

Each moving average is determined by calculating the average of last three or ...