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\mathrm{.}74$ oz bags will have a cost of $$15\mathrm{.}91$ per case

$$ Supplier B: $48-$count $1\mathrm{.}74$ oz bags will have a cost of $$14\mathrm{.}75$ per case

$$ Supplier C: $48-$count $1\mathrm{.}74$ oz bags will have a cost of $$15\mathrm{.}00$ per case

$$ Supplier D: $48-$count $1\mathrm{.}74$ oz bags will have a cost of $$14\mathrm{.}91$ per case

$$ Supplier E: $48-$count $1\mathrm{.}74$ oz bags will have a cost of $$15\mathrm{.}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\mathrm{.}99$

$$ Labor costs $($variable cost$)$ for the warehouse are $$7\mathrm{.}57$ per case

$$ Marketing costs $($variable cost$)$ for the candy are $$3\mathrm{.}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\mathrm{.}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