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)