Question

Answer the following break even problems. In addition to the answers (rounded to 0 decimal places), show the spreadsheets you created to solve them (like you do for homeworks in lab). Next to any cells with equations, put the equation in text. For example, if I was doing a spreadsheet to sum the number of fish my son and I caught, it would look like:

Q1 (5 points)

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 $4350. In addition, it costs $2.17 for each unit (CD Case) produced, and for each 120 CD Cases they have to put them in a box that costs $2.58. If there is less than 120 CD Cases they will put them in a box (in other words, if they had 122 CD Cases, they would do 2 boxes, one with 120 CD Cases and one with 2 CD Cases)

What is the break-even point (in terms of number of CD cases) if they sell them for $7.25 each?

Q2 (5 points)

Jerry, the manager of a small printing company, needs to replace a worn out copy machine. He is considering two machines; each has a monthly lease cost and a cost per page that is copied:

• Machine 1 has a $368 monthly lease with a 2.9 cents per page cost up to 300 pages, and then 1.9 cents per page after the 1^st 300 pages.
• Machine 2 has a $533 monthly lease with a 1.7 cents per page cost up to 300 pages, and then 1.0 cents per page after the 1^st 300 pages.

Jerry knows the break-even point is more than 300 pages for each machine. Determine the break-even point (per month) in terms of the number of copies for each machine if Jerry charges customers 5.5 cents per copy. Based on this, which machine do you recommend?

Answer 1

For Question-1:

For Question-2: Solving the question using Goal Seek method available in excel.

For both machine, we will create a table in excel naming - Number of copies, selling price, Fixed cost, variable cost, Total cost, total revenue and profit.

Now, Break Even Point is that point when the total cost becomes equals to total revenue; or in other words, when Profit of zero is achieved.

For Machine 1:

Here, Number of copies is blank for now.

Selling price per copy is 5.5 cents.

Fixed cost = 36800 (in cents)

Variable cost = =IF(B1>300,300*2.9+(B1-300)*1.9,B1*2.9)

Total Cost = =(B3+B4)

Total Revenue = =(B1*B2)

Profit = =(B6-B5)

Now, we go to Data tab, and select Goal seek analysis under what if analysis:

Now, when we click on Goal Seek, a window will pop up. Here, we want to set value of Profit (cell B7) as zero (break even condition) by changing Number of copies (B1). So we will give these values and click on OK.

After clicking on OK:

Thus, for break even, we need 10305.55556 copies for machine 1.

For Machine 2: Repeating all the above steps along with changing the given data-

For break even, we need 11891.11111 copies for machine 2.

Based on this, I would recommend Machine 1.