Question 2: Modelling Cost Behaviour Truck Parts Company (TPC) is an engineering firm that produces custom-designed engine parts for customers who manufacture trucks. The company employs an engineering consultant who works with the company's customers to design the engineering specifications for the parts required. The company then manufactures those parts in their factory to the customers' specifications. Each customer's order varies in terms of the complexity of the engine part design and the volume of units they require. The company has calculated its budgeted overhead costs using the account method for many years. This method is applied using machine hours. However, the management accountant has been analysing past budget variances and thinks this method has not been very good at predicting actual overhead costs. He has shared his concerns with the CFO, and she has asked him to prepare a comparison of the account method with the regression method (which the management accountant thinks should be adopted), and to present his findings in a memo. For the last income year (year ended 30 June 2019), the company's budgeted overhead costs were determined using the actual costs from the previous year (year ended 30 June 2018). In the year ended 30 June 2018, the total machine hours were 121,250 and the overhead cost breakdown of the costs incurred in the year ended 30 June 2018 is shown below: Overhead cost Total Cost Description (percentages relate to the total cost) Factory supervisor $89,000 The factory supervisor is employed on a flexible contract. His salary is made up of a fixed portion (75%) and the remainder is a bonus based on the number of engine parts produced. Machine maintenance $36,000 Standard maintenance on all the machines is performed once a month. $52,000 Machine part replacements The cutting tools in each of the machines need to be replaced after a set number of machine hours. Tools $20,000 The total incurred varies in proportion with the number of engine parts produced. Utilities $96,000 This cost primarily relates to the number of machine hours (85%), but also includes the cost of lighting in the factory (15%). Factory rent $260,000 This is the annual rental cost. Engineering consultant $175,000 An engineer is employed on contract and is paid a fixed rate (60%) for each design he produces. He also receives a bonus (40%) based on the number of engine parts produced. Total Cost year ended 30 June 2018 $728,000 In addition, the management accountant has also collected monthly data for the overhead costs for the year ended 30 June 2018. He has also compiled information on machine hours, direct labour hours and the volume of engine parts made in that year, which he thinks could be possible cost drivers. Month Engine Parts Machine Volume | Hours Direct Labour Hours 35,000 61,000 31,000 36,000 26,000 54,000 42,000 38,000 40,000 49,000 35,000 21,000 468,000 7,500 10,000 11,000 9,000 11,000 9,500 11,500 11,500 10,000 10,900 10,600 8,750 121,250 10,750 13,700 16,000 14,000 16,800 13,800 16,700 17,000 15,000 16,000 15,500 13,000 178,250 Total Overhead Costs $45,000 $58,000 $64,000 $53,000 $66,000 $55,000 $70,000 $71,000 $62,000 $69,000 $63,000 $52,000 728,000 12 Total For the year ended 30 June 2019, the actual overheads incurred by the company were $811,000 with actual machine hours of 132,000, direct labour hours of 190,000 and the total volume of engine parts made were 530,000. Required: We strongly recommend using Excel to prepare your answers to (a) and (b). In particular, for part (b) you will need to use the Data Analysis toolpak in Excel. (a) Classify the overhead costs detailed in the overhead cost breakdown on page 6 into fixed and variable costs. Explain your reasoning for the classification of each item. Using this classification, determine the annual cost function to be used in the year ended 30 June 2019 using the account method. (b) Determine the annual cost function using the monthly data above and using regression analysis. Select the cost driver(s) you think are best to estimate total overhead costs using this method. (To determine the best cost drivers you should consider the information provided in the overhead cost breakdown, prepare scatterplots for each cost driver and analyse the results of at least two alternative regression equations.). (c) Prepare a memo for the CFO that (i) explains your analysis for the two methods in (a) and (b) above and (ii) provides a recommendation on which of the two cost modelling techniques you think should be used and why. You should add supporting evidence and/or calculations to show how you came to your conclusion. Also, include your annual overhead cost equation for both the account method and the regression method. Note the CFO requests a memo of no more than one page (within specified margins and font size) excluding any regression results or other supporting calculations. Question 2 Monthly Data Machine Hours Engine Month Parts Volume 1 35,000 2 61,000 3 31,000 4 36,000 5 26,000 6 54,000 7 42,000 8 38,000 940,000 10 49,000 11 35,000 12 21,000 7,500 10,000 11,000 9,000 11,000 9,500 11,500 11,500 10,000 10,900 10.600 8,750 Direct Total Labour Overhead Hours Costs 10.750 $ 45,000 13,700 $ 58,000 16,000 $ 64,000 14,000 $ 53,000 16,800 $ 66,000 13,800 $ 55,000 16,700 $ 70,000 17,000 $ 71,000 15,000 $ 62,000 16,000 $ 69,000 15,500 $ 63,000 13,000 $ 52,000 Total 468.000 121.250 178,250 $728.000 Question 2: Modelling Cost Behaviour Truck Parts Company (TPC) is an engineering firm that produces custom-designed engine parts for customers who manufacture trucks. The company employs an engineering consultant who works with the company's customers to design the engineering specifications for the parts required. The company then manufactures those parts in their factory to the customers' specifications. Each customer's order varies in terms of the complexity of the engine part design and the volume of units they require. The company has calculated its budgeted overhead costs using the account method for many years. This method is applied using machine hours. However, the management accountant has been analysing past budget variances and thinks this method has not been very good at predicting actual overhead costs. He has shared his concerns with the CFO, and she has asked him to prepare a comparison of the account method with the regression method (which the management accountant thinks should be adopted), and to present his findings in a memo. For the last income year (year ended 30 June 2019), the company's budgeted overhead costs were determined using the actual costs from the previous year (year ended 30 June 2018). In the year ended 30 June 2018, the total machine hours were 121,250 and the overhead cost breakdown of the costs incurred in the year ended 30 June 2018 is shown below: Overhead cost Total Cost Description (percentages relate to the total cost) Factory supervisor $89,000 The factory supervisor is employed on a flexible contract. His salary is made up of a fixed portion (75%) and the remainder is a bonus based on the number of engine parts produced. Machine maintenance $36,000 Standard maintenance on all the machines is performed once a month. $52,000 Machine part replacements The cutting tools in each of the machines need to be replaced after a set number of machine hours. Tools $20,000 The total incurred varies in proportion with the number of engine parts produced. Utilities $96,000 This cost primarily relates to the number of machine hours (85%), but also includes the cost of lighting in the factory (15%). Factory rent $260,000 This is the annual rental cost. Engineering consultant $175,000 An engineer is employed on contract and is paid a fixed rate (60%) for each design he produces. He also receives a bonus (40%) based on the number of engine parts produced. Total Cost year ended 30 June 2018 $728,000 In addition, the management accountant has also collected monthly data for the overhead costs for the year ended 30 June 2018. He has also compiled information on machine hours, direct labour hours and the volume of engine parts made in that year, which he thinks could be possible cost drivers. Month Engine Parts Machine Volume | Hours Direct Labour Hours 35,000 61,000 31,000 36,000 26,000 54,000 42,000 38,000 40,000 49,000 35,000 21,000 468,000 7,500 10,000 11,000 9,000 11,000 9,500 11,500 11,500 10,000 10,900 10,600 8,750 121,250 10,750 13,700 16,000 14,000 16,800 13,800 16,700 17,000 15,000 16,000 15,500 13,000 178,250 Total Overhead Costs $45,000 $58,000 $64,000 $53,000 $66,000 $55,000 $70,000 $71,000 $62,000 $69,000 $63,000 $52,000 728,000 12 Total For the year ended 30 June 2019, the actual overheads incurred by the company were $811,000 with actual machine hours of 132,000, direct labour hours of 190,000 and the total volume of engine parts made were 530,000. Required: We strongly recommend using Excel to prepare your answers to (a) and (b). In particular, for part (b) you will need to use the Data Analysis toolpak in Excel. (a) Classify the overhead costs detailed in the overhead cost breakdown on page 6 into fixed and variable costs. Explain your reasoning for the classification of each item. Using this classification, determine the annual cost function to be used in the year ended 30 June 2019 using the account method. (b) Determine the annual cost function using the monthly data above and using regression analysis. Select the cost driver(s) you think are best to estimate total overhead costs using this method. (To determine the best cost drivers you should consider the information provided in the overhead cost breakdown, prepare scatterplots for each cost driver and analyse the results of at least two alternative regression equations.). (c) Prepare a memo for the CFO that (i) explains your analysis for the two methods in (a) and (b) above and (ii) provides a recommendation on which of the two cost modelling techniques you think should be used and why. You should add supporting evidence and/or calculations to show how you came to your conclusion. Also, include your annual overhead cost equation for both the account method and the regression method. Note the CFO requests a memo of no more than one page (within specified margins and font size) excluding any regression results or other supporting calculations. Question 2 Monthly Data Machine Hours Engine Month Parts Volume 1 35,000 2 61,000 3 31,000 4 36,000 5 26,000 6 54,000 7 42,000 8 38,000 940,000 10 49,000 11 35,000 12 21,000 7,500 10,000 11,000 9,000 11,000 9,500 11,500 11,500 10,000 10,900 10.600 8,750 Direct Total Labour Overhead Hours Costs 10.750 $ 45,000 13,700 $ 58,000 16,000 $ 64,000 14,000 $ 53,000 16,800 $ 66,000 13,800 $ 55,000 16,700 $ 70,000 17,000 $ 71,000 15,000 $ 62,000 16,000 $ 69,000 15,500 $ 63,000 13,000 $ 52,000 Total 468.000 121.250 178,250 $728.000