Critical Thinking Project QM-3345

in /by

Critical Thinking Project QM-3345

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 sheettab 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.
  • Do you think we need all threecurrent independent variables in our Regression model to predict changes in MPG_Highway (Circle)? Yes No
  • Which variable(s) would you remove (Circle)?
  • Of the following variables in the spreadsheet, which variable would you select next to add to the model i.e., you think it would create a stronger prediction of MPG_Highway)?
  • Run a SAS Regression Model on the CARS database using MPG_Highway as the Dependent Variable (Y) and include the original Independent Variables (minus any you removed in step 6) and adding the variable you chose in step 7.Print the “Results” page to a PDF file and save it as NAME-Part2 (Print icon is found on left side above Table of Contents) Submit this File.
  • Provide the following from theSAS Model:
    • Coefficient of Determination (R-squared). ________________________
    • Y-Intercept for the Regression Model ________________________
    • Slope value for each of your Independent Variables. ________________________
  • Did your SAS model provide a stronger Coefficient of Determination (Circle)? Yes No
  • A dealership is trying to use similar data to Forecast total sales for the coming year (of New and Used Vehicles) and they have pulled data from their Finance records. They are trying to assemble the best data to build a Regression model. Which set of data fields from the following Independent Variables make the most practical sense to use in the decision model forecasting the Dependent Variable called “Sale Price” for any vehicles in inventory (CIRCLE)? This is a logically decision process. What is the Sale Price based on? DO NOT have SAS do more regression models.
    • MSRP, Color, Overall Length, Automatic vs. Manual (Transmission)
    • MSRP, Invoice, Make, Model, Odometer Reading
    • Invoice, Wheelbase, Model Year, Horsepower
    • MSRP, Manufacturer, Wheelbase Length, Gas Mileage

Recall the horizontal axis is displaying the explanatory variable (independent variable, X) and the vertical axis is displaying the dependent variable(Y).

ALL 5 Scatterplots should be on ONE worksheet to Submit in Canvas.

*Note: Not required here, but for professional usage — Do put in chart titles and axis titles, if your work will be used (or viewed) by other professionals. We are using your work here as a personal learning lab – to become familiar with the data, regression, to build models and to select a good regression model.

No relationship Weak Moderate Strong

No relationship Weak Moderate Strong

No relationship Weak Moderate Strong

No relationship Weak Moderate Strong

No relationship Weak Moderate Strong

Provide the followingfrom the “Excel Regression Model” in #3:

Recall that the coefficient on each X variable is the slope.

Y = ______ + _____X1 + ______X2 + ______X3 R2 = _____

AT THIS POINT, PLEASE SAVE YOUR EXCEL SPREADSHEET WITH YOUR “NAME–Part1” (John Doe-Part1.xlsx) and upload it (submit it) using the “Submit CARS Spreadsheet” assignment item in Canvas.

THEN, Do further analysis in Excel to answer these questions. Have Excel runmore regressions, and compare the R2 to make your decisions. Run the regression with only two variables and note the increase in R2 (percentage of Y’s variation explained in the model) when there are three variables.

Write the regression model for each of the 2 variable models and note the R2

Y = ______ + _____X1 + ______X2 R2 = _____

Y = ______ + _____X1 + ______X3 R2 = _____

Y = ______ + _____X2 + ______X3 R2 = _____

X1 = Horsepower X2 = Cylinders X3 = EngineSize

Start with the best 2 variable regression model (above) have Excel runmore regressions, and compare the R2 to make your decision. Run the regression with only two variables and note the increase in R2 (percentage of Y’s variation explained in the model) when there is one additional variable. Which one gives the highest improvement to R2?

X5 = Type X6 = DriveTrain X7 = Weight X8 = Wheelbase[DCN1] X9 = Length

END PART 1 Copy your answers from here as required, save and submit in Canvas“NAME-ANSWERS Part 1”” (John Doe-ANSWERS-Part1.xlsx)


PART 2 is due on or before November 26, 2017.

Part 2 of the Project (Critical Thinking and SAS® Model)

Note: You may repeat #3 and #4 (above in SAS) then continue the data analysis, model building decisions and critical thinking activities. OR just rely on what you learned in Part 1.

Critical Thinking Question:

END PART 2 Copy your answers from here as required and submit “NAME-ANSWERS Part 2” ” (John Doe-ANSWERS-Part2)

DATA ANALYSIS (Analysis Tool Pak add-in)

OPEN Excel

Check the DATA tab — on the far right – If “Data Analysis” is not there you need to add-in the option.

TO ADD-IN the Analysis ToolPak

File tab – Left menu, last item, click on “Options”.

Next page, Left menu, next to last item, click on “Add-ins”. At bottom (middle) click on “Go”

Next dialog box, check first item, “Analysis ToolPak. Click on ok.

Check the DATA tab — on the far right – “Data Analysis” should be there.

"You need a similar assignment done from scratch? Our qualified writers will help you with a guaranteed AI-free & plagiarism-free A+ quality paper, Confidentiality, Timely delivery & Livechat/phone Support.


Discount Code: CIPD30



Click ORDER NOW..

order custom paper