Travel Agency Management System in Excel: Tour Packages, Bookings, Payments, Balance Due and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: travel-agency-management-system-template.xlsx: a 7-sheet system with example data and real formulas (Read me, Setup, Bookings, Payments, Report, Invoice, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Ravina Holidays, Nashik, as of 17 October 2026 (made-up data). Children pay 50% of the adult price; the balance is due 15 days before travel; GST 5% (the rate generally used for tour operator packages without input tax credit; confirm with your accountant).

The margin across all packages is ₹1,73,000 (23.5%). For friends splitting a trip, see the trip budget split.

2. Build it sheet by sheet

Step 1. Setup: packages and rules

1. B4 GST on tour package, B5 days before travel to collect the balance (15), B6 child price as a share of adult (50%).
2. Packages in A9:D13: Package, Nights, Cost per adult, Price per adult; E9 margin =D9-C9.

Step 2. Bookings: price and payments

1. A5 ="TR-"&TEXT(ROW()-4,"000"). Type customer, package, adults, children and travel date.
2. H5 price: =ROUND((E5+F5*Setup!$B$6)*XLOOKUP(D5,Setup!$A$9:$A$13,Setup!$D$9:$D$13,0),0). Two adults and two children pay for 3 adults.
3. I5 cost the same way from column C, J5 GST =ROUND(H5*Setup!$B$4,0), K5 total.
4. L5 paid: =SUMIFS(Payments!$C$5:$C$2000,Payments!$B$5:$B$2000,A5) (SUMIFS) and M5 balance =K5-L5.

Step 3. Bookings: days to travel and status

1. N5 days to travel =G5-Setup!$B$3 (dates).
2. O5: =IF(G5<Setup!$B$3,"Completed",IF(M5<=0,"Fully paid",IF(N5<=Setup!$B$5,"Collect balance","Advance paid"))) (nested IF).
3. Colour Collect balance red and Completed grey with conditional formatting.

Step 4. Report and Invoice

1. Report B5 bookings with COUNTIF, C5 travellers (adults + children), D5 sales, E5 cost, F5 margin and G5 margin %.
2. Rows 13 to 16 count bookings and balance by status.
3. Invoice: type a booking ID in B4; rows 6 to 15 look up the details and B17 the balance due date =B10-Setup!B5. Set the print area.

Step 5. Dashboard

1. KPI cards: bookings, travellers, sales before GST, margin, balance to collect and collect-now count.
2. Charts: margin by package and travellers by package.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Pricing children as adults. Use the child share from Setup. Families compare quotes closely.
2. Typing paid amounts on Bookings. Payments come in parts. One Payments row per receipt lets SUMIFS add them.
3. Margin after GST. GST is not the agency's income. Compare price and cost before GST.
4. No balance rule. Without the 15-day rule nobody calls until the day before. The status makes the call list.
5. Package names typed differently. "Goa 4N" and "Goa 4 nights" are two packages to COUNTIF. Use the drop-down.

5. More real examples

Example 1: Pilgrimage tours. Add a Seat no column for coach tours and count seats sold per departure date.

Example 2: Air ticket desk. Track service fee per ticket as the margin, with fare as cost passed through.

Example 3: Corporate offsites. Add a Company column and a company-wise outstanding report like the khata book.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I price a tour for adults and children in Excel?

(adults + children × child share) × price per adult, rounded: as in Bookings H5.

How do I flag bookings whose balance is due?

Days to travel = travel date − today; show Collect balance when the balance is above 0 and days to travel ≤ 15.

What GST applies to tour packages?

Tour operator services are generally taxed at 5% without input tax credit, or 18% with it. Confirm which applies to you with your accountant.

How do I add part payments?

Record each payment with the booking ID and let SUMIFS add them in Bookings L5.

How do I find the most profitable package?

Report column F: margin by package with SUMIFS on price and cost.

7. Quick quiz

Ten questions on this page. Sign in to save your score.