cost allocation

All,

I'm enquiring if this method can be done within excel.


Reciprocal Method of Allocation
The final method, is the reciprocal method. Although it is the most accurate, it is also the most complicated. In the reciprocal method, the relationship between the service departments is recognized. This means service department costs are allocated to and from the other service departments.
We can break the process up into 3 steps so we can make sense of the process. To demonstrate, we will use the same basic data:
Service Dept Operating Dept
Maintenance Administration 1 2
Costs $8,000 $4,000 $32,000 $36,000
Machine-hours used 1,000 2,000 1,500 2,500
Number of Employees 100 200 250 150
Step 1: Determine allocation bases
Just like you would for direct or step,we need to calculate the allocation base EXCEPT the only thing you are excluding is the department cost you are trying to allocate — ALL other departments are included. We can calculate the allocation base amount for each service department as follows:
Maintenance Dept Administration Dept
Mach Hrs % of Total Employees % of Total
Maintenance – – 100 20.00% (100/500)
Administration 2,000 2/6** (2,000/6,000) – –
Dept 1 ` 1,500 25% (1,500/6,000) 250 50.00% (250/500)
Dept 2 2,500 2.5/6** (2,500/6,000) 150 30.00% (150/500)
Total 6,000 100% 500 100.00%
**since these numbers do not come out evenly, we keep them in the fraction form and only round the final answer to the nearest dollar.
Step 2: Setup the formulas.
Since maintenance costs are allocated to administration and administration cost is allocated to maintenance — things get interesting. You will need to determine the TOTAL cost being allocated to both the Administration and Maintenance Departments first.
(a) Total Maintenance cost = Maintenance department cost + cost allocated to maintenance from administration.
(b) Total Administration cost = Administration department cost + cost allocated to administration from maintenance.
We will work with the administration cost formula in (b) first:
Total Administration cost = Administration department cost + cost allocated to administration from maintenance. From step 1, we know the cost allocation to administration from maintenance is (2/6 x total maintenance cost). We still do not know what total maintenance cost but we can plug in this new formula.
Total Administration cost = $ 4,000 administration department cost + (2/6 x total maintenance cost)
We can insert the formula from (a) for total maintenance cost into the total administration cost formula (b) as follows:
Total Administration cost = $4,000 admin department cost + [2/6 x (Maintenance department cost + cost allocated to maintenance from administration) ]
We can see from step 1 that administration cost is allocated to maintenance as total administration cost x 20%. Adding this to our formula, we now have:
Total Administration cost = $4,000 admin department cost + [2/6 x ($8,000 Maintenance department cost + (total admin cost x 20%) ) ]
Using algebra, we can assign Total Administration Cost a variable of A giving the formula:
A = 4,000 + [ 2/6 x ($8,000 + 0.20)A]
A = 4,000 + (2/6 x 8,000) + (2/6 x 0.20A)
A = 4,000 + 2,666.67 + 0.067A — rounded
A = 6,666.67 + 0.067A
1.0A – 0.067A = 6,666.67

0.933A = 6,666.67
A or total administration cost = $7,145 rounded
Total maintenance cost can be calculated as $8,000 department cost + $1,429 (7,145 x 20%) allocated from administration for a total of $9,429.
Step 3: Show cost allocations
Now that you have the TOTAL Cost of Maintenance and Personnel, it is time to allocate it using the Total Cost amounts from Step 2 and the percents from Step 1.
Service Dept Operating Dept
Maintenance Administration 1 2
Costs $8,000 $4,000 $32,000 $36,000
Maintenance costs allocated -9,429 3,143 2,357 3,929
(9,429 x 2/6) (9,429 x 25%) (,9429 x 2.5/6)
Administration costs allocated 1,429 -7,145 3,572.50 2,143.50
(7,145 x 20%) (7,145 x 50%) (7,145 x 30%)
Total costs $0 ($2)** $37,930 $42,073
** difference due to rounding in calculations.