Case Problem #6: Finding the Best Car Value in Excel CASE PROBLEM 4: FINDING THE BESTCAR VALUEWhen trying to decide what car to buy, real value is not necessarily determined by howmuch you spend on the initial purchase. Instead, cars that are reliable and don't cost muchto own often represent the best values. But, no matter how reliable or inexpensive a carmay cost to own, it must also perform well.To measure value, Consumer Reports developed a statistic referred to as a value score.The value score is based upon five-year owner costs, overall road-test scores, and predictedreliability ratings. Five-year owner costs are based on the expenses incurred in the firstfive years of ownership, including depreciation, fuel, maintenance and repairs, and so on.Using a national average of 12,000 miles per year, an average cost per mile driven is used Poor, 2 Fair, 3 = Good, 4 Very Good, and 5=A car with a value score of 1.0 is considered to be "average-value." A car with a valueas the measure of five-year owner costs. Road-test scores are the results of more than 50tests and evaluations and are based upon a 100-point scale, with higher scores indicatingbetter performance, comfort, convenience, and fuel economy. The highest road-test scorescore of 2,0 is considered to be twice as good a value as a car with a value score of 10, acar with a value score of .5 is considered half as good as average; and so on. The data forobtained in the tests conducted by Consumer Reports was a 99 for a Lexus LS A66Predicted-reliability ratings (1 =Excellent) are based on data from Consumer Reports' Annual Auto Survey20 family sedans, including the price ($) of each car tested, follow,Road-TestPredictedValueCarPrice ($) Cost/Mile ScoreReliabilityScoreNissan Altima 2.5 S (4-cyl.)Kia Optima LX (2.4)Subaru Legacy 2.5i PremiumFord Fusion HybridHonda Accord LX-P (4-cyl.)Mazdaó i Sport (4-cyl.)Hyundai Sonata GLS (2.4)Ford Fusion SE (4-cyl.)Chevrolet Malibu LT (4-cyl.)Kia Optima SX (2.0T)Ford Fusion SEL (V6)Nissan Altima 3.5 SR (V6)23,97021,885.59914.1.751.731.731.701.621.601.581.551.4858814.23,83032,36023,73022,03521,80023,625.59834..63845..5680.58734..56893..57764.24,11529,05028,40030,33528,09028,69530,790.5774.72844.1.431.42.67804..699341.42Hyundai Sonata Limited (2.0T)Honda Accord EX-L (V6)Mazda6 s Grand Touring (V6)Ford Fusion SEL (V6, AWD)Subaru Legacy 3.6R LimitedChevrolet Malibu LTZ (V6).66.67891.39901.36.748141.3430,05530,094.71754.1.32.71881.2928,04527,82528,995.67831.20Chrysler 200 Limited (V6)Chevrolet Impala LT (3.6).70521.20.67631.05Managerial Report1. Develop numerical summaries of the data.2. Use regression analysis to develop an estimated regression equation that could be usedto predict the value score given the price of the car.3. Use regression analysis to develop an estimated regression equation that could be usedto predict the value score given the five-year owner costs (cost/mile).4. Use regression analysis to develop an estimated regression equation that could be usedto predict the value score given the road-test score.5. Use regression analysis to develop an estimated regression equation that could be uscoto predict the value score given the predicted-reliability.6. What conclusions can you derive from your analysis?t3353

Question

Case Problem #6: Finding the Best Car Value in Excel CASE PROBLEM 4: FINDING THE BESTCAR VALUEWhen trying to decide what car to buy, real value is not necessarily determined by howmuch you spend on the initial purchase. Instead, cars that are reliable and don't cost muchto own often represent the best values. But, no matter how reliable or inexpensive a carmay cost to own, it must also perform well.To measure value, Consumer Reports developed a statistic referred to as a value score.The value score is based upon five-year owner costs, overall road-test scores, and predictedreliability ratings. Five-year owner costs are based on the expenses incurred in the firstfive years of ownership, including depreciation, fuel, maintenance and repairs, and so on.Using a national average of 12,000 miles per year, an average cost per mile driven is used Poor, 2 Fair, 3 = Good, 4 Very Good, and 5=A car with a value score of 1.0 is considered to be &#34;average-value.&#34; A car with a valueas the measure of five-year owner costs. Road-test scores are the results of more than 50tests and evaluations and are based upon a 100-point scale, with higher scores indicatingbetter performance, comfort, convenience, and fuel economy. The highest road-test scorescore of 2,0 is considered to be twice as good a value as a car with a value score of 10, acar with a value score of .5 is considered half as good as average; and so on. The data forobtained in the tests conducted by Consumer Reports was a 99 for a Lexus LS A66Predicted-reliability ratings (1 =Excellent) are based on data from Consumer Reports' Annual Auto Survey20 family sedans, including the price ($) of each car tested, follow,Road-TestPredictedValueCarPrice ($) Cost/Mile ScoreReliabilityScoreNissan Altima 2.5 S (4-cyl.)Kia Optima LX (2.4)Subaru Legacy 2.5i PremiumFord Fusion HybridHonda Accord LX-P (4-cyl.)Mazdaó i Sport (4-cyl.)Hyundai Sonata GLS (2.4)Ford Fusion SE (4-cyl.)Chevrolet Malibu LT (4-cyl.)Kia Optima SX (2.0T)Ford Fusion SEL (V6)Nissan Altima 3.5 SR (V6)23,97021,885.59914.1.751.731.731.701.621.601.581.551.4858814.23,83032,36023,73022,03521,80023,625.59834..63845..5680.58734..56893..57764.24,11529,05028,40030,33528,09028,69530,790.5774.72844.1.431.42.67804..699341.42Hyundai Sonata Limited (2.0T)Honda Accord EX-L (V6)Mazda6 s Grand Touring (V6)Ford Fusion SEL (V6, AWD)Subaru Legacy 3.6R LimitedChevrolet Malibu LTZ (V6).66.67891.39901.36.748141.3430,05530,094.71754.1.32.71881.2928,04527,82528,995.67831.20Chrysler 200 Limited (V6)Chevrolet Impala LT (3.6).70521.20.67631.05Managerial Report1. Develop numerical summaries of the data.2. Use regression analysis to develop an estimated regression equation that could be usedto predict the value score given the price of the car.3. Use regression analysis to develop an estimated regression equation that could be usedto predict the value score given the five-year owner costs (cost/mile).4. Use regression analysis to develop an estimated regression equation that could be usedto predict the value score given the road-test score.5. Use regression analysis to develop an estimated regression equation that could be uscoto predict the value score given the predicted-reliability.6. What conclusions can you derive from your analysis?t3353

Accepted Answer

Price
cost/mile
road test
predicted reliability
value score

1
23970
0.59
91
4
1.75

2
21885…