practice with forecasting

8/15/2019 Practice With Forecasting

1/34

Pr

Intr

Inst1

2

Ref

1

2

Addi

1


2/34

2


3/34

ctice with ForecastingNaïve Forecast

Naïve Trend

Moving Average

Weighted Moving AverageSingle Exponential Smoothing

Doule Exponential Smoothing

!Measuring Forecast Accuarc"#

!Answers#

This $le can e %ound online as Excel &''( and Excel &'') spreasheets*

+" ,im Flowers- +all State .niversit"

April /- &''/

0evised Septemer &1- &''/

duction

uctionsProcede through the numered wor2sheet tas in order3

The answers are in the $nal ta3 .se the values speci$ed to get the answers listed3

rences

tional Instructional Resources from the Author

Forecasting Trends

http*445c6owers73iwe3su3edu4rlo4PracticeWithForecasting3xlsx

http*445c6owers73iwe3su3edu4rlo4PracticeWithForecasting3xls

http*445c6owers73iwe3su3edu

The purpose o% this spreadsheet is to provide instruction on how to per%orm some simple%orecasting techni8ues- and to suggest practice3

The approach ta2e in this spreadsheet is ased on the in%ormation and examples in 9evin-0uin- : Stinson !7/1;#3

9evin- 03- 0uin- D3- : and Stinson- ,3 !7/1;#3 raw?@ill3

.nited States Department o% Energ"3 !&''/#3 CFL Market Profle - March 20093 Washington-D* Author3 0etrieved April (- &''/ %rom

http*44www3energ"star3gov4ia4products4downloads4F9BMar2etBPro$le3pd%

http*445c6owers73iwe3su3edu4rlo4trends3htm

http://jcflowers1.iweb.bsu.edu/rlo/PracticeWithForecasting.xlsxhttp://jcflowers1.iweb.bsu.edu/rlo/PracticeWithForecasting.xlshttp://jcflowers1.iweb.bsu.edu/http://www.energystar.gov/ia/products/downloads/CFL_Market_Profile.pdfhttp://jcflowers1.iweb.bsu.edu/rlo/trends.htmhttp://jcflowers1.iweb.bsu.edu/rlo/trends.htmhttp://www.energystar.gov/ia/products/downloads/CFL_Market_Profile.pdfhttp://jcflowers1.iweb.bsu.edu/http://jcflowers1.iweb.bsu.edu/rlo/PracticeWithForecasting.xlshttp://jcflowers1.iweb.bsu.edu/rlo/PracticeWithForecasting.xlsx


4/34

Forecasting Exercise 4 Example* umulative 0elease o% Mercur" %rom ompact Fluorescent9amps

http*445c6owers73iwe3su3edu4rlo4ForecastingF9Mercur"3xlsx

http*445c6owers73iwe3su3edu4rlo4ForecastingF9Mercur"3xls

http://jcflowers1.iweb.bsu.edu/rlo/ForecastingCFLMercury.xlsxhttp://jcflowers1.iweb.bsu.edu/rlo/ForecastingCFLMercury.xlshttp://jcflowers1.iweb.bsu.edu/rlo/ForecastingCFLMercury.xlshttp://jcflowers1.iweb.bsu.edu/rlo/ForecastingCFLMercury.xlsx


5/34

73 Naïve Forecasta3

Note

Example

@istorical Data on Mos8uito ounts at Station (

Date Mosquitoes

&'?,un &/C

&7?,un &;

&&?,un )&&

&)?,un )77

&C?,un &1'

&?,un )&1

&;?,un )7

Predict: &(?,un

Answer: 1!"#

$ormula: %&D1'

Rationale:

PRA()I(E 1: Na*+e $orecast

.se the naïve %orecast method to predict*

Numer o% F9 manu%acturers estimated %or the "ear &''/

,ear Mf-s

7/// 7'

&''' &7

&''7 C1

&''& ;/

&'') 17

&''C /)

&'' /;

&''; /(

&''( 7'7

&''1 /)

Predict: &''/ /)

Answer:

Enter the most recent datum %or the next%orecast3

This assumes a constant trend centered on the most recent datum-and is o%ten superior to some ver" complex strategies3

We merel" entered the most recent data %rom &;?,un as the %orecast%or &(?,un3


6/34

&3 Naïve Trenda3

Note

Example


Date Mosquitoes

&'?,un &/C

&7?,un &;

&&?,un )&&

&)?,un )77

&C?,un &1'

&?,un )&1

&;?,un )7

Predict: &(?,un

Answer: #2"#

$ormula: %&D1'&.D1'/D1!0

Rationale:

PRA()I(E 2: Na*+e )rend

.se the naïve trend method to predict*


,ear Mf-s

7/// 7'

&''' &7

&''7 C1

&''& ;/

&'') 17

&''C /)

&'' /;

&''; /(

&''( 7'7

&''1 /)

Predict: &''/ !"#

Assume the data will change as it did in themost recent period3

This assumes a linear trend ased on the most recent changeetween the last two data points3

There was a decrease o% 7) %rom & to &; ,un- so with anotherdecrease o% 7) the result is )'&3


7/34

)3 Moving Averagea3

3 .se the average o% the previous n data items3

Note

Example


Date Mosquitoes

&'?,un &/C&7?,un &;

&&?,un )&&

&)?,un )77

&C?,un &1'

&?,un )&1

&;?,un )7

Predict: &(?,un

Answer: 11"2

$ormula: %AERA3E.D12:D1'0

Rationale:

PRA()I(E : Mo+in- A+era-e

.se the moving average method to predict*


,ear Mf-s

7/// 7'

&''' &7

&''7 C1&''& ;/

&'') 17

&''C /)

&'' /;

&''; /(

&''( 7'7

&''1 /)

Predict: &''/ 45

Determine the numer !n# o% data items toaverage3

A moving average can eliminate minor 6uctuation in the data3Selecting the numer o% items !n# determines the degree o%smoothing3

@ere- n was used- so the average %or the last $ve da"s was thepreduction3


8/34

n % 6

The solution in the Answers ta used*

n )


9/34

C3 Weighted Moving Averagea3

3

c3

d3

Note

Example


Date Mosquitoes $actor Product

&'?,un &/C

&7?,un &;

&&?,un )&& 1#7 2"2

&)?,un )77 1!7 8'"5

&C?,un &1' 2#7 !'"#

&?,un )&1 2!7 2"#

&;?,un )7 #7 48"!

Predict: &(?,un 1##7 11"8

Answer: 11"8

$ormula: %&D19E1&D189E18&D1!9E1!&D1'9E1'&D159E15

Rationale:

PRA()I(E 8: ei-hted Mo+in- A+era-e

.se the weighted moving average method to predict*


,ear Mf-s

7/// 7'

&''' &7

&''7 C1

&''& ;/

Determine the numer o% past items toaverage3

For each item- determine a diGerent weighting%actor- ma2ing sure all weighting %actors addup to 7''H3

Multipl" each o% these past data items " itsweighting %actor3

Add the products o% the %actors and their dataitems3

Sometimes- the most recent period should car" more weight in amoving average than one that is more distant3 So assign %actors toeach o% the diGerent historical data used in a moving average-ensuring these %actors add to 73' or 7''H3

The percentages shown in olumn E allowed more weight to e givento more recent data items3


10/34

&'') 17

&''C /)

&'' /;

&''; /(

&''( 7'7

&''1 /)

Predict: &''/ 4'"8n % 6

The solution in the Answers ta used*

n )

wt7 'H

wt& )H

wt) 7H


11/34

a3

3 Multipl" the primar" %actor " the last datum3

c3

d3 Add the products o% these two calculations3

Note

Example


Date Mosquitoes

&'?,un &/C&7?,un &;

&&?,un )&&

&)?,un )77

&C?,un &1'

&?,un )&1

&;?,un )7

Predict: &(?,un

or;sheet

Primar" Factor* ;'HDamping Factor* C'H

Date Mosquitoes Prediction

&'?,un &/C none

&7?,un &; &/C I Enter the $rst datum as the

&&?,un )&& &(( I !;'H J &;# K !C'H J &/C#

&)?,un )77 )'C

&C?,un &1' )'1

&?,un )&1 &/7

3 Single !or Simple# ExponentialSmoothing

Determine two %actors !Primar" and Damping#

that add to 7''H3

Multipl" the damping %actor " the lastprediction3

@ere- 7''H is divided into two parts- li2e ('H and )'H3 We use oneo% these !('H# as a %actor to multipl" " the previous data item- andthe other !)'H# as a dampin- %actor to multipl" " our lastpredication3 This tends to act as a uGer- 2eeping the predication%rom eing too radicall" eGected " 6uctuations in the data3


12/34

&;?,un )7 )7)

&(?,un )7C

Answer: 18"

$ormula: %&E


13/34

Primar" Factor* 1'H

Damping Factor* &'H


14/34

$rst prediction3

&((


15/34


16/34


17/34

;3 Doule Exponential Smoothinga3

3

Note

Example


Date Mosquitoes

&'?,un &/C

&7?,un &;

&&?,un )&&

&)?,un )77

&C?,un &1'

&?,un )&1

&;?,un )7

Predict: &(?,un 6

or;sheet

Primar" Factor* '3&'

Secondar" Factor* '3') ntial T

7'31'

Time Date Mosquitoes Prediction Error 9evel at the end o% t

t > $ e ?

' &'?,un &/C &(73''7 &7?,un &; 21" 7;31' &;13);

& &&?,un )&& 254"5 ?C&3)C )7)3)

) &)?,un )77 2"' 7&3( )7)37

C &C?,un &1' 2"4 C)3/& &113(1

&?,un )&1 ##"! ?&(3C/ )&&3'

; &;?,un )7 "8 713C7 )713;1

( &(?,un 6 #"1

Determine two levels o% ad5ustment %actors to emultiplied " the %orecast error- such as 3& and 3')3

.sing the e8uations elow appl" one %actor tothe level- and another to the trend3

As in single exponential smoothing- we still use a primar" smoothing%actor- li2e 3&3 +ut we also appl" a smoothing %actor to the trend itsel%-something small li2e 3')3 We egin " estimating the trend- and then the%ormulas sel%?ad5ust over time3


18/34

Answer: #"1

$ormulas: &7?,un %&E


19/34

nitial S

&(7

Trend at the end o% t3 Forecast %or next datum3

) $

7'31' 21"773)' 254"5

7'3') 2"'

7'3C7 2"4

773() ##"!

7'3/' "8

773C; #"1


20/34

%&B2&E


21/34

Measuring Forecast Accurac"a3

3 alculate Theils . statistic3

Note

Example


Date Mosquitoes Predicatio Error

&'?,un &/C

&7?,un &; &1731 7;31'

&&?,un )&& &(/3( ?C&3)C

&)?,un )77 )&)3; 7&3(

&C?,un &1' )&)3/ C)3/&

&?,un )&1 )''3 ?&(3C/

&;?,un )7 )))3C 713C7

&(?,un ))'37

or;sheet A to compute MAEC M=EC and MAPE

Date Mosquitoes $orecast Error A@s" Error

t > $ e e

' &'?,un &/C

7 &7?,un &; &1731 7;31' 7;31'

& &&?,un )&& &(/3( ?C&3)C C&3)C

) &)?,un )77 )&)3; 7&3( 7&3(

C &C?,un &1' )&)3/ C)3/& C)3/& &?,un )&1 )''3 ?&(3C/ &(3C/

; &;?,un )7 )))3C 713C7 713C7

( &(?,un ))'37

=tatistic MAE

Mean 2'"42

.se the %ormulas in olumns ?9 to compute theMean Asolute Error- Mean S8uared Error- andMean Asolute Percent Error3

Data %rom our doule exponential smoothing %orecasts to illustrate how tocompute %our diGerent statistics related to %orecast accurac"3 For each o%these statistics- a value closer to Qero means a more accurate %orecast3


22/34

$ormulas &7?,un &; &1731 KE&C?D&C KA+S!F&C#

Rationale:

or;sheet to compute )heilFs G

Date Mosquitoes $orecast Numerator

t > $

' &'?,un &/C

7 &7?,un &; &1731 '3'&

& &&?,un )&& &(/3( '3''7

) &)?,un )77 )&)3; '3'7//

C &C?,un &1' )&)3/ '3''/; &?,un )&1 )''3 '3'')7

; &;?,un )7 )))3C

( &(?,un ))'37

=um '3'/1

)heilFs G #"2

$ormulas &7?,un &; &1731 K!!EC&?DCDC7#R&

)heilFs G $ormula: KSC/4@C/#

Rationale:

PRA()I(E: Measurin- $orecast Accurac

Determine the MAE- MSE- MAPE- and Theils . %or the %ollowing*


,ear Mf-s $orecast

The mean asolute error and the mean s8uared error are in units ased onthe oserved values3 The mean asolute percent error is not ased onthose units- ut is instead a percentage o% error- and thus ma" %acilitatecomparing models ased on diGerent units3 n all cases- a lower statisticmeans etter accurac"3

The %orumlas used in the &7?,un line are shown aove3

The denominator is the percent error o% the naïve %orecast- though

expressed here as a decimal3 The numerator is the percent error o% the%orecast- again- as a decimal3 +oth ase the error as a percentage o% thevalue oserved %rom the previous period3

Theils . is computed " ta2ing the positive s8uare root o% this %raction o%the sum o% the numerator values divided " the sum o% the denominatorvalues3% Theils . were 73'''- the prediction would e exactl" as accurate as thenaïve %orecast3 alues less than 73''' indicate greater accurac"3

The %orumlas used in the &7?,un line are shown aove3


23/34

7/// 7'

&''' &7 (31 73(C&C

&''7 C1 )13C '3&'1&1)()(

&''& ;/ ;3; '3'''/''&(

&'') 17 1(37 '3''(/'C(;1

&''C /) 7''3& '3''(/&1'&';

&'' /; 77&3 '3')7)C77;'7&''; /( 77;3( '3'C&7/')'

&''( 7'7 7713C '3')&)&'&/

&''1 /) 7&&3; '3'1;))'1

&''/ 77;3C

MAE:

M=E:MAPE:

)heilFs G =um of Numerators: &37;)'1'&

)heilFs G =um of Numerators: )377(1C;

)heilFs G: '31))&';C


24/34

=quared Error

eH2 e>

&1&3&C ;3)H

7(/&3)C 7)37H

7(3/& C3'H

7/&/3)C 73(H(37 13CH

))1317 31H

M=E MAPE

5'"# "47

A@s"Percent

Error


25/34

KF&CR& KA+S!F&C4D&C#

Denominator

'3'C;)

'3''7&

'3''//

'3'&/C'3''7;

'3'11)

K!!DC&?DC7#4DC7#R&


26/34

73&7

73;)';7&&C

'37/7C';&

'3')'&C(C;(

'3'&7/C(1()1

'3''7'C'1&('3'''7'1';/

'3''7(''C//

'3'';&()1/C(


27/34

Answers

PRA()I(E 1: Na*+e $orecast

.se the naïve %orecast method to predict*


,ear Mf-s

7/// 7'

&''' &7

&''7 C1

&''& ;/

&'') 17

&''C /)

&'' /;

&''; /(

&''( 7'7

&''1 /)

Predict: &''/ 4"#Explanation: ,ust choose the previous datum point %rom &''13

PRA()I(E 2: Na*+e )rend

.se the naïve trend method to predict*


,ear Mf-s

7/// 7'

&''' &7

&''7 C1

&''& ;/&'') 17

&''C /)

&'' /;

&''; /(

&''( 7'7

&''1 /)

Predict: &''/ !"#

Explanation:

PRA()I(E : Mo+in- A+era-e

.se the moving average method to predict*


,ear Mf-s

7/// 7'

&''' &7

&''7 C1

Appl" the most recent change in data !in this case- asutraction o% 13#


28/34

&''& ;/

&'') 17

&''C /)

&'' /;

&''; /(

&''( 7'7

&''1 /)Predict: &''/ 45"#

If n % ) /(3'

If n % /;3'

Explanation:

PRA()I(E 8: ei-hted Mo+in- A+era-e

.se the weighted moving average method to predict*Numer o% F9 manu%acturers estimated %or the "ear &''/

,ear Mf-s

7/// 7'

&''' &7

&''7 C1

&''& ;/

&'') 17

&''C /)

&'' /;

&''; /( 7H&''( 7'7 )H

&''1 /) 'H

Predict: &''/ 4'"8

n % )

Explanation:

PRA()I(E !: =in-le Exponential =moothin-

.se the single exponential smoothing method to predict*


Primar" Factor* 1'H

Damping Factor* &'H

,ear Mf-s Prediction

7/// 7'

There are man" correct answers since diGerent valuesare acceptale %or n- %rom & to 7'3 n each case- it wille the average o% the preceding n data items3

There are man" correct answers since diGerent valuesare acceptale %or n- %rom & to 7'3 n each case- thereshould e a weighting %actor multiplied " the datum3

These %actors must add to 7''H3 t ma2es more senseto give more weight to the more recent items3


29/34

&''' &7 7'3'

&''7 C1 7131

&''& ;/ C&3&

&'') 17 ;)3;

&''C /) ((3

&'' /; 1/3/

&''; /( /C31&''( 7'7 /;3;

&''1 /) 7''37

Predict: &''/ 48"8

=moothin- $actor: '31

Explanation:

PRA()I(E ': Dou@le Exponential =moothin-

.se the doule exponential smoothing method to predict*


7/ ?773&

,ear Mf-s Predictio Error 9evel at th Trend at th

t > $ e ? )

7/// 7' ?773& 7/3'

&''' &7 (31 ?7)3& 7/3( 713(&''7 C1 )13C ?/3; C(3' 713

&''& ;/ ;3; ?)3C ;13( 713

&'') 17 1(37 ;37 173; 713;

&''C /) 7''3& (3& /)3( 713(

&'' /; 77&3 7;3 /(3; 7/37

&''; /( 77;3( 7/3( //3' 7/3

&''( 7'7 7713C 7(3C 7'&3( 7/31

&''1 /) 7&&3; &/3; /;3' &'3C

Predict: &''/ 11'"8 77;3C '3' 77;3C &'3C

Primar $actor: '37=econdar $actor: '3'&

Initial ?e+el: ?773&

Initial )rend: 7/

PRA()I(E: Measurin- $orecast Accurac

Determine the MAE- MSE- MAPE- and Theils . %or the %ollowing*


A %actor o% 31 was multipled " the previous data point-and 3& was multiplied " the previous prediction theresults were summed3 @owever- other examples thatused diGerent weighting %actors would have diGerentresults3


30/34

Error

,ear Mf-s $orecast e e eH2

7/// 7'

&''' &7 (31 ?7)3&' 7)3&' 7(C3&C

&''7 C1 )13C ?/31 /31 /731&''& ;/ ;3; ?)3C7 )3C7 773;;

&'') 17 1(37 ;37) ;37) )(3;)

&''C /) 7''3& (3&7 (3&7 &3'&

&'' /; 77&3 7;3C; 7;3C; &(73'(

&''; /( 77;3( 7/3(& 7/3(& )1131)

&''( 7'7 7713C 7(3CC 7(3CC )'C37'

&''1 /) 7&&3; &/3; &/3; 1()3(

&''/ 77;3C

Mean 7)3;C &C3'&Sum

MAE: 7)3;C

M=E: &C3'&

MAPE: &737H

)heilFs G =um of Numerators: &37;)7

)heilFs G =um of Denominators: )3771

)heilFs G: '31))&

A@s"Error

=quaredError


31/34


32/34


33/34

Forecast %or next datum3

$

(31

)13C;3;

1(37

7''3&

77&3

77;3(

7713C

7&&3;

77;3C

7);31


34/34

Numerator Denominator

e>

73(C&C 73&7''

;&3/H '3&'1) 73;)7

&'3'H '3''7 '37/7CC3/H '3''(/ '3')'&

(3;H '3''(/ '3'&7/

(31H '3')7) '3''7'

7(3&H '3'C&& '3'''7

&'3)H '3')&) '3''7(

7(3)H '3'1( '3'';)

)731H

&737H &37;)7 )3771

A@s"Percent

Error

practice with forecasting

Documents