Accounting -Excel Assignment 3 – Allocating Service Department Costs

Question
Excel Assignment 3 – Allocating Service Department Costs
Due Monday, October 5, 2015
(30 Points)
Clyde’s Cycles produces high­end 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 step­down method or reciprocal method of
allocating their service department costs. However, your boss feels that the ongoing work involved in
using the step­down 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%

Pre­Allocation 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 post­allocation production department costs using the direct, step­down, 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, Step­Down, Reciprocal, and Recap.