Complete the following Activities questions in the spreadsheet Chapter 14 Data Analytics 1 Conduct Multiple Regression Analysis using the Data Analysis Toolpak a Dependent Variable(Y) Maintenance Cost b Independent Variables(X) i EngineType(0 diesel) x1 ii Age x2 iii Odometer Miles x3 iv Miles x4 2 Type out the complete Regression equation using the calculated coefficients in the format as described in the text a a b1x1 b2x2 b3x3 b4x4 3 Predict the maintenance cost of a bus with the following characteristics a Gasoline engine b 8 years old c Odometer 85,000 miles d Miles since last maintenance 12,000 ID Manufacturer Engine Type Capacity Maintenance cost Engine Type (0 diesel) Age Odometer Miles Miles 10 Keiser Gasoline 14 4646 1 5 54375 11973 396 Thompson Diesel 14 1072 0 2 21858 11969 122 Bluebird Gasoline 55 9394 1 10 116580 11967 751 Keiser Diesel 14 1078 0 2 22444 11948 279 Bluebird Diesel 55 1008 0 2 22672 11925 500 Bluebird Gasoline 55 5329 1 5 50765 11922 520 Bluebird Diesel 55 4794 0 10 119130 11896 759 Keiser Diesel 55 3952 0 8 87872 11883 714 Bluebird Diesel 42 3742 0 7 73703 11837 875 Bluebird Diesel 55 4376 0 9 97947 11814 600 Bluebird Diesel 55 4832 0 10 119860 11800 953 Bluebird Diesel 55 5160 0 10 117700 11798 101 Bluebird Diesel 55 1955 0 4 41096 11789 358 Bluebird Diesel 55 2775 0 6 70086 11782 29 Bluebird Gasoline 55 5352 1 6 69438 11781 365 Keiser Diesel 55 3065 0 6 63384 11778 162 Keiser Gasoline 55 3143 1 3 31266 11758 686 Bluebird Diesel 55 1569 0 3 34674 11757 370 Keiser Gasoline 55 7766 1 8 86528 11707 887 Bluebird Diesel 55 3743 0 8 93672 11704 464 Bluebird Gasoline 55 2540 1 3 34530 11698 948 Keiser Diesel 42 4342 0 9 97956 11691 678 Keiser Diesel 55 3361 0 7 75229 11668 481 Keiser Gasoline 6 3097 1 3 34362 11662 43 Bluebird Gasoline 55 8263 1 9 102969 11615 704 Bluebird Diesel 55 4218 0 8 83424 11610 814 Bluebird Diesel 55 2028 0 4 40824 11576 39 Bluebird Gasoline 55 5821 1 6 69444 11533 699 Bluebird Gasoline 55 9069 1 9 98307 11518 75 Bluebird Diesel 55 3011 0 6 71970 11462 693 Keiser Gasoline 55 9193 1 9 101889 11461 989 Keiser Diesel 55 4795 0 9 106605 11418 982 Bluebird Diesel 55 505 0 1 10276 11359 321 Bluebird Diesel 42 2732 0 6 70122 11358 724 Keiser Diesel 42 3754 0 8 91968 11344 732 Keiser Diesel 42 4640 0 9 101196 11342 880 Keiser Gasoline 55 8410 1 9 97065 11336 193 Thompson Diesel 14 5922 0 11 128711 11248 884 Bluebird Diesel 55 4364 0 9 92457 11231 57 Bluebird Diesel 55 3190 0 7 79240 11222 731 Bluebird Diesel 42 3213 0 6 68526 11168 61 Keiser Diesel 55 4139 0 9 103536 11148 135 Bluebird Diesel 55 3560 0 7 76426 11127 833 Thompson Diesel 14 3920 0 8 90968 11112 671 Thompson Gasoline 14 6733 1 8 89792 11100 692 Bluebird Diesel 55 3770 0 8 93248 11048 200 Bluebird Diesel 55 5168 0 10 103700 11018 754 Keiser Diesel 14 7380 0 14 146860 11003 540 Bluebird Gasoline 55 3656 1 4 45284 10945 660 Bluebird Gasoline 55 6213 1 6 64434 10911 353 Keiser Gasoline 55 4279 1 4 45744 10902 482 Bluebird Gasoline 55 10575 1 10 116534 10802 398 Thompson Diesel 6 4752 0 9 95922 10802 984 Bluebird Diesel 55 3809 0 8 87664 10760 977 Bluebird Diesel 55 3769 0 7 79422 10759 705 Keiser Diesel 42 2152 0 4 47596 10755 767 Keiser Diesel 55 2985 0 6 71538 10726 326 Bluebird Diesel 55 4563 0 9 107343 10724 120 Keiser Diesel 42 4723 0 10 110320 10674 554 Bluebird Diesel 42 1826 0 4 44604 10662 695 Bluebird Diesel 55 1061 0 2 23152 10633 9 Keiser Gasoline 55 3527 1 4 46848 10591 861 Bluebird Gasoline 55 9669 1 10 106040 10551 603 Keiser Diesel 14 2116 0 4 44384 10518 156 Thompson Diesel 14 6212 0 12 140460 10473 427 Keiser Gasoline 55 6927 1 7 73423 10355 883 Bluebird Gasoline 55 1881 1 2 20742 10344 168 Thompson Gasoline 14 7004 1 7 83006 10315 954 Bluebird Diesel 42 5284 0 10 101000 10235 768 Bluebird Diesel 42 3173 0 7 71778 10227 490 Bluebird Gasoline 55 10133 1 10 106240 10210 725 Bluebird Diesel 55 2356 0 5 57065 10209 45 Keiser Diesel 55 3124 0 6 60102 10167 38 Keiser Gasoline 14 5976 1 6 61662 10140 314 Thompson Diesel 6 5408 0 11 128117 10128 507 Bluebird Diesel 55 3690 0 7 72849 10095 40 Bluebird Gasoline 55 9573 1 10 118470 10081 918 Bluebird Diesel 55 2470 0 5 53620 10075 387 Bluebird Gasoline 55 6863 1 8 89960 10055 418 Bluebird Diesel 55 4513 0 9 104715 10000 Type out the complete Regression equation using the calculated coefficients in the format as described in the text Round coefficients to 2 decimal places a b 1 x 1 b 2 x 2 b 3 x 3 b 4 x 4 Using the coefficents from the regression output, predict the following using the values from the instructions Component Coefficient(a b) Prediction Value(x) Cacluation Intercept Engine Type (0 diesel) Age Odometer Miles Miles since last maintenance Predicted annual maintenance cost

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 11, 2024

Complete the following Activities/questions in the spreadsheet: Chapter 14 Data Analytics 1. Conduct Multiple Regression Analysis using the Data Analysis Toolpak a. Dependent Variable(Y): Maintenance

Complete the following Activities/questions in the spreadsheet: Chapter 14 Data Analytics 1.

Conduct Multiple Regression Analysis using the Data Analysis Toolpak a. Dependent Variable(Y): Maintenance Cost b. Independent Variables(X): i. EngineType(0=diesel) - x1 ii. Age - x2 iii. Odometer Miles - x3 iv. Miles - x4 2.

Type out the complete Regression equation using the calculated coefficients in the format as described in the text a. = a + b1x1 + b2x2 + b3x3 + b4x4 3.

Predict the maintenance cost of a bus with the following characteristics:

a. Gasoline engine

b. 8 years old

c. Odometer = 85,000 miles

d. Miles since last maintenance = 12,000

ID	Manufacturer	Engine Type	Capacity	Maintenance cost	Engine Type (0=diesel)	Age	Odometer Miles	Miles
10	Keiser	Gasoline	14	4646	1	5	54375	11973
396	Thompson	Diesel	14	1072	0	2	21858	11969
122	Bluebird	Gasoline	55	9394	1	10	116580	11967
751	Keiser	Diesel	14	1078	0	2	22444	11948
279	Bluebird	Diesel	55	1008	0	2	22672	11925
500	Bluebird	Gasoline	55	5329	1	5	50765	11922
520	Bluebird	Diesel	55	4794	0	10	119130	11896
759	Keiser	Diesel	55	3952	0	8	87872	11883
714	Bluebird	Diesel	42	3742	0	7	73703	11837
875	Bluebird	Diesel	55	4376	0	9	97947	11814
600	Bluebird	Diesel	55	4832	0	10	119860	11800
953	Bluebird	Diesel	55	5160	0	10	117700	11798
101	Bluebird	Diesel	55	1955	0	4	41096	11789
358	Bluebird	Diesel	55	2775	0	6	70086	11782
29	Bluebird	Gasoline	55	5352	1	6	69438	11781
365	Keiser	Diesel	55	3065	0	6	63384	11778
162	Keiser	Gasoline	55	3143	1	3	31266	11758
686	Bluebird	Diesel	55	1569	0	3	34674	11757
370	Keiser	Gasoline	55	7766	1	8	86528	11707
887	Bluebird	Diesel	55	3743	0	8	93672	11704
464	Bluebird	Gasoline	55	2540	1	3	34530	11698
948	Keiser	Diesel	42	4342	0	9	97956	11691
678	Keiser	Diesel	55	3361	0	7	75229	11668
481	Keiser	Gasoline	6	3097	1	3	34362	11662
43	Bluebird	Gasoline	55	8263	1	9	102969	11615
704	Bluebird	Diesel	55	4218	0	8	83424	11610
814	Bluebird	Diesel	55	2028	0	4	40824	11576
39	Bluebird	Gasoline	55	5821	1	6	69444	11533
699	Bluebird	Gasoline	55	9069	1	9	98307	11518
75	Bluebird	Diesel	55	3011	0	6	71970	11462
693	Keiser	Gasoline	55	9193	1	9	101889	11461
989	Keiser	Diesel	55	4795	0	9	106605	11418
982	Bluebird	Diesel	55	505	0	1	10276	11359
321	Bluebird	Diesel	42	2732	0	6	70122	11358
724	Keiser	Diesel	42	3754	0	8	91968	11344
732	Keiser	Diesel	42	4640	0	9	101196	11342
880	Keiser	Gasoline	55	8410	1	9	97065	11336
193	Thompson	Diesel	14	5922	0	11	128711	11248
884	Bluebird	Diesel	55	4364	0	9	92457	11231
57	Bluebird	Diesel	55	3190	0	7	79240	11222
731	Bluebird	Diesel	42	3213	0	6	68526	11168
61	Keiser	Diesel	55	4139	0	9	103536	11148
135	Bluebird	Diesel	55	3560	0	7	76426	11127
833	Thompson	Diesel	14	3920	0	8	90968	11112
671	Thompson	Gasoline	14	6733	1	8	89792	11100
692	Bluebird	Diesel	55	3770	0	8	93248	11048
200	Bluebird	Diesel	55	5168	0	10	103700	11018
754	Keiser	Diesel	14	7380	0	14	146860	11003
540	Bluebird	Gasoline	55	3656	1	4	45284	10945
660	Bluebird	Gasoline	55	6213	1	6	64434	10911
353	Keiser	Gasoline	55	4279	1	4	45744	10902
482	Bluebird	Gasoline	55	10575	1	10	116534	10802
398	Thompson	Diesel	6	4752	0	9	95922	10802
984	Bluebird	Diesel	55	3809	0	8	87664	10760
977	Bluebird	Diesel	55	3769	0	7	79422	10759
705	Keiser	Diesel	42	2152	0	4	47596	10755
767	Keiser	Diesel	55	2985	0	6	71538	10726
326	Bluebird	Diesel	55	4563	0	9	107343	10724
120	Keiser	Diesel	42	4723	0	10	110320	10674
554	Bluebird	Diesel	42	1826	0	4	44604	10662
695	Bluebird	Diesel	55	1061	0	2	23152	10633
9	Keiser	Gasoline	55	3527	1	4	46848	10591
861	Bluebird	Gasoline	55	9669	1	10	106040	10551
603	Keiser	Diesel	14	2116	0	4	44384	10518
156	Thompson	Diesel	14	6212	0	12	140460	10473
427	Keiser	Gasoline	55	6927	1	7	73423	10355
883	Bluebird	Gasoline	55	1881	1	2	20742	10344
168	Thompson	Gasoline	14	7004	1	7	83006	10315
954	Bluebird	Diesel	42	5284	0	10	101000	10235
768	Bluebird	Diesel	42	3173	0	7	71778	10227
490	Bluebird	Gasoline	55	10133	1	10	106240	10210
725	Bluebird	Diesel	55	2356	0	5	57065	10209
45	Keiser	Diesel	55	3124	0	6	60102	10167
38	Keiser	Gasoline	14	5976	1	6	61662	10140
314	Thompson	Diesel	6	5408	0	11	128117	10128
507	Bluebird	Diesel	55	3690	0	7	72849	10095
40	Bluebird	Gasoline	55	9573	1	10	118470	10081
918	Bluebird	Diesel	55	2470	0	5	53620	10075
387	Bluebird	Gasoline	55	6863	1	8	89960	10055
418	Bluebird	Diesel	55	4513	0	9	104715	10000

Type out the complete Regression equation using the calculated coefficients in the format as described in the text.
Round coefficients to 2 decimal places.

= a + b₁x₁ + b₂x₂ + b₃x₃ + b₄x₄

=



Using the coefficents from the regression output, predict the following using the values from the instructions

Component	Coefficient(a/b)	Prediction Value(x)	Cacluation
Intercept
Engine Type (0 = diesel)
Age
Odometer Miles
Miles since last maintenance
		Predicted annual maintenance cost