car age ● click labels .● output range B u s i n e s s F i n a n c e
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.
● 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.
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.
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
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.