Traveling in a Group – Tracking expenses

This is probably the last article of the series. Comment below if you think I missed something! 

Now, Let’s get to it:  Tracking Expenses

When going on vacation with a group, expenses can get a little tricky. Everyone wants it to be fairly split down the middle — unless you came to an agreement that someone will be paying for the vacation OR its more of a 60/40 split. 

I’m going to go out on a limb and say that that isn’t the case for everyone — and chances are at least one of your vacations are going to be with a couple of buddies that want to evenly calculate expenses. 

You came to the right spot! Here we at Simply Prepared, you are already aware of how much we love spreadsheets! 

DISCLAIMER: 

1. You can use this spreadsheet for anything… roommate situation, a night out with friends… whatever. But for the sake of sticking with the travel theme, we will use it as traveling with a group.
2. There are apps that do this for you. But how many apps do you already have that you only used one? This way no one needs to perform any downloads and installations — or have a hard time logging in. The beauty of creating your own spreadsheet is the fact that you have FULL CONTROL over how things are divvied up. 

With a few quick spreadsheet tricks, I’ll show you how you can create a phenomenal tracking sheet that will take the hassle out of figuring out who owes whom, and how much.

Let’s jump the gun a little bit here — this is what your final product will look like:

An elegant sheet that has the item information, price, who purchased it, and how much money is owed to which person. Let’s get into the nitty gritty so you can build it on your end…

Gather your information

The first half of the photo has the Date, Store, Item, Purchaser, Cost. It’s critical that you gather as much information as you can about the purchase. Yes, it’s true that we only need who purchased the item, and for how much — but there is a bigger issue here. Sometimes you might forget that you paid for an item! Sometimes you double input the same item. Mistakes HAPPEN. By outlining the details of your purchase, it will help remembering what was purchased on the trip. 

This system will allow you to track who purchased what, and for how much. There. Simple & Easy!

Splitting the cash

We need to know three things

• How many people are paying on this trip?
• What is the total each person has paid during the trip?
• Who owes whom money?

If you haven’t taken a course in spreadsheets… don’t worry! Here is the code behind the sheet: 

1. Set up a chart similar to the one below. You will have a Monthly Total, individual totals, and the spit costs. 

To get the trip total take the sum of the purchases. If you are not sure how many purchases there will be, then take the entire sum =SUM (Column:Column) This will take the sum of ALL the purchases made on the trip. 

Next — take the individual sum of each purchaser. Every person is going to buy something different. Add up ALL of their purchases. This part is critical for the next step.

Because adding them manually can cause some hiccups. Try to use a = SUMIF formula. It will only take the sum of the purchases made by a certain individual. In our example we have the following

Take the sum of all the purchases made by Person 1 

=SUMIF(PurchaserColumn:PurchaserColumn, “Person 1”, CostColumn:CostColumn)

Now… the most exciting part. Are you ready? Distributing the costs. This will help tell you who owes money to whom, and how much? I know — it’s like magic! 

In the next row (under Pay) make a section for each person.

The formula can be interpreted as:

(Person 1 total amount paid – Total amount paid on trip) / total amount on trip. 

This will produce a positive or negative number. If your number is positive it means people are going to pay you. If your number is negative, that means you owe money. 

What is really lovely about this, the negative number is the amount you pay to the people who are positive. In this example… Person 2 owes Person 1 $219.52 & Person 3 owes Person 3 $529.79. 

Got it? 

And that’s it! Go forth with your magical spreadsheet and figure out how to travel in a group with less stress, and more time to do the things you want to do!