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, 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 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 Quality Insepctions Cost Units Produced Batches Machine Hours Direct Labor Hours $ 1,626 83,481 57 16,353 10,518 $ 2,044 105,939 98 25,598 13,136 $ 1,736 138,215 84 25,416 17,125 $ 2,401 82,028 123 27,485 10,528 $ 2,280 89,964 148 27,776 11,299 $ 1,267 130,227 75 19,183 16,180 $ 2,391 84,843 166 21,745 10,746 $ 2,418 93,504 161 22,909 11,748 $ 774 95,214 56 26,631 11,685 $ 2,332 117,356 120 21,225 14,621 $ 2,572 112,073 131 21,317 13,986 $ 1,918 82,702 139 27,087 10,490 $ 1,819 148,925 86 26,939 18,323 $ 1,326 125,663 58 17,269 15,574 $ 1,648 118,270 117 27,161 14,573 $ 1,882 115,109 143 18,017 14,447 $ 2,129 90,066 112 27,124 11,307 $ 1,962 130,190 150 12,683 16,184 $ 1,751 131,078 99 17,705 16,107 $ 1,061 100,707 64 19,261 12,374 $ 762 148,448 54 18,166 18,092 $ 2,166 118,074 104 25,257 14,553 $ 1,571 138,106 112 19,470 17,068 $ 1,691 108,828 107 17,036 13,649 $ 2,647 131,576 134 19,045 16,498 $ 1,631 123,399 60 20,018 15,375 $ 1,215 89,104 61 16,209 11,106 $ 1,027 106,660 63 18,393 13,085 $ 644 87,025 51 18,437 10,821 $ 1,958 133,661 93 10,925 16,552 $ 1,554 128,015 120 17,937 15,828 $ 1,401 143,200 86 18,559 17,726 $ 740 128,785 51 10,099 15,893 $ 2,860 142,728 165 20,995 17,733 $ 2,224 96,737 132 22,561 12,100 $ 1,371 94,023 67 23,730 11,717 $ 920 82,081 72 17,493 10,257 $ 1,395 149,748 101 16,047 18,412 $ 2,474 100,344 169 10,768 12,713 $ 2,268 101,425 131 26,647 12,854 $ 2,013 89,539 119 14,015 11,135 $ 2,356 117,345 156 18,850 14,494 $ 2,257 86,370 168 14,751 10,961 $ 2,422 98,056 143 19,805 12,276 $ 1,932 83,271 109 11,061 10,502 $ 2,590 107,880 159 26,449 13,586 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 1,701 2,040 1,908 2,152 1,705 1,091 1,285 1,749 1,899 2,574 1,460 1,236 1,451 2,052 1,246 1,546 2,338 1,178 1,664 1,042 1,667 1,085 1,690 2,148 1,724 2,002 2,903 2,258 1,496 1,940 1,626 2,129 1,749 1,810 2,003 2,213 1,453 1,311 1,037 1,134 2,371 2,428 2,467 2,638 2,400 2,107 1,139 114,017 141,278 131,427 87,576 105,897 137,863 105,966 113,491 135,404 118,977 108,169 139,303 141,178 135,222 117,651 90,802 145,299 143,988 139,198 138,232 149,345 97,291 100,025 92,106 144,295 126,032 83,548 95,736 100,358 104,231 80,340 137,565 82,523 97,869 90,266 119,616 115,210 104,454 88,748 139,251 97,479 128,002 111,798 106,219 116,886 142,368 112,606 117 122 102 159 72 52 101 68 93 155 67 76 55 108 55 118 156 62 109 66 79 58 116 114 92 116 155 148 72 107 77 110 107 73 110 99 57 102 65 60 124 115 169 164 125 91 52 16,632 26,212 23,126 11,435 14,396 20,149 27,544 18,207 18,511 13,952 12,894 14,862 14,084 22,784 15,647 11,623 12,498 26,536 25,516 20,416 15,158 15,769 10,179 27,464 23,986 14,026 27,859 26,346 13,726 14,854 18,798 12,692 24,803 23,296 15,686 27,074 13,688 23,384 19,517 12,888 17,916 18,031 22,302 21,894 11,629 19,878 10,829 14,023 17,597 16,291 11,155 13,084 17,051 13,152 14,036 16,630 14,758 13,451 17,082 17,372 16,865 14,409 11,485 18,081 17,798 17,157 17,061 18,490 12,127 12,600 11,484 17,806 15,580 10,735 12,087 12,564 12,927 10,234 16,882 10,498 12,276 11,481 14,844 14,113 13,033 10,893 17,237 12,142 15,953 13,962 13,343 14,705 17,618 13,777 94 95 96 97 98 99 100 101 102 103 104 $ $ $ $ $ $ $ $ $ $ $ 1,306 2,885 1,100 2,131 1,811 1,928 1,276 1,527 2,176 1,564 2,142 92,930 85,927 128,001 138,025 105,397 88,465 118,707 129,211 144,143 80,889 94,066 96 152 87 148 80 127 67 99 113 118 165 22,443 14,086 21,742 25,300 25,729 22,027 25,246 23,519 20,821 22,465 12,640 11,710 10,972 15,807 16,943 13,039 11,082 14,510 15,988 17,874 10,197 11,702