5 5 7 B 9 Problem Ithacor Training Inc. started as a single facility for job retraining in Ithaca, New York. The company recently expanded to a new, much larger, second facility in Cortland. The company provides meals from the cafeteria in the Cortland facility to the employees at both locations. Ithacor Training also has a common cleaning crew, and an HR department that provides hiring and job placement for the retrained workers. The company is interested in how the support costs can best be assigned to the facilities which should be held responsible. Information about the costs for the support departments and the consumption of that support is provided here. 0 1 2 23 24 Operating costs incurred by department Cost per year to operate Cost allocation bases and usage by department Number of employees served Square footage cleaned Number of employees hired Human Cafeteria Janitorial Resources $1,450,000 $160,000 $98,000 $ 1,708,000 Cafeteria 35,000 20 Janitorial 75 75 Human Resources Total Service Department Costs 50 10,000 Ithaca 300 50,000 300 Cortland ALIAGE 75 105,000 75 H Total Benchmark 25 Instructions 26 There are four parts to this problem. Use Excel to perform the following: 27 a. Set up the iterative formulas to compute the costs allocated to each support department using the 500 200,000 470 28 reciprocal method. 29 b. Use formulas to calculate the amount allocated from the three service departments to the two locations, use the reciprocal method. 30 Dennnge untered column short buon the alle Antal a. Total costs plus resources consumed Department's own costs Allocate Cafeteria Allocate Janitorial Allocate HR Total costs plus resources consumed by each support department Cafeteria Janitorial b. Allocation of support costs using the reciprocal method Cafeteria Janitorial Department's own costs Cafeteria Janitorial Human Resources Total costs after allocations Human Resources Resources Ithaca Cortland Total