View topic - Spreadsheet for reconcilliation

It is currently November 21st, 2019, 4:19 am

All times are UTC - 5 hours





Post new topic Reply to topic  [ 6 posts ] 
Author Message
PostPosted: October 9th, 2017, 4:22 pm 
Offline

Joined: April 10th, 2012, 4:41 pm
Posts: 166
Location: Stouffville, ON
I'm looking for a spreadsheet template that will do the accounting for a group trip where more than one member contributes. Sounds like it should be a simple formula? Not so! Here is an example (from our private forum) of what I ran into this year:

You would think that something like reconciling a trip between 5 people would be simple. I certainly did. I was wrong. So I went to my more analytical better half, my wife. This is what she worked out:

Bill paid out $310.75 for park fees
Josh paid out $91.60 for gas for trip home
Steve paid out $918.05 for foods and canoe rentals

Steve pays Bill $310.75 - the amount Bill paid for park fees

Steve pays Josh $71.60 - the amount Josh paid for gas for the trip home
($91.60 - $20.00 paid in cash to Josh by Bill)

Bill pays Steve $248.66
($183.61 + $62.15 + $2.90) (Bill gave Josh $20 in cash leaving a $2.90 balance)

Josh pays Steve $268.66
($183.61 + $62.15 +$22.90)

Terry pays to Steve $268.66
($183.61 + $62.15 + +$22.90)

Reg pays to Steve $245.76
$183.61 + $62.15)

When all that dust settles, here are the amounts paid out:

I pay Bill $62.09 ($310.75 - $248.66)

Josh pays Steve $197.06 ($268.66 - $71.60)

Terry pays Steve $268.66

Reg pays Steve $245.76


When you have money going in different directions from different sources, it gets confusing. A spreadsheet should simplify things. Does anyone have, or know of one?


Top
 Profile  
 
PostPosted: October 9th, 2017, 4:55 pm 
Offline
CCR Assistant Administrator
User avatar

Joined: November 6th, 2009, 9:37 am
Posts: 557
Location: Kingston, ON
Those days are over www.kittysplit.com


Top
 Profile  
 
PostPosted: October 9th, 2017, 5:04 pm 
Offline

Joined: April 10th, 2012, 4:41 pm
Posts: 166
Location: Stouffville, ON
Martin, you are my new hero

Does the download come with any unwanted friends, if you know what I mean?


Top
 Profile  
 
PostPosted: October 9th, 2017, 5:07 pm 
Offline

Joined: October 31st, 2016, 9:32 pm
Posts: 100
Location: Missoula, Montana
Back in the days before Excel and other spreadsheet programs, I frequently took on the chore of dividing and allocating expenses for group trips. I did it manually, often on a paper bag or a piece of cardboard from a box.

I had one set of columns for the expenses paid by each person. Each participant had a vertical column, and each expense was on a row. This part of the spreadsheet provided a total amount of expenses paid by each participant at the bottom of each column.

Then I had a second set of columns, usually to the right of the first set, which allocated each expense between the participants. For example, in a five person trip, one expense might be divided between only two participants, and the next expense might be divided between all five participants. This part of the spreadsheet provided a total amount of expenses allocated to each participant at the bottom of each column.

Then I would calculate the difference between the total amount paid by each participant, and the total amount allocated to each participant. For example, one participant may have paid $360 more than the amount of expenses allocated to them, and another participant may have paid $75 less than the expenses allocated to them. To determine if I had made any calculation errors, I would add up the total expenses paid and the total expenses allocated. If I was lucky, those numbers were the same.

In some cases, the easiest way to handle the cash exchanges was for me to act as banker. Everybody who owed money would write me a check or give me cash, and then I would write checks and/or give cash to the people who were owed money. In other cases, it worked to have several people who owed money directly pay somebody who had paid a lot of expenses, such as a shuttle or bush pilot fee, and then make whatever minor adjustments were necessary to make the payments come out right.

This is a low tech way of handling trip finances. You can do with a dull pencil when crammed in the back seat of a vehicle while driving home. A pocket calculator or calculator app comes in handy. But if somebody comes up with a fancy spreadsheet which does all the calculations automatically, you could do the same thing a bit faster and with fewer calculation errors on a laptop or a tablet during the drive back.


Top
 Profile  
 
PostPosted: October 9th, 2017, 5:23 pm 
Offline
CCR Assistant Administrator
User avatar

Joined: November 6th, 2009, 9:37 am
Posts: 557
Location: Kingston, ON
@Realstone There's no download! It's a thing of beauty.


Top
 Profile  
 
PostPosted: October 10th, 2017, 8:51 pm 
Offline

Joined: April 10th, 2012, 4:41 pm
Posts: 166
Location: Stouffville, ON
Ok, I tried the kittysplit. Where have you been all my life? Couldn't be simpler. It actually reduced our payouts by one transaction. pmmpete, I acted as the banker in the past. It was ok so long as I was the main purchaser, but when a contributor or two is added in it gets complicated quickly. Give it a try.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 6 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 8 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group