Question

In: Statistics and Probability

Q2: Using MS Excel Analyze a regression of Air Quality on three covariates CO2, NOx, and...

Q2: Using MS Excel Analyze a regression of Air Quality on three covariates CO2, NOx, and Sox

Data Set is as following

Air Quality

CO2

NOx

SOx

197

61

22

17

191

33

16

27

224

65

18

18

183

55

18

32

236

50

26

23

200

60

24

26

226

59

16

25

164

54

22

24

100

83

10

17

285

50

21

29

207

73

22

21

336

61

26

35

299

42

36

21

192

44

25

4

264

54

20

29

244

24

17

27

227

53

22

42

216

41

18

30

263

57

29

24

265

87

23

25

How to use CO2, NOx, and SOx to predict the Air Quality? Please provide the formula.

What is the correlations between Air Quality and each of the three factors? What do they mean?

What is the Coefficient of Determination (R Square) of the regression result? What does it mean?

Solutions

Expert Solution

Solution:
Go to data >data analysis >Regression

select Y as Air quality

Rest all as X variables

You will get'

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.739169
R Square 0.546371
Adjusted R Square 0.461316
Standard Error 38.24893
Observations 20
ANOVA
df SS MS F Significance F
Regression 3 28193.26 9397.755 6.423705 0.004621
Residual 16 23407.69 1462.98
Total 19 51600.95
Coefficients Standard Error t Stat P-value Lower 95% Upper 95%
Intercept 40.78836 62.1144 0.656665 0.52073 -90.8883 172.465
CO2 -0.17858 0.591485 -0.30191 0.766606 -1.43247 1.075315
NOx 5.964901 1.605223 3.715932 0.001878 2.561979 9.367822
SOx 2.681185 1.136653 2.358843 0.031382 0.271589 5.090781

Regression equation is

Air quality=40.78836-0.17858*CO2+5.964901*NOx+2.681185*SOx

What is the correlations between Air Quality and each of the three factors? What do they mean?
Air Quality CO2 NOx SOx
Air Quality 1
CO2 -0.1844 1
NOx 0.614144 -0.12749 1
SOx 0.368603 -0.12929 -0.06312 1

there exists a negaive relationship between air quality and C02

there exists a positive relationship between air quality and NOx

there exists a positive relationship between air quality and SOx

Solutionc:

What is the Coefficient of Determination (R Square) of the regression result? What does it mean?

R sq=0.546371

=0.546371*100

=54.64% variation in Air quality is explained by model.


Related Solutions

i. Use MS Excel Data Analysis ToolPak to perform a multiple regression analysis using Quality as...
i. Use MS Excel Data Analysis ToolPak to perform a multiple regression analysis using Quality as the response variable and Helpfulness and Clarity as the explanatory variables. Write down the corresponding coefficient estimates and provide the regression output. j. Perform an F-test for the overall usefulness of the model in part i) using a 5% significance level. Make sure you follow all the steps for hypothesis testing indicated in the Instructions section and clearly state your conclusion. k. Test manually...
Use Excel to develop a regression model for the Consumer Food Database (using the “Excel Databases.xls”...
Use Excel to develop a regression model for the Consumer Food Database (using the “Excel Databases.xls” file) to predict Annual Food Spending by Annual Household Income. Assume a 5% level of significance. (file here: https://drive.google.com/file/d/13uDUXwoSRZHEUtjMUedu2yjR_4lrLepC/view?usp=sharing ) Must complete all the parts to this problem: PART 1: Perform a simple linear regression in Excel to predict Annual Food Spending by Annual Household Income and output the results. Include the Regression Statistics, ANOVA, and table of Coefficients for each model. PART 2:...
1) Use Excel to develop a regression model for the Consumer Food Database (using the “Excel...
1) Use Excel to develop a regression model for the Consumer Food Database (using the “Excel Databases.xls” file on Blackboard) to predict Annual Food Spending by Annual Household Income for those living in the Metro area only.    Suppose a household in the metro area has an annual income of $60,000. Predict how much they spend on food per year. Write your answer as a number (do not include the $ sign or comma) and round to 2 decimal places....
Use Excel to develop a regression model for the Hospital Database (using the “Excel Databases.xls” file...
Use Excel to develop a regression model for the Hospital Database (using the “Excel Databases.xls” file on Blackboard) to predict the number of Personnel by the number of Births. Perform a test of the overall model, what is the value of the test statistic? Write your answer as a number, round your answer to 2 decimal places. SUMMARY OUTPUT Regression Statistics Multiple R 0.697463374 R Square 0.486455158 Adjusted R Square 0.483861497 Standard Error 590.2581194 Observations 200 ANOVA df SS MS...
This week we have reviewed working in MS Excel. How do you see yourself using MS...
This week we have reviewed working in MS Excel. How do you see yourself using MS Excel, both in your personal and professional daily activities? Are you currently using Excel in your career? What are some of the features offered in Excel you find most helpful? How do you specifically use Excel and these features? Give two examples.
Shown below is a partial Excel output from a regression analysis. ANOVA df SS MS F...
Shown below is a partial Excel output from a regression analysis. ANOVA df SS MS F Regression   60 Residual Total 19 140 Coefficients Standard Error Intercept 10.00 2.00 x1 -2.00 1.50 x2 6.00 2.00 x3 -4.00 1.00 a. Use the above results and write the regression equation. [4 Marks] b. Compute the coefficient of determination and fully interpret its meaning. [4 Marks] c. Is the regression model significant? Perform the test at let α = 0.05. [4 Marks] d. At...
Shown below is a partial Excel output from a regression analysis. ANOVA df SS MS F...
Shown below is a partial Excel output from a regression analysis. ANOVA df SS MS F Regression   60 Residual Total 19 140 Coefficients Standard Error Intercept 10.00 2.00 x1 -2.00 1.50 x2 6.00 2.00 x3 -4.00 1.00 a. Use the above results and write the regression equation. [4 Marks] b. Compute the coefficient of determination and fully interpret its meaning. [4 Marks] c. Is the regression model significant? Perform the test at let α = 0.05. [4 Marks] d. At...
Time Value of Money Complete the following exercise using MS Excel. Using the Present Value and...
Time Value of Money Complete the following exercise using MS Excel. Using the Present Value and Future Value Equations 4. If you invested $200 at 5%, how much would it be worth in 30 years? 5. How many years does it take to double your money if it is invested at 6%? 6. If you invest $10,000 in a 20 year annuity paying 5%, what would be the annual payment made to you? 7. If you have a student loan...
Using the Excel file Weddings, apply the Regression tool using the wedding cost as the dependent...
Using the Excel file Weddings, apply the Regression tool using the wedding cost as the dependent variable and attendance as the independent variable. What is the regression model? Interpret all key regression results, hypothesis tests, and confidence intervals in the output. If a couple is planning a wedding for 175 guests, how much should they budget? Couple's Income Bride's age Payor Wedding cost Attendance Value Rating $130,000 22 Bride's Parents $60,700.00 300 3 $157,000 23 Bride's Parents $52,000.00 350 1...
Using a regression model in excel to understand the factors that contribute to customer satisfaction and...
Using a regression model in excel to understand the factors that contribute to customer satisfaction and spending. Refer to the data provided to identify what variables are significant to predicting overall satisfaction. Develop and interpret the prediction equation and the coefficient of determination. Based on the data, what areas should the business focus on to improve customer satisfaction? Dine In (1)/Take Out (2) Satisfaction with Service Satisfaction with Food Overall Satisfaction Driving Distance to Restaurant Total Bill 1 4 4...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT