Document Sample

Seasonality Often times the observations in a time series data set represent less than a year. In such situations it is useful to control for seasonal effects in order to understand how the data varies over time. Time series data often is organized into less than year long intervals. When working with such data it is worth knowing what seasonality exists within the year. For example, the seasonality in sales across the year would be very helpful for managing inventory requirements. Regression analysis easily calculates seasonal variations using dummy variables. This tutorial introduces how to set up and interpret these variables. This tutorial employs three data sets that have been created to show seasonality in linear and constant growth settings. (These data sets have been created in a fashion similar to the data set in the growth modeling tutorial. The setup for creating each data set has been hidden in rows 1-5 and columns B-E on each data sheet.) Data set A has a linear trend and the data shows no seasonality (and is thus denoted DA-L,non-s). Data set B has linear trend and has seasonality (DB-L,s). Data set C has nonlinear trend and seasonality (DC-NL,s). The thee data worksheets have been reduced so that you do not have to scroll down when running regressions. Each includes data worksheet includes a graph of the data. For each data set regressions are run with and without seasonal dummy variables. In each data set, three seasonal dummy variables are created using Q3 as the base. Seasonal dummy variables are easy to construct if you use the repetitive aspect of Excel to advantage. Code only one year's worth of dummy variables (for example see cells I7:K10 in the DA-L,non-s worksheet). At the start of the second year, reference the first (note the equations in cells I11:K11), then drag these equations to the end of the data set. The difference between data set A and B is that B is seasonal while A is not. This is easily seen by comparing the two graphs on pages DA-L,non-s and DA-L,s. B's graph has regular peaks and valleys, while A's does not. Note that this same regularity of peaks and valleys occurs with C but C is nonlinear in nature (it is based on a constant growth model). Two regressions are shown for A and B (the first letter R stands for regression (and D stands for data set) and the second letter stands for the data set that the model is estimated from), and four regressions are shown for C. The discussion in each regression (and across regressions) focus on the line fit plot and residual plot together with a few numbers that have been highlighted and formatted to ease comparison. Coefficient interpretation is put off until data set C when linear and nonlinear models can be directly compared. 2 Data set A, DA-L,non-s) R always increases with additional variables so the equation with seasonal 2 2 dummies necessarily has higher R but adjusted R declines and Standard Error of Estimate (SEE) increases with the addition of the seasonal dummies. The increased explanatory power from the extra independent variables did not overcome the harm caused by loss of degrees of freedom. The t-statistics for each of the seasonal dummy variables is not statistically significant. Finally, there is no significant change in residual plot between equations, neither exhibits a pattern. The best fit equation in this instance is RA,L. 2 2 Data set B, DB-L,s) The seasonality in data set B dominates the data set. As a result, R and adjusted R increase dramatically and SEE decreases dramatically. T-statistics for each coefficient are strongly significant in the seasonal equation and the strong seasonal pattern of the residuals in the RB,L equation vanishes in the RB,L+s equation. The best fit equation is RB,L+s. Before examining data set C it is worth considering why the Durbin-Watson statistic in RB,L did not show that we had serial correlation. The Durbin-Watson test is for first order serial correlation (i.e.. consecutive quarters' errors are correlated). In this instance, we do not have first order serial correlation, we have 4th order serial correlation because we have quarterly seasonal data. For further help in understanding time series correlations, consult an econometrics text. Data set C, DC-NL,s) The seasonality in data set C is also clear, as a result the model without seasonal dummies, RC,L, does not provide a very effective fit. The residual plot has a strong regular saw-tooth pattern. Heteroskedasticity is also present here (and in RC,L+s) as the spread of the residual plot is an increasing function of time. (This was not present in the first two residual plots because the random error terms that were introduced into those data sets was additive in nature; in data set C, the random error term is multiplicative. (Compare the equation in cell F7 of DB-L,s and DC-NL,S. The random error component is in the (hidden) column E.)) The seasonal dummies take care of the saw-tooth pattern in RC,L+s but the nonlinearity is visible in the residual plot. The Durbin-Watson statistic also supports this view as it exhibits positive serial correlation (dlower=1.08=dw). By contrast, the Durbin-Watson statistic for the RC,NL+s equation exhibits no serial correlation (dw=2.10 in cell D28). (A second nonlinear equation, RC,NL+s123, is shown that only differs in the choice of base quarter. This allows us to more finely understand the interpretation of coefficients within the nonlinear model.) Before we discuss the interpretation of coefficients in this model it is useful to consider coefficient interpretation in the linear Coefficient Interpretation The coefficients in these basic time series models are readily interpretable if you remember a simple rule. If the dependent variable is Q, then coefficients represent absolute levels of change in Q. By contrast, if the dependent variable is ln(Q), then coefficients represent relative levels of change in Q. Consider the quarterly dummy variable coefficients in the RB,L+s worksheet. Each coefficient represents the best guess estimate of how much larger or smaller this quarter is than the base quarter, ceteris paribus. That is, the first quarter is 67 smaller than the third, the second is 50 smaller, and the fourth is 54 larger, ceteris paribus. (Ceteris paribus here refers to holding the trend constant (i.e.. the time variable) but of course, as we move from one quarter to the next this does not happen.) The coefficient of time in the linear model provides a best guess estimate to the increase (or decrease) in number of units per time period, ceteris paribus. The notion of ceteris paribus is very important in this context. Using the equation from the RB,L+s worksheet, the coefficient of time is 1.1. This says that each quarter, expect an increase in Q of just over one unit, ceteris paribus. This is not the same as saying that in moving from quarter 1 to quarter 2 you increase Q by 1.1 units because as you move from quarter 1 to quarter 2 two other parts of the equation change; the Q1 dummy variable turns off and the Q2 dummy variable turns on (notice it is easiest to think of dummy variables as light switched that are either on or off). As a result, the correct statement for moving from quarter 1 to quarter 2 is: we expect Q to increase by 18.2 units in moving from the first to the second quarter of a year (18.2=-(-67.4)+1.1+(-50.3) = Q1turnedoff+1quarter+Q2turnedon). The coefficients in this model refer to absolute units of Q; the same is not true in the logarithmic model. The time trend in the nonlinear model represents the deseasonalized growth rate per period. As discussed in c the growth rate tutorial, the growth rate in Q per unit of time is given by e - 1 where c is the coefficient of time in the ln(Q) equation. Since we are interested in annual growth rates, we need to annualize the quarterly information given in this model. The table in cells C1:J8 of the RC,NL+s123 page provide various interpretations of quarterly and annual growth rates based on different notions of compounding. The coefficient of a quarterly dummy variable shows the difference in size of that quarter in relative terms relative to the base quarter. (With the linear model the coefficient of a quarterly dummy variable shows the difference in size of that quarter in absolute terms relative to the base quarter). Two interpretations for each coefficient are given in the table in cells A23:G26 of the RC,NL+s page. Both are based on notions of relative The two nonlinear seasonal regressions only differ in their choice of base quarter. RC,NL+s uses Q3 and RC,NL+s123 uses Q4. By clicking back and forth between the pages you can see that many aspects of the two regressions are identical. This should not be surprising in light of the discussion in the dummy variable tutorial that compared two equations for Canadian bond sales that only differed in their choice of base for their dummy variable (peace versus war). Both equations had identical summary statistics and ANOVA tables. The only difference lay in the magnitudes and signs of the dummy variables. The same is true here. The reason RC,NL+s123 was included in this tutorial is to provide a second way of interpreting seasonal dummy variables. Since the 4th quarter is the peak quarter, the dummy variables for the other quarters can be interpreted as how much slack there is relative to peak in each quarter (see the interpretation of these coefficients in the table in cells A23:G26 of the RC,NL+s123 page). Such information would be useful for inventory managers, marketing managers, or for a maintenance manager wishing to coordinate scheduled maintanence time Quarter Quantity (quarters) Q1 Q2 Q4 90.01 97 0 1 0 0 90.02 88 1 0 1 0 160 90.03 104 2 0 0 0 90.04 116 3 0 0 1 91.01 116 4 1 0 0 91.02 122 5 0 1 0 140 91.03 84 6 0 0 0 91.04 105 7 0 0 1 92.01 119 8 1 0 0 120 92.02 98 9 0 1 0 92.03 103 10 0 0 0 92.04 128 11 0 0 1 100 93.01 104 12 1 0 0 93.02 103 13 0 1 0 93.03 106 14 0 0 0 93.04 94 15 0 0 1 80 94.01 110 16 1 0 0 94.02 131 17 0 1 0 94.03 119 18 0 0 0 60 94.04 105 19 0 0 1 95.01 117 20 1 0 0 0 4 8 95.02 117 21 0 1 0 95.03 135 22 0 0 0 95.04 122 23 0 0 1 96.01 122 24 1 0 0 96.02 120 25 0 1 0 96.03 146 26 0 0 0 96.04 136 27 0 0 1 97.01 152 28 1 0 0 97.02 122 29 0 1 0 97.03 147 30 0 0 0 97.04 115 31 0 0 1 Quantity 12 16 20 24 28 32 SUMMARY OUTPUT Quantity time (quarters) Line Fit P Regression Statistics 160 Multiple R 0.661874 140 R Square 0.4381 120 Adj. R^2 0.4193 100 SEE 12.52 80 Observations 32 60 40 ANOVA 20 df SS MS F Significance F 0 Regression 1 3666.638 3666.638 23.38815 3.7E-05 0 4 8 12 Residual 30 4703.199 156.7733 time (quarters) Total 31 8369.837 Residuals time (quarters) Residual P Coefficients Standard Error t Stat P-value Lower 95%Upper 95% 30 95.0% Lower Upper 95.0% Intercept 97.79 4.325032 22.6 2.13E-20 88.9545 106.6203 20 88.9545 106.6203 time (quarters)1.16 0.239725 4.8 3.7E-05 0.669759 1.648927 0.669759 1.648927 10 0 0 4 8 12 -10 RESIDUAL OUTPUT -20 2.04 dw -30 time (quarters) Predicted Quantity Observation Residuals 9574.879 numerator 1 97.78739 -0.78971 2 98.94673 -10.7236 98.68143 3 100.1061 4.3365 226.8055 4 101.2654 14.49932 103.2829 5 102.4248 13.55874 0.884683 6 103.5841 18.74723 26.92039 7 104.7434 -20.5793 1546.578 8 105.9028 -1.2446 373.8314 9 107.0621 11.8881 172.4677 10 108.2215 -10.0885 482.9698 11 109.3808 -6.28286 14.48276 12 110.5402 17.36417 559.1817 13 111.6995 -8.16861 651.9226 14 112.8588 -9.63514 2.150706 15 114.0182 -7.75326 3.541479 16 115.1775 -21.3568 185.0575 17 116.3369 -6.01612 235.3377 18 117.4962 13.54426 382.6085 19 118.6556 0.692975 165.1556 20 119.8149 -14.4698 229.9106 21 120.9742 -4.24415 104.5645 22 122.1336 -4.83599 0.350276 23 123.2929 12.13349 287.9632 24 124.4523 -2.30511 208.4732 25 125.6116 -3.47319 1.364398 26 126.771 -6.90303 11.7638 27 127.9303 17.79182 609.8357 28 129.0896 6.56709 125.9946 29 130.249 21.50757 223.2178 30 131.4083 -8.95739 928.1136 31 132.5677 14.04689 529.197 32 133.727 -18.851 1082.27 time (quarters) Line Fit Plot 12 16 20 24 28 32 time (quarters) time (quarters) Residual Plot Upper 95.0% 12 16 20 24 28 32 time (quarters) SUMMARY OUTPUT Quantity time (quarters) Line Fit P Regression Statistics 160 Multiple R 0.679139 140 R Square 0.4612 120 Adj. R^2 0.3814 100 SEE 12.92 80 Observations 32 60 ANOVA 40 df SS MS F Significance F 20 Regression 4 3860.417 965.1043 5.77853 0.00171 0 Residual 27 4509.419 167.0155 0 4 8 12 Total 31 8369.837 time (quarters) Standard Error Coefficients t Stat Lower 95.0% P-value Lower 95%Upper 95% Residuals Upper 95.0% (quarters) Residual P time Intercept 99.29 6.064914 16.4 1.52E-15 86.84704 111.7354 86.84704 111.7354 30 time (quarters)1.18 0.249266 4.7 6.38E-05 0.666295 1.689199 0.666295 1.689199 Q1 1.27 6.480927 0.20 0.845947 -12.0264 14.56911 20 -12.0264 14.56911 Q2 -4.14 6.46653 -0.64 0.527784 -17.4046 9.131827 10 -17.4046 9.131827 Q4 -4.29 6.46653 -0.66 0.512564 -17.5595 8.976932 -17.5595 8.976932 0 0 4 8 12 -10 -20 -30 time (quarters) RESIDUAL OUTPUT 1.992763 dw Predicted Quantity Observation Residuals 8986.203 numerator 1 100.5626 -3.56488 2 96.33256 -8.10939 20.65259 3 101.6467 2.795875 118.9249 4 98.53316 17.23157 208.3894 5 105.2735 10.70995 42.53153 6 101.0435 21.28778 111.8904 7 106.3577 -22.1936 1890.627 8 103.2441 1.41404 557.3188 9 109.9845 8.965691 57.02743 10 105.7545 -7.62154 275.1363 11 111.0687 -7.97071 0.12192 12 107.9551 19.94919 779.5209 13 114.6955 -11.1646 968.0697 14 110.4655 -7.24182 15.38845 15 115.7797 -9.51473 5.166123 16 112.6661 -18.8454 87.06205 17 119.4065 -9.08576 95.25129 18 115.1765 15.86397 622.4887 19 120.4906 -1.14211 289.2068 20 117.3771 -12.032 118.5904 21 124.1175 -7.3874 21.57265 22 119.8875 -2.5899 23.016 23 125.2016 10.22478 164.2162 24 122.0881 0.059061 103.3419 25 128.8285 -6.69006 45.5506 26 124.5985 -4.73056 3.839643 27 129.9126 15.8095 421.894 28 126.7991 8.857649 48.32826 29 133.5395 18.21708 87.59895 30 129.3095 -6.85854 628.7865 31 134.6236 11.99095 355.3033 32 131.5101 -16.634 819.3905 time (quarters) Line Fit Plot Q1 Residual Plot Residuals 40 Q2 Residual Plot 20 0 Q4 Residual Plot -20 0 40 Residuals 0.5 1 1.5 -40 20 0 40 Residuals -20 0 20 Q1 0.5 1 1.5 -40 0 Q1 Line Fit Plot -20 0 0.2 0.4 0.6 0.8 1 1.2 12 16 20 24 28 32 -40 Q2 time (quarters) Q2 Line Fit Plot Quantity 200 Q4 150 100 Upper 95.0% (quarters) Residual Plot time 50 Quantity 0 Q4 Line Fit Plot Quantity 200 0 150 0.5 1 1.5 Predicted Quantity 100 50 0 Quantity Q1 200 0 150 0.5 1 1.5 100 50 0 Q2 0 0.5 1 12 16 20 24 28 32 Q4 time (quarters) Quantity Q4 Line Fit Plot Predicted Quantity Quantity Predicted Quantity Quantity 1.5 Predicted Quantity time Quarter Quantity Q1 Q2 Q4 (quarters) 90.01 37 0 1 0 0 90.02 48 1 0 1 0 240 90.03 90.04 104 167 2 3 0 0 0 0 0 1 220 91.01 54 4 1 0 0 200 91.02 91.03 80 84 5 6 0 0 1 0 0 0 180 91.04 158 7 0 0 1 160 92.01 92.02 54 55 8 9 1 0 0 1 0 0 140 92.03 103 10 0 0 0 120 92.04 183 11 0 0 1 93.01 36 12 1 0 0 100 93.02 58 13 0 1 0 80 93.03 106 14 0 0 0 93.04 151 15 0 0 1 60 94.01 41 16 1 0 0 40 94.02 84 17 0 1 0 94.03 119 18 0 0 0 20 94.04 95.01 165 45 19 20 0 1 0 0 1 0 0 95.02 69 21 0 1 0 0 4 8 95.03 135 22 0 0 0 95.04 184 23 0 0 1 96.01 48 24 1 0 0 96.02 70 25 0 1 0 96.03 146 26 0 0 0 96.04 199 27 0 0 1 97.01 75 28 1 0 0 97.02 71 29 0 1 0 97.03 147 30 0 0 0 97.04 180 31 0 0 1 Quantity 8 12 16 20 24 28 32 SUMMARY OUTPUT Quantity time (quarters) Line Fit 250 Regression Statistics Multiple R 0.300881 200 R Square 0.0905 150 Adj. R^2 0.0602 SEE 50.23 100 Observations 32 50 ANOVA 0 df SS MS F Significance F 0 4 8 12 time (quarters) Regression 1 7533.908 7533.908 2.986226 0.094258 Residual 30 75686.59 2522.886 Residuals time (quarters) Residual Total 31 83220.5 100 Standard Error t Stat Coefficients P-value Lower 95%Upper 95% Upper 95.0% Lower 95.0% 50 Intercept 76.02 17.35011 4.38 0.000133 40.59014 111.4574 40.59014 111.4574 time (quarters)1.66 0.961671 1.73 0.094258 -0.30216 3.625828 -0.30216 3.625828 0 0 4 8 12 -50 RESIDUAL OUTPUT 2.13 dw -100 time (quarters) Predicted Quantity Observation Residuals 161175.5 numerator 1 76.02375 -39.0261 2 77.68559 -29.8624 83.97257 3 79.34742 25.09515 3020.334 4 81.00926 86.25548 3740.586 5 82.67109 -29.0876 13304.02 6 84.33293 -4.0016 629.3071 7 85.99476 -1.83064 4.713055 8 87.6566 70.50159 5231.951 9 89.31843 -35.1682 11166.11 10 90.98027 -36.4473 1.636013 11 92.6421 10.45585 2199.904 12 94.30394 89.10038 6184.962 13 95.96578 -59.6349 22122.18 14 97.62761 -39.6039 401.2401 15 99.28945 6.975484 2169.639 16 100.9513 50.36941 1883.033 17 102.6131 -61.8924 12602.71 18 104.275 -20.0345 1752.083 19 105.9368 13.41174 1118.65 20 107.5986 57.24645 1921.481 21 109.2605 -64.5304 14829.59 22 110.9223 -42.0247 506.505 23 112.5841 22.84229 4207.726 24 114.246 69.40119 2167.732 25 115.9078 -68.1694 18925.66 26 117.5696 -47.7017 418.9254 27 119.2315 26.49065 5504.506 28 120.8933 78.26342 2680.42 29 122.5551 -47.5986 15841.25 30 124.217 -53.366 33.26347 31 125.8788 20.73575 5491.075 32 127.5406 52.83538 1030.386 time (quarters) Line Fit Plot 12 16 20 24 28 32 time (quarters) time (quarters) Residual Plot Upper 95.0% 12 16 20 24 28 32 time (quarters) SUMMARY OUTPUT Quantity time (quarters) Line Fit Plo Regression Statistics 250 Multiple R 0.97217 R Square 0.9451 200 Adj. R^2 0.9370 150 SEE 13.01 Observations 32 100 ANOVA 50 df SS MS F Significance F 0 Regression 4 78652.4 19663.1 116.2199 1.32E-16 0 4 8 12 16 20 Residual 27 4568.095 169.1887 time (quarters) Total 31 83220.5 Standard Error t Stat Coefficients Lower 95.0% P-value Lower 95%Upper 95% Residual Upper 95.0% (quarters) Residual Pl time Intercept 101.29 6.104244 16.6 30 s 1.09E-15 88.76634 113.8161 88.76634 113.8161 time (quarters)1.1 0.250883 4.2 0.000263 0.537978 1.567515 0.537978 1.567515 20 Q1 -67.4 6.522955 -10.3 7.05E-11 -80.7626 -53.9947 -80.7626 -53.9947 Q2 -50.3 6.508465 -7.7 2.64E-08 -63.6156 -36.9071 10 -63.6156 -36.9071 Q4 54.3 6.508465 8.3 5.87E-09 40.97946 67.68797 40.97946 67.68797 0 0 4 8 12 16 20 -10 18.2 -20 -30 RESIDUAL OUTPUT 1.92 dw time (quarters) Predicted Quantity Observation Residuals 8760.612 numerator 1 33.9126 3.085118 2 52.0826 -4.25939 53.94186 3 103.397 1.045875 28.14589 4 158.783 8.481575 55.28962 5 38.1235 15.45995 48.69779 6 56.2935 24.03778 73.57912 7 107.608 -23.4436 2254.478 8 162.994 -4.83596 346.2428 9 42.3345 11.81569 277.2775 10 60.5045 -5.97154 316.3857 11 111.819 -8.72071 7.557937 12 167.205 16.19919 621.0015 13 46.5455 -10.2146 697.6898 14 64.7155 -6.69182 12.4102 15 116.03 -9.76473 9.44278 16 171.416 -20.0954 106.7235 17 50.7565 -10.0358 101.1971 18 68.9265 15.31397 642.6084 19 120.241 -0.89211 262.637 20 175.627 -10.782 97.81058 21 54.9675 -10.2374 0.296629 22 73.1375 -4.2399 35.97 23 124.452 10.97478 231.4867 24 179.838 3.809061 51.34759 25 59.1785 -11.4401 232.5356 26 77.3485 -7.48056 15.67765 27 128.663 17.0595 602.2145 28 184.049 15.10765 3.80973 29 63.3895 11.56708 12.53563 30 81.5595 -10.7085 496.2031 31 132.874 13.74095 597.7776 32 188.26 -7.88404 467.6405 me (quarters) Line Fit Plot Q1 Residual Plot 40 Q2 Residual Plot Residuals 20 0 Plot -20 0 Q4 Residual 0.4 40 0.2 Residuals 0.6 0.8 1 1.2 -40 20 0 Residuals 40 Q1 20 -20 0 0.2 0.4 0.6 0.8 1 1.2 0 -40 Q1 Line Fit Plot 20 24 28 32 -20 0 0.2 0.4 0.6 0.8 1 Q2 1.2 -40 ime (quarters) Q2 Line Fit Plot Quantity 300 Q4 200 me (quarters) Residual Plot 100 Quantity 0 Q4 Line Fit Plot Quantity 300 0 200 0.5 1 1.5 Predicted Quantity 100 0 Quantity Q1 300 0 200 0.5 1 1.5 100 0 Q2 0 0.5 1 20 24 28 32 Q4 time (quarters) 1.2 Quantity Q4 Line Fit Plot Predicted Quantity Quantity Predicted Quantity Quantity 1.5 Predicted Quantity time Quarter Quantity ln(Q) Q1 Q2 Q4 (quarters) 90.01 52 3.946 0 1 0 0 90.02 74 4.310 1 0 1 0 90.03 83 4.414 2 0 0 0 600 90.04 147 4.987 3 0 0 1 91.01 57 4.034 4 1 0 0 91.02 79 4.372 5 0 1 0 500 91.03 116 4.750 6 0 0 0 91.04 92.01 161 77 5.080 4.340 7 8 0 1 0 0 1 0 400 92.02 75 4.316 9 0 1 0 92.03 92.04 133 190 4.891 5.247 10 11 0 0 0 0 0 1 300 93.01 68 4.226 12 1 0 0 93.02 93.03 120 134 4.790 4.899 13 14 0 0 1 0 0 0 200 93.04 213 5.361 15 0 0 1 94.01 98 4.581 16 1 0 0 100 94.02 141 4.951 17 0 1 0 94.03 164 5.097 18 0 0 0 94.04 268 5.590 19 0 0 1 0 95.01 98 4.580 20 1 0 0 95.02 153 5.027 21 0 1 0 0 4 8 95.03 264 5.575 22 0 0 0 95.04 308 5.731 23 0 0 1 96.01 162 5.088 24 1 0 0 96.02 222 5.404 25 0 1 0 96.03 257 5.551 26 0 0 0 96.04 438 6.082 27 0 0 1 97.01 167 5.117 28 1 0 0 97.02 278 5.628 29 0 1 0 97.03 367 5.905 30 0 0 0 97.04 535 6.283 31 0 0 1 Quantity 12 16 20 24 28 32 SUMMARY OUTPUT Quantity time (quarters) Line Fit Plo 600 Regression Statistics 500 Multiple R 0.762725 400 R Square 0.5817 300 Adj. R^2 0.5678 200 SEE 75.05 Observations 32 100 0 0 4 8 12 ANOVA time (quarters) df SS MS F Significance F Regression 1 235046.8 235046.8 41.7273 3.89E-07 Residuals time (quarters) Residual Pl Residual 30 168987.8 5632.927 Total 31 404034.6 250 200 Coefficients Standard Error t Stat P-value Lower 95%Upper 95% 150 95.0% Lower Upper 95.0% Intercept 34.14964 25.9251 1.317243 0.19773 -18.7964 87.09569 100 -18.7964 87.09569 50 9.282287 1.436961 6.459667 time (quarters) 3.89E-07 6.347625 12.21695 6.347625 12.21695 0 -50 0 4 8 12 -100 -150 RESIDUAL OUTPUT time (quarters) 1.79 dw Predicted Quantity Observation Residuals 301661.8 numerator 1 34.14964 17.56501 2 43.43193 31.03957 181.5637 3 52.71421 29.88319 1.337227 4 61.9965 84.55195 2988.674 5 71.27879 -14.7754 9865.919 6 80.56108 -1.39791 178.9567 7 89.84336 25.77934 738.6033 8 99.12565 61.68121 1288.944 9 108.4079 -31.6996 8719.983 10 117.6902 -42.8308 123.9037 11 126.9725 6.088941 2393.145 12 136.2548 53.81941 2278.197 13 145.5371 -77.0645 17130.59 14 154.8194 -34.5446 1807.937 15 164.1017 -29.9891 20.75283 16 173.384 39.47225 4824.879 17 182.6662 -85.0459 15504.77 18 191.9485 -50.6154 1185.46 19 201.2308 -37.6434 168.2735 20 210.5131 57.14769 8985.346 21 219.7954 -122.243 32180.92 22 229.0777 -76.572 2085.818 23 238.36 25.3796 10394.12 24 247.6423 60.71729 1248.752 25 256.9245 -94.8915 24214.09 26 266.2068 -43.8907 2601.076 27 275.4891 -18.0232 669.1293 28 284.7714 153.0329 29260.2 29 294.0537 -127.247 78556.66 30 303.336 -25.3544 10382.03 31 312.6183 54.22911 6333.541 32 321.9005 213.4404 25348.24 time (quarters) Line Fit Plot 12 16 20 24 28 32 time (quarters) time (quarters) Residual Plot Upper 95.0% 12 16 20 24 28 32 time (quarters) SUMMARY OUTPUT Quantity time (quarters) Line Fit Plot Regression Statistics 600 Multiple R 0.925081 500 R Square 0.8558 400 Adj. R^2 0.8344 SEE 46.46 300 Observations 32 200 ANOVA 100 df SS MS F Significance F 0 Regression 4 345762.3 86440.58 40.05156 5.57E-11 0 4 8 12 Residual 27 58272.29 2158.233 time (quarters) Total 31 404034.6 Residuals time (quarters) Residual Plo Standard Error t Stat Coefficients P-value Lower 95%Upper 95%Lower 95.0% Upper 95.0% Intercept 53.25 21.80195 2.4 150 0.021418 8.514727 97.98249 8.514727 97.98249 time (quarters)8.52 0.896055 9.5 4.1E-10 6.685242 10.36234 6.685242 10.36234 100 Q1 -75.41 23.29742 -3.2 0.003192 -123.208 -27.6029 -123.208 -27.6029 Q2 -38.24 23.24567 -1.65 0.111535 -85.9385 9.453791 -85.9385 9.453791 50 Q4 84.28 23.24567 3.6 0.001181 36.58218 131.9745 36.58218 131.9745 0 0 4 8 12 -50 -100 time (quarters) RESIDUAL OUTPUT 1.08 dw Predicted Quantity Observation Residuals 62981.71 numerator 1 -22.1566 73.87126 2 23.53005 50.94145 525.7761 3 70.29619 12.30121 1493.068 4 163.0983 -16.5499 832.3839 5 11.93857 44.56484 3735.006 6 57.62522 21.53794 530.2379 7 104.3914 11.23134 106.226 8 197.1935 -36.3866 2267.471 9 46.03374 30.67455 4497.202 10 91.7204 -16.861 2259.63 11 138.4865 -5.42509 130.7805 12 231.2887 -41.2145 1280.879 13 80.12892 -11.6563 873.6844 14 125.8156 -5.54082 37.39906 15 172.5817 -38.4692 1084.275 16 265.3838 -52.5276 197.6408 17 114.2241 -16.6038 1290.524 18 159.9107 -18.5776 3.896124 19 206.6769 -43.0895 600.8297 20 299.479 -31.8182 127.0407 21 148.3193 -50.7666 359.0408 22 194.0059 -41.5002 85.86594 23 240.7721 22.9675 4156.085 24 333.5742 -25.2146 2321.519 25 182.4144 -20.3814 23.36031 26 228.1011 -5.785 213.0544 27 274.8672 -17.4013 134.939 28 367.6694 70.13498 7662.605 29 216.5096 -49.7026 14361.05 30 262.1963 15.78527 4288.663 31 308.9624 57.88496 1772.384 32 401.7645 133.5764 5729.2 time (quarters) Line Fit Plot Q1 Residual Plot 200 Residuals 100 Q2 Residual Plot 0 -100 0 Q4 0.5 200 Residual Plot 1 1.5 Residuals 100 Q1 200 Residuals 0 100 12 16 20 24 28 32 -100 0 0.5Q1 1 Line Fit Plot 0 time (quarters) -100 0 0.2 0.4 0.6 0.8 Q2 1 1.2 600 Q4 Q2 Line Fit Plot Quantity time (quarters) Residual Plot 400 Upper 95.0% 200 0 600 Quantity -200 0 400 0.5 1 1.5 200 0 Q1 600 Quantity -200 0 400 0.5 1 200 0 Q2 -200 0 0.5 12 16 20 24 28 32 time (quarters) 1.5 Q2 Line Fit Plot Quantity Q4 Line Fit Plot Predicted Quantity Quantity Predicted Quantity 1 1.5 Quantity Predicted Quantity 0.5 1 1.5 Q4 SUMMARY OUTPUT time (quarters) Line Fit P Regression Statistics ln(q) Multiple R 0.9839537 8.0 R Square 0.9682 6.0 Adj. R^2 0.9634 SEE 0.1148 4.0 Observations 32 2.0 ANOVA 0.0 df SS MS F Significance F Regression 4 10.82449 2.70612 205.3 8.7E-20 0 4 8 12 Residual 27 0.355931 0.01318 time (quarters) Total 31 11.18042 S Coefficients tandard Error t Stat P-valueLower 95% Upper 95% Lower 95.0%Upper 95.0%(quarters) Residual Plo Residuals time Intercept 4.377 0.053882 81.2 8E-34 4.26673 4.487848174 4.266733 4.487848 0.2 time (quarters) 0.047 0.002215 21.4 2E-18 0.04283 0.051915911 0.042828 0.051916 Q1 -0.551 0.057578 -9.6 4E-10 -0.66956 0.1 -0.433282652 -0.66956 -0.43328 Q2 -0.238 0.057451 -4.1 3E-04 -0.35609 -0.120330568 -0.35609 -0.12033 0 Q4 0.363 0.057451 6.3 9E-07 0.24466 0.480420389 0.244663 4 0.48042 0 8 12 -0.1 =exp(Qcoef) Understanding the seasonal dummies =exp(Qcoef)-1 57.6% Q1 as a percent of Q3 Q1 less than Q3 by -42.4% -0.2 78.8% Q2 as a percent of Q3 Q2 less than Q3 by -21.2% -0.3 143.7% Q4 as a percent of Q3 Q4 more than Q3 by 43.7% time (quarters) ObservationPredicted ln(q)Residuals 0.74646 numerator 1 3.8258669 0.119874 2.10 dw 2 4.1864535 0.123963 1.7E-05 3 4.4720347 -0.058056 0.03313 4 4.8819485 0.105408 0.02672 5 4.015355 0.018946 0.00748 6 4.3759416 -0.00443 0.00055 7 4.6615228 0.08881 0.00869 8 5.0714366 0.008767 0.00641 9 4.2048431 0.135167 0.01598 10 4.5654296 -0.249818 0.14821 11 4.8510109 0.0398 0.08388 12 5.2609247 -0.01351 0.00284 13 4.3943312 -0.167897 0.02384 14 4.7549177 0.034861 0.04111 15 5.040499 -0.141819 0.03122 16 5.4504127 -0.089796 0.00271 17 4.5838193 -0.002734 0.00758 18 4.9444058 0.006714 8.9E-05 19 5.2299871 -0.132639 0.01942 20 5.6399008 -0.05018 0.0068 21 4.7733074 -0.192915 0.02037 22 5.1338939 -0.106692 0.00743 23 5.4194751 0.155487 0.06874 24 5.8293889 -0.098122 0.06432 25 4.9627955 0.125005 0.04979 26 5.323382 0.080718 0.00196 27 5.6089632 -0.058076 0.01926 28 6.018877 0.062895 0.01463 29 5.1522836 -0.035446 0.00967 30 5.5128701 0.114685 0.02254 31 5.7984513 0.106495 6.7E-05 32 6.2083651 0.074539 0.00102 time (quarters) Line Fit Plot Q1 Residual Plot 0.2 Q2 Residual Plot Residuals 0 -0.2 0 0.2 0.5 1 1.5 Residuals -0.4 Q4 Residual Plot 0 -0.2 0 0.5 Q1 1 1.5 0.2 Residuals -0.4 0 12 16 20 24 28 32 Q2 Q1 Line Fit Plot -0.2 0 0.2 0.4 0.6 0.8 1 1.2 time (quarters) -0.4 Q2 Line Fit Plot 8.000 Q4 6.000 ln(q) 4.000 Upper 95.0%(quarters) Residual Plot time 2.000 ln(q) 0.000 8.000 6.000 Q4 Line Fit Plot ln(q) 0 4.000 0.5 1 1.5 Predicted ln(q) 2.000 0.000 0 Q18.000 0.5 6.000 1 1.5 ln(q) 4.000 2.000 Q2 0.000 12 16 20 24 28 32 0 0.5 1 Q4 time (quarters) ln(q) Q4 Line Fit Plot Predicted ln(q) ln(q) 1.5 Predicted ln(q) ln(q) 1 1.5 Predicted ln(q) SUMMARY OUTPUT Examining different notions of compounding Regression Statistics Formula Rate Assumption implicit in using this formula Multiple R 0.983954 coef time 4.74% quarterly growth rate, no compounding R Square 0.9682 exp(coef time)-1 4.85% quarterly growth rate, continuous compounding Adj. R^2 0.9634 4*coef time 18.95% APR, no compounding SEE 0.1148 (1+coef time)^4-1 20.34% APR, compounded quarterly Observations 32exp(coef time)^4-1 20.86% APR, continuously compounded ANOVA df SS MS F Significance F Regression 4 10.8244898 2.706122 205.279466 8.6607E-20 Residual 27 0.35593091 0.013183 Total 31 11.1804207 Standard Error t Stat Coefficients P-value Lower 95% Upper 95% Intercept 4.740 0.05536386 85.6 2.0246E-34 4.62623522 4.85342957 time (quarters) 0.0474 0.00221455 21.4 1.8396E-18 0.04282814 0.05191591 Q1 -0.914 0.05779096 -15.8 3.5514E-15 -1.03254265 -0.79538831 Q2 -0.601 0.05757841 -10.4 5.6672E-11 -0.71889202 -0.48260989 Q3 -0.363 0.05745051 -6.3 9.3742E-07 -0.48042039 -0.24466314 =exp(Qcoef) Understanding the seasonal dummies relative to peak =exp(Qcoef)-1 40.1% Q1 as a percent of peak (Q4) Q1 less than peak (Q4) by -59.9% 54.8% Q2 as a percent of peak (Q4) Q2 less than peak (Q4) by -45.2% 69.6% Q3 as a percent of peak (Q4) Q3 less than peak (Q4) by -30.4% RESIDUAL OUTPUT 2.10 dw ObservationPredicted ln(q) Residuals 0.746465 numerator 1 3.825867 0.11987427 2 4.186453 0.12396302 1.67E-05 3 4.472035 -0.0580565 0.033131 4 4.881948 0.10540765 0.026721 5 4.015355 0.01894592 0.007476 6 4.375942 -0.0044305 0.000546 7 4.661523 0.08880961 0.008694 8 5.071437 0.00876746 0.006407 9 4.204843 0.13516679 0.015977 10 4.56543 -0.2498182 0.148213 11 4.851011 0.03980023 0.083879 12 5.260925 -0.0135101 0.002842 13 4.394331 -0.1678973 0.023835 14 4.754918 0.03486095 0.041111 15 5.040499 -0.1418195 0.031216 16 5.450413 -0.0897959 0.002706 17 4.583819 -0.0027335 0.00758 18 4.944406 0.00671383 8.93E-05 19 5.229987 -0.1326394 0.019419 20 5.639901 -0.0501804 0.006799 21 4.773307 -0.1929149 0.020373 22 5.133894 -0.1066919 0.007434 23 5.419475 0.15548696 0.068738 24 5.829389 -0.0981225 0.064318 25 4.962795 0.12500485 0.049786 26 5.323382 0.08071815 0.001961 27 5.608963 -0.0580759 0.019264 28 6.018877 0.06289505 0.014634 29 5.152284 -0.0354461 0.009671 30 5.51287 0.11468457 0.022539 31 5.798451 0.10649453 6.71E-05 32 6.208365 0.07453874 0.001021 mpounding time (quarters) Residual Plot licit in using this formula Q1 Residual Plot no compounding 0.2 Residuals 0 continuous compounding 0.2 Residuals -0.2 0 10 Q2 Residual Plot 20 30 40 -0.4 0 -0.2 0 0.2 0.4 0.6 0.8 1 1.2 0.2 time (quarters) Q3 Residual Plot Residuals -0.4 0 -0.2 0 0.2 0.4 Q1 0.6 0.8 1 1.2 Residuals 0.15 0.1 time (quarters) Line Fit Plot Fit Q1 Line Plot -0.4 0.05 0 Q2 ln(q) 0 10.000 5.000 0.2 0.4 8.000 0.6 0.8 1 0.000 6.000 Q3 ln(q) 0 10 4.000 20 30 2.000 8.000 0.000 (quarters) ln(q) time 6.000 0 4.000 0.5 1 2.000 0.000 0 Q1 4.000 3.950 ln(q) 3.900 3.850 3.800 ) Line Fit Plot Fit Q1 Line Plot 1 Q2 Line Fit Plot 1.2 ln(q) 40 ln(q) Predicted ln(q) Q3 Line Fit Plot 1 1.5 Predicted ln(q) ln(q) 0.5 4.000 1 1.5 Predicted ln(q) 3.950 3.900 Q2 3.850 ln(q) 3.800 0 0.5 1 1.5 Predicted ln(q) Q3

DOCUMENT INFO

Shared By:

Categories:

Tags:

Stats:

views: | 3 |

posted: | 6/27/2012 |

language: | |

pages: | 42 |

OTHER DOCS BY LMwgG8

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.