Top Co. is a large corporation with more than 30 departments each employing 25­100people. Recent experience suggests that the cost function used to estimate overhead inDepartment XO­12 is no longer appropriate. The current function was developed threeyears ago and a number of departmental changes have occurred since. The changeshappened one at a time and they included changes in facilities and processes. The costaccountant decided that each individual change that occurred wasn’t major enough tojustify calculation of a new cost function. Now it’s clear that the cumulative effect of allthe changes has been large.You’ve been assigned the task of developing a new cost function for overhead inDepartment XO­12. Initial analysis suggests that the number of direct labor hours maybean appropriate cost driver. Departmental records are available for nine months and can befound on the attached spreadsheet.An assistant has analyzed the data for March through July and made the appropriateadjustments except for the following items (for which the assistant was unsure of theproper treatment).1. The semiannual property tax bill for Department XO­12 was paid on June 30. Theentire amount (1) $3,000 was charged to overhead for June.2. The costs to install a new piece of equipment with a life of 10 years in thedepartment were charged to overhead in April. The installation cost was (2)$4,300.3. Factory depreciation is allocated to Department XO­12 every month. Thedepartment’s share, (3) $8,000, is included in overhead.4. A strike closed the plant for three weeks in July. Several supervisors were kepton payroll during the strike. Their duties were general housekeeping. These costswere charged to overhead.You also have the details for the overhead account for the months of August andSeptember. They are presented on the attached spreadsheet. You were hired on October 1and have keeping the department accounts since then. Therefore, you know that the datafor October and November are correct, except for any adjustments that might be neededfor the preceding items.ALL formulas, references are required.Required:1. Using the information provided, prepare a spreadsheet to show the adjustments tothe monthly cost data to more accurately reflect the overhead costs incurredduring the months March through November. Use the following column headings:Month Unadjusted Property Depreciation1 Other2 Payroll3 AdjustedOverheadTaxCost2. Discuss whether the data for July should be included in the estimate of futurecosts. Use a scatter plot to help you answer this question.3. Use Excel to develop a cost function by regressing overhead costs in DepartmentXO­12 on direct labor hours.4. Discuss whether your cost function would be reasonable for estimating futureoverhead costs. Be specific and refer to the Excel regression output.1 To predict future overhead the first month depreciation adjustment for the new piece ofequipment starts in March even though it’s not an actual cost until April.2 Dept. AB­34’s power should be added to Dept. XO­12.3 Note payroll paid every two weeks: ½ of August 5 payroll goes to July. Similarly, 80%of September 2 payroll to August.
Continue to order