Regression Problems

Regression Problems Regression Analysis The primary method for associative forecasting is Regression Analysis. The relationship between two or more variables. The relationship could be linear or non-linear. Simple Linear Regression : Linear Regression Between Two Variables We could use available data to investigate such a relationship. We use this relationship to forecast future.  The relationship between a dependent variable and one or more independent variables. The independent variables are also referred to as predictor variables.  We may use regression to investigate the relationship between demand (y) and time (x), or advertisement (x) as independent variable and sales (y) as the dependent variable.  We can associate terms such as Association Analysis, Causal Analysis, Time Sires Analysis, Regression for Prediction, and Regression for Association to Regression Analysis.      Regression  An linear trend is when the amount of increase in Y (dependent Variable) for each     unit increase in X (independent Variable) is constant. For example for each 1 unit crease in X there is 0.2 units increase in Y. An exponential trend is when the percentage of increase in Y foe each unit increase in X is constant. For example for each 1 unit increase in X there is 2% increase in Y. A causal model provides evidence of an association between an independent variable and the variable to be forecast. Causal models relate a time series or other dependent variables to independent variables that are believed to explain or cause its behavior. They do not provide evidence of a causal relationship between an independent variable and the variable to be forecast. For causal modeling, scatter charts are used to detect linear or nonlinear relationships between the independent and dependent variables. Patterns Linear Linear Linear Linear Non-Leaner Non-Leaner Non-Leaner Non-Leaner Two Variables More Than Two Variables Independent Variable: Time Independent Variable: Not Time Tmore Than wo Variables More Than Two Variables Independent Variable: Time Independent Variable: Not Time Mean Squared Error- Measure of Forecast Accuracy 2500 y=b0+b1x 2000 1500 1000 1 b0 500 0 b1 1 3 5 7 9 11 Linear Relationship Linear relationship between two variables is stated as y = b0 + b1 x y : Dependent variable b1 > 0 x : Independent variable b0 : Intercept with y axis b1 : Slope of the line b1 < 0 b1 = 0 Correlation coefficient is a measure of the strength of a linear association between two variables. It has a value between -1 and +1 Rxy = +1 : two variables are perfectly related through a line with positive slope. Rxy = -1 : two variables are perfectly related through a line with negative slope. Rxy = 0 : two variables are not linearly related. Coefficient of Determination and Correlation Coefficient are both measures of associations between variables. Correlation Coefficient for linear relationship between two variables. Coefficient of Determination for linear and nonlinear relationships between two and more variables. 7 LA/LB Ports – Twenty-Foot Equivalent Unit (TEUs) Year 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 TEUs 6464 7475 8237 9480 11642 11705 11837 13101 14194 15760 15667 14338 11816 14095 14001 14123 14599 15160 15352 15631 16887 17549 17090 Excel Regression Multiple R R Square Adjusted R Square Standard Error Observations 0.92 Correlation Coefficient (-1 and +1) we want it close to +1 0.84 Coefficient of Determination: Large (0 to 1) we want close to 1 0.82 To know if the line had + or - slope we look at X Varaible 1 or Multiple R 4.07 Standard Error is the standard deviation of our forecast 12 Something similar to 1.25MAD ANOVA df Regression Residual Total Intercept X Variable 1 SS 1 10 11 873.0 165.9 1038.9 MS 873.0 16.6 F Significance F 52.61 2.74837E-05 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% 5.06 2.686944916 1.883208505 0.089052275 -0.926808842 11.04696388 1.59 0.219632305 7.253137252 2.74837E-05 1.103651983 2.082394529 We want is less that .05 Y=5.06+1.59t Regression Output Correlation Coefficient +↑. Close to + 1 SUMMARY OUTPUT Coefficient of Determination ↑ Close to 1 Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations 0.98 0.95 0.95 22.21 10 Standard Deviation of Forecast ↓ If the first period is 1, next period is 10+1 = 11 ANOVA Regression Residual Total df 1 8 9 SS 77771 3945 81716 MS 77771 493 F 158 Significance F 1.51524E-06 Intercept X Variable 1 Coefficients 94.13 30.70 Standard Error 15.17 2.44 t Stat 6.21 12.56 P-value 0.000258 0.000002 Lower 95% 59.15 25.07 Upper 95% 129.12 36.34 P-value ↓ less than 0.05 10 Regression Output Provide the equation of the Regression line. Ft = 94.13 +30.71t What is your forecast for the next period? Next period? F11 = 94.13 +30.71(11) = 431.7 Mean Forecast = 431.7, Standard Deviation of Forecast = 22.21 SUMMARY OUTPUT Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations 0.98 0.95 0.95 22.21 10 ANOVA Regression Residual Total df 1 8 9 SS 77771 3945 81716 MS 77771 493 F 158 Significance F 1.51524E-06 Intercept X Variable 1 Coefficients 94.13 30.70 Standard Error 15.17 2.44 t Stat 6.21 12.56 P-value 0.000258 0.000002 Lower 95% 59.15 25.07 Upper 95% 129.12 36.34 11 Regression Problem 2 Given the following regression report for the relationship between demand and time. (Demand is the dependent variable and Time is the independent variable) SUMMARY OUTPUT What is your forecast for the next period? 52+10(20+1) = 262 What is the standard deviation of your forecast for the next period? 15.05 Regression Statistics Multiple R 0.97 R Square 0.95 Adjusted R Square 0.95 Standard Error 15.06 Observations 20 ANOVA Regression Residual Total df 1 18 19 Intercept X Variable 1 Coefficients 52 10 SS 75988 4083 80071 Standard Error 7.00 0.58 MS 75988 227 t Stat 7.44 18.30 F 335 P-value 6.80516E-07 4.42292E-13 Significance F 4.42292E-13 Lower 95% 37.34 9.46 Upper 95% 66.73 11.92 Is there a strong relationship between the dependent and the independent variables? Yes R-Square (Coefficient of Determination) id 0.95, Multiple R (Correlation Coefficient) is 0.97, p-value is very small Is the relationship positive or negative? Positive. We can check it by Multiple R being + or b1 being + 12 Short Questions 1. If the coefficient of determination between interest rate (x) and residential real estate prices (y) is 0.85, this means that: A) 85% of the y values are positive B) 85% of the variation in y can be explained by the variation in x C) 85% of the x values are equal D) 85% of the variation in x can be explained by the variation in y E) none of the above 2. Which value of the coefficient of correlation (r) indicates a stronger correlation than 0.7? A) 0.6 B) -0.9 C) 0.4 D) -0.5 E) none of the above 13 Short Questions 3. In a good regression we expect A) P-value to be high and R-square to be high B) P-value to be low and R-square to be low C) P-value to be low and R-square to be high D) P-value to be high and R-square to be low E) none of the answers 4. Discuss the relationship between MAD in moving average and exponential smoothing and Standard Error in regression. Standard Error in regression is an estimate of the Standard Deviation of the Forecast. Standard Deviation of the Forecast = Standard Error MAD in moving average and exponential smoothing is an estimate of the Standard Deviation of the Forecast. Standard Deviation of the Forecast = 1.25MAD 14 Regression Provide forecasts and standard deviation of forecast for September using linear Regression. Also Compute R-Squared (Coefficient of determination) and Correlation Coefficient. t 1 2 3 4 5 6 7 8 9 10 11 At 19 18 15 20 18 22 20 17.4 17.9 18.4 18.9 19.4 19.9 20.4 20.9 21.4 21.9 22.4 Intercept Slope R-Squared CC StdDevOfForc F8 16.86 0.5 0.24 0.49 2.1 20.86 =INTERCEPT($B$3:$B$9,$A$3:$A$9) =SLOPE($B$3:$B$9,$A$3:$A$9) =RSQ($B$3:$B$9,$A$3:$A$9) =CORREL($B$3:$B$9,$A$3:$A$9) =STEYX($B$3:$B$9,$A$3:$A$9) rxy = ( sign of b1 ) r 2 15 Seasonality  Seasonality is when a time series show a recurring pattern over day, a week, a month, a year or less.  A time series with a seasonal pattern can be modeled using multi-variable regression by treating the season as a 0/1dummy variable. For example we can have X1, X2, and X3 for Spring, Summer, and Fall. Then X2=1 means this specific piece of data is for Summer. When all of them are 0, then we are in Winter. The number of variables are always one less than the number of seasons. Seasonality Enhanced Regression Using 0/1 Variables Season-1 Season-2 Season-3 Season-4 Year-1 920 1,060 1,120 990 Year-2 1,110 1,160 1,120 1,080 Year-3 1,240 1,300 1,250 1,200 Chart Title 1400 1300 1200 t At 1 2 3 4 5 6 7 8 9 10 11 12 920 1060 1120 990 1110 1160 1120 1080 1240 1300 1250 1200 t 1 2 3 4 5 6 7 8 9 10 11 12 Seas2 Seas3 Seas4 Ft 0 0 0 977.50 1 0 0 1060.83 0 1 0 1050.83 0 0 1 977.50 0 0 0 1090.00 1 0 0 1173.33 0 1 0 1163.33 0 0 1 1090.00 0 0 0 1202.50 1 0 0 1285.83 0 1 0 1275.83 0 0 1 1202.50 SUMMARY OUTPUT Regression Statistics Multiple R 0.95 R Square 0.90 Adjusted R Square 0.85 Standard Error 43.23 Observations 12 1100 1000 900 800 0 2 4 6 8 10 12 ANOVA Regression Residual Total Intercept t Seas2 Seas3 Seas4 df SS MS 4 ###### 29952.08 7 13083.33 1869.05 11 ###### gnificance F F 16.03 0.00 Coefficientsandard Err t Stat P-value Lower 95% Upper 95%ower 95.0% Upper 95.0% 949.38 31.43 30.20 0.00 875.05 1023.70 875.05 1023.70 28.13 3.82 7.36 0.00 19.09 37.16 19.09 37.16 55.21 35.51 1.55 0.16 -28.75 139.17 -28.75 139.17 17.08 36.12 0.47 0.65 -68.32 102.49 -68.32 102.49 -84.38 37.11 -2.27 0.06 -172.14 3.39 -172.14 3.39 14 LA/LB Ports Total TEU Data (2015-2019) January February 2015 959 916 2016 1241 1275 2017 1409 1124 2018 1467 1387 2019 1466 1322 959 916 1 2 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 March April May June July August 1422 1278 1330 1305 1389 1490 1078 1135 1411 1279 1325 1440 1294 1273 1445 1390 1517 1540 1153 1324 1456 1475 1522 1506 1131 1334 1343 1400 1456 1491 1422 1278 1330 1305 1389 1490 3 4 5 6 7 8 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 September October 1386 1325 1295 1396 1465 1418 1503 1658 1508 1641 1386 1325 9 10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 November December 1330 1223 1412 1346 1537 1476 1454 1645 1432 1568 1330 1223 1241 1275 11 12 13 14 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 15 0 1 0 0 0 0 0 0 0 16 17 18 19 20 21 22 23 24 25 26 27 28 29 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 61 0 0 1 0 0 0 0 0 0 0 0 0 0 071 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1078 1800 1700 1600 1500 1400 1300 1200 1100 1000 900 800 1 11 1135 1411 1279 1325 1440 1295 1396 1412 1346 1409 1124 211294 1273 1445 1390 Chart Title 31 30 41 0 51 DNCBE-CSUN Incoming Students Given 16 years data on the college of Business incoming students. Using 6-period moving average. A) Commune Average and Standard Deviation of the data. B) Computed Standard Error and 95% Confidence Interval for the average number of incoming students. C) Compute MAD, MSE, MARD (MAPE). E) Compute your forecast for the next period. F) Compute the standard deviation of your forecast for the next period using MAD, MSE, MARD (MAPE). G) What is the average Age of data H) Draw the Tracking Signal Curve. I) Using the Standard Deviation obtained by MSE, compute the probability of the next period demand to be less that 90% of your forecast. DNCBE-CSUN Incoming Students Year Incoming F(t,6) E=A-F |E| 2001 1883 2002 1796 2003 1786 2004 1643 2005 2201 2006 2122 2007 2169 1905 264 2008 2204 1953 251 2009 1961 2021 -60 2010 1857 2050 -193 2011 1986 2086 -100 2012 1678 2050 -372 2013 1997 1976 21 2014 2166 1947 219 2015 2241 1941 300 2016 1856 1988 -132 Forecast 1987 StdDev Forecast CV Row 8 Formulas Row 17 Formulas Row 18 Formulas Row 19 Formulas =AVERA GE(B2:B7 ) =B8-C8 =AVERA GE(B11:B 16) =B17-C17 =AVERA GE(B12:B 17) E2 264 69608 251 63085 60 3580 193 37249 100 9933 372 138260 21 448 219 47888 300 90100 132 17292 =ABS(D8: D17) =D8^2 |E|/A 0.122 0.114 0.031 0.104 0.050 0.222 0.011 0.101 0.134 0.071 MAD MSE MARD 264 258 192 192 174 207 180 185 198 191 69608 66346 45424 43380 36691 53619 46023 46256 51128 47744 0.122 0.118 0.089 0.093 0.084 0.107 0.093 0.094 0.099 0.096 239 0.120 219 0.110 0.120 =AVERAG =E8/B8 E(E$8:E8) =AVERAG E(E$8:E17 =ABS(D1 7:D26) =D17^2 =E17/B17 ) =AVERAG E(F$8:F8) =AVERAG E(F$8:F17 ) =AVERAG E(G$8:G8 ) =AVERAG E(G$8:G1 7) TS Descriptive Statistics for Input Data Average Data 1972 =AVERAGE($B$2:$B$17) StdDev Data 196 =STDEV.S($B$2:$B$17) Count 16 =COUNT($B$2:$B$17) StdErr Data 49 =N3/SQRT(N4) 0.95CI 96 =CONFIDENCE.NORM(1-0.95,N3,N4) Age of Data 3.5 =(1+6)/2 P(D<=0.9F) 0.181539 UCL-TS 1 4 9 4 1 2 2.375471 1.365748 0.936599 -1.01345 -1.04496 0.16584 1.673223 1.043084 LCL-TS 1 9 -4 -4 Zero 1 9 0 0 Tracking Signal 5 =SUM(D$ 8:D8)/H8 =SUM(D$ 8:D17)/H 17 4 3 2 1 0 -1 -2 -3 =1.25*H1 =SQRT(I1 7 7) =1.25*J17 -4 -5 Ft+1 = (1/2) Ft+ (1/2) (At) Ft+1 = (Ft+At)/2 F3 = (F2+A2)/2 F3 = (300+500)/2= 400 F = (F +A )/2 = Comparing Several Forecasting Techniques When comparing several methods, we need to use the same time horizon for all methods. We need to have actual as well as forecasts for all methods for all periods of MAD computations Here we have Actual for periods 1 to 7; that is 7 periods. Regression can provide us with forecast for periods 1 to ∞ Five period moving average can only provide forecast for periods 6 and 7; that is 2 periods Therefore, to compare all these methods, we can compute MAD only over 2 periods. But two periods is not enough. Naïve Method or Exponential Smoothing ? Naïve method forecasts for periods 2 to 7; That is 6 periods Exponential Smoothing for periods 2 to 7; That is 6 periods We can compare NM and ES over 6 periods.