### Create a StudySoup account

#### Be part of our community, it's free to join!

Already have a StudySoup account? Login here

# H BUSINESS ANALYSIS MGS 3100

GSU

GPA 3.88

### View Full Document

## 10

## 0

## Popular in Course

## Popular in Managerial studies

This 23 page Class Notes was uploaded by Ms. Aaliyah Crist on Monday September 21, 2015. The Class Notes belongs to MGS 3100 at Georgia State University taught by Thomas Whalen in Fall. Since its upload, it has received 10 views. For similar materials see /class/209859/mgs-3100-georgia-state-university in Managerial studies at Georgia State University.

## Similar to MGS 3100 at GSU

## Popular in Managerial studies

## Reviews for H BUSINESS ANALYSIS

### What is Karma?

#### Karma is the currency of StudySoup.

#### You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!

Date Created: 09/21/15

Introduction to Forecasting Thomas Whalen January 2007 Forecasting is perhaps the most fundamental model based activity in an organization At their core budgets are forecasts Sales quotas are forecasts even if they often are intended to be selffulfilling prophecies Financial pro forma39s are forecasts Market research studies often produce what amount to forecasts Our interest here is in forecasting at a more fundamental level Often the types of forecasts we consider are the underlying basis of the forecasts mentioned above Broadly speaking we consider Time Series models Leading Indicator modelsl and Qualitative models We will put most of our time and effort into time series models We will take a brief look at leading indicator modeling Qualitative modeling is a hodgepodge of approaches from a wide variety of sources the text will take us as far as we need to go in that area There are also many valuable forecasting methodologies that are simply beyond the level of this course Delphi and Other Qualitative Methods Qualitative modeling is a hodgepodge of approaches from a wide variety of sources Some such as opinion polls or quot grass roots forecastingquot average the opinions of a large number of people in the hopes that their various biases and misinformation will statistically quotwash ou quot into an objective forecast At the other extreme you can rely on one expert maybe yourself who you believe to know more about the future prospects of what you re interested in than anybody else But generally the best approach is to try to get the consensus of a group of experts quotPanel Consensusquot is the simplest method the experts get together either in person or on line and discuss the future prospects of the variable to be forecasted However group dynamics problems can lead to the group becoming dominated by high status or just loudmouthed members so that you end up with just one person s opinion even though you39ve paid for a whole group of experts One very successful methods to make sure you get the benefit of all the participant s thoughts is the Delphi Technique In the Delphi technique each expert contributes an estimate anonymously to the group as a whole then they all look at the whole collection of extimates and each member submits a revised estimate in the light of this new information The process continues until consensus or deadlock is reached Leading Indicator quotCausalquot Models See the discussion about using housing starts data to forecast sales of landscape blocks on the course website for an example of leading indicator forecasting Note that housing starts are a useful predictor of landscape blocks because housing starts are a leading indicator for landscape blocks housing starts are useless for forecasting sales of foundation blocks because they are a simultaneous indicator 1 Sometimes misleadingly called quotcausal modelsquot though usefulness as a leading indicator does not imply any simple causeandeffect relation 1 Time Series Models When we build a time series model we are letting the passage of time act as a surrogate for whatever is really causing the behavior of the data In effect if we are forecasting sales we are saying that the passage oftime causes sales to happen Now not being fools we know that isn39t so but it is a convenient fiction The real cause system involves a very great many causal factors not all of which are known to us and many of which we have no way to measure And even if we had all that information we don t have a reliable affordable methodology to use the information So instead we rely on finding a pattern in the sales data that we can relate to the passage of time Thus time series models are based on what we call the Stationary Time Series Assumption The Stationary Time Series Assumption says that whatever the true underlying cause system behind the data may be it will continue to operate in the future as it has in the past Anybody can cite cases where this simply is not true the cause system changes We call this catastrophic error By this we do not necessarily mean that something bad happened but just that there has been a sharp break in the pattern due to some change in the nature of the system For military industries the end of the Cold War induced catastrophic error but it s pretty hard to describe it as a bad thing Our conceptual model of a time series says that Y T gtltC gtltS iEt In longer form it is Y Trend gtltCyclical gtltSeasonal iErrort where Yt is the original data for time t for example unit sales of widgets for February 2007 Trend establishes the time pattern of the basic level of the data We presume this to really stand for the effects of many things such as population incomes the effects of the product life cycle and so forth Cyclical represents any long term swings above and below the trend level This presumably relates to the business cycle Political cycles and various cycles of nature may enter in here too Seasonal is any cycle that has a period of exactly one year for a complete cycle This component can arise from weather customs holidays and any other in uences that repeat in approximately the same way year after year Error Forecasts are always wrong but often still useful We have already looked at catastrophic error which invalidates the model when it happens Error can also come from selecting a wrong model to apply This amounts to self induced catastrophic error The original data usually contains some errors of measurement If we estimate coefficients there will necessarily be estimation errors And finally there is the underlying degree of randomness of behavior of the phenomenon that the data measures We use Actual Forecast as the value of Error for any given period so a positive error means the forecast should have been higher it was too low and a negative error means the forecast should have been lower it was too high Measuring the Error in Historical Forecasting In deciding on a forecast model to use certain questions always come up What does it cost Will forecasts be available in time to use them How accurate is it To answer the last question we will need standard ways of measuring the error term A few are special to some particular models but several are quite general Let s look at the general ones For a new forecasting model we construct these measures using the historical data we based our model on and on a quotbackcastquot using the model Bias The Bias is simply the arithmetic mean of the errors We would of course like it to be zero A positive bias means that the model on the average forecasts low Negative means the reverse 2 ActualForecast II Bias Mean Absolute Deviation MAD penalizes all errors equally in direct proportion to their magnitude You can interpret the MAD as meaning the amount by which the forecast model missed on the average The ABS function makes negative numbers positive and leaves positive numbers alone M AD ZABS ActuIaIForecast Mean Absolute Proportional Error 0r Mean Absolute Percent Error This is like MAD but penalizes error on the basis of what proportion of the actual value it is rather than its raw numeric amount ABS Actual Forecast 2 MAPE 2 Actual II Mean Square Error MSE as we usually call it is very much like the simple variance you learned in statistics It penalizes larger errors much more heavily than smaller ones 2 MSE Z ActualForecast II Rocky Gold39s problem We will look at all of our time series techniques through this one sample problem mostly using Excel You should too When you try to duplicate my results remember that except where I tell you differently only the original data is numbers Everything else other than labels is formulas using cell references as needed Rocky Gold started a jewelry store some years ago and it has grown nicely To support the kind of volume he now sees Rocky needs to carry inventories of j ewelry larger than his personal assets can support The manufacturers are happy to quot oor planquot their products but their interest rates combined with lost discounts make this very costly He needs a cheaper source of cash The Last National Bank is willing to consider giving Rocky s Jewelers a relatively low cost line of credit that would solve Rocky s problem Before they can process the loan they need Rocky to provide them with financial statements and a sales forecast for the next year Rocky s accountant is working up the financial statements and Rocky has come to us for help with the forecast He has given us the store s dollar sales in thousands by quarter for the last 5 years That is the data we ll work with Nalve models The simplest of all forecast models is called the naive model The naive model simply says that whatever happened last time will happen again this time It is understandable takes no calculations and obviously is cheap Sometimes it is a very good choice The naive model39s other strength is that it gives us a baseline to measure other models against Every other forecast model is more complex and errorprone than the naive model You can t justify using a complicated model that won t outperform the naive model If we believe that the data may be seasonal we sometimes use a slight variation on the naive model Instead of saying quotwhat happened last period will happen againquot we say quotwhat happened in this period last year will happen againquot Let s look at Rocky s data and see how these two versions of the naive model perform Looking at the results it is pretty clear that the model labeled Naivel which says quotNext quarter will repeat this quarterquot is very unsatisfactory Its bias of 999 thousand dollars isn39t bad The positive bias re ects the fact that there is an overall upward trend that the Naive model can t keep up with However as we shall see we can do much better than the MAD of 5136 MAPR of 31 and MSE of 492667 Naive2 says quotNext quarter will be the same as the same quarter a year agoquot It39s not surprising that the bias is larger since it ignores a whole year39s growth in sales The MAD is smaller by nearly 26000 and the MPE for Naive2 is less than half of Naivel39s MAPE with a MSE about onefifth that of Naivel Rocky could use this forecast It makes a fine benchmark to compare other forecasts with See if you can duplicate these results Remember that only the column headed Raw Data is numbers Everything else uses formulas Naive Models Raw Naive1 Error Abs Sq Err Naive2 Error Abs Sq Err Error Error Error Error from to Qtr Data Jan97 Mar97 1 1078 Apr97 Jun97 2 104 1078 38 38 4 1444 Jul97 SeP97 3 1119 104 79 79 7 6241 Oct97 Dec97 4 1892 1119 773 773 41 597529 Jan98 Mar98 5 998 1892 894 894 90 799236 1078 8 8 8 64 Apr98 Jun98 61327 998 329 329 25 108241 104 287 287 22 82369 Jul98 Sep 98 71035 1327 292 292 28 85264 1119 84 84 8 7056 Oct98 Dec98 81755 1035 72 72 41 5184 1892 137 137 8 18769 Jan99 Mar99 91145 1755 61 61 53 3721 998 147 147 13 21609 Apr99 Jun99 101147 1145 02 02 0 004 1327 18 18 16 324 Jul99 Sep 99 111141 1147 06 06 1 036 1035 106 106 9 11236 Oct99 Dec99 12 2155 1141 1014 1014 47 1028196 1755 40 40 19 1600 Jan00 Mar00 13 1347 2155 808 808 60 652864 1145 202 202 15 40804 Apr00 Jun00 14 1314 1347 33 33 3 1089 1147 167 167 13 27889 Jul00 Sep OO 15 1268 1314 46 46 4 2116 1141 127 127 10 16129 Oct00 Dec00 16 2845 1268 1577 1577 55 2486929 2155 69 69 24 4761 Jan01 Mar01 17 1642 2845 1203 1203 73 1447209 1347 295 295 18 87025 Apr01 Jun01 18 1772 1642 13 13 7 169 1314 458 458 26 209764 Jul01 Sep 01 19 1868 1772 96 96 5 9216 1268 60 60 32 3600 Oct01 Dec01 20 2976 1868 1108 1108 37 1227664 2845 131 131 4 17161 Jan02 Mar02 21 2976 1642 Apr02 Jun02 22 Bias MAD MAPE MSE Bias MAD MAPE MSE 10 51 31 4927 20 26 15 984 Exponential Smoothing Models Exponential Smoothing models are slightly more complex than the naive model but not a lot They have A ta es and 139 Jvanta es that you should Advantages Disadvantages gt Requires storing very little data gt Simple Exponential Smoothing always lags any trend in the data gt Very quick and simple to compute gt Double Exponential Smoothing ignores seasonality is more complex gt Emphasizes the most up todate information gt Winter39s Method is complex Simple Exponential Smoothing The Simple Exponential Smoothing model is of the form F OL XYt1 l OCgtltFt1 Where F is the forecast for period t Fm H is the most recent forecast Ytl is the most recent actual data point or is the smoothing constant 0ltocltl Lets see how it performs for Rocky Look on the next page for my results Raw Abs Squared from to Qtr DataExponential Error Error Error Erroralpha 025 Jan97 Mar97 1 1078 1078 Apr97 Jun97 2 104 1078 38 38 4 1444 300EXP quotequott39a39sm th39quotg Jul97 Sep97 31119 106850 505 505 5 2550 o Oct97 Dec97 41892 108113 8109 8109 43 657518 Jan98 Mar98 5 998 128384 2858 2858 29 81707 250 Apr98 Jun98 6 1327 121238 1146 1146 9 13137 Jul98 Sep98 7 1035 124104 2060 2060 20 42451 200 Oct98 Dec98 81755 118953 5655 5655 32 319759 Jan99 Mar99 9 1145 133090 1859 1859 16 34557 g Apr99 Jun99 10 1147 128442 1374 1374 12 18885 5 150 Jul99 Sep99 11 1141 125007 1091 1091 10 11895 E Oct99 Dec99 12 2155 122280 9322 9322 43 868997 Jan00 Mar00 13 1347 145585 1088 1088 8 11848 100 Apr00 Jun00 14 1314 142864 1146 1146 9 13142 Jul00 SepOo 15 1268 139998 1320 1320 10 17418 Oct00 Dec00 16 2845 136698 14780 14780 52 2184533 Jan01Mar01 17 1642 173649 945 945 6 8928 Apr01 Jun01 18 1772 171287 591 591 3 3497 Jul01Sep01 19 1868 172765 1404 1404 8 19698 Oct01 Dec01 20 2976 176274 12133 12133 41 1472007 Gum Jan02 Mar02 206605 Apr02 Jun02 MM M4 Bias MAD MAPE MSE 21 36 19 3044 You can see that its performance is not impressive in this case If you try different values of a you may be able to get slightly better results but they won t be wonderful because Rocky s sales are so seasonal and his business is growing The simple exponential smoothing model isn39t well suited to a case like this If Rocky were a fastener wholesaler with 30000 different kinds of nuts bolts studs screws washers cotter pins and so forth to keep track of simple exponential smoothing might give him 30000 cheap forecasts very satisfactorily Exponential smoothing models need to be quotjump startedquot with some initial values In the simple exponential smoothing model the initial quotForecastquot is usually taken as equal to the actual value for the same period If you look back at the spreadsheet you ll see that that is exactly what I did Now you try to first get the same results I got then use different smoothing constants as to see if you can do better Double Exponential Smoothing The Double Exponential Smoothing model is a little more complicated than the simple exponential smoothing model because it attempts to stay quoton top ofquot any trend component that may be present in the data It doesn39t cope well with strong seasonality so we won t expect it to work too well for Rocky See Appendix 1 for a more complete treatment Trend Models Another way to approach the problem is to try to fit a line through the data and to project that line into the future In some cases an quoteyeballquot fit works pretty well but there are problems with that approach First off it is difficult to computerize Reasonable people can disagree over which line gives the best fit And the error measures are all but guaranteed to increase steadily over time We need a better way and one is available to us It39s called the method of least squares A linear trend is of the form Y a bX where Y represents the trend value in the present case the forecast a is an intercept the value of the trend at time 0 b is the slope the amount of increase or decrease per period and X stands for time Normally we will identify our oldest data point as Xl and increase X by one for each period in the data To project into the future we just continue increasing the value of X by l for each period we extend the trend The trick lies in identifying in some sense the quotbestquot a and b to use We need a criterion The criterion that is most generally accepted is the least squares criterion Think of squaring the error terms Actual Trend and adding up all those squared values For a really bad fit that sum would be huge We want to select a value for a and a value for b that will produce a sum of squared errors smaller than we would get for any other choice of a and b If we use actual past observed values as our values for Y then mathematically what we want is Minimize 2Yt a bX2 with respect to our choice of a and b Excel provides us with two functions INTERCEPTY1YNX1XN and SLOPEY1YNX1XN to find the optimal values of a and b respectively Let s try it on Rocky s data It didn t work too badly but the seasonality is still giving us trouble As long as we don t somehow capture seasonality we just aren39t going to give Rocky a forecast we can be proud of Seasonality has even messed up our trend If you look at the graph it is plain as the nose on your face that the trend starts too low and rises too fast Because sales are low at the start of the year and peak during the holidays we have underestimated the intercept and overestimated the slope We had better do something about that Raw Trend Abs from to Qtr Data Model Error Error Error Sq Err a 9163105 Jan97 Mar97 1 1078 9760 1020 1020 9 10401 b 5970376 Apr97 Jun97 2 10410357 043 043 0 018 Jul97 Sep97 3 1119 10954 236 236 2 556 LinearTrend Oct97 Dec97 4 1892 11551 7369 7369 39 542984 Jan98 Mar98 5 998 12148 2168 2168 22 47015 Apr98 Jun98 6 1327 12745 525 525 4 2753 Jul98 Sep98 7 1035 13342 2992 2992 29 89543 Oct98 Dec98 8 1755 13939 3611 3611 21 130364 Jan99 Mar99 9 1145 14536 3086 3086 27 95261 Apr99 Jun99 10 1147 15133 3663 3663 32 134211 Jul99 Sep99 11 1141 15731 4321 4321 38 186669 Oct99 Dec99 12 2155 16328 5222 5222 24 272739 Jan00 Mar00 13 1347 16925 3455 3455 26 119342 Apr00 Jun00 14 1314 17522 4382 4382 33 191987 Jul00 SepOO 15 1268 18119 5439 5439 43 295791 Oct00 Dec00 16 2845 18716 9734 9734 34 947565 Jan01 Mar01 17 1642 19313 2893 2893 18 83680 Apr01 Jun01 18 1772 19910 2190 2190 12 47951 Jul01 Sep01 19 1868 20507 1827 1827 10 33373 Oct01 Dec01 20 2976 21104 8656 8656 29 749288 Jan02 Mar02 21701 11 Apr02 Jun02 22 22298 Quarter Bias MAD MAPE Revenue I x MSE 000 3642 23 199075 Seasonal Decomposition Calculating Seasonal Indexes The conceptual model we started with at the beginning said Yt Trend gtlt Cyclicalt gtlt Seasonal iError Let s return to that idea We have become pretty knowledgeable about Trend and Error Now how about the other 2 components First we ll dispose of the Cyclical component While cycles are probably real there is room for debate on the subject they are hard to measure and harder to predict Usually there are several cycles operating Each of these has its own period length of time for one full cycle and amplitude how far up and down it goes Often the period and amplitude are variable It takes a lot of data and sophisticated techniques to even measure much less predict cycles accurately And for short term forecasting even if this is done very well you have added very little to your forecasting accuracy at great expense We will assume that any cyclical component that is present will be captured by the trend and won t deal with it separately That simplifies our model to Yt Trendtheasonalt iErrort While we know that Error is always present by its definition we aren t trying to forecast it Let s leave it out for now That gives us 1 Trendtheasonalt The Seasonal Index Seasonal for a given quarter such as JanuaryMarch is the same for JanuaryMarch of any year in the time period being modeled It is a quantitative measure of the consistent tendency for Rocky s sales revenue in any JanuaryMarch quarter to be below the average quarterly sales revenue for the year in which that quarter occurs specifically it is the typical ratio of the sales revenue in any JanuaryMarch quarter divided by the average quarterly sales revenue for the year in which that quarter occurs The seasonal index is a very important part of the Time Series Decomposition Model Not only will we use it as part of our final forecast but it will help us to measure the Trend component much more accurately Remember to get the full benefit of this you should be trying to match my results A very simple approach would be to compare JanuaryMarch 2007 to the 2007 quarterly average compare JanuaryMarch quarter 2008 to the 2008 quarterly average and so on then take the average of these ratios However notice that this compares JanuaryMarch to the quarters following it a similar procedure for OctoberDecember quarter would compare the OctoberDecember quarter only to the quarters preceding it A somewhat better approach which we will use for conceptual purposes is to take the ratio of the average of all JanuaryMarch quarters to the average of all the quarters in the database and similarly for the other quarters It39s not perfect but it is close enough and we can see the big picture without getting bogged down in details amp fourth up Columns F through J contain the same raw data as Column D but arranged into a table by year and season Cell K3 is the average of all the Quarter 1 JanuaryMarch sales in the historical data Cell K4 is the average of all the Quarter 2 AprilJune sales in the historical data and so on for K5 and K6 Cell K7 is the average of all the quarters regardless of what season of the year The simpli ed seasonal index for Quarter 1 JanuaryMarch is found in Cell L3 by dividing K3 by K7 the simplified seasonal index for Quarter 2 AprilJune sales is found in cell L4 by dividing K4 by K7 and similarly for the other two quarters The rest of column M repeats the Quarter 1 seasonal index for all Quarter 1 rows the Quarter 2 seasonal index for all Quarter 2 rows and so on See Appendix 2 for a discussion of the more sophisticated method covered in textbooks Calculating the Deseasonalized Trend Well it looks like Christmas accounts for a lot of Rocky s business with a little help from June brides Now let39s build a better Trend How can we do that Remember that we said Yt TrendgtltSeasonalt We ll use a little light duty algebra again and this time we39ll get Trendt YtSeasonal YtSeasonal is just our first estimate of trend the spreadsheet refers to it as deseasonalized data Each number in this column is the quotient of the corresponding number in the column headed Raw Data divided by the number in the column headed Seasonal Indexes It s not a trend that we can extend into the future for forecasting purposes so we need to feed the deseasonalized data into a regression model to get a linear2 trend model useful for forecasting Excel s SLOPE and INTERCEPT functions tell us that the best linear model fit to the Rocky s deseasonalized data is Trend t 102673 485X This is seen in the spreadsheet column headed Linear Model based on the coefficients a and b that appear above the graph Note that the fit between the raw data and the linear model of the deseasonalized data is not as good as the fit between the raw data and the linear model of the raw data itself But when we reintroduce the seasonality in the next step that will more than make up for it 2 Actually we could apply any forecasting technique to the deseasonalized data to get a forecast from the naivel model to the fanciest mathematical hocus pocus But we ll stick with linear trend modeling for the moment 11 from Jan97 Apr97 Jul97 Oct97 Jan98 Apr98 Jul98 Oct98 Jan99 Apr99 Jul99 Oct99 Jan00 Apr00 Jul00 Oct00 Jan01 Apr01 Jul01 Oct01 Jan02 Apr02 to Mar97 Jun97 Sep97 Dec97 Mar98 Jun98 Sep98 Dec98 Mar99 Jun99 Sep99 Dec99 Mar00 Jun00 SepOO Dec00 Mar01 Jun01 Sep01 Dec01 Mar02 Jun02 Qtr 1 2 3 4 5 6 7 8 9 10 11 Raw Seasonal Data 1078 1040 1119 1892 998 1327 1035 1755 1145 1147 1141 2155 1347 1314 1268 2845 1642 1772 1868 2976 Indexes 8048 8554 8335 15064 8048 8554 8335 15064 8048 8554 8335 15064 8048 8554 8335 15064 8048 8554 8335 15064 8048 8554 Deseas Data 133943 121585 134259 125602 124003 155138 124181 116507 142268 134095 136899 143061 167366 153619 152136 188867 204020 207163 224125 197564 Linear Model 109913 114587 119262 123936 128611 133285 137959 142634 147308 151983 156657 161332 166006 170681 175355 180029 184704 189378 194053 199727 203402 208076 Error 211 1059 736 6526 2881 059 3446 3287 3281 3728 4256 5417 3131 3928 4855 10447 2050 1218 725 9887 Bias 000 Abs Error Error 211 2 1059 10 736 7 6526 34 2881 29 059 0 3446 33 3287 19 3281 29 3728 33 4256 37 5417 25 3131 23 3928 30 4855 38 10447 37 2050 12 1218 7 725 4 9887 33 MAD MAPE 36 22 Sq Err 446 11209 5419 425937 83005 034 118745 108018 107639 139001 181112 293421 98007 154296 235759 1091410 42041 14831 5260 977583 2047 slope 4674 intercept 105238 Linear Trend based on Deseasonalized Data 3000 0 2500 2000 mi 3 5 1500 gt w M 1000 500 00 1 1 Quarter 21 Calculating the Reseasonalized Forecast Our error measures look a lot better but remember that I computed them to match the trend and the deseasonalized data They give us hope but let39s not stop now We have a Trend function Deseasonalized Trend 105238 4674Xt where X stands for the period number We also have the 4 Seasonal Indices we have estimated that the sales revenue for the JanuaryMarch quarter is 80 of the average quarter the sales revenue for the AprilJune quarter is 86 of the average quarter the sales revenue for the JulySeptember quarter is 83 of the average quarter the sales revenue for the OctoberDecember quarter is 155 of the average quarter Now to put together our forecast model all we need to do is compute the Trend value for each quarter and multiply it by that quarter s Seasonal Index If we want to forecast that s easy too Since the past 5 years were periods 1 through 20 next year must be periods 21 through 24 We still of course reseasonalize the forecast with the same Seasonal Indices The spreadsheet shows our result the model of the past and forecast of the future are in the column headed ReSeas Model Each number in this column is the product of the corresponding numbers in the columns headed Linear Model and Seasonal Indexes Now we have some error measures that really outperform the benchmark Naive2 model Rocky can take this to the bank Alternatively if he has some good business reason to suspect that this trend might not be linear he might consider fitting a nonlinear trend to his deseasonalized data However this is a very advanced topic since just quotfishingquot in your historical data for a good fit without a sound business reason to expect a particular functional form is risking a model that fits the old data so well that that s ALL it fits destroying the predictive ability that we re really looking for from Jan97 Apr97 Jul97 Oct97 Jan98 Apr98 Jul98 Oct98 Jan99 Apr99 Jul99 Oct99 Jan00 Apr00 Jul00 Oct00 Jan01 Apr01 Jul01 Oct01 Jan02 Apr02 to Mar97 Jun97 Sep97 Dec97 Mar98 Jun98 Sep98 Dec98 Mar99 Jun99 Sep99 Dec99 Mar00 Jun00 SepOO Dec00 Mar01 Jun01 Sep01 Dec01 Mar02 Jun02 D LoooxlcnmLmM k xxxxxxxx ICDU ILQM O 18 19 20 21 Raw Seasonal Data 1078 1040 1119 1892 998 1327 1035 1755 1145 1147 1141 2155 1347 1314 1268 2845 1642 1772 1868 2976 Indexes 8048 8554 8335 15064 8048 8554 8335 15064 8048 8554 8335 15064 8048 8554 8335 15064 8048 8554 8335 15064 8048 8554 Deseas Data 133943 121585 134259 125602 124003 155138 124181 116507 142268 134095 136899 143061 167366 153619 152136 188867 204020 207163 224125 197564 Linear ReSeas Model 109913 114587 119262 123936 128611 133285 137959 142634 147308 151983 156657 161332 166006 170681 175355 180029 184704 189378 194053 199727 203402 208076 Model 88460 98014 99400 186691 103509 114007 114984 214857 118557 130001 130568 243022 133605 145994 146152 271187 148654 161988 161736 299353 163702 177981 Error 1934 599 1250 251 371 1869 1148 3936 406 1530 1647 2752 109 1459 1935 1331 1555 1521 2506 175 Bias 12 Abs Error 18 6 Error 1934 599 1250 251 371 1869 1148 3936 406 1530 1647 2752 109 1459 1935 1331 1555 1521 2506 175 MAD MAPE 14 10 Sq Err 37403 3583 15625 629 1375 34941 13188 154894 1646 23412 27119 75746 120 21299 37450 17722 24169 23141 62821 307 288 slope 4674 intercept 105238 Revenue Reseasonalized Model 3000 2500 2000 1500 1000 500 00 1 5 9 1317 21 Quarter Problems If you haven t done it already replicate my time series work with Excel 1 2 The friendly folks from the Federal ATF Division collected data on beer sales in the US over a number of years The data is in millions of cases sold and appears on the next page They would like to develop a forecast of beer sales for 2002 This would help in working out the Federal budget Buddy Weiser Agent In Charge for the Milwaukee Office has hired you to develop the forecast Try several methods and use error measures to decide which one to use for your report to Mr Weiser Write him a short letter explaining what the results mean Include in your letter a printout of your Excel spreadsheet that develops your forecast Since December and January are lumped together use half the 9394 and half the 9495 forecast for DecemberJanuary PER39D MONTHS 1 JUNJUL 2 AUGSEP 3 OCTNOV 4 DECJAN 5 FEBMAR 6 ADPJVTAV 7 JUNJUL 8 AUGSEP 9 OCTNOV 1o DECJAN 11 FEBMAR 12 APRMAY 13 JUNJUL 14 AUGSEP 15 OCTNOV 16 DECJAN 17 FEBMAR 18 APRMAY 19 JUNJUL 20 AUGSEP 21 OCTNOV 22 DECJAN 23 FEBMAR 24 APRMAY 25 JUNJUL 26 AUGSEP 27 OCTNOV 28 DECJAN 29 FEBMAR 30 APRMAY 31 JUNJUL 32 AUGSEP 33 OCTNOV 34 DECJAN 35 FEBMAR 36 APRMAY 37 JUNJUL 38 AUGSEP 39 OCTNOV 40 DECJAN 41 FEBMAR 42 APRMAY 43 JUNJUL 44 AUGSEP 45 OCTNOV 46 DECJAN 47 FEBMAR 48 APRMAY 49 JUNJUL 50 AUGSEP YEAR 1993 1993 1993 1994 1 004 1994 1994 1994 1995 1995 1995 1995 1995 1996 1996 1996 1996 1996 1997 1997 1997 1997 1997 1998 1998 1998 1998 1998 1999 1999 1999 1999 1999 2000 2000 2000 2000 2000 2001 2001 2001 2001 SEAS Million Cases UJgtDJN OUJgtDJN OUJgtDJN OUJgtDJN OUJgtWNHQMbwwt wawwt wa wt wa 176 172 141 144 136 161 188 184 152 158 148 174 215 204 162 169 162 183 222 214 179 187 182 211 248 247 202 207 193 223 261 259 205 211 201 235 271 275 224 234 227 256 304 289 243 249 239 270 312 314 3 Calculate Trend and Forecast values for Rocky for each of the rst 4 quarters of this year the data stops at the end of last year First do it in Excel because it s easier and you can print your results and then do it on your calculator In using your calculator gure out how to avoid needing to stop and write down intermediate values And if you nd you must write down intermediate values don t round them Rounding at intermediate steps can build to huge errors 4 An old friend of your family George A Power is an electrical supply wholesaler who stocks 20000 different products 18000 of those products trouble him because he continually either runs out for several weeks or wanders into a comer of the warehouse and nds what he suspects is a 10 year supply If he could forecast demand for these items he could then set up a simple reorder point reorder quantity inventory system that he believes would let him give his customers better service while reducing his inventory investment He maintains his inventory records on a computer system that constantly updates stock on hand whenever an order is received or sold He has no weekend transactions so he gures that he could update a forecasting and reordering system over each weekend probably employing a GSU student who had gotten an A in MgS 3100 to run the system as a part time job But 18000 forecasts per weekend sounds like a lot He has gotten you weekly sales for a typical one of these products for the last 2 years You have agreed to try to identify an effective and economical forecast procedure to use on these 18000 products Since George has assured you that 12 bipolar doppelganger connectors sales shown above are absolutely typical you both agree that anything that works satisfactorily for them will work for the whole line He doesn t believe that there is anything seasonal about their use and the data seems to bear that out You re both grateful for that You because the methods that measure seasonality are more work and George because they are a lot more time consuming and expensive to use Test the Naive and Simple Exponential models using Excel and make George a recommendation Week Sold Week Sold Week Sold Week Sold 1 52 27 63 53 78 79 81 2 47 28 68 54 68 80 83 3 53 29 67 55 69 81 83 4 55 30 61 56 74 82 77 5 57 31 55 57 65 83 79 6 52 32 63 58 67 84 78 7 49 33 59 59 65 85 84 8 52 34 55 60 75 86 88 9 55 35 59 61 77 87 78 10 60 36 68 62 72 88 84 11 54 37 71 63 66 89 76 12 59 38 62 64 70 90 76 13 56 39 71 65 78 91 83 14 55 40 72 66 75 92 83 15 53 41 63 67 75 93 87 16 54 42 66 68 75 94 80 17 58 43 62 69 68 95 79 18 54 44 73 70 79 96 88 19 59 45 76 71 83 97 84 20 63 46 65 72 85 98 81 21 55 47 65 73 76 99 83 22 53 48 64 74 82 100 93 23 66 49 66 75 79 101 91 24 57 50 64 76 85 102 93 25 61 51 63 77 80 103 92 26 56 52 73 78 81 104 96 Minicase Time Series Forecasting THE SCENARIO You are now Assistant to the Vice President for Marketing at Signi cant Concessions a company whose specialty is operating concession stands at events that involve large public gatherings If the opportunity looks right your company will set up and operate concession stands at anything from a football game to a goat roping Your boss has just learned that the beer concession at the Snellville Dome will soon be coming open This may or may not be a good opportunity for Signi cant Concessions Your boss has asked you to do some preliminary analysis before she enters serious negotiations with the Snellville Dome management They have provided her with a copy of a spreadsheet containing the last 10 years39 data on total event attendance by quarter They have also indicated that they could provide for the same period additional data The additional data they could provide for each event held in those same ten years includes per capita beer consumption in 16 ounce cups the price per cup in 2004 dollars the temperature at the event and the type of event She has asked that you submit to her a memorandum dealing with 2 topics 39 First she wants your forecast of Snellville Dome attendance by quarter for the five years following the end of the available data and she would like some information on how accurate you expect that forecast to be Also she wants to know what information you think you could squeeze out of the additional data if you had it She s willing to press them for it but only if there s a prospect of a worthwhile result Tell her what you could do and what statistical approach you would use THE DATA AND THE ANALYSIS The final step is to implement your forecast model and find ways to summarize both your results and the error term Then you should be ready to write your memo Think carefully about what your boss wants to know You probably could construct some useful summary information about your forecast of the next 5 years attendance at the Snellville Dome while you are there Oh and by the way you have remembered to format everything appropriately haven t you YOUR JOB Once you have completed the tasks we39ve just outlined you are most of the way done Write your boss the kind of memorandum that might get you a raise She wants the information mentioned on the first page of this document Minicase Time Series and Regression THE SCENARIO Congratulations We are now in the near future and you have a job With an of ce And a salary You work for MegaMammoth Properties a rm which manages commercial real estate for large investors Your boss Bill Ding has been wrestling with a perplexing problem Mr Ding knows that you passed this course with ying colors and gures that if anybody in the of ce can solve the problem you can The company manages a large of ce building Power Place in a suburb of Buford Georgia The building is less than 4 years old and MegaMammoth has managed it since it opened Lease provisions for such buildings are often complex and arcane and Power Place is no exception The lease for Power Place speci es that if the occupants of the building use more electricity than normal MegaMammoth can quotbackchargequot them for the excess quotNormalquot is de ned in the lease to mean their fair share of the expected annual usage at 95 occupancy Thus for example suppose a tenant occupies 20 ofthe space and 50 of the building is occupied altogether Then they are responsible for 20 50 or 40 of any electricity used during the year over and above 5095 of the annual 95 occupancy usage Only a lawyer or CPA could dream up such a complicated system but that s a simpli ed version of how it works Mr Ding has attempted to apply a quotRule of Thumbquot that managers of commercial real estate often apply He has rejected it on 2 counts First he believes that the results it yields are completely unrealistic Even more important applying that rule gives a gure that would not permit him to backcharge the tenants This is probably because of 3 major factors 1 The building has never even been close to 95 occupied 2 The quotcommon spacesquot in the building seem to use about the same amount of electricity per year at almost any level of occupancy 3 The usage of electricity appears to be highly seasonal The actual Data is on the next page After examining it you have hypothesized the in uence diagram that appears on this page The diagram suggests that if you are going to estimate the amount of electricity that would normally be used you must take into account the month of the year39s effect on the weather and the weather39s resulting effect on usage It also suggests as expected that you must take into account the proportion of the space occupied THE ANALYSIS The seasonality in the data will clearly interfere with your ability to measure the effect of occupancy on electricity usage You must first develop seasonal indices and deseasonalize the data Then perhaps a simple regression analysis will reveal the effect of occupancy on monthly electricity usage If you can estimate a meaningful regression equation of the form Y be b1 X where Y is deseasonalized monthly consumption in KWH kilowatt hours and X is proportion of Power Place that is occupied then you can answer Bill Ding39s question The answer will be Annual usage at 95 occupancy 12 be 95 b1 in kilowatt hours YOUR JOB Conduct the analysis using any combination of spreadsheet andor statistical software you prefer Write Bill Ding a memo presenting and explaining your results Do not make the memo technical He will probably need it in convincing tenants that the large bill he will present them is just and proper 18 Power Place Month Occupancy KWH Mar 39X1 1531 258801 Apr 39X1 1531 218144 May 39X1 1531 172389 Jun 39X1 1531 204089 Jul 39X1 1531 182770 Aug 39X1 1531 174790 Sep 39X1 1531 191275 Oct 39X1 1531 160733 Nov 39X1 1531 160393 Dec 39X1 1531 250057 Jan 39x2 1531 272315 Feb 39X2 1531 311148 Month of Year Mar 39X2 1531 229274 Apr39X2 1531 201303 May 39x2 1531 190875 Jun 39x2 1531 196769 Jul 39X2 1531 233098 Aug 39X2 1531 255380 Sep 39X2 2398 243214 Oct 39X2 3198 255419 Nov 39X2 3245 221817 Dec 39X2 3245 218251 Jan 39X3 3245 399001 Feb 39X3 3245 321716 Electricity Usage Rate in Mar 39X3 5275 415620 PFOPOITIOH 9f Space men and Occupied Apr 39X3 5275 333029 OCCUPIBd 8 aces May X3 5275 335982 p Jun 39X3 5275 325488 Jul 39X3 5275 395888 Aug 39X3 5275 348128 Sep 39X3 5880 358880 Oct 39X3 5782 340037 Nov 39X3 5782 287422 Dec 39X3 5782 281 875 Jan 39X4 5800 459952 Feb 39X4 5800 429184 Mar 39X4 5000 425202 Kilowatt Hours Used Per Apr 39X4 8000 381 084 Month May 39X4 8000 298841 Jun 39X4 8000 337947 Appendix 1 Double Exponential Smoothing The Double Exponential Smoothing model is a little more complicated than the simple exponential smoothing model because it attempts to stay quoton top ofquot any trend component that may be present in the data It doesn t cope well with strong seasonality so we won t expect it to work too well for Rocky There is a version of the model with one smoothing constant and one with two We ll look at the 2 constant version This model says F Ct T Ct X XYtl 1 XgtltFt1 T 3xC C1 17mm1 F1 is the forecast for periodt and tl indicates previous period C1 acts like the intercept in a linear equation but continuously updated T acts like the slope in a linear equation a smoothed period to period growth Y11 is the most recent actual observed value of the time series we are trying to forecast on is the smoothing constant for the quotinterceptquot C is the smoothing constant for the quottrendquot T Some versions of this model reuse on for this purpose The following spreadsheet shows how double exponential smoothing sometimes called trend adjusted exponential smoothing performed for Rocky No real improvement is visible but if you try new values for on and 3 you might be able to do a little better I used the rst actual value as the rst C1 intercept and 0 as the rst T1 slope Those are reasonable values for you to use too Go ahead and do it Raw Smoothed Smoothed Double Abs Squared to Qtr Data Intercept Trend Exponen Error Error Error Error alpha 003 Mar97 1 1078 1078 0 10780 beta 099 Jun97 2 104 10780 000 10780 380 38 4 1444 DoubleExponemialSmoothing Sap97 31119 10769 011 10757 433 4327 4 1872 Dec97 4 1892 10770 002 10772 8148 8148 43 663922 300 Mar98 5 998 11016 244 11260 1280 1280 13 16381 0 Jun98 61327 11221 206 11427 1843 1843 14 33966 Sap98 7 1035 11482 260 11743 1393 1393 13 19393 250 Dec98 8 1755 11701 219 11920 5630 5630 32 316997 Mar99 9 1145 12089 386 12475 1025 1025 9 10502 200 Jun99 10 1147 12444 356 12800 1330 1330 12 17683 a Sap99 11 1141 12760 316 13076 1666 1666 15 27759 a Dec99 12 2155 13026 267 13293 8257 8257 38 681806 g 150 Mar00 13 1347 13541 512 14053 583 583 4 3393 g Jun00 14 1314 14035 495 14530 1390 1390 11 19313 Sap00 15 1268 14488 453 14941 2261 2261 18 51140 100 Dec00 16 2845 14874 386 15260 13190 13190 46 1739810 Mar01 17 1642 15656 778 16433 013 013 0 002 50 Jun01 181772 16433 778 17211 509 509 3 2594 Sap01191868 17226 793 18019 661 661 4 4374 Dec01 20 2976 18038 812 18851 10909 10909 37 1190097 0 Mar02 21 19178 1136 20314 1 11 21 Jun02 22 Bias MAD MAPE MSE Quarter 2014 3205 17 252760 Winter s Method Winter39s Method adds a third smoothing constant and smoothed Seasonal Indices to the picture It would work much better for Rocky than what we ve seen so far but it is complex losing a lot of the advantages of exponential smoothing We won t try it but you should be aware of it 20 Appende 2 Calculation 0139 Seasonal Indexes Clearly it would make more sense to de ne quotthe year in which that JanuaryMarch quarter occursquot as the year whose midt e quarter is the quarter in question rather than using calendar years Unfortunately due to the fact that there are an even number of quarters to a year the year whose middle quarter is JanuaryMarch 2008 is the year starting August 16 2007 and ending August 15 2008 This consists of half of the JulySeptember 2007 quarter the three full quarters OctoberDecember 2007 JanuaryMarch 2008 and AprilJune 2008 and half of the JulySeptember 2008 quarter Since we don t have speci c data for halfquarters we have to estimate the two halfquarter sales gures as 50 of the corresponding quarterly totals In particular the total sales revenue of the year whose middle quarter is JanuaryMarch 2008 is estimated as 11192 1892 998 1327 10352 5294 The average quarterly sales revenue for the year whose middle quarter is JanuaryMarch 2008 is estimated as 52944 13235 The ratio of sales revenue for JanuaryMarch 2008 divided by the average quarterly sales revenue for the year whose middle quarter is JanuaryMarch 2008 is estimated as 99 813235 754 We can t calculate this for JanuaryMarch 2007 or AprilJune 2007 because these would require data from 2006 and we can t calculate it for JulySeptember 2001 or OctoberDecember 2001 because we don t have the required 2002 data In the following spreadsheet the column headed quotAnnual Totalquot shows the total sales revenue of the year whose middle quarter is indicated for each row The column headed quotCentered Mov Avgquot shows the average quarterly sales revenue for the year whose middle quarter is indicated for each row The column headed quotRaw Ratioquot shows the ratio of sales revenue for the quarter indicated for each row divided by the average quarterly sales revenue for the year for which that quarter is the middle quarter The rst number in the column headed quotAverage Ratioquot shows the average of the aforementioned ratios for all four JanuaryMarch quarters for which the ration could be computed ie 2008 2009 2000 and 2001 The second number in the column headed quotAverage Ratioquot shows the average of the aforementioned ratios for all four AprilJune quarters for which the ration could be computed ie 2008 2009 2000 and 2001 The third number in the column headed quotAverage Ratioquot shows the average of the aforementioned ratios for all four JulySeptember quarters for which the ration could be computed ie 2007 2008 2009 and 2000 The fourth number in the column headed quotAverage Ratioquot shows the average of the aforementioned ratios for all four OctoberDecember quarters for which the ration could be computed ie 2007 2008 2009 and 2000 The last number in the column headed quotAverage Ratioquot shows the average of these four averages this will be used in column I to force the average of the nal estimates of the seasonal indexes to be exactly 10 The column headed quotSeasonal Indexesquot shows the nal estimates of the seasonal indexes found by dividing the average ratios in Column H by their own average to force the average of the nal estimates of the seasonal indexes to be exactly 10 21 Raw Annual Centered Raw Average Seasonal from to Qtr Data Total Mov Avg Ratio Ratio lndeexes Jan97 Mar97 1 1078 8450 8454 Centered Moving Average Apr97 Jun97 2 104 8896 8900 Jul97 Sep97 3 1119 5089 127225 880 8031 8035 Oct97 Dec97 4 1892 5193 129813 1457 14604 14611 Jan98 Mar98 5 998 5294 132350 754 Average 8454 Apr98 Jun98 6 1327 5184 129588 1024 ofthe 8900 Jul98 Sep98 7 1035 5189 129713 798 Averages 8035 Oct98 Dec98 8 1755 5172 129300 1357 9995 14611 Jan99 Mar99 9 1145 5135 128375 892 8454 a Apr99 Jun99 10 1147 5388 134700 852 8900 g Jul99 Sep99 11 1141 5689 142225 802 8035 g Oct99 Dec99 12 2155 5874 146838 1468 14611 Jan00 Mar00 13 1347 6021 150513 895 8454 Apr00 Jun00 14 1314 6429 160725 818 8900 Jul00 SepOO 15 1268 6922 173038 733 8035 Oct00 Dec00 16 2845 7298 182450 1559 14611 Jan01 Mar01 17 1642 7827 195675 839 8454 Apr01 Jun01 18 1772 8193 204813 865 8900 Jul01 Sep01 19 1868 8035 Oct01 Dec01 20 2976 14611 1 11 21 Jan02 Mar02 21 8454 Quarter Apr02 Jun02 22 8900 There is an alternate way to compute the same seasonal indexes that involves less typing and slightly simpler Excel code Instead of calculating the total sales revenue of the year whose middle quarter is the one speci ed and dividing by 4 we take a simple 4quarter moving average then take a twoitem moving average of those moving averages A little algebra will show that this trick gives us exactly the same seasonal indexes as the averages center quarters we to a average moving averages to get our moving average centered on the data points While we are noticing things let s also notice that we couldn t get a centered moving average for the rst 2 Quarters We can t see it here but we can t get the last 2 Quarters either The data we d need to do that doesn t exist That is the penalty we must pay for using this method If we have at least 3 years of data it is worth paying the penalty We can t use the rst and last half year in getting our seasonal ratios 22 The notation Rid simply means the raw seasonal ratio for season i in year j To calculate Seasonal Indexes from here is the same regardless of which way we got to the centered moving average aw Moving Centered Raw Average Seasona from to Qtr Data Average Mov Avg Ratio Ratio Indeexes Jan97 Mar97 1 1078 8450 8454 Apr97 Jun97 2 1040 8896 8900 Jul97 Sep97 3 1119 128225 1272250 880 8031 8035 Oct97 Dec97 4 1892 126225 1298125 1457 14604 14611 Jan98 Mar98 5 998 133L400 1323500 754 Average 8454 Apr98 Jun98 6 1327 131300 1295875 1024 ofthe 8900 Jul98 Sep98 7 1035 121875 1297125 798 Averages 8035 Oct98 Dec98 8 1755 131550 1293000 1357 9995 14611 Jan99 Mar99 9 1145 121050 1283750 892 8454 Apr99 Jun99 10 1147 129100 1347000 852 8900 Jul99 Sep99 11 1141 139100 1422250 802 8035 Oct99 Dec99 12 2155 144150 1468375 1468 14611 Jan00 Mar00 13 1347 148325 1505125 895 8454 Apr00 Jun00 14 1314 152100 1607250 818 8900 Jul00 Sepoo 15 1268 169350 1730375 733 8035 Oct00 Dec00 16 2845 176125 1824500 1559 14611 Jan01 Mar01 17 1642 33175 1956750 839 8454 Apr01 Jun01 18 1772 2011 2048125 865 8900 Jul01Sep01 19 1868 20639450 8035 Oct01 Dec01 20 2976 14611 Jan02 Mar02 21 8454 Apr02 Jun02 22 8900 23

### BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.

### You're already Subscribed!

Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'

## Why people love StudySoup

#### "I was shooting for a perfect 4.0 GPA this semester. Having StudySoup as a study aid was critical to helping me achieve my goal...and I nailed it!"

#### "Selling my MCAT study guides and notes has been a great source of side revenue while I'm in school. Some months I'm making over $500! Plus, it makes me happy knowing that I'm helping future med students with their MCAT."

#### "There's no way I would have passed my Organic Chemistry class this semester without the notes and study guides I got from StudySoup."

#### "It's a great way for students to improve their educational experience and it seemed like a product that everybody wants, so all the people participating are winning."

### Refund Policy

#### STUDYSOUP CANCELLATION POLICY

All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email support@studysoup.com

#### STUDYSOUP REFUND POLICY

StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here: support@studysoup.com

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to support@studysoup.com

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.