Forecasting with a Trend

Dr. Ron Lembke

Averaging Methods•Simple Average•Moving Average•Weighted Moving Average•Exponentially Weighted Moving Average

(Exponential Smoothing)•They ALL take an average of the past

▫With a trend, all do badly▫Average must be in-between 30

2010

Linear Regression?•Determine how demand increases as a

function of time

t = periods since beginning of datab = Slope of the linea = Value of yt at t = 0

btayt

Computing Values

2)(

12

22

nYy

S

xbyn

xbya

xnx

yxnxyb

n

i iiyx

Linear Regression•Four methods

1. Type in formulas for trend, intercept2. Tools | Data Analysis | Regression3. Graph, and R click on data, add a trendline,

and display the equation.4. Use intercept(Y,X), slope(Y,X) and RSQ(Y,X)

commands•R2 measures the percentage of change in

y that can be explained by changes in x.•Gives all data equal weight.•Exp. smoothing with a trend gives more

weight to recent, less to old.

Trend-Adjusted Ex. Smoothing

Trend IncludingForecast Estimate Trend Smoothed Exp.

for forecast Smoothed Exp.

t

t

t

FITT

tF

ttt

tttt

tt

tttt

TFFITFITFTTAFITFITAFITF

.3

.2)1(

.1

11

11

111

constants smoothing are and where

Trend-Adjusted Ex. Smoothing

3.103.010)110111(*30.010

121112

FITFTFITFTT ttt

F1 100

T1 10

0.20

0.30Forecast including trend for period 1 is

FIT1 F1 T1100 10 110

F2 FITt 1 At 1 FITt 1 FIT1 A1 FIT1 110 0.2*(115 110) 110 1111.0

Suppose actual demand is 115, A1=115

FIT2 F2 T 2111 10.3 121.3

Trend-Adjusted Ex. Smoothing

22.10078.03.10)3.12104.121(*30.03.10

2323

FITFTT

0.1112 F 3.102 T

0.20

0.30Forecast including trend for period 2 is

3.1213.10111222 TFFIT

04.1213.1*2.03.121)3.121120(*2.03.121

2223

FITAFITF

Suppose actual demand is 120, A2=120

26.13122.1004.121333 TFFIT

F5

FIT5=F5+T5

A5F6

Long’s Peak, CO, 14,259

Selecting and •You could:

▫Try an initial value for each parameter.▫Try lots of combinations and see what looks

best.▫But how do we decide “what looks best?”

•Let’s measure the amount of forecast error.

•Then, try lots of combinations of parameters in a methodical way.▫Let = 0 to 1, increasing by 0.1

For each value, try = 0 to 1, increasing by 0.1

Another Analogy•Hitting moon reflectors

▫“Lunar Laser Ranging Exp”•Ridiculously Simplified:

▫Suppose know your location, and the proper angle•Error in location, miss target by

few feet•Error in angle, miss the moon•Make small adjustments to

trend• Buzz Aldrin video (age 72)

Projecting Further Into Future•F is our best guess, currently of the level•T is our best guess of growth rate

•Boss asks for period 15.▫Come back after period 14?▫No!

900,10400500,10121212 TFFIT

100,12400*4500,10*4700,11400*3500,10*3300,11400*2500,10*2

121215

121214

121213

TFFITTFFITTFFIT

Causal Forecasting•Linear regression seeks a linear

relationship between the input variable and the output quantity.

•For example, furniture sales correlates to housing sales

•Not easy, multiple sources of error:▫Understand and quantify relationship▫Someone else has to forecast the x values

for you

bxayc

Economist, Feb. 2011

Dangers of Historical Analogies

Box Office $ Millions

01002003004005006007008009001000

Shrek Shrek2

•Shrek did $500m at the box office, and sold almost 50 million DVDs & videos

•Shrek2 did $920m at the box office•What will be the video sales?

Video sales of Shrek 2?•Assume 1-1 ratio:

▫920/500 = 1.84▫1.84 * 50 million = 92 million videos?▫Fortunately, not that dumb.

•January 3, 2005: 37 million sold!•March analyst call: 40m by end Q1•March SEC filing: 33.7 million sold. Oops.•May 10 Announcement:

▫In 2nd public Q, missed earnings targets by 25%.

▫May 9, word started leaking▫Stock dropped 16.7%

Lessons Learned•Guaranteed Sales: flooded market with

DVDs▫Promised the retailer they would sell them, or

else the retailer could return them▫Didn’t know how many would come back

•5 years ago▫Typical movie 30% of sales in first week▫Animated movies even lower than that

•2004/5 50-70% in first week▫ Shrek 2: 12.1m in first 3 days▫Far Far Away Idol▫Had to vote in first week

Summary• Including a trend

▫ Linear Regression gives equal weight to all data

▫ FIT includes a trend, gives more weight to more recent data

▫ Can predict more than one period into future

• Causal relationships require estimating input numbers and relationships

• Past history very helpful in predicting▫ But not perfect. Be aware of your

assumptions