Profit and Loss Template for Google Sheets: Layout, Formulas and a Worked Example

What's the best format for a P&L?

For a small business, the best format is a single-step-per-line statement: revenue at the top, direct costs, gross profit, operating expenses grouped into 8 to 15 categories, operating profit, then tax and net profit. Months run across as columns with a year-to-date column at the end.

Template galleries (Smartsheet, Microsoft, Template.net) offer dozens of variations of this layout. The format matters less than two habits: put every cost in exactly one row, and let formulas, not typing, fill the totals. I build spreadsheet math; I am not an accountant, and this is not tax or financial advice.

Try the P&L calculator (free, no signup)

Enter one month's revenue, direct costs and operating expenses. The calculator returns gross profit, operating profit and each margin, using the same math as the worked example.

Worked example: a service business P&L for one month

With $18,500.00 of revenue, $5,200.00 of direct costs and $8,400.00 of operating expenses, the month shows $13,300.00 gross profit and $4,900.00 operating profit. All figures are assumptions for illustration; replace each with your own.

For a service business, direct costs are what you would not spend if the job did not happen: subcontractors, job materials, fuel for the job, and the wages of the people doing the work. Rent, the phone plan and the accounting software are operating expenses, because they arrive whether or not you work that week.

If you set money aside for tax, show it. With an assumed 25% reserve (a placeholder; your rate depends on your structure and income) the month's net profit falls to $3,675.00:

Monthly vs year-to-date P&L in Google Sheets

Put each month in its own column and add a YTD column that sums the months. Then calculate YTD margins from the YTD totals. Averaging the three monthly margins gives the wrong answer whenever revenue changes from month to month.

Gross margin barely moved across the quarter, but operating margin rose from 18.42% to 26.49% because revenue grew faster than overhead. That is the story a P&L exists to tell, and it only shows up when overhead has its own row.

How to make a profit and loss spreadsheet step by step

Build a transaction tab, a category list and a P&L tab that totals the transactions by category and month. It takes under an hour and the formulas below do the rest.

1. On a tab called Tx, add columns: Date (A), Description (B), Category (C), Amount (D).
2. On a tab called Cats, list each category and whether it is Revenue, COGS or Opex.
3. On the P&L tab, put category names down column A and the first day of each month across row 1.
4. Fill the grid with SUMIFS, then add subtotal rows for revenue, COGS and opex.
5. Add gross profit, operating profit and margin rows as formulas.
6. Add the YTD column last.

The common mistake: margin vs markup (and other P&L errors)

The most common P&L error is reading a markup as a margin. A job priced at cost plus 40% does not earn a 40% margin; it earns 28.57%. Your P&L reports margin, so if you quote with markup, the statement will always look worse than the quote did.

The markup vs margin calculator guide shows that conversion step by step. Other errors that make a P&L lie:

• Owner draws as expenses. For many small structures, money you take out is not an operating expense; put it below the profit line and ask your accountant how your entity handles pay.
• Equipment purchases as a single month's expense. A vehicle or large tool bought in March makes March look like a loss. Keep capital purchases on their own tab and ask how they are depreciated.
• Loan repayments in expenses. Interest is an expense; the principal repayment is not. It shows up in cash flow instead.
• Averaging margins. Divide totals, not percentages.

Profit is not cash: why the P&L needs a cash view

A P&L can show profit while the bank balance falls, because invoices are unpaid, equipment was bought, or loan principal went out. None of those change profit, all of them change cash.

If customers pay 30 days after the invoice, March's $18,500.00 of revenue may land in April. That is why a P&L template should sit next to a running cash position. The cash flow forecast template guide walks through a six-month example where profit and cash differ by thousands, and the break-even calculator for small business guide turns the operating-expense row into the number of jobs you need each month.

Free template vs paid P&L template vs accounting software

A free spreadsheet you build suits a business with a few dozen transactions a month and an owner who wants to see every formula. Accounting software suits a business that needs bank feeds, invoicing and an accountant working in the same file. A paid template skips the build but keeps the transparency.

The small business P&L template is $14.99 one-time for Google Sheets and Excel. It tracks revenue, COGS, gross profit and margin, operating expenses and net profit, and adds break-even revenue and a running cash position. It is also part of the $49 Complete Toolkit of seven templates. See the profit and loss spreadsheet page for the short demo.

Step-by-step: Profit and Loss Template for Google Sheets: Layout, Formulas and a Worked Example

1. List your categories. Write every income and cost category and tag each one Revenue, COGS (direct cost) or Opex (overhead).
2. Log transactions. On a transaction tab, record date, description, category and amount for every sale and expense.
3. Total by month. On the P&L tab, use SUMIFS to total each category for each month column.
4. Add subtotal rows. Revenue minus COGS gives gross profit; gross profit minus opex gives operating profit; subtract tax for net profit.
5. Add margins. Divide gross, operating and net profit by revenue, wrapped in IFERROR so empty months show 0%.
6. Add YTD. Sum the month columns for YTD totals, then calculate YTD margins from those totals.

Related guides