Note: By submitting the work, you are confirming that this is your own work. Rutgers Academic Integrity policies

apply if you copied or borrowed anyone$$s work.

Data Set:

$1.$ Download Cars Data.xlsx from Canvas.

Project Objectives:

$1.$ Focus on columns B$-$G$.$ Column B $($MPG$)$ is the output or dependent variable. Columns C$-$G are predictors.

$2.$ Develop a prediction model using regression to predict MPG as a function of Cylinders, Displacement,

Horsepower, Weight, and Acceleration.

Project Steps:

$1.$ Create a new worksheet $($name the worksheet Title$)$: On that worksheet write your name and date of

submission. $5$ pts

$2.$ Understanding the Data:

a$.$ Complete the three steps outlined on Slide $11$ in the machine learning lecture deck. Show your work for

each step in a separate worksheet. You will need three new worksheets, once for each step. $15$ pts

b$.$ Create a new worksheet $($name it Data Insights$)$ in the project file and write FIVE short sentences of

what you have observed $$ summary statistics of variables, correlation between output and each of the

inputs, between inputs, what do the X$-$Y plots show, etc. $10$ pts

$3.$ Use Excel Data Analysis to fit a multiple linear regression model $($Y $-$ MPG$,$ Xs $-$ Cylinders, Displacement,

Horsepower, Weight, and Acceleration$).$ Follow the steps demonstrated in class. Ensure model output is in a

separate worksheet. Name that worksheet Original Regression. $15$ pts

$4.$ Determine if the model is valid and good based on steps outlined on Slide $17.$ Create a new worksheet $($name it

Verification$)$ and write your conclusion for each of the steps. Highlight the data you used in performing the

verification steps in the Original Regression tab. $25$ pts

$5.$ In the Original Regression, if you found any of the predictors are insignificant $($p$-$value $>0\mathrm{.}05),$ remove the

predictor with the highest p$-$value $($least significant$).$ You can do this by copying and pasting data set to a new

worksheet and deleting the column that you would like to remove. Name this new worksheet $($Reduced Data$).$

$10$ pts

$6.$ Perform regression using the data in the Reduce Data worksheet. Use the result as your final regression

$($prediction$)$ equation. Ensure that regression model output is in a new worksheet. Name this worksheet Final

Regression. $15$ pts

$7.$ Write your final regression equation to a new worksheet tab $($Prediction$).5$ pts

$8.$ Predict MPG for the following car:

Cylinders $4$

Displacement $200$

Horsepower $100$

Weight $2970$

Acceleration $15$

Note: If you dropped any of the above predictors in Step $5,$ you can ignore that predictor.

Write the predicted MPG in the Prediction worksheet.