Guide to Using Excel 2007 For Basic Statistical Application

Guide to Using Excel 2007 For Basic Statistical Application Guide to Using Excel 2007 For Basic Statistical Application - Start

Added : 2016-10-06 Views :53K

Download Presentation

Guide to Using Excel 2007 For Basic Statistical Application




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.



Presentations text content in Guide to Using Excel 2007 For Basic Statistical Application

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, 2011

Slide2

Chapter 15 Excel Examples

Multiple Regression First City Real EstateMultiple Regression –Dummy Variable First City Real EstateCurvilinear Regression Ashley Investment ServicesInteraction Effects Ashley Investment Services

More Examples

Slide3

Chapter 15 Excel Examples

Residual Analysis First City Real Estate

Slide4

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

Slide5

Multiple Regression – First City Real Estate

Open the file FirstCity.xls, Sheet Homes-Sample 1

Select the Data Tab

Select Data Analysis

Select Correlation

OK

Slide6

Multiple Regression – First City Real Estate

Input Range: A1:G320

Grouped by: Columns

New Worksheet Ply: corr-1

OK

Slide7

Multiple Regression – First City Real Estate

This cell shows the correlation, 0.7477, between Price and Square Feet

Slide8

Multiple Regression – First City Real Estate

Return to

Homes-Sample 1

Select the Data tab

Select Data Analysis

Select Regression

OK

Slide9

Multiple Regression – First City Real Estate

Input Y Range: A1:A320

Input X Range: B1:F320

Select labels

New Worksheet Ply: regress-1

OK

Slide10

Multiple Regression – First City Real Estate

Excel produces the Regression Model

Slide11

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

Slide12

Multiple Regression – First City Real Estate

Y variable … : A1:A320

X variable … : B1:F320

Select First Cells in both …

Select Regression Statistics …

Select ANOVA and …

Select Variance Inflation …

OK

Slide13

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

Multiple Regression - Dummy Variable -First City Real Estate

Slide15

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

Slide16

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 columns

Select Data tab

Select Regression

Input Y Range: A1 – A320

Input X Range B1-E320

Select Labels

New Worksheet: Regress-2

OK

Slide17

Multiple Regression- Dummy Variable – First City Real Estate

All variables are significant and have the expected sign

Slide18

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

Slide19

Curvilinear Relationships – Ashley Investment Services

Open the file Ashley.xls

Slide20

Curvilinear Relationships – Ashley Investment Services

Select A1:B21

Select the Insert Tab

Select Scatter Plot

Select the Scatter Plot desired

Slide21

Curvilinear Relationships – Ashley Investment Services

Slide22

Curvilinear Relationships – Ashley Investment Services

Select the Data tab

Select Data Analysis

Select Regression

Slide23

Curvilinear Relationships – Ashley Investment Services

Y Range A1:A21

X Range B1:B21

Labels

New Worksheet Ply: Ashley-1

OK

Slide24

Curvilinear Relationships – Ashley Investment Services

Slide25

Curvilinear Relationships – Ashley Investment Services

To develop a nonlinear model, return to the data file. Create a new variable Socialization Squared

Slide26

Curvilinear Relationships – Ashley Investment Services

Select the Data tab

Select Data Analysis

Select Regression

OK

Slide27

Curvilinear Relationships – Ashley Investment Services

Input Y Range: A1:A21

Input X Range: B1:C21

Select Labels

New Worksheet Ply: Ashley-2

Slide28

Curvilinear Relationships – Ashley Investment Services

The output shows the R Square value and the Regression Coefficients.

Slide29

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

Slide30

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

Slide31

Interaction Effects – Ashley Investment Services

Using The Insert tab and Chart tools set up a Scatter Plot for one gender

Slide32

Interaction Effects – Ashley

Investment Services

Add the second gender

Add exponential trend line for male and female

Slide33

Interaction Effects – Ashley

Investment Services

The 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.xls

Residual Analysis - First City Real Estate

Slide35

Residual Analysis – First City Real Estate

Open the file FirstCity3.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

Slide36

Residual Analysis – First City Real Estate

Select the Data tab

Select Data Analysis

Select Regression

OK

Slide37

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

Slide38

Residual Analysis – First City Real Estate

This is the Residual Plot for Square Feet

Slide39

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 Histogram

Slide41

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

Slide42

Residual Analysis – First City Real Estate

Slide43

Slide44

Slide45

Slide46

Slide47


About DocSlides
DocSlides allows users to easily upload and share presentations, PDF documents, and images.Share your documents with the world , watch,share and upload any time you want. How can you benefit from using DocSlides? DocSlides consists documents from individuals and organizations on topics ranging from technology and business to travel, health, and education. Find and search for what interests you, and learn from people and more. You can also download DocSlides to read or reference later.
Youtube