There are several methods (direct, step, reciprocal) to allocate the cost of service departments to operating departments. Most cost accounting textbooks describe the direct and step methods in detail, but only briefly refer to the reciprocal method. Although the reciprocal method is generally regarded as the most accurate, it is also the most complicated and often requires the use of simultaneous equations and matrix algebra. Kaplan and Atkinson (1998) report that the reciprocal method was initially recommended by the Cost Accounting Standards Board (CASB), but based on industry concerns regarding the complexity of the method, the CASB modified the proposed standard to allow either the reciprocal or step methods, or the direct method if its results approximated the results of the other two methods.

Electronic spreadsheet software has greatly facilitated the reciprocal method by providing matrix algebra functions. This paper illustrates the use of Excel to perform the reciprocal method on simplified textbook problems. As such, the value of this paper is largely pedagogical. However, the paper also makes an original empirical contribution by comparing the relative accuracy of the direct and step methods. Using simulation, allocation error data are computed for the direct method and two popular versions of the step method. The results show that the mean absolute relative error (MARE) of the step method is significantly less than the MARE of the direct method.

Accounting textbooks (e.g., Horngren et al. 2000, Blocher et al.1999) typically describe three methods for allocating the costs of service departments to operating departments. Allocating the costs of service departments is complicated by "reciprocal relationships" among the service departments, where the service departments provide support to each other. The direct method completely ignores reciprocal relationships by allocating service department costs directly to operating departments. The step method gives partial recognition to reciprocal relationships by allocating service department costs in a specific sequence. The reciprocal method fully models the reciprocal relationships by a series of simultaneous equations. Accordingly, the reciprocal method is the most accurate but also the most difficult to implement.

In a recent simulation, Jacobs and Marshall (1999:45) found that the mean absolute relative errors created by the direct and step methods can be quite large and vary a great deal. However, they did not report standard deviations and the results of any difference testing. Without this information, it is not possible to tell whether the mean allocation errors across the three methods are statistically different.

A simple example of the service department cost allocation problem is found in Horngren et al. (2000:531). In the problem, a company has two service departments (S1 and S2) and two production departments (P1and P2). ⁽¹⁾ As shown in Table 1, the cost of a power-generating department (S1) is allocated using kilowatt-hours. The cost of a materials-handling department (S2) is allocated using labor hours. The task is to allocate the costs of S1 and S2 to P1 and P2 using the direct, step, and reciprocal methods. The solution is summarized in Table 2.

The solution is easily accomplished in Excel (Exhibit 1). ⁽²⁾ Data from Table 1 appear at the top of the spreadsheet. Using a spreadsheet to solve such problems is useful for exploring the impact of uncertainty on decisions related to service department cost allocations. In this example, the spreadsheet uses formulas linked to the data in Table 1. If the input data are changed, the solutions are immediately computed, thus facilitating a sensitivity analysis of the solution to the service department costs or the allocation bases.

As shown in the spreadsheet file (Exhibit 1), the solution to the direct method is straightforward, with the allocations based on the hours consumed by P1 and P2. There are two solutions to the step method because there are two possible sequences for allocating the costs of S1 and S2. The costs of either S1 or S2 may be allocated first, with the cost of the remaining service department allocated second.

Accounting textbooks suggest ways to minimize the allocation error created by the step method. One way is to sort the service departments based on percentages of service provided to the other service departments, termed "Step (percentage)" in Table 2. Another is to sort on service department cost, termed "Step (cost)" in Table 2. Either method will likely result in some allocation error because the reciprocal relationships occur in more than one direction.

The solution to the reciprocal method is illustrated in four stages. First, the relative amount of each allocation base that is consumed by each department is computed. Second, a coefficient matrix is created from the simultaneous equations describing the reciprocal relationships. Third, the coefficient matrix is inverted using the Excel function "MINVERSE." Finally, the inverted coefficient matrix is multiplied against the vector of service department costs using the Excel function "MMULT." ⁽³⁾ The product is the costs allocated to P1, P2, S1 and S2.

The foregoing example had only two service departments and two production departments. Other tabs in the spreadsheet file (Reciprocal.xls) contain templates for problems with three service departments and three production departments. Of course, more realistic problems require more service and production departments. Thus, the value of this spreadsheet application is primarily pedagogical. Based on classroom experience, students quickly learn to create their own spreadsheet models. Once created, the spreadsheet creates an opportunity for sensitivity analysis that generates insight into the service department cost allocation problem and its impact on strategic business decisions such as pricing or outsourcing.

Table 3 shows the allocation errors from the direct and step methods. In this example, the direct method has the smallest error, but this is not always the case. In general, one would expect the step method to be more accurate than the direct method because the step method gives only partial recognition to reciprocal relationships between service departments.

To test this expectation, the input cells in the spreadsheet (Reciprocal.xls) were changed to random variables using Excel's RAND function (Exhibit 2). Because all the formulas are linked to the input cells, the cost allocations and the relative errors are immediately computed. After a few iterations (accomplished by pressing the enter button), it becomes quickly apparent that neither the direct nor the step methods will always have the smallest allocation error. However, after a large number of iterations, the differences in the mean absolute relative error of each method may be tested for statistical significance. The results of this simulation appear in Tables 4 and 5.

Table 4 shows the mean absolute relative errors (MARE) and standard deviations of the direct and step methods. The number of service departments was varied from two to four and the number of production departments was varied from two to three. For each combination of service and production departments, 500 iterations were run. The last column in the table shows the MARE for all combinations and represents 2,500 iterations for each method. Note that the MARE for the Step (percent) method is the smallest. This result is consistent with results reported by Jacobs and Marshall (1999). However, they did not test for statistically significant differences across methods.

A one-way ANOVA using SPSS indicated that the MARE across the three methods was significantly different (F = 37.3, df = 2 and 7497, p = 0.000). Results of the pairwise comparisons are shown in Table 5. ⁽⁴⁾ The step methods were more accurate than the direct method. The step (percentage) method was more accurate than the step (cost) method.

Electronic spreadsheets have made the reciprocal method more feasible by removing the computational difficulty of matrix inversion and multiplication. Moreover, by dynamically linking the results of the cost allocation to input values, electronic spreadsheets facilitate sensitivity analysis. Students, accountants, and managers can easily explore the impact of alternative allocation bases (e.g., unit-level versus batch-level drivers), uncertain cost estimates, and alternative cost allocation methods (e.g., direct, step, reciprocal) on the costs allocated to operating departments. Such sensitivity analysis is useful in making business decisions related to profit planning and in performance evaluation.

When the reciprocal method is not used, the step method is significantly more accurate than the direct method. In addition, the step (percent) method is significantly more accurate than the step (cost) method.

Blocher, Edward J., Kung H. Chen, and Thomas W. Lin. 1999. Cost Management, A Strategic Emphasis. Blacklick, Ohio: Irwin-McGraw Hill.

Horngren, Charles T., George Foster, Srikant M. Datar. 2000. Cost Accounting, A Managerial Emphasis. Tenth Edition. Upper Saddle River, NJ: Prentice Hall.

Jacobs, Fred and Ron Marshall. 1999. "Accuracy of Service Cost Allocations." The Journal of Cost Analysis and Management, Winter, pp.45-58.

Kaplan, Robert S, and Anthony A. Atkinson. 1998. Advanced Management Accounting. Third Edition. Upper Saddle River, NJ: Prentice Hall.

INPUT DATA							SUMMARY OF RESULTS
	s1	s2	p1	p2	total		Method	p1	p2
s1	0	100	250	150	500		direct	70500.0	69500.0	140000
s2	500	0	100	400	1000		step12	62000.0	78000.0	140000
cost	100000	40000			140000		step21	79000.0	61000.0	140000
							recip	73333.3	66666.7	140000
Direct	s1	s2	p1	p2	total
s1	-100000		62500	37500	0		ABSOLUTE RELATIVE ERRORS
s2		-40000	8000	32000	0			p1	p2	MARE
	-100000	-40000	70500	69500	0		direct	3.86%	4.25%	4.06%
							step12	15.45%	17.00%	16.23%
Step12	s1	s2	p1	p2	total		step21	7.73%	8.50%	8.11%
s1	-100000	20000	50000	30000	0
s2		-60000	12000	48000	0
	-100000	-40000	62000	78000	0
Step21	s1	s2	p1	p2	total
s2	20000	-40000	4000	16000	0
s1	-120000		75000	45000	0
	-100000	-40000	79000	61000	0
Reciprocal - normalized matrix
	s1	s2	p1	p2
s1	0.000	0.200	0.500	0.300	1
s2	0.500	0.000	0.100	0.400	1
Reciprocal - coefficient matrix					Service
	p1	p2	s1	s2	Cost
P1	1	0	-0.500	-0.100	0
P2	0	1	-0.300	-0.400	0
s1	0	0	1.000	-0.500	100000
s2	0	0	-0.200	1.000	40000
Reciprocal - inverted coefficient matrix					Allocated
	p1	p2	s1	s2	Cost
P1	1	0	0.578	0.389	73333
P2	0	1	0.422	0.611	66667
s1	0	0	1.111	0.556	133333
s2	0	0	0.222	1.111	66667

Exhibit 1. Reciprocal.xls with input data from example problem.

RANDOMIZED INPUT DATA							SUMMARY OF RESULTS
	s1	S2	p1	p2	total		Method	p1	p2
S1	0.671	0.999	0.052	0.533	2.256		direct	0.277	0.727	1.005
S2	0.755	0.179	0.900	0.793	2.628		step12	0.439	0.565	1.005
cost	0.579	0.425			1.005		step21	0.219	0.785	1.005
							recip	0.390	0.615	1.005
Direct	s1	S2	p1	p2	total
S1	-0.579		0.052	0.528	0		ABSOLUTE RELATIVE ERRORS
S2		-0.425	0.226	0.199	0			p1	p2	MARE
	-0.579	-0.425	0.277	0.727	0		direct	28.79%	18.24%	23.51%
							step12	12.70%	8.05%	10.38%
Step12	s1	S2	p1	p2	total		step21	43.68%	27.68%	35.68%
S1	-0.579	0.365	0.019	0.195	0
S2		-0.791	0.420	0.370	0
	-0.579	-0.425	0.439	0.565	0
Step21	s1	S2	p1	p2	total
S2	0.131	-0.425	0.156	0.138	0
S1	-0.711		0.063	0.647	0
	-0.579	-0.425	0.219	0.785	0
Reciprocal - normalized matrix
	s1	s2	p1	p2
S1	0.297	0.443	0.023	0.236	1
S2	0.287	0.068	0.342	0.302	1
Reciprocal - coefficient matrix					Service
	p1	p2	s1	s2	Cost
P1	1	0	-0.023	-0.342	0
P2	0	1	-0.236	-0.302	0
S1	0	0	0.703	-0.287	0.579
S2	0	0	-0.443	0.932	0.425
Reciprocal - inverted coefficient matrix					Allocated
	p1	p2	s1	s2	Cost
P1	1	0	0.328	0.469	0.390
P2	0	1	0.672	0.531	0.615
S1	0	0	1.767	0.545	1.256
S2	0	0	0.840	1.332	1.053

Exhibit 2. Reciprocal.xls with randomized input data.

¹ An operating department adds value to a product or service. In a manufacturing company, the operating department is often termed a "production department."

² An Excel file (Reciprocal.xls) is linked to this manuscript. I assume that the file can be viewed while reading this manuscript.

³ Similar functions are available in Quattro Pro.

⁴ These results were insensitive to the specific pairwise multiple comparison test (e.g., LSD, Bonferonni, Scheffe, Tukey) used.