Description Critical Thinking Project – using Excel and SAS Regression Tools INSTRUCTIONS, GUIDE AND NOTES We use Excel to become familiar with the collected data, recognize relationships, and to build several regressions models and to select a good regression model then we use SAS to build a multiple regression model efficiently. This project utilizes the CARS database. The CARS database includes the following variables for 428 automobiles. These variables are shown as qualitative variables within the database and will NEED to be formatted as numbers for Excel’s work (Home tab | number area | -0- decimal places): *Make (Automobile manufacturer) *Model (Specific auto model) *Type (Hybrid, Sedan, Sports, SUV, Truck Wagon) *Origin (Country where manufactured) *DriveTrain (All-wheel drive, front-wheel drive, rear-wheel drive) MSRP (Manufacturer’s Suggested Retail Price) Invoice (Invoice price paid by dealer) Engine Size (Stated in Liters) Cylinders (Number of engine cylinders) Horsepower (Of engine) MPG_City (Miles per gallon of fuel used in City driving) MPG_Highway (Miles per gallon of fuel used in Highway driving) Weight (Gross weight of the empty vehicle) Wheelbase (Length in inches from front axle to rear axle) Length (Overall length, bumper to bumper) Part 1 of the Critical Thinking Project for QM3345 using Excel for data relationships Create the following charts in Excel using the charting tools (center of the Insert tab in Excel). Create a new sheet\tab in the spreadsheet called “Scatterplots”. After creating each chart scatterplot) on the original tab, move or copyeach of the 5 scatterplots to the Scatterplot tab you created. Create a Scatterplot using the variables Invoice and MSRP. Notice the very strong positive relationship between invoice price and MSRP. (Y=Invoice on vertical axis, X=MSRP on horizontal axis). Create a Scatterplot using the variables MPG_Highway and MSRP.Notice the very weak relationship between MPG_Highway price and MSRP. (Y=MPG_Highway on vertical axis, X=MSRP on horizontal axis). Create a Scatterplot using the variables MPG_Highway and EngineSize. (Y=MPG_Highway on vertical axis, X=EngineSize on horizontal axis). Create a Scatterplot using the variables MPG_Highway and Cylinders. . (Y=MPG_Highway on vertical axis, X=Cylinders on horizontal axis). Create a Scatterplot using the variables MPG_HIghway and Horsepower.. (Y=MPG_Highway on vertical axis, X=Horsepower on horizontal axis). What sort of relationship do you see between these variables based on the scatterplots? (Recall r and R2give this information with more accuracy, but you can see it with a scatterplot) Between Invoice and MSRP (Circle)? Between MPG_Highway and MSRP (Circle)? Between MPG_Highway and EngineSize (Circle)? Between MPG_Highway and Cylinders (Circle)? Between MPG_Highway and Horsepower (Circle)? UsingExcel’s Data Analysis Add-in (you must open this zipped file in Excel), run a regression analysis with MPG_Highway as “Y” the dependent variable and the 3 Xs- independent variables (at the same time – multiple regression) Horsepower, Cylinders, and EngineSize (in this order)are the Independent Variables. Have the “output” put on a new worksheet|tab and name the sheet “Regression Model”. Coefficient of Determination (R-squared) ___________________ Y-Intercept for the Regression Model ___________________ Slope value for X1 (Horsepower) ___________________ Slope value for X2 (Cylinders) ___________________ Slope value for X3 (EngineSize) ___________________ Write the regression model that includes the information from b- e.
Recent Comments