Most "P&L Excel format" templates you'll find are built for a generic small business. They give you a Revenue row, an Expenses row, and a Net Income row — and they quietly let you drop every cost into one bucket. For a print-on-demand store running paid traffic, that layout hides the one number that decides whether you live or die: your customer acquisition cost relative to your margin.
This is the format for a store that already has sales history and ad spend — not a business plan. If you want the deeper reasoning behind each line, the ecommerce P&L guide covers the full construction; this piece is about getting the Excel sheet right.
The exact row layout (top to bottom)
Build your P&L vertically, one row per line item, with month columns running left to right (Jan, Feb, Mar…). Here is the skeleton, in order:
- Gross sales — total order value booked when the sale happens
- Less: discounts — coupon and automatic-discount codes (enter as a negative)
- Less: returns & refunds — refunded order value (negative)
- = Net sales —
Gross sales − discounts − refunds - COGS: product (supplier charge) — the blank plus printing from Printify, Printful, or Gelato
- COGS: supplier shipping — the production shipping to your customer
- COGS: payment processing — card fees on the sales you kept
- = Gross profit —
Net sales − COGS - Gross margin % —
Gross profit ÷ Net sales - OpEx: ad spend — Meta and Google combined (its own line, always)
- OpEx: platform & apps — Shopify plan, apps, email tool
- OpEx: software & design tools
- OpEx: contractors / owner pay
- = Operating profit —
Gross profit − total OpEx - Operating margin % —
Operating profit ÷ Net sales
The two rules that separate a useful sheet from a misleading one: book gross sales at the top (not the net deposit Shopify sends you), and put ad spend in operating expenses, never in COGS. More on both below, because they are the two mistakes that quietly wreck POD books.
The Excel formulas that make it live
The point of doing this in Excel instead of on paper is that the sheet recalculates itself. Enter raw numbers only in the gross sales, discount, refund, cost, and spend rows. Everything with an = in front of it is a formula.
If your month's figures live in column B, the formulas look like this:
- Net sales:
=B3-B4-B5(gross minus discounts minus refunds) - Gross profit:
=B6-SUM(B7:B9)(net sales minus the three COGS rows) - Gross margin %:
=B10/B6(format the cell as a percentage) - Operating profit:
=B10-SUM(B12:B15) - Operating margin %:
=B16/B6
Enter discounts and refunds as positive numbers and subtract them, or enter them as negatives and use SUM — just pick one and stay consistent, or your signs will fight you. Once column B is right, drag the formulas across for every month, and add a =SUM() column at the far right for a rolling year-to-date total.
Worked example: a store doing real volume
Say your store runs 340 orders a month at a $31 average order value, with $2,800/month in Meta spend and a little Google on top. Here's what one month looks like when you fill in the rows. All figures are illustrative.
| Line | Amount |
|---|---|
| Gross sales (340 × $31) | $10,540 |
| Less: discounts (a discount code on part of orders) | −$620 |
| Less: refunds (11 orders) | −$340 |
| Net sales | $9,580 |
| COGS: product + supplier shipping (340 × ~$13.50) | −$4,590 |
| COGS: payment processing | −$405 |
| Gross profit | $4,585 |
| Gross margin % | 47.9% |
| OpEx: ad spend (Meta + Google) | −$3,100 |
| OpEx: Shopify plan + apps | −$180 |
| OpEx: email + design tools | −$95 |
| OpEx: owner pay / contractor | −$500 |
| Operating profit | $710 |
| Operating margin % | 7.4% |
Read it the way the format forces you to: the product itself is healthy at nearly 48% gross margin, but ad spend eats roughly two-thirds of your gross profit, leaving about $710 on $9,580 of net sales. If your cost per acquisition drifts up 20% — another $620 of spend — operating profit nearly vanishes. That's the whole reason ad spend sits on its own line below gross profit: the sheet should scream "your risk is CAC," and it can't if that cost is buried in COGS.
Don't book your Shopify payout as revenue
The single most common way a POD P&L goes wrong in Excel: typing the bank deposit from Shopify into the gross sales row. The payout is a net settlement — it already has processing fees, refunds, and adjustments taken out, and it lands on a rolling delay, so it almost never matches your actual sales for the month.
Book gross sales at the top from your Shopify order reports, then record fees and refunds on their own rows. The net payout is a cash figure, not a revenue figure. For processing, Shopify Payments commonly runs around 2.9% plus 30¢ per online transaction on lower-tier plans, and a disputed charge carries a $15 chargeback fee in the US that you get back only if you win, per A2X's breakdown of Shopify fees. Verify your own rate against your Shopify plan before you hard-code it into the sheet.
One gotcha the Excel format should capture: when you refund an order, you generally do not get the original processing fee back. So a refunded $31 order still costs you roughly $1.20 in fees even though you kept nothing. Leave the already-booked fee where it is and record the refund as contra-revenue.
Why a profit row isn't a cash row
Your Excel P&L can show $710 of profit and your bank account can still be tight the same week. Profit is booked on the sale date; cash moves on Shopify's payout schedule, while ad spend and supplier charges leave immediately. The faster you scale ads, the wider that gap gets.
That's why the P&L format and a cash view are two different tools. Once your P&L is clean, pair it with a simple cash-timing view — the cash flow tracking approach is the natural next step for a store pre-funding growth on ad spend. If you want to see the fuller statement with year-end structure, the yearly profit and loss statement format and a worked accounting P&L example both extend this layout.
Rows people forget to add
A few lines that belong in the format even though templates skip them:
- An owner-pay row. If you pay yourself nothing, your operating profit is fiction. Put a real number there.
- A tax set-aside note. Your store's profit usually has no tax withheld, so some of that operating profit is already spoken for. This is general information, not tax advice. Rules change and vary by situation — consult a licensed CPA or tax professional before acting.
- A gross margin % and operating margin % row. These two ratios, tracked month over month, tell you more than the dollar figures alone.
Where the Excel format stops being enough
A monthly P&L in Excel is a genuinely good discipline, but it only tells you the store-level story. It can't tell you which products or which ad campaigns are actually profitable once you spread supplier costs, shipping, and fees across individual orders — that's a per-order calculation a spreadsheet re-keyed by hand each month tends to get wrong.
That's the gap PodVector AI closes. Victor is an AI employee that connects to your Shopify store, Meta Ads, Google Ads, and your Printify, Printful, or Gelato account, computes true per-order profit from your live data, and delivers the reports straight to your Google Drive — no re-keying payouts into a sheet. Every write action Victor takes is approval-gated, so you stay in control. Put Victor to work on your numbers and keep your Excel P&L as the sanity check, not the whole job.
FAQs
What is the best format for a profit and loss statement in Excel?
A vertical, single-column-per-month layout that runs gross sales → net sales → gross profit → operating profit, with ad spend on its own line in operating expenses. The vertical format lets you drag formulas across months and spot trends in margin, which a flat revenue-minus-expenses sheet can't show.
Should ad spend go in COGS or operating expenses on my P&L?
Operating expenses, every time. Ad spend is paid acquisition, not a direct per-unit product cost. Putting it in COGS inflates your gross margin and hides the fact that customer acquisition cost is your real risk — exactly the thing an operating store needs its P&L to surface.
Do I enter my Shopify payout or my sales in the revenue row?
Your gross sales, pulled from Shopify's order reports — not the payout. The payout is a net settlement (sales minus fees minus refunds, on a delay) and belongs at the bottom as a cash figure. Booking the deposit as revenue understates your top line and hides your fees entirely.
What counts as COGS for a print-on-demand store?
The direct cost of the units you actually sold: the supplier's production charge (blank plus printing) and the supplier shipping to your customer. Many sellers also put payment processing here. Everything else — ads, apps, subscriptions, your own pay — sits below in operating expenses.
How often should I update my P&L in Excel?
Monthly, as soon as the prior month closes. A month is short enough to catch a margin problem before it compounds and long enough to smooth out the day-to-day noise of refunds and ad swings. Keep every month in its own column so you're always looking at the trend, not a single snapshot.
Can a P&L tell me if I have enough cash?
No — and this trips up profitable, growing stores constantly. A P&L shows profit booked on the sale date; it says nothing about when cash actually arrives versus when ad and supplier bills leave. You need a separate cash-timing view alongside it, especially if you're scaling ad spend against a payout delay.