I especially need help with the Departmemtal Expense Allocation Spreadsheet!

Management at DTI Inc. has requested you prepare departmental allocation sheet. DTI has two service departments (Janitorial Services and Administration) and three operating departments (Electronics, Appliances, and Garden Center). These departments incur several indirect expenses. These expenses must be allocated across the five departments. Relevant indirect expense information, and allocation base is provided in Table 1. Table 2 provides the direct cost information and allocation base information for the service and operating departments. Janitorial services' cost is allocated to the operating departments based on the janitorial hours used for each department. The administration cost is allocated to the operating departments based on number of employees. Required: Part 1: Prepare the supporting cost allocation schedules for indirect costs and service departments using the templates in the worksheet titled "Supporting Schedules". Enter the department names and the allocation bases in the pink background cells in the order provided in Table 2. The blue background cells require you to use cell references and or formulas. Allocation of the Rent expense has been done for you. Part 2: Complete the departmental expense allocation sheet provided in the worksheet titled "Expense Allocation". Enter the department names and the allocation bases in the pink cells in the order provided in Table 2. The blue cells require you to use cell references and or formulas. Allocation of the Rent expense has been done for you. TABLE 1 INDIRECT EXPENSE RENT UTILITIES ADVERTISING TOTAL COST $145,000 60,000 96,000 ALLOCATION BASE Square footage occupied Square footage occupied Percentage of total sales Sq. footage Sales TABLE 2 DEPARTMENT JANITORIAL DEPT ADMINSTRATION ELECTRONICS APPLIANCES GARDEN CENTER DIRECT COSTS $55,000 90,000 190,000 225.000 185,000 Janitorial hrs of Employees 500 700 3600 600 10 4000 6 3000 450 8 720 $320,000 384,000 272.000 Supporting expense allocation calculations RENT expense: 5 JANITORIAL DEPT ADMINSTRATION ELECTRONICS APPLIANCES GARDEN CENTER TOTAL $145,000 Sq. Feet 500 700 3,600 4,000 3,000 11.800 % of Total 4% 6% 31% 34% 25% 100% Cost $6,144 $8,602 $44,237 $49,153 $36,864 $145.000 9 10 11 12 UTILITIES expense: 13 Sq. Feet % of Total Cost 14 15 16 17 18 14 TOTAL 20 21 ADVERTISING expense: 22 Sales % of TOTAL COST 23 24 25 26 TOTAL 27 28 JANITORIAL DEPT expense: 24 Janitor Hrs % of TOTAL COST 30 31 32 TOTAL 33 34 35 ADMIN DEPT expense: 36 # Employees % of TOTAL COST 37 34 40 TOTAL Die Departmental Expense Allocation Spreadsheet For Year Ended November 30, 2020 Allocation Expense Account Base Balance Janitorial Dept Allocation of Expenses to Departments Administrative Electronics Dept. Dept. Appliances Garden CW Dept Dept. Direct expenses Indirect expenses Rent SR $145.000 6144 8.602 44,237 49,153 $36.864 Total Dept Expenses Service Department penses Total esp, allocated to operating dept