Guide to Using Excel 2007 For Basic Statistical

Guide to Using Excel 2007 For Basic Statistical Applications To Accompany Business Statistics: A Decision Making Approach, 8 th Ed. Chapter 15: Multiple Regression and Model Building By Groebner, Shannon, Fry, & Smith Prentice-Hall Publishing Company Copyright, 2011

Chapter 15 Excel Examples n n Multiple Regression First City Real Estate Multiple Regression –Dummy Variable First City Real Estate Curvilinear Regression Ashley Investment Services Interaction Effects Ashley Investment Services More

Chapter 15 Excel Examples n Residual Analysis First City Real Estate

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 First. City. xls

Multiple Regression – First City Real Estate • Open the file First. City. xls, Sheet Homes-Sample 1 • Select the Data Tab • Select Data Analysis • Select Correlation • OK

Multiple Regression – First City Real Estate • Input Range: A 1: G 320 • Grouped by: Columns • New Worksheet Ply: corr-1 • OK

Multiple Regression – First City Real Estate This cell shows the correlation, 0. 7477, between Price and Square Feet

Multiple Regression – First City Real Estate Return to Homes-Sample 1 • Select the Data tab • Select Data Analysis • Select Regression • OK

Multiple Regression – First City Real Estate • Input Y Range: A 1: A 320 • Input X Range: B 1: F 320 • Select labels • New Worksheet Ply: regress-1 • OK

Multiple Regression – First City Real Estate Excel produces the Regression Model

Multiple Regression – First City Real Estate To determine if multicollinearity is a problem: • Select the Add-Ins tab • Select PHStat • Select Regression • Select Multiple Regression

Multiple Regression – First City Real Estate • Y variable … : A 1: A 320 • X variable … : B 1: F 320 • Select First Cells in both … • Select Regression Statistics … • Select ANOVA and … • Select Variance Inflation … • OK

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 X 4, X 3, X 2, X 1 to see the VIF for the other variables.

Multiple Regression - Dummy Variable -First City Real Estate 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. xls

Multiple Regression- Dummy Variable – First City Real Estate • Select the Home-Sample 2 sheet • Select the Add-In tab • Select PHStat • Select Regression • Select Multiple Regression

Multiple Regression- Dummy Variable – First City Real Estate • Cut Bedrooms and Bathrooms columns and paste in cell G 1. Delete columns C and D so that all data is in connected columns • Select Data tab • Select Regression • Input Y Range: A 1 – A 320 • Input X Range B 1 -E 320 • Select Labels • New Worksheet: Regress-2 • OK

Multiple Regression- Dummy Variable – First City Real Estate All variables are significant and have the expected sign

Curvilinear Relationships Ashley Investment Services Issue: The director of personnel is trying to determine whethere 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. xls

Curvilinear Relationships – Ashley Investment Services Open the file Ashley. xls

Curvilinear Relationships – Ashley Investment Services • Select A 1: B 21 • Select the Insert Tab • Select Scatter Plot • Select the Scatter Plot desired

Curvilinear Relationships – Ashley Investment Services

Curvilinear Relationships – Ashley Investment Services • Select the Data tab • Select Data Analysis • Select Regression

Curvilinear Relationships – Ashley Investment Services • Y Range A 1: A 21 • X Range B 1: B 21 • Labels • New Worksheet Ply: Ashley-1 • OK

Curvilinear Relationships – Ashley Investment Services

Curvilinear Relationships – Ashley Investment Services To develop a nonlinear model, return to the data file. Create a new variable Socialization Squared

Curvilinear Relationships – Ashley Investment Services • Select the Data tab • Select Data Analysis • Select Regression • OK

Curvilinear Relationships – Ashley Investment Services • • Input Y Range: A 1: A 21 Input X Range: B 1: C 21 Select Labels New Worksheet Ply: Ashley-2

Curvilinear Relationships – Ashley Investment Services The output shows the R Square value and the Regression Coefficients.

Interaction Effects Ashley Investment Services Issue: 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. xls

Interaction Effects – Ashley Investment Services • Open file Ashley-2. xls • Insert a new column C as (Socialization Squared) which is Column B squared • Add Column E as Columns B * D • Add Column F as Columns C * D

Interaction Effects – Ashley Investment Services Using The Insert tab and Chart tools set up a Scatter Plot for one gender

Interaction Effects – Ashley Investment Services Add the second gender Add exponential trend line for male and female

Interaction Effects – Ashley Investment Services The regression for the curvilinear model.

Residual Analysis First City Real Estate 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. xls

Residual Analysis – First City Real Estate Open the file First. City 3. xls Since Excel requires independent variables to be in adjacent columns – cut and paste these columns. We will be using: • Price • Sq. Feet • Bedrooms • Garage # • Log of Lot Size • Note: I will swap Lot Size and Log Lot Size to simplify the operation

Residual Analysis – First City Real Estate • Select the Data tab • Select Data Analysis • Select Regression • OK

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 Minitab

Residual Analysis – First City Real Estate This is the Residual Plot for Square Feet

Residual Analysis – First City Real Estate While Excel will not automatically generate a histogram of the standardized residuals , one can be created.

Residual Analysis – First City Real Estate Define Bin values for the Histogram

Residual Analysis – First City Real Estate • Select the Data tab • Select Data Analysis • Select Histogram • On the Histogram Chart identify the data and bins • Select Chart output

Residual Analysis – First City Real Estate