Question

Plyler Plastics Company produces a variety of custom plastics products for a worldwide clientele. The company's cost accounting manager, Martha Johns, is beginning to implement an activity-based costing system and has gathered data on the quality inspections activity. She is unsure what the most appropriate driver is for this activity cost pool, but she is considering number of units produced, number of batches produced, machine hours, and direct labor hours. She has gathered weekly information for the past two years and has asked you to help her determine which activity driver to select.

Required

(a)Using the activity cost pool and activity driver data, prepare a scatterplot for each potential activity driver. What do you notice about the appropriateness of each as the selected driver for assigning inspections costs to products under the new activity-based costing system?

(b)Using Excel's CORREL formula, determine the correlation between each activity driver level and the inspections cost.

(c)Using Excel's RSQ formula, determine how much of the variation in activity costs each activity driver explains.

(d)Based on your analysis, which activity driver do you recommend? Why?

(e)Assuming the past two years represent the expected level of costs and activity for the coming year, what activity cost rate for quality inspections should be used to assign costs to products in the coming year?

Week Quality Insepctions Cost Units Produced    Batches    Machine Hours    Direct Labor 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

Answer 1

A)

The scatter plots for the other three cost driver except batches are clustered and are not true representative of the change in cost w.r.t the change in Cost driver. However the Scatter chart of Batches cost driver is inclining that represents positive elasticity in the cost driver with change in the cost hence Batches must be the cost driver.

B) & C)

D) The correlation and variation nearest to 1 should be choosen as cost driver, here batches are true representative of the data since it has correlation and variation nearest to 1 whereas the others anre negative in some cases or nearest to zero.

E) Using the growth formulae in excel we can find out that the following activity will take place.

Week

Quality Insepctions Cost

Units Produced

Batches

Machine Hrs

Direct Labour Hrs

105	1,671	1,10,918	97	19,906	13,815
106	1,676	1,12,155	99	19,991	13,963
107	1,665	1,12,366	99	19,733	13,991
108	1,665	1,11,431	100	19,486	13,877
109	1,645	1,12,748	99	19,187	14,025
110	1,628	1,13,671	98	18,886	14,135
111	1,648	1,12,979	99	18,858	14,050
112	1,628	1,14,162	97	18,740	14,188
113	1,608	1,14,923	95	18,586	14,278
114	1,658	1,15,606	97	18,328	14,371
115	1,640	1,15,361	96	18,233	14,340
116	1,615	1,15,324	95	18,135	14,334
117	1,609	1,16,646	94	17,873	14,488
118	1,606	1,15,340	95	17,620	14,332
119	1,622	1,14,804	97	17,670	14,268
120	1,625	1,14,543	96	17,414	14,241
121	1,620	1,14,404	95	17,435	14,220
122	1,607	1,15,351	94	17,183	14,333
123	1,599	1,14,667	93	17,438	14,249
124	1,598	1,13,962	93	17,469	14,168
125	1,628	1,14,420	94	17,442	14,232
126	1,680	1,13,178	97	17,455	14,089
127	1,665	1,12,946	96	17,247	14,065
128	1,670	1,12,039	96	17,213	13,956
129	1,671	1,12,166	96	17,267	13,968
130	1,643	1,11,477	94	17,246	13,878
131	1,646	1,11,068	96	17,192	13,827
132	1,668	1,12,057	98	17,277	13,949
133	1,701	1,12,271	100	17,278	13,983
134	1,766	1,13,367	102	17,276	14,120
135	1,756	1,12,596	102	17,625	14,027
136	1,762	1,12,020	102	17,639	13,959
137	1,775	1,10,970	102	17,630	13,831
138	1,833	1,10,384	105	18,037	13,762
139	1,796	1,09,370	103	17,940	13,637
140	1,778	1,10,002	102	17,794	13,713
141	1,793	1,10,754	103	17,615	13,806
142	1,835	1,12,091	104	17,645	13,970
143	1,849	1,10,844	104	17,733	13,822
144	1,823	1,11,318	102	18,096	13,871
145	1,803	1,11,744	101	17,832	13,915
146	1,791	1,12,709	100	18,013	14,035
147	1,769	1,12,502	98	17,988	14,014
148	1,751	1,13,628	96	18,134	14,144
149	1,728	1,14,201	95	18,074	14,212
150	1,720	1,15,500	95	18,423	14,366
151	1,693	1,15,652	93	18,157	14,379
152	1,694	1,15,559	92	18,219	14,374
153	1,683	1,14,521	92	17,964	14,245
154	1,676	1,13,815	92	17,799	14,159
155	1,662	1,14,897	90	18,121	14,281
156	1,663	1,15,142	91	18,284	14,316

and the correlations will be

Correlations
Cost with units produced		-0.63
Cost with batches		0.75
Cost with machine Hrs		-0.08
Cost with Direct Labour Hrs		-0.61

and still the activity cost driver will be batches due to the above mentioned reason in part D