Allocating Service Department Costs with Excel - Strategic Finance (2024)

An accurate, clear understanding of service department costs is valuable in several ways. First and foremost, service department costs are used to determine the full cost of a product. They’re also valuable for rationing demand for internal services—if no price is charged for a service, for example, the service may be overconsumed by operating departments. In addition, they make it possible to assess the department’s operational efficiency. If the internal cost for a service is greater than the price charged by an external supplier, the service department could be considered for elimination.

There are three methods for allocating service department costs: direct, sequential, and reciprocal. The first step of each method is to classify each organizational unit as either an operating or service department. Operating departments directly produce or distribute the company’s output, such as machining and assembly departments. Service departments provide services and support to operating departments as well as other support departments. Examples include human resources and information systems departments.

The problem in allocating service department costs is complicated by multiple-department relationships, where each service department may provide service to all of the other departments, including other service departments and itself. The three service cost-allocation methods vary in terms of ease and accuracy because of how they approach this problem.

The direct method allocates costs to the operating departments directly, with no allocations to the other service departments. The method is easy to implement, but it ignores the fact that other service departments require services from each other, so it’s less accurate.

The sequential method (also known as the step-down method), allocates costs to operating departments and other service departments sequentially, but only in one direction. There is no set order in the sequence used: One common technique is to begin with the service department that incurs the most costs supporting other service departments and work downward to the department with the least costs. Once a service department’s cost is allocated out, however, no portion of its cost is allocated back to it from other service departments. This method partially recognizes other service departments, which makes it more accurate than the direct method.

The reciprocal method fully recognizes the other service departments by allowing reallocations back to each service department. As such, it’s more difficult to calculate but also more accurate than the other methods.

The direct method was used most in practice until the 1970s, when the Cost Accounting Standards Board (CASB) established a standard for service department cost allocation. The exposure draft for Cost Accounting Standard (CAS) 418, “Allocation of direct and indirect costs,” initially specified the reciprocal method. But according to Robert Kaplan and Anthony Atkinson in Advanced Management Accounting, defense contractors complained that “they had neither the expertise nor the computational ability to implement the method,” and thus the sequential method was specified as a reasonable alternative to the reciprocal method.

The direct method was allowed, but only if the allocations could be considered reasonably close to the allocations resulting from the sequential method. CAS 418 stipulates that “the allocation of indirect cost pools which benefit one another may be accomplished by the use of (1) the cross-allocation (reciprocal) method, (2) the sequential method, or another method the results of which approximate that achieved by either of the methods [listed previously]” (CAS 9904.418.50).

Today, cost accounting textbooks describe the reciprocal method with simple examples that involve repeated iterations or simultaneous equations (or both) to model the cost of each department. In a simple example with two service departments and two operating departments, only four simultaneous equations are needed and can be easily solved by hand. When more than two service departments are involved, accounting textbooks recommend the use of simultaneous equations, matrix algebra, and a computer to solve the equations. Unfortunately, students and financial managers find the use of matrix algebra to be quite a challenge.

Fortunately, there is now an alternative solution using the iterative calculation option in Excel (or any spreadsheet software with similar functionality) to calculate the reciprocal method more easily. Using a simple example, we’ll show how the iterative calculation option eliminates the need to manually reallocate service department costs through multiple iterations (or through simultaneous equations using matrix algebra). This method is an attractive alternative to explain and solve the service department cost allocation problem and should make the reciprocal method more accessible to managers.

THE RECIPROCAL METHOD

Consider an example using two service departments, S1 and S2, and two operating departments, P1 and P2. Each service department provides services to the other three departments. The $100 direct cost of S1 is allocated using 500 direct labor hours, with 100, 250, and 150 direct labor hours consumed by S2, P1, and P2, respectively. S2’s direct cost of $40 is allocated using 1,000 machine hours, with 500, 100, and 400 machine hours consumed by S1, P1, and P2, respectively (see Figure 1).

To illustrate the methods, it’s convenient to convert the allocation bases from hours consumed by each department to percentages of the total base for each service department (see Figure 2). To keep it simple, neither S1 nor S2 consumes its own services.

Repeated Iterations

Figure 3 shows the reciprocal method with repeated iterations. Service department costs are reallocated back to the service departments for several rounds until the reallocated costs are reduced to near-zero amounts. In the first round, S1’s cost is allocated to S2, P1, and P2 using the allocation percentages for S1 shown in row 8 of Figure 2, and S2’s direct cost of $40 and its newly allocated cost of $20 from S1 are allocated to S1, P1, and P2 using the percentages shown for S2 in row 9.

In the second round, S1’s cost of $30 (cell C14) is allocated and reallocated, resulting in a balance of $3 in S1 (cell C15). This process continues through multiple rounds until the balance in S1 is immaterial. We stopped at the sixth round, where the balance of S1 was $0.0003 (cell C23).

Although allocating and reallocating service department costs through multiple rounds successfully recognizes the reciprocal relationships of these departments, this method is tedious and impractical when there are many service departments.

Matrix Algebra

A more refined approach uses simultaneous equations and matrix algebra to model the reciprocal relationships and allocate the service department costs without the need for multiple rounds of reallocations. In this method, linear equations are created for the allocated costs of each department using the percentages in rows 8 and 9 of Figure 2. The four equations are:

1. P1 = 0.5(S1) + 0.1(S2)
2. P2 = 0.3(S1) + 0.4(S2)
3. S1 = 0.5(S2) + 100
4. S2 = 0.2(S1) + 40

Equations 1 and 2 describe the allocated costs to P1 and P2. P1’s allocated cost is 50% of S1’s cost and 10% of S2’s cost, and P2’s allocated cost is 30% of S1’s cost and 40% of S2’s cost. Equations 3 and 4 describe the “reciprocated costs” of S1 and S2. S1’s reciprocated cost is 50% of S2 and its own direct cost of $100, and S2’s reciprocated cost is 20% of S1’s cost and its own direct cost of $40.

Solving the four simultaneous equations by hand is relatively easy, but as the number of departments grows, it’s convenient to use matrix algebra and a computer to find the solution. In matrix notation, the equation for the simultaneous equations is AX = B, where A is a 4-by-4 matrix of the coefficients from the simultaneous equations, X is the costs allocated to the departments, and B is the costs allocated from the service departments. Multiplying both sides of the equation by the inverse of A results in the solution, X = A-1B.

Excel has array functions that can be used to do the matrix inversion (MINVERSE) and multiplication (MMULT). In Figure 4, the coefficient matrix is cells C28:F31, the inverted coefficient matrix is cells C34:F37, and the allocated costs are shown in cells G34:G37. Note that the costs allocated to P1 and P2 are the same as derived by the reciprocal method with multiple iterations. But like we said earlier, practitioners and students often resist the use of matrix algebra because they perceive it to be too hard.

The costs allocated to S1 and S2 are termed “reciprocated costs.” They are greater than the direct costs of S1 and S2 because costs are reallocated back to S1 and S2.

The Iterative Calculation Option

As we’ve noted, the problem with the reciprocal method with repeated iterations is the number of rounds required and increased complexity when many more departments are involved. Our simple example with two departments took six rounds. This quickly becomes unwieldy when the number of rounds needed is much larger. Our alternative method uses Excel’s “iterative calculation option” and a template for the cost allocations to have Excel itself calculate a larger number of rounds.

With the iterative calculation option enabled, Excel will allow circular references in formulas. A circular reference is when the formula in a cell refers to other cells that in turn refer to the original cell. Normally, Excel can’t automatically calculate a formula like that because it would by default keep recalculating indefinitely—almost like a basic go-to loop in programming that never ends. The iterative calculation option lets you put a limit on the number of times Excel recalculates the formula.

To enable this option, go to the Formula category of the Excel Options dialog. (In Excel 2013, go to File, Options, Formula.) In the Calculations section, select the “Enable iterative calculations” checkbox. The default settings for the number of iterations and precision are sufficient for the purposes here.

Once the iterative calculation option is enabled, cell formulas can be used in the template to make the cost allocations (see Figure 5). First create formulas to compute the “reciprocated cost” of S1 and S2. The reciprocated cost of S1 is equal to S1’s direct cost + cost allocated to S1 from other service departments. The reciprocated cost of S2 is S2’s direct cost + cost allocated to S2 from other service departments. Then allocate the reciprocated costs to the other departments.

Figure 6 shows what this would look like using our example. The reciprocated cost of S1 is the direct cost of S1 ($100) and the cost allocated to S1 from S2 ($33). Thus, the formula in cell C40 of the spreadsheet is =–C5–C41. Likewise, the reciprocated cost of S2 is S2’s direct cost of $40 and the cost allocated from S2 to S1 ($27). The formula in cell D41 of the spreadsheet is =–D5–D40. (Entering these formulas will result in a “circular error” warning message unless the iterative calculation option is enabled.) The negative values of these reciprocated costs suggest that these costs are being allocated out of S1 and S2.

The remaining cells in the template are computed by multiplying the reciprocated costs (cells C40 and D41) by the percentages in rows 8 and 9 of Figure 2. Multiply S1’s reciprocated cost of $133 by the percentages shown in the row for S1 (20%, 50%, 30%). The spreadsheet formulas in cells D40, E40, and F40 are =C40*D8, =C40*E8, and =C40*F8. Likewise, multiply S2’s reciprocated cost of $67 by the percentages shown in the row for S2 (50%, 10%, 40%). The spreadsheet formulas in cells C41, E41, and F41, are =D41*C9, =D41*E9, and =D41*F9. The positive signs for these costs indicate that the reciprocated costs were allocated into the departments. The reciprocated costs of S1 and S2, and the costs allocated to P1 and P2, are the same as calculated by the other methods.

A SIMPLER APPROACH

There are some drawbacks to this option. The reciprocal method that uses simultaneous equations and matrix algebra provides additional information that isn’t available from our suggested method. For example, the coefficients in the main diagonal of the inverted coefficient matrix (the “reciprocated factors”) can be used to compute the total variable costs avoided when considering the outsourcing of service departments. Because this information isn’t available from the alternative method described here, the simultaneous equation method that uses matrix algebra is necessary if such information is needed.

But the reciprocated costs of S1 and S2 can be converted into internal prices by dividing by the allocation bases used for S1 and S2. The internal price of S1 is $0.27 per DLH ($133.3/500 hours), and the internal price of S2 is $0.07 per machine hour ($66.7/1,000 hours). Assuming that all costs in the analysis are variable, these prices may then be compared to external prices for the same service from external suppliers and used as benchmarks to evaluate the efficiency of the internal service departments.

Despite those shortcomings, financial managers should find the reciprocal method much more accessible using this approach. Like the defense contractors that balked at the CASB’s recommendation to use the reciprocal method, many students today find the simultaneous equations method using matrix algebra too difficult to implement. It requires simultaneous equations, a coefficient matrix, matrix inversion, and matrix multiplication to get the cost allocations. Even when using Excel for the matrix functions, the method is still resisted by students whose eyes often glaze over when the use of matrix algebra is discussed—not to mention the added complexity involved in using matrix (i.e., array) formulas in Excel. And using the sequential method with repeated iterations is very tedious. No one wants to set up a spreadsheet with many rounds of cost allocations. Excel’s iterative calculation option makes the reciprocal method much easier and quicker.

You may also like