Brief Discussion 2

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher.

Printed in the United States of America.

7-‹#›

Copyright ©2013 Pearson Education, Inc. publishing as Prentice Hall.

Systematic component = (level + trend) × seasonal factor

Ft+l = [L+(t + l)T ]St+l

184 Chapter 7 • Demand Forecasting in a Supply Chain

We now describe one method for estimating the three parameters L, T, and S. As an example, consider the demand for rock salt used primarily to melt snow. This salt is produced by a firm called Tahoe Salt, which sells its salt through a variety of independent retailers around the Lake Tahoe area of the Sierra Nevada Mountains. In the past, Tahoe Salt has relied on estimates of demand from a sample of its retailers, but the company has noticed that these retailers always overestimate their purchases, leaving Tahoe (and even some retailers) stuck with excess inventory. After meeting with its retailers, Tahoe has decided to produce a collaborative forecast. Tahoe Salt wants to work with the retailers to create a more accurate forecast based on the actual retail sales of their salt. Quarterly retail demand data for the past three years are shown in Table 7-1 and charted in Figure 7-1.

In Figure 7-1, observe that demand for salt is seasonal, increasing from the second quarter of a given year to the first quarter of the following year. The second quarter of each year has the lowest demand. Each cycle lasts four quarters, and the demand pattern repeats every year. There is also a growth trend in the demand, with sales growing over the past three years. The company estimates that growth will continue in the coming year at historical rates. We now describe how each of the three parameters—level, trend, and seasonal factors—may be estimated. The following two steps are necessary to making this estimation:

1. Deseasonalize demand and run linear regression to estimate level and trend. 2. Estimate seasonal factors.

40,000

30,000

20,000

10,000

0

50,000

1, 2 1, 3 1, 4 2, 1 2, 2 Period

D em

an d

2, 3 2, 4 3, 1 3, 2 3, 3 3, 4 4, 1

FIGURE 7-1 Quarterly Demand at Tahoe Salt

Table 7-1 Quarterly Demand for Tahoe Salt

Year Quarter Period, t Demand, Dt

1 2 1 8,000

1 3 2 13,000

1 4 3 23,000

2 1 4 34,000

2 2 5 10,000

2 3 6 18,000

2 4 7 23,000

3 1 8 38,000

3 2 9 12,000

3 3 10 13,000

3 4 11 32,000

4 1 12 41,000

M07_CHOP3952_05_SE_C07.QXD 10/22/11 6:54 PM Page 184

Dt = Dt–( p/2) + Dt+( p/2) + 2Di

i=t+1–( p/2)

t–1+( p/2)

∑ ⎡

⎣ ⎢ ⎢

⎤

⎦ ⎥ ⎥ / (2p) for p even

Di / p for p odd i=t–[( p–1)/2]

t+[( p–1)/2]

∑

⎧

⎨

⎪ ⎪

⎩

⎪ ⎪

Dt = Dt–(p/2) + Dt+(p/2) + 2Di i=t+1–(p/2)

t–1+(p/2)

∑ ⎡

⎣ ⎢ ⎢

⎤

⎦ ⎥ ⎥ / (2p)

= D1 + D5 + 2Di i=2

4

∑ /8

Dt = L+Tt

St = Di Dt

Si = Sjp+1

j=0

r–1

∑

r

S1 = (S1 +S5 +S9) /3 = (0.42+0.47+0.52) /3 = 0.47 S2 = (S2 +S6 +S10) /3 = (0.67+0.83+0.55) /3 = 0.68 S3 = (S3 +S7 +S11) /3 = (1.15+1.04+1.32) /3 =1.17 S4 = (S4 +S8 +S12) /3 = (1.66+1.68+1.66) /3 =1.67

F13 = (L+13T)S13 = (18,439+13×524)0.47 =11,868 F14 = (L+14T)S14 = (18,439+14×524)0.68 =17,527 F15 = (L+15T)S15 = (18,439+15×524)1.17 = 30,770 F16 = (L+16T)S16 = (18,439+16×524)1.67 = 44,794

Ft+1 = (Lt + lTt)St+1

Lt+1 =αDt+1 +(1–α)Lt

L0 = 1 n

Di i=1

n

∑

Ft+1 = Lt and Ft+n = Lt

Lt+1 = α(1–α) nDt+1–n +(1–α)

t D1 n=0

t–1

∑

L0 = Di i=1

4

∑ / 4 =120.75

F1 = L0 =120.75

L1 =αD1 +(1–α)L0

= 0.1×120+0.9×120.75 =120.68

Et = Ft – Dt

MSEn = 1 n

Et 2

t=1

n

∑

At = Et MADn = 1 n

At t=1

n

∑

σ =1.25MAD

MAPEn =

Et Dt 100

t=1

n

∑

n

biasn = Et t=1

n

∑

TSt biast MADt

αt = αt–1

ρ +αt–1 = 1– ρ 1– ρt

196 Chapter 7 • Demand Forecasting in a Supply Chain

as shown in Figure 7-5. The forecast shown in Figure 7-5 uses the resulting ! ! 0.54 and gives MSE ! 2,460, MAD ! 42.5 and MAPE ! 2.1 percent.

The smoothing constant can also be selected using Solver by minimizing the MAD or the MAPE at the end of 10 periods. In Figure 7-6, we show the results from minimizing MAD (cell G13). The forecasts and errors with the resulting ! ! 0.32 are shown in Figure 7-6. In this case, the MSE increases to 2,570 (compared to 2,460 in Figure 7-5) while the MAD decreases to 39.2 (compared to 42.5 in Figure 7-5) and the MAPE decreases to 2.0 percent (compared to 2.1 percent in Figure 7-5). The major difference between the two forecasts is in period 9 (the period with the largest error shown in cell D11), where minimizing MSE picks a smoothing constant that reduces large errors, while minimizing MAD picks a smoothing constant that gives equal weight to reducing all errors even if large errors get somewhat larger.

FIGURE 7-5 Selecting Smoothing Constant by Minimizing MSE

M07_CHOP3952_05_SE_C07.QXD 10/22/11 6:54 PM Page 196

Chapter 7 • Demand Forecasting in a Supply Chain 197

In general, it is not a good idea to use smoothing constants much larger than 0.2 for extended periods of time. A larger smoothing constant may be justified for a short period of time when demand is in transition. It should, however, generally be avoided for extended periods of time.

7.8 FORECASTING DEMAND AT TAHOE SALT

Recall the Tahoe Salt example earlier in the chapter with the historical sell-through demand from its retailers shown in Table 7-1. The demand data are also shown in column B of Figure 7-7. Tahoe Salt is currently negotiating contracts with suppliers for the four quarters between the second quarter of Year 4 and the first quarter of Year 5. An important input into this negotiation is the forecast of demand that Tahoe Salt and its retailers are building collaboratively. They have assigned a team consisting of two sales managers from the retailers and the vice president of

FIGURE 7-6 Selecting Smoothing Constant by Minimizing MAD

M07_CHOP3952_05_SE_C07.QXD 10/22/11 6:54 PM Page 197