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 data 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? What Excel formula are you using?

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

Answer 1

a)

Scatter plot between Quality Insepctions Cost and Units Produced

The scatter plot is obtained in excel by following these steps,

Step 1: Write the data values in excel.

Step 2: select the Quality Insepctions Cost and Units Produced column then INSERT > Recommended Charts > Scatter > OK.

The Chart is obtained. The screenshot is shown below,

Scatter plot between Quality Insepctions Cost and Batches

Scatter plot between Quality Insepctions Cost and Machine Hours

Scatter plot between Quality Insepctions Cost and Direct Labor Hours

b)

The correlation coefficient values are obtained in excel. The screenshot is shown below,

c)

The R square values are obtained in excel. The screenshot is shown below,

From the above results, we can say that ,

Units Produced explains approximately 9.32% of the variance of Quality Insepctions Cost,

Batches explains approximately 7.71% of the variance of Quality Insepctions Cost,

Machine Hours explains approximately 1.86% of the variance of Quality Insepctions Cost,

Direct Labor Hours explains approximately 7.55% of the variance of Quality Insepctions Cost.

d)

Since the R Square value is high for activity Batches, it will be recommended to determine the cost.

e)

Since activity Batches is used to determine the cost, the expected level of cost is given by,

Where, a = intercept, b = slope of linear regression equation.

The least square estimate of a and b is obtained in excel. The screenshot is shown below,