8/2/2019 NumXL Cookbook - AirLine Model in Excel

1/10

NumXLTutorial 1 SpiderFinancialCorp,2011

TimeSeriesAnalysiswithNumXL

Inthisdocument,wewilldemonstratethefewstepstoconvertarawtimeseriesdataintoarobust

forecastusingNumXL.

Asanexample,weareusingthemonthlyelectricpowerdemand(inMWh)forthecityofJeddeh(Saudi

Arabia)between2003and2010.

0

20000

40000

60000

80000

100000

120000

140000

160000

Jan-03

May-0

3

Sep-0

3

Jan-04

May-0

4

Sep-0

4

Jan-05

May-0

5

Sep-0

5

Jan-06

May-0

6

Sep-0

6

Jan-07

May-0

7

Sep-0

7

Jan-08

May-0

8

Sep-0

8

Jan-09

May-0

9

Sep-0

9

Jan-10

May-1

0

Sep-1

0

DEMAND

Thetimeseriesinthefigureabovedemonstratesaseasonalityoftwelve(12)monthsandanupward

trendovertime.

Thisdatacanbeexplainedbybreakingdownthepowerdemandintoits'twoprimarycomponents:(1)

theindustrialdemand,and(2)theresidentialandbusinessdemand.Theresidentialandbusiness

demandisinfluencedbyweatherpatterns(e.g.season).Youcanseethisthroughtheincrease/decrease

useofelectricityforcooling/heating. Ontheotherhand,theindustrialdemandisratherflat.Asan

input,theindustrialdemandisdrivenprimarilybytheeconomicconditionsofthecityorindustry,and

toless

extent

by

weather

patterns.

8/2/2019 NumXL Cookbook - AirLine Model in Excel

2/10

NumXLTutorial 2 SpiderFinancialCorp,2011

Step1:SummaryStatisticsUsingNumXLtoolbar,launchthedescriptivestatisticsWizard.

Forourillustration,wewillkeepalloptionsselected. Foroutput,selectthecellinyourspreadsheet

whereyouwishthetabletobewrittento:

DescriptiveStatistics SignificanceTest 5.00% Test pvlaue Result?

Targe t pvlaue Different? Whitenoise 0. 00% F ALS E

AVERAGE: 99004.77 0.000 0.00% TRUE NormalDistributed? 19. 85% TRUE

STDDEV: 26026.9 ARCHEffect? 0. 00% TRUE

SKEW: 0.03 0.000 45.76% FALSE

EXCESSKURTOSIS: 0.88 0.000 3.63% FALSE

MEDIAN: 98989

MIN: 47772

MAX: 149954

Q1: 77524

Q3: 117380.5

8/2/2019 NumXL Cookbook - AirLine Model in Excel

3/10

NumXLTutorial 3 SpiderFinancialCorp,2011

IMPORTANT:Inthetableabove,NumXLgeneratestheformulasintoyourspreadsheet.Youcaneasily

reviewthecalculation,and,ifneeded,makeanyproperchanges.

Step2:CorrelogramAnalysisUsingtheNumXLtoolbar,launchtheCorrelogramWizard:

Forthisillustration,wewillcomputeandplottheACFandPACFforfirst20lags.

8/2/2019 NumXL Cookbook - AirLine Model in Excel

4/10

NumXLTutorial 4 SpiderFinancialCorp,2011

TheACF

plot

demonstrates

aclassic

case

for

the

AirLine

model

with

acycle

length

of

12

steps.

Step3:DataTransformationThereisnoneedtotransformthedatabeforemodeling.TheAirLinemodelwillstabilizetheinputseries

throughthedifferencingtermsinitsdefinition.

8/2/2019 NumXL Cookbook - AirLine Model in Excel

5/10

NumXLTutorial 5 SpiderFinancialCorp,2011

Step4:ModelingUsingNumXL,themodelingprocessconsistsof3majorsteps:(1A)modelspecification,(2B)calibration

and(3C)residualsdiagnosis.

1A:ModelSpecificationClicktheAirLineIconontheNumXLtoolbar.

NumXLwillprepareatableinyouspreadsheetwithmodelscoefficient,goodnessoffitandresidual

diagnosisroutines.Note:thevaluesofthemodelarenotoptimalyet.

8/2/2019 NumXL Cookbook - AirLine Model in Excel

6/10

NumXLTutorial 6 SpiderFinancialCorp,2011

2B:ModelCalibrationSelectthecellwherethemodeltablestartsfrom(i.e.AIRLINE(12)).Clickonthecalibrationiconinthe

toolbar.

TheMS

Excel

solver

dialog

will

pop

up

on

your

screen

with

your

model

specifications

filled

in.

Click

the

Solvebutton.

IMPORTANT:InExcel2010,thesolverhasanonnegativeconstraintturnedonbydefault.Theconstraintrestrictsthemodel'scoefficientstononnegativevalues.Asaresult,thesolveryieldsaless

thanoptimalsolution.Theconstraintisnotrelevantforthemodelcalibrationprocess,andshouldbe

turnedoff.

UncheckthisoptionbeforeyouclickSolve.

8/2/2019 NumXL Cookbook - AirLine Model in Excel

7/10

NumXLTutorial 7 SpiderFinancialCorp,2011

Theoptimizerlocatesasetofcoefficientvaluesthatfitsourdatabest,anddefinesastableAirLine

model.

8/2/2019 NumXL Cookbook - AirLine Model in Excel

8/10

NumXLTutorial 8 SpiderFinancialCorp,2011

3C:ResidualdiagnosisTheresidualsdiagnosisroutinesarealreadypopulatedinyourspreadsheetwithyourmodelparameters.

Nowisagoodtimetoreexaminewhethertheoptimalmodelsatisfiestheunderlyingassumptions.

Step5:ForecastingToperformaforecastusingNumXL,selectthecellofthemodelstable(i.e.AirLine).Clickonthe

Forecasticoninthetoolbar.

Theforecastwizardwillpopup.Selecttherealizedobservations(i.e.tailpointsofyoursampledata),

andtheforecasthorizoninunitsofsteps.

8/2/2019 NumXL Cookbook - AirLine Model in Excel

9/10

NumXLTutorial 9 SpiderFinancialCorp,2011

Forthisillustration,thelast23observationsinthesampledatawerechosen, thentheforecasthorizon

wassetto30steps(orunits),asshownabove.

Plottheforecastanditsconfidenceintervals.

8/2/2019 NumXL Cookbook - AirLine Model in Excel

10/10

NumXLTutorial 10 SpiderFinancialCorp,2011

0

20000

40000

60000

80000

100000

120000

140000

160000

180000

200000

Jan-11

Feb-11

Mar-11

Apr-11

May-11

Jun-11

Jul-11

Aug-11

Sep-11

Oct-11

Nov-11

Dec-11

Jan-12

Feb-12

Mar-12

Apr-12

May-12

Jun-12

Jul-12

Aug-12

Sep-12

Oct-12

Nov-12

Dec-12

Jan-13

Feb-13

Mar-13

Apr-13

May-13

Jun-13

Jul-13

Aug-13

Sep-13

Oct-13

Nov-13

Dec-13

UL

LL