Question

please use excel to answer and show formulas thank you!!

Chapter 5: Applying Excel Data Unit sales Selling price per unit Variable expenses per unit Fixed expenses 40,000 units $50 per unit $35 per unit $570,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 Variable expenses per unit Contribution margin per unit $50 per unit 35 per unit $15 per unit CM ratio Variable expense ratio 30% 70% Compute the break-even Break-even in unit sales Break-even in dollar sales 38,000 units $1,900,000 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 $ 40,000 1,400,000 (1,360,000) 570,000 $ (790,000) Degree of operating leverage

Answer 1

А B с 40000 Units 50 Per unit 35 Per unit 570000 4 Sales Units 5 Selling Price Per unit 6 Varaibel Expense Per unit 7 Fixed Expenses 9 Use the Data To Answer the following 10 11 1. Compute the CM ratio and Varaible Expense ratio 12 Selling Price Per unit 13 Variable costPer unit 14 Contribution Margin per unit (Selling Price - Varible Cost) 50 Per unit 35 Per unit 15 Per unit 15 16 CM Ratio = (CM/Selling Price) 17 Varaible Expense Ratio = (Variable Expense /Selling Price ) 30% 70% 18 38000 Units 1900000 Dollars 22 19 2. Compute the Break Even point 20 Break Even in Unit Sales = ( Fixed Cost / CM Per unit) 21 Brake Even in Dollar sales = ( Fixed Cost/ CM ratio) 23 3. Compute the Margin of Safety 24 Margin of Safety in dollars =(units *selling price - BEP Dollars) 25 Margin of Safety in Percentage =(Margin of safety Dollars/ Actual sales) 100000 5.00% 26 27 4. Compute the Operating Leverage 28 Sales 29 Varaible Expenses 30 Contribution Margin 31 Fixed Cost 32 Net operating Income = (CM-Fixed Cost) 34 Degree of Operating Leverage =(Contrubution Margin/ Net operating Income) 2000000 1400000 600000 570000 30000 20.00 times

A B с 40000 50 35 570000 Units Per unit Per unit 4 Sales Units 5 Selling Price Per unit 6 Varaibel Expense Per unit 7 Fixed Expenses 9 Use the Data To Answer the following 10 11 1. Compute the CM ratio and Varaible Expense ratio 12 Selling Price Per unit 13 Variable costPer unit 14 Contribution Margin per unit (Selling Price - Varible Cost) =B5 =B6 =B12-B13 Per unit Per unit Per unit 15 16 CM Ratio =( CM/Selling Price) 17 Varaible Expense Ratio = (Variable Expense /Selling Price ) =B14/B12 =B13/B12 18 =B7/B14 =B7/B16 Units Dollars ZZ 19 2. Compute the Break Even point 20 Break Even in Unit Sales = ( Fixed Cost / CM Per unit) 21 Brake Even in Dollar sales = ( Fixed Cost/ CM ratio) 23 3. Compute the Margin of Safety 24 Margin of Safety in dollars =(units *selling price - BEP Dollars) 25 Margin of Safety in Percentage = (Margin of safety Dollars/ Actual sales) =B28-B21 =B24/B28 26 27 4. Compute the Operating Leverage 28 Sales 29 Varaible Expenses 30 Contribution Margin 31 Fixed Cost 32 Net operating Income = (CM-Fixed Cost) 34 Degree of Operating Leverage = (Contrubution Margin/ Net operating Income) =B4*B12 =B4*B13 =B28-B29 =B7 =B30-B31 =B30/B32 times

Please use the Correct Cell for applyin Excel, Thank you . Please Comment for Any Issue.