IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 11

SIMPLERSIMPLER THAN ABCTHAN ABC

New Ideas for Using Microsoft New Ideas for Using Microsoft Excel for Allocating CostsExcel for Allocating Costs

Reciprocal CostingReciprocal Costing

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 22

OUTLINEOUTLINE

The purpose of the presentation is to The purpose of the presentation is to introduce a new method of solving an old introduce a new method of solving an old problemproblem

The old problem is service cost allocation The old problem is service cost allocation using reciprocal costing.using reciprocal costing.

The new method uses Excel to determine The new method uses Excel to determine the reciprocal allocations using matrix the reciprocal allocations using matrix algebra and a mathematics technique algebra and a mathematics technique known as Cramer’s Rule.known as Cramer’s Rule.

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 33

BenefitsBenefits

By understanding this technique Decision By understanding this technique Decision Support Specialists can have at their Support Specialists can have at their fingertips what is considered the most fingertips what is considered the most accurate method of cost allocation accurate method of cost allocation between service departments while between service departments while overcoming some of the problems which overcoming some of the problems which have kept reciprocal costing from being have kept reciprocal costing from being more widely used in practice.more widely used in practice.

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 44

BenefitsBenefits

Traceability of costsTraceability of costs Doesn’t require specialized software Doesn’t require specialized software Doesn’t require extensive mathematical Doesn’t require extensive mathematical

training to solve systems of equations.training to solve systems of equations. More accurate than earlier methods of More accurate than earlier methods of

reciprocal costingreciprocal costing

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 55

ExampleExample

Service DepartmentsService Departments

MaintenanceMaintenance

PersonnelPersonnel

Production DepartmentsProduction Departments

Product AProduct A

Product BProduct B

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 66

Budgeted Costs & AllocationBudgeted Costs & Allocation

MaintenanceMaintenance

$1,000,000$1,000,000

Allocation BasisAllocation Basis

Value of AssetsValue of Assets

PersonnelPersonnel

$1.800,000$1.800,000

Allocation BasisAllocation Basis

Number of WorkersNumber of Workers

IMA Hands-on: Simpler than ABC 7

Table 1: Allocation AmountsAssets Employees Costs

Maintenance (M) 2,000,000$ 8 1,000,000$ Personnel (P) 1,000,000$ 2 1,800,000$

A 8,000,000$ 20B 24,000,000$ 15

Total 35,000,000$ 45

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 88

Solution MethodsSolution Methods

Direct allocationDirect allocation

Sequential AllocationSequential Allocation

Reciprocal AllocationReciprocal Allocation

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 99

DIRECT ALLOCATIONDIRECT ALLOCATION

B

A

B

A

P

P

M

M

3515000,800,1$

3520000,800,1$

3224000,000,1$

328000,000,1$

Microsoft Equation 3.0

A

$250,000

$750,000

B

Products

$1,028,571

$771,429

$1,278.571 $1,521,429

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 1010

Sequential MethodSequential MethodMaintenance FirstMaintenance First

PP AA BB

30,30330,303

242,424242,424

727,273727,273

B

A

P

P

3515303,830,1

3520303,830,1

B

A

P

M

M

M

3324000,000,1

338000,000,1

331000,000,1

1,045,8871,045,887

784,416784,416

$1,288,311$1,288,311 $1,511,689$1,511,689

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 1111

Sequential MethodSequential MethodPersonnel FirstPersonnel First

MM AA BB

334,884334,884

837.209837.209

627,907627,907B

A

M

P

P

P

4315000,800,1

4320000,800,1

438000,800,1

B

A

M

M

3224884,334,1

328884,334,1 333,721333,721

1,001,1631,001,163

1,170,9301,170,930 1,629,0701,629,070

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 1212

Direct & Sequential MethodsDirect & Sequential Methods

Direct method is simple but less accurateDirect method is simple but less accurate

Traceability of costs are easy with Traceability of costs are easy with sequential method butsequential method but

Results are dependent on the order of Results are dependent on the order of allocation so sequencing needs to be allocation so sequencing needs to be justified.justified.

A BMaintenance first 1,288,311 1,511,689

Personnel first 1,170,930 1,629,070

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 1313

Reciprocal AllocationReciprocal AllocationTextbook Algebraic MethodTextbook Algebraic Method

MP

PM

33

1000,800,1

43

8000,000,1

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 1414

Reciprocal AllocationReciprocal AllocationTextbook Algebraic Method(2)Textbook Algebraic Method(2)

452,342,1

884,334,114191411

1419

8884,334,1

33

1000,800,1

43

8000,000,1

M

M

MM

MM

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 1515

Reciprocal AllocationReciprocal AllocationAlgebraic Method(3)Algebraic Method(3)

000,800,2

132,183,3

680,840,1

452,342,133

1000,800,1

PBudgetedM

PM

P

P

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 1616

Reciprocal MethodReciprocal MethodFinal Allocations to A & BFinal Allocations to A & B

AA BB

1,342,4521,342,452 xx 8/338/33 325,443325,443

1,342,4521,342,452 xx 24/3324/33 976,329976,329

1,840,6801,840,680 xx 20/4320/43 856,130856,130

1,840,6801,840,680 xx 15/4315/43 642,098642,098

1,181,5731,181,573 1,618,4271,618,427

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 1717

Problems with Textbook Reciprocal Problems with Textbook Reciprocal Allocation MethodAllocation Method

In the preceding method the sums In the preceding method the sums attributed to each service department attributed to each service department include added amounts which defy easy include added amounts which defy easy explanation and leave the reciprocal costs explanation and leave the reciprocal costs useful only for allocation to final product.useful only for allocation to final product.

These reciprocal costs include only inward These reciprocal costs include only inward flows of resources so cannot be used to flows of resources so cannot be used to provide useful information to service provide useful information to service department managers.department managers.

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 1818

Problems with Textbook Reciprocal Problems with Textbook Reciprocal Allocation MethodAllocation Method

Traceability of cost flows between service Traceability of cost flows between service departments is limited.departments is limited.

Solution of problems becomes Solution of problems becomes cumbersome when service departments cumbersome when service departments become numerous.become numerous.

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 1919

Alternative Solution: The MathAlternative Solution: The Math

Rewriting the equations for each service Rewriting the equations for each service department to include both outflows and department to include both outflows and inflows allows the full specification of a inflows allows the full specification of a matrix that can be used with a common matrix that can be used with a common Excel function to provide reciprocal costs Excel function to provide reciprocal costs that do not include double counted that do not include double counted amounts and provide for traceability of amounts and provide for traceability of cost flows between service departmentscost flows between service departments..

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 2020

Same example: new methodSame example: new method..

PMP

MPM

45

8

35

1000,800,1

35

1

45

8000,000,1

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 2121

Reciprocal Cost New Method (2)Reciprocal Cost New Method (2)

000,800,145

81

35

1

000,000,145

8

35

11

PM

PM

IMA Hands-on: Simpler than ABCIMA Hands-on: Simpler than ABC 2222

Constructing the MatrixConstructing the Matrix