Excel assignment, due 12/2

profileJJJQQQZZZ
Class_Exercise_4_answers.xlsx
Home>Business & Finance homework help>Operations Management homework help>Excel assignment, due 12/2

Sheet4

Odometer Price
Odometer 1
Price -0.8063076039 1

Simple Linear Regression

Car Odometer Price Use scatter plot diagram to demonstrate the relationship between Car Price and Odometer values and obtain the estimated value for the 5th car price using the regression formula
1 37388 14636
2 44758 14122
3 45833 14016
4 30862 15590
5 31705 15568
6 34010 14718
7 45854 14470
8 19057 15690
9 40149 15072
10 40237 14802
11 32359 15190
12 43533 14660
13 32744 15612
14 34470 15610
15 37720 14634
16 41350 14632
17 24469 15740
18 35781 15008 15091.7785
19 48613 14666
20 24188 15410
21 38775 14300
22 45563 14498
23 28676 15550
24 38231 14654
25 36683 14384
26 32517 15088
27 39050 14108
28 45251 14230
29 34384 14820
30 38383 15058
31 32161 15014
32 26561 15746
33 33533 14606
34 41849 14474
35 36668 14766
36 37495 14572
37 25629 15654
38 40099 14966
39 31014 14880
40 42233 14430
41 37407 14210
42 34356 15370
43 30599 15576
44 42485 14416
45 38430 14336
46 40452 14256
47 26030 15500
48 46296 13930
49 34844 14476
50 27379 15526
51 47875 14324
52 35648 14972
53 42501 14514
54 43803 14456
55 43481 14270
56 34279 14534
57 41370 14580
58 34966 14774
59 41427 14182
60 30241 15334
61 47228 14292
62 24464 15530
63 21221 15822
64 35521 15064
65 28006 14998
66 38079 14408
67 42332 14542
68 49223 14014
69 33358 14674
70 37819 14132
71 35975 15210
72 38085 15274
73 35236 14572
74 20962 15640
75 45808 14374
76 36183 14870
77 34399 14998
78 44330 13574
79 32063 15414
80 34641 14914
81 31049 14730
82 38636 14320
83 36468 15102
84 25745 15136
85 39198 14244
86 21535 15708
87 37135 14594
88 42581 14296
89 33023 14832
90 31644 15466
91 35969 14956
92 29051 15380
93 38180 14572
94 31494 15490
95 31372 15050
96 36238 14848
97 34212 14566
98 33190 14518
99 39196 14712
100 36392 14266

Price

37388 44758 45833 30862 31705 34010 45854 19057 40149 40237 32359 43533 32744 34470 37720 41350 24469 35781 48613 24188 38775 45563 28676 38231 36683 32517 39050 45251 34384 38383 32161 26561 33533 41849 36668 37495 25629 40099 31014 42233 37407 34356 30599 42485 38430 40452 26030 46296 34844 27379 47875 35648 42501 43803 43481 34279 41370 34966 41427 30241 47228 24464 21221 35521 28006 38079 42332 49223 33358 37819 35975 38085 35236 20962 45808 36183 34399 44330 32063 34641 31049 38636 36468 25745 39198 21535 37135 42581 33023 31644 35969 29051 38180 31494 31372 36238 34212 33190 39196 36392 14636 14122 14016 15590 15568 14718 14470 15690 15072 14802 15190 14660 15612 15610 14634 14632 15740 15008 14666 15410 14300 14498 15550 14654 14384 15088 14108 14230 14820 15058 15014 15746 14606 14474 14766 14572 15654 14966 14880 14430 14210 15370 15576 14416 14336 14256 15500 13930 14476 15526 14324 14972 14514 14456 14270 14534 14580 14774 14182 15334 14292 15530 15822 15064 14998 14408 14542 14014 14674 14132 15210 15274 14572 15640 14374 14870 14998 13574 15414 14914 14730 14320 15102 15136 14244 15708 14594 14296 14832 15466 14956 15380 14572 15490 15050 14848 14566 14518 14712 14266

Sheet5

Population Density (people/sq mi) Median Household Income ($) Electronic Waste (tons)
Population Density (people/sq mi) 1
Median Household Income ($) 0.9267633827 1
Electronic Waste (tons) 0.9270966594 0.8559914627 1

Correlation

Year Population Density (people/sq mi) Median Household Income ($) Electronic Waste (tons)
2003 92.07 46,967.25 18,108.186
2004 93.30 49,585.35 27,341.564
2005 94.66 50,004.20 35,887.901
2006 96.48 53,522.31 46,126.412
2007 98.06 56,141.32 53,737.509
2008 99.31 57,857.52 62,071.464
2009 100.27 55,458.05 69,246.269
2010 101.19 54,888.45 68,777.911
2011 101.84 55,500.26 69,673.018
2012 102.59 56,443.76 73,851.238
2013 103.56 57,283.94 65,894.784
2014 104.85 60,153.00 67,822.933
2015 106.26 63,439.20 72,103.408
2016 108.10 65,500.29 N/A
2017 110.00 68,550.43 N/A
Run Correlation Analysis and obtain the correlation coefficients

Sheet6

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.927135986
R Square 0.8595811366
Adjusted R Square 0.8314973639
Standard Error 7626.2662974082
Observations 13
ANOVA
df SS MS F Significance F
Regression 2 3560289841.58208 1780144920.79104 30.6077515392 0.0000545918
Residual 10 581599376.389837 58159937.6389837
Total 12 4141889217.97192
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept -332796.86691982 69234.0660893197 -4.8068369477 0.0007162373 -487059.979454419 -178533.754385222 -487059.979454419 -178533.754385222
Population Density (people/sq mi) 3959.5221328184 1317.3269358771 3.005724718 0.013213772 1024.3348062225 6894.7094594143 1024.3348062225 6894.7094594143
Median Household Income ($) -0.0953119198 1.3226184763 -0.0720630488 0.9439726655 -3.0422895332 2.8516656935 -3.0422895332 2.8516656935
RESIDUAL OUTPUT
Observation Predicted Electronic Waste (tons) Residuals
1 27279.7972294057 -9171.6112294057
2 31900.4730318976 -4558.9090318976
3 37245.5013974157 -1357.6005974157
4 44116.5140579672 2009.8975420328
5 50122.9360888308 3614.5727111692
6 54908.7645839918 7162.6994160082
7 58938.6040213346 10307.6651446654
8 62635.6540626937 6142.2573873063
9 65151.0309077358 4521.9874922642
10 68030.7452923122 5820.4928666878
11 71791.402954247 -5896.619120247
12 76625.7307924262 -8802.7977924263
13 81895.4429687423 -9792.0347887423

Multiple Linear Regression

Year Population Density (people/sq mi) Median Household Income ($) Electronic Waste (tons)
2003 92.07 46,967.25 18,108.186
2004 93.30 49,585.35 27,341.564
2005 94.66 50,004.20 35,887.901
2006 96.48 53,522.31 46,126.412
2007 98.06 56,141.32 53,737.509
2008 99.31 57,857.52 62,071.464
2009 100.27 55,458.05 69,246.269
2010 101.19 54,888.45 68,777.911
2011 101.84 55,500.26 69,673.018
2012 102.59 56,443.76 73,851.238
2013 103.56 57,283.94 65,894.784
2014 104.85 60,153.00 67,822.933
2015 106.26 63,439.20 72,103.408
2016 108.10 65,500.29 N/A
2017 110.00 68,550.43 N/A
Predict 2016 and 2017 E-waste amount using Multiple Linear Regression