Excel Profit and Loss Template: The Lines That Matter

The document

What an Excel profit and loss template is for

A profit and loss statement answers one question: over a period, did the trading activity make or lose money. It is not a record of what moved through the bank, and it is not a statement of what the business is worth. Those are two other documents, and confusing the first one with a P&L is the most common thing that goes wrong here.

Building it in a spreadsheet is a reasonable choice for a small operation. What a spreadsheet does not do is stop you filling it in wrongly, so the useful part of any guide is the part about what goes on the lines rather than the part about how to format them.

Timing

The cash-versus-accrual mistake

Here is the error that makes an otherwise careful P&L misleading. Export the bank statement, categorise every line, sum by month, and you have built something that looks exactly like a profit and loss statement and is not one. It is a record of when money moved.

The difference matters whenever payment and delivery happen in different months. Invoice in March, get paid in May, and a cash-based sheet shows a poor March and an excellent May. Neither is true: the work was done in March, so the revenue belongs in March, and May was simply the month a debt was settled.

The same applies on the cost side. An annual insurance bill paid in January is not a January expense of the full amount; it is twelve months of cover, and a P&L that charges all of it to one month makes January look terrible and the other eleven look better than they are.

You do not have to be rigorous about every small item to get the benefit. Handling the two or three largest timing differences honestly removes most of the distortion, and doing none of it removes most of the value.

The lines

The line order, and why gross profit sits where it does

The order is not a convention, it is an argument, and each subtotal answers a different question.

1. i.

   Revenue

   What you earned in the period, net of refunds and discounts given. Not what you invoiced, and not what landed in the bank.

2. ii.

   Cost of sales

   Only the costs that rise and fall with what you sold: materials, the goods themselves, direct labour, transaction fees. If it would be roughly zero in a month with no sales, it belongs here.

3. iii.

   Gross profit

   The one that matters

   Revenue minus cost of sales. This is whether the thing you sell makes money at the price you charge, before anything about how the business is run.

4. iv.

   Overheads

   The costs of existing rather than of selling: rent, software, insurance, accountancy, most salaries. They would still arrive in a month with no sales at all.

5. v.

   Net profit

   Gross profit minus overheads. The number people ask for, and the least diagnostic of the five, because it cannot tell you which half went wrong.

The split between the second and fourth lines is where most home-made statements collapse, because it takes a judgement per cost and a single expenses block takes none. The price of skipping it is that a falling net profit has two possible causes and your document cannot distinguish them.

Without a gross profit line, a falling profit has two possible explanations and your statement cannot tell you which one you have.

Drawings

What the owner takes out is not an expense

This one appears constantly in owner-built statements and it is a genuine error rather than a stylistic preference. Money the owner withdraws from the business is a distribution of profit, not a cost of producing it. It sits after the P&L, not inside it.

Book it as an expense and two things go wrong at once. The profit figure is understated, so the business looks less viable than it is, and any decision based on that figure, about pricing, hiring or whether to continue, is made on a number that is wrong in a known direction. Anything computed from profit is wrong with it.

A wage paid to the owner through a payroll is a different matter from an ad-hoc withdrawal, and which applies depends on how the business is structured and where it is. That is a question for an accountant who knows your situation rather than for a spreadsheet guide, and this post is not tax or accounting advice.

The build

Building it so a new month does not break it

The Excel-specific failure is the one this whole lane keeps running into. A statement built with fixed ranges works beautifully for the months you built it for and silently stops counting when the data grows past them. A subtotal reading a range that ends at row 60 does not error when row 61 arrives; it just quietly excludes it.

The structural fix is to keep the underlying transactions in an Excel Table and build the statement from it rather than from raw cells. Microsoft documents that “by entering a formula in one cell in a table column, you can create a calculated column in which that formula is instantly applied to all other cells in that table column”, and that “instead of using cell references, such as A1 and R1C1, you can use structured references that reference table names in a formula”.

Structured references are the part that matters here. A formula that names a table and a column keeps describing the same thing as the data grows, where a formula naming a rectangle describes a rectangle. Microsoft also notes that a table offers a summary row at its foot with an AutoSum drop-down offering functions such as SUM and AVERAGE, which is a convenience rather than the reason to use one.

Keep the transactions on one sheet and the statement on another, reading from it. Two sheets, one direction of flow, and nobody ever types a figure into the statement itself. Our Excel budget template guide makes the same argument at the point where a household budget outgrows its ranges, and the Excel templates hub covers the free files.

The limit

What a P&L will not tell you

Two things, and both catch profitable businesses out. It will not tell you whether you can pay next week’s bills, because a profitable month with everything on sixty-day terms has no money in it. That is the cash flow question, and it is the reason the cash-based statement people accidentally build is useful, just not as a P&L.

And it will not tell you what the business owns or owes. Stock, equipment and money owed to you sit on a balance sheet, which is a separate document answering a separate question at a point in time rather than over a period.

If the trading is property rental rather than a business, the shape is different again: the IRS describes Schedule E (Form 1040) as covering “Supplemental Income and Loss”, with its own list of deductible categories and its own rule that expenses must be split between rental and personal use where a property is used both ways. Our Airbnb Excel spreadsheet guide covers that case, and the expense tracker guide covers the capture habits that feed either.

Put revenue in the month it was earned, keep drawings out of the expenses, and give yourself a gross profit line. Those three make the difference between a statement that explains the business and one that merely adds up. Tell us in the comments whether your version has a gross profit line, because it is the one most owner-built statements are missing.

FAQ

Common questions, answered briefly

People also ask

Other questions, briefly answered