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

1

1

T

his exercise

provide

s

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 y

ou are responsible for the M&M product

line. Mars has given you some pricing forecast data concerning 202

1

product prices and payment

terms. You need

to

determine the break

â€“

even points for you

r facility based on the information

detailed below.

Plain M&M Analysis:

You have identified

the following 202

1

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 $1

4.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 $1

5.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

1

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