Profit and loss statement template (Excel, free): a monthly P&L with budget, variance and a built-in check

This template is a company P&L, built the way a finance team builds one: from the trial balance, not typed in by hand. You paste the ledger export, tell it once which P&L line each account belongs to, and every month fills itself. It is for business use; it is not a household budget.

The lines of a P&L, top to bottom

The captions follow US practice: the SEC's Regulation S-X, Rule 5-03 lists the income statement captions public companies use, from net sales down to net income. EBITDA and operating profit are management subtotals under US GAAP, so the template shows exactly which lines make them. (IFRS reporters get a defined operating profit subtotal under IFRS 18 from 2027.)

What is in the workbook

• TB. Paste your trial balance: Account, Name, Period as yyyy-mm, Amount (debits positive, credits negative) and Type (PL or BS). Column F looks up each account's P&L line in the Map sheet:

=IF(E2="PL",IFERROR(INDEX(Map!$C$2:$C$500,MATCH(A2,Map!$A$2:$A$500,0)),"UNMAPPED"),"")

• Map. Every account once, with its P&L line. This is the only place a judgment is made.
• P&L. Lines down column A, months across row 4, year to date in column O. Each line is one SUMIFS on the mapped line and the month:

=-SUMIFS(TB!$D$2:$D$2000,TB!$F$2:$F$2000,$A6,TB!$C$2:$C$2000,C$4)

The minus sign is for revenue, which a trial balance holds as a credit. Cost lines drop it. Subtotals are plain arithmetic on the rows above, and each margin row divides by net revenue:

=IFERROR(C10/C8,"")

• Budget. The same lines. Type or paste the budget into the yellow cells; subtotals and margins compute.
• Variance. Type a month in B2 and every line shows actual, budget, variance (actual − budget), variance % and a reading. On cost lines a negative variance is favorable, so the reading flips there.

The lookups use INDEX and MATCH, not XLOOKUP, so the workbook works in Excel 2016 and 2019 as well as Microsoft 365.

Filling it from your trial balance

1. Export the trial balance by month from your accounting system: one row per account per period.
2. Paste it over the sample rows in TB, keeping column F.
3. Filter column F for UNMAPPED. Add each of those accounts to Map with its P&L line.
4. Read the checks at the bottom of the P&L sheet. Both must be 0 before anyone reads the numbers.

A filled example: March, actual against budget

The sample company in the workbook, as the Variance sheet shows it for March:

Gross margin is 40.0% against a budgeted 40.6%, operating margin 11.3% against 13.6%, and net margin 7.5% against 9.3%.

The checks that prove it

The template carries three:

1. Net income equals the ledger. For each month, net income on the P&L must equal minus the sum of every PL row in the trial balance for that month. The "Difference" row shows the gap and must be 0.
2. Nothing unmapped. The "Unmapped accounts" row counts trial balance rows whose account is missing from Map. An account opened mid-year drops out of the P&L silently until it is mapped.
3. The variances add up. The line variances bridge the budget to the actual: −48,000 on net revenue, +21,000 on COGS, −8,000 on operating expenses and +8,750 on tax come to −26,250, the net income variance. A bridge that does not sum has a sign wrong somewhere.

That is the same tie-out habit as five checks before you trust a spreadsheet total, applied to a P&L: a control total from the source, compared with the report.

Where it goes wrong

• Sign convention. Trial balances hold income as credits, so revenue comes out negative unless the SUMIFS is negated.
• Year-to-date exports. A year-to-date trial balance pasted as if it were one month makes every line from February onward several times too large. Export by period.
• Depreciation inside operating expenses. If D&A sits inside G&A, EBITDA is understated and D&A is taken off twice.
• Periods that do not match. A 4-4-5 fiscal calendar and a calendar-month export do not line up; see fiscal calendars and period cuts.
• Coloring variances. One color rule on the variance column paints cost savings red. Read the reading column, not the sign.

When the template stops being enough

A workbook like this handles one entity, one currency and a few dozen accounts. It strains with several entities, intercompany eliminations or hundreds of cost centers, and it does nothing about the week of questions after the P&L goes out in the monthly pack. Covirage reads the trial balance and budget exports, keeps the account mapping once, and computes the P&L, margins and variances with deterministic tools that reconcile to the ledger; the external AI model explains what moved and never does the arithmetic. See FP&A reporting. For margins taken per customer rather than for the company, read gross margin vs contribution margin vs net margin. For a filled-in statement, see the profit and loss statement example and how to analyze a P&L; for budget against actual with commentary, use the budget vs actual template.