User Guide proForecaster 2011 R1
Contents
1. The further you forecast into the future the greater is the likelihood that events will diverge from past behavior and the less confident you can be of the predictions proForecaster provides confidence intervals for forecasts that help to gauge how reliable predictions are With a 90 confidence interval you can be sure that with a certainty of 90 the prediction will lie between the bounds indicated by proForecaster QUICK START Figure 3 Main Menu MAIN MENU The proForecaster menu is located in the Add ins tab in the Microsoft Excel ribbon Home Insert Pag e m V a Welcome an 7 Q Help Q Info proForecaster Forecast Regression FORECAST Opens the Forecast wizard This wizard helps you to forecast time series data REGRESSION Opens the Regression wizard This wizard helps you to create forecasts based on one or more variables that influence the variable that shall be predicted WELCOME Opens the Welcome screen The Welcome screen provides introductionary information and links to resources that may help you to work with proForecaster HELP Opens the Help menu where you can find the User Guide Tutorials and Support in case you need our help INFO Opens the nfo menu where you can find information about the proForecaster version and the status of your license FORECASTING TIME SERIES DATA Click in the main menu on the Forecast command and the Forecast wizard to2. hidden layer the higher the risk of overfitting Some studies suggest that the amount of neurons in the hidden layer is a function of the number of neurons in the input layer NUMBER OF NEURONS IN THE OUTPUT LAYER This is the easiest selection since we use the NN to make a one step ahead forecast which means the output layer has a single neuron proForecaster always trains the NN to make a one step ahead forecast through a sliding window approach ACTIVATION FUNCTION proForecaster offers four activation functions Bipolar Sigmoid Sigmoid Hyperbolic Tangent Semi linear TRAINING A NEURAL NETWORK Before a NN can be used to forecast a time series it has to be trained on its historical observations For each NN you want to forecast you need one trained NN The general idea of training a NN is to present it with data from the time series such that it can learn the data pattern in a way that it can generate predictions Please note that each time a NN is trained on one time series with identical parameters the NN may lead to different forecasting results That is due to the random initialization of the neurons each time the neural network is trained Therefore even with identical parameters two NNs trained on the same time series may lead to slightly different forecasts AUTOMATIC TRAINING Through its Automatic forecasting mode proForecaster provides the possibility to use NNs for predictions without requirin
3. predict time series data will be shown The wizard will guide you through the forecasting process in four steps SELECT DATA First you need to select where your data is located Click into the range field and select the range on the excel sheet or click on the button highlighted in dark red and another window will open asking you to Select the range containing the data Click into the Excel sheet and select the range of your data In the example the sales data in F2 F26 was selected where F2 contains the header for the time series x p E Welcome QHep TY Forecast Predict time series data proForecaster 2011 R1 Professional 30 day trial Ed Forecast Regression mo proForecaster 4 Sales E gt Select Data Step 1 Select Data L G D E aora Select the range that contains the data View Forecast Result Select Report Options SFS2 SFS26 1997 1 330 z ES Click inside the box and mark the range in Excel or use the button to the right E En Is the data organized in columns or rows 4 300 1998 1 378 Columns 3 A B gt 3 1 3 408 Rows 3 4 341 First column row has header 1999 NY 354 2 329 V Has headers E m Select columns if the data is vertically arranged or rows if otherwise 4 303 2000 1 358 Is the data seasonal 2 317 V Yes seasonal quatety other seasonal period 2 3 402 4 299 For instance choose quarterly if
4. 6 proBs proForecasier Professional forecasting software for Excel User Guide prakorecasier 2011 R1 User Guide proForecaster 2011 R1 Copyright 2011 pro BS UG haftungsbeschraenkt amp Co KG All rights reserved The program which includes both the software and documentation contains proprietary information it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright patent and other intellectual and industrial property laws Reverse engineering disassembly or decompilation of the program is prohibited proForecaster references the AForge NET framework and the Accord NET framework both licensed under GPL v3 The information contained in this document is subject to change without notice If you find any problems in the documentation please report them to us in writing This document is not warranted to be error free The program is not intended for use in any nuclear aviation mass transit medical or other inherently dangerous applications It shall be the licensee s responsibility to take all appropriate fail safe backup redundancy and other measures to ensure the safe use of such applications if the program is used for such purposes and we disclaim liability for any damages caused by such use of the program Microsoft Excel is a registered trademark of the Microsoft Corporation in the U S and other countries Other names may be trademarks of thei
5. SEASONAL TREND METHOD This forecasting model is a three parameter model proposed by Holt and Winter to model data with a multiplicative seasonal trend effect DAMPED ADDITIVE SMOOTHING Damped additive smoothing is based on the additive seasonal trend method by Holt and Winter and introduces a damping factor into the forecasting function This model is useful when the trend should be modeled with a certain degree of conservatism DAMPED MULTIPLICATIVE SMOOTHING Damped multiplicative smoothing is based on the multiplicative seasonal trend method by Holt and Winter and introduces a damping factor into the forecasting function This model is useful when the trend should be modeled with a certain degree of conservatism LINEAR GROWTH Linear growth is a simple linear regression against time where the sum of the squared residuals is minimized QUADRATIC GROWTH Quadratic growth applies a quadratic regression function to the data POLYNOMINAL GROWTH Polynominal growth is the general growth function proForecaster supports the application of a polynominal function of a degree up to ten NEURAL NETWORKS Neural networks NNs are a powerful and flexible forecasting paradigm NNs can be used for a variety of forecasting problems proForecaster uses NNs to model time series data to generate forecasts This section introduces NNs and how proForecaster uses NNs to create predictions FROM BIOLOGY TO FORECASTING NNs mimic the dat
6. fact the more variables we add the more information can be used to predict the target variable However this better explanation may be only due to pure chance Adjusted R accounts for this and only increases if the new variable added is providing information that would not be expected by chance MODEL SIGNIFICANCE If the regression model is significant can be determined by the F Test that is shown in the Model Statistics view proForecaster automatically interprets the F Test statistic and comments whether the whole model is statistically significant or not VARIABLE SIGNIFICANCE proForecaster shows whether a variable is statistically significant by displaying the p Value If the p Value is lower than 0 05 than the variable is significant with a confidence of 95 proForecaster uses the alpha value specified in the Regression Options view to determine the significance REGRESSING TIME SERIES DATA proForecaster can automatically generate time series predictions for the independent variables and insert them into the regression function This helps to short cut the forecasting process Select Use time series forecasts in the regression and enter the number of periods you want to forecast r y Regression Predict a variable through explanatories proForecaster 2011 R1 Professional 30 day trial bab f Select input data Step 2 Select Variables Select E Select the variab
7. mode you do not have to worry about the technical details of selecting a specific forecasting model and choosing its parameters proForecaster will do that for you 2 Manual mode You can select which forecasting models should be applied to the data and which model parameters proForecaster should use proForecaster Professional offers three forecasting approaches to forecast time series data 1 Smoothing models and growth function 2 Artificial neural networks 3 A hybrid method where a smoothing model and a neural network are combined to produce forecasts After the range of models that should be applied to the time series has been selected four different ranking methods are available to rank each forecasting model proForecaster provides three commonly used error measures and an Expert ranking method Expert Ranking Root Mean Square Error RMSE Mean Absolute Deviation MAD Mean Absolute Percent Error MAPE All forecasting models that were tested on the data are ranked according to the ranking method selected and the best model which will yield the most accurate forecast is placed at position one in the ranking The final forecast shows the most likely continuation of the data But keep in mind that all forecasting models no matter how sophisticated they are depend on the assumption that the pattern found in the data will continue into the future That means that history will repeat itself to a certain degree
8. nnne nn nennen en nnne enne 38 SHOW TADO Wai te act AAA wal AAA A cies AAA AAA a id 39 lcg 40 HoWito change the Series tactica aid ne n Pe REP ee epe e ha Poe ke tok Ee ere ado ee Ene dad 40 How to change the forecast model sineren e enat ee aa ai E EE a i a N aa i de 40 How to change the chart View wose e cr es coeds couse Gate a a Ieee teen EE OR did 40 How to hide a series in the forecast plot o o occccccoconoooonnonononononnnnnononnnanononnnnncnncnnononnnnnnnnnnnononnnnncnnannnonnnnnos 40 How to zoom into the chart eto rer pra cel E EEEE E AE Ea E XR AER TERRE aiot 41 How to adjust forecasts ince riter io 41 CDU A IDIOT DOLI ILI 42 PROFORECASTER WELCOME Welcome to proForecaster the forecasting software which adds advanced time series forecasting functions to Microsoft Excel proForecaster is an Add In for Microsoft Excel and can be easily used inside the Excel environment to produce time series forecasts for all kind of time series data proForecaster helps you to create time series predictions fast and without statistical know how This user guide will introduce you to time series forecasting and how to use proForecaster Professional to generate accurate and reliable predictions Further information about proForecaster can be found on the proForecaster website www proforecaster net INSTALLING PROFORECASTER You can download proForecaster from www proforecaster net proForecaster is available in two edi
9. regression function E Figure 10 Select Hele s lt Back J Next gt gt Finish Variables proForecaster supports three Regression options 1 Include all Explanatory Variables This is the standard regression method where all variables contained in the Explanatory variables list are used to construct the final regression model 2 Stepwise Forward Regression This regression method starts with a model containing no variable at all and adds one variable after another to the model At each run the variable which does the best job of estimating the target variable is included in the model Use this regression method if you have a lot of potential explanatory variables and want to keep the final regression model as simple as possible 3 Stepwise Backward Regression This regression method starts with a full regression model containing all explanatory variables that are listed in the Explanatory variables list and removes them one after another until a final model is constructed that includes all important explanatory variables Figure 11 Regression Result In case the variable to be predicted is a time series variable which means it has been recorded over equally spaced time periods you can select the Use Time Series forecasts in the Regression option This option will automatically generate time series forecasts for the explanatory variables These forecasts will be used in the final regression model to predict the targe
10. significance p Value 0 036 lt 0 05 model is significant good E Le Varishle sinnificance sll vsrishlae sra eianifiesnt N 1 nnd r3 Cs Figure 25 Forecast Plot Regression SHOW FORECAST PLOT Displays the target variable the fitted values created through the regression model and the forecasts This chart is only available if the Use time series forecasts in the regression is chosen Y Regression Predict a variable through explanatories proForecaster 2011 R1 Professional 30 day trial A ef Select input data Step 3 View Regression Result Y Sipiectienebles Forecast Plot Distance P View regression result Report options 2M 150 inti zo M 100 1 50 0 T 012345267 B 9 1011 12 13 14 15 16 17 18 19 20 MMM Distance E Fitted MN Forecast Model Statistics Variable Statistics Stat Value Predictor Coeff SECoeff T Stat PValue 95 Ad R2 0 6 intercept 22 326 38 078 0 586 0 569 10 R2 0 67 Hang 43 501 9 643 4511 0 001 221 Std Er 16 33 FTest 2 04 LBQ 2472 Obs 19 m D Model Summary Moderate R2 0 67 0 5 ok E Variable significance all variables are significant at 0 1 good 3 s Recidiusl sitnanrralatinn RO 24 70 20 14 na sitannrralstinn nand a Fish LS The residual histogram shows the distribution of the residuals This graph helps to indentify whether the residuals show a normal dis
11. the data has been recorded for each quarter 2001 1 340 2 303 3 387 4 305 2002 1 360 2 346 Ha E a ta Figure 4 Select Data After you selected the range containing the time series data select whether the data is organized in columns or rows In our case we have just one time series which is arraged in a single column Note that proForecaster automatically detected that the first row of data contains a header Select or deselect this option if necessary Tip If you have more than one time series arrange your data either in rows or columns and select the Columns or Rows option Do not use discontinous data that means blank rows or columns proForecaster will give an error message if a blank cell is encountered in the data range Figure 5 Select Sesonality Next proForecaster asks you about the properties of the data in particular if the data exhibits seasonality In our case the sales data was recorded for each month so check Yes seasonal and choose the kind of seasonality rp WW Forecast Predict time series data proForecaster 2011 R1 Professional 30 day trial Le aj E Select Data Step 1 Select Data Select Forecast Options Select the range that contains the data View Forecast Result Select Report Options F 2 F 26 Click inside the box and mark the range in Excel or use the button to the right Is the data organized in columns or rows Columns i A B c Rows 3 First column row has
12. 24 very good 3 Low MAPE fitted of 0 043 very good a Figure 23 Forecast s Enranastinn sons tom ited LORS H Help lt lt Back Next gt gt Finish Values View pl j 35 Figure 24 Residual Plot Regression REGRESSION proForecaster provides five different charts on the regression result view SHOW RESIDUAL PLOT Displays the residuals of the current regression model This chart is useful to determine whether a structural part is present in the residuals An assumption of a linear regression model is that the residuals are randomly distributed r X A Regression Predict a variable through explanatories proForecaster 2011 R1 Professional 30 day trial icz isis le Sl f Select input data Step 3 View Regression Result f Select variables E View regression result Residual Distribution Renortoptons I E E B oS l Qoo Unusual data point 90th Percentile 75th Percentile UQ Mean al 25th Percentile LO H oss y i 1 10th Percentile Xx 4 4 8 d E wd Model Statistics Variable Statistics Stat Value Predictor Coeff SECoef TStat PVaue 95 AdR2 072 intercept 47 459 55606 0853 0412 17 i R2 07 RRe 2224 0902 2466 0031 0 1 StdEr 13 86 LRex 0 688 0 65 1059 0312 21 FTes 3 68 ost 0233 0 117 194 0071 44 LBQ 1255 HObs 19 am x Model Summary E High adj R2 0 72 gt 0 7 good a l Model
13. 500 v d 2000 1500 1000 500 H 0 Training Validation Forecast 0 5 10 15 20 25 MM column 5 MH Fitted MN Forecast Series J Series Column 5 v Adjust forecasts J Method Best Holt amp Winterfor Mut v Override best model Method Statistics Fitted Method Parameter Statistic Value Statistic Value Para Value Para Value RMSE 64 328 MAD 49182 Apha 0 319 Beta 1 MPE 0024 MAPE 004 Gamma 0 625 Damp Theis U 0 307 LBQ 9 067 Model Summary Low MPE fitted of 0 024 very good 3 Low MAPE fitted of 0 043 very good le Enranzotina annnran fad nf OR A A Figure 21 Residual Plot SHOW RESIDUAL PLOT Displays the residuals of the current forecasting model This chart is useful to determine whether a structural part is present in the residuals Good forecasting models produce randomly distributed residuals r Y TY Forecast Predict time series data proForecaster 2011 R1 Professional 30 day trial camy f Select Data Step 3 View Forecast Result f Select Forecast Options f View Forecast Result Distribution Select Report Options 30th Percentile i 75th Percentile U0 Mean 25th Percentile LO 10th Percentile 55652 8 9 4 04104730454 4 4 12 72 Series I Series Column 5 y 2 Adiustforecasts i Method Best Hok amp WinterforMut Overidebestmode Method Statistics Fitted Method
14. Error 13382 563 PQ 1 547 Training Error 3000 zu Epoch 245 ERI MSE 5997378 Error 0 024 10001 5001 Save NN for prediction M 5 10 15 20 Start training Figure 16 Manual uM Training Cine L d In the Manual train Neural Network view you can select for which time series you want to manually train a NN Select the NN architecture i e the number of neurons in the input and hidden layer see Neural Network Design Furthermore select for how many epochs you want to train the NN If no stopping condition is applied to NN training proForecaster will train the NN until the last epoch finished LEARNING RATE The learning rate of a NN controls how quickly the weight of the neuron changes A too high learning rate may lead to large oscillations of the NN Whereas a too small learning rate may lead the NN to be stuck in a local minimum and not finding the lowest forecasting error possible MOMENTUM The momentum controls the tendency of a weight to change the direction That means that each weight remembers the weight where it comes from and if it increased or decreased and the momentum tries to keep the weight in that direction With a small momentum weights are allowed to change more freely whereas a high momentum forces the weight more into a certain direction Thus a NN responds more slowly to new training data OVERFITTING A NEURAL NETWORK As already mentioned a NN can ap
15. Parameter Statistic Value Statistic Value Para Value Para Value RMSE 64 328 MAD 49 182 Alpha 0319 Beta 1 MPE 0 024 MAPE 0 043 Gamma 0 625 Damp Theis U 0 307 LBQ 9 067 Model Summary Low MPE fitted of 0 024 very good a Low MAPE fitted of 0 043 very good E Enranaotino aaniran fitted nf OR Help Finish x SHOW RESIDUAL CORRELATION PLOT Displays the autocorrelation coefficients at different lags of the residuals of the current forecasting model This chart helps to determine whether the forecasting model is a good fit to the data High autocorrelation coefficients indicate that a data pattern is present in the time series that is not adequately captured by the forecasting model The autocorrelation coefficient can be in the range of 1 to 1 An autocorrelation coefficient of 0 6 for a lag can be an indication of autocorrelation f Select Data Step 3 View Forecast Result Y n Fon Pu os Residual Autocorrelation Plot Holt Winter for Mult Seasonal View Forecast Result Select Report Options of 0 2 m Him 7 m 0 2 0 4 i 0 5 10 15 Lag Will Autocorrelaton Series Series Colum 5 C Adiustforecasts Method Best Holt amp Winterfor Mut Override best model Method Statistics Fitted Method Parameter Statistic Value Statistic Value Para Valu
16. ThiNB cccoccococonocncnonononannnonononanononnnnncnnnanenonnnnnnnncnnenonnnnnnnnnnnononnnnnnnnannnnnnnnnos 23 Linear GrOWwth LEE 23 Quadratic growth ia edad 23 Polynorminal growtlhi iere eee rore rer e En eod AAA AAA AA ese Ee AA ea vo AAA in 23 N ur l ras 24 From biology to forecasting ccccecsssccececseseeesecececeesesaeseeececeesesaeeesececeesesassesecsceeeeseseeseesceesesaeaeseesceesenaaeees 24 Neural rietwork d sigri niente deer ee Peto aeree EP re ane Pen Ra Doe canina sado cab Pee ae rye eR TERES 25 Number of Input NeUrons une eR eere rex Gate Pee e RO Head ues i eee teen REO Ber du tes E o uen 25 Number of Neurons in the Hidden layer eene enne nnne ne nnns ener tana assess nana s 25 Number of Neurons in the Output Layer ccccccssessssscecececsesseececececseseeaeeecececeeeesesseseesesesesaeaeseesceeseenaaees 26 ACTIVATION FUNCTION e E enere k 26 Training ac ne ralinetWork 2 rtr rte a PERS RA decd Cb E PERRA ES 26 Automatic training eee enero A ii ninia 26 Manual trainin E 27 Learning Ri A A tes 27 MONO O EE EE ia 27 Overtitting a neural NetWork cocida tin da EET 28 PQ Threshold iiie dir ait atre d re en be e Po ESPERE eo REDE eR ERE ER e Ra Ree EE ORAE eM EE ER Pen 29 Stripz is aec eoe Dee e DD ce ei e NE 29 Hybrid forecasting 2 A O uasa re piae 29 Understanding regression ioi iis oen oo say ooa ehb Nido doy Ra NE oa hb LI ea ERAI SPARE M EE DER INN aeuo ETE SE
17. W TO ADJUST FORECASTS Forecasts can be directly adjusted inside the Adjust Forecast dialog You can adjust the forecasts by a certain value You can round the values and set lower and upper bounds r Yt Adjust Forecast Series Column 5 Model Holt Winter for Mult Seasonal Adjust forecast Adjust forecast by value 0 00 E Round forecast to 2 E Restrict value range to Max 3953 09 Min 2121 44 E REFERENCES Bishop Christopher M 2006 Pattern Recognition and Machine Learning Springer 2006 Hanke John E et al 2001 Business Forecasting 7 Ed Prentice Hall International Inc 2001 Linoff Gordon S and Berry Michael J A 2011 Data Mining Techniques 3 Ed Wiley Publishing Inc 2011 Makridakis Spyros et al 1994 Forecasting Methods and Applications 2 Ed John Wiley amp Sons 1994 Pegels Carl C 1969 Exponential Forecasting Some New Variations Management Science Vol 12 No 5 p 311 315 1969 Ragsdale Cliff T 2004 Spreadsheet Modeling amp Decision Analysis A Practical Introduction to Management Science 4 Ed Thomson South Western 2004 Rumsey Deborah 2007 Intermediate Statistics for Dummies Wiley Publishing Inc 2007
18. YR A Roa Sek 30 Linear tegressiOfi A doen Enna tran eta een eed a sua ge e FE Gen aU eg ea an e uceuscstseaevsdsuceastecsdsavsdsudessss 30 How to assess a good regression model eee eeee esee ee ee eese eene e seen nnns ente ns sesta nsss nsn 30 User Guide proForecaster 2011 R1 Adj steq2 a ade 30 M delsienificance ii O AS Ne eds 30 NAME SISMITICANC Cs A EE AE EA EEES 31 Regressing time series data 5 rri iret ii erret taa scesdes seaatecscastescseestiesseasiad sosasacseeaasessseas s 31 CHALE VICWS A 33 Time series forecasting irera p naco E ete erronee PER ete eoa eene oa etae 2o M eene DE se en oe ipee p oo d eto e AS 33 Show Forecast lO inodoro nere oet ober ed ro sept reo rne vag eo snes lo coeds nena AA REA EEEE aia 33 Show Residual Plot avance esca 34 Show Residual Correlation Plot ooooconcconoccconccononnnnnnnononnnnnn nono cnnnnnnnnonnnnn nero nnne nen nnne tentent nnne tenen nennen nens 34 Show Forecast Value atop Ga oet n e e eate b ru bee ee debusheAlasbesunvertetssans Adds 35 A m 36 Show Resid al Plot A AE AA A ee adobe e an BILE Tes 36 Show Forecast Plot AN 37 Show Residual HistOgram cccsscccccecsessssssecececsesesesecececeeseeeesececeeseeeeeseeececeeseeaeseeseeceeseeaeseeeesceesesasaeeeeeees 37 Show Residual Autocorrelation esee eene enne ene en nnne en nnnt
19. a processing functionality of the human brain The human brain consists of billions of neurons that are interconnected Each neuron is used to store a tiny amount of information and by interconnection large and complex information can be stored and processed For time series forecasting an artificial neural network is created which takes a number of historical observations as input to predict a value one step ahead Several thousand runs are performed to update the weights with which each input influences the one step ahead prediction The objective is to minimize the overall forecasting error NNs possess an interesting property which makes them especially suitable for time series forecasting NNs do not assume a certain underlying data generation process of the time series Unlike smoothing models NNs are not bound to data patterns such as trend or seasonality NNs learn the pattern directly from the data and can model even non linear data patterns The interdependencies in our complex world are seldom linear in fact non linearity is found in many business forecasting problems A NN can approximate any linear and non linear continuous function to any level of accuracy which makes them a must have in forecasting software proForecaster uses a three layer neural network consisting of an input a hidden layer and an output layer The NN is trained through backpropagation where at each training epoch the information coming from the input neurons
20. alue Predictor Coeff SECoeff T Stat PValue 95 Ad R2 0 6 Intercept 22 326 38 078 0 586 0 569 10 R2 0 67 Hang 43 501 9 643 4511 0 001 224 Std Er 16 33 F Test 2 04 LBQ 2472 HObs 19 7 m T Model Summary Moderate R2 0 67 0 5 ok 8 Variable significance all variables are significant at 0 1 good Er le Recidusl autannrralatinn RA JA 79 lt IN 14 na sitannerelatinn anad Fash 39 FAQ HOW TO CHANGE THE SERIES In the Forecasting Result view change the value in the Series dropdown HOW TO CHANGE THE FORECAST MODEL In the Forecasting Result view change the value in the Method dropdown HOW TO CHANGE THE CHART VIEW Right click into the chart area and the context menu will be shown WY Forecast Predict time series data f Select Data Step 3 View Forecast Result f Select Forecast Options of View Forecast Result Select Report Options Forecast Plot Double Moving Average S ae t os 1 P PT id v Show Forecast Plot ANS Show Residual Plot 0 Training Show Residual Cor 0 10 Show Forecast Values MM Sales N Fitted MF Copy Series Series Sales x Adjust forecasts Method Best Double Moving Averac v Override best model Method Statistics Validation Method Parameter Statistic Value Statistic Value Para Value Para Value RMSE 1777433 MAD 1483 75 Apha Beta MPE 0 055 MAPE 0 061 Gamma Damp T
21. asting important enrica RIEF ee en Ero Eee Fee E ECT E FER Ra CERTE ea rad iS EEEn E REF CER redd 4 proForecaster Professional 22 5 0 anos orae suos oath Saa Suto ERR aaa SERE RR RR RE SNS RASSE SAEs SRRasa ae 4 QUICK duci fMI 7 Main MenU ee ii cOn 7 e RN 7 Regression ON 7 Tourer 7 ls M 7 opo ER RES 7 Forecasting time series data eee oerte e EE eoa nee ER eaa SR neon ERE qu NER Pa Ee YE REN NANNES 8 a e LONE LIIS 8 FOE CASTING OPTIONS MERECE 9 OPE CASES ii 12 REDO OPEIOINS ANREDE uem 13 User Guide proForecaster 2011 R1 Forecasting Multivariate Data ccsssccccssssscecssssceccssssceccessecsccessessscesssssseesssssseesssssseessseeeoes 14 MI CHa it aia AAA AAA AAA A AAA AAA ARALAR Ra Ad 14 Select variables itii ee tior ii taa 14 Regressiomresulp z5 exo D e e e ERE e eee e ARN EG Ee NR D ERU E ku ed 16 REPONE ODEON ANSEHEN EE 17 Understanding Time series FOrecAsting sccscosssscccossssccccssssccccsssscsccssssscccssssssccssssssccesssssccessssscoessses 18 A good fitting model NOA 18 Time series accuracy measUtes 2 00 ee ee cc eo ue ua oce anas da ee RR RED RE RE OD P BERE EE RE RED PRESE RR ODMReRRR PAR era nos 18 AMES 18 MAD i Lice titor u hoe tipo noe rate aos vr eame Pau
22. ation such as the status of the license and for whom the license is activated KE P License Info proForecaster 2011 Beta x r License Manager proBS Product proForecaster 2011 Beta Version 1 0 4395 16862 License Information License Status Professional Edition Single License This license is activated and registered Registered to John Smith patrick schubert pro excel development com Click on the Enter License button and the Enter License dialog will open Figure 2 Enter License 3 Enter License 3 Enter License Information License Key In the License Key field enter the license key that was provided to you via email Click on the Activate button and a connection to the pro BS license server will be established Please note that a Single License can be activated on a single computer as often as needed in case you installed the software anew After successful activation your license is registered FORECASTING BASICS TIME SERIES FORECASTING Time series forecasting is an approach to predict future outcomes based upon historical data whereby different models are applied to the data in order to find the one which best captures trend and seasonal patterns Historical data can be anything from quarterly sales recordings to daily stock prices proForecaster can be used to predict for example Inventory Stock prices Oil amp Gas prices Sale
23. cast Series Series Sales y 2 A iustforecaste Method Best Damped for Multi Sea v Override best model Method Statistics Fitted Method Parameter Statistic Value Statistic Value Para Value Para Value RMSE 21 444 MAD 17 704 Alpha 0 143 Beta 0 006 MPE 0 006 MAPE 0 051 Gamma 0 496 Damp 0 014 Theis U 0 338 LBQ 12 037 LBQ lt 12 037 no autocorrelation present very good a RMSE validation 23 035 MAD validation 19 74 lr MADE himlidatinmi N NRA Help Back Net Finish L d proForecaster automatically ranks the forecasting models according to the ranking method that was chosen at the previous step Each forecasting model that was applied to the data can be selected through the Method dropdown In case you select more than one time series all time series are listed in the Series dropdown The Method Statistics view displays the main statistics for the selected forecasting model Note that these statistics are computed based on the fitted forecasts The Method Parameter view shows the parameters associated with the selected forecasting model These parameters were either automatically determined by the Expert Forecasting Engine or you entered them in the Manual Mode Together with the statistical output proForecaster gives the main statistics and comments on their values The Adjust forecasts button opens a new window were forecasts can be adjusted The Override best mod
24. e Para Value n RMSE 64 328 MAD 49 182 Alpha 0 319 Beta 1 MPE 0 024 MAPE 0 043 Gamma 0 625 Damp Theils U 0 307 LBQ 9 067 Model Summary Low MPE fitted of 0 024 very good La Figure 22 Residual Low MAPE fitted of 0 043 very good Enransetinn snriiracs fitted nf OR Autocorrelation Plot lu T nen SS d A SHOW FORECAST VALUE Displays a data table showing the time series values and the forecast values r al W Forecast Predict time series data proForecaster 2011 R1 Professional 30 day tral elo ie Select Data Step 3 View Forecast Result ef Select Forecast Options ef View Forecast Result Time Column 5 Forecasts Residual Select Report Options 14 12604 1280 828 20 427 15 17352 1694076 41 1243 16 20297 1961381 68 3187 7 21078 2055424 52 3756 18 16503 1656554 52541 19 23044 2257102 47 2977 20 26394 2639799 03985 21 2715043 z 22 2121 703 23 2900 848 24 3296 584 Series Series Column 5 y Adjust forecasts Method Best Holt amp Winterfor Mut Override bestmodel Method Statistics Fitted Method Parameter Statistic Value Statisic Value Paa Value Para Value l RMSE 64 328 MAD 49 182 Alpha 0 319 Bea 1 MPE 0 024 MAPE 0043 Gamma 0 625 Damp Theis U 0 307 LEQ 9 067 Model Summary Low MPE fitted of 0 0
25. e series forecasting process is to find the good forecasting model which is used to generate the predictions The term good is rather ambiguous It incorporates serveral properties A good forecasting model is one which produces accurate forecasts measured by some error statistics Additionally the forecasting model should capture the structural part of the time series in a way that extrapolations can be made which are robust against random fluctuations A good forecasting model is consequently a forecasting model which scores high on all those properties and outperforms the competing models That said there is no single forecasting model which performs well on all time series data The more forecasting models are applied with different statistical properties the more likely is it that a model will be considered good TIME SERIES ACCURACY MEASURES A number of accuracy measures have been proposed to summarize the errors residuals generated by a forecasting model Most of these measures are based on some function of the difference between the actual value and its predicted value RMSE The Root Mean Squared Error RMSE is an often used measure It sums the squares of the residuals and divides them by the number of observations Finally the square root is taken The RMSE penalizes large forecasting errors due to the squaring of the error MAD The Mean Absolute Deviation MAD averages the magnitude of the fo
26. eds a certain threshold the training is stopped Generalization Loss is the current error of the validation set divided by the lowest validation error obtained so far However we want the training progress to continue as long as the training error decreases rapidly Overfitting occurs when the training error decreases only slowly For that reason we define the quotient of generalization loss and progress PQ as the generalization loss divided by the training progress made during k epochs The progress allows to measure whether the training error decreases slowly and whether the training is approaching overfitting PQ THRESHOLD The threshold when the quotient of generalization loss and progress is used to indicate overfitting In general a PQ Threshold of 1 5 is often used STRIP The number of training epochs that are used to measure the training progress A strip of 5 epochs is recommended HYBRID FORECASTING Hybrid forecasting is an approach to combine different forecasting approaches and techniques into a single forecasting method The idea behind hybrid forecasting is that different complementary properties of single forecasting methods are combined to create forecasts that enjoy a high robustness proForecaster blends the forecasts derived from Linear Smoothing and a Neural Network into the hybrid model The weight of each model is determined by finding the smallest absolute error of the combined forecasts hybrid foreca
27. el button overrides the best model with the currently selected forecasting model REPORT OPTIONS In the last forecasting step proForecaster asks you where to insert the predicted values Per default the forecasts are inserted at the end of the data range that was selected at step 1 By checking other you can specify in the Select target cell box were to insert the forecasts in case you want them in another place on your sheet Click into the box and select the cell on your sheet ia WW Forecast Predict time series data proForecaster 2011 R1 Professional 30 day trial Sa ef Select Data Step 4 Select Report Options Nf Select Forecast Options Where to paste the forecasts of View Forecast Result Select Report Options Z at the end of the data range 7 other Select target cell Fis Generate Forecasting Report V Create a Forecasting Report 9 in current workbook in a new workbook Forecasting Report Options V Create Forecast Chart V Method Statistics Y Show Predictions of 9 best method only best 3 methods Figure 8 Report Options Help lt Back Finish x 4 proForecaster can create a Forecasting Report either in the currently active workbook or in a newly created workbook You can select which reporting options shall be generated Create Forecast Chart generates a prediction chart in Excel Method Statistics all method statistics and mod
28. el parameters are shown Show Predictions of select if you want to display only the best model or best three models Finally click on Finish and the predictions will be pasted in the Excel sheet together with the Forecasting Report FORECASTING MULTIVARIATE DATA Clicking in the main menu on the Regression command will open the Regression wizard for predicting multivariate data The wizard will guide you through the forecasting process in four steps SELECT DATA Click on the button highlighted in dark red and another window will open asking you to Select the range containing the data Click into the Excel sheet and select the range of your data In the example the sales data in C3 116 was selected where row 3 contains the header for the data T fan ue l Regression Predict a variable through explanatories proForecaster 2011 Ri Professional 30 day trial cz isses Forecast Regression Gwetcome proForecaster Select input data Step 1 Select Data Select variables Select the range that contains the data View regression result Report options C 3 1 16 Click inside the box and mark the range in Excel or use the button to the right Distance Hang RST ls the data organized in columns or rows 9 Columns Rows First column row has header V Has header Select columns if the data is vertically arranged or rows if otherwise Figure 9 Selec
29. for prediction Forecast with V Smoothing Methods Forecast with Statistical Methods and Growth Functions Y Neural Network Forecasts with an Expert Manual Trained Network v Hybrid Method Forecasts with a combination of Smoothing and Neural Network Smoothing Methods Neural Network Rank Methods by 9 Expert Rank checks goodness of fit on validation data RMSE Root Mean Squared Error MAD Mean Absolute Deviation MAPE Mean Absolute Percent Error Periods to Forecast Periods to predict 4 Confidence Interval Help lt lt Back JI Next gt gt L Finish proForecaster supports three forecasting approaches 1 Smoothing Methods These include standard statistical methods such as Linear Smoothing Moving Average and Growth Functions proForecaster will apply up to 15 different smoothing methods to your data 2 Neural Network Selecting this option proForecaster will use a Neural Network to forecast your data In Automatic Mode the network design and training will be performed automatically proForecaster uses a genetic optimization approach to find the most suitable neural network design for the data 3 Hybrid Method Selecting this option proForecaster will blend the predictions coming from the Neural Network and the Linear Smoothing method into a new hybrid prediction model If you select all three approaches the likelyhood increases that one part
30. g any knowledge about the NN design proForecaster will select the NN architecture and all other parameters automatically proForecaster uses expert heuristics and a genetic optimization approach to train several neural networks with different parameters Finally one surviving NN is chosen for the predictions This approach allows to evaluate several different NN architectures and find the most suitable one for the time series to be forecasted MANUAL TRAINING proForecaster supports the manual training of NNs Select the Manual forecasting mode and the button Neural Network will be enabled Click on that button and the Manual train Neural Network view will be shown P u Forecast Predict time series data proForecaster 2011 R1 Professional 30 day trial SES f Select Data Manually train Neural Network Back to forecast options E Select Forecast Options Train Neural Network for Series View Forecast Result Select Report Options Saui Select the series for which you want to train the neural network Network Architecture of Input Layer Neurons 4 Learning rate 0 50 of Hidden Layer Neurons 8 E Momentum 0 40 Activation Function Bipolar Sigmoid v Trials 1000 Choose how the neural network should be designed Stop Training Condition Y Cross validate PQ Loss Treshold 1 5 Strip 2 Learning Error 4381 830 General Loss 1 211 Validation
31. header V Has headers Select columns if the data is vertically arranged or rows if otherwise ls the data seasonal V Yes seasonal quate r other seasonal period 2 hourly For instance choos dal data has been recorded for each quarter weekly Tene other Help lt lt Back Next gt gt Finish L d Finally click on the Next button and the next step Select Forecast Options will be shown FORECASTING OPTIONS proForecaster can predict data in an Automatic Mode where all technical decisions about which forecasting model to apply and which parameters to select are automatically made by proForecaster through its Expert forecasting engine The Expert forecasting engine combines expert heuristics and genetic optimization methods to find the right parameters for smoothing models and neural network architectures In case you want to select which Smoothing method should be used by proForecaster and you want to design the Neural Network yourself select the Manual Mode Figure 6 Forecast Options r OW Forecast Predict time series data proForecaster 2011 R1 Professional 30 day trial tel a ef Select Data Step 2 Select Forecast Options Select Forecast Options Forecasting Mode View Forecast Result Select Report Options 9 Automatic Mode proForecaster will try all methods to find the best performing Manual Mode choose which methods proForecaster will use
32. hei s U E Leo 5 157 mary Low MPE validation of 0 055 very good l Low MAPE validation of 0 061 very E s wares hralidatinn nf DA Figure 29 Change Chart View lt lt Back Next gt gt Finish HOW TO HIDE A SERIES IN THE FORECAST PLOT Click on the label name in the legend of the chart to either display or hide the series Figure 30 Zoom Into A Chart Figure 31 Adjust Forecast HOW TO ZOOM INTO THE CHART Just click into the chart and draw a rectangle this will zoom into the area Use the minus and plus sign to change the scale as needed W Forecast Predict time series data PENES f Select Data Step 3 View Forecast Result f Select Forecast Options f View Forecast Result Select Report Options 10 MM sales MUI Fitted Ml Forecast E Validation Series Series Sales y f Adjust forecasts Method Best Double Moving Avera v Override best model Method Statistics Validation Method Parameter Statistic Value Statistic Value Para Value Para Value RMSE 1777433 MAD 1483 75 Apha Beta MPE 0 055 MAPE 0 061 Gamma Damp Thei s UNE Leo 5 157 Model Summary Low MPE validation of 0 055 very good a k Low MAPE validation of 0 061 very good E Enransetina armuirare hualidatinmi of QA Help lt lt Back Wet Finish HO
33. icular forecasting model will make a good fit to your data and will generate accurate forecasts Please be aware that when you want to forecast several time series in one run and select all three forecasting approaches a considerable computation time may be required Especially neural networks are quite computationally intensive proForecaster offers four different ranking methods to determine the best forecasting method for the data Expert ranking is the default ranking method for the forecasting models 1 Expert Ranking Each model is validated on separate historical data the time series is divided into 80 of the observations for training the models and 20 for testing the trained models on actual historical data and a number of statistical properties are examined to test the model s performance 2 Root Mean Square Error RMSE The RMSE is an absolute error measure that squares the deviations to keep the positive and negative deviations from cancelling out each other The RMSE is very sensitive to large forecasting errors 3 Mean Absolute Deviation MAD The MAD averages the distance between each pair of actual and fitted data points 4 Mean Absolute Percent Error MAPE The MAPE uses absolute values to keep the positive and negative errors from cancelling out each other and uses relative errors to let you compare forecast accuracy between time series methods Select how many periods you want to forecast and whether proFo
34. is multiplied by its assigned weight The result is fed into the activation function which fires when a certain threshold is reached and then sends the signal to the output neurons At each run the forecasting error is calculated and fed back into the NN Figure 15 Network Architecture NEURAL NETWORK DESIGN The challenge of applying a NN to a time series forecasting problem is to choose the right design of the NN This requires to determine how many neurons should be in the input hidden and output layer Furthermore if a neuron is presented with a signal coming from other neurons the activation function tells the neuron how to react to that signal proForecaster provides four commonly used activation functions to choose from INPUT 1 INPUT 3 INPUT 4 INPUT LAYER NUMBER OF INPUT NEURONS This is the first choice to make how many past periods should be used to predict a future value If you select the number of input neurons to be two than the forecasted value is a function of its past two predecessors NUMBER OF NEURONS IN THE HIDDEN LAYER In the hidden layer the data processing takes place Selecting the number of neurons in the hidden layer is a non trivial task The more neurons are in the hidden layer the more complex data patterns can be learned by the NN But that does not mean that the NN gets smarter by choosing a high number of neurons in the hidden layer in fact the higher the number of neurons in the
35. l xao topi nx crabe one Doe exon eeu nane ipe urn Ere De Ine ve ea stds bidoauissauoadestolesntessasnedsalusanvenins 18 lupidrrp ER 19 ud m 19 Theil Uic EE re ERG ret Uta re ioa eh dis hb Machen at ood Vue Lon er e poets ro eb deua 19 Merc PEE 19 Expert rank EE 20 E uptetib Aun rige A NAA 20 Forecasting model parameters ccccccccccecsesessesecececseseaesecececeeseeesesececeesesaesesecsceesesesseseesceesesasaeseesceeseseeaeees 21 Ao ITI ESTERI pLL e PI 22 Exponential smoothing x5 ete obere A ido 22 D o blexmoving average iii A DUO B esa deuten de Whee een fuente HE ex due d UR o uel 22 Linear smoothing holt s Method ccccccccssececsssceceesececsessececsssseceesseeeceesaececesseceessesecsesaeeeceesseceeseseceeaaes 22 Damped linear smoothlhg i nacen ina 22 Triple exponential smoothing cccccccccccsssessscecececeeseessaecececeeseeseseesesceesesaeseeecsceeeaaeseeececeesesaesesececeeseseaeees 22 Additive seasonal methodi isinir aeii ene e a nein tnnt nennen thee nennen nennen teen rnnt nenne 22 Multiplicative seasonal method cccccccccccssssssscecececsessaesecececsesesaeseeeesceesesaeseeecsceesesesseseesceesesasaeseeseeesesaeaeees 23 Additive Seasonal trend method dico ici 23 User Guide proForecaster 2011 R1 Multiplicative seasonal trend methods sieniin ei a E e aa Eai iTe EE i a 23 Damped additive smoothing siesena s tiaa A Anidado 23 Damped multiplicative SMOO
36. le you want to predict View ern result Available variables Variable to be predicted Report options Hang gt Distance RST LST lt lt Explanatory variables R Flex his ost Select the variable to be predicted and the respective explanatory variables Regression Options 9 Include all Explanatory Variables Stepwise Forward Regression Use for keeping the regression model simple Stepwise Backward Regression Use for not missing important explanatories Combine Regression with Time Series Forecasts V Use Time Series forecasts in the Regression Periods to predict 6 x Select this option if you want to predict the explanatory variables and put those forecasts in the regression function na Figure 18 Training vs Help lt lt Back Next gt gt Finish A Validation Error In the Regression Result view the forecasting plot will show the historical data of the target variable the historical fitted data created by the regression and the forecasts r RD al v Regression Predict a variable through explanatories proForecaster 2011 RI Professional 30 day trial gt f Select input data Step 3 View Regression Result of Select forecast method Forecast Plot Distance gt Compare forecast View result a 150 AN _ 100 50 i 0 x 1 01234 5 6 7 8 9 1011 12 13 14 15 16 17 18 19 20 MMM Distance iil Fitted MN Forecast Model Statistics Va
37. nally proForecaster provides standard growth functions such as the linear trend or polynominal growth FORECASTING MODEL PARAMETERS By default proForecaster optimizes the model parameters through a genetic algorithm where the Mean Squared Error is minimized This optimization procedure is very robust and provides accurate parameters In order to manually select the model parameters right click on the forecasting model and the Parameter Options dialog will open Y Parameter Options 5l Additive Seasonal Method Forecast Option Min MSE Default 9 User Parameter Model Summary Models seasonal data that does not have a trend Models data that shows a seasonal effect by adding the expected level and seasonal factor to create a forecast Set all to default Save Cancel In the Parameter Options dialog you can select how the parameters of the forecasting model should be determined Use Min MSE to let proForecaster determine the optimal parameters Use User Parameter to apply your own parameters to the forecasting model Click Save and the selection will be saved The Set all to default button applies the default optimization of the parameters to all forecasting models MOVING AVERAGE The Moving Average smoothes out past data by averaging the last two periods and projects that view forward EXPONENTIAL SMOOTHING Exponential smoothing provides an ex
38. nd should be the default ranking option SMOOTHING MODELS Smoothing models are the classic forecasting methods and have a proven record over many decades They are simple to apply and provide a robust forecasting accuracy Every smoothing model relies on an assumption about the time series Time series are categorized by their fundamental data patterns Two common data patterns are Trend behavior Seasonality Based on an extended time series classification framework by Pegel 1969 proForecaster provides for every data pattern at least one forecasting model Figure 13 Manual Select Forecasting Methods Figure 14 Forecasting Model Parameters r WY Forecast Predict time series data proForecaster 2011 R1 Professional 30 day trial lil il a dotate i f Select Data Manually select forecasting methods gt Select Forecast Options Smoothing Methods View Forecast Result E No seasonal Additiveseasonal Multiplicative seasonal Select Report Options effect effect effect No trend Y Moving Av V Add Seas 7 Multi Seas V Exp Smoothing V Dbl Mov Av W Add S Trend Y Mu S Trend V Linear S Holt Y Triple Exp S Damped as trend 4 V Da Lin S V Da Add S W Da Mu S Growth Functions Y Linear Fct Y Quadratic Fet V Polynomial Fct Help F n Additio
39. odel REPORT OPTIONS In the Report Options view the report options of the regression result can be selected You can create a report in the current workbook or create a new workbook a UE al Regression Predict a variable through explanatories proForecaster 2011 R1 Professional 30 day trial la esas If Select input data Step 4 Select Report Options f Select forecast method Generate Regression Report Y Compare forecast Y Create a Regression Report gt View result A 9 in current workbook in a new workbook Summary Charts Summary Statistics 7 Residual Plot V Model Summary Residual Autocorrelation Plot V Regression Statistics Forecast Plot V Variable Statistics Regression Result Table s Help lt lt Back ext Finish Figure 12 Report Options L J Three different charts can be generated Residual Plot plots the residuals Residual Autocorrelation Plot plots the autocorrelation coefficients for different lags Forecast Plot plots the forecasts for the target variable together with the historical observations Click Finish and the forecasting report will be generated UNDERSTANDING TIME SERIES FORECASTING In this chapter we explore forecasting in more detail and present the main concepts and statistics that help you to judge the goodness of a model A GOOD FITTING MODEL The goal of a tim
40. ponentially weighted moving average of all previously observed values This model is often appropriate for time series with no predictable upward or downward trend DOUBLE MOVING AVERAGE The double moving average computes a first set of moving averages anda second set of moving averages is computed on the first set This model can model linear trends LINEAR SMOOTHING HOLT S METHOD Holt s exponential smoothing uses a two parameter approach to model data with a trend component This model is very flexible for trending time series DAMPED LINEAR SMOOTHING Damped linear smoothing is based on linear smoothing and introduces a damping factor into the forecasting model The damping factor allows to bring conservatism into the trend projections and can therefore model time series which show saturation effects in its trend TRIPLE EXPONENTIAL SMOOTHING Triple exponential smoothing can model quadratic trends and or seasonality ADDITIVE SEASONAL METHOD The additive seasonal method models data that shows a seasonal effect by adding the expected level and seasonal factor to create a forecast MULTIPLICATIVE SEASONAL METHOD The multiplicative seasonal method models data by multiplying the expected level with the seasonal factor to create a forecast ADDITIVE SEASONAL TREND METHOD This forecasting model is a three parameter model proposed by Holt and Winter to model data with an additive seasonal trend effect MULTIPLICATIVE
41. proximate any function to any degree of accuracy This leads to the problem of overfitting where a NN captures the time series perfectly but does not have enough generalization capacity to make accurate predictions Each time series consists of some structural element and some degree of noise A good forecasting model should only approximate the structural part and not the noise Overfitted NNs can be avoided by applying a stopping condition to the NN training This condition is used to detect overfitting during the training process When a NN begins to overfit the data the training is automatically stopped Figure 18 shows the evolution of the training error and the validation error over time The idea is to split the time series into a training set used to train the neural network and a validation set to validate its predictions At the start of the training both the training and the validation error decrease rapidly After some epochs the NN overfits i e the training error still decreases but the validation error changes direction and rises Training Error vs Validation Error Validation Error Training Error Figure 17 Training vs Validation Error To overcome overfitting a formal stopping condition for NN training is required proForecaster implements the advanced stopping through applying a PQ Threshold and a Strip parameter to the training process The concept is that as soon as the generalization loss exce
42. r respective owners The program may provide links to Web sites and access to content products and services from third parties pro BS is not responsible for the availability of or any content provided on third party Web sites You bear all risks associated with the use of such content If you choose to purchase any products or services from a third party the relationship is directly between you and the third party pro BS is not responsible for a the quality of third party products or services or b fulfilling any of the terms of the agreement with the third party including delivery of products or services and warranty obligations related to purchased products or services pro BS is not responsible for any loss or damage of any sort that you may incur from dealing with any third party TABLE OF CONTENTS lu CET 1 Iristalling DrOFOFeCaster ooorcee oret Exon Eee ooa od Nnnn nea nE poa Rae ERUNT E CHE Donde EE poa Re EE Dou E EEPDa Eres DENM E EEEENT 1 Software Requiere 1 License ISSUCS ae 2 Activated liCemse siet remet riores eee Nee e x eR RER Eu neuro we en eese dn em venue E ek de nrc leu de ox Ne RE Re Rae ed 2 Forecasting Basics sesser eise ei reca A rad Feo RDV E ra anb Md eR ida 4 Time series forecasting ooo een ooo suain oo sona so o uua a op saccades suas a oo sa EE ESSE ERR ERE Osa EISE Eas E EE saga gao 4 Why is for c
43. recaster should give a confidence interval for those predictions In our case we want to forecast 12 months ahead and we want to display confidence bands so that we can be 90 sure Please note that the higher the confidence interval the wider the confidence range for the predictions proForecaster offers you three commonly used percentages 90 95 and 99 Finally click Next and proForecaster will forecast the data Depending on the number of time series to be forecasted and the number of forecasting models applied to the time series computation time may range from seconds to several minutes proForecaster will inform you about the current status of the forecasting run Figure 7 Forecast Result FORECASTING RESULT After the forecasting run proForecaster displays the forecasting result view This view displays the forecasting result in graphical form containing the time series the currently fitted model and the predictions derived from that model together with method statistics and model parameters A Forecast Predict time series data proForecaster 2011 R1 Professional 30 day trial l ahak f Select Data Step 3 View Forecast Result Y Select For Ooa Forecast Plot Damped for Multi Seasonal of View Forecast Result Select Report Options p AWA Nahe SA 300 LA 200 a 100 I A Training Validation Forecast 0 5 10 15 20 25 MMM Sales UN Fitted MM Fore
44. recasting absolute values of the errors The MAD is very useful to measure the forecasting error in the same unit as the time series MAPE The Mean Absolute Percentage Error MAPE is calculated by taking the absolute values of the error at each time period and dividing this by the actual observed value Then the average of these percentage errors is computed The MAPE indicates how large the forecasting error is compared to the actual values of the time series MPE The Mean Percentage Error MPE is calculated similar to the MAPE The MPE takes the residual at each time period and divides it by the actual value of the series Finally the average of the percentage errors is calculated The MPE helps to decide whether a forecasting model is biased which means if the forecasting model consistently overstates or understates the time series THEIL S U Theil s U compares the forecasted values to naive forecasts A naive forecast is simply the last observed value taken as the prediction for the next period The naive forecast is the simplest to make and the best guess to make when no information is available A good forecasting model should outperform naive forecasts How to interpret Theil s U Theil s U value Interpretation More than 1 The forecasting model is worse than guessing Equal to 1 The forecasting model is as good as guessing Less than 1 The forecasting model is better than guessing LBQ The Ljung Box Q sta
45. riable Statistics Sta Value Predictor Coeff SECoeff T Stat P Value AdjR2 0 63 intercept 31 263 73 047 0 428 0 68 R2 081 Hang 2608 27 192 0096 0 926 5 Std Er 15 81 RST 0 276 0 493 0 559 0 591 FTest 44 LST 0 038 0 618 0 062 0 952 LBQ 957 Rex 122 1 564 0 794 0 45 A HObs 19 Qm TT ES nem a Model Summary E Moderate adj R2 0 63 0 5 ok g Model significance p Value 0 014 lt 0 05 model is significant good T le Variahla cinnifiranne alluarishlae sra einmificant H 1 annd Figure 19 Time Series Gee Finish Regression Result CHART VIEWS Figure 20 Forecast Plot proForecaster provides the visual inspection of the residuals through different charts Charts help to check whether a time series forecasting or a regression model is a good fit to the data To change the chart view right click into the chart and a menu opens with different charts to select TIME SERIES FORECASTING proForecaster provides four different charts in the Forecast Result view Displays the time series the fitted values and the forecasts for a forecasting model Wi rorecest predict E EE dita DR canes 2017 Rl Protecconal 30 day til et f Select Data Step 3 View Forecast Result Y Select Forecast Options Forecast Plot Holt amp Winter for Mult Seasonal of View Forecast Result Select Report Options Eee 3000 2
46. roForecaster uses state of the art time series methods to analyze the data and projects them into the future proForecater uses two forecasting approaches 1 Time series forecasting The time series is analysed and projected into the future based on its time series structure 2 Regression Uses independent variables to forecast a target variable Time series forecasting indirectly assumes that all information about the time series is already present in the historical observations whereas in regression analysis one or more variables show an effect on the target variable that is to be predicted When you run the Forecast command on the main proForecaster menu the time series forecast wizard will be called The wizard will guide you through the forecasting process in four simple steps 1 You select where the historical data is located on your sheet 2 You select which forecasting methods shall be used and how many periods should be forecasted 3 You compare the different forecasting models and judge how they perform on the historical observations 4 You select where the predictions will be inserted into the sheet To generate predictions proForecaster has two basic operating modes 1 Automatic mode proForecaster takes control of the forecasting process and selects a range of candidate models including Smoothing Models Growth Functions and Neural Network Design to find the best forecasting model for the data In the automatic
47. s demand The goal is to find a forecasting method that best captures and projects the data into the future WHY IS FORECASTING IMPORTANT Every organization must try to predict future events As the timeliness of market actions becomes more important the need for accurate planning and forecasting is essential to get ahead of competitors The difference between good and bad forecasting can affect the success of an entire organization PROFORECASTER PROFESSIONAL proForecaster can give you the leading edge in forecasting by providing you with advanced prediction technology that incorporates state of the art developments from artificial intelligence and statistics proForecaster is graphic oriented and wizard driven making proForecaster the forecasting tool of choice for business professionals Assume you have recorded historical sales data for your main product line for each month over the last 2 years You want to answer the question What are the likely sales figures for the next 12 coming months Why is this important Because it allows you to better plan your supplies and minimize your inventory Generating accurate sales forecasts will help you to save substantial money and streamline your supply chain Most historical or time based data contains an underlying trend or seasonal pattern However most historical data also contains random fluctuations noise that make it difficult to detect these data trends and patterns p
48. ssion Values e Regression Predict a variable through explanatories proForecaster 2011 R1 Professional 30 day trial lW alli h EA f Select input data Step 3 View Regression Result Y Select vr Residual Autocorrelation Plot gt View regression result Report options uA 0 sn a 1 im 0 4 0 6 8 10 12 bd Ill Autocorrelaton Model Statistics Variable Statistics Stat Value Predictor Coeff SECoeff T Stat PValue 95 AdjR2 0 6 Intercept 22 326 38 078 0 586 0 569 10 R2 0 67 Hang 43 501 9 643 4511 0 001 224 Std Er 16 33 F Tet 2 04 LBQ 24 72 Mbs 19 m r Model Summary Moderate R2 0 67 0 5 ok a Variable significance all variables are significant at 0 1 good le Residual stdannrrelatinn RO JA 72 IN 14 nn sitnenrralstinn annd Help lt lt Back Finish n SHOW TABLE Displays a data table showing all independent variables the target variable and the residuals for the current regression x Regression Predict a variable through explanatories proForecaster 2011 R1 Professional 30 day trial f Select input data f Select variables CP View regression result Report options Step 3 View Regression Result 105 67 109 05 Model Statistics Variable Statistics Stat V
49. st x linear forecast 1 NN forecast Hybrid forecasting can be especially interesting for financial time series forecasts These time series often show linear and non linear data patterns UNDERSTANDING REGRESSION Regression is suitable for forecasting applications when there is a relationship between the to be forecasted variable and other independent variables In regression a mathematical relationship is built to describe how strong the influence of the independent variables is on the target variable LINEAR REGRESSION proForecaster supports multivariate forecasting through linear regression Here a linear relationship is built between predictor variables and the to be forecasted variable For a neat introduction to regression we recommend the book Intermediate Statistics for Dummies by Deborah Rumsey HOW TO ASSESS A GOOD REGRESSION MODEL In order to judge whether a regression model is suited to generate forecasts its statistical properties and the distribution of the residuals have to be examined R2 Measures how well the variability of the target variable is explained by the predictor variables R can range from 0 and 1 Where 1 is a perfect relationship and 0 is no relationship at all An R of 0 7 and higher is considered to be a quite good value ADJUSTED R2 One interesting feature about R is that the more predictor variables we add to the regression model the higher the R becomes This is due to the
50. t Regression Data cem Finish Click on the Next button and the next step Select Forecast Options will be shown SELECT VARIABLES In the Select Variables view you select which variable you want to forecast and which variable or variables should be used to predict the to be forecasted variable Mark the target variables in the Available variables list and click on the button to put them into the respective list In order to delete a variable either from the Variable to be predicted or the Explanatory variables list use the button r s Regression Predict a variable through explanatories proForecaster 2011 R1 Professional 30 day trial 2 ef Select input data Step 2 Select Variables p Des Select the variable you want to predict a siio variables Variable to be predicted Reece opns Hang Distance RST LST lt lt Explanatory variables R Flex Ce Ost Select the variable to be predicted and the respective explanatory variables Regression Options 9 Include all Explanatory Variables Stepwise Forward Regression Use for keeping the regression model simple Stepwise Backward Regression Use for not missing important explanatories Combine Regression with Time Series Forecasts 7 Use Time Series forecasts in the Regression Periods to predict Select this option if you want to predict the explanatory variables and put those forecasts in the
51. t variable Click on next and proForecaster will construct the regression model REGRESSION RESULT The Regression Result view displays the regression result The Method Statistics list all important statistics such as R and the Standard Error of the final regression model The Variable Statistics view shows all explanatory variables and their statistical properties r 3 Regression Predict a variable through explanatories proForecaster 2011 R1 Professional 30 day trial E PE f Select input data Step 3 View Regression Result f Select variables E View regression result Residual Distribution Report options A f Unusual data point 30th Percentile 75th Percentile UO Mean 25th Percentile LQ 10th Percentile Model Statistics Variable Statistics Stat Value Predictor Coeff SECoeff T Stat PValue 95 AdjR2 0 72 Intercept 47 459 55 606 0 853 0 412 7 R2 0 79 R Flex 2 224 0 902 2466 0 031 0 1 Std Er 13 86 L Flex 0 688 0 65 1 059 0 312 2 1 F Test 3 68 ost 0 233 0 117 1 994 0 071 1 0 LBQ 1255 HObs 13 T E j T Model Summary 2 High adj R2 0 72 0 7 good E Model significance p Value 0 056 lt 0 1 model is significant ok s Warishla cinnifirance all uariahlao sra sinnifinant N 1 anand E Heb The Model Summary summarizes the statistics and interprets the result with respect to the goodness of fit of the regression m
52. tions 1 proForecaster Free Edition free for personal and commercial use 2 proForecaster Professional Edition full features and technical support When you download the proForecaster Free Edition you automatically receive a 30 days trial license to test the Professional Edition At the end of the trial period proForecaster will be downgraded to the Free Edition if no Professional license is purchased and activated 1 Download proForecaster from www pro bs net 2 Open the setup and proForecaster will guide you through the installation process 3 After successful installation proForecaster is available at each Microsoft Excel start up in the Add In tab SOFTWARE REQUIREMENTS proForecaster requires Microsoft Excel version 2007 or 2010 and the Microsoft NET Framework version 3 5 to be installed Figure 1 License Info LICENSE ISSUES You can purchase a license from the proForecaster website www proforecaster net Two professional licensing options are available 1 Single User License The software can be used on a single computer for personal and commercial use 2 Site License The Software can be used on up to 100 computers of the company that purchased that license ACTIVATE A LICENSE Click on the License button in the Info Menu and the License Info dialog will be shown In order to activate a license your computer needs to have access to the internet The License Manager shows all relevant inform
53. tistic indicates whether the residuals of a forecasting model show structural patterns The objective of a good forecasting model is to produce residuals that are randomly distributed Technically the LBQ statistic measures whether a set of autocorrelation coefficients are significantly different from autocorrelations that are all zero Randomly distributed residuals should not be autocorrelated to each other therefore the LBQ helps to gauge whether some structural part remains in the time series that was not modeled by the forecasting model EXPERT RANK Expert Ranking requires a sufficient number of historical observations two full cycles of data are usually sufficient You need for instance 24 data points of monthly data to apply the Expert Rank proForecaster will then divide the observations into a training set 80 of the data and a validation set 20 of the data 1 The forecasting model parameters will be optimized on the training set 2 Forecasts are generated for the validation period 3 Forecasts and observations from the validation set are compared 4 Validation set error statistics such as RMSE MAD Theil s U will be calculated 5 The model with the lowest RMSE on the validation set is considered the best model 6 All models are rerun to optimize the parameters an all data including the validation set observations This approach helps to determine how well a model will perform on real world data a
54. tribution as is assumed by the linear regression model Figure 26 Residual Histogram T 3 z Regression Predict a variable through explanatories proForecaster 2011 R1 Professional 30 day trial rm f Select input data Step 3 View Regression Result Y Select epit Residual Histogram gt View regression result 35 p Report options sal 3 3 D g ES u 30 20 10 0 10 20 30 je e e ccc oo eee o Veil HE Predictor Coeff SECoeff T Stat PValue 95 Intercept 22 326 38 078 0 586 0 569 10 Hang 43 501 9 643 4511 0 001 22 4 m Model Summary a Moderate R2 0 67 gt 0 5 ok Variable significance all variables are significant at 0 1 good I Besides sittannrralstinn RA IA 79 7 20 14 na sittnnnrrelstinn anand LS SHOW RESIDUAL AUTOCORRELATION Displays the autocorrelation coefficients at different lags of the residuals generated by the regression model This chart helps to determine whether autocorrelation is present in the residuals High autocorrelation coefficients indicate that a data pattern is present in the residuals thus violating the assumptions of the linear regression model The autocorrelation coefficient can be in the range of 1 to 1 An autocorrelation coefficient of 0 6 for a lag can be an indication of autocorrelation Figure 27 Residual Autocorrelation Regression Figure 28 Table Regre
Download Pdf Manuals
Related Search