1. Money

Using a Spreadsheet to Figure Breakeven Point Analysis

Quick and Easy Way to and Simple to Modify

By

As I discuss in the first article of this series on breakeven point analysis, for accounting purposes breakeven point is when your total revenues equal your total expenses for your arts and crafts business. In other word, you neither made nor lost money.

What Breakeven Point Tells You

How many craft items you have to sell to cover your costs is important info for you the business owner as you have to be able to hand craft your arts and crafts items at a price that your customers will pay while still providing an adequate amount of income to cover your personal living expenses. This can be a laborious process, if you've trying to do it with a pencil and calculator, once you get the hang of it you will find it quick and easy to figure breakeven point using an Excel spreadsheet.

Facts and Numbers for the Breakeven Point Calculation

As a review, here are some basic facts and numbers for the sample arts and crafts business, Oak Desk Clocks, Inc.:

  1. Sales price per clock is $35.00 with an expected increase in sales price of 10% per year.
  2. Variable costs per clock are $25.00 with an expected increase in the price of raw materials and labor of 5% per year.
  3. Fixed costs per year are $75,000, which Oak Desk Clocks feels will remain constant over the next five years.
  4. Advertising expenses will be a major expense in the first year of business, but should decrease by 12% each year over the next five years.

What very important variable don't I have listing above? If you're scratching you head on this one - it's revenue. The objective of this breakeven point analysis is to figure out what volume of sales is need to cover all costs.

You can easily modify this basic analysis to figure the value of just about any of the above as unknowns. I'll show you how to do that in future articles in this series as well as show you how to add in some payroll dollars for that all important member of you business - you the owner!

Setting Up the Breakeven Point Excel Spreadsheet

It's really important that you use the exact same cells as I list below for this sample analysis. After you become more familiar with the process, you can set up your spreadsheets any way you like, but for this learning process, just follow my lead.

Your first step is to enter the known variables into the spreadsheet. Using whatever spreadsheet program you prefer, open a new workbook, name it Breakeven Point Analysis.

I have entered the preliminary assumptions into my breakeven point spreadsheet which form the backbone of all future calculations. Make sure you use the exact same cells as in the image or your breakeven point analysis won't be right. Use this breakeven point image to guide you - remember you can click on the image to make it larger.

Use the following as your written road map to accompany the image I reference in the paragraph above:

  • Type Selling Price per Unit in cell A6: 35.00 in cell B6. Format cells D6 through D9 for percentages entering 10% in D6, representing the change in selling price per unit each year.
  • Type Cost of Goods Sold in cell A7; 25.00 in cell B7; and 5% in cell D7
  • Type Fixed Costs in cell A8; 75,000 in cell B8; don't enter anything into cell D8 since Oak Desk Clocks reckons this figure remains constant.
  • Type Advertising in cell A9; 15,000 in cell B9; and -12% in cell D9.

The next article in this series about breakeven point analysis sets up breakeven point for four years for Oak Desk Clocks, Inc.

©2014 About.com. All rights reserved.