Excel Companion Chapter 1 section 2
Break Even Type I - Manufacturer’s Perspective LinBE.xls

Open the file LinBE.xls and click the tab for BE Type I

SITUATION 1: The Babson Press must decide whether or not to publish a book. The estimated costs of publishing this book include a fixed cost of $5,500 plus $ 8.75 for each copy printed. Projected sales figures suggest that 1,200 copies of the book can be sold at $25.00 per copy.

LinBE.xls –BE Type I

QUESTIONS Type I :

1. Under these conditions, can the press make a profit with this book?
2. Determine the cost, revenue, and profit if 1000 books are made and sold.
3. To the nearest book, what is the break-even quantity?
4. If selling price and variable cost per unit are unchanged, but fixed cost can be reduced to $4,250, then what will be the impact on the break even quantity?
5. Suppose fixed cost is $9,000, variable cost per book is $12.00, and selling price is $22.00 per book.  
  a) What is the break-even quantity?
  b) What is the break-even dollars of sales?
6. Let the variable q represent quantity and let the parameters Vc and Fc stand for variable cost per unit and fixed cost, respectively.  
  a) Write a formula for cost as a linear function of q.
  b) Let p be the parameter for unit selling price and write a formula for revenue as a linear function of q.
  c) Write a simplified formula for profit as a linear function of q

 

SITUATION 2: An electric utility is about to bring a new generating unit into production at a fixed cost of $400,000. It will cost the utility $0.025 (2.5 cents) per kilowatt-hour to generate electrical power which they can sell for $0.075 (7.5 cents) per kwh. Choose an appropriate scale to represent this situation graphically with BE type I. It will be necessary to make adjustments to the number of decimal places displayed, to the increment size, and to the starting value of q. The intersection point of the cost and revenue functions must appear in the final chart.

7. What was your choice for a starting value and an increment?
8. Let q be the number of kwh. Write simplified formulas for cost, revenue, and profit as functions of q.
9. Determine the break-even quantity in millions of kwh and the break-even dollar of sales.

 

Break Even Type II - Accountant’s Perspective LinBE.xls

Open the file LinBE.xls and click the tab for BE Type II

SITUATION I. A company’s accountants are more interested in the total dollars generated by sales than the precise number of units sold. The accountants have access to data relating the company’s revenue, its dollars of sales in a given period, the company’s fixed cost , and the total variable amount that the company had to spend in order to bring in that revenue. The horizontal axis and the vertical axis are both scaled in dollars. In this first situation, fixed costs are $70,000 and the company operates on a margin of 45%. This means that for each dollar of sales, the manufacturer’s "contribution" is $1.00 - $0.45 = $0.55. For s dollars in sales, the variable cost to the manufacturer is .55s dollars. There is another variable cost of 6% related to selling expenses. Total variable cost as a percent of sales are 55% plus 6% or 61%. This means that 61 cents of each one dollar of sales is utilized to cover variable cost. The table and graph indicate that revenue equals cost when sales are near $180,000.

LinBE.xls – BE Type II

QUESTIONS Type II:

10. To the nearest hundred dollars, what is the break-even sales? (remember that you can adjust the starting value and the increment).

For questions 11- 13 that follow, assume that: a company makes personal care products and has a division that operates on a margin of 42%. This division also incurs selling expenses, which amount to 7% of sales. Fixed costs are $850,000.  
11. Determine the profit that corresponds to:  
  a) sales of two million dollars
  b) sales of four million dollars.
12.   Adjust the starting sales and the increment and examine the table or graph to estimate the break-even point to the nearest ten thousand dollars.
13. What is the break-even point to the nearest dollar? 

Enter your name here:

first name last name


Copyright © Joseph F. Aieta, Babson College 1997