Question

Required: Please create a model in a second tab in the Jasmine Co Excel file that employs the step method to properly distribute the Support department costs to the operating departments. The model's format should look like that presented in Exhibit 15-5 in the text. The model should only be made up of formulae and cell references: NO NUMBERS should be directly keyed into any cell in your solution tab. I should be able to change any number in the original question and that correction should flow through to produce the correct answer based on your modelling,

Answer 1

Solution : The department which has highest overhead is allocated first Support Department Operating Department Power General Factory Purchasing Pesticide Liq Factory $90,000 $314,000 $167,000 $78,900 $107,800 Total $757,700 Overhead Allocation General Factory Purchasing Power Total $314,000 $39,250 $206,250 $39,250 $20,625 $149,875 $0 $109,900 $123,750 $99,9171 $412,467) $125,600 $61,875 $49,958 $345,233 $0 $0 $0 $757,700 $0 $0

H 1 2 3 4 Power General Facton Purchasing Pesticide Liq Factory Total 5 Overhead 90000 314000 167000 78900 107800 =SUM(C5:H5) 6 7 Square feet 1500 1500 4200 4800 =SUM(C7:H7) 8 Machine hours 1403 1345 24000 8000 =SUM(C8:H8) 9 Purchase order 20 40 7 120 60 =SUM(C9:H9) 10 11 Solution : 12 The department 13 14 Support Department Operating Department 15 Power General Factory Purchasing Pesticide Liq Factory Total 16 =A5 =C5 =D5 =E5 =G5 =H5 =SUM(C16:G16) 17 Allocation 18 General Factory --$D18/$17*C7 |=-D16 --$D18/$17*E7 --$D18/$17*G7 =-$D18/$17*H7 =SUM(C18:18) 19 Purchasing --$E19/($C9+$G9+$H9) *09 --E16-E18 --$E19/($C9+$G9+$H9)*G9 --$E19/($C9+$G9+$H9) *H =SUM(C19:G19) 20 Power =-C16-C18-C19 - $C20/($G9+$H9) *G9 - $C20/($G9+$H9) *H9 I=SUM(C20:20) 21 Total =SUM(C16:C20) =SUM(D16:D20 =SUM(E16:E20) =SUM(F16:F20) =SUM(G16:20) |=SUM(H16:H20) 22