One of the things I often asked when I was at school was \”When will we ever use this?\”
It was a regular problem for our teachers that often the abstract science and maths we learnt would have little use outside the classroom. However some of it is useful and here I present a case study.
I live in a house with three friends and we have bills to pay each month. To make sure that the bills get paid and that we get the early payment discounts each one of us is in charge of a different bill.
This works well and the bills all get paid on time however it raises a problem:
Some people’s bills are larger than others so to make sure that everybody pays their fair share we had to settle up with each other, every month. This meant that each of us had to pay the three other people a total of 12 separate payments. A lot of the money was also going back and forth between us. For example: Person A pays £10 to person B, who pays £5 to person A and £5 person C, while person C pays £10 to person A. Now in that example it’s clear that we actually didn\’t need to pay each other anything because the net exchange of money was £0.
Sorting all these separate payments out was a pain in the backside so I decided to use some simple logic, maths and computer skills to solve the problem.
I created this spreadsheet:
Which can solve the bills for any amount in three transactions! That’s 75% reduction in the workload.
But how does it work?
The first stage is to understand the problem:
For any combination of bills some people will have paid more than their fair share and will be owed money by the group. While others will have paid less than their fair share and will owe money to the group.
If we are dividing the total cost evenly between the four of us our balance is easy to calculate
[Total cost of all bills]/4 – [how much you paid for your bill] = [Your balance with the group]
If the answer is positive then you owe the group money
If the answer is negative then the group owes you money
The Next stage is more complex
Having identified who owes money and who is owed money it is necessary to break up the payments. The way to minimise both the number of payments and the value of those payments is whenever possible pay you dept to a single person.
The method I used was to create a matrix for each payment possibility e.g. person A has to pay person B in each point on the matrix I put a logical statement.
My solution is not the most elegant (as I explain below) but goes like this:
If [the money you owe to the group] = 0
Then [the money you owe to this person] = 0
Else
If [the money you owe to the group] – [any money already paid] < [money this person is owed] – [any money they have already been paid]
Then [the money you owe this person] = [the money you owe to the group] – [any money already paid]
Else
[The money you owe this person] = [money this person is owed] – [any money they have already been paid]
The reason that is not the ideal solution is that it will miss opportunities for a two payment solution e.g. when the payments are (A=£11, B= £8, C=£9, D=£12) this is because it does not solve the problem simultaneously but progressively always solving A’s debts first. However, it should always solve the problem in three stages thus meeting its design requirement.
If anybody has ideas on how to fix this flaw or how to allow the system to work with more or less than 4 people please post below.
So there you go, perhaps a little nerdy but it took an hour to create the spreadsheet and saved that much time for us each month so I think it was worth it.