Profit and Loss Excel - Free Template

1. Open P&L_Data and enter each sales invoice or expense line on a separate row. Use the same date, reference and category format each time.
2. Check the VAT rate, net amount and gross amount for each item. If you are VAT-registered, make sure the VAT column agrees with the invoice.
3. Update the Paid? column and payment method when money clears. That gives you a quick view of what is still outstanding.
4. Go to P&L_Summary after you have entered the month’s transactions. Use it to review totals by category and see whether income is covering overheads.
5. Use the Dashboard for a fast management check. It is the easiest place to spot trends in sales, costs and overall profit.
6. Keep the Instructions sheet nearby if you are handing the file to someone else. It helps them follow the same process without guessing.

A UK sole trader can use this when they are building a month-end view before Self Assessment, while a bookkeeper at a Ltd company can use it to check trading performance before the management accounts go out. A trades firm with 4 employees might enter 60 sales and purchase lines in a month; an online shop might enter 300 orders and returns, then look for the true margin after postage and fees.

When the file gets opened

The pressure point is usually the end of the month, the VAT quarter, payroll week or year-end close. If you are trying to see whether a £24,000 sales month really made money after £8,500 of wages, £2,100 of materials and £1,400 of overheads, a proper profit and loss layout does the job quickly.

Why the summary matters

The P&L_Summary sheet pulls the figures together so you do not have to total every line by hand. That is useful for a club treasurer, a freelance consultant or the office manager who needs one clean report before the director’s meeting.

HMRC expects you to keep enough records to support the numbers in your accounts and tax return. For the self-employed, keep records for at least 5 years after the 31 January Self Assessment deadline; for companies, keep accounting records for 6 years.

VAT and invoice detail

If you are VAT-registered, this workbook fits the practical parts of the return: tax point date, net value, VAT value and gross amount. In 2026 the VAT registration threshold is £90,000 of taxable turnover, the standard rate is 20%, the reduced rate is 5% and the zero rate applies to items such as most food, children’s clothing and books.

Why the structure is useful at quarter-end

A quarterly return under Making Tax Digital is easier when the figures are already sorted by date, category and VAT rate. If you have 120 purchase invoices at an average net cost of £85, the VAT check is much quicker when the file is already carrying the VAT column and not just one total number.

That same date-and-VAT layout also makes an invoice tracking sheet straightforward at quarter-end.

The common failure is not the maths, it is the coding. A £1,200 design fee gets entered as income one month and a refund the next, or a £480 laptop is treated as stationery instead of a capital item, so the margin looks wrong and the year-end accounts need tidying later.

Bad VAT treatment

If you put a VAT-inclusive figure into the net column, the totals become nonsense straight away. For example, £1,200 gross at 20% VAT should be £1,000 net and £200 VAT; if you record £1,200 as net, you overstate sales and tax by £240 on that one line.

Cash flow blind spots

Another mistake is ignoring unpaid invoices. A month with £18,000 of sales and £6,000 still unpaid is not the same as £18,000 collected, and that difference matters when wages, rent and supplier terms fall due.

Those unpaid items also often hide reimbursable staff costs, so a simple expense claim form keeps the cash flow picture honest.

Use it at the same time every week, not only when the books are already messy. The simplest habit is to update P&L_Data straight after the sales invoice run or purchase batch, then review the summary before the month closes.

Three habits that stick

• Copy the previous month’s working file and clear only the transaction rows you no longer need.
• Review the dashboard every Friday so issues show up before the next payroll or supplier payment run.
• Keep categories consistent, so marketing, fuel, subcontractors and office costs do not drift into mixed labels.

When to move on

If you are pushing past a few thousand rows a year, need stock or jobs costing, or want live bank feeds, a spreadsheet starts to become too manual. At that point, move to bookkeeping software and keep this workbook for reporting, checking and training new staff.