Support department cost allocation, reciprocal method (continuation of 14-26). Assume the same facts as in Exercise 14-26.

Required 1. Allocate the two support department costs to the two operating departments using the reciprocal method.

2. Compare and explain differences in requirement 1 with those in requirement 1 ofExercise 14-26. Which method do you prefer?

Excel Application For students who wish to practise their spreadsheet skills, the following is a step-by-step approach to creating an Excel spreadsheet to use repeated iterations to work requirement 1 of Exercise 14-26.

Step-by-Step 1. Open up a new spreadsheet. At the top, create an “Original Data” section for the departmentcost and support relationship data provided by Phoenix Consulting. Enter the department costs for A/HR, IS, GOVT, and CORP and the level of support relationships (expressed in %) among these departments in exactly the same format as presented above.

(Program your spreadsheet to perform all necessary calculations. Do not “hard-code” any amounts, such as your cost allocation, requiring addition, subtraction, multiplication, or divi¬

sion operations.)

2. Skip two rows and create a section “Cost Allocation—Reciprocal Method” using a similar format to Exhibit 14-9

(p. 570). Create columns for the support departments (A/HR and IS), the operating departments (GOVT and CORP), and for “Totals.” Create rows for

“Department Costs Before Cost Allocations, 1st Allocation ofA/HR Costs, 1st Allocation of IS Costs, 2nd Allocation ofA/HR Costs, and 2nd Allocation ofIS Costs.” You will add more rows as more iterations are needed.

3. Use the department cost data from your Original Data section to fill in the row for Department Costs Before Cost Allocations. Next, use the data on the level ofsupport rela¬

tionships among the departments from your Original Data section to calculate the 1st and 2nd allocations of A/HR and IS costs. Continue to add rows for further iterations of cost allocations until the costs remaining to be allocated are sufficiently close to zero, at which time allocate any costs remaining in a support department to CORP. When done, include calculations on separate rows for “Total Costs of Operating Departments” and “Total Support Department Costs Allocated to Operating Departments.”

4. Check the accuracy ofyour spreadsheet: Go to your Original Data section and change the level of support relationships between the A/HR department and the IS, GOVT, and CORP departments from 25%, 40%, 35% to 25%, 50%, 25%. If you programmed your spread¬

sheet correctly, total costs (after all cost allocations) in the GOVT department should change to $1,805,641.03.