A profit and loss statement, often just called a P&L, tells you whether your business made money over a period. It is the report your accountant, your bank and HMRC all understand, and you can build a perfectly good one in Excel. In this guide I will take you through the structure from revenue down to net profit, with the formulas that tie it together. Once you have built it, you will have a single page that answers the most important question in business: are we actually making money?
Many owners only ever see a P&L once a year, prepared by their accountant long after the period has ended. That is far too late to be useful for decisions. Building your own in Excel lets you produce one every month, while the figures are fresh and you can still act on them. It does not need to be as polished as your formal accounts; it needs to be timely and honest, so you can spot a slipping margin or a runaway cost before it does real damage.
The shape of a P&L
A P&L flows downwards in a set order. You start with sales, take off the direct cost of those sales to get gross profit, then take off your running costs to get net profit. Following that order is what makes the report readable.
| Line | Amount (£) |
|---|---|
| Revenue | 84,000.00 |
| Cost of sales | 31,000.00 |
| Gross profit | 53,000.00 |
| Overheads | 38,500.00 |
| Net profit | 14,500.00 |
Step 1: Total your revenue
Revenue is all your sales income for the period, excluding VAT. If your sales are listed on another tab, bring the total in with =SUM(Sales!D:D). Keep VAT out of the P&L entirely: it is not your income, it is money you collect for HMRC. This single point trips up more first-time spreadsheet accounts than any other, so it is worth burning into memory: the P&L deals in net figures throughout.
Pulling the total from a separate sales tab rather than typing it keeps your P&L honest. When you add a new sale to the list, the revenue line updates on its own, so the report can never quietly fall out of step with your underlying records. The same principle applies to every line below: where a figure exists somewhere else in your books, link to it rather than retyping it, and your whole statement stays consistent by design.
Step 2: Work out gross profit
Cost of sales is the direct cost of what you sold, such as materials or stock. Gross profit is revenue minus cost of sales. With revenue in B2 and cost of sales in B3, gross profit is =B2-B3. This figure tells you how much each pound of sales contributes before your fixed running costs.
Step 3: List and total your overheads
Overheads are the costs of running the business regardless of sales, such as rent, software, insurance and wages. List them line by line, then total them with =SUM(B6:B20). A clear breakdown here is where you spot creeping costs, like three subscriptions you forgot you were paying for.
Resist the urge to lump everything into a single “expenses” figure. The detail is the value. When rent, software, marketing and travel each have their own line, a glance down the column tells you where your money actually goes, and a comparison across months tells you what is changing. Many businesses discover their biggest savings not by cutting sales-related costs but by tidying up overheads they had stopped noticing, and you can only do that if the P&L shows them individually rather than hiding them in a total.
Cash basis or accruals: a quick word
There is one decision worth understanding before your P&L can be truly accurate, and that is when you record income and costs. Under the cash basis, you count money when it actually moves in or out of the bank, which is simple and suits many small sole traders. Under accruals, you count income when you earn it and costs when you incur them, regardless of when the cash moves, which gives a truer picture of a period’s performance and is required for most limited companies. The structure of the P&L is identical either way; only the timing of when a figure lands in a given month changes. Pick the basis that matches how your accounts are prepared, be consistent, and if in any doubt have a quick word with your accountant, because mixing the two within one statement is what produces figures that never quite make sense.
Step 4: Calculate net profit
Net profit is gross profit minus total overheads. With gross profit in B4 and overheads in B21, use =B4-B21. This is the bottom line: what the business actually earned after everything. If it is negative, the report is telling you costs are outrunning sales.
Step 5: Add useful margins
Two quick ratios turn the P&L into a tool. Gross margin is gross profit divided by revenue, and net margin is net profit divided by revenue. Protect them from a blank period with =IFERROR(B4/B2,0) and format as a percentage. Tracking these month to month shows whether your business is getting more or less efficient, not just bigger. A business can grow its sales while its margins quietly shrink, and the headline revenue figure hides that entirely; the margins do not.
If you build one column per month across the year, you can lay your P&L lines down the side and read trends straight across. Suddenly patterns jump out: a cost that creeps up every month, a margin that dips in your quiet season, a marketing spend that does or does not move sales. This is the moment a spreadsheet stops being bookkeeping and starts being management information, and it is well within reach of anyone willing to keep the figures current. Our profit and loss template sets all of this up for you, and it sits naturally alongside a cash flow forecast template.
Common mistakes
- Including VAT in revenue. The P&L works on net figures. VAT is not income.
- Mixing cost of sales with overheads. Direct costs belong above gross profit; running costs belong below it. Mixing them hides your true margin.
- Putting capital purchases in the P&L. Large equipment is usually treated as an asset and depreciated, not expensed in one go. Ask your accountant.
- Dividing by zero. Wrap your margin formulas in
=IFERROR(...)so an empty month does not show#DIV/0!.
Frequently asked questions
What is the difference between gross and net profit?
Gross profit is revenue minus the direct cost of sales. Net profit is gross profit minus all your overheads. Net profit is the true bottom line.
Should I include VAT in a profit and loss statement?
No. A P&L uses net figures. VAT is collected on behalf of HMRC and is not part of your income or your costs.
What counts as cost of sales?
Costs directly tied to making a sale, such as materials, stock bought for resale or subcontractor labour on a specific job. Rent and insurance are overheads, not cost of sales.
How often should I produce a P&L?
Monthly is ideal for spotting trends, with a full version at the year end for your accounts and tax. A consistent layout makes month-on-month comparison easy.
