1. Money

Modifying the Breakeven Point Analysis Spreadsheet

Add Owner Compensation or Tailor to Your Business


The first few articles in this series about breakeven point analysis explain what breakeven point analysis is, which is a method to figure out what volume of revenue you need to cover your costs, and how to quickly and easily prepare a breakeven point analysis using a spreadsheet program such as Excel. This article wraps up the series by explaining how to modify the basic spreadsheet program.

Looking at the net income from prior pages, you see that I address revenue at varying levels and cost of goods sold , the difference between the two is gross profit. Now, cost of goods sold for this spreadsheet fluctuates based on varying revenue volume and also for owner estimates for the future cost of raw materials and labor.

Adding in Owner Payroll

Looking at the section after gross profit, I factor in fixed expenses, which are all expenses that pretty much stay the same no matter if you sell one item or one million items, and advertising. The really important expense that I don't specifically break out is owner compensation.

Well, I'm fixing that oversight right now! It's really easy to just add a line item for owner compensation on the spreadsheet . Insert a row between Fixed costs and Advertising, name it Owner Compensation, add the amount of owner compensation you want to receive and your breakeven point spreadsheet automatically resets all cell values so that breakeven point includes owner compensation.

For example, if you want to have owner compensation of $50,000 in year 1, enter 50,000 into that cell (in my spreadsheet it's cell B27). Clocks sold changes to 14,000 and gross sales is now $490,000 and cost of goods sold is $350,000 to achive your goal of $50,000 of officer compensation at breakeven point.

Showing Net Income

You can also modify the breakeven analysis to show net income. In this case, I want to show $100,000 of net income in each of the four years. Maybe, I'll use this for owner compensation or I might use the excess income to expand my arts and crafts business.

It's easy to make your spreadsheet do all the work for you by just changing the formula for Clocks Sold. Going back to the orginal spreadsheet before you added in $50,000 of office compensation, in year 1 the number of clocks sold is in cell B17. To show net income of $100,000,cell B17 has to be modified to show the difference between the sales price and cost of goods sold by clock. Then use this info to change the number of clocks sold.

It's easier to understand if I walk you through the calculations:

  1. For year 1 the sales price is $35 and cost of goods sold is $35. $35 - $25 is $10.
  2. To show net income of $100,000, divide your desired net income 100,000 by 10 (the difference between sales price and cost of goods sold)gives you 10,000.
  3. This is the additional number of clocks you have to sell to have net income of $100,000 in year one.
  4. Change the formula in cell B17 to the following: +B30/(B18-B21)+10000.

Voila! Clocks sold in year 1 goes from 9,000 to 19,000, gross profit increases to $190,000. Net income goes from 0 to $100,000.

Figuring Breakeven Point By Item

I tailor the breakeven point analysis in this series of articles to the entire company. But what about if you want to do breakeven point analysis for a certain product line or show breakeven point for each of your products?

Once you have the basic breakeven point spreadsheet set up you can copy and modify it by product. Here are the steps:

  1. Figure selling price and cost of goods sold for each individual item.
  2. Allocate fixed costs per product line. To make this really simple, you can allocate fixed costs evenly among your products. If you have 10 different products and fixed costs per year are $90,000, fixed costs per product are $9,000.

©2014 About.com. All rights reserved.