The Harper 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 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 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 April 6 May 7 June 8 July 9 August B Year Utility costs 3,130 4,050 4,116 2,386 2,621 3,623 3,609 4,091 2017 $ 2017 $ 2017 $ 2017 $ 2017 $ с 2017 $ 2017 $ 2017 $ E Number of units produced Sales revenue 2,175 $ 2,382 $ 2,521 $ 2,342 $ 2,508 $ 2,643 $ 2,192 $ 2,796 $ 237,817 258,013 261,443 241,243 250,123 277,707 243,170 278,415 F Machine hours 3,170 3,699 4,102 2,424 2,876 3,671 3,801 4,103 G Labor hours 7,811 9,114 10,532 5,969 7,541 9,372 9,498 6,844