ep. Requirements The Hanson 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. Use that data to complete the following requirement. Requirements for the Excel analysis 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? Print Done urs 1 Month 2 January 3 February 4 March 5 April 6 May 7 June 8 July 9 August 10 September 11 October 12 November 13 December 14 January 15 February 16 March 17 April 18 May 19 June 20 July 21 August 22 September 23 October Number of Year Utility costs units produced Sales revenue 2017 S 3,785 2,455 $ 245,991 2017 S 4,955 2,622 $ 267,884 2017 S 4,866 2,715 $ 271,993 2017 S 2,785 2,811 $ 256,994 2017 S 2,954 2,788 $ 261,884 2017 S 4,011 2,911 $ 294,330 2017 $ 4.169 2,345 $ 257,991 2017 S 4,450 2,996 $ 293,551 2017 S 4,599 3,110 $ 301,661 2017 S 4,862 3,260 $ 315,871 2017 S 4,455 2,999 $ 294,332 2017 S 4,007 2,575$ 275,991 2018 S 3,955 2,395 $ 249,510 2018 S 4,796 2,5925 284,957 2018 $ 4,910 2,696 S 295,535 2018 5 3,064 * 2,260 $ 263,066 2018 S 3,343 2,474 $ 274,075 2018 $ 4,261 2,658 $ 291,772 2018 $ 4,240 2,124 $ 266,781 2018 $ 4,759 2,805 S 291,894 2018 S 4,865 2,880 $ 316,231 2018 S 5,049 2,944 $ 345,729 Machine hours 3,488 3,051 4,492 2,828 3,266 4,009 3,255 4,491 4,366 4,899 4,467 3,291 3,677 3,358 4,853 2,802 3,419 4,265 3,939 5,633 5,758 5,494 Labor hours 8,433 9,558 14,558 7,599 7,958 9,595 9,877 7,291 7,816 8,585 7.982 6,93 8,088 9,494 14,662 7,291 7,955 9,898 9,931 7,288 7,982 8,837 21 AUBUS 22 September 23 October 24 November 25 December 26 January 27 February 28 March 29 April 30 May 31 June 32 July 33 August 34 September 35 October 36 November 37 December 38 January 39 February 40 March 41 April 42 May 43 June 44 July 45 August 46 September 47 October 48 November 49 December 50 2018 $ 2018 $ 2018 $ 2018 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2020 $ 2020 $ 2020 S 2020 $ 2020 $ 2020 $ 2020 $ 2020 $ 2020 $ 2020 S 2020 $ 2020 $ 4,865 5,049 4,640 4,256 3,925 4,766 4,880 3,034 3,313 4,231 4,210 4,729 4,835 5,019 4,610 4,226 4,055 4,896 5,010 3,164 3,443 4,361 4,340 4,859 4,965 5,149 4,740 4,356 2,880 $ 2,944 $ 2,787 $ 1,974 $ 2,588 $ 2,785 $ 2,889 $ 2,453 $ 2,667 $ 2,851 $ 2,317 S 2,998 $ 3,073 $ 3,137 $ 2,980 $ 2,167 $ 2,784 $ 2,981 $ 3,085 $ 2,649 $ 2,863 $ 3,047 $ 2,513 $ 3,194 $ 3,269 $ 3,333 $ 3,176 $ 2,363 $ 316,231 345,729 318,615 291,631 261,557 297,004 307,582 275,113 286,122 303,819 278,828 303,941 328,278 357,776 330,662 303,678 266,184 301,631 312,209 279,740 290,749 308,446 283,455 308,568 332,905 362,403 335,289 308,305 5,758 5,494 4,875 3,978 3,951 3,632 5,127 3,076 3,693 4,539 4,213 5,907 6,032 5,768 5,149 4,252 3,784 3,465 4,960 2,909 3,526 4,372 4,046 5,740 5,865 5,601 4,982 4,085 Using the Excel file you created for Hanson, which variable would be the best allocation base for the company's utility costs? A. Labor hours B. Machine hours C. Sales revenue OD. Number of units produced Using the Excel file you created for Hanson, which variable would be the least desirable base for the company's utility costs? A Machine hours B. Labor hours OC Number of units produced D. Sales revenue Using the Excel file you created for Hanson, what is the variable for sales revenue? Using the Excel file you created for Hanson, what is the variable for sales revenue? O A. 0.733721 B. 0.363567 C. 0.476175 D. 0.657051 Using the Excel file you created for Hanson, what is the variable for machine hours? O A 0.657051 B. 0.476175 OC. 0.733721 D. 0.363567 ep. Requirements The Hanson 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. Use that data to complete the following requirement. Requirements for the Excel analysis 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? Print Done urs 1 Month 2 January 3 February 4 March 5 April 6 May 7 June 8 July 9 August 10 September 11 October 12 November 13 December 14 January 15 February 16 March 17 April 18 May 19 June 20 July 21 August 22 September 23 October Number of Year Utility costs units produced Sales revenue 2017 S 3,785 2,455 $ 245,991 2017 S 4,955 2,622 $ 267,884 2017 S 4,866 2,715 $ 271,993 2017 S 2,785 2,811 $ 256,994 2017 S 2,954 2,788 $ 261,884 2017 S 4,011 2,911 $ 294,330 2017 $ 4.169 2,345 $ 257,991 2017 S 4,450 2,996 $ 293,551 2017 S 4,599 3,110 $ 301,661 2017 S 4,862 3,260 $ 315,871 2017 S 4,455 2,999 $ 294,332 2017 S 4,007 2,575$ 275,991 2018 S 3,955 2,395 $ 249,510 2018 S 4,796 2,5925 284,957 2018 $ 4,910 2,696 S 295,535 2018 5 3,064 * 2,260 $ 263,066 2018 S 3,343 2,474 $ 274,075 2018 $ 4,261 2,658 $ 291,772 2018 $ 4,240 2,124 $ 266,781 2018 $ 4,759 2,805 S 291,894 2018 S 4,865 2,880 $ 316,231 2018 S 5,049 2,944 $ 345,729 Machine hours 3,488 3,051 4,492 2,828 3,266 4,009 3,255 4,491 4,366 4,899 4,467 3,291 3,677 3,358 4,853 2,802 3,419 4,265 3,939 5,633 5,758 5,494 Labor hours 8,433 9,558 14,558 7,599 7,958 9,595 9,877 7,291 7,816 8,585 7.982 6,93 8,088 9,494 14,662 7,291 7,955 9,898 9,931 7,288 7,982 8,837 21 AUBUS 22 September 23 October 24 November 25 December 26 January 27 February 28 March 29 April 30 May 31 June 32 July 33 August 34 September 35 October 36 November 37 December 38 January 39 February 40 March 41 April 42 May 43 June 44 July 45 August 46 September 47 October 48 November 49 December 50 2018 $ 2018 $ 2018 $ 2018 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2019 $ 2020 $ 2020 $ 2020 S 2020 $ 2020 $ 2020 $ 2020 $ 2020 $ 2020 $ 2020 S 2020 $ 2020 $ 4,865 5,049 4,640 4,256 3,925 4,766 4,880 3,034 3,313 4,231 4,210 4,729 4,835 5,019 4,610 4,226 4,055 4,896 5,010 3,164 3,443 4,361 4,340 4,859 4,965 5,149 4,740 4,356 2,880 $ 2,944 $ 2,787 $ 1,974 $ 2,588 $ 2,785 $ 2,889 $ 2,453 $ 2,667 $ 2,851 $ 2,317 S 2,998 $ 3,073 $ 3,137 $ 2,980 $ 2,167 $ 2,784 $ 2,981 $ 3,085 $ 2,649 $ 2,863 $ 3,047 $ 2,513 $ 3,194 $ 3,269 $ 3,333 $ 3,176 $ 2,363 $ 316,231 345,729 318,615 291,631 261,557 297,004 307,582 275,113 286,122 303,819 278,828 303,941 328,278 357,776 330,662 303,678 266,184 301,631 312,209 279,740 290,749 308,446 283,455 308,568 332,905 362,403 335,289 308,305 5,758 5,494 4,875 3,978 3,951 3,632 5,127 3,076 3,693 4,539 4,213 5,907 6,032 5,768 5,149 4,252 3,784 3,465 4,960 2,909 3,526 4,372 4,046 5,740 5,865 5,601 4,982 4,085 Using the Excel file you created for Hanson, which variable would be the best allocation base for the company's utility costs? A. Labor hours B. Machine hours C. Sales revenue OD. Number of units produced Using the Excel file you created for Hanson, which variable would be the least desirable base for the company's utility costs? A Machine hours B. Labor hours OC Number of units produced D. Sales revenue Using the Excel file you created for Hanson, what is the variable for sales revenue? Using the Excel file you created for Hanson, what is the variable for sales revenue? O A. 0.733721 B. 0.363567 C. 0.476175 D. 0.657051 Using the Excel file you created for Hanson, what is the variable for machine hours? O A 0.657051 B. 0.476175 OC. 0.733721 D. 0.363567