Please give excel formulas !!!

Introduction Running any business is challenging and fraught with decisions to be made. Running Muesli AG is no different. Some of the very important decisions that can make or break the company come up during strategic planning. Strategic planning occurs once a year at Muesli AG during a multi-day meeting where managers plan for the upcoming years. During this strategic planning meeting, they examine not only the company's prior performance, but also market conditions, legal and political issues affecting the company, technological advances in muesli manufacturing, labor conditions, agricultural issues, research developments, and finances. The management team looks for opportunities for the company to capitalize on. In past meetings they have completed a SWOT analysis identifying Muesli AG's strengths, weaknesses, opportunities, and threats, which allows the company to strategically plan for the upcoming year. During this year's meeting some situations have been identified that require management decisions. These decisions are so important, that they could propel Muesli AG to another successful year or cause it to perform sub-optimally or even cause the company to fail. Therefore, it is important that these decisions are made carefully. Problem Statement: Case Details Your manager has asked you to prepare the initial cost analysis for five situations that will be discussed in this year's meeting. You will need to be careful to identify costs and revenues relevant to each situation and provide the results to your manager. Decision 1: The various flavors of muesil are considered product lines at Muesli AG. A contribution margin statement segmented by product line is provided. Management has noticed that nut muesli has consistently shown a negative net income. In the strategic planning meeting, they will be discussing whether to discontinue selling nut muesli. Here is some more information on the various expenses in the statement. - Advertising is specific to the various product lines - Sales teams are assigned to specific product lines. If the product line is dropped, the sales reps' salaries will be discontinued. - Labor unions are strong in Germany. Direct labor costs will remain at the stated amount regardless of how many or which products are made. - Variable selling expense is commission paid on the sales of the products. - All other fixed costs are divided evenly across product lines. - The team believes that they will likely not be able to use any time on the machine that is freed up from production of nut muesli for other product lines because of market constraints so the machine will remain idle. Use cell formulas and cell links in the Excel worksheet provided to answer the following questions. 1. How will dropping the sales of nut muesil affect net income at Muesli AG? 2. Should the company discontinue manufacturing and selling nut muesili Explain. 3. What other considerations should go into making this decision? Decision 2: Muesili AG currently purchases all its packaging for the muesil that it sells. They pay 60.12 for the large bags, 0.28 for large boxes, 0.09 for small bags and 0.21 for small boxes. Management is considering using a corner of the warehouse that is unused to manufacture the large boxes. They currently purchase and use 48,000 large boxes per month, 576,000 large boxes per year. The table below describes the estimated costs to manufacture the large boxes. Muesil AG will need to purchase equipment to cut, fold and glue the boxes. It will be depreciated for five years at 610,400 per year. Decision 2: Muesli AG currently purchases all its packaging for the muesli that it sells. They pay 60.12 for the large bags, 0.28 for large boxes, 0.09 for small bags and 0.21 for small boxes. Management is considering using a corner of the warehouse that is unused to manufacture the large boxes. They currently purchase and use 48,000 large boxes per month, 576,000 large boxes per year. The table below describes the estimated costs to manufacture the large boxes. Muesil AG will need to purchase equipment to cut, fold and glue the boxes. It will be depreciated for five years at 10,400 per year. 4. Compare the costs of manufacturing the large boxes to the cost of purchasing the large boxes. 5. Should Muesil AG manufacture the large boxes or purchase them from their current supplier? 6. Explain how the purchase of the box manufacturing equipment informs your decision. Decision 3; A new grocery chain is launching several stores in the northern region of Germany. There will be a estival and quite a lot of media coverage of the openings. They would like to have Muesli AG do a special lirnited edition run of the 1Kg Mixed Fruit muesli with special festival packaging and a change in the recipe to introduce additional nuts into the standard mixed fruit recipe. This will be a one-time festival deal and is expected to be a big hit with consumers. Therefore, they would like to order 32,000 boxes of the special muesli. Because the grocery chain believes that this is a great opportunity for Muesli AG, they are offering 67.25 per box. The normal selling price is 67.90 per box. For the special order, the festival labels will be purchased from a local printer at a cost of 6.02 each plus a 500 set up fee. There will be no variable selling costs because no commission will be paid on this special order. The additional nuts for the festival recipe will cost c.12 per box produced. The grocery chain will pick up the special order from the manufacturing plant so no additional transportation costs will apply. Assume that Muesli AG has the capacity to produce the special edition Mixed Fruit muesli without disrupting their normal sales. 7. Using the data given for the standard cost of producing a 1Kg box of mixed fruit muesli, identify the revenue and costs relevant to this special-order decision. a. Calculate the per unit (box) values and the values for an order of 32,000 boxes of the muesti. 8. Calculate the revenues and costs relevant to the special order. a. Calculate the per unit (box) values and the values for an order of 32,000 boxes of the muesh. 9. Calculate the difference between the standard mixed fruit muesli and the special order. 10. Should the special order be accepted? Why or why not? 11. Using Goal Seek in Excel, calculate the lowest selling price per box that Muesli AG should be willing to accept for the special order. Decision 4: Research and development at Muesli AG has been working on creating a product that could move Muesli AG into new markets. The new product is a granola energy bar created by processing Mixed fruit muesli further into a bar to be sold as a quick snack or even a corvenient breakfast bar. The new muesii granola bar will be wrapped in a special biodegradable waxed paper printed with the Mueali AG logo. The ingredients for 8,0001kg boxes of muesil produce 24,000 granola bars. Management plans to sell the granola bars for 62.95 each. 12. Based on the costs of manufacturing mixed fruit muesli and the estimated conts to comvert the muedi into granola bars, determine whether it is more profitable to sell the muesli as is or process it further into granola bars? 13. Given the results of this analysis, what advice would you give the maragement at Muesli AG? Diccuss the implications of your findings. Decision 5: Machine time is a constrained resource at Muesli AG. There is only one machine and it runs 24 hours a day, five (5) days a week, 20 days a month. New equipment can be purchased, but it quite expensive. Given the current constraints, management would like to explore ways of maximixing profit in spite of the constraints. In particular, they would like to look at 500g mixed fruit muesli, 1Kg fruit muesli, and the new granola energy bar products. For purposes of this analysis, assume the granola bar sells for 63.95 each. 14. If Muesli AG could sell whatever it produces, in what order should it manufacture the three products? 15. What other considerations go into deciding what products to manufacture? Insert formulas and cell references into the "gold" cells below. Product Lines Sales Revenues Nut.718,080Blueberry1,795,200Strawberry1,705,440Raisin1,346,400Original2,154,240Mixed1,256,640Total8,976,000 Variable Expenses: Variable Manufacturing Expense Variable Sales Expense Contribution Margin Fixed Expenses: Decision 1 Sales Revenues Variable Expenses: Variable Manufacturing Expense Variable Sales Expense Contribution Margin Fixed Expenses: Direct Labor Administrative Expenses Advertising Depreciation - Bullding Depreciation - Mfg Equipment Research \& Development Sales Salaries Transportation Costs Net income A Transortation Costs Netincome (6,275.640) [5] [6] Decision 3 [8] Order [9] 6447 65 order quontify 66 Seling Price e7 Current costs of makine Moxed Fruit Muesil chapter 13 (5) Decision 3 Riet Profit Pechion 4 Quantities: Each 8,00024,000 1 Kr Bones of Maxed fruit Muesli: b4 Siling Price B3 Costs of making Mixed Fruit Muesli Common loint Costs: chapter 13 Net Proit [7] [7] [8] [B] [9] \{11] Decision A Quantities Each b,000 24,000 1Kg Bowes of Mosed Fruit Muesli: Seiling Price Costs of makine Mixed fruit Moesil Common loint Costs: [12] Profit Granole bars: Costs to Process further: Selline Price c.70,800.00 Added Casts harily peanuthutter packaging Total Added Manufacturint Costs 101 Pratit c6,480.00C1,800.00[4,800.00C.13,080.00[57,720.00 [12] 5ales itevenue from Processing further (Granola liars) Wha Leis Commen Joint costs los Leis Costs to Process Further 106 Profit: [12] 109 1010 Difference in Profit (Process further - Sell at Split otf) (\&. 20,500.00)[12] 109 110 Decision 5 Selling Price variable costs A B C E G 94. Granola bars: 70,800,00 95 Selling Price 96. Added Costs honcy peanutbuttet packaging Total Added Manufacturing Costs 101 Protit 102 103 Sales Revenue from Processing Further (Granola Bars) 104 Less Common Joint Costs 105 Less Costs to Process Further 12\} 106 Profit: 107 108 109 110 Decision 5 Difference in Profit (Process Further-Sell at Split Oif) (c20.800,00)[12] Contribution Margin per MH [14] 117 in what order should they be produced? [14] 118 (number these 1 through 3) Introduction Running any business is challenging and fraught with decisions to be made. Running Muesli AG is no different. Some of the very important decisions that can make or break the company come up during strategic planning. Strategic planning occurs once a year at Muesli AG during a multi-day meeting where managers plan for the upcoming years. During this strategic planning meeting, they examine not only the company's prior performance, but also market conditions, legal and political issues affecting the company, technological advances in muesli manufacturing, labor conditions, agricultural issues, research developments, and finances. The management team looks for opportunities for the company to capitalize on. In past meetings they have completed a SWOT analysis identifying Muesli AG's strengths, weaknesses, opportunities, and threats, which allows the company to strategically plan for the upcoming year. During this year's meeting some situations have been identified that require management decisions. These decisions are so important, that they could propel Muesli AG to another successful year or cause it to perform sub-optimally or even cause the company to fail. Therefore, it is important that these decisions are made carefully. Problem Statement: Case Details Your manager has asked you to prepare the initial cost analysis for five situations that will be discussed in this year's meeting. You will need to be careful to identify costs and revenues relevant to each situation and provide the results to your manager. Decision 1: The various flavors of muesil are considered product lines at Muesli AG. A contribution margin statement segmented by product line is provided. Management has noticed that nut muesli has consistently shown a negative net income. In the strategic planning meeting, they will be discussing whether to discontinue selling nut muesli. Here is some more information on the various expenses in the statement. - Advertising is specific to the various product lines - Sales teams are assigned to specific product lines. If the product line is dropped, the sales reps' salaries will be discontinued. - Labor unions are strong in Germany. Direct labor costs will remain at the stated amount regardless of how many or which products are made. - Variable selling expense is commission paid on the sales of the products. - All other fixed costs are divided evenly across product lines. - The team believes that they will likely not be able to use any time on the machine that is freed up from production of nut muesli for other product lines because of market constraints so the machine will remain idle. Use cell formulas and cell links in the Excel worksheet provided to answer the following questions. 1. How will dropping the sales of nut muesil affect net income at Muesli AG? 2. Should the company discontinue manufacturing and selling nut muesili Explain. 3. What other considerations should go into making this decision? Decision 2: Muesili AG currently purchases all its packaging for the muesil that it sells. They pay 60.12 for the large bags, 0.28 for large boxes, 0.09 for small bags and 0.21 for small boxes. Management is considering using a corner of the warehouse that is unused to manufacture the large boxes. They currently purchase and use 48,000 large boxes per month, 576,000 large boxes per year. The table below describes the estimated costs to manufacture the large boxes. Muesil AG will need to purchase equipment to cut, fold and glue the boxes. It will be depreciated for five years at 610,400 per year. Decision 2: Muesli AG currently purchases all its packaging for the muesli that it sells. They pay 60.12 for the large bags, 0.28 for large boxes, 0.09 for small bags and 0.21 for small boxes. Management is considering using a corner of the warehouse that is unused to manufacture the large boxes. They currently purchase and use 48,000 large boxes per month, 576,000 large boxes per year. The table below describes the estimated costs to manufacture the large boxes. Muesil AG will need to purchase equipment to cut, fold and glue the boxes. It will be depreciated for five years at 10,400 per year. 4. Compare the costs of manufacturing the large boxes to the cost of purchasing the large boxes. 5. Should Muesil AG manufacture the large boxes or purchase them from their current supplier? 6. Explain how the purchase of the box manufacturing equipment informs your decision. Decision 3; A new grocery chain is launching several stores in the northern region of Germany. There will be a estival and quite a lot of media coverage of the openings. They would like to have Muesli AG do a special lirnited edition run of the 1Kg Mixed Fruit muesli with special festival packaging and a change in the recipe to introduce additional nuts into the standard mixed fruit recipe. This will be a one-time festival deal and is expected to be a big hit with consumers. Therefore, they would like to order 32,000 boxes of the special muesli. Because the grocery chain believes that this is a great opportunity for Muesli AG, they are offering 67.25 per box. The normal selling price is 67.90 per box. For the special order, the festival labels will be purchased from a local printer at a cost of 6.02 each plus a 500 set up fee. There will be no variable selling costs because no commission will be paid on this special order. The additional nuts for the festival recipe will cost c.12 per box produced. The grocery chain will pick up the special order from the manufacturing plant so no additional transportation costs will apply. Assume that Muesli AG has the capacity to produce the special edition Mixed Fruit muesli without disrupting their normal sales. 7. Using the data given for the standard cost of producing a 1Kg box of mixed fruit muesli, identify the revenue and costs relevant to this special-order decision. a. Calculate the per unit (box) values and the values for an order of 32,000 boxes of the muesti. 8. Calculate the revenues and costs relevant to the special order. a. Calculate the per unit (box) values and the values for an order of 32,000 boxes of the muesh. 9. Calculate the difference between the standard mixed fruit muesli and the special order. 10. Should the special order be accepted? Why or why not? 11. Using Goal Seek in Excel, calculate the lowest selling price per box that Muesli AG should be willing to accept for the special order. Decision 4: Research and development at Muesli AG has been working on creating a product that could move Muesli AG into new markets. The new product is a granola energy bar created by processing Mixed fruit muesli further into a bar to be sold as a quick snack or even a corvenient breakfast bar. The new muesii granola bar will be wrapped in a special biodegradable waxed paper printed with the Mueali AG logo. The ingredients for 8,0001kg boxes of muesil produce 24,000 granola bars. Management plans to sell the granola bars for 62.95 each. 12. Based on the costs of manufacturing mixed fruit muesli and the estimated conts to comvert the muedi into granola bars, determine whether it is more profitable to sell the muesli as is or process it further into granola bars? 13. Given the results of this analysis, what advice would you give the maragement at Muesli AG? Diccuss the implications of your findings. Decision 5: Machine time is a constrained resource at Muesli AG. There is only one machine and it runs 24 hours a day, five (5) days a week, 20 days a month. New equipment can be purchased, but it quite expensive. Given the current constraints, management would like to explore ways of maximixing profit in spite of the constraints. In particular, they would like to look at 500g mixed fruit muesli, 1Kg fruit muesli, and the new granola energy bar products. For purposes of this analysis, assume the granola bar sells for 63.95 each. 14. If Muesli AG could sell whatever it produces, in what order should it manufacture the three products? 15. What other considerations go into deciding what products to manufacture? Insert formulas and cell references into the "gold" cells below. Product Lines Sales Revenues Nut.718,080Blueberry1,795,200Strawberry1,705,440Raisin1,346,400Original2,154,240Mixed1,256,640Total8,976,000 Variable Expenses: Variable Manufacturing Expense Variable Sales Expense Contribution Margin Fixed Expenses: Decision 1 Sales Revenues Variable Expenses: Variable Manufacturing Expense Variable Sales Expense Contribution Margin Fixed Expenses: Direct Labor Administrative Expenses Advertising Depreciation - Bullding Depreciation - Mfg Equipment Research \& Development Sales Salaries Transportation Costs Net income A Transortation Costs Netincome (6,275.640) [5] [6] Decision 3 [8] Order [9] 6447 65 order quontify 66 Seling Price e7 Current costs of makine Moxed Fruit Muesil chapter 13 (5) Decision 3 Riet Profit Pechion 4 Quantities: Each 8,00024,000 1 Kr Bones of Maxed fruit Muesli: b4 Siling Price B3 Costs of making Mixed Fruit Muesli Common loint Costs: chapter 13 Net Proit [7] [7] [8] [B] [9] \{11] Decision A Quantities Each b,000 24,000 1Kg Bowes of Mosed Fruit Muesli: Seiling Price Costs of makine Mixed fruit Moesil Common loint Costs: [12] Profit Granole bars: Costs to Process further: Selline Price c.70,800.00 Added Casts harily peanuthutter packaging Total Added Manufacturint Costs 101 Pratit c6,480.00C1,800.00[4,800.00C.13,080.00[57,720.00 [12] 5ales itevenue from Processing further (Granola liars) Wha Leis Commen Joint costs los Leis Costs to Process Further 106 Profit: [12] 109 1010 Difference in Profit (Process further - Sell at Split otf) (\&. 20,500.00)[12] 109 110 Decision 5 Selling Price variable costs A B C E G 94. Granola bars: 70,800,00 95 Selling Price 96. Added Costs honcy peanutbuttet packaging Total Added Manufacturing Costs 101 Protit 102 103 Sales Revenue from Processing Further (Granola Bars) 104 Less Common Joint Costs 105 Less Costs to Process Further 12\} 106 Profit: 107 108 109 110 Decision 5 Difference in Profit (Process Further-Sell at Split Oif) (c20.800,00)[12] Contribution Margin per MH [14] 117 in what order should they be produced? [14] 118 (number these 1 through 3)