Plyler Plastics Company produces a variety of custom plasticsproducts for a worldwide clientele. The company's cost accountingmanager, Martha Johns, is beginning to implement an activity-basedcosting system and has gathered data on the quality inspectionsactivity. She is unsure what the most appropriate driver is forthis activity cost pool, but she is considering number of unitsproduced, number of batches produced, machine hours, and directlabor hours. She has gathered weekly information for the past twoyears and has asked you to help her determine which activity driverto select.
Required
(a)Using the activity cost pool and activity driver data,prepare a scatterplot for each potential activity driver. What doyou notice about the appropriateness of each as the selected driverfor assigning inspections costs to products under the newactivity-based costing system?
(b)Using Excel's CORREL formula, determine the correlationbetween each activity driver level and the inspections cost.
(c)Using Excel's RSQ formula, determine how much of thevariation in activity costs each activity driver explains.
(d)Based on your analysis, which activity driver do yourecommend? Why?
(e)Assuming the past two years represent the expected level ofcosts and activity for the coming year, what activity cost rate forquality inspections should be used to assign costs to products inthe coming year?
Week Quality Insepctions Cost Units Produced Batches Machine Hours DirectLabor Hours
1$ 1,626 83,481 57 16,353 10,518
2$ 2,044 105,939 98 25,598 13,136
3$ 1,736 138,215 84 25,416 17,125
4$ 2,401 82,028 123 27,485 10,528
5$ 2,280 89,964 148 27,776 11,299
6$ 1,267 130,227 75 19,183 16,180
7$ 2,391 84,843 166 21,745 10,746
8$ 2,418 93,504 161 22,909 11,748
9$ 774 95,214 56 26,631 11,685
10$ 2,332 117,356 120 21,225 14,621
11$ 2,572 112,073 131 21,317 13,986
12$ 1,918 82,702 139 27,087 10,490
13$ 1,819 148,925 86 26,939 18,323
14$ 1,326 125,663 58 17,269 15,574
15$ 1,648 118,270 117 27,161 14,573
16$ 1,882 115,109 143 18,017 14,447
17$ 2,129 90,066 112 27,124 11,307
18$ 1,962 130,190 150 12,683 16,184
19$ 1,751 131,078 99 17,705 16,107
20$ 1,061 100,707 64 19,261 12,374
21$ 762 148,448 54 18,166 18,092
22$ 2,166 118,074 104 25,257 14,553
23$ 1,571 138,106 112 19,470 17,068
24$ 1,691 108,828 107 17,036 13,649
25$ 2,647 131,576 134 19,045 16,498
26$ 1,631 123,399 60 20,018 15,375
27$ 1,215 89,104 61 16,209 11,106
28$ 1,027 106,660 63 18,393 13,085
29$ 644 87,025 51 18,437 10,821
30$ 1,958 133,661 93 10,925 16,552
31$ 1,554 128,015 120 17,937 15,828
32$ 1,401 143,200 86 18,559 17,726
33$ 740 128,785 51 10,099 15,893
34$ 2,860 142,728 165 20,995 17,733
35$ 2,224 96,737 132 22,561 12,100
36$ 1,371 94,023 67 23,730 11,717
37$ 920 82,081 72 17,493 10,257
38$ 1,395 149,748 101 16,047 18,412
39$ 2,474 100,344 169 10,768 12,713
40$ 2,268 101,425 131 26,647 12,854
41$ 2,013 89,539 119 14,015 11,135
42$ 2,356 117,345 156 18,850 14,494
43$ 2,257 86,370 168 14,751 10,961
44$ 2,422 98,056 143 19,805 12,276
45$ 1,932 83,271 109 11,061 10,502
46$ 2,590 107,880 159 26,449 13,586
47$ 1,701 114,017 117 16,632 14,023
48$ 2,040 141,278 122 26,212 17,597
49$ 1,908 131,427 102 23,126 16,291
50$ 2,152 87,576 159 11,435 11,155
51$ 1,705 105,897 72 14,396 13,084
52$ 1,091 137,863 52 20,149 17,051
53$ 1,285 105,966 101 27,544 13,152
54$ 1,749 113,491 68 18,207 14,036
55$ 1,899 135,404 93 18,511 16,630
56$ 2,574 118,977 155 13,952 14,758
57$ 1,460 108,169 67 12,894 13,451
58$ 1,236 139,303 76 14,862 17,082
59$ 1,451 141,178 55 14,084 17,372
60$ 2,052 135,222 108 22,784 16,865
61$ 1,246 117,651 55 15,647 14,409
62$ 1,546 90,802 118 11,623 11,485
63$ 2,338 145,299 156 12,498 18,081
64$ 1,178 143,988 62 26,536 17,798
65$ 1,664 139,198 109 25,516 17,157
66$ 1,042 138,232 66 20,416 17,061
67$ 1,667 149,345 79 15,158 18,490
68$ 1,085 97,291 58 15,769 12,127
69$ 1,690 100,025 116 10,179 12,600
70$ 2,148 92,106 114 27,464 11,484
71$ 1,724 144,295 92 23,986 17,806
72$ 2,002 126,032 116 14,026 15,580
73$ 2,903 83,548 155 27,859 10,735
74$ 2,258 95,736 148 26,346 12,087
75$ 1,496 100,358 72 13,726 12,564
76$ 1,940 104,231 107 14,854 12,927
77$ 1,626 80,340 77 18,798 10,234
78$ 2,129 137,565 110 12,692 16,882
79$ 1,749 82,523 107 24,803 10,498
80$ 1,810 97,869 73 23,296 12,276
81$ 2,003 90,266 110 15,686 11,481
82$ 2,213 119,616 99 27,074 14,844
83$ 1,453 115,210 57 13,688 14,113
84$ 1,311 104,454 102 23,384 13,033
85$ 1,037 88,748 65 19,517 10,893
86$ 1,134 139,251 60 12,888 17,237
87$ 2,371 97,479 124 17,916 12,142
88$ 2,428 128,002 115 18,031 15,953
89$ 2,467 111,798 169 22,302 13,962
90$ 2,638 106,219 164 21,894 13,343
91$ 2,400 116,886 125 11,629 14,705
92$ 2,107 142,368 91 19,878 17,618
93$ 1,139 112,606 52 10,829 13,777
94$ 1,306 92,930 96 22,443 11,710
95$ 2,885 85,927 152 14,086 10,972
96$ 1,100 128,001 87 21,742 15,807
97$ 2,131 138,025 148 25,300 16,943
98$ 1,811 105,397 80 25,729 13,039
99$ 1,928 88,465 127 22,027 11,082
100$ 1,276 118,707 67 25,246 14,510
101$ 1,527 129,211 99 23,519 15,988
102$ 2,176 144,143 113 20,821 17,874
103$ 1,564 80,889 118 22,465 10,197
104$ 2,142 94,066 165 12,640 11,702
