Guide to Using Excel For Basic Statistical Applications
Guide to Using Excel For Basic Statistical Applications To Accompany Business Statistics: A Decision Making Approach, 7 th Ed. Chapter 14: Introduction to Linear Regression and Correlation Analysis By Groebner, Shannon, Fry, & Smith Prentice-Hall Publishing Company Copyright, 2008
Chapter 14 Excel Examples n n Correlation Coefficient Midwest Distribution Regression Analysis Fitzpatrick and Associates Regression Analysis Vantage Electronics Regression Analysis for Description Car Mileage
Chapter 14 Excel Examples n n Regression Analysis for Prediction Freedom Hospital Residual Analysis Freedom Hospital
Correlation Analysis Midwest Distribution Issue: The company wants to analyze the relationship between salary and years with the company. Objective: Use Excel to help test whether a significant linear relationship exists between salary and years with the company. Data file is Midwest. xls
Correlation Analysis – Midwest Distribution. Open the file Midwest. xls To construct the scatter diagram see the Personal Computers tutorial in Chapter 2.
Correlation Analysis – Midwest Distribution. • Select the Data tab • Select Data Analysis • Select Correlation • OK
Correlation Analysis – Midwest Distribution. • Input Range = A 1: B 13 • Grouped by Columns • Labels in First Row • New Worksheet Ply: Correlation
Correlation Analysis – Midwest Distribution. The correlation between Sales and Years with Midwest is 0. 8325
Regression Analysis Midwest Distribution Issue: The marketing manager wants to determine the relationship between sales and years with the company. Objective: Use Excel to test whether the apparent linear relationship is statistically significant. Data file is Midwest. xls
Regression Analysis – Midwest Distribution. Open the file Midwest. xls
Regression Analysis – Midwest Distribution. • Select the Data tab • Select Data Analysis • Select Regression • OK
Regression Analysis – Midwest Distribution. nput Y Range = A 1: A 13 nput X Range = B 1: B 13 elect Labels ew Worksheet Ply: Correlation K
Regression Analysis – Midwest Distribution. The output shows: • the correlation coefficient • the coefficients for the regression equation. • for each additional year, the average increase in company sales is 49. 91008 = $4, 991
Regression Analysis Fitzpatrick & Associates Issue: The Fitzpatrick analysts want to model the relationship between profits and number of employees. Objective: Use Excel to test whether the apparent linear relationship is statistically significant. Data file is Fortune 50. xls
Regression Analysis – Fitzpatrick & Associates Open the file Fortune 50. xls
Regression Analysis – Fitzpatrick & Associates • • Because you want the Employees to plot on the horizontal axis, it needs to be to the left of Profit. To accomplish this, copy Column D to Column K. Select the Insert Tab Select Scatter chart Select the Scatter with only Markers
Regression Analysis – Fitzpatrick & Associates • • • Add titles Delete Legend Move chart to new sheet titled Scatter plot
Regression Analysis – Fitzpatrick & Associates To find Regression: • Return to the Data Sheet • Select the Data tab • Select Regression • OK
Regression Analysis – Fitzpatrick & Associates • Y Range = K 3: K 53 • X Range = J 3: J 53 • New Worksheet Ply • OK
Regression Analysis – Fitzpatrick & Associates Output Shows R square and Coefficients
Regression Analysis for Vantage Electronics Issue: Analysts want to compare the effectiveness of the Vantage Sensor - Vanguard against its competitor, Scorpion. Objective: Use Excel to test whether the apparent linear relationship is statistically significant. Data file is Vanguard. xls
Regression Analysis for Vantage Electronics Using the procedures from this chapter Create a Scatter Plot Note: Copy the Scorpion data to the column to the right of the Vantage data. Add titles, delete legend, and move to a new sheet.
Regression Analysis for Vantage Electronics The resulting model will give you the regression coefficient and the slope
Regression Analysis for Description- Car Mileage Issue: Analysts want to determine the relationship between EPA highway mileage and automobile horsepower. Objective: Use Excel to test whether the apparent linear relationship is statistically significant. Data file is Automobiles. xls
Regression Analysis – Car Mileage • Open the file Automobiles. xls • Select the Data tab • Select Data Analysis • Select Regression • OK
Regression Analysis – Car Mileage • Input Y Range: B 1: B 31 • Input X Range: F 1: F 31 • Select Labels • New Worksheet Ply: Auto • OK
Regression Analysis – Car Mileage The output shows • the coefficient of determination • the coefficients for the regression equation.
Regression Analysis for Prediction - Freedom Hospital Issue: The administrator has been requested by the hospital’s board of directors to develop a model that can be used to predict the total charges for a geriatric patient. Objective: Use Excel to develop a statistically significant prediction model. Data file is Patients. xls
Regression Analysis for Prediction – Freedom Hospital • Open the file Patients. xls • Select the Data tab • Select Data Analysis • Select Regression • OK
Regression Analysis for Prediction – Freedom Hospital • Input Y Range: G 139 • Input X Range: B 139 • Select Labels • New Worksheet Ply: Patients • OK
Regression Analysis for Prediction – Freedom Hospital The Excel output shows the coefficient of determination and the coefficients of the linear regression model.
Residual Analysis Freedom Hospital Issue: The administrators want to determine whether the prediction model developed is a valid use of regression analysis or whether it violates some of the necessary assumptions. Objective: Use Excel to analyze the residuals from the model. Data file is Patients. xls
Residual Analysis – Freedom Hospital In the Patients Data sheet: • Select the Data tab • Select Data Analysis • Select Regression
Residual Analysis – Freedom Hospital • Input Y Range: G 139 • Input X Range: B 139 • Select Labels • Select Residual Plots • OK
Residual Analysis – Freedom Hospital At the bottom of the regression output (note the row number) the residual for each observation is given.
Residual Analysis – Freedom Hospital The regression output will also plot the residuals.
- Slides: 36