Total Net Sales ($6,875,000 Hartford; $5,625,000 Boston) 12500000
Fixed Costs
Partly Traceable and Controllable 400000
Partly Traceable but Noncontrollable 350000
Nontraceable Costs 325000
=SUM(D3:D5)
Total Net Sales
Boston 5625000
Hartford Total Sales 6875000
Hartford Clothing 0.6
=A10 Cycle&Run 0.4
Cost of Goods Sold (Variable) Percent of Sales
=A8 0.6
=A10 =B10 0.7
=A11 =B11 0.5
Variable Operating Costs Percent of Sales
=A14 0.3
=A15 =B15 0.25
=A16 =B16 0.35
Total Variable Costs Percent of Sales
=A19 =D14+D19 =60% + 30%
=A20 =B20 =D15+D20 =70% + 25%
=A21 =B21 =D16+D21 =50% + 35%
Fixed Controllable Costs Percent of Total Cost
=A19 0.45
=A20 Total 0.4
=B10 0.5
=B11 0.3
Could not be Traced to Clothing or Cycling at Hartford 0.2
Could not be Traced to Boston or Hartford 0.15
Fixed Noncontrollable Costs Percent of Total Cost
=A14 0.4
=A15 Total 0.5
Income Statement by Business Unit
Combined
Company Not Allocated =A8 =A10
Net Sales =E48+F48 =D8 =D9
Variable Costs:
COGS =E50+F50 =E48*D14 =((D10*D15)+(D11*D16))*F48
Operating Costs =E51+F51 =D19*E48 =((D10*D20)+(D11*D21))*F48
Less: Noncontrollable Fixed Costs =D4 =D4*D42 =D4*D37 =D4*D38
Contribution by Profit Center =C56-C58 =C59-E59-F59 =(E56-E58) =(F56-F58)
Less: Nontraceable Costs =D5
Operating Income =C59-C61
Breakdown of contribution: =F47
=F47 Not Allocated =B10 =B11
Net Sales =F48 =F48*D10 =F48*D11
Variable Costs:
COGS =F50 =D15*E68 =D16*F68
Variable Operating Costs =F51 =D20*E68 =D21*F68
Total Variable Costs =F52 =SUM(E70:E71) =SUM(F70:F71)
Contribution Margin =F53 =(E68-E70-E71) =(F68-F70-F71)