Tracking Client Travel Details, Commissions Owed, and Payments Received

Closed Posted 6 years ago Paid on delivery
Closed Paid on delivery

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.

Accounting Business Analysis Data Entry Database Development Excel

Project ID: #14695236

About the project

22 proposals Remote project Active 6 years ago

22 freelancers are bidding on average $471 for this job

schoudhary1553

Greetings sir, i am an expert freelancer for this job and your 100% satisfaction is assured if you allow me to serve. I can do this task as per your requirement Relevant Skills and Experience I have the 5 years of e More

$600 USD in 10 days
(339 Reviews)
8.0
ksriniravi

I can develop an Excel based tool (User Interface to input data, automated Invoice Generation, Dashboards for key metrics, Client Database) to completely manage your business. Relevant Skills and Experience 18 years e More

$300 USD in 5 days
(197 Reviews)
6.8
dpune

create excel tool based on the requirements mentioned Relevant Skills and Experience Excel/VBA Proposed Milestones $500 USD - Completed tool

$500 USD in 10 days
(173 Reviews)
6.4
andriphone0

Hello, Please refer to your PM, I Will provide a sample for you to prove Our ability before awarding, I will follow all instructions and If you have any question related to budget, deadline of work and any other iss More

$300 USD in 10 days
(83 Reviews)
6.0
daniyal3214

I posses expertise in all the using Excel tools and spreadsheets for Financial Analysis,Financial Modeling, Business Valuation, Break-even Analysis and Quantitative Analysis with years of experience Relevant Skills an More

$250 USD in 1 day
(185 Reviews)
6.4
fanninson45

I am an experienced Certified Professional with Database Programming and Administration skills across both MS SQL and Business Intelligence. Having the ability to take the technical lead in projects. Relevant Skill More

$250 USD in 10 days
(20 Reviews)
5.9
adslegal

A proposal has not yet been provided

$594 USD in 7 days
(5 Reviews)
4.0
Filipov74

This job offer sounds very good to me. Could you get in touch with me on Freelancer? I am working as a customer agent on German language Relevant Skills and Experience Data entry, excel, internet research, customer su More

$500 USD in 10 days
(6 Reviews)
3.8
rizkikho1108

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.

$550 USD in 7 days
(2 Reviews)
2.8
OmnisysSolutions

If you continue to do data-entry, the current structure with automation is ideal. If it's an employee then an excel form will work well. Data organization will be overhauled in both cases. Relevant Skills and Experien More

$400 USD in 5 days
(3 Reviews)
2.9
tracygearth

I have build such sites ,,.....

$550 USD in 30 days
(1 Review)
2.6
Shaimah26

I can help with organizing and sorting out clients data and generating reports when necessary. I am very experience with admin work and handling large databases. Relevant Skills and Experience Administration; Data Pro More

$333 USD in 10 days
(2 Reviews)
2.1
johnpaulob

I can write a portal for you to manage your clients data and automate commissions, profit, reports etc using html, php Relevant Skills and Experience html, css, ajax, jQuery, php, database Proposed Milestones $333 US More

$333 USD in 3 days
(1 Review)
1.8
expertspk

Hi, I am an expert developer and can build a web application for you, you will able to track your business on the go with ease. Please let's talk, so we can start. Thanks Relevant Skills and Experience PHP/MYSQL P More

$700 USD in 10 days
(1 Review)
1.0
bmmonline

Hi, Thanks for the details project description & the attachment. After reading your requirement I understand that you require an application which will have these functions(Which can share on chat) Relevant Skills More

$750 USD in 15 days
(0 Reviews)
4.5
DigiWhizz

My name is James and I am a developer from London, UK. Your project caught my eye and would love to discuss with you. Feel free to read reviews and ask for examples of my work. Thanks Relevant Skills and Experience I More

$555 USD in 10 days
(0 Reviews)
0.0
Fatheh37

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.

$250 USD in 3 days
(0 Reviews)
0.0
Idesanti01

I can take all the data you currently have and build a custom database for you that will have very user-friendly layouts that could streamline your data entry, management, reporting and invoicing. Relevant Skills and More

$750 USD in 15 days
(0 Reviews)
0.0
x2jobs123

Harvard University Data Science Certificate holder and expert in Data Analysis/Data Science and been working in USA for 14+ years. I can help you with your project using excel or other open source DB. Relevant Skills More

$444 USD in 10 days
(0 Reviews)
0.0
royarijit004

My name is Arijit. I am an entrepreneur and a freelancer, looking for projects Relevant Skills and Experience expert in Data Entry, Web Scrapping, Virtual Assistant. MS Excel & Word, Web Researching,Shopify & Amazon More

$333 USD in 15 days
(0 Reviews)
0.0