Profit and Loss Statement Template: Layout, Excel Formulas and Example
A profit and loss statement template for a small business: the line-by-line layout, monthly and year-to-date columns, SUMIFS formulas that pull from your bookkeeping, a worked example and the checks that keep it accurate.
Short answer: a profit and loss (P&L) statement template lists, from top to bottom: revenue, minus returns and discounts to get net revenue; minus cost of goods sold to get gross profit; minus operating expenses to get operating profit; then interest and other items to reach net profit. In Excel, give each month its own column, add a year-to-date total and a “% of revenue” column, and let SUMIFS pull every figure from your bookkeeping so nothing is typed twice.
This guide gives you the layout to copy, the formulas, a worked example and the checks that make the numbers trustworthy. If you want to learn how to interpret a finished P&L, see our companion guide on how to read a profit and loss statement.
The P&L template layout
| Line | How it is calculated |
|---|---|
| Sales revenue | All sales in the period, before deductions |
| − Returns, refunds and discounts | Money given back or not collected |
| = Net revenue | Sales − returns and discounts |
| − Cost of goods sold (COGS) | Product costs, materials, inbound shipping, direct labor |
| = Gross profit | Net revenue − COGS |
| − Operating expenses | Marketing, selling and payment fees, wages, rent, software, insurance, professional fees, other |
| = Operating profit | Gross profit − operating expenses |
| ± Other income and expenses | Interest paid or earned, one-off gains or losses |
| = Profit before tax | Operating profit ± other items |
| − Income tax (if the business pays it) | Corporations only; see the note below |
| = Net profit | The bottom line |
Use the same expense categories as your bookkeeping. If your books have a category the P&L does not, totals will never match.
A note on tax: sole proprietors and single-member LLCs taxed as sole proprietors do not pay income tax at the business level — profit flows to the owner’s personal return. For them, the P&L usually ends at profit before tax, and owner’s draws are not an expense.
Building the template in Excel
Columns
Put line names in column A and the first day of each month in row 3 (C3 = 1 Jan, D3 = 1 Feb, and so on through N3). Add O for year to date and P for % of revenue. Format the dates to show only the month.
Pulling numbers from your bookkeeping
If your transactions are in a sheet called Transactions (A Date, B Description, C Category, D Amount), each P&L cell for a category in column A is:
=SUMIFS(Transactions!$D:$D,Transactions!$C:$C,$A5,Transactions!$A:$A,">="&C$3,Transactions!$A:$A,"<"&EDATE(C$3,1))
Copy it across all months and down all category rows. Our bookkeeping template in Excel guide shows how to set up that transactions sheet with categories and drop-downs.
Subtotals and ratios
- Net revenue:
=C5-C6(sales minus returns and discounts) - Gross profit:
=C7-C8 - Total operating expenses:
=SUM(C11:C18) - Operating profit:
=C9-C19 - Year to date (O):
=SUM(C5:N5) - % of revenue (P):
=IFERROR(O5/$O$7,0)— every line as a share of net revenue
Make subtotal rows bold and keep them formula-only. Protect the sheet so only the transactions sheet is edited by hand.
A worked example
A one-month P&L for a small online shop. The figures are illustrative.
| Line | March ($) | % of net revenue |
|---|---|---|
| Sales revenue | 18,500 | |
| − Returns and discounts | 500 | |
| Net revenue | 18,000 | 100.0% |
| − Cost of goods sold | 7,200 | 40.0% |
| Gross profit | 10,800 | 60.0% |
| Marketing | 1,500 | 8.3% |
| Platform and payment fees | 1,260 | 7.0% |
| Wages | 4,000 | 22.2% |
| Rent | 900 | 5.0% |
| Software | 300 | 1.7% |
| Other operating expenses | 540 | 3.0% |
| Total operating expenses | 8,500 | 47.2% |
| Operating profit | 2,300 | 12.8% |
| − Interest | 100 | 0.6% |
| Profit before tax | 2,200 | 12.2% |
The “% of revenue” column is what makes a P&L useful for decisions. Here, every $100 of net revenue leaves $60 after product costs and $12.80 after running costs. If next month’s gross margin slips to 55%, you will see it immediately — and our profit margin calculator guide shows how to trace that back to prices and costs.
Single-step or multi-step?
| Single-step P&L | Multi-step P&L (this template) | |
|---|---|---|
| Structure | All revenue minus all expenses | Gross profit, operating profit, net profit shown separately |
| Best for | Service businesses with no product costs | Anyone who sells products or wants margin insight |
| Shows gross margin? | No | Yes |
A freelancer or consultant with no cost of goods can delete the COGS line; the rest of the template works unchanged.
Three useful variations
- Budget vs actual: add a planned column next to each month and a variance column (
=actual − budget). Investigate any line that is off by more than a set amount. - Prior-year comparison: put last year’s month next to this year’s and calculate the change in dollars and percent.
- By channel: split revenue, COGS and fees by sales channel (own website, marketplace, wholesale) to see which channel actually makes money.
Checks before you trust the numbers
- Reconcile your accounts first. A P&L built on unreconciled bank and card statements is a guess.
- Check that every transaction has a category. A quick filter for blank categories catches most errors.
- Keep transfers and owner contributions out. Moving money between your own accounts, loan principal and owner’s draws are not revenue or expenses.
- Be consistent with cash or accrual. Mixing them month to month makes trends meaningless. Our small business bookkeeping guide explains the difference.
- Look at the ratios. A sudden jump in a “% of revenue” line is usually a misclassified transaction rather than a real change.
Frequently asked questions
Is a profit and loss statement the same as an income statement?
Yes. Profit and loss statement, P&L, income statement and statement of operations are different names for the same report.
Do sole proprietors need a P&L?
There is usually no separate filing requirement, but the numbers on Schedule C are essentially a P&L, and lenders, landlords and grant programs often ask for one. Keeping a monthly P&L makes tax time much easier.
How often should I prepare a P&L?
Monthly is ideal for decisions, with a quarterly and annual view. With formulas linked to your bookkeeping, a monthly P&L updates itself once transactions are categorized.
What is the difference between a P&L and a cash flow statement?
The P&L shows whether you made a profit; the cash flow statement shows where cash came from and went. A profitable business can still run short of cash, for example while waiting for customers to pay or when buying inventory.
Can I use this template in Google Sheets?
Yes. SUMIFS, EDATE and IFERROR work the same way in Google Sheets.
More guides on keeping your books are collected in Bookkeeping & Income Tracking.