To Accompany Business Statistics A Decision Making Approach 8th Ed Chapter 15 Multiple Regression and Model Building By Groebner Shannon Fry amp Smith PrenticeHall Publishing Company ID: 472260
Download Presentation The PPT/PDF document "Guide to Using Excel 2007 For Basic Stat..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Guide to Using Excel 2007 For Basic Statistical Applications
To AccompanyBusiness Statistics: A Decision Making Approach, 8th Ed.Chapter 15:Multiple Regression and Model BuildingByGroebner, Shannon, Fry, & SmithPrentice-Hall Publishing CompanyCopyright, 2011Slide2
Chapter 15 Excel ExamplesMultiple Regression
First City Real EstateMultiple Regression –Dummy Variable First City Real EstateCurvilinear Regression Ashley Investment ServicesInteraction Effects Ashley Investment ServicesMore ExamplesSlide3
Chapter 15 Excel Examples
Residual Analysis First City Real EstateSlide4
Multiple Regression – First City Real Estate
Issue: First City management wishes to build a model that can be used to predict sales prices for residential property. Objective: Use Excel 2007 to build a multiple regression model relating sales price to a set of measurable variables. Data file is FirstCity.xlsSlide5
Multiple Regression – First City Real Estate
Open the file FirstCity.xls, Sheet Homes-Sample 1Select the Data TabSelect Data AnalysisSelect CorrelationOKSlide6
Multiple Regression – First City Real Estate
Input Range: A1:G320Grouped by: ColumnsNew Worksheet Ply: corr-1OKSlide7
Multiple Regression – First City Real Estate
This cell shows the correlation, 0.7477, between Price and Square FeetSlide8
Multiple Regression – First City Real Estate
Return to Homes-Sample 1Select the Data tabSelect Data AnalysisSelect RegressionOKSlide9
Multiple Regression – First City Real Estate
Input Y Range: A1:A320Input X Range: B1:F320Select labelsNew Worksheet Ply: regress-1OKSlide10
Multiple Regression – First City Real Estate
Excel produces the Regression ModelSlide11
Multiple Regression – First City Real Estate
To determine if multicollinearity is a problem:Select the Add-Ins tabSelect PHStatSelect RegressionSelect Multiple RegressionSlide12
Multiple Regression – First City Real Estate
Y variable … : A1:A320X variable … : B1:F320Select First Cells in both …Select Regression Statistics …Select ANOVA and …Select Variance Inflation … OKSlide13
Multiple Regression – First City Real Estate
PHStat will find Variance Inflation Factors for all independent variables. This slide shows the VIF for Garage # and all other X variables. Click on the sheet tabs X4,X3,X2,X1 to see the VIF for the other variables. Slide14
Issue:
First City managers wish to improve the model by adding a location variable for the area. Objective: Use Excel 2007 to improve a regression model by adding a dummy variable for the area either foothills or flatland.Data file is First City.xlsMultiple Regression - Dummy Variable -First City Real EstateSlide15
Multiple Regression- Dummy Variable – First City Real Estate
Select the Home-Sample 2 sheetSelect the Add-In tabSelect PHStatSelect RegressionSelect Multiple RegressionSlide16
Multiple Regression- Dummy Variable – First City Real Estate
Cut Bedrooms and Bathrooms columns and paste in cell G1. Delete columns C and D so that all data is in connected columnsSelect Data tabSelect RegressionInput Y Range: A1 – A320Input X Range B1-E320 Select LabelsNew Worksheet: Regress-2OKSlide17
Multiple Regression- Dummy Variable – First City Real Estate
All variables are significant and have the expected signSlide18
Curvilinear Relationships - Ashley Investment Services
Issue: The director of personnel is trying to determine whether there is a relationship between employee burnout and time spent socializing with co-workers. Objective: Use Excel 2007 to determine whether the relationship between the two measures is statistically significant. Data file is Ashley.xlsSlide19
Curvilinear Relationships – Ashley Investment Services
Open the file Ashley.xlsSlide20
Curvilinear Relationships – Ashley Investment Services
Select A1:B21Select the Insert TabSelect Scatter PlotSelect the Scatter Plot desiredSlide21
Curvilinear Relationships – Ashley Investment ServicesSlide22
Curvilinear Relationships – Ashley Investment Services
Select the Data tabSelect Data AnalysisSelect RegressionSlide23
Curvilinear Relationships – Ashley Investment Services
Y Range A1:A21X Range B1:B21LabelsNew Worksheet Ply: Ashley-1OKSlide24
Curvilinear Relationships – Ashley Investment ServicesSlide25
Curvilinear Relationships – Ashley Investment Services
To develop a nonlinear model, return to the data file. Create a new variable Socialization SquaredSlide26
Curvilinear Relationships – Ashley Investment Services
Select the Data tabSelect Data AnalysisSelect RegressionOKSlide27
Curvilinear Relationships – Ashley Investment Services
Input Y Range: A1:A21Input X Range: B1:C21Select LabelsNew Worksheet Ply: Ashley-2Slide28
Curvilinear Relationships – Ashley Investment Services
The output shows the R Square value and the Regression Coefficients.Slide29
Interaction Effects -
Ashley Investment ServicesIssue: The director of personnel is trying to determine whether the model can be improved by separating observations between those taken from men and women. Objective: Use Excel 2007 to determine whether the relationship between the measures can be improved. Data file is Ashley-2.xlsSlide30
Interaction Effects – Ashley Investment Services
Open file Ashley-2.xlsInsert a new column C as (Socialization Squared) which is Column B squaredAdd Column E as Columns B * DAdd Column F as Columns C * DSlide31
Interaction Effects – Ashley Investment Services
Using The Insert tab and Chart tools set up a Scatter Plot for one genderSlide32
Interaction Effects – Ashley
Investment ServicesAdd the second genderAdd exponential trend line for male and femaleSlide33
Interaction Effects – Ashley
Investment ServicesThe regression for the curvilinear model. Slide34
Issue:
The company is interested in analyzing the residuals of the regression model to determine whether the assumptions of multiple regression are satisfied. Objective: Use Excel 2007 to analyze residuals from a regression model. Data file is First City-3.xlsResidual Analysis - First City Real EstateSlide35
Residual Analysis – First City Real Estate
Open the file FirstCity3.xlsSince Excel requires independent variables to be in adjacent columns – cut and paste these columns.We will be using:PriceSq. FeetBedroomsGarage #Log of Lot Size Note: I will swap Lot Size and Log Lot Size to simplify the operationSlide36
Residual Analysis – First City Real Estate
Select the Data tabSelect Data AnalysisSelect RegressionOKSlide37
Residual Analysis – First City Real Estate
Define the range for X and Y variables.Excel gives several options for Residual Analysis but does not have as complete a set as MinitabSlide38
Residual Analysis – First City Real Estate
This is the Residual Plot for Square FeetSlide39
Residual Analysis – First City Real Estate
While Excel will not automatically generate a histogram of the standardized residuals , one can be created.Slide40
Residual Analysis – First City Real Estate
Define Bin values for the HistogramSlide41
Residual Analysis – First City Real Estate
Select the Data tabSelect Data AnalysisSelect HistogramOn the Histogram Chart identify the data and binsSelect Chart outputSlide42
Residual Analysis – First City Real Estate