Question

COMPUTING MARGIN OF SAFETY FOR A SMALL BUSINESS (in excel)

Please explain the work through

Sam Calagione owns Dogfish Head Craft Brewery, a microbrewery in Rehobeth Beach, Delaware. He charges distributors as much as $100 per case for his premium beers such as World Wide Stout. The high-priced microbrews bring in $800,000 in operating income on revenue of $7 million. Calagione reports that his raw ingredients and labor costs for one case of World Wide Stout are $30 and $16, respectively. Bottling and packaging costs are $6 per case. Gas and electric costs are about $10 per case.

If we assume that World Wide Stout is representative of all Dogfish microbrews, then we can compute the company's margin of safety in five steps. First, variable cost as a percentage of sales is 62% [($30 + $16 + $6 + $10)/$100]. Second, the contribution margin ratio is 38% (1 − 0.62). Third, Dogfish's total fixed cost is $1,860,000 [($7,000,000 × 0.38) − $800,000]. Fourth, the break-even point in dollar sales is $4,894,737 ($1,860,000/0.38). Fifth, the margin of safety is $2,105,263 ($7,000,000 − $4,894,737)

Answer 1

• All working form part of the solution/explanation.
• First thing you need to know is the conceptual calculation of Net Income from Revenues. It goes like this:--

Sales Revenue	XXX
(-) All variable costs	(XXX)
Contribution Margin	XXX
(-)Fixed Cost	(XXX)
Net Income/(Loss)	XXX

• Now, Break Even point is calculated by dividing Total Fixed Cost by Contribution per unit.
• Total Sales Revenue = Break Even Sales + Margin of Safety Sales,
  Hence, Margin of Safety = Total Sales Revenue – Break Even Sales
• Applying the above concepts and calculation on available data---

	Amount	Per case	No of cases
Sales Revenue	7000000	(given) 100	70000
(-) All variable costs
Raw ingredients	2100000	30	70000
Labor Costs	1120000	16	70000
Bottling	420000	6	70000
Gas/electricity	700000	10	70000
Contribution Margin	2660000	38	70000

• As seen above, contribution margin is $2660000 (which is $38 per case)
• Now Contribution – Fixed Cost gives us Net Income. Net Income is already given. Hence, Fixed Cost can be calculated as Fixed Cost = Contribution Margin – Net Income.
  = 2660000 – 800000 = $1,860,000 (matches the amount as calculated in question)
• Contribution Margin Ratio = Contribution Margin / Total Sales Revenue
  = 2660000 / 7000000 = 38%
• Break Even point in Sales Dollars = Fixed Cost / Contribution Margin Ratio
  = 1860000 / 38% = $ 4,894,737
• As discussed earlier, Total Sales Revenue = Break Even Sales + Margin of Safety Sales
  Hence, Margin of Safety Sales = Total Sales Revenue – Break Even Sales
  = 7000000 – 4894737 = $2,105,263