[Solved] excel

Assignment Prompt & Instructions

**M&M Case Study: Break-Even Analysis** – ## MMBreakEvenAnalysisInstructions.docx – MMBreak-EvenAnalysis.xlsx **## MMBreakEvenAnalysisInstructions.docx**  **M&M Case Study: Break-Even Analysis**  This exercise provides you the opportunity to apply break-even analysis concepts and Excel skills to a case study problem. The activity continues our work with Mars Inc. M&M candies. ## Assignment Your company is a wholesaler of Mars candies, and you are responsible for the M&M product line. Mars has given you some pricing forecast data concerning 2022 product prices and payment terms. You need to determine the break-even points for your facility based on the information detailed below. **Plain M&M Analysis:** You have identified the following 2022 price points on cases of Plain M&M candies from five different suppliers (variable costs) · Supplier A: 48-count 1.74 oz bags will have a cost of $15.91 per case · Supplier B: 48-count 1.74 oz bags will have a cost of $14.75 per case · Supplier C: 48-count 1.74 oz bags will have a cost of $15.00 per case · Supplier D: 48-count 1.74 oz bags will have a cost of $14.91 per case · Supplier E: 48-count 1.74 oz bags will have a cost of $15.65 per case Your additional costs are below: · Fixed costs for your warehouse are $4,500,000 annually · The selling price per case is $32.99 · Labor costs (variable cost) for the warehouse are $7.57 per case · Marketing costs (variable cost) for the candy are $3.00 per case ### Assignment: **_Part #1:_** You need to analyze the break-even point for each of the listed supplier options _(\*\* Hint-You will need to calculate five break-even points)._ In the Excel document for this assignment, there are five tabs named “Part #1 Supplier A” through “Part #1 Supplier E”. Each tab represents one of the five payment options. Complete the indicated break-even analysis by filling in the indicated columns, cells, and graphs. Remember, every chart must have a title, axis labels, axis titles, and a legend. Determine the supplier option that results in the lowest break-even point based on the number of cases and dollars _(\*\* Hint – it should be the same price point option for both the number of cases and dollars)._ Complete the information on the “Conclusions” tab for Part #1. **_Part #2:_** The market has changed. Your VP of sales indicates that the market will support a $36.00 per case selling price. To accomplish this, however, marketing costs will need to be increased by 35%. You need to analyze the break-even point for the option you selected with the lowest break-even point in Part #1. Use the “Part #2” tab in the document for your analysis. Complete the information on the “Conclusion” tab for Part #2. **_Part #3_** – Finally, answer the question on the “Conclusions” tab for Part #3. Submit one file with the filename **_Breakeven.xlsx_** ## 1 ## image1.jpeg ## image2.png **MMBreak-EvenAnalysis.xlsx** This file is too large to display. [View in new window](https://www.sweetstudy.com/files/mmbreak-evenanalysis-xlsx-9782177) **MMBreak-EvenAnalysis.xlsx** This file is too large to display. [View in new window](https://www.sweetstudy.com/files/mmbreak-evenanalysis-xlsx-9782177) **## MMBreakEvenAnalysisInstructions.docx**  **M&M Case Study: Break-Even Analysis**  This exercise provides you the opportunity to apply break-even analysis concepts and Excel skills to a case study problem. The activity continues our work with Mars Inc. M&M candies. ## Assignment Your company is a wholesaler of Mars candies, and you are responsible for the M&M product line. Mars has given you some pricing forecast data concerning 2022 product prices and payment terms. You need to determine the break-even points for your facility based on the information detailed below. **Plain M&M Analysis:** You have identified the following 2022 price points on cases of Plain M&M candies from five different suppliers (variable costs) · Supplier A: 48-count 1.74 oz bags will have a cost of $15.91 per case · Supplier B: 48-count 1.74 oz bags will have a cost of $14.75 per case · Supplier C: 48-count 1.74 oz bags will have a cost of $15.00 per case · Supplier D: 48-count 1.74 oz bags will have a cost of $14.91 per case · Supplier E: 48-count 1.74 oz bags will have a cost of $15.65 per case Your additional costs are below: · Fixed costs for your warehouse are $4,500,000 annually · The selling price per case is $32.99 · Labor costs (variable cost) for the warehouse are $7.57 per case · Marketing costs (variable cost) for the candy are $3.00 per case ### Assignment: **_Part #1:_** You need to analyze the break-even point for each of the listed supplier options _(\*\* Hint-You will need to calculate five break-even points)._ In the Excel document for this assignment, there are five tabs named “Part #1 Supplier A” through “Part #1 Supplier E”. Each tab represents one of the five payment options. Complete the indicated break-even analysis by filling in the indicated columns, cells, and graphs. Remember, every chart must have a title, axis labels, axis titles, and a legend. Determine the supplier option that results in the lowest break-even point based on the number of cases and dollars _(\*\* Hint – it should be the same price point option for both the number of cases and dollars)._ Complete the information on the “Conclusions” tab for Part #1. **_Part #2:_** The market has changed. Your VP of sales indicates that the market will support a $36.00 per case selling price. To accomplish this, however, marketing costs will need to be increased by 35%. You need to analyze the break-even point for the option you selected with the lowest break-even point in Part #1. Use the “Part #2” tab in the document for your analysis. Complete the information on the “Conclusion” tab for Part #2. **_Part #3_** – Finally, answer the question on the “Conclusions” tab for Part #3. Submit one file with the filename **_Breakeven.xlsx_** ## 1 ## image1.jpeg ## image2.png **MMBreak-EvenAnalysis.xlsx** This file is too large to display. [View in new window](https://www.sweetstudy.com/files/mmbreak-evenanalysis-xlsx-9782177) **## MMBreakEvenAnalysisInstructions.docx**  **M&M Case Study: Break-Even Analysis**  This exercise provides you the opportunity to apply break-even analysis concepts and Excel skills to a case study problem. The activity continues our work with Mars Inc. M&M candies. ## Assignment Your company is a wholesaler of Mars candies, and you are responsible for the M&M product line. Mars has given you some pricing forecast data concerning 2022 product prices and payment terms. You need to determine the break-even points for your facility based on the information detailed below. **Plain M&M Analysis:** You have identified the following 2022 price points on cases of Plain M&M candies from five different suppliers (variable costs) · Supplier A: 48-count 1.74 oz bags will have a cost of $15.91 per case · Supplier B: 48-count 1.74 oz bags will have a cost of $14.75 per case · Supplier C: 48-count 1.74 oz bags will have a cost of $15.00 per case · Supplier D: 48-count 1.74 oz bags will have a cost of $14.91 per case · Supplier E: 48-count 1.74 oz bags will have a cost of $15.65 per case Your additional costs are below: · Fixed costs for your warehouse are $4,500,000 annually · The selling price per case is $32.99 · Labor costs (variable cost) for the warehouse are $7.57 per case · Marketing costs (variable cost) for the candy are $3.00 per case ### Assignment: **_Part #1:_** You need to analyze the break-even point for each of the listed supplier options _(\*\* Hint-You will need to calculate five break-even points)._ In the Excel document for this assignment, there are five tabs named “Part #1 Supplier A” through “Part #1 Supplier E”. Each tab represents one of the five payment options. Complete the indicated break-even analysis by filling in the indicated columns, cells, and graphs. Remember, every chart must have a title, axis labels, axis titles, and a legend. Determine the supplier option that results in the lowest break-even point based on the number of cases and dollars _(\*\* Hint – it should be the same price point option for both the number of cases and dollars)._ Complete the information on the “Conclusions” tab for Part #1. **_Part #2:_** The market has changed. Your VP of sales indicates that the market will support a $36.00 per case selling price. To accomplish this, however, marketing costs will need to be increased by 35%. You need to analyze the break-even point for the option you selected with the lowest break-even point in Part #1. Use the “Part #2” tab in the document for your analysis. Complete the information on the “Conclusion” tab for Part #2. **_Part #3_** – Finally, answer the question on the “Conclusions” tab for Part #3. Submit one file with the filename **_Breakeven.xlsx_** ## 1 ## image1.jpeg ## image2.png **MMBreak-EvenAnalysis.xlsx** This file is too large to display. [View in new window](https://www.sweetstudy.com/files/mmbreak-evenanalysis-xlsx-9782177) – 1 – 2