How do you track travel agency commissions in Google Sheets or Excel?

Kindra AI℠ takes over the moment Google Sheets or Excel commission tracking outgrows its limits — but well before that moment, the right setup is six linked tabs (Bookings Log, Supplier Master, Agent Roster, Commission Payments, Agent Payouts, and Dashboard) with XLOOKUP formulas for rate and split lookups, SUMIFS roll-ups for monthly totals, and conditional formatting highlighting overdue commissions in red.

The setup works for a solo advisor or small agency for several years. Host Agency Reviews confirms that most new travel agents start with Excel — it's not pretty but it gets the job done. This page walks through the exact sequence to build the tracker, the formulas that automate the math, the conditional formatting that surfaces problems, and the specific breaking points that signal the spreadsheet has outgrown its usefulness.

Kindra AI is the graduation target. When the spreadsheet breaks — around 50+ monthly bookings, multiple concurrent editors, or 5,000+ rows with formula-heavy pages — Kindra AI replaces it with automatic supplier-statement matching, iOS and Android apps, agent self-service portals, and published pricing. Until that moment, build the sheet right. After it, migrate — the Kindra AI team handles the import in days.

Step 1 — Build the Supplier Master tab first

Reference table with every supplier: Supplier Name, Supplier Type (cruise / hotel / tour / air / insurance), Default Commission Rate, Override Rate, Payment Terms (Net 30 / 60 / 90), Payment Method (ACH / check / clearinghouse). IATA, CLIA, and ARC numbers and portal login URLs go in a Notes column for the bookkeeper. Data validation: Supplier Type uses a dropdown from a fixed list. Payment Method uses a dropdown. This prevents the typo-drift (“Royal Caribbean” vs. “RCCL” vs. “Royal Carribean”) that breaks XLOOKUP downstream. Populate this tab before any bookings are entered. Rate lookups depend on it.

Step 2 — Build the Agent Roster tab

Every IC or employee agent: Agent ID, Name, Independent Contractor flag, Commission Split % (what agent keeps), Tier (New / Mid / Senior), Start Date, Active/Inactive, W-9 On File flag. Add a column indicating the 1099-NEC $2,000 threshold for TY2026. The spreadsheet uses this to segment year-end reports. The Tier column enables SUMIFS-based tier-up rules if the agency uses production-based tier advancement; at scale, this becomes material overhead to maintain.

Step 3 — Build the Bookings Log with linked formulas

One row per booking. Twenty columns: Booking ID, Booking Date, Client Name, Agent Name (dropdown from Agent Roster), Supplier Name (dropdown from Supplier Master), Booking Type, Confirmation Number, Travel Dates, Gross Sale, Commissionable Amount, Commission Rate (formula), Expected Commission (formula), Agent Split (formula), Agent Payout Expected (formula), Payment Status, Date Received, Amount Received, Variance, Payout Date to Agent, Notes.

Key formulas to embed:

• Commission Rate: =XLOOKUP(SupplierName, SupplierMaster!A:A, SupplierMaster!C:C, "Rate Not Found")
• Expected Commission: =CommissionableAmount * CommissionRate
• Agent Payout: =ActualReceived * XLOOKUP(AgentName, AgentRoster!A:A, AgentRoster!D:D, 0)
• Days Overdue: =IF(Status="Received", "", TODAY()-ExpectedDate)

Data validation on Supplier Name and Agent Name is non-negotiable — it prevents the typo-drift that silently breaks every downstream lookup.

Step 4 — Add conditional formatting rules

Apply to the Bookings Log in order (most severe first, with Stop If True enabled):

• Red fill if overdue > 90 days: =AND(DaysOverdue>90, Status<>"Received")
• Orange if 61-90 days: =AND(DaysOverdue>60, Status<>"Received")
• Yellow if 31-60 days: =AND(DaysOverdue>30, Status<>"Received")
• Light blue if due within 7 days: =AND(ExpectedDate<=TODAY()+7, Status<>"Received")
• Green fill if Status = "Received": =Status="Received"

Additionally: flag any non-zero Variance cell in red. Variance should always be zero; any non-zero is an investigate-now signal.

Step 5 — Build the Dashboard tab with SUMIFS

Summary view pulling from Bookings Log: total expected commissions YTD (by month, supplier, agent); total received YTD; total outstanding (expected minus received); aging breakdown (Current, 1-30, 31-60, 61-90, 90+); top 5 suppliers by commission volume; each agent's YTD production and payout.

SUMIFS example: =SUMIFS(Bookings!ExpectedCommission, Bookings!Agent, $A2, Bookings!Date, ">="&DATE(2026,1,1), Bookings!Date, "<="&DATE(2026,12,31))

Archive annually. At year-end, copy completed bookings to an Archive workbook. Keep only active/pending rows in the live tracker. This prevents the performance degradation that hits formula-heavy sheets past 5,000 rows. For a broader look at how agencies handle tracking, see how travel agencies track commissions.

When to migrate to Kindra AI

Signals the spreadsheet has peaked:

• Monthly reconciliation takes a full business day.
• An agent was paid incorrectly due to formula error.
• Multiple people can't edit simultaneously without conflicts.
• 5,000+ rows, slow recalculation.
• You maintain a "Final" copy to avoid overwrites.
• Your bookkeeper fixes formulas instead of reconciling.

Kindra AI's 30-day trial lets the agency migrate before committing. The Kindra AI team imports the entire spreadsheet in days. Format-agnostic statement ingestion replaces the manual XLOOKUP chain — see how automatic commission reconciliation works. iOS and Android apps replace Google Sheets' mobile limitations. Per ASTA, 2-4% of commissions leak in manual processes; moving to automation recovers that margin. The full $10 to $20 per user per month breakdown is on the pricing page.

Workflow comparison — spreadsheet vs. Kindra AI

Pricing and getting started

$10 to $20 per user per month. A 50-agent agency pays $750 a month. 30-day free trial, no credit card. Migration included. Context from Travel Weekly: the move from spreadsheets to automated reconciliation delivers the highest single-year ROI of any back-office change an agency can make. Phocuswright research confirms the pattern across segments.