The Ivy Toy Company manufactures a variety of toys. The controller is trying to determine what the best allocation base for its utility costs would be and has gathered the company’s utility cost data and other information over the past four years. 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 CORRELL function in Excel to analyze whether number of units produced, sales revenue, machine hours, or labor hours would be the appropriate cost allocation base for the company’s utility costs. Rank the four variables from the most highly correlated with utility costs to the least correlated. Which variable would you recommend be used as the allocation base for utility costs?

Transcribed Image Text:

A 1 Month 2 January 3 February 4 March 5 6 April May 7 June 8 July 9 August B C Year Utility costs 4,150 4,860 4,939 2017 $ 2017 $ 2017 $ 2017 $ 2017 $ 2017 $ 2017 $ 2017 $ 2,863 3,145 4,347 4,330 4,909 D Number of units produced 2,392 $ 2,620 $ 2,773 $ 2,576 $ 2,758 $ 2,907 $ 2,411 $ 3,075 $ E Sales revenue 273,489 297,150 310,500 277,429 287,641 319,363 279,645 320,177 Machine hours 3,487 4,068 4,512 2,666 3,163 4,038 4,181 4,513 G Labor hours 8,748 10,207 12,350 6,685 8,445 10,496 10,637 7,665