Required:

1. Using regression analysis and choosing from several potential cost drivers, derive the cost drivers that are significant predictors of overall overhead costs that can be used to help allocate overhead.

2. Derive the formula for allocating overhead.

3. Compare estimated overhead to actual overhead using a scatterplot.

Ask the Question:How can regression analysis be used to find cost drivers and derive a formula for allocating overhead?

Master the Data:Apply the same steps as Lab 8-4 to the Lab 8-4 Alt Data.xlsx dataset to perform cost driver analysis for the service call center at Thrustmaster. Thrustmaster sells game controllers, joysticks, and steering wheels for PCs, Xbox, Nintendo, and Playstation consoles.

Thrustmaster is trying to understand its overhead allocation for its call center that supports its sales. It believes that by understanding the overhead costs and their respective cost drivers, it would be in a better position to allocate overhead to the various products that require more service. Thrustmaster are considering these four potential cost drivers:

1. Customer call minutes
2. Customer complaints
3. Average workers
4. Company sales

Software needed

• Excel
• Screen capture tool (Windows: Snipping Tool; Mac: Cmd+Shift+4)

Data:Excel File Lab 8-4 Alt Data.xlsx.

Perform the Analysis:Refer to Lab 8-4 Alternate in the text for instructions and Lab 8-4 steps for each of the lab parts.

Share the Story:We have now developed a formula for overhead costs that can be used to apply overhead to subsequent deliveries. This will help us determine the cost (as well as price) for subsequent deliveries.

Required:

1. For upcoming customer service months, the company will apply overhead using which formula?

multiple choice 1

• ($223.201 Customer Complaints) + ($1,058.172 Average Workers)
• $13,139.189 + ($222.211 Customer Complaints) + ($1,052.086 Average Workers)
• ($222.211 Customer Complaints) + ($1,052.086 Average Workers)
• $14,396.291 + ($223.201 Customer Complaints) + ($1,058.172 Average Workers)

2. How many months are we using to estimate overhead costs for the service center?

multiple choice 2

• 46
• 48
• 49
• 47

3. Based on the results of the cost driver analysis for Thrustmaster, which cost drivers should be used?

multiple choice 3

• Customer Call Minutes and Average Workers
• Customer Call Minutes and Customer Complaints
• Customer Complaints and Average Workers
• Average Workers

4. The dependent variable in the regression in our cost driver analysis is:

multiple choice 4

• Total Overhead Cost for the Period Studied
• Total Overhead Cost per Month
• Overhead Cost per Delivery
• Company Sales

5. If the number of customer complaints for a month is 50 and the average workers is 30, what is the estimated overhead cost per month for Thrustmaster?

multiple choice 5

• $57,301.50
• $39,800.11
• $14,396.29
• $42,905.10

Total Overhead Cost per Month Customer Cal] Minutes Customer Complaints Avg. Workers Company Sales 39,055 , 1753, 44, 13, 3,111,810 , 54,998 _ 2395 95_ 15_ 3,553,530 _ 35,124 _ 2313 45 12_ 3,357,950 _ 39,004 _ 1511_ 40_ 14_ 1,785,150 _ 37,925 _ 1912 48_ 15_ 1,597,550 _ 41,533 _ 2342 47 13_ 3,851,550 _ 40,851 _ 1258 42_ 13_ 1,252,350 _ 39,510 _ 1499_ 50_ 15_ 2,352,780 _ 48,731 _ 2070 83 14_ 1,553,810 _ 37,527 _ 1992_ 50_ 14_ 3,585,350 _ 43,172 _ 1887 47 14_ 2,255,310 _ 35,580 _ 1938 48_ 12_ 2,785,040 _ 51,242 , 2428, 97, 14, 1,297,430 , 47,324 _ 1317 88 11_ 2,111,800 _ 49,101 _ 1215 81_ 13_ 1,055,330 _ 34,395 _ 1251 42 13_ 1,957,380 _ 40,347 _ 1537 41_ 13_ 3,745,950 _ 43,855 _ 1525 81_ 12_ 853,940 45,802 _ 1405 94 13| 1,532,450 _| 48,502 _ 1479 99_ 15, 2,554,030 _ 35,542 _ 1525 41_ 14_ 955,550 _ 52,427 _ 2481 99_ 14_ 3,559,050 _ 49,883 _ 2007 80_ 12 2,829,010 _ 42,331 _ 2181 87_ 11_ 2,093,310 _ 40,472 , 1594, 42, 15, 1,370,150 , 35,177 2257 45 12 2,648,390 36,491 1624 41 12 3,130,220 31,232 1241 41 11 3,260,620 47,675 2469 99 11 3,093,120 48,475 1389 93 14 3,923,930 37,687 1648 41 14 3,532,600 36,092 1400 47 12 1,407,770 40,411 1863 47 15 3,544,220 47,606 1219 81 15 1,804,470 40,070 2280 46 13 2,986,740 50,142 1856 93 15 1,937,110 43,156 1349 45 14 3,846,060 42,701 1205 80 11 2,696,670 49,216 1314 88 12 1,329,160 45,428 1969 98 11 2,712,170 46,698 1832 92 11 1,330,060 42,436 2439 49 13 3,641,900 38,806 2332 47 14 3,174,290 44,215 1711 86 14 3,551,590 50,796 1905 95 11 3,025,140 36,588 2222 44 12 2,936,070 46,471 2108 84 14 3,676,490 53,721 1922 96 14 2,601,950