Question

Use Microsoft excel to solve the following problem. Insert screenshots of your excel spreadsheet to provide answers and explain your reasoning. If use any equations for cells, provide that information in your solution. Stew’s Plastics produces a variety of CD cases. The best-selling product is the CD-50. Several products are produced on the same manufacturing line, so there is a setup cost each time a changeover is made for a new product. The setup cost for the CD-50 is $3650. In addition, it costs $1.72 for each unit (CD Case) produced, and for each 100 CD Cases they have to put them in a box that costs $1.98. If there is less than 100 CD Cases, they will still need to put them in a box (in other words, if they had 102 CD Cases, they would do 2 boxes, one with 100 CD Cases and one with 2 CD Cases).

What is the break-even point (in terms of number of CD cases rounded to the nearest integer) if they sell them for $6.85 each?

Answer 1

To find the break-even point for Stew's Plastics, we need to calculate the total cost and total revenue at different levels of production. By comparing the total cost and total revenue, we can determine the break-even point.

Step-by-step explanation:

To find the break-even point for Stew's Plastics, we need to calculate the total cost and total revenue at different levels of production. The total cost includes the setup cost, the cost per unit, and the cost of the boxes. The total revenue is the selling price multiplied by the number of CD cases sold. By comparing the total cost and total revenue, we can determine the break-even point.

To set up the analysis in Excel, we can create a table with the number of CD cases produced as the input and calculate the total cost, total revenue, and profit for each level of production. To calculate the total cost, we can multiply the cost per unit by the number of CD cases and add the setup cost and the cost of the boxes. The total revenue is the selling price multiplied by the number of CD cases sold.

Next, we can create a line chart to visualize the total cost and total revenue curves. The break-even point is where the total cost curve intersects with the total revenue curve. We can find the break-even point by looking for the number of CD cases where the profit is zero.