San Diego State University Simple Linear Regression Analysis Worksheet

Please follow the directions document added below.

Simple Linear Regression (SLR)

I am interested in buying a used car for my niece. She moved from the east coast to San Diego for an extended job training program. I am only interested in 4-door cars (no sports coupes, no trucks) with automatic transmission and costing less than $50,00, although cars approaching that top-end price point would need to be ideal. I have collected data on 100 used cars meeting the criteria that are for sale within 25 miles of San Diego city center.

For myself I want a good deal financially. That involves the purchase price. As the job training program is more about “training” and less about “job pay,” for my niece I want a car that does not cost a lot to operate – low maintenance, reasonable insurance terms, and good gas mileage, as the job training requires many miles of driving around the county, and occasionally to the counties north of San Diego.

I would appreciate you performing a two-pronged analysis on the data:

? First, please analyze the relationship – via SLR — between the age of the car and selling price.

? Second, of the three listed cars below make a recommendation as to which one I should further

investigate as to purchasing.

.

Car 1: Age – 6 Years Old Price: $18,300

Audi Tires & Wheels Upgraded Head Up Display for Driver 2d Owner

MPG: 20 City, 25 Highway 1 Year Warranty

Car 2: Age – 9 Years Old Price: $8,600

Volvo Sold As Is

Advanced Safety Features Excellent Condition

MPG: 23 City, 34 Highway Confident Handling

Car 3: Age – 3 Years Old Price: $33,000

BMW Low Miles!

Drivetrain AWD Loaded!

MPG: 20 City, 29 Highway 36/36 Warranty

Open the Excel SLR File

Data Sheet – The Age and Selling Prices of 100 Used Cars

SLR Analysis Sheet – This sheet contains a preconfigured table to instantly peel, analyze and tabulate the relevant results of your Raw Data regression analysis.

SLR Analysis

1 Start on the Data Sheet

2 Click on the DATA TAB on the toolbar, select Data Analysis, and then select Regression.

Fill the dialog box as follows:

? Input Y Range: The range of the data in Column B, including the heading, Selling Price

? Input X Range: The range of the data in Column A, including the heading, Car Age

? Click Labels.

? Output Range: E1 (You must use Cell E1 as the Output location.)

? Click Okay

3 The table on the SLR Analysis sheet should now be complete.

SLR Graph

1 Highlight the Car Age and Selling Price data

2 Click on INSERT on the ribbon tool bar, select Charts, Scatter Plot

? Add a trendline to the chart. To do so varies greatly across different versions of Excel. You may need to click on the “+” sign beside the chart, then select Trendline. You may need to click on Chart Elements on the ribbon bar. You may need to click on the graph, select Layout from Chart Tools, and then Trendline and lastly Linear Trendline. If none of these works, Google Trendline Scatter Plot and your version of Excel.

? Label, identify, scale, format, and clean up your graph, as needed.

SLR Submission Form

1 Copy & Paste your SLR Used Cars Selling Price Table into the first box.

2 Copy & Paste your SLR Graph into the second image box.

3 You can click on an image and use the circle resize handles to fit an image to fill its box.

4 Based on any of the given information/data, output data, or your personal insight, provide

your recommendation in the third box.

5 Do not go behind 1 page.