Insert formulas and cell references into the "gold" cells below. Scenario 1 Contribution Margin Statement Sales Revenues Ratios Per Unit Information Break Even 30,000 boxes sold [7] # of Boxes 225,000 Variable Expense Contribution Margin Fixed Expenses 163,500 [2] [4] [5] [8] Sales Revenue 61,500 [1] [6] CM per unit 47,875 Net Operating Income 13,625 [3] Scenario 2 Contribution Margin Statement Ratios Per Unit Information boxes sold [13] [14] Break Even #DIV/O! #DIV/01 # of Boxes Sales Revenue Sales Revenues Variable Expense Contribution Margin Fixed Expenses Net Operating Income [11] Scenario 3 [10] [9] [12] [15] CM per unit Margin of Safety [16] # of Boxes [17] Boxes of Muesli Contribution Margin Statement Ratios Per Unit Information Break Even boxes sold Sales Revenues Variable Expense Contribution Margin 0.00 0.00 #DIV/01 WDIV/O! # of Boxes Sales Revenue 0.00 CM per unit Target Profit 50,000 Fixed Expenses Net Operating Income [18] Scenario 4 Contribution Margin Statement [19] [20] # of Boxes [21] Sales Revenue 16,000 14,000 1 1 1 1 1 BreakEven Analysis 1 1 1 1 1 1 CO Scenario 1 of Boxes Scenario 2 D Scenario 3 Scenario 4 Scenario 5 Scenario 6 -Sales Revenue Co Net Operating Income Analysis 120.00% 100.00% Ratios Per Unit Information Break Even [23] 12,000 boxes sold of Boxes 80.00% Sales Revenues Variable Expense Sales Revenue 10,000 8,000 Contribution Margin Fixed Expenses Net Operating Income [22] [24] CM per unit 6,000 60.00% 40.00% 4,000 20.00% Scenario 5 2,000 Contribution Margin Statement Ratios Per Unit Information Break Even [26] co 0.00% 30,000 boxes sold # of Boxes Scenario 1 Scenario 2 Scenario 3 Sales Revenues Variable Expense Contribution Margin Fixed Expenses Sales Revenue Net Operating Income Scenario 4 Profit Margin Ratio (NDI Ratcl Scenario S Scenario Net Operating Income [25] CM per unit [27] Scenario 6 Contribution Margin Statement Ratios 30,000 boxes sold 225,000 Sales Revenues Variable Expense Fixed Expenses Contribution Margin Net Operating Income [28] [30] Per Unit Information Break Even [29] 120.00% # of Boxes 7.50 Sales Revenue 100.00% CM per unit 80.00% 60.00% 40.00% 20.00% Comparison of CM Ratio and Profit Margin Ratio Page 2 > of 4 - ZOOM Instructions: Use formulas and links to cells in the worksheet provided in order to answer the following questions. Scenario 1 Using the contribution margin statement in Figure 1, answer the following questions 1. What is the contribution margin ratio (rounded to 2 decimal points)? 2. What is the variable expense ratio (rounded to 2 decimal points)? 3. What is the profit margin (net operating income percentage)? 4. What is the sales price per box of muesli? 2 lbid. Created by and for the Charles W. Lamden School of Accountancy, Fowler College of Business, San Diego State University 2020 by Nancy Jones and C. Janie Chang 5. What is the variable cost per box of muesli? 6. What is the contribution margin per box of muesli? 7. How many boxes of cereal does Muesli AG need to sell to break even? 8. What will be the sales revenue at the breakeven point? Scenario 2 Let's look at what would happen if Muesli AG sells 40,000 boxes of cereal during the time period instead of 30,000. 9. What is the contribution margin ratio (rounded to 2 decimal points)? 10. What is the variable expense ratio (rounded to 2 decimal points)? 11. What is the net operating income? Page > of 4 - ZOOM Scenario 2 Let's look at what would happen if Muesli AG sells 40,000 boxes of cereal during the time period instead of 30,000. 9. What is the contribution margin ratio (rounded to 2 decimal points)? 10. What is the variable expense ratio (rounded to 2 decimal points)? 11. What is the net operating income? 12. What is the profit margin (net operating income percentage)? 13. What is the sales price per box of muesli? 14. What is the variable cost per box of muesli? 15. What is the contribution margin per box of muesli? 16. What is the margin of safety in units (boxes of muesli)? 17. What is the margin of safety as a percentage? Scenario 3 Let's look at what would happen if Muesli AG sells 20,000 boxes of cereal during the time period instead of 30,000. We'll also look at the break even point and how many boxes of cereal Muesli AG will need to sell to earn a desired profit. 18. What is the net operating income? 19. What is the profit margin (net operating income percentage)? 20. How many boxes of cereal does Muesli AG need to sell to earn a profit of 50,000? 21. If the net operating income is 50,000, what will be the sales revenue amount? Scenario 4 Start from the original contribution margin statement. The Marketing Department would like to run a special promotion to increase sales in the grocery stores in the Southern region. They believe that by decreasing the price of a box of muesli by 0.10, that sales will increase by 10%. 22. If the Marketing Department's assumptions are true, what will be the net operating income? Should Muesli AG go ahead with the promotion? 23. How does the break even point with the sales promotion in place compare to the break even point you originally calculated? 24. Compare the contribution margin ratio in this scenario to the contribution margin ratio in scenario 1. What caused the change? Scenario 5 Start from the original contribution margin. The Marketin during the Page of 4 - ZOOM additional blueberries and strawberries can be added to the muesli at an additional cost of 0.20 per box. Marketing believes that the premium fruit muesli could command a selling price of 8.00 per box although the quantity is expected to remain at 30,000 boxes. 25. If the Marketing Department's assumptions are true, what will be the net operating income? Should Muesli AG go ahead with the promotion? 26. How does the break even point with the sales promotion in place compare to the break even point you originally calculated? 27. Compare the contribution margin ratio in this scenario to the contribution margin ratio in scenario 1. What caused the change? Scenario 6 Start from the original contribution margin. The Production Department has researched a method of producing more boxes of muesli each day at a 10% lower cost than the current process. The new production will require investment resulting in an increase in fixed costs of 12,000 each time period. Assuming that sales price and quantity remains the same, analyze the effects of the new production method. 28. What will be the new net operating income in this scenario? Should Muesli AG invest in the new process? 29. How does the break even point with the new production process compare to the break even point you originally calculated? 30. Compare the contribution margin ratio in this scenario to the contribution margin ratio in scenario 1. What caused the change