SERVICE DEPT ALLOCATION - ACCT 545 Assignment, Module 8 Tooth, Inc. manufactures both traditional toothpaste and gel toothpaste, with each type of toothpaste produced in separate departments. Three support departments support the production departments: Power, General Factory, and Personnel. Power is allocated based on machine hours, General Factory is allocated based on square feet, and Personnel is allocated based on the number of employees. Budgeted data on the five departments are as follows. General Traditional Gel Power Factory Personnel Toothpaste Toothpaste Costs $90,000 $300,000 $120,000 $137,500 $222.500 Square feet 3,600 2.400 10,800 7,200 Machine hours 1,403 1,345 8,000 24,000 No. of employees 8 13 18 14 Required: Create an EXCEL file with multiple worksheets (2), with proper headings and details, and complete the following. 1. Allocate the service department costs to the production departments using the Direct Method and compute each service departments' cost allocation rate. Be sure to properly label each rate. 2. Allocate the service department costs to the production departments using the Step Method. Use the following allocation order Highest initial cost to lowest initial cost. Thus an order of General Factory, Personnel, and then Power. Also, compute each service departments' cost allocation rate. Be sure to properly label each rate. 3. Allocate the service department costs to the production departments using the Step Method. Use the following order: Largest percentage user of another service department first, 2" Largest user of another service dept. 2", etc... Thus the order of: Personnel, General Factory, and then Power. Also, compute cach service departments' cost allocation rate. Be sure to properly label each rate. 4. Allocate the service department costs to the production departments using the Reciprocal Method via Excel's inverse matrix function. Also, compute each service departments' cost allocation rate. Be sure to properly label each rate. 5. Prepare a summary table that lists each method's cost allocation rates and the total final production costs in each production department. 6. Discuss the benefits and challenges of implementing each method. And based on your results, what method would you recommend Tooth, Inc. implement and why? Worksheet Guidance: I would advise one worksheet for requirements 1, 2, 3, 5, and 6, similar to the Module example template I provided. After completing the first three requirements you could create the summary table for #5 and then a textbox for #6. Then a separate worksheet could be created for requirement #4. But, this is only guidance. Create what you believe is best