Scribd
Upload
620 views5 pages

Time Series Simulation Tutorial in Excel

In this tutorial, we’ll demonstrate the steps to construct one 12‐month sales simulation using an ARIMA(5,1,1) model. Next, using the simulated 12‐month (step) values, we’ll compute their median as an example of a target (derived) value. Finally, using the “Run” functionality, NumXL will re‐evaluate the spreadsheet (creating a new simulation path each time) and collect the target values in a separate column in our spreadsheet. Once done, we are left with several potential values for the median of the following 12 sales months and may start analyzing them as we see fit. For more information, example file, tutorial video or download NumXL free trial, visit us at: http://bitly.com/numxl-userguide-simulation

Uploaded by

NumXL Pro
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
620 views5 pages

Time Series Simulation Tutorial in Excel

In this tutorial, we’ll demonstrate the steps to construct one 12‐month sales simulation using an ARIMA(5,1,1) model. Next, using the simulated 12‐month (step) values, we’ll compute their median as an example of a target (derived) value. Finally, using the “Run” functionality, NumXL will re‐evaluate the spreadsheet (creating a new simulation path each time) and collect the target values in a separate column in our spreadsheet. Once done, we are left with several potential values for the median of the following 12 sales months and may start analyzing them as we see fit. For more information, example file, tutorial video or download NumXL free trial, visit us at: http://bitly.com/numxl-userguide-simulation

Uploaded by

NumXL Pro
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

TutorialSimulations 1 SpiderFinancialCorp,2014

Simulation Tutorial
Inthistutorial,welldemonstratethestepstoconstructone12monthsalessimulationusingan
ARIMA(5,1,1)[Link],usingthesimulated12month(step)values,wellcomputetheirmedianas
anexampleofatarget(derived)[Link],usingtheRunfunctionality,NumXLwillreevaluatethe
spreadsheet(creatinganewsimulationpatheachtime)andcollectthetargetvaluesinaseparate
[Link],weareleftwithseveralpotentialvaluesforthemedianofthe
following12salesmonthsandmaystartanalyzingthemasweseefit.
(1) Selecttheuppercellofagivenmodeltable.

(2) LocatetheSimulationiconintheNumXLtoolbar(ormenuinExcel2003)andclickit.

(3) TheSimulationWizard(dialogbox)[Link]
theactive(selected)cellinyourworksheet.

TutorialSimulations 2 SpiderFinancialCorp,2014

(4) Now,selectthelatest(orthewholeset)[Link],we
chose35observations.

(5) IntheHorizonfield,wechose12stepsforone12monthsimulationpath.
(6) Leavetheseedto100.
(7) IntheOutputRangefield,selectanemptycellinyourworkbooktostorethesimulation
path.
(8) ClickOK.

Wehavenowmanagedtoconstructone12monthsimulationpathforanARIMA(5,1,1)modelandthe
latestobservationsinourdataset.

Next,letsassumewewishtocalculateavaluethatusesoneormoreobservationsinthesimulation
[Link],[Link]
onesimulationpath,wecomputeonlyonevalue.HittingF9willcauseNumXLtogeneratenewvalues
forthesimulationpath,forcingreevaluation,andthereforegeneratinganewtargetvalue.

Whatifwewanttouse100simulationpathsandcollectthetargetvalueofeachpath?Wecaninstruct
theSimulationWizardtogenerate100paths,[Link]
enough,butwhatabout10,000simulationpaths?

TutorialSimulations 3 SpiderFinancialCorp,2014

Wecanmakethissimplerbygeneratingonesimulationpath,calculatingthetargetvalue,thenusingthe
[Link]
[Link]:
(1) Selectanemptycellinyourworksheetwhereyouwishtheoutputtobedisplayed.
(2) LocatetheRuniconintheNumXLtoolbar(ormenuinExcel2003)andclickit.
(3) [Link],theoutputreferencestheactive(selected)cell
inyourworkbook.

(4) Next,selectthecellforthetargetvalue.
(5) Now,weneedtodecidetheextentoftherecalculationneededtoevaluatethetargetvalue
[Link],wellchooseActiveWorksheet.

Note:Ifyouareunsureorifthecalculationspansmultipleworksheets,selectcurrent
workbook.

TutorialSimulations 4 SpiderFinancialCorp,2014

(6) Finally,setthenumberofsimulationstoavalue(lessthan30,000)forareasonable
performance.Wellchoose100(default)forthistutorial.
(7) ClickOK.
(8) TheRunWizardgeneratesthevaluesforeachsimulationrun.

(9) Thecomputedtargetvaluesofthedifferentsimulationrunsarestatisticallyindependent,soyou
maywishtorunsummarystatistics([Link],standarddeviation,etc.)orplottheirempirical
distribution.
(10) Forthistutorial,wechosetoahistogramplot:

Runnormalitytest.

TutorialSimulations 5 SpiderFinancialCorp,2014

Q1:DoIneedtousetheNumXLsimulationfunctionstousetheRunWizard?
A:No!IfyouhitF9andyourtargetcellvalueisrefreshed,thenyoucanusetheRunWizardforanytype
ofsimulation.

You might also like