Paper 3-4 pages on data driven decision making

Regression, Trend Analysis & Multiple Regression using Excel

Regression allows for…

· Determining if there is a statistically significant relationship between a target (dependent) variable and one or more predictor (independent) variables (e.g., Is on-time progress for course work related to GPA?)

· Determining if there is a trend over time

· Possibility for predicting the value of a target variable given the value of one or more predictor variables (e.g., Predict the number of months to graduate based on an Objective Assessment score for this course.)

Let’s look at a regression example involving one target variable and one predictor variable (i.e., Simple Linear Regression, also known as Least Squares Regression). Here we’ll determine whether there’s a significant trend from 1998 to 2014 in U.S. deaths from heroin (source: http://wonder.cdc.gov/mcd.html). Below is a scatter plot of the data (note that a scatter plot is usually the plot of choice for regression).

This type of regression analysis is a Trend Analysis given that our predictor variable is time.

There are 16 observations (1999 to 2014). With this type of analysis, there should generally be at least 15 observations.

Using Excel to Conduct a Simple Linear Regression

Below are the actual values, from the previous chart, shown in an Excel table.

Note: If you are running a trend analysis based on days, weeks, months, quarters, etc., you should code each time period as 1, 2, 3, etc. (e.g., month 1 = 1, month 2 = 2, …).

In this example we can use the actual years, given that they are consecutive numbers.

In Excel, select … Data Data Analysis (note: if you cannot find the “Data Analysis” option, then check out this tip sheet )

Then select “Regression” from the analyses options.

Fill in the Regression Analysis pop-up box as shown below.

Note: The “Labels” option is only appropriate if the first row of your selected Input contains labels for your columns (“Year” and “Heroin Deaths” in the case above).

Upon hitting “OK”, you should get the following output (you may need to expand some of the columns).

Key information has been highlighted including:

· R-square = .645; this is the correlation coefficient squared and is a measure of the “goodness of fit” between the two variables of time and heroin deaths [recall, R-square can vary between 0 (no fit) and 1 (perfect fit); a value of .645 indicates a reasonably high fit]

· While ANOVA is most commonly used to test for significant mean differences for 3 or more groups, in this case it is used to statistically test whether there is a significant relationship between the two variables.

· In this case, F=25.4 and p = .00018

· Because p < .05, we reject the null hypothesis (of no relationship) and instead conclude that there is a significant relationship (trend) between time and heroin deaths

· As can be seen from the previous chart, heroin deaths progressively increase from 1999 to 2014

(note: for the “Significance F or P-value”, Excel has a specific notation for very small numbers/fractions. For example, suppose that a p-value is reported in Excel as 1.73E-8. This is Excel’s notation for “move the decimal place to the left 8 places”, or 0.0000000173. In other words, this is much smaller than 0.05, so we could conclude in this case that p<.05 and that there is a significant relationship).

· Finally, the regression coefficients are given for the straight regression line.

· Recall, the formula for a straight line … Y = mX + b

· Here, m and b are coefficients from the prior table

· So, our regression line formula is

· Y = 435.4 X – 870,120.1

· Because our regression is statistically significant, we can use this formula to predict future heroin deaths

· For example, the predicted number of Heroin deaths for 2015 is…

· Y = 435.4 (2015) – 870,120.1 = 7,231

· Notice that the 2015 predicted value (7,231) is less than the actual values for 2014 (10,574) and 2013 (8,257)

· This is because a straight line through the data points isn’t the best fit of the trend. As we’ll see, a curvi-linear trend captures the data pattern more accurately.

Here is an appropriate write-up of these results (notice the inclusion and interpretation of R-square, F-value, p-value, and regression formula):

There is a relatively strong goodness of fit between time and increasing heroin deaths as indicated by an R-square of 0.645. This relationship is statistically significant (F=25.4, p<.05). The regression formula is Y = 435.4 X – 870,120.1, and indicates that the predicated number of heroin deaths for the next time period (i.e., 2015) is 7,231.

To add a trend (regression) line to the chart select (left click on) the data points Add Trendline

Then select “Linear” trend. You can also check the boxes to include the Regression Equation and R-Square value.

For the math inclined, a better predictive trend can be derived using a polynomial function as shown below. Notice that the R-square approaches 1.0, indicating that time and heroin deaths are very highly related in this curvi-linear function.

Multiple Regression

Multiple regression is used when there are multiple predictors and a single target (dependent) variable.

For example, can the number of annual heroin deaths be predicted from the percent of U.S. adults who use Marijuana, Cocaine, Hallucinogens, and/or Psychotherapeutics?

The screen shot on the next page shows such data from 2002-2013 (source: http://www.samhsa.gov/data/sites/default/files/NSDUHresultsPDFWHTML2013/Web/NSDUHresults2013.htm ).

Within Excel Data Data Analysis Regression

In this case our Y (target) variable is annual heroin deaths.

Notice in the following screen shot that we specify the entire range of the X (predictor) variables (columns C through F).

In this analysis, we’re going to exclude “Year” as a variable per se.

After clicking OK, here are the results.

· The overall R-square is 0.796, indicating a good fit between the number of heroin deaths and one or more of the predictor variables.

· The ANOVA shows F=6.84, p=.014. Because p < .05 we can conclude that overall there is a significant relationship between the number of heroin deaths and one or more of the predictor variables.

· The individual t-tests reveal that only Marijuana usage is significantly related to number of heroin deaths (p = .015; for all other predictors p > .05).

An appropriate write-up of these results would be:

The R-square of 0.80 indicates a relatively strong goodness of fit between annual heroin deaths and the predicator variables (incidence of use of marijuana, cocaine, hallucinogens, and psychotherapeutics). Overall, there is a significant relationship (F=6.84, p<.05). However, only marijuana use is significantly related to heroin deaths (p<.05). The regression formula for predicating annual heroin deaths is

Y = 3,226.6 X1 + 871.4 X2 – 3,376.5 X3 -1,093.1 X4 -13,854.5. Where X1, X2, X3, and X4 are, respectively, incidence of marijuana, cocaine, hallucinogens and psychotherapeutics use.

Below is a scatterplot of Number of Heroin Deaths and Marijuana Usage across 12 years. Included in the scatterplot are: the simple regression line, as well as the R-Square and the Regression Equation for just these 2 variables.

So, can we conclude that a rise in marijuana use is a cause of the increase in deaths by heroin? While these are significantly related, we cannot conclude that one causes the other (You may have heard the expression “correlation does not imply causation”; the same applies to regression).

For example, below are findings for 2002-2013 of Harvard Tuition rates (source: http://kwharbaugh.blogspot.com/2005/02/educational-costs.html) and Heroin deaths. Despite a statistically significant relationship (p < .001), we wouldn’t conclude that the rise in Harvard tuition rates is a cause of the increase in heroin deaths.

1