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.
25 freelancers are bidding on average $468 for this job
i am currently working as fulltime freelancer so i have plenty of free time to fulfil your task on time. i am familiar with excel and have working experience more than 6 years.
Hi Sir , I would love to work with you and I assure you that I will complete the work with perfection and in time Stay tuned, I'm is still working on this proposal.