*** PLEASE SHOW HOW TO SOLVE IN EXCEL***
Case Problem2:Â Â Â Â Â Â Â Finding the Best Car Value
(Copy the worksheet named “FamilySedans†inQMB3200-Homework#10Data.xlsx into your file for this problem)
When trying to decide what car to buy, real value is notnecessarily determined by how much you spend on the initialpurchase. Instead, cars that are reliable and don’t cost much toown often represent the best values. But, no matter how reliable orinexpensive a car may cost to own, it must also perform well. Tomeasure value, Consumer Reports developed a statistic referred toas a value score. The value score is based upon five-year ownercosts, overall road-test scores, and predicted reliability ratings.Five-year owner costs are based on the expenses incurred in thefirst five years of ownership, including depreciation, fuel,maintenance and repairs, and so on. Using a national average of12,000 miles per year, an average cost per mile driven is used asthe measure of five-year owner costs. Road-test scores are theresults of more than 50 tests and evaluations and are based upon a100-point scale, with higher scores indicating better performance,comfort, convenience, and fuel economy. The highest road-test scoreobtained in the tests conducted by Consumer Reports was a 99 for aLexus LS 460L. Predicted-reliability ratings (1 = Poor, 2 = Fair, 3= Good, 4 = Very Good, and 5 = Excellent) are based on data fromConsumer Reports’ Annual Auto Survey.
A car with a value score of 1.0 is considered to be“average-value.†A car with a value score of 2.0 is considered tobe twice as good a value as a car with a value score of 1.0; a carwith a value score of 0.5 is considered half as good as average;and so on. The data for 20 family sedans, including the price ($)of each car tested are contained in the worksheet“FamilySedans.â€
| Car | Price ($) | Cost/Mile | Road-Test Score | Predicted Reliability | Value Score |
| NissanAltima 2.5 S (4-cyl.) | 23,970.00 | 0.59 | 91 | 4 | 1.75 |
| KiaOptima LX (2.4) | 21,885.00 | 0.58 | 81 | 4 | 1.73 |
| SubaruLegacy 2.5i Premium | 23,830.00 | 0.59 | 83 | 4 | 1.73 |
| FordFusion Hybrid | 32,360.00 | 0.63 | 84 | 5 | 1.7 |
| HondaAccord LX-P (4-cyl.) | 23,730.00 | 0.56 | 80 | 4 | 1.62 |
| Mazda6 iSport (4-cyl.) | 22,035.00 | 0.58 | 73 | 4 | 1.6 |
| HyundaiSonata GLS (2.4) | 21,800.00 | 0.56 | 89 | 3 | 1.58 |
| FordFusion SE (4-cyl.) | 23,625.00 | 0.57 | 76 | 4 | 1.55 |
| ChevroletMalibu LT (4-cyl.) | 24,115.00 | 0.57 | 74 | 3 | 1.48 |
| KiaOptima SX (2.0T) | 29,050.00 | 0.72 | 84 | 4 | 1.43 |
| FordFusion SEL (V6) | 28,400.00 | 0.67 | 80 | 4 | 1.42 |
| NissanAltima 3.5 SR (V6) | 30,335.00 | 0.69 | 93 | 4 | 1.42 |
| HyundaiSonata Limited (2.0T) | 28,090.00 | 0.66 | 89 | 3 | 1.39 |
| HondaAccord EX-L (V6) | 28,695.00 | 0.67 | 90 | 3 | 1.36 |
| Mazda6 sGrand Touring (V6) | 30,790.00 | 0.74 | 81 | 4 | 1.34 |
| FordFusion SEL (V6, AWD) | 30,055.00 | 0.71 | 75 | 4 | 1.32 |
| SubaruLegacy 3.6R Limited | 30,094.00 | 0.71 | 88 | 3 | 1.29 |
| ChevroletMalibu LTZ (V6) | 28,045.00 | 0.67 | 83 | 3 | 1.2 |
| Chrysler200 Limited (V6) | 27,825.00 | 0.7 | 52 | 5 | 1.2 |
| ChevroletImpala LT (3.6) | 28,995.00 | 0.67 | 63 | 3 | 1.05 |
Managerial Report
- Develop numerical summaries of the data.
- Use regression analysis to develop an estimated regressionequation that could be used to predict the value score given theprice of the car.
- Use regression analysis to develop an estimated regressionequation that could be used to predict the value score given thefive-year owner costs (cost/mile).
- Use regression analysis to develop an estimated regressionequation that could be used to predict the value score given theroad-test score.
- Use regression analysis to develop an estimated regressionequation that could be used to predict the value score given thepredicted-reliability
What conclusions can you derive from your analysis?
