Unit V Scholarly Activity

Unit V Data Analysis

SUCCESS CENTER 800.977.8449 EXT. 6538 | [email protected]

VIDEO RECORDING: https://youtu.be/J-MBDJB-72g PROBLEM: Use the Sun Coast Remediation data set to conduct a correlation analysis, simple regression analysis, and multiple regression analysis using the correlation tab, simple regression tab, and multiple regression tab respectively. Cut and paste the statistical output tables from Excel, directly into the final project document. For the regression hypotheses, display and discuss the predictive regression equations. SOLUTION: This assignment is asking you to add to those tables, the following information: 1. Restate the hypotheses (both the null and alternative) 2. Enter data tables (correlation and regression data) 3. Interpret and explain the correlation analysis to include: r, r2, alpha level, and p-value 4. Reject the null hypothesis in favor of the alternative hypothesis, or fail to reject the null hypothesis. Another note: The Unit V template shows the correlation data table that includes the p-value. Unfortunately, the Excel Toolpak does not include the p-value calculation in the correlation analysis. You have to use the regression data to see the r2, alpha level, and p-value results. Correlation: Hypothesis Testing 1. Restate the hypotheses. This information comes from your Unit II assignment. a. Ho1: There is no statistically significant relationship between _______________________________. b. Ha1: There is a statistically significant relationship between _________________________________. For demonstration purposes, only a small portion of the data is used. You must use all of the data for your assignment. (Please continue to next page)

Use Excel to calculate correlation and regression statistics to determine the Pearson correlation

coefficient r and to determine the p-value. These values will help determine whether to reject or fail to reject the null hypothesis H0.

If the p-value from the data is less than 0.05, then you will reject the null hypothesis.

a.) To calculate correlation in Excel: Go to the Data Analysis tool and select CORRELATION. Press OK.

Fill in accordingly, make sure to highlight B2, go over to C2, and then scroll down to last row.

Do not forget to select a place to show the output range.

The correlation coefficient will just say Column 1, Column 2, etc. However, click on each cell to

label them accordingly (optional):

b.) Regression data in Excel. Go to Data Analysis tool. Scroll down and select REGRESSION. Press OK.

Make sure to uncheck residuals if it is checked. Highlight a region where you want the output to

be located. The x input is the microns column. The y input is the mean sick days column.

Press OK.

The significant data has been bold printed and yellow highlighted:

The −0.93 (correlation table) refers to a negative, but strong correlation. As the number of microns get

smaller, then the sick days increase. It is strong, because it is approaching −1. In the Course Project

Guidance, it discusses the significance of particulate matter on page 2. If you recall, the smaller the PM,

then it is easier to invade the body through inhalation into the lungs. This is why we see more sick days

with respect to smaller PM values in microns (the size of the PM).

Notice the P-values (highlighted in yellow). As an example, 8.4E-09 actually means 8.4 × 10−9 or

0.0000000084, which is definitely less than a p-value of 0.05. In my example, I would reject the null hypothesis. 3. Interpret and explain the correlation analysis to include: r, r2, alpha level, and p-value

The Pearson correlation coefficient of r ≈ −0.93, which indicates a strong negative correlation: the smaller the micron size of particulate matter, the greater the mean sick days occur. This equates to an r2 of approximately 0.868, explaining 86.8% of the variance between the variables. Using an alpha of 0.05, the results indicate a p-value of 8.4 × 10−9 which is considerably less than the alpha of 0.05. Therefore, the null hypothesis is rejected, and the alternative hypothesis is accepted, that there is a statistically significant relationship between mean sick days and particulate matter size in microns.

Simple Regression: Hypothesis Testing 1. Restate the hypotheses.

a. Ho1: There is no statistically significant relationship between _______________________________. b. Ha1: There is a statistically significant relationship between _________________________________.

2. For correlation and regression statistics, use lost time hours as the input y-range, and safety

training expenditure as the input x-range.

3. Data Tables: (This data only uses rows 2 through 150)

4 . Interpret and explain multiple R, R-square, alpha level, Anova F (significance) value, statistical

significance of the x variable coefficient and the intercept coefficient with respect to the regression model as an equation, and include an explanation.

Correlation r The correlation, while negative, is very strong. In other words, as more money is spent on safety training, the amount of lost time hours decreases. Multiple R The multiple R of approximately 95.5%, indicates a very strong correlation between the regression model and the dependent variable (lost time hours). ANOVA f (significance) value and alpha level Using the alpha level of 0.05, the ANOVA f (significance) value of 1.324 ×10−79 is considerably less than the alpha level 0.05. Therefore, the null hypothesis is rejected, in favor of the alternative hypothesis, which states ________________________. R-square The coefficient of determination, also referred to as R-square, explains the amount of variation in the dependent variable (lost time hours) to the regression model. In this example, approximately 91.2% of the lost time hours can be explained by the safety training expenditures. [Note: the regression model will be explained next].

Regression model with x and intercept variables

Use the coefficients for the intercept and the x variable (found in the ANOVA data table).

We use the slope intercept formula y = mx + b for the regression model and use those coefficients to describe the regression line. The intercept coefficient is 241.402586, but we will round it to be 241.4. This is the “b” value. The slope “m” is represented by the safety training coefficient of −0.1134 (rounded). Therefore, we plug these values into the formula to get our regression model: y = −0.1134x + 241.4 If we plug in a dollar amount as “x” on safety training, we should be able to tell how many approximate lost time hours that would represent.

REGRESSION MODEL

Multiple Regression: Hypothesis Testing 1. Restate the hypotheses. a. Ho1: There is no statistically significant relationship between _______________________________.

b. Ha1: There is a statistically significant relationship between _________________________________.

For correlation and regression statistics, you must use all the data, as follows:

a.) Go to Data Analysis and select Regression, then press OK.

b.) Enter data as shown, except use all the data. This example only contains 74 rows of data. You will also highlight the titles, so be sure to click on “Labels.” Press OK for results.

c.) Instead of using correlation, you will discuss the

multiple regression R-value, which will tell you how

strong or weak, the relationships of the frequency,

angles, chord length, velocity and displacement are

to decibels. The closer the R-value is to +1 or −1,

then the stronger these variables are related

(correlated).

2. Data tables:

3. Interpret and explain multiple R, R-square, alpha level, Anova F (significance) value, statistical

significance of the x variable coefficient and the intercept coefficient with respect to the regression model as an equation, and include an explanation.

Multiple R The multiple R of approximately 91%, indicates a very strong correlation between the regression model and the dependent variable (decibels). ANOVA f (significance) value and alpha level Using the alpha level of 0.05, the ANOVA f (significance) value of 1.31 ×10−24 is considerably less than the alpha level 0.05. Therefore, the null hypothesis is rejected, in favor of the alternative hypothesis, which states ________________________. R-square The coefficient of determination, also referred to as R-square, explains the amount of variation in the dependent variable (decibels) to the regression model. In this example, approximately 83% of the decibels can be explained by the combination of frequency, angle in degrees, chord length, velocity (meters per second), and displacement. Regression Model Use the coefficients for the intercept and the x variable (found in the ANOVA data table).

We use the slope intercept formula y = mx + b for the regression model and use those coefficients to describe the regression line. The intercept coefficient is 124.8659075, but we will round it to be 124.87. This is the “b” value. The slope “m” is represented by all of the variables, and will look quite complex. We will round each coefficient by 4 decimal places. The variable x1 will represent frequency. −0.0016 x1 The variable x1 will represent frequency. −0.0615 x2 The variable x2 will represent angle in degrees. −6.7424 x3 The variable x3 will represent chord length. 0.1167 x4 The variable x4 will represent velocity. −209.3837 x5 The variable x2 will represent displacement. Therefore, we plug these values into the formula to get our regression model: y = −0.0016 x1 − 0.0615 x2 − 6.7424 x3 + 0.1167 x4 − 209.3837 x5 + 124.87 If we plug in amounts for each variable and do the calculation, we can predict (approximately) the amount of decibel levels of the work environment before placing employees on site.