Select the best regression model. P3.6 (LO 1,3) S Excel Business Writing Meyer Grove Company has traditionally had large man- ufacturing overhead costs relative to its direct material (DM) and direct labor (DL) costs. Because of the significance of these overhead costs, management is trying to better understand what is driving them. Dawn, the cost analyst on the team, gathered select information from key activities and product costs that are potential cost drivers: direct labor hours, machine hours, direct material costs, and number of material moves. Data pertaining to the past 18 months for manufacturing overhead costs, as well as usage informa- tion for each of the possible cost drivers, is as follows. ce AutoSave Of B1 fx E Number of Material Moves 7 16 4 B DL Hours 2,900 3,800 3,500 4,200 3,750 4,400 3,050 5,700 Machine Hours 1,950 2,200 2,400 1,950 2,400 2,600 3,100 2,750 F Overhead Costs $195,000 220,000 170,000 260,000 300,000 275,000 230,000 305,000 19 20 22 12 11 A 1 Date 2 Jul-24 3 Aug-24 4 Sep-24 5 Oct-24 6 Nov-24 7 Dec-24 8 Jan-25 9 Feb-25 10 Mar-25 11 Apr-25 12 May-25 13 Jun-25 14 Jul-25 15 Aug-25 16 Sep-25 17 Oct-25 18 Nov-25 19 Dec-25 D DM Costs $16,000 21,500 14,700 19,000 23,500 20,500 26,000 32,000 31,500 34,000 33,000 33,000 34,000 32,000 27,500 28,000 31,500 22,000 5,400 2,000 9 320,000 6,050 15 5,600 11 3,250 5,250 5,900 5,100 4,350 3,500 4,400 1,850 2,300 2,300 1,800 2,300 2,200 2,500 2,250 2,250 18 22 16 14 21 315,000 340,000 280,000 290,000 318,000 315,000 310,000 280,000 300,000 28 19 Sheet1 + Required a. Using the above observations and regression analyses, determine the best cost formula to explain this company's monthly overhead costs. The company is perfectly happy to select a model with one cost driver if it's the best model. Management would like to at least consider a multiple regression model if it seems viable, though. Clearly write out the formal cost function that Dawn should con- clude is the best one. b. As if you were Dawn, write a memo to the rest of the team, explaining how and why you reached this conclusion. The team would benefit from a thorough explanation of not only why this preferred model is valid, but also why this model's cost driver is (or why this model's multiple cost drivers are) better than any other combination of cost drivers available to predict monthly overhead costs. The team would also benefit from Dawn's understanding of the key linearity assumption. c. Use Dawn's preferred regression model as specified in part (a) to predict this company's overhead costs for next month if the following cost driver levels are expected: Direct labor hours Machine hours Direct material costs Number of material moves 4,000 2,400 $24,000 14 Select the best regression model. P3.6 (LO 1,3) S Excel Business Writing Meyer Grove Company has traditionally had large man- ufacturing overhead costs relative to its direct material (DM) and direct labor (DL) costs. Because of the significance of these overhead costs, management is trying to better understand what is driving them. Dawn, the cost analyst on the team, gathered select information from key activities and product costs that are potential cost drivers: direct labor hours, machine hours, direct material costs, and number of material moves. Data pertaining to the past 18 months for manufacturing overhead costs, as well as usage informa- tion for each of the possible cost drivers, is as follows. ce AutoSave Of B1 fx E Number of Material Moves 7 16 4 B DL Hours 2,900 3,800 3,500 4,200 3,750 4,400 3,050 5,700 Machine Hours 1,950 2,200 2,400 1,950 2,400 2,600 3,100 2,750 F Overhead Costs $195,000 220,000 170,000 260,000 300,000 275,000 230,000 305,000 19 20 22 12 11 A 1 Date 2 Jul-24 3 Aug-24 4 Sep-24 5 Oct-24 6 Nov-24 7 Dec-24 8 Jan-25 9 Feb-25 10 Mar-25 11 Apr-25 12 May-25 13 Jun-25 14 Jul-25 15 Aug-25 16 Sep-25 17 Oct-25 18 Nov-25 19 Dec-25 D DM Costs $16,000 21,500 14,700 19,000 23,500 20,500 26,000 32,000 31,500 34,000 33,000 33,000 34,000 32,000 27,500 28,000 31,500 22,000 5,400 2,000 9 320,000 6,050 15 5,600 11 3,250 5,250 5,900 5,100 4,350 3,500 4,400 1,850 2,300 2,300 1,800 2,300 2,200 2,500 2,250 2,250 18 22 16 14 21 315,000 340,000 280,000 290,000 318,000 315,000 310,000 280,000 300,000 28 19 Sheet1 + Required a. Using the above observations and regression analyses, determine the best cost formula to explain this company's monthly overhead costs. The company is perfectly happy to select a model with one cost driver if it's the best model. Management would like to at least consider a multiple regression model if it seems viable, though. Clearly write out the formal cost function that Dawn should con- clude is the best one. b. As if you were Dawn, write a memo to the rest of the team, explaining how and why you reached this conclusion. The team would benefit from a thorough explanation of not only why this preferred model is valid, but also why this model's cost driver is (or why this model's multiple cost drivers are) better than any other combination of cost drivers available to predict monthly overhead costs. The team would also benefit from Dawn's understanding of the key linearity assumption. c. Use Dawn's preferred regression model as specified in part (a) to predict this company's overhead costs for next month if the following cost driver levels are expected: Direct labor hours Machine hours Direct material costs Number of material moves 4,000 2,400 $24,000 14