Refunds, Returns, Write-Offs: Build a “Reality Ledger” in Excel
Gross Revenue is vanity. We show how to pull refunds and write-offs out of the shadows and build a reconciled table that shows your Net Reality.
The Phantom Profit
Open your “Monthly Sales” report. Look at the total: $150,000. Now, open your Stripe or PayPal dashboard. Look at the balance transfer: $132,000.
Where is the missing $18,000?
It is ghost money. It is money that you “earned” on Tuesday and “returned” on Thursday. But because your sales report only counts the “Additions” and ignores the “Subtractions,” you are running the business on a phantom number.
The Distortion: We treat Revenue as a permanent victory. But in a subscription business or e-commerce, a sale is not a sale until the refund period is over. By keeping “Sales” and “Refunds” in separate silos, we allow the Gross Revenue to lie to us.
The Signal: We must force these two datasets to collide.
The Ledger of Truth
We are going to build a simple table. It is not pretty, but it is honest.
You need three columns:
- Gross Sales (+)
- Refunds & Chargebacks (-)
- Net Reality (=)
[TO EDITOR: Illustration of a “Waterfall Chart” or “Bridge Chart”. Left bar is tall and green (Gross Sales). Next bar is red and hangs downwards (Refunds). Next bar is red and hangs downwards (Write-offs). Final bar is blue (Net Reality). The visual drop should be significant.]
When you map this by Product Category, the story changes. You might see that “Product A” has high sales, but a 25% return rate. It is not a bestseller; it is a defective product that is churning your support team. “Product B” has lower sales, but a 0% return rate. It is the true profit engine.
The Write-Off Graveyard
And then, there is the darkest corner of the spreadsheet: The Unpaid Invoice.
I often see “Accounts Receivable” treated as “Cash” in the founder’s mind. “Oh, they owe us $10,000, so we are fine.” Come, look at the date on that invoice. It is 120 days old. That is not an asset. That is a memory.
We must move that $10,000 from the “Revenue” column to the “Write-Off” column. It hurts. It ruins the chart. But it clears the fog.
Do not let the “Gross Revenue” number boost your ego. It is a vanity metric. Only the money that stays in the bank gets to speak.
FAQs
Why are refunds usually hidden?
Because they are painful. And often, the sales team doesn't have access to the returns data, so they just report the 'win'.
What is a 'Reality Ledger'?
A single table that joins 'Gross Sales' with 'Returns' and 'Disputes' by customer ID.
Is a high refund rate always bad?
Not always. But a *hidden* refund rate is fatal. You cannot fix what you do not subtract.