Break-even template in Excel: units and sales value

Break-even template in Excel: units and sales value

A business can sell a lot and still make no money: what comes in goes straight back out in goods, payroll and rent, and at the end of the month the owner cannot tell whether the problem was the price, the volume sold or the costs that do not move with sales. The break-even point answers that question with a single figure: how many units must be sold to neither lose nor gain.

This break-even template in Excel works out that figure from four numbers you already have: the selling price, the variable cost per unit, the fixed costs of the month and the profit you want to make. It brings the automatic result, a sensitivity table with eleven quantities and a line chart where revenue and total cost cross. Type your figures and read the answer on the same sheet.

What the break-even point is and who it is for

The break-even point is the number of units that must be sold for revenue to cover costs exactly. At that point there is neither a loss nor a profit: everything has been paid and nothing is left over. That is why it is described as the floor of the business and not the goal. Above that quantity, every extra unit leaves a slice of profit; below it, every missing unit leaves a gap that somebody has to fill with cash.

The format serves the shop owner who wants to know how many items must be sold; the workshop that wants to know how many jobs must be invoiced to cover rent and payroll; the distributor who needs to check whether the price being charged leaves a margin; the salesperson who wants to understand why a good month in sales did not turn into profit. It also serves the accountant or adviser who has to explain in a meeting, with a chart on the table, why cutting the price without cutting the cost does not increase the profit.

The file is meant for one product line or one service at a time, not for the whole business mixed together. If you sell several products with different prices and costs, work out the break-even point of each one separately, because an average margin hides precisely the product that leaves nothing.

Why fixed costs must be kept apart from variable costs

The template only works if your costs are classified properly. A variable cost changes with every unit sold: the goods, the packaging, the commission, the freight on that sale. If you do not sell, that cost disappears. A fixed cost is paid either way, whether you sell a lot or a little: rent, the manager's salary, internet, security, the loan instalment.

That separation is the heart of the calculation, because fixed costs are the amount to be covered and the margin on each sale is the brick used to cover it. If rent goes in with the variable costs, the sheet will tell you each sale is less profitable than it is and the break-even point will come out too high; if the goods stay as a fixed cost, the result comes out too low and the decision gets taken on a false number.

When a cost is mixed, such as electricity or the salesperson's phone, split it: the part paid even with no sales is fixed and the part that rises with output is variable. There is no need for an accountant's precision; there is a need for the criterion to be written down and used every month, so that one month's figures can be compared with the next.

What the template includes

The file is an .xlsx spreadsheet with two tabs: the calculation tab and the instructions tab. On the first you type four numbers and everything else is calculated for you; on the second the guidance and a worked example are kept. This is what it contains:

The columns and fields of the sheet

The sheet does not carry hundreds of columns: it carries just the fields needed so nobody gets lost among formulas. The header holds the company, the product or service and the currency. Under it sit the four input figures, the results block and, at the bottom, the sensitivity table with columns of its own.

How it is calculated, with a worked example

The contribution margin is what each sale brings in to cover the fixed costs and, after that, to leave a profit. It is obtained by subtracting the variable cost from the price. With that margin in hand, the break-even point in units is the total of the fixed costs divided by the margin. The break-even point in sales value is that number of units multiplied by the price. And if you are after a specific profit, the calculation shifts a little: fixed costs plus the profit you want, divided by the margin.

Take the example built into the template: price 10,000, variable cost 6,000 and fixed costs 2,000,000. The contribution margin comes to 4,000 per unit, that is, forty per cent of the price. With fixed costs of 2,000,000 divided by 4,000, the break-even point is 500 units, which in sales value is 5,000,000. Everything sold below 500 units leaves a loss; from unit 501 onwards, every sale leaves 4,000 that go first towards profit.

How to read the chart and the sensitivity table

The chart crosses two lines: revenue, which climbs with every unit sold, and total cost, which starts at the fixed costs and rises more slowly. While the revenue line stays below, the business is losing money; when the two lines cross, it is exactly at the break-even point; to the right of the crossing, the distance between the two lines is the profit for the period.

The sensitivity table is that same chart in numbers: eleven quantities with their revenue, variable costs, total cost and profit. In the example, at zero units the loss is 2,000,000; at 500 units the profit is zero, because that is where the lines cross; at 1,000 units the profit is 2,000,000. Look for the row where profit stops being negative: that row is your floor.

Step by step

1. Download the file and open it in Excel or in a compatible spreadsheet program.
2. Type the company, the product or service and the currency in the header.
3. Fill in the selling price per unit, the variable cost per unit and the fixed costs of the month. If you are chasing a specific profit, write it down; if not, leave it at zero.
4. Read the results block: contribution margin, break-even point in units and break-even point in sales value.
5. Go through the sensitivity table and find the row where profit stops being negative.
6. Change the price or the variable cost and watch how the crossing moves on the chart. Keep a copy for every scenario you want to compare.

Tips and common mistakes

• The break-even point is the floor, not the goal. Selling exactly that quantity leaves no profit: it only covers the costs. Set a target above it and work out how many units you need to get there.
• Separate fixed from variable with a written criterion. If rent slips in with the variable costs the break-even point comes out too high; if the goods slip in with the fixed costs it comes out too low.
• Use the same period throughout. Price and variable cost are per unit; fixed costs must be those of the same month. Mixing a quarterly rent with monthly sales throws the result out.
• Revise the variable cost when the supplier or the freight changes. An old cost produces an old margin and a break-even point that no longer exists.
• Do not forget the costs paid every month even when they are not on the invoice: fixed commissions, loan instalments, security, software, accountant fees.
• Look at the margin as a share of the price, not only the margin in money. Two products can each leave 4,000, but if one sells at 8,000 and the other at 20,000, the effort of selling the first is far greater.

When to move to inventory and sales software

The template solves the arithmetic, but it depends on somebody typing the price and the cost by hand. As a business grows, the problem stops being the calculation and becomes where the numbers come from. If the variable cost is built from the average cost of the items that left the inventory, and that cost lives in the system records, the sheet is disconnected from reality. That is where Kardex Tauro helps: receipts, issues and the cost of every item stay in one place, so the figure you need for the calculation is already worked out instead of being chased between folders.

The natural step is not to replace the spreadsheet overnight, but to stop retyping. While the volume is small and the catalogue is short, the template is enough and it is cheap. Once several warehouses, several salespeople or hundreds of references appear, the cost question becomes a daily one and the manual work no longer holds. That is the moment to look at software: when the calculation is easy but getting the right figure has become the job.

Download the template, type your three numbers and see where your floor sits. That single figure already changes the conversation for the month.