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, 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 per case
- Supplier B: 48-count 1.74 oz bags will have a cost of $15.65 per case
- Supplier C: 48-count 1.74 oz bags will have a cost of $14.75 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.00 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 $5.67 per case
- Marketing costs (variable cost) for the candy are $2.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
$35.00 per case selling price. To accomplish this, however, marketing costs will need to be increased by 25%. 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 10 points
- Graph Title 1 point
- x-axis title 1 point
- x-axis labels 1 point
- y-axis title 1 point
- y-axis labels 1 point
- Legend 2 points
- Data lines (fixed costs, revenue, and total costs) 3 points
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 the optimal option 10 points
- Graph Title 1 point
- x-axis title 1 point
- x-axis labels 1 point
- y-axis title 1 point
- y-axis labels 1 point
- Legend 2 points
- Data lines (fixed costs, revenue, and total costs) 3 points
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: 55 points
