COMPUTING MARGIN OF SAFETY FOR A SMALL BUSINESS in excel Ple

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)

Solution

Answer

Sales Revenue

XXX

(-) All variable costs

(XXX)

Contribution Margin

XXX

(-)Fixed Cost

(XXX)

Net Income/(Loss)

XXX

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