Question

Use the following data to prepare a model that allocates the support departments using the three methods (direct, sequential, and reciprocal).

Instructions:

• You should design your model to include an input section, an output section, and user instructions.
• The output section should have nothing but references and formulas in it.  
• For the sequential method, allocate the support department with the highest cost first.
• For the reciprocal method, either calculate the reciprocal amounts using a formula in Excel or use iterative equations to calculate the amounts.

	Support Departments	Producing Departments
Departments	General Factory	Receiving	Assembly	Finishing
Direct Overhead	$350,000	$185,000	$41,000	$74,000
Square Footage		3,200	5,100	4,700
# of Receiving Orders	300		1,680	1,020

Answer 1

	Assembly	Finishing
Direct Method	$ 338,254	$ 311,746
Step Down Method	$ 347,026	$ 302,974
Reciprocal Method	$ 344,892	$ 305,108

Direct Method Service Department Operating Department Support Producing Department Department Assembly Finishing Total $ 350,000 $ 185,000 $ 41,000 $ 74,000 $ 650,000 Departmental costs before allocation Allocation: Support Department-Square Footage (5100/9800; 4700/9800) Producing Department-# of Receiving Orders (1680/2700; 1020/2700) Total Costs after allocation -$ 350,000 $ 182,143 $ 167,857 $ 185,000 $ 115,111 69,889 $ $ 338,254 $ 311,746 $ 650,000 -$ $ is $ Step-down Service Department Operating Department Support Producing Department Department Assembly Finishing Total $ 350,000 $ 185,000 $ 41,000 $ 74,000 $ 650,000 Departmental costs before allocation Allocation: Support Department-Square Footage (3200/13000; 5100/13000; 4700/13000) Producing Department-# of Receiving Orders (1680/2700; 1020/2700) Total Costs after allocation -$ 350,000 $ -$ $ 86,154 $ 137,308$ 126,538 $ 271,154 $ 168,718 $ 102,436 $ 347,026 $ 302,974 $ 650,000 $

Reciprocal Method Service Department Operating Department Support Producing Department Department Assembly Finishing Total Departmental costs before allocation $ 350,000 $ 185,000 $ 41,000 $ 74,000 $ 650,000 Allocation: Support Department-Square Footage (3200/13000; 5100/13000; 4700/13000)- 377,800 $ 92,997 $ 148,214 $ 136,589 Producing Department-# of Receiving Orders (300/3000;1680/3000; 1020/3000) $ 27,800 -$ 277,997 $ 155,678$ 94,519 Total Costs after allocation $ $ $ 344,892 $ 305,108 $ 650,000 Workings Reciprocal Method Support Department = Y Producing Department =Z Y=$350,000+0.10Z ... (1) Z=$185,000+ 0.2462 Y............(2) Y=$350,000+0.10($185,000+ 0.2462 Y) Y=$350,000 +$ 18,500+ 0.02462 Y Y-0.02462 Y = $350,000 +$ 18,500 0.97538 Y = $368,500 Y = $377,800 Z=$185,000+ 0.2462Y Z=$185,000+ 0.2462 ($377,800) Z=$185,000 + $92,997 Z=$277,997

Formula Sheet:

124 B D E F G H 1 2 3 Direct Method 4 Service Department Operating Department 5 Support Department Producing Department 350000 185000 Assembly 41000 Finishing 74000 Total =D6+E6+F6+G6 6 7 8 9 Departmental costs before allocation Allocation: Support Department-Square Footage (5100/9800; 4700/9800) Producing Department# of Receiving Orders (1680/2700 ; 1020/2700) Total Costs after allocation =-D6 --E6 0 =D6*51/98 =E6*1680/2700 =SUM(F6:F9) =D6*47/98 =E6*1020/2700 -SUM(G6:59) =D8+E8+F8+G8 =D9+E9+F9+G9 =F10+G10 10 =D6+D8+D9 11 12 Step-down 13 Service Department Operating Department 14 Support Department Producing Department 350000 185000 Assembly 41000 Finishing 74000 Total -D15+E15+ 15+G15 15 16 17 Departmental costs before allocation Allocation: Support Department-Square Footage (3200/13000; 5100/13000; 4700/13000) Producing Department-# of Receiving Orders (1680/2700; 1020/2700) Total Costs after allocation --D15 =D17+E17+F17+G17 18 =D15*32/130 --E15-E17 =E15+E17+E18 =D15*51/130 =D15*47/130 --E18*1680/2700 --E18*1020/2700 =SUM(F15:F18) =SUM(G15:G18) =D15+D17+D18 =F19+G19 19 20 21 Reciprocal Method 22 Service Department Operating Department 23 Support Department Producing Department 350000 185000 Assembly 41000 Finishing 74000 Total =D24+E24+F24+G24 24 25 Departmental costs before allocation Allocation: Support Department-Square Footage (3200/13000; 5100/13000; 4700/13000) 26 2-377800 --D26*32/130 --D26*51/130 --D26*47/130 27 Producing Department# of Receiving Orders (300/3000;1680/3000 ; 1020/3000) 27800 |--E24-E26 |--E27*1680/3000 --E27*1020/3000

B с D E F G H 20 Reciprocal Method 21 22 Service Department Operating Department 23 Support Department Producing Department 350000 185000 Assembly 41000 Finishing 74000 Total =D24+E24+F24+G24 24 25 Departmental costs before allocation Allocation: Support Department-Square Footage (3200/13000; 5100/13000; 4700/13000) 26 -377800 --D26*32/130 --D26*51/130 --D26*47/130 27 28 29 Producing Department-# of Receiving Orders (300/3000;1680/3000 ; 1020/3000) Total Costs after allocation 27800 =D24+D26+D27 |--E24-E26 =E24+E26+E27 --E27*1680/3000 --E27*1020/3000 | =SUM(F24:F27) =SUM(G24:627) =F28+G28 30 31 Workings Reciprocal Method 32 33 Support Department = Y Producing Department = 2 34 35 36 Y=$350,000+0.10Z ............(1) Z=$185,000+ 0.2462 Y..............(2) 37 38 39 40 41 42 Y=$350,000+0.10($185,000+ 0.2462 Y) Y=$350,000 +$ 18,500+ 0.02462 Y Y-0.02462 Y = $350,000 +$ 18,500 0.97538 Y = $368,500 Y = $377,800 43 44 45 46 47 Z=$185,000+ 0.2462Y Z=$185,000+ 0.2462 ($377,800) Z=$185,000 + $92,997 Z=$277,997 48 49

----------------------------------------------------------------------------------------------------------------------------------

If you have any doubts, drop your messages in the comment box! Thank you!!!  ALL THE BEST!!!!
Please rate this answer, If you like the answer, kindly raise your THUMBS-UP button.
#STAY SAFE