Tracking Client Travel Details, Commissions Owed, and Payments Received

I am an independent travel agent focused on luxury leisure travel for my clients. I currently maintain a detailed Microsoft Excel file where I keep track of the details my clients trips by manually entering the data. The details include the client(s) names, date booked, confirmation #s, check in/out dates, nightly rates, total amount, # of days, destination (hotel(s) names, cruise ship, flights, tour packages, etc.), the hotel, vendor, or wholesale provider that I used to book the trip, among other items. Additionally, I track the commission rate earned on each trip, and the amount that is owed to me after partners withhold a portion of the commission that they are entitled to receive based upon my business arrangements (for example, if I book a trip for $1,000.00, I may earn a 10% commission ($100), while a vendor that I use might be entitled to keep 20% of that 10% commission. In this case, I would expect to receive $80.00). Upon receipt of payment from the hotel, vendor, or wholesale provider, I manually enter payment details including the date of the payment, check #, $ amount, etc. and verify that the payment amount matches the expected commission amount. On a periodic basis, I reference the spreadsheet to identify outstanding unpaid commissions, and use a manual copy/paste to generate simple invoices from the available data that I then send to the hotel/vendor/wholesale provider in order to request payment. I also perform analysis of the data to identify trends in my business, including top clients and locations/destinations, earned and expected/projected revenue. I also use conditional formatting to identify upcoming and ongoing trips. Ideally, I would like to streamline the data entry portion of the spreadsheet, perhaps by developing a "loader" or data entry capture page that would insert the details into the spreadsheet or "database". I would also like to automate/improve the invoice generation and data analytics by creating static templates/reports that could be populated from the data. Additionally, I am interested in developing a client information database that would allow me to track client details such as personal information (date of birth, passport #, frequent flyer program numbers, etc.), travel preferences, payment details, etc. The current excel file has approximately 1200 rows. I have attached a sample of the data for review. To highlight a few items: 1. Column L is the Nightly Rate, and Column M is the total rate. Sometimes I calculate the Total Rate, with # of Nights * Nightly Rate, and sometimes I calculate the Nightly Rate with Total Rate ÷ # of Nights. 2. Column N is the Gross Commission Rate. Sometimes this is a fixed 10%, 12%, 15%, etc. Occasionally the $ amount of the commission is fixed, and I calculate the % by dividing the Expected Commission (column O) by the Total Rate (column M) 3. Column P is the % of the commission that I keep. I typically keep 80%, 90%, or 100% of the gross commission. Column Q is the Gross Commission * the Commission Keep Rate, and this is the $ amount that I expect to be paid. 4. Columns R through V are where I track payment details upon receipt of payment from the payor. I am comfortable and familiar with Excel, though I am open to alternative suggestions. Please contact me with any questions and I look forward to working with you.