Question

Spreadsheet Tutorials: Decentralized Operations, Absolute and Relative Cell References SA 23-1: Service department charges The Kirkland Company has three central service departments: sales administration, credit and human resources. The expenses for the three departments are as follows for the year ended December 31 20X1: Sales administration $120,000 Credit 84,000 Human resources 185,000 Total $309,000 Service department expenses are allocated to divisions based on an appropriate activity base. The activity bases associated with each service department to each division for 20X1 is as follows: Service Departments Northern Division Southern Division Total Sales administration 1,650 3,850 5,500 sales orders Credit 4,900 9,100 14,000 customers Human resources 400 1,000 employees 500 a. Open the Excel file SA23-1 b. Prepare a report showing the service department charges allocated to each division c. When you have completed the report, perform a "save as," replacing the entire he name with the following: SA23-1_your first name initiall your last name? In problem SA 23-1, how are the sales administration charges calculated for the Northern Division? Ca. Departmental expenses times (Northern sales orders divided by total sales orders)

Answer 1

The allocation of service department shall be made among the northern and southern division, basis that is most appropriate for the given expense of service department.

Allocation of Service Department to Northern and Southern Divisions
Service Department	Basis	Northern	Southern	Total
Sales Administration	Sales Orders	=120000*1650/5500	=120000*3850/5500	=SUM(C14:D14)
Credit	Customers	=84000*4900/14000	=84000*9100/14000	=SUM(C15:D15)
Human Resources	Employees	=185000*400/1000	=185000*600/1000	=SUM(C16:D16)
Total Allocation		=SUM(C14:C16)	=SUM(D14:D16)	=SUM(E14:E16)

Allocation of Service Department to Northern and Southern Divisions
Service Department	Basis	Northern	Southern	Total
Sales Administration	Sales Orders	36000	84000	120000
Credit	Customers	29400	54600	84000
Human Resources	Employees	74000	111000	185000
Total Allocation		139400	249600	389000