A food industry client recently asked me to help them calculate their breakeven point, which is the Revenue they needed to achieve and be at zero profits.  Here are the steps I went through to complete the analysis:

1.  I reviewed their p&l from QuickBooks, to understand what was in each major category:

• Cost of Goods Sold: ingredient costs, direct labor, and a third category which was called Fixed Cost of Production, and included all of the other factory overhead, such as support personnel, rent, insurance, depreciation, repair and maintenance, and so on.
• Other Expense, which included outbound shipping, marketing and selling, office expense, and staff salaries

2.  I asked the client what was their ability to manage Direct Labor up or down, in response to higher or lower unit sales volumes and learned that he could do that to a fairly great degree. So in this cae I assumed that direct labor really had two components:50% was variable (i.e., would change in direct relations to sales/production), and 50% was fixed.   The % split may be very different for your business.

3.  Now it was time to create an Excel model to make this work easier to do, review and document.   I created an Excel spreadsheet with four rows:

• Revenue
• Variable Cost
• Fixed Cost
• Profit

Using the client’s actual 2011 p&l results, I populated this simple spreadsheet, so that the Variable Cost included the Ingredient Costs, plus 50% of the Direct Labor (see point 1) , while the fixed cost included 50% of the Direct Labor,  plus the Fixed Cost of Production and Other Expense.

4.  Next I inserted columns to the spreadsheet for Revenue at 80%, 85%, 90%, 95%, 105%, 110%, 115% and 120% of actual 2011 results.    For example, assuming that 2011 actual revenue was $2.0 million for this client, the spreadsheet would model possible results from $1.6 million to $2.4 million.

5.   I then went on to add formula which adjusted the Variable Cost in relationship to Revenue, taking it up or down by the same percentage from the actual 2011 results.   Fixed Cost was kept constant for all the lower and higher Revenue columns.

6.  In all cases, Revenue – Variable Cost – Fixed Cost = Profit, so I added this formula in the Profit row for all nine columns.  In this instance, it became immediate apparently where the breakeven point was.  If you are modeling a business that has Revenue that is either more than 20% above, or more than 20% below the breakeven point, you will have to broaden the range of possible Revenue outcomes to determine the breakeven point.

7.  This is a description how to do a very simple breakeven model, so is intended as a good starting point, not the final word.  There are a lot of additional considerations, that need more complex modeling than what I have described above, including the following:

• Some of the Other Expense may also be variable, including shipping costs and brokerage expense.
• In the real world, at some point increases in Revenue will max out the client’s current manufacturing capacity.
• Direct Labor may go up and down in a step-wise function, e.g., when a second shift is added or eliminated.
• Growing Revenue also usually requires additional working capital, for accounts receivable and inventories, which the company may lack, if it is cash constrained
• Using the last complete year’s actual reported results is expedient, but may miss out on recent cost trends

Good luck doing your breakeven analysis!  If you have questions on the above, please post them in the comments section, or send me an email at David@RudofskyAssociates.com.