Accounting -Excel Assignment 3 – Allocating Service Department Costs
Question # 00112996
Posted By:
Updated on: 10/05/2015 02:00 PM Due on: 11/04/2015
Excel Assignment 3 – Allocating Service Department Costs
Due Monday, October 5, 2015
(30 Points)
Clyde’s Cycles produces highend touring bicycles. In the past they have used the direct method of
allocating service department costs. You think the company could increase the accuracy of their
production department costing by switching to either the stepdown method or reciprocal method of
allocating their service department costs. However, your boss feels that the ongoing work involved in
using the stepdown or the reciprocal method is not worth the increased accuracy. You’re determined to
show the boss that by using Excel the ongoing work to use either of the two more accurate systems
would be minimal. You’ve gathered the following information:
Service
Provider
Human
Resources
Maintenance
Purchasing
Service Departments
Human
Resources Maintenance
0%
10%
2%
0%
Human Resources
$135,500
0%
5%
Maintenance
$308,000
Production Departments
Purchasing Machining
5%
35%
2%
0%
20%
60%
PreAllocation Costs
Purchasing Machining
$83,000
$1,325,275
Assembly
30%
Packaging
20%
Total
100%
36%
10%
40%
25%
100%
100%
Assembly
$750,400
Packaging
$602,395
In the past Human Resources costs have been allocated based on the number of positions, Maintenance
costs on the amount of square footage, and Purchasing costs on the number of purchase orders.
Human
Resources
Number of
Positions
Square
Footage of
Department
Number of
Purchase
Orders
Placed for
Department
Maintenance
Purchasing
Machining
Assembly
Packaging
10
10
5
40
30
25
120
1,000
2,500
500
25,000
12,500
10,000
51,500
0
25
0
300
50
75
450
Total
Using Excel compute the postallocation production department costs using the direct, stepdown, and
reciprocal allocation methods. After you finish your computations determine which system Clyde’s
Cycles should use. Justify your answer.
Submit the assignment as an Excel workbook using the name Excel Assignment 3_Your Last Name in the
Assignments section of our Canvas course site. The workbook should contain four worksheets titled:
Direct, StepDown, Reciprocal, and Recap.
Due Monday, October 5, 2015
(30 Points)
Clyde’s Cycles produces highend touring bicycles. In the past they have used the direct method of
allocating service department costs. You think the company could increase the accuracy of their
production department costing by switching to either the stepdown method or reciprocal method of
allocating their service department costs. However, your boss feels that the ongoing work involved in
using the stepdown or the reciprocal method is not worth the increased accuracy. You’re determined to
show the boss that by using Excel the ongoing work to use either of the two more accurate systems
would be minimal. You’ve gathered the following information:
Service
Provider
Human
Resources
Maintenance
Purchasing
Service Departments
Human
Resources Maintenance
0%
10%
2%
0%
Human Resources
$135,500
0%
5%
Maintenance
$308,000
Production Departments
Purchasing Machining
5%
35%
2%
0%
20%
60%
PreAllocation Costs
Purchasing Machining
$83,000
$1,325,275
Assembly
30%
Packaging
20%
Total
100%
36%
10%
40%
25%
100%
100%
Assembly
$750,400
Packaging
$602,395
In the past Human Resources costs have been allocated based on the number of positions, Maintenance
costs on the amount of square footage, and Purchasing costs on the number of purchase orders.
Human
Resources
Number of
Positions
Square
Footage of
Department
Number of
Purchase
Orders
Placed for
Department
Maintenance
Purchasing
Machining
Assembly
Packaging
10
10
5
40
30
25
120
1,000
2,500
500
25,000
12,500
10,000
51,500
0
25
0
300
50
75
450
Total
Using Excel compute the postallocation production department costs using the direct, stepdown, and
reciprocal allocation methods. After you finish your computations determine which system Clyde’s
Cycles should use. Justify your answer.
Submit the assignment as an Excel workbook using the name Excel Assignment 3_Your Last Name in the
Assignments section of our Canvas course site. The workbook should contain four worksheets titled:
Direct, StepDown, Reciprocal, and Recap.
-
Rating:
/5
Solution: Accounting -Excel Assignment 3 – Allocating Service Department Costs