Simpler Than ABC: New Ideas for Using Microsoft Excel for Allocating Costs
Anthony Craig Keller · Management accounting quarterly · 2005
A SIMPLE TOOL THAT ALL ACCOUNTANTS ARE FAMILIAR WITH CAN BE USED TO ALLOCATE SERVICE DEPARTMENT COSTS. EXECUTIVE SUMMARY This article demonstrates an improved method of service department cost allocation using the functionality of spreadsheets to reintroduce a technique first presented in 1965 in The Accounting Review. The method suggested has been made more accessible, and therefore more useful, to practitioners with the functionality of spreadsheets. The solution method presented here also provides the user with a tool to directly assess intermediate (service department) costs and improved traceability. (ProQuest Information and Learning: ... denotes formula omitted.) Reasons both for and against the use of service department allocations bring up the need for accuracy, as well as the perception of accurac y, in the allocation method. Many reasons against allocating these costs refer to the arbitrary nature of the allocation method and the dist orting influence of these costs. The accuracy of the assignment of cost is in large part a function of the choice of the correct allocation base, but, having chosen the allocation base, the method used must provide a rational and believable basis of allocation. In the face of the increased use of activity-based costing systems, the traditional methods of service cost allocations have been replaced. In some settings where ABC may be too costly or cumbersome or does not fit the management style, service cost allocations remain in use. Even in cases where it would be appropriate to use reciprocal costing it has been noted that the reciprocal costing method may be passed up in favor of sequential or direct methods. Common reasons cited for this include lack of computing power, lack of training on solving systems of equations, difficulty in explaining results to nonfinancial managers, and the possibility of minimizing the inaccuracies by carefully selecting the proper sequencing of costs. I address almost all of the possible problems mentioned and, by eliminating some of the inaccuracies and complexities of the usual textbook solution, open the door to more widespread use of the method in practice. A TWO-DEPARTMENT EXAMPLE USING EXCEL A typical example of the reciprocal method involves the presentation of the relationships as formulas containing both the cost to be assigned from a department and the percentage of the costs of other service departments to be assigned to that department. Consider the following example with two service departments and two products: Budget costs of $1,000,000 and $1,800,000 are traced to Maintenance and Personnel, respectively. Costs are to be allocated to Product A and B according to the value of assets for Maintenance and number of workers for Personnel. Table 1 gives the allocation bases. The typical reciprocal method of solution would cons truct formulas like the following: Formula 1: M = 1,000,000 + 8/43(P) or M = 1,000,000 + 8/45(P) + 2/35(M) P = 1,800,000 + 1/33(M) or P = 1,800,000 + 1/35(M) + 2/45(P) It becomes messy at this point because the solutions for M and P are not the reallocated costs but are instead amounts used to allocate costs to production departments. The solutions include added costs that are double counted because of the reciprocity. This means that the intermediate solutions have no easily explainable meaning and thus are not useful in decision making or performance evaluation at the service department level. In addition, according to Manes and Livingstone, the specification of the above formulas are incorrect.1 Manes would construct the formulas to solve this pro blem in the following way: Formula 2: M = 1,000,000 + 8/45(P) - 1/35(M) P = 1,800,000 + 1/35(M) - 8/45(P) Notice that the equations for M and P include negative coefficients to indicate the outward flow of resources to be used by the other service department. …