Fill in a Spreadsheet with Data

This assignment needs to be completed in excel. I left the data on the excel file that needs to be used. This assignment will show a simple case of building a model using multiple linear regression. For this data set, a representative sample of over eight hundred 2005 GM cars was selected, then retail price was calculated from the tables provided in the 2005 Central Edition of the Kelly Blue Book The data set containing the following variables: • Price: suggested retail price of the used 2005 GM car in excellent condition. The condition of a car can greatly affect price. All cars in this data set were less than one year old when priced and considered to be in excellent condition. • Mileage: number of miles the car has been driven • Make: manufacturer of the car such as Saturn, Pontiac, and Chevrolet • Model: specific models for each car manufacturer such as Ion, Vibe, Cavalier • Trim (of car): specific type of car model such as SE Sedan 4D, Quad Coupe 2D • Type: body type such as sedan, coupe, etc. • Cylinder: number of cylinders in the engine • Liter: a more specific measure of engine size • Doors: number of doors • Cruise: indicator variable representing whether the car has cruise control (1 = cruise) • Sound: indicator variable representing whether the car has upgraded speakers (1 = upgraded) • Leather: indicator variable representing whether the car has leather seats (1 = leather) Part 1 (40 Points) Everyone says mileage is a key indicator of any used car. Your task is to create a scatter chart using Price and Mileage only. Use Mileage to go along the X-axis and Price along the Y-axis. • Create the scatter plot and include in your report • Include a linear trend line • Include the regression equation in the plot Answer the following questions: 1. What is the formula between Mileage and Price? Put the formula as the part of the chart title. 2. In general, what happens to price when there is one more mile on the car? 3. Does mileage help you predict price? In order words, is there a strong correlation (a steeply sloping trend line) between mileage and price? Part 2 (60 Points) In this part, we will perform a regression using more than one variable, a technique known as multiple regression. Two indicators come from regression that are of particular importance in describing the accuracy of our model. 1. R2 describes the proportion of variation in the response data explained by the predictors in the model. 2. Adjusted R2 is a modified version of R that has been adjusted for the number of predictors in the model. A good model should have high R and adjusted R. Create a regression model for price using the following variables: • Mileage • Cylinders • Doors • Cruise • Sound • Leather Answer the following questions: 1. What is the regression formula? 2. What is the R2 value? Is it better than the one for part 2? What does that tell you about this model as a predictor or used car price? 4. Based on the formula, list the variables in the order of their influence/importance in formula 5. For each variable, what happens to price when there is one unit increase for that variable?