Chapter_3_Applying_Excel.xls
Chapter 3: Applying Excel
Data
Unit sales 20,000 units
Selling price per unit $60 per unit
Variable expenses per unit $45 per unit
Fixed expenses $240,000
Enter a formula into each of the cells marked with a ? below
Review Problem: CVP Relationships
Compute the CM ratio and variable expense ratio
Selling price per unit ? per unit
Variable expenses per unit ? per unit
Contribution margin per unit ? per unit
CM ratio ?
Variable expense ratio ?
Compute the break-even
Break-even in unit sales ? units
Break-even in dollar sales ?
Compute the margin of safety
Margin of safety in dollars ?
Margin of safety percentage ?
Compute the degree of operating leverage
Sales ?
Variable expenses ?
Contribution margin ?
Fixed expenses ?
Net operating income ?
Degree of operating leverage ?
Page 1
Chapter_3_Applying_Excel.xls
Chapter 3: Applying Excel
Data
Unit sales 20,000 units
Selling price per unit $60 per unit
Variable expenses per unit $45 per unit
Fixed expenses $240,000
Enter a formula into each of the cells marked with a ? below
Review Problem: CVP Relationships
Compute the CM ratio and variable expense ratio
Selling price per unit $60 per unit
Variable expenses per unit 45 per unit
Contribution margin per unit $15 per unit
CM ratio 25%
Variable expense ratio 75%
Compute the break-even
Break-even in unit sales 16,000 units
Break-even in dollar sales $960,000
Compute the margin of safety
Margin of safety in dollars $240,000
Margin of safety percentage 20%
Compute the degree of operating leverage
Sales 1,200,000$
Variable expenses 900,000
Contribution margin 300,000
Fixed expenses 240,000
Net operating income 60,000$
Degree of operating leverage 5.00
Page 2
Chapter_3_Applying_Excel.xls
Chapter 3: Applying Excel
Data
Unit sales 20000 units
Selling price per unit 60 per unit
Variable expenses per unit 45 per unit
Fixed expenses 240000
Enter a formula into each of the cells marked with a ? below
Review Problem: CVP Relationships
Compute the CM ratio and variable expense ratio
Selling price per unit =B5 per unit
Variable expenses per unit =B6 per unit
Contribution margin per unit =B13-B14 per unit
CM ratio =B15/B13
Variable expense ratio =B14/B13
Compute the break-even
Break-even in unit sales =B7/B15 units
Break-even in dollar sales =B7/B17
Compute the margin of safety
Margin of safety in dollars =B4*B5-B22
Margin of safety percentage =B25/(B4*B5)
Compute the degree of operating leverage
Sales =B4*B13
Variable expenses =B4*B6
Contribution margin =B29-B30
Fixed expenses =B7
Net operating income =B31-B32
Degree of operating leverage =B31/B33
Page 3
Chapter_3_Applying_Excel.xls
Chapter 3: Applying Excel
Data
Unit sales 20,000 units
Selling price per unit $60 per unit
Variable expenses per unit $45 per unit
Fixed expenses $270,000
Enter a formula into each of the cells marked with a ? below
Review Problem: CVP Relationships
Compute the CM ratio and variable expense ratio
Selling price per unit $60 per unit
Variable expenses per unit 45 per unit
Contribution margin per unit $15 per unit
CM ratio 25%
Variable expense ratio 75%
Compute the break-even
Break-even in unit sales 18,000 units
Break-even in dollar sales $1,080,000
Compute the margin of safety
Margin of safety in dollars $120,000
Margin of safety percentage 10%
Compute the degree of operating leverage
Sales 1,200,000$
Variable expenses 900,000
Contribution margin 300,000
Fixed expenses 270,000
Net operating income 30,000$
Degree of operating leverage 10.00
Page 4
Chapter_3_Applying_Excel.xls
Chapter 3: Applying Excel
Data
Unit sales 10,000 units
Selling price per unit $120 per unit
Variable expenses per unit $72 per unit
Fixed expenses $420,000
Enter a formula into each of the cells marked with a ? below
Review Problem: CVP Relationships
Compute the CM ratio and variable expense ratio
Selling price per unit $120 per unit
Variable expenses per unit 72 per unit
Contribution margin per unit $48 per unit
CM ratio 40%
Variable expense ratio 60%
Compute the break-even
Break-even in unit sales 8,750 units
Break-even in dollar sales $1,050,000
Compute the margin of safety
Margin of safety in dollars $150,000
Margin of safety percentage 13%
Compute the degree of operating leverage
Sales 1,200,000$
Variable expenses 720,000
Contribution margin 480,000
Fixed expenses 420,000
Net operating income 60,000$
Degree of operating leverage 8.00
Page 5
Chapter_3_Applying_Excel.xls
Chapter 3: Applying Excel
Data
Unit sales 11,500 units
Selling price per unit $120 per unit
Variable expenses per unit $72 per unit
Fixed expenses $420,000
Enter a formula into each of the cells marked with a ? below
Review Problem: CVP Relationships
Compute the CM ratio and variable expense ratio
Selling price per unit $120 per unit
Variable expenses per unit 72 per unit
Contribution margin per unit $48 per unit
CM ratio 40%
Variable expense ratio 60%
Compute the break-even
Break-even in unit sales 8,750 units
Break-even in dollar sales $1,050,000
Compute the margin of safety
Margin of safety in dollars $330,000
Margin of safety percentage 24%
Compute the degree of operating leverage
Sales 1,380,000$
Variable expenses 828,000
Contribution margin 552,000
Fixed expenses 420,000
Net operating income 132,000$
Degree of operating leverage 4.18
Page 6
Chapter_3_Applying_Excel.xls
Chapter 3: Applying Excel
Data
Unit sales 600 units
Selling price per unit $10,000 per unit
Variable expenses per unit $7,500 per unit
Fixed expenses $1,200,000
Enter a formula into each of the cells marked with a ? below
Review Problem: CVP Relationships
Compute the CM ratio and variable expense ratio
Selling price per unit $10,000 per unit
Variable expenses per unit 7,500 per unit
Contribution margin per unit $2,500 per unit
CM ratio 25%
Variable expense ratio 75%
Compute the break-even
Break-even in unit sales 480 units
Break-even in dollar sales $4,800,000
Compute the margin of safety
Margin of safety in dollars $1,200,000
Margin of safety percentage 20%
Compute the degree of operating leverage
Sales 6,000,000$
Variable expenses 4,500,000
Contribution margin 1,500,000
Fixed expenses 1,200,000
Net operating income 300,000$
Degree of operating leverage 5.00
Page 7
Chapter_3_Applying_Excel.xls
Chapter 3: Applying Excel
Data
Unit sales 600 units
Selling price per unit $9,000 per unit
Variable expenses per unit $7,500 per unit
Fixed expenses $900,000
Enter a formula into each of the cells marked with a ? below
Review Problem: CVP Relationships
Compute the CM ratio and variable expense ratio
Selling price per unit $9,000 per unit
Variable expenses per unit 7,500 per unit
Contribution margin per unit $1,500 per unit
CM ratio 17%
Variable expense ratio 83%
Compute the break-even
Break-even in unit sales 600 units
Break-even in dollar sales $5,400,000
Compute the margin of safety
Margin of safety in dollars $0
Margin of safety percentage 0%
Compute the degree of operating leverage
Sales 5,400,000$
Variable expenses 4,500,000
Contribution margin 900,000
Fixed expenses 900,000
Net operating income –$
Degree of operating leverage #DIV/0!
Page 8