James Academy is a private school providing K-12 education. The academy operates three divisions: Lower, Middle, and Upper Schools. Two additional divisions provide support to these operating divisions: Administration and Human Resources. Direct cost data for each division are provided in the following partial spreadsheet. The percentage of costs that should be allocated from each support division to each of the other divisions also is reported in the spreadsheet.

Required Use the data provided to determine the total cost of each of the school divisions after allocation of support costs. Accomplish this task as follows: (1) Copy the data into a spreadsheet. (2) Use the simultaneous equations method to determine the costs of the support divisions that need to be allocated to the operating divisions. You will need to make these calculations and enter them in the spreadsheet. (3) Below the data and calculations you have entered, prepare a cost allocation schedule similar to Exhibit 6 in this chapter. Use cell references and formulas where possible. Use appropriate formatting throughout.

Transcribed Image Text:

1 A Cost and Allocation Data D E F G 2 Administration Human Resources Lower School Middle School Upper School Total 3 Direct Costs 560,000.00 325,000.00 1,300,000.00 1,200,000.00 1,486,500.00 4,871,500.00 4 Administration % 10% 30% 5 Human Resources % 20% 20% 30% 25% 30% 35%