Track your business performance with automated calculations and insightful analytics
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
A P&L is only as useful as its cost classification. This template keeps cost of sales and operating expenses genuinely separate, so gross margin means something and can be compared across periods.
The actual columns, with sample rows
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| 1 | Line | Q1 | Q2 | Δ | % of revenue |
| 2 | Revenue | 512,400 | 587,900 | +14.7% | 100.0% |
| 3 | Cost of sales | -198,300 | -238,600 | +20.3% | 40.6% |
| 4 | Gross profit | 314,100 | 349,300 | +11.2% | 59.4% |
| 5 | Operating expenses | -241,700 | -252,100 | +4.3% | 42.9% |
| 6 | Operating profit | 72,400 | 97,200 | +34.3% | 16.5% |
Why each one is written the way it is
=Revenue-CostOfSales
Gross profit. The discipline is in what goes into cost of sales: costs that scale with revenue, and nothing else. Putting a fixed salary here makes gross margin meaningless.
=IF(Revenue=0,"",[@Line]/Revenue)
Common-size percentage. Reading every line as a share of revenue is what makes two periods of different size comparable.
=IF([@Q1]=0,"",[@Q2]/[@Q1]-1)
Period-over-period change, guarded against a zero prior period so new lines do not error.
=SUMIFS(Ledger[Amount],Ledger[Account],[@Account],Ledger[Period],[@Period])
Each line pulled from the ledger by account code, so the P&L is never retyped and always agrees with the source.
A worked example with real numbers
An agency saw gross margin drift from 63% to 59% over four quarters without an obvious cause. Common-sizing every line showed contractor cost rising from 18% to 24% of revenue while permanent salaries — correctly classified as operating expense — stayed flat. The business had been absorbing demand with contractors at roughly twice the internal cost. Two hires closed the gap within two quarters.
Tab by tab
| Tab | Contents |
|---|---|
| P&L | The statement, by period, with margins at each level. |
| Ledger | Transaction detail by account code. |
| Mapping | Account code to P&L line, the only place classification is decided. |
| Common size | Every line as a percentage of revenue. |
| README | What belongs in cost of sales and what does not. |
What is included, and what to look at next
Specific to this workbook, not generic
Cost of sales scales with what you sell — materials, contractor delivery time, payment processing. Operating expenses do not — rent, salaries of people not delivering to customers, software. Misclassifying between them is the most common reason a gross margin trend is misleading.
Both. Monthly for management, quarterly for trend. Monthly figures in a service business are noisy enough that a single month rarely means anything on its own.
Split it in the mapping sheet with a stated ratio, and record the reasoning. An arbitrary split applied consistently is far better than putting the whole amount on one side and forgetting why.
"The automated calculations and clear layout make it easy to track our profitability. The trend analysis helps us make better business decisions."