Question

home / study / business / operations management / operations management questions and answers / a plastic cup manufacturer is considering adding a new plant to keep up with growth in demand. ... Question: A plastic cup manufacturer is considering adding a new plant to keep up with growth in demand. Th... A plastic cup manufacturer is considering adding a new plant to keep up with growth in demand. The location being considered will have fixed costs of $25,000 per month and variable costs of $25 per box of 1,000 cups produced. Cups are sold for a price of $40 per box of 1,000.

Plot the revenue total cost and profit lines

How many cups needed to be manufactured per month to break even

How much profit does the manufacturer earn if they make 6,000 boxes

Determine the production volume required to make a profit of $25,000 per month express your answer in terms of boxes as well as cups

Needs to be done on excel please

Answer 1

1) Plot the revenue total cost and profit lines

FORMULAS:

Number of boxes	Revenue	Total cost	Profit
0	=A2*40	=25000+A2*25	=B2-C2
1000	=A3*40	=25000+A3*25	=B3-C3
2000	=A4*40	=25000+A4*25	=B4-C4
3000	=A5*40	=25000+A5*25	=B5-C5
4000	=A6*40	=25000+A6*25	=B6-C6
5000	=A7*40	=25000+A7*25	=B7-C7

2)

Number of cups needed to be manufactured per month to break-even = Fixed cost / (selling price - Variable cost)

= 25000 / (40/1000-25/1000)

= 1,666,667 cups

3)

Quantity = 6000 boxes

Profit = 6000*(40-25)-25000

= $ 65,000

4)

Production volume required to make a profit of $ 25000 per month

= (Profit + Fixed cost)/(Selling price - Variable cost)

= (25000+25000)/(40/1000-25/1000)

= 3,333,333 cups

= 3,334 boxes

In EXCEL

FORMULAS:

B9 =B4/(B2-B3)*B5

B12 =B11*(B2-B3)-B4

B16 =(B14+B4)/(B2-B3)

B17 =B16*B5