How to Reconcile Booking.com Commission Invoices with Hotel Guest Registers in Excel

Introduction

For boutique hotel managers, bed and breakfast owners, and vacation rental hosts, Booking.com is a powerful channel for driving reservations. However, at the end of every month, online travel agencies (OTAs) send a commission invoice that must be reconciled against your internal guest register.

Hotel accountants and operators regularly search Google for *"how to reconcile Booking.com commission invoice with hotel guest list in Excel."* The difficulty arises because booking platforms log reservations, cancellations, and virtual credit card payments under transaction structures that rarely match your Property Management System (PMS).

If your OTA reconciliation process is disorganized, you risk paying commissions on cancelled bookings or no-shows, failing to detect underpayments, and experiencing cash flow mismatches. This guide explains how to build a clean reconciliation sheet in Excel to audit your OTA bills.

---

Why Booking.com Commissions are Hard to Reconcile

Mismatched Reservation IDs and Folios

To reconcile an OTA invoice, you must match booking records.

• The Issue: Booking.com identifies a guest by a unique 10-digit Reservation ID. Your internal guest register or PMS likely logs guests under a different reservation number or room folio ID.

• The Result: Trying to match bookings by customer names alone fails due to spelling mistakes or duplicate common names.

Unreconciled Cancellations and No-Shows

OTAs charge commission based on expected stays, but guest plans change.

• The Problem: If a guest cancels a booking late or fails to show up, the booking status may change to "no-show" in your PMS. However, Booking.com will still bill you for commission unless the cancellation was correctly updated in their extranet.

• The Consequence: Hotels routinely overpay thousands in commission fees for guests who never actually slept in their rooms.

Virtual Credit Card (VCC) Settlements

When guests pay Booking.com directly, the platform issues a Virtual Credit Card for the hotel to charge.

• The Pain Point: The VCC charge dates, transaction fees, and net payouts are logged separately from the room nights, making it difficult to trace payments back to individual guest checkout folios.

---

Step-by-Step: How to Structure an OTA Reconciliation Sheet in Excel

To audit your Booking.com invoices, set up a dedicated OTA Reconciliation Workbook:

1. Structure Your Audit Columns

Create an Excel sheet with columns that align invoice items with guest logs:

• `Booking.com Reservation ID` (Format as Text to prevent digit rounding)

• `Guest Name`

• `Check-In Date` (Standardized to YYYY-MM-DD)

• `Gross Room Revenue` (OTA reported)

• `PMS Room Revenue` (Actual billed)

• `Commission %` (e.g., 15%)

• `Commission Due` = `=[PMS Room Revenue] * [Commission %]`

• `Invoice Discrepancy` = `=[Commission Due] - [OTA Invoice Charge]`

2. Use Lookup Formulas to Match Records

Use Excel’s `XLOOKUP` or `VLOOKUP` to match Booking.com IDs against your PMS export:

`=XLOOKUP(A2, PMS_Export[Reservation_ID], PMS_Export[Actual_Revenue], "Not Found")`

If the formula returns "Not Found", it indicates a booking recorded by Booking.com that is missing from your PMS, or vice versa.

The Solution: Use a Pre-Built Template

Stop building lookup sheets and pivot charts from scratch. Download our pre-configured Tourism & Hospitality Excel Templates. These templates include pre-built commission auditing calculators designed to import OTA exports and PMS registers.

---

Frequently Asked Questions

Why Does Excel Corrupt Reservation IDs?

Booking.com reservation numbers are 10 digits long. If they are imported into Excel alongside other numerical fields, Excel can treat them as raw numbers and apply rounding, or strip leading zeros.

How to fix it:
Always format the reservation ID column as Text during CSV import. This preserves the exact digit string for VLOOKUP matching.

How Do I Exclude Cancellations from Commission Audits?

Ensure your PMS export contains a clean `Booking Status` column. Filter the data to include only "Checked Out" reservations before matching against the Booking.com invoice. If an item on the Booking.com invoice is marked as cancelled or no-show in your PMS, flag it for a commission dispute.

---

The Solution: CleanData Tourism Suite

Clean Your Guest Lists and OTA Exports in 10 Seconds

Booking exports and hotel guest registers are filled with formatting discrepancies, date timezone differences, and mismatched names.

Upload your raw files into the Free Excel Cleaner. In under 10 seconds, the cleaner will:
1. Standardize date formats across all columns to YYYY-MM-DD.
2. Normalize guest names and uniform transaction labels.
3. Clean room rates and commission values into clean numbers.

Get AI-Powered Commission Audits

Once your spreadsheet is clean, upload it to the CleanData AI chat. Ask questions like:

• *"Are there any Booking.com reservation IDs on the invoice that are marked as cancelled in our guest register?"*

• *"Calculate the total commission discrepancy value between Booking.com invoice and our calculated room revenue."*

• *"What was our net room revenue after subtracting OTA commissions and credit card fees?"*

The AI delivers instant, grounded answers. No pivot tables or formulas required.

> 🚀 Optimize Your Hotel Auditing: Download pre-formatted templates and clean your tourism files today at the CleanData Templates Directory.