Problem 1 - 30 points The Halberstadt Manufacturing Company produces a variety of goods for sale. The company is divided into six departments. The company accountant has compiled the following overhead costs for each department. Total Department Factory Overhead per Factory Overhead Analysis Sheets SERVICE DEPARTMENTS Department Overhead Amount Maintenance 167,699 Power 104,242 Personnel 94,840 PRODUCING DEPARTMENTS Department Overhead Amount Fabrication 271,133 Assembly 267,295 Finishing 192,711 Summary of departments served by each of the service departments Maintenance Department: The Power Department: Personnel Department: ALL Departments ALL Departments ALL departments The Bases for Allocation of Service Department Costs are: Power Department Maintenance Department Personnel Department Kilowatt hours used Square Feet of Floor Space Number of Employees Data for Overhead Cost Allocation: Department Kilowatt Hours Used Square Feet Number of Employees Direct Labor Hours Direct Labor Cost Maintenance Power Personnel Fabrication Assembly Finishing 6,141 9,600 5,619 13,340 20,671 15,318 1,150 1,438 966 1,886 1,196 1,265 7 12 4 6 9 41,400 46,000 28,750 592,020 596,620 524,688 6 Required: 1. Use the concepts discussed in class to prepare a worksheet on Excel to Allocate the Service Department Costs. Round all overhead allocation rates to 4 decimal places. (.7657 or 76.57%) 2. Round all allocated dollar amounts to the nearest cent. ($102.346 = $102.35) M7 fx A B D E F G H DISTRIBUTION OF SERVICE DEPARTMENT COSTS Service Departments Producing Departments Allocation Base Allocation Rate Maintenance Power Personnel Fabrication Assembly Finishing Total Description Total Overhead from Analysis Worksheets $ 167,699.00 $ 104,242.00 $ 94,840.00 $ 271,133.00 $ 267,295.00 $ 192,711.00 $ 1,097,920.00