Question

File Home Insert Draw Page Layout Formulas Data Review Vie Arial 10 AA Paste * Cut Еe Copy Format Painter Clipboard BIU Font Alignment A1 fx Chapter 2: Applying Excel B D ? per unit A B с Chapter 2: Applying Excel 2 3 Data 4 Unit sales 20,000 units 5 Selling price per unit $60 per unit 6 Variable expenses per unit $45 per unit 7 Fixed expenses $240,000 8 9 Enter a formula into each of the cells marked with a ? below 10 Review Problem: CVP Relationships 11 12 Compute the CM ratio and variable expense ratio 13 Selling price per unit ? per unit 14 Variable expenses per unit 15 Contribution margin per unit ? per unit 16 17 CM ratio ? 18 Variable expense ratio ? 19 20 Compute the break-even 21 Break-even in unit sales ? units 22 Break-even in dollar sales ? 23 24 Compute the margin of safety 25 Margin of safety in dollars ? 26 Margin of safety percentage ? 27 28 Compute the degree of operating leverage 29 Sales 30 Variable expenses ? 31 Contribution margin ? 32 Fixed expenses ? 33 Net operating income 34 35 Degree of operating leverage 36 37 38 ? ? ?

Chapter_2_Applying_Excel_Student_Form.xls

Answer 1

A B с 1 Chapter 2: Applying Excel 2 3 4 5 Data: Unit sales Selling price per unit Variable Expenses per unit Fixed Expenses 20,000 units $60 per unit $45 per unit $240,000 6 7 8 9 10 CVP Relationships 11 12 13 Compute the CM ratio and Variable Expenses ratio Selling price per unit Variable Expenses per unit Contribution margin per unit 14 $60 per unit $45 per unit $15 per unit 15 16 17 CM ratio Variable Expense ratio 25 75 18 19 20 21 Compute the Break-even Break-even in unit sales Break-even in dollar sales 16000 units $960,000 22 23 24 25 Compute the margin of safety Margin of safety in dollars Margin of safety percentage $240,000 20 26 27 28 29 30 Compute the degree of operating leverage Sales Variable Expenses Contribution margin Fixed Expenses Net operating income 31 $1,200,000 $900,000 $300,000 $240,000 $60,000 32 33 34 35 Degree of operating leverage 5 36 37

Excel formula:

A B с 1 Chapter 2: Applying Excel 2 3 4 5 Data: Unit sales Selling price per unit Variable Expenses per unit Fixed Expenses 20,000 units $60 per unit $45 per unit $240,000 6 7 8 9 10 CVP Relationships 11 12 13 B5 Compute the CM ratio and Variable Expenses ratio Selling price per unit Variable Expenses per unit Contribution margin per unit 14 B6 B13-B14 per unit per unit per unit 15 16 17 CM ratio Variable Expense ratio B15/B13*100 B14/B13*100 18 19 20 21 Compute the Break-even Break-even in unit sales Break-even in dollar sales units B7/B15 B21*B5 22 23 24 25 Compute the margin of safety Margin of safety in dollars Margin of safety percentage (B4*B5)-B22 100*B25/(B4*B5) 26 27 28 29 30 Compute the degree of operating leverage Sales Variable Expenses Contribution margin Fixed Expenses Net operating income 31 B4*B5 B4*B6 B29-B30 B7 B31-B32 32 33 34 35 Degree of operating leverage B31/B33 36

Contribution margin per unit is calculated by reducing variable Expenses per unit from Sales per unit.

Contribution margin ratio is calculated by dividing contribution margin per unit by sales per unit.

Variable Expense ratio is calculated by dividing Variable Expense per unit by sales per unit.

Break-even in unit sales is determined by dividing Fixed expenses by Contribution margin per unit.

Break-even in dollar sales is determined by multiplying Break-even in unit sales with selling price per unit.

Margin of safety in dollars is calculated by reducing Break-even sales from Current sales.

Margin of safety percentage is calculated by dividing Margin of safety in dollars by Current sales.

Degree of operating leverage is calculated by dividing contribution margin by net operating income.