Refunds, Returns, Write-Offs: Build a “Reality Ledger” in Excel

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 (=)

Build the bridge from matched records, with each adjustment counted once:

GROSS SALES
    |
    | Subtract matched refunds and chargebacks
    v
SALES AFTER THOSE ADJUSTMENTS
    |
    | Show separately: write-offs of unpaid invoices
    v
MANAGEMENT VIEW AFTER IDENTIFIED ADJUSTMENTS

The opening example provides two totals, but not their reconciliation:

Reported total	Amount
Sales report	$150,000
Processor balance transfer	$132,000
Difference requiring explanation	$18,000

Do not label the whole $18,000 as refunds without matching the records. Nor does the separate $10,000 overdue-invoice example establish a component of that payout difference. Keep the sales-adjustment bridge and the bank-transfer reconciliation distinct, and record the source of each adjustment.

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.