If possible, please show your answer using excel. Thank you!!

SUPPORT OPERATING Total AS IS GOVT CORP Budgeted overhead costs before any interdepartment cost allocations 300,000 S 3,000,000 S 8,850,000 S 12,580,000 S 24,730,000 Support work supplied by AS (budgeted head count) Support work supplied by IS (budgeted computer time) 100% 25% 30% 100% 10% 27% 63% Support Departments Operating Departments Total Direct Method AS IS GOVT CORP Budgeted overhead costs before interdepartment cost allocations300,000 $ 3,000,000 S 8,850,000 S 12,580,000 S 24,730,000 Allocation of AS costs Allocation of IS costs Total budgeted overhead of operating 120,000 (3,000,000) 900,000 2,100,000 180,000 (300,000) S 9,930,000 S 14,800,000 S 24,730,000 departments Support Departments Operating Departments Total Step-down Method AS IS CORP Budgeted overhead costs before interdepartment cost allocations300,000$ 3,000,000 S 8,850,000 S 12,580,000 S 24,730,000 75,000 (300,000) 135,000 Allocation of AS costs 90,000 3075,000 (3,075,000) 922,500 2,152,500 Allocation of IS costs Total budgeted overhead of operating departments S 9,907,500 S 14,822,500 S 24,730,000 Support Departments Operating Department Step-down Method Budgeted overhead costs AS IS GOVT CORP Total before interdepartment cost allocations 300,000 S 3,000,000 S 8,850,000 12,580,000 S 24,730,000 00,000 3,00000 810,000 1,890.000 Allocation of IS costs 600,000 (600,000) 360,000 240,000 Allocation of AS costs Total budgeted overhead of operating departments S 10,020,000 $ 14,710,000 S 24,730,000 GOVT CORP 1,080,000 $ 1,057,500 1,170,000 2,220,000 2,242,500 2,130,000 Direct method Step-down (AS first) Step-down (IS first) Requirement 1a. Allocate the two support departments' costs to the two operating departments using the reciprocal method First, determine the linear equations for each support department. $300,000+ 0.10 IS AS- $3,000,000+ 0.25 AS IS- Now allocate the support departments' costs (AS and IS department costs that you determined in the prior step) to the two operating departments using the reciprocal method. (Round decimal calculations to three decimal places, .XXX, and your final answers to the nearest whole dollar. Use parentheses or a minus sign when decreasing departments by allocating costs. Enter a "O" for any zero balances.) Now allocate the support departments' costs (AS and IS department costs that you determined in the prior step) to the two operating departments using the reciprocal method. (Round decimal calculations to three decimal places, Xxx, and your final answers to the nearest whole dollar. Use parentheses or a minus sign when decreasing departments by allocating costs. Enter a "0 for any zero balances.) Operating Departments Support Departments GOVT Total Reciprocal Method Budgeted overhead costs AS IS CORP before interdepartment cost allocations Allocation of AS costs Allocation of IS costs Total budgeted overhead of operating departments Requirement 1b. Allocate the two support departments' costs to the two operating departments using the reciprocal method. Use repeated iterations. (Round your final answers to the nearest whole dollar. Use parentheses or a minus sign when decreasing departments by allocating costs. Enter a O" for any zero balances.) Support Departments Operating Departments Reciprocal Method Budgeted overhead costs AS IS GOVT CORP Total before interdepartment cost allocations First allocation of AS costs First allocation of IS costs Second allocation of AS costs Second allocation of IS costs Third allocation of AS costs Third allocation of IS costs Fourth allocation of AS costs Fourth allocation of IS costs Fifth allocation of AS costs Fifth allocation of IS costs Total budgeted overhead of operating departments _ Requirement 2. Compare and explain differences in requirement 1 with those of the direct method and the step down method. Which method do you prefer? Why? Begin by entering the total support-department costs allocated to each operating department under each of the methods below GOVT CORP Direct method Step-down (AS first) Step-down (IS first) Reciprocal The four methods differ in the level of support department allocation across support departments .The step-down method partially recognizes services to other departmentsThe reciprocal method recognizes all the interactions and is thus the most accurate