Determining appropriate activity cost drivers

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:

• 1. Using the activity cost pool and activity driver data, prepare a scatter plot 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?
  2. Using Excel's CORREL formula, determine the correlation between each activity driver level and the inspections cost.
  3. Using Excel's RSQ formula determine how much of the variation in activity costs each activity driver explains.
  4. Based on your analysis, which activity driver do you recommend? Why?
  5. 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