Question

Use excel to answer, please

thanks

4. A survey is conducted to determine whether the age of car influences the annual maintenance cost. A sample of 10 cars is selected and the data is shown below. Age of car (months) x Annual Maintenance Cost ($) y 120 115 135 290 275 300 350 475 500 550 a. Develop a scatter chart for these data with age of cars as the independent variable. What does the scatter chart indicate about the relationship between age of a car and the annual maintenance cost? b. Use the data to develop an estimated regression equation that could be used to predict the annual maintenance cost given the age of the car. What is the estimated regression model?

Answer 1

a.

We will create scatter diagram as shown below in excel screenshot

Select data area and then go to Insert -> Scatter

5.si Home Insert Page Layout Book 1 - Excel Tell me what you want to do... File Formulas Data Review View & Share Puneet Sabharwal - IT Equation L 4 Store My Add-ins - Liew III. Y Mh. - IN Tin Recommended PivotChart 3D Line Column Win/ Charts Man - Loss Char Scatter Sparklines PivotTable Recommended Table Pictures Online Pivot Tables Pictures Tables illustrations e A Slicer Timeline Hyperlink Text Header Box & Footer Add-ins Filters Links Text Symbols Chart 2 - X fi J K L M N O P A 2 Age of car- months (x) Annual Maintenance - Cost $(y)] 120 115 Scatter Use this chart type to: - Compare at least two sets of values or pairs of data. Bubble - Show relationships between sets of values uw 135 00 290 275 300 Use it when: - The data represents separate Mol measurements. 350 475 500 15 550 0 2 4 6 8 10 12 14 16 Sheet1 Sheet2 Sheet3 Sheet4 Sheet5 Sheet6 Ready Average: 160.15 Count: 22 Sum: 3203 @ -—_ + O Dope here to search Type here to search + 100% 9:03 AM 3/18/2020 деево P3

Puneet Sabharwal & Share SSS Book 1 - Excel File Home Insert Page Layout Formulas Data Review View Tell me what you want to do... X Cut Calibri -11 AA === >> Wrap Text Wrap Text General General D E Paste Copy Format Painter BTU. U A E E Merge & Center - $ -% O..Conditional Format as Cell Formatting Table Styles Clipboard Font Alignment Number Styles AutoSum - A Fill 2Y O Insert Delete Format - Cells Sort & Find & Filter - Select - Editing C D E F G H I J K L M N O P A 2 Age of car-months (x) Annual Maintenance - Cost $(y) 120 115 135 290 Survey results 275 300 13 350 475 500 550 14 Annual Maintenance cost $ 0 2 4 6 8 10 Age of car - month 12 14 16 Sheet1 Sheet2 Sheet3 Sheet4 Sheet5 Sheet6 Ready E @ E -—_ F + 100% 9:05 AM O Type here to search Type here to search деево P3

Looking at the scatter chart, we can clearly see that there is direct relationship between age of a car and annual maintenance cost which means that annual maintenance cost $ will increase with the age of the car.

b.

To come up with regression model, we will add regression equation to the chart as shown below.

Add Trendline

Chart Tools Book 1 - Excel File Home Insert Page Layout Formulas Data Review View Design Format Tell me what you want to do... X Cut 11 AA == Wrap Text General Paste Copy Format Painter BTU U A . Merge & Center - $ % ) . Conditional Format as Cell Formatting Table Styles Clipboard Font Alignment Number Styles Chart 5 - X V for C D E F G H I J 2Y O X Insert Delete Format lete Format - Cells Puneet Sabharwal & Share AutoSum - A O Clear Sort & Find & Filter - Select - Editing K L M N O P A 2 Age of car-months (x) Annual Maintenance - Cost $(y) 120 115 135 290 CHART ELEMENTS Survey results Axes 275 300 13 350 475 500 550 Axis Titles Chart Title Data Labels Error Bars Gridlines Legend Trendline 14 Annual Maintenance cost $ ► 0 2 4 6 8 10 Age of car-month 12 14 16 Sheet1 Sheet2 Sheet3 Sheet4 Sheet5 Sheet6 Ready E @ E -—_ F Type here to search Type here to search деево + 100% 9:08 AM 3/18/2020 P3 "" -

Click on More options

Chart Tools Book 1 - Excel Tell me what you want to do... File Insert Page Layout Formulas Data Review View Design Format Home X Cut == Y O Wrap Text General E Merge & Center - $ -% Copy Paste 11 AA inter BIU. I A Font X V for Puneet Sabharwal & Share AutoSum - A O Clear Sort & Find & Filter - Select - Editing DX Insert Delete Format lete Format - 6.00 Conditional Format as Cell Formatting Table Styles Styles Alignment Number Cells Clipboard Chart 5 - C D E F G H I J K L M N O P A 2 Age of car-months (x) Annual Maintenance - Cost $(y) 120 115 135 290 CHART ELEMENTS Survey results Axes 275 300 13 350 475 500 550 Axis Titles Chart Title Data Labels Error Bars Gridlines Legend Trendline 14 Annual Maintenance cost $ Linear Exponential Linear Forecast Two Period Moving Average More Options... 0 2 4 6 8 10 Age of car-month 12 14 16 Sheet1 Sheet2 Sheet3 Sheet4 Sheet5 Sheet6 Ready @ E -—_ + 100% 9:08 AM Type here to search Type here to search деево P3

Chart Tools Book 1 - Excel Tell me what you want to do... File Insert Page Layout Formulas Data Review View Design Format Home X Cut Puneet Sabharwal & Share O == AutoSum - A ZY O Wrap Text General E Merge & Center - $ -% Paste Copy 11 AA inter BIU. I A Font X V for Clear DX Insert Delete Format at - Cells 6.00 Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter - Select - Editing Alignment Number Clipboard Chart 5 - C D E F G H I J K A Format Trendline Trendline Options 2 Age of car-months (x) Annual Maintenance - Cost $(y) 120 115 135 290 Survey results Polynomial Order 2 275 300 y-38.344x45,595 13 350 475 500 550 14 Annual Maintenance cost $ Power Moving Period ? Average Trendline Name Linear (Annual Automatic Maintenance Cost $ {y} Custom Forecast Forward 0.0 periods Backward 0.0 periods Set Intercept 0.0 Display Equation on chart Display R-squared value on chart 0 2 4 6 8 10 Age of car-month 12 14 16 Sheet1 Sheet2 Sheet3 Sheet4 Sheet5 Sheet6 + Ready @ E -—_ + 100% 9:09 AM Type here to search Type here to search де есе P3

Estimated regression model is Y = 38.344 X - 45.595

Please give thumbs up if you like my answer. Thank you!