SIMPLER THAN ABC

1. SIMPLERTHAN ABC New Ideas for Using Microsoft Excel for Allocating Costs Reciprocal Costing IMA Hands-on: Simpler than ABC

2. OUTLINE • The purpose of the presentation is to introduce a new method of solving an old problem • The old problem is service cost allocation using reciprocal costing. • The new method uses Excel to determine the reciprocal allocations using matrix algebra and a mathematics technique known as Cramer’s Rule. IMA Hands-on: Simpler than ABC

3. Benefits • By understanding this technique Decision Support Specialists can have at their fingertips what is considered the most accurate method of cost allocation between service departments while overcoming some of the problems which have kept reciprocal costing from being more widely used in practice. IMA Hands-on: Simpler than ABC

4. Benefits • Traceability of costs • Doesn’t require specialized software • Doesn’t require extensive mathematical training to solve systems of equations. • More accurate than earlier methods of reciprocal costing IMA Hands-on: Simpler than ABC

5. Service Departments Maintenance Personnel Production Departments Product A Product B Example IMA Hands-on: Simpler than ABC

6. Maintenance $1,000,000 Allocation Basis Value of Assets Personnel $1.800,000 Allocation Basis Number of Workers Budgeted Costs & Allocation IMA Hands-on: Simpler than ABC

7. IMA Hands-on: Simpler than ABC

8. Solution Methods • Direct allocation • Sequential Allocation • Reciprocal Allocation IMA Hands-on: Simpler than ABC

9. DIRECT ALLOCATION Products A B $250,000 $750,000 $1,028,571 $771,429 $1,278.571 $1,521,429 IMA Hands-on: Simpler than ABC

10. Sequential MethodMaintenance First IMA Hands-on: Simpler than ABC

11. Sequential MethodPersonnel First IMA Hands-on: Simpler than ABC

12. Direct & Sequential Methods • Direct method is simple but less accurate • Traceability of costs are easy with sequential method but • Results are dependent on the order of allocation so sequencing needs to be justified. A B Maintenance first 1,288,311 1,511,689 Personnel first 1,170,930 1,629,070 IMA Hands-on: Simpler than ABC

13. Reciprocal AllocationTextbook Algebraic Method IMA Hands-on: Simpler than ABC

14. Reciprocal AllocationTextbook Algebraic Method(2) IMA Hands-on: Simpler than ABC

15. Reciprocal AllocationAlgebraic Method(3) IMA Hands-on: Simpler than ABC

16. Reciprocal MethodFinal Allocations to A & B IMA Hands-on: Simpler than ABC

17. Problems with Textbook Reciprocal Allocation Method In the preceding method the sums attributed to each service department include added amounts which defy easy explanation and leave the reciprocal costs useful only for allocation to final product. These reciprocal costs include only inward flows of resources so cannot be used to provide useful information to service department managers. IMA Hands-on: Simpler than ABC

18. Problems with Textbook Reciprocal Allocation Method • Traceability of cost flows between service departments is limited. • Solution of problems becomes cumbersome when service departments become numerous. IMA Hands-on: Simpler than ABC

19. Alternative Solution: The Math Rewriting the equations for each service department to include both outflows and inflows allows the full specification of a matrix that can be used with a common Excel function to provide reciprocal costs that do not include double counted amounts and provide for traceability of cost flows between service departments. IMA Hands-on: Simpler than ABC

20. Same example: new method. IMA Hands-on: Simpler than ABC

21. Reciprocal Cost New Method (2) IMA Hands-on: Simpler than ABC

22. Constructing the Matrix IMA Hands-on: Simpler than ABC