Please help and guide me through the rest of this excel sheet!

Chapter 5 15e Note: Use your text and provided resourc See Problem details beginning page 215 Instructions: Complete worksheet, do not respond to questions unless instructed to do so. Everything in this worksheet at the end of the chapter in the assigned text. Chapter 5: Applying Excel p. 215, Only the worksheet in the text and below. Provided Data Unit sales Selling price per unit Variable expenses per unit Fixed expenses $ $ $ 20,000 units 60.00 per unit 45.00 per unit 240,000 Enter a formula into each of the shaded cells below Review Problem: CVP Relationships (p. 212) Compute the CM ratio and variable expense ratio Selling price per unit $ 60.00 per unit Variable expenses per unit $ 45.00 per unit Contribution margin per unit $ 15.00 per unit CM ratio Variable expense ratio Compute the break-even Break-even in unit sales Break-even in dollar sales Compute the margin of safety Margin of safety in dollars Margin of safety percentage 25% Correct 75% Correct $ $ Compute the degree of operating leverage Sales $ Variable expenses $ Contribution margin $ Fixed expenses $ Net operating income $ Degree of operating leverage 16,000 units 960,000 240,000 Correct 20% Correct 1,200,000 900,000 300,000 240,000 60,000 Correct Correct Correct Correct Correct 5.00 Correct Exercise 5-1: 1,2, and 3 (p. 218) Provided Data Sales (10,000 units) Variable expenses Contribution Margin Fixed Expenses Net Operating Income Correct $ $ $ $ $ Total 350,000 200,000 150,000 135,000 15,000 $ $ $ Per Unit 35.00 20.00 15.00 Correct Correct 1. The new income statement would be: Sales Variable expenses Contribution margin Fixed expenses Net operating income $ $ $ $ $ Total Per Unit 353,500 $ 35.00 202,000 $ 20.00 151,500 $ 15.00 135,000 16,500 Correct You can get the same net operating income using the following approach: Original net operating income Change in contribution margin $ $ 15,000 1,500 New net operating income $ 16,500 Correct 2. The new income statement would be: Sales Variable expenses Contribution margin Fixed expenses Net operating income $ $ $ $ $ Total Per Unit 346,500 $ 35.00 198,000 $ 20.00 148,500 $ 15.00 135,000 13,500 Correct You can get the same net operating income using the following approach: Original net operating income Change in contribution margin (-) $ $ 15,000 (1,500) Correct New net operating income $ 13,500 Correct 3. The new income statement would be: Sales Variable expenses Contribution margin Fixed expenses Net operating income $ $ $ $ $ Total Per Unit 315,000 $ 35.00 180,000 $ 20.00 135,000 $ 15.00 135,000 - Note: This is the company's break-even point. Exercise 5-4: 1, and 2 (p. 218) 1. The company's contribution margin (CM) ratio is: Total sales $ 200,000 Total variable expenses $ 120,000 Total contribution margin $ 80,000 Contribution margin ratio 40.0% Correct 2. The change in net operating income from an increase in total sales of $1,000 can be estimated by using the CM ratio as follows: Change in total sales CM ratio Estimated change in net operating income $ 1,000 40.0% Estimated change in net operating income $ 400 Correct This computation can be verified as follows: Total sales Total units sold Selling price per unit $ Increase in total sales Selling price per unit Increase in unit sales Original total unit sales New total unit sales $ $ Total unit sales Sales Variable expenses Contribution margin Fixed expenses Net operating income $ $ $ $ $ $ 200,000 50,000 units 4.00 per unit 1,000 4.00 250 50,000 50,250 Original 50,000 200,000 120,000 80,000 65,000 15,000 Correct per unit units units units $ $ $ $ $ Correct New 50,250 201,000 120,600 80,400 65,000 15,400 Correct Correct Correct Correct Exercise 5-5: 1, and 2 (p. 218-219) The following table shows the effect of the proposed change in monthly advertising budget: Provided Data Selling price Variable expense Contribution Margin Per Unit $ $ $ 1. Units Sold Sales Variable expenses Contribution margin Fixed expenses Net operating income % of Sales 100.0% 70.0% 30.0% 90 63 27 Sales With Additional Advertising Budget Current Sales 2,000 Difference Incorrect Incorrect Incorrect Alternative Solution 1 (with additional Ad $) Expected total contribution margin: $ Present total contribution margin: $ 56,700 - Incremental contribution margin Change in fixed expenses: Less incremental advertising expense Change in net operating income Alternative Solution 2 (with additional Ad $) Incremental contribution margin: Current Incorrect Incorrect Incorrect $ - Less incremental advertising expense Change in net operating income $ Incorrect 2. The $2 increase in variable expense will cause the unit contribution margin to decrease from $27 to $25 with the following impact on net operating income: Expected total contribution margin with the higher-quality components: Incorrect Present total contribution margin: Incorrect Change in total contribution margin Incorrect Exercise 5-6: 1, 2, 3, and 4 (p. 219) 1. The equation method yields the break-even point in unit sales, Q, as follows: Equation: (Unit CM) Q Fixed expenses = Profit (Profit at BE is 0) Selling Price per Unit $ 15.00 Variable expense per Unit $ 12.00 CM per Unit $ 3.00 Correct Unknown BE basket quantity xQ Less: Fixed expenses $ 4,200 Break Even basket quantity 1,400 Correct 2. The equation method can be used to compute the break-even point in dollar sales as follows: Unit contribution Margin / Unit Selling Price = CM ratio Unit contribution Margin $ 3.00 Correct Unit Selling Price $ 15.00 CM Ratio 0.20 Correct Equation: CM ratio Sales Fixed expenses = Profit Unit contribution Margin $ 3.00 Correct Unknown BE basket Sales xS Less: Fixed expenses $ 4,200 Break Even basket Sales $ 21,000 Correct 3. The formula method gives an answer that is identical to the equation method for the break-even point in unit sales: Equation: Unit Sales to BE = Fixed Expenses / Unit Contribution Margin Fixed expenses $ 4,200 Correct CM per Unit $ 3.00 Correct Break Even basket quantity 1,400 Correct 4. The formula method also gives an answer that is identical to the equation method for the break-even point in dollar sales: Formula: Dollar Sales to BE = Fixed Expenses / Unit Contribution Margin CM ratio = = Unit contribution m Unit selling pri $3 = 0.20 $15 Fixed expenses CM Ratio Break Even basket Sales $ $ 4,200 Correct 0.20 Correct 21,000 Correct Exercise 5-7: 1, and 2 (p. 219) 1. The equation method yields the required unit sales, Q, as follows: Equation: Profit = Unit CM Q Fixed expenses Unit contribution Margin $ 40.00 Correct Known Target basket Sales x $ 10,000 Less: Fixed expenses $ 50,000 Target basket Units 1,500 Correct 2. Equation: (Target Profit - Fixed Expenses) / Contribution Margin = Units sold to attain the Target profit Target Profit $ 10,000 Fixed Expenses $ 50,000 $ 15,000 Unit Contribution Margin $ 40.00 Target basket Units 1,500 Correct Problem 5-26: 1, 3, 4, 5, and 6 (p. 226) Provided Data Selling Price Variable Expenses Invoice Cost Sales Commission Total Variable Expenses Fixed Expenses Advertising Rent Salaries Total Fixed Expenses Per Pair of Shoes $ 30.00 $ $ $ 13.50 4.50 18.00 $ $ $ $ 30,000 20,000 100,000 150,000 1. The equation method yields the break-even point in unit sales, Q, as follows: Equation: (Unit CM) Q Fixed expenses = Profit (Profit at BE is 0) Selling Price per Unit $ 30.00 Variable expense per Unit $ 18.00 CM per Unit Incorrect Unknown BE Shoe quantity xQ Less: Fixed expenses $ 150,000 Break Even Shoe quantity Incorrect The equation method can be used to compute the break-even point in dollar sales as follows: Unit contribution Margin / Unit Selling Price = CM ratio Unit contribution Margin Incorrect Unit Selling Price CM Ratio $ 30.00 Incorrect Equation: CM ratio Sales Fixed expenses = Profit Unit contribution Margin Incorrect Unknown BE Shoe Sales xS Less: Fixed expenses $ 150,000 Break Even basket Sales Incorrect 2. Not required 3. The simplest approach is: Break-even sales (units) Actual sales (units) Sales short of break-even (units) Contribution Margin per pair Net Operating Income Alternative solution: Sales Total Variable expenses Contribution margin Total Fixed expenses Net operating loss $ 12,500 pairs 12,000 pairs pairs - Incorrect Incorrect Incorrect Incorrect $ 216,000 Incorrect $ 150,000 Incorrect 4. The variable expenses will now be $X: ($Y + $0.75) per pair, and the contribution margin will be $CM: ($30.00 - $X) per pair. Old variable expenses per unit Add: Incentive Commission New variable expenses per unit Incorrect Equation: (Unit CM) Q Fixed expenses = Profit (Profit at BE is 0) Selling Price per pair $ 30.00 Variable expense per Unit $ CM per Unit Incorrect Unknown BE Shoe quantity xQ Less: Fixed expenses $ 150,000 Break Even Shoe quantity pairs (rounded) Break Even Shoe quantity Selling Price per pair Dollar Break Even sales $ 30.00 Incorrect Alternative solution: Equation: Unit Sales to BE = Fixed Expenses / Unit Contribution Margin Fixed expenses $ 150,000 CM per Unit $ Break Even Shoe quantity Incorrect Formula: Dollar Sales to BE = Fixed Expenses / Unit Contribution Margin Fixed expenses $ 150,000 Incorrect CM Ratio Break Even Shoe Sales $ Incorrect 5. The simplest approach is: Actual sales Break-even sales Excess over break-even sales 15,000 pairs 12,500 pairs pairs Excess over break-even sales $ Present Contribution Margin less sales commission Total Profit Alternative solution: Sales Variable expenses Contribution margin Fixed expenses Net operating income 6. The new variable expenses will be 0.375 - Incorrect pairs Incorrect Incorrect Incorrect Incorrect Incorrect $ 150,000 Incorrect per pair Equation: (Unit CM) Q Fixed expenses = Profit (Profit at BE is 0) CM per Unit $ 30.00 Variable expense per Unit $ 13.50 CM per Unit Incorrect Unknown BE Shoe quantity xQ Less: Fixed expenses $ 181,500 Break Even Shoe quantity pairs Incorrect Incorrect Attention: Tests (and/or quizzes) in Managerial Accounting WILL NOT include the CORRECT / INCORRECT indicators. Therefore, your goal should not be to merely arrive a solution that moves the indictor from INCORRRECT to CORRECT but to know how you arrived at the correct solution. The End overhad teEstimaedtoalmountfhealoctinbase xt and provided resources! ails beginning page 215 erything in this worksheet relates directly to Problems and Exercises $80, With Ad $ Diff Incorrect Incorrect Incorrect Incorrect Incorrect = =160% $50, directmaerilscot Unit contribution margin Unit selling price $3 = 0.20 $15 $50, directmaerilscot INCORRECT indicators. ORRRECT to CORRECT