A1 B fx Function: SUM; Formulas: Add; Multiply, Divide; Cell Refer F G C 1 Function: SUM; Formulas: Add; Multiply; Divide; Cell Referencing 2 3 E14.2a-Using Excel to Allocate Support Department Costs Using the Direct Method 13 14 15 16 17 18 19 20 21 22 D Initial cost Square feet occupied IT labor hours used E 4 PROBLEM 5 The Screamin' Eagles Company makes wheels and wings for ground transport vehicles and drones. In 6 addition to the key manufacturing departments, Screamin' Eagles needs two key support services, provided 7 by its Maintenance and IT departments. The Maintenance costs are allocated to other departments based on 8 the square footage of space each department occupies. The IT department allocates its costs to other 9 departments based on the amount of time the IT employees spend assisting the other departments. The 10 cost information of all four departments, along with their usage information for both allocation bases, is as 11 follows: 12 Support Departments Maintenance Operating Departments Wheels Wings IT $ 125,000 $80,000 $ 415,000 $ 650,000 1,200 2,400 2,850 2,000 1,000 neiB 5,600 2,150 H Totals $1,270,000 11,200 6,000 1 J K Initial cost Allocations Maintenance IT Student Work Area Required: Provide input into cells shaded in yellow in this template. Input the required mathematical formulas or functions with cell references to the Problem area or work area as indicated. Total Allocate the support department costs using the direct method to determine the total costs for which each operating department should be accountable. Support Departments Maintenance 125,000 $ $ M $ (125,000) $ IT 80,000 $ (80,000) N 7 $ O Operating Departments Wheels Wings Totals 650,000 $1,270,000 415,000 $ 37,500 45,600 498,100 $ 87,500 34,400 771,900 $1,270,000 a R S
recastim Alowi
