M&M Case Study: Break-Even Analysis

This exerciseprovides 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, where you are responsible for the M&M product line. Mars has given you some pricing forecast data concerning 2021 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 2021 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 percase
• Supplier B: 48-count 1.74 oz bags will have a cost of $15.65 percase
• Supplier C: 48-count 1.74 oz bags will have a cost of $15.00 percase
• Supplier D: 48-count 1.74 oz bags will have a cost of $14.91 percase
• Supplier E: 48-count 1.74 oz bags will have a cost of $14.75 percase

 

Your additional costs are below:

• Fixed costs for your warehouse are $4,500,000annually
• The selling price per case is$32.99
• Labor costs (variable cost) for the warehouse are $7.57 percase
• Marketing costs (variable cost) for the candy are $3.00 percase

 

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

$35.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

Grading:

Part #1:

Break-even graph for optimal option                                                                       10points

• GraphTitle 1point
• x-axistitle 1 point
• x-axislabels 1point
• y-axistitle 1 point
• y-axislabels 1point
• Legend 2points
• Data lines (fixed costs, revenue, and total costs) 3points

Correct break-even point (in units) for each supplier option (2 points each)            10 points Correct break-even point (in dollars) for each supplier option (2 points each)         10 points

Part #2:

Correct new break-even point (in units) for optimal supplier option                        2 points Correct new break-even point (in dollars) for optimal supplier option                         2 points

New break-even graph for theoptimal option                                                           10points

• GraphTitle 1point
• x-axistitle 1 point
• x-axislabels 1point
• y-axistitle 1 point
• y-axislabels 1point
• Legend 2points
• Data lines (fixed costs, revenue, and total costs) 3points

 

Conclusions

Part #1:

Identification of optimal options:

·        Optimal option                                                                                           1 point

·        BEPU                                                                                                        1 point

·        BEP$                                                                                                         1 point

Part #2:

Identification of new break-even points:

·        BEPU                                                                                                        1 point

·        BEP$                                                                                                         1 point

Part #2:

Reflection                                                                                                            6 points

Total Points:                                                                                                        55points