please when filling the excel sheet write the formulas next to it please

Introduction The muesli market in Germany has some distinct characteristics that will influence company strategy. Your understanding of cost behavior will help you take advantage of the markets. Here is some information about the market. "As a manufacturer, you do not sell directly to the end consumer but rather to a variety of retail stores. Competition and margins in the breakfast cereals market is very tight. While the total demand for readyto-eat cereal is relatively stable throughout the year the net sale of local brands of muesli cereal was not. Traditional ready-to-eat cereal seems to offer genuine competition to the local muesli brands. ...There are four key decision areas that each firm can use to influence the sale of their specitic muesili cereals product recipes, marketing, price and product availability."1 We will focus on pricing and sales volume for this case study. "Stores that carry breakfast cereals in Germany can be roughly classified into three types, largely based on their size. Each type of store has similar buying behavior. The consumers that shop of each type of store also share some characteristics, at least with regard to the purchase and consumption of muesli. Your firm maintains 3 standard pricelists for all your products, one for each type of store, It will be your responsibility to maintain these 3 price lists. Understanding the importance of various factors affecting the buying decisions of each type of store is essential to pricing effectively. Grocery stores are the main distribution channel for the muesli industry. Grocery stores offer their customers more variety than the other types of stores. A typical store would carry several different flavors and sizes of muesli on their shelves. If a grocery store is to carry an individual firm's brand, they will want to buy more than just one type of muesli. Grocery stores typically buy 4 different products at a time from each availability, price, recipe and consumer product preferences. Independent grocers or convenience stores are small proximity stores. They sell a small variety of products: beverages, beer, dairy products, snacks, newspapers and magazines, and some everyday nonperishable food products including muesli cereals. Convenience stores have limited shelf space, and will only carry a few types of muesli, whether that be different brands or different flavors. They only carry the 500g size boxes. Given the lack of variety, shelf space, and volume in sales they will want to carry the flavors and recipes preferred by their consumers, and typically place orders for a limited quantity of just. one product at time. Consumers buying muesii at convenience stores are typically not looking for a good price, and so it is much easier for these retailers to pass on any price changes you make. Prices are on average 15% to 20% higher 1 Participant's Guide: Manufacturing Game, ERPSim, HEC Montreal 2019 Creoted by and for the Charles W. Lamden School of Accountancy, Fowler College of Business, San Diego State University @2022 by Nancy Jones and C. Janie Chang in convenience stores than in traditional grocery stores. However, these consumers are relatively easy to influence with in store promotions (marketing). Large discount stores or hypermarkets are a growing phenomenon. They not only sell food, they also sell books, CDs, clothing, and electronic appliances. These stores have managed to attract a large number of consumers looking for cheaper prices. Hypermarkets are a high volume, low margin business and are very sensitive to price changes, If a hypermarket is to carry an individual firm's brand, they will want to buy more than just one type of mueslu, typically buying large quantities of 3 different products at a time from each individual firm. They only carry the 1kg boxes, and the products are sold at prices 5% to 10s lower than in traditional grocery stores." Problem Statement: Case Details Recall that there are twelve products sold by Muesli AG. For simplicity, we will analyze as if there is one product, mixed fruit muesli in the 500g size box. However, the analysis can easily be extended to all flavors and sizes of muesli with a few additions to our Excel worksheet. In addition, we might choose to analyze all of our products together. For example, we might ask how product mix affects our profits? Which products should we promote and sell more of given the constraints of the market? A contribution margin statement is given below. Sales during the period were 30,000 boxes of muesli. Using the contribution margin statement as a starting point, examine various scenarios to help inform your decision regarding pricing, marketing, and product recipe changes. Assume that your analysis is within the relevant range; in other words, cost behaviors do not change within the parameters of our analysis. 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? 5. What is the variable cost per box of muesli? 6. What is the contribution margin per box of muesli? x ibid. Created by and for the Chorles W. Lomden School of Accountancy, Fowler College of Business, San Diego State University 62022 by Nancy Jones and C. Jonie Chang 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? 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 muesii)? 17. What is the margin of safety as a percentage? Scenario 3 Let's look at what would happen if Muesli AG selis 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 650,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 60.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 Marketing Department has discovered during their market research that customers in the West region prefer fruitier cereals. They have talked with Production and Purchasing and calculated that additional blueberries and strawberries can be added to the muesli at an additional cost of 60.20 per Created by and for the Charles W. Lamden School of Accountancy, Fowler Callege of Business, San Diego State University 02022 by Nancy Jones and C. Jonie Chang 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? Scmurie 1 Introduction The muesli market in Germany has some distinct characteristics that will influence company strategy. Your understanding of cost behavior will help you take advantage of the markets. Here is some information about the market. "As a manufacturer, you do not sell directly to the end consumer but rather to a variety of retail stores. Competition and margins in the breakfast cereals market is very tight. While the total demand for readyto-eat cereal is relatively stable throughout the year the net sale of local brands of muesli cereal was not. Traditional ready-to-eat cereal seems to offer genuine competition to the local muesli brands. ...There are four key decision areas that each firm can use to influence the sale of their specitic muesili cereals product recipes, marketing, price and product availability."1 We will focus on pricing and sales volume for this case study. "Stores that carry breakfast cereals in Germany can be roughly classified into three types, largely based on their size. Each type of store has similar buying behavior. The consumers that shop of each type of store also share some characteristics, at least with regard to the purchase and consumption of muesli. Your firm maintains 3 standard pricelists for all your products, one for each type of store, It will be your responsibility to maintain these 3 price lists. Understanding the importance of various factors affecting the buying decisions of each type of store is essential to pricing effectively. Grocery stores are the main distribution channel for the muesli industry. Grocery stores offer their customers more variety than the other types of stores. A typical store would carry several different flavors and sizes of muesli on their shelves. If a grocery store is to carry an individual firm's brand, they will want to buy more than just one type of muesli. Grocery stores typically buy 4 different products at a time from each availability, price, recipe and consumer product preferences. Independent grocers or convenience stores are small proximity stores. They sell a small variety of products: beverages, beer, dairy products, snacks, newspapers and magazines, and some everyday nonperishable food products including muesli cereals. Convenience stores have limited shelf space, and will only carry a few types of muesli, whether that be different brands or different flavors. They only carry the 500g size boxes. Given the lack of variety, shelf space, and volume in sales they will want to carry the flavors and recipes preferred by their consumers, and typically place orders for a limited quantity of just. one product at time. Consumers buying muesii at convenience stores are typically not looking for a good price, and so it is much easier for these retailers to pass on any price changes you make. Prices are on average 15% to 20% higher 1 Participant's Guide: Manufacturing Game, ERPSim, HEC Montreal 2019 Creoted by and for the Charles W. Lamden School of Accountancy, Fowler College of Business, San Diego State University @2022 by Nancy Jones and C. Janie Chang in convenience stores than in traditional grocery stores. However, these consumers are relatively easy to influence with in store promotions (marketing). Large discount stores or hypermarkets are a growing phenomenon. They not only sell food, they also sell books, CDs, clothing, and electronic appliances. These stores have managed to attract a large number of consumers looking for cheaper prices. Hypermarkets are a high volume, low margin business and are very sensitive to price changes, If a hypermarket is to carry an individual firm's brand, they will want to buy more than just one type of mueslu, typically buying large quantities of 3 different products at a time from each individual firm. They only carry the 1kg boxes, and the products are sold at prices 5% to 10s lower than in traditional grocery stores." Problem Statement: Case Details Recall that there are twelve products sold by Muesli AG. For simplicity, we will analyze as if there is one product, mixed fruit muesli in the 500g size box. However, the analysis can easily be extended to all flavors and sizes of muesli with a few additions to our Excel worksheet. In addition, we might choose to analyze all of our products together. For example, we might ask how product mix affects our profits? Which products should we promote and sell more of given the constraints of the market? A contribution margin statement is given below. Sales during the period were 30,000 boxes of muesli. Using the contribution margin statement as a starting point, examine various scenarios to help inform your decision regarding pricing, marketing, and product recipe changes. Assume that your analysis is within the relevant range; in other words, cost behaviors do not change within the parameters of our analysis. 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? 5. What is the variable cost per box of muesli? 6. What is the contribution margin per box of muesli? x ibid. Created by and for the Chorles W. Lomden School of Accountancy, Fowler College of Business, San Diego State University 62022 by Nancy Jones and C. Jonie Chang 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? 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 muesii)? 17. What is the margin of safety as a percentage? Scenario 3 Let's look at what would happen if Muesli AG selis 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 650,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 60.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 Marketing Department has discovered during their market research that customers in the West region prefer fruitier cereals. They have talked with Production and Purchasing and calculated that additional blueberries and strawberries can be added to the muesli at an additional cost of 60.20 per Created by and for the Charles W. Lamden School of Accountancy, Fowler Callege of Business, San Diego State University 02022 by Nancy Jones and C. Jonie Chang 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? Scmurie 1