LIAF105 – Quantitative Methods -Regresion and Correlation

1 Simple and Multiple Regression Analysis Example: Explore the relationships among Month, Adv .$ and Sales $: 1. Prepare a scatter plot of these data. The scatter plot s for Adv.$ versus Sales, and Month versus Sales are given in the Figures below with Excel@ Insert/ Scatter. a. Do the data appear to be stationary or nonstationary? The data appear to be nonstationary, it is not random, but with clear linear trend upward. b. Do the data appear to have a trend? Yes, the data have cl ear up trend, that is as the Adv.$ or Month increase, the Sales increase as well. c. If we want to fit a straight line to the data, how many lines could we possibly fit? We can fit infinite number of straight lines to the data. Eac h line is represented with a different set of b 0 (Y intercept), b 1 (Slope for Month) and b 2 (Slope for Adv.$) for this case. d. Compute the coefficient of correlation r between Month, Adv.$ and Sales, respectively, with =CORREL(Array1,Array2) and interpret the meanings. r(Adv versus Sales) = 0.901 and r(Month versus Sales) = 0.9722 indicate strong positive correl ation between the Adv and Sales, and Month and Sales, respectably. (Regression.xls/Reg0) 2. What is the general linear model to be used to model linear t rend? (Write out the model) 2g1;512g3036= 2g2010 2g2;6;+ 2g2010 2g2;692g1;502g2;692g3036+ 2g2010 2g2;702g1;502g2;702g3036+ 2g2013 2g3036 or 2g1;±52g1;532g1;6±2g1;572g1;71 2g3036= 2g2010 2g2;6;+ 2g2010 2g2;692g1;392g1;672g1;662g1;72ℎ 2g3036+ 2g2010 2g2;702g1;272g1;562g1;7± 2g3036+ 2g2013 2g3036 2 where Yi is the Sales in Month I with the amount of Adv.$ given in Month I, β0 is the Y intercept, or the Sales at Month =0 and Adv.$ = 0, β1 is the slope of the regression line drawn with Month as independent variable (X 1) and Sales as dependent variable (Y), it shows the ma rginal change (increase or decrease) in Sales when the variable Month changes one unit (increase or decrease) while keep no change for all of other variabl es, β2 is the slope of the regression line drawn with Adv$ as independent variable (X 2) and Sales as dependent variable (Y), it shows the marginal change (increase or decrease) in Sales (Y) when the amount of the variable Adv$ (X 2) incrementally changes (increases or decreases ONE unit) wh ile keep no change for all of other variables. 3. Use FIVE possible ways in Excel@ to find b 0, b 1 and b 2 in the linear regression model for Adv, Month and Sales data set, and predict Sales in Months 11 t o 13. a. Use Excel@ Solver to Minimize ESS or SSE in order to get optimal values of b 0, b1 and b2. 1) to assign arbitrary values for b 0, b1 and b 2 first, 2) compute Sales = b 0 + b 1 (Month) +b 2 (Adv), 3) compute SSE with =SUMXMY2(SalesRange,FcstRagne), 4) us e Excel@ Solver to minimize SSE to get the optimal values of b 0, b1 and b 2. (Regression.xls/Reg1) Use Excel@ Solver to get optimal values of b 0, b1 and b 2 that will minimize SSE Objective Function: SSE Changing Cells: I5:I7 3 b. Use Excel@ Data/Data Analysis/Regression to get the Summ ary Output for the data and print a copy of it, find values of b 0, b 1, and b 2 in the Summary Output. The values of b 0, b1, and b 2 are labeled in the Summary Output below. (Regression.xls/Reg1SOa) c. Use Excel@ =LINEST(ArrayY, ArrayXs) to get b 0, b 1 and b 2 simultaneously. Use Excel@ =LINEST(C2:C11,A2:B11) as in Regression.xls/Reg1. No te, Highlight the I15:K15, type =LINEST(C2:C11,A2:B11), then CTRL+SHIFT+ENTER. (Regression.xls/Reg1) d. =INTERCEPT(Y-RANGE,X-RANGE) for b 0 and =SLOPE(Y-RANGE,X-RANGE) for b1 when only single X variable is considered each time. (Regression.xls/Reg1) 4 e. Click any data point on the scatter plots for Month and Sa les, or Adv and Sales, select Add Trendline / Display equations & Display R-Squared value on the charts. The Y and Xs are renamed to Month,