Crossing reservations with payments: how to know instantly who hasn’t paid

There’s a question no hotel should take more than three seconds to answer, and that almost always takes hours: “who hasn’t paid me yet?” The answer seems obvious and isn’t, because outstanding money isn’t hidden inside one hard-to-read table. It’s hidden somewhere subtler: in the gap between two tables that are almost never looked at together. One list tells you who’s arriving. Another list tells you who already paid. The balance, what they owe you, only appears when you cross the two. This essay is about that specific cross, reservation against payment, step by step, because it’s the first one any hotel should build.

Why outstanding money lives between two tables, never in one

Picture two spreadsheets. The first is your reservations list: guest name, check-in and check-out dates, room, rate, the channel they came from, total to collect. The second is your payments list: amount charged, method (card, cash, transfer), date of the charge, sometimes a reference. Each sheet, on its own, is honest but blind.

The reservations sheet knows how much you should collect, but not how much actually came in. The payments sheet knows how much came in, but not which reservation each dollar belongs to, or whether that reservation is settled. A one-thousand reservation with a three-hundred deposit is neither “paid” nor “unpaid”: it’s a balance of seven hundred, and that number lives in neither sheet. It exists only when you subtract one against the other.

The balance isn’t a number someone wrote down. It’s the difference between two numbers someone wrote down separately. That’s why it slips past you: you’re looking for a figure nobody ever saved.The rule of the gap between tables

This is what makes outstanding collection so slippery in practice. Your front desk sees the reservation. Your cash desk sees the payment. But the owner’s question, how much money is out on the street right now? lives squarely in between, and nobody answers it because nobody has both sheets open at once with the subtraction already done.

How to build the cross, without code

A cross, in plain words, is linking two lists by a value they share. Here, reservations and payments share a reservation identifier: each payment “points at” the reservation it’s covering. When you link both by that identifier, every reservation drags all its charges along with it. In Spider Data you do this by dragging and dropping the two tables and choosing the field that joins them; there’s nothing to type.

The three numbers that matter

Once linked, the cross produces, for each reservation, three figures that tell the whole story:

• Reservation total: what should be collected in full (the rate across the nights, plus extras if any).
• Collected to date: the sum of every payment pointing at that reservation, wherever it came from (online deposit, cash at the desk, transfer).
• Balance: the total minus what was collected. If it’s zero, it’s settled. If it’s positive, that’s what they owe you. That’s the number you were after.

The balance is a calculated field: it isn’t captured, it’s derived. And because it’s derived from live data, it fixes itself the instant someone records a charge. Nothing has to be updated by hand. The subtraction redoes itself.

Payment states: putting a name to each balance

A balance is a number, but people collect better when that number has a name. With the cross done, classifying each reservation is trivial, because the state falls out of comparing collected against total. These are the states that show up again and again in a real hotel (the figures below are an illustrative example, not real data):

Notice something important: “pending” and “pay on arrival” look identical in the numbers (balance equal to the total), but they’re different situations. One is a risk, someone who confirmed and hasn’t put down a dollar, and the other is a normal house policy. The cross gives you the balance; you give it the context. That’s why the state is best read next to the arrival date: a full balance three weeks out is calm; the same balance for someone arriving today is a conversation to have before they walk through the door.

What the cross reveals when you look at it whole

So far we’ve talked about one reservation at a time. The real force shows up when you sum (what we call a total, or rollup) all the balances together. Suddenly you see things neither sheet would tell you:

1. Accounts receivable: the sum of every positive balance. It’s, literally, how much money you’re owed right now.
2. Money on the street: that same total, but read as risk. How much of this week’s operation depends on charges that haven’t happened yet?
3. Balances by method: how much outstanding is “pay on arrival” cash versus card “deposit”. It tells you where to focus the collection effort.
4. Aging of the balance: by crossing with the date, you separate what’s due soon from what can wait. Not every outstanding amount is equal.

None of these four numbers lives in a table. All four are born in the gap between the two. That’s the whole idea of a cross: it doesn’t invent information, it frees what you already had, trapped between two lists that weren’t speaking to each other.

Why seeing it live changes collection, not just the report

Here’s the difference that decides everything. Most hotels discover their outstanding balances late: at the night audit, in Monday’s report, or worse, once the guest has already left and the balance turned uncollectible. By then it isn’t information, it’s a loss already taken.

Collecting on time and discovering too late are two different worlds. If the cross is alive, if it reflects the payment the second it’s recorded, not last night’s close, the balance appears while there’s still something you can do: ask for the deposit before confirming, remind the charge the eve of arrival, let no one leave with an open tab. The same data, read a day earlier, stops being a lament and becomes an action.

And because the builder lets you filter the dashboard across tables, you can go from “all balances” to “only the ones arriving today and owing more than half” in a couple of clicks, without building a new report. Whoever collects stops scanning a list of a hundred and tends to the five that matter.

The first cross every hotel should build

Spider Data measures and explains: it shows you who owes and why that balance came to be. It doesn’t set prices or collect for you; it tells you, with live data, exactly where the money sits that’s already yours but hasn’t come in. If you could only ever build one cross, make it this one. Not because it’s the most sophisticated, but because it’s the most profitable: it turns a question that kept you up at night, “who hasn’t paid me?”, into a column that answers on its own. Everything else you do with your data will be finer. But none of it will return money as fast as the cross that, at last, tells you who owes you.