The Cheshire Equipment Company manufactures lathes, routers, saws, and other woodworking equipment. The company’s controller imported its weekly manufacturing data from the past year into Excel. The data included materials handling costs, number of parts moved, weight of parts, and volume of parts. The following is a sample of the records in that Excel file. Access the full data set online in the MyLab Accounting exercise.

Requirements
Use the CORREL function in Excel to determine which variable would be the best allocation base for the company’s material handling costs. Rank the four variables from the most highly correlated with materials handling costs to the least correlated. Which variable would you recommend be used as the allocation base for materials handling costs?

Transcribed Image Text:

1 2 3 4 5 6 7 8 9 A Week handling costs 10,299 4,676 6,788 2,985 2,876 7,432 6,892 5,316 1 $ 2 $ 3 $ B Materials 4 $ 5 $ 6 $ 7 $ 8 $ C Number of parts moved 2,641 998 1,609 549 969 1,065 784 823 D Weight of parts (pounds) 1,971 796 977 991 416 2,227 2,124 992 E Volume of parts (pallets) 960 977 2,336 1,377 800 1,971 2,250 1,325 Time spent (hours) 465 207 311 156 149 334 304 246