Data of activities and weekly delivery costs Date Miles DrivenPackages DeliveredDelivery StopsDelivery Cost1/1 2,656 15,700 12,175 $ 5,4791/8 2,085 12,756 9,019 4,6081/15 2,719 17,555 12,358 5,4281/22 2,998 18,547 12,507 5,8261/29 3,655 13,030 12,476 4,8462/5 2,650 18,355 9,910 4,2502/12 2,829 16,719 11,906 5,0732/19 2,056 17,751 10,833 4,6682/26 2,890 19,379 9,883 4,8413/5 2,341 15,542 10,184 4,3633/12 2,269 12,546 9,724 4,8593/19 2,632 16,346 10,735 4,6893/26 2,588 17,832 11,579 5,3324/2 3,254 17,990 12,681 5,7144/9 2,956 19,877 12,940 5,9144/16 2,368 15,917 10,495 4,6154/23 2,879 12,970 12,021 5,1154/30 2,142 17,467 10,756 4,4145/7 2,671 13,410 11,515 5,3165/14 3,165 15,245 11,694 5,3565/21 2,742 19,088 12,610 5,8885/28 2,383 16,454 9,983 4,7276/4 2,111 13,400 10,659 4,7566/11 2,358 16,191 11,723 4,9116/18 2,563 12,693 9,177 4,3636/25 2,472 16,145 10,213 4,8917/2 2,345 12,243 9,025 4,4007/9 2,612 15,280 11,086 5,0107/16 2,655 15,243 12,161 5,4827/23 2,625 14,700 10,140 4,5567/30 2,345 18,145 12,764 5,6838/6 2,659 16,090 12,445 5,6408/13 2,318 14,770 12,159 4,7768/20 3,456 18,878 12,097 4,8288/27 2,987 18,719 12,134 5,7309/3 2,436 15,452 10,727 4,8549/10 2,261 12,565 9,823 4,5009/17 2,492 16,432 12,038 5,4229/24 2,659 16,365 12,560 5,47610/1 1,951 17,093 12,394 4,83710/8 2,445 16,296 11,030 4,92510/15 2,922 16,583 11,892 5,56010/22 2,890 18,556 11,446 5,34110/29 2,189 16,624 12,241 4,90411/5 2,520 18,357 11,587 4,71011/12 2,377 14,729 10,769 5,22211/19 2,143 15,341 10,111 4,38011/26 3,145 15,607 11,526 4,94512/3 2,872 18,360 9,734 4,95012/10 2,146 16,940 11,257 4,56812/17 2,960 15,688 10,434 5,10212/24 2,185 18,589 11,295 4,53212/31 2,248 18,707 9,888 4,749 Instructions:There are two parts to this problem. Use Excel to perform the following:a. Generate a linear regression for each cost driver. Include a line fit plot.b. Identify the best cost driver for delivery costs. Explain your choice. ProblemZip Delivery is a small shipping company that delivers packages as a third partycontractor for Amazon. The cost accountant if selecting the 'best' cost driver touse to allocate overhead costs. The three possible drivers she has selected arenumber of miles driven on the routes, the number of packages delivered, andthe number of delivery stops. While one package is delivered to many of thedelivery stops, some stops have multiple packages, often requiring multiple tripsfrom the delivery truck to the customers' home or business location. Theaccountant has assembled and provided data containing the weekly deliverycost, miles driven, packages delivered, and number of delivery stops.

Question

Data of activities and weekly delivery costs Date Miles DrivenPackages DeliveredDelivery StopsDelivery Cost1/1             2,656               15,700                 12,175 $        5,4791/8             2,085               12,756                   9,019           4,6081/15             2,719               17,555                 12,358           5,4281/22             2,998               18,547                 12,507           5,8261/29             3,655               13,030                 12,476           4,8462/5             2,650               18,355                   9,910           4,2502/12             2,829               16,719                 11,906           5,0732/19             2,056               17,751                 10,833           4,6682/26             2,890               19,379                   9,883           4,8413/5             2,341               15,542                 10,184           4,3633/12             2,269               12,546                   9,724           4,8593/19             2,632               16,346                 10,735           4,6893/26             2,588               17,832                 11,579           5,3324/2             3,254               17,990                 12,681           5,7144/9             2,956               19,877                 12,940           5,9144/16             2,368               15,917                 10,495           4,6154/23             2,879               12,970                 12,021           5,1154/30             2,142               17,467                 10,756           4,4145/7             2,671               13,410                 11,515           5,3165/14             3,165               15,245                 11,694           5,3565/21             2,742               19,088                 12,610           5,8885/28             2,383               16,454                   9,983           4,7276/4             2,111               13,400                 10,659           4,7566/11             2,358               16,191                 11,723           4,9116/18             2,563               12,693                   9,177           4,3636/25             2,472               16,145                 10,213           4,8917/2             2,345               12,243                   9,025           4,4007/9             2,612               15,280                 11,086           5,0107/16             2,655               15,243                 12,161           5,4827/23             2,625               14,700                 10,140           4,5567/30             2,345               18,145                 12,764           5,6838/6             2,659               16,090                 12,445           5,6408/13             2,318               14,770                 12,159           4,7768/20             3,456               18,878                 12,097           4,8288/27             2,987               18,719                 12,134           5,7309/3             2,436               15,452                 10,727           4,8549/10             2,261               12,565                   9,823           4,5009/17             2,492               16,432                 12,038           5,4229/24             2,659               16,365                 12,560           5,47610/1             1,951               17,093                 12,394           4,83710/8             2,445               16,296                 11,030           4,92510/15             2,922               16,583                 11,892           5,56010/22             2,890               18,556                 11,446           5,34110/29             2,189               16,624                 12,241           4,90411/5             2,520               18,357                 11,587           4,71011/12             2,377               14,729                 10,769           5,22211/19             2,143               15,341                 10,111           4,38011/26             3,145               15,607                 11,526           4,94512/3             2,872               18,360                   9,734           4,95012/10             2,146               16,940                 11,257           4,56812/17             2,960               15,688                 10,434           5,10212/24             2,185               18,589                 11,295           4,53212/31             2,248               18,707                   9,888           4,749 Instructions:There are two parts to this problem. Use Excel to perform the following:a. Generate a linear regression for each cost driver. Include a line fit plot.b. Identify the best cost driver for delivery costs. Explain your choice. ProblemZip Delivery is a small shipping company that delivers packages as a third partycontractor for Amazon. The cost accountant if selecting the 'best' cost driver touse to allocate overhead costs. The three possible drivers she has selected arenumber of miles driven on the routes, the number of packages delivered, andthe number of delivery stops. While one package is delivered to many of thedelivery stops, some stops have multiple packages, often requiring multiple tripsfrom the delivery truck to the customers' home or business location. Theaccountant has assembled and provided data containing the weekly deliverycost, miles driven, packages delivered, and number of delivery stops.

Accepted Answer

Linear Regression:
In Excel, Under the Data analysis tab, regression operations are performed for…

Data of activities and weekly delivery costs
Date	Miles Driven	Packages Delivered	Delivery Stops	Delivery Cost
1/1	2,656	15,700	12,175	$ 5,479
1/8	2,085	12,756	9,019	4,608
1/15	2,719	17,555	12,358	5,428
1/22	2,998	18,547	12,507	5,826
1/29	3,655	13,030	12,476	4,846
2/5	2,650	18,355	9,910	4,250
2/12	2,829	16,719	11,906	5,073
2/19	2,056	17,751	10,833	4,668
2/26	2,890	19,379	9,883	4,841
3/5	2,341	15,542	10,184	4,363
3/12	2,269	12,546	9,724	4,859
3/19	2,632	16,346	10,735	4,689
3/26	2,588	17,832	11,579	5,332
4/2	3,254	17,990	12,681	5,714
4/9	2,956	19,877	12,940	5,914
4/16	2,368	15,917	10,495	4,615
4/23	2,879	12,970	12,021	5,115
4/30	2,142	17,467	10,756	4,414
5/7	2,671	13,410	11,515	5,316
5/14	3,165	15,245	11,694	5,356
5/21	2,742	19,088	12,610	5,888
5/28	2,383	16,454	9,983	4,727
6/4	2,111	13,400	10,659	4,756
6/11	2,358	16,191	11,723	4,911
6/18	2,563	12,693	9,177	4,363
6/25	2,472	16,145	10,213	4,891
7/2	2,345	12,243	9,025	4,400
7/9	2,612	15,280	11,086	5,010
7/16	2,655	15,243	12,161	5,482
7/23	2,625	14,700	10,140	4,556
7/30	2,345	18,145	12,764	5,683
8/6	2,659	16,090	12,445	5,640
8/13	2,318	14,770	12,159	4,776
8/20	3,456	18,878	12,097	4,828
8/27	2,987	18,719	12,134	5,730
9/3	2,436	15,452	10,727	4,854
9/10	2,261	12,565	9,823	4,500
9/17	2,492	16,432	12,038	5,422
9/24	2,659	16,365	12,560	5,476
10/1	1,951	17,093	12,394	4,837
10/8	2,445	16,296	11,030	4,925
10/15	2,922	16,583	11,892	5,560
10/22	2,890	18,556	11,446	5,341
10/29	2,189	16,624	12,241	4,904
11/5	2,520	18,357	11,587	4,710
11/12	2,377	14,729	10,769	5,222
11/19	2,143	15,341	10,111	4,380
11/26	3,145	15,607	11,526	4,945
12/3	2,872	18,360	9,734	4,950
12/10	2,146	16,940	11,257	4,568
12/17	2,960	15,688	10,434	5,102
12/24	2,185	18,589	11,295	4,532
12/31	2,248	18,707	9,888	4,749

There are two parts to this problem. Use Excel to perform the following: a. Generate a linear regression for each cost driver. Include a line fit plot. b. Identify the best cost driver for delivery costs. Explain your choice.