Guide to Using Excel For Basic Statistical Applications
Guide to Using Excel For Basic Statistical Applications To Accompany Business Statistics: A Decision Making Approach, 6 th Ed. Chapter 14: Multiple Regression Analysis and Model Building By Groebner, Shannon, Fry, & Smith Prentice-Hall Publishing Company Copyright, 2005
Chapter 14 Excel Examples ® 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 14 Excel Examples ® 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 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 File First City. xls
Multiple Regression – First City Real Estate First click on Tools, then Data Analysis and finally on Correlation.
Multiple Regression – First City Real Estate Define ranges for Variables. Click on O. K.
Multiple Regression – First City Real Estate The Excel output shows the correlation between Age and Square Feet.
Multiple Regression – First City Real Estate Return to the data sheet, click on Tools, Data Analysis and then Regression.
Multiple Regression – First City Real Estate Identify range of dependent and independent variables. Click OK.
Multiple Regression – First City Real Estate Excel determines the regression model.
Multiple Regression – First City Real Estate To determine the Variance Inflation Factors, return to the data sheet, click on PHStat, Regression and Multiple Regression.
Multiple Regression – First City Real Estate Identify range of dependent and independent variables. Click on VIF then click OK.
Multiple Regression – First City Real Estate PHStat will find Variance Inflation Factors for all independent variables.
Multiple Regression - Dummy Variable -First City Real Estate Issue: First City managers wish to improve the model by adding a location variable. Objective: Use Excel to improve a regression model by adding a dummy variable. Data file is First City. xls
Multiple Regression- Dummy Variable – First City Real Estate Open File First City. xls click on the worksheet containing the Area data.
Multiple Regression- Dummy Variable – First City Real Estate Click on PHStat then Regression and Multiple Regression.
Multiple Regression- Dummy Variable – First City Real Estate Define the Data ranges for the Y and X Variable then select Regression Statistics Table, ANOVA Coefficients Table and VIF.
Multiple Regression- Dummy Variable – First City Real Estate The output shows improved Regression Statistics.
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 to determine whether the relationship between the two measures is statistically significant. Data file is Ashley. xls
Curvilinear Relationships – Ashley Investment Services Open File Ashley. xls File contains values for 20 Investment Advisors.
Curvilinear Relationships – Ashley Investment Services To develop the scatter plot first click on Chart Wizard button then select XY (Scatter).
Curvilinear Relationships – Ashley Investment Services Define the Data range and remember the X variable must be in the left column. You may have to reverse the columns by cutting and pasting.
Curvilinear Relationships – Ashley Investment Services Label the X and Y axis.
Curvilinear Relationships – Ashley Investment Services Find the scatter plot.
Curvilinear Relationships – Ashley Investment Services To find the regression model, return to the data sheet, click on Tools, then Data Analysis and finally Regression.
Curvilinear Relationships – Ashley Investment Services Identify the range for the X and Y variables. Then click OK.
Curvilinear Relationships – Ashley Investment Services The output shows the R Square value and the Regression Coefficients.
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 To find the regression model, click on Tools, then Data Analysis and finally Regression.
Curvilinear Relationships – Ashley Investment Services Identify the range for the X and Y variables. Then click OK.
Curvilinear Relationships – Ashley Investment Services The output shows the R Square value and the Regression Coefficients.
Curvilinear Relationships – Ashley Investment Services For an alternate way to find a nonlinear model, return to the scatter plot, left click on any point on the graph then right click. Select Add Trendline.
Curvilinear Relationships – Ashley Investment Services Select Exponential.
Curvilinear Relationships – Ashley Investment Services This gives the Exponential Regression Line. To add the Regression Equation, left click on the trend line, then right click to Format Trendline
Curvilinear Relationships – Ashley Investment Services Select Options. Check Display equation and Display Rsquare.
Curvilinear Relationships – Ashley Investment Services This adds the Regression Equation and Rsquare value to the Trend Line.
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 to determine whether the relationship between the measures can be improved. Data file is Ashley-2. xls
Curvilinear Relationships –Ashley Investment Services Open File Ashley-2. xls
Curvilinear Relationships –Ashley Investment Services Group the data from males and females together by identifying the range of data, then clicking on Data then Sort.
Curvilinear Relationships –Ashley Investment Services Click on the Chart Wizard and then (XY) Scatter. Click Next
Curvilinear Relationships –Ashley Investment Services Click on the Series Tab. Identify the range for data from Males.
Curvilinear Relationships –Ashley Investment Services Add Females to the Series and identify the appropriate Data Range.
Curvilinear Relationships –Ashley Investment Services Label the Chart and the X and Y Axes. Then click Next.
Curvilinear Relationships –Ashley Investment Services The scatter plot now separates male and female data points.
Curvilinear Relationships –Ashley Investment Services To find a nonlinear model for each data group, left click on any point on each series then right click. Select Add Trendline then Exponential.
Curvilinear Relationships –Ashley Investment Services Add colors by left cicking on each Trendline, then right click and select Format Trendline.
Curvilinear Relationships – Ashley Investment Services A nonlinear model can also be developed by creating new variables and proceeding as in the last tutorial.
Curvilinear Relationships – Ashley Investment Services To find the regression model, click on Tools, then Data Analysis and finally Regression.
Curvilinear Relationships – Ashley Investment Services Identify the range for the X and Y variables. Then click OK.
Curvilinear Relationships – Ashley Investment Services The output shows the R Square value and the Regression Coefficients.
Residual Analysis First City Real Estate Issue: The company is interested in analyzing the residuals of the regression model to determine whether the assumptions are satisfied. Objective: Use Excel to analyze residuals from a regression model. Data file is First City-3. xls
Residual Analysis – First City Real Estate Open file First City-3. xls
Residual Analysis – First City Real Estate Since Excel requires the independent variables to be adjacent, cut and paste the appropriate columns.
Residual Analysis – First City Real Estate First click on Tools, then Data Analysis and then on Regression.
Residual Analysis – First City Real Estate Define 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 Selecting the Residual Plot option on the previous page will give a set of residual plots.
Residual Analysis – First City Real Estate Any of the residual plots can be adjusted to give a clearer picture of the residual pattern.
Residual Analysis – First City Real Estate This is the output if the Normal Probability Plot option is selected.
Residual Analysis – First City Real Estate While Excel will not automatically generate a histogram of residual values, one can be generated. Start with the Standardized Residuals.
Residual Analysis – First City Real Estate Define the Bin values for the Histogram. Then click on Tools, select Data Analysis and then Histogram.
Residual Analysis – First City Real Estate Identify the data range for the Standard Residuals and Bins. Specify Chart Output.
Residual Analysis – First City Real Estate Size and format the Histogram output. Also, close the gaps between the rectangles.
- Slides: 63