Maintain shared account in an excel sheet

I created an excel sheet to maintain shared accounts in a room.

U may skip the background --------->

We are 4 ppl staying in same flat and the calculation of money spent every month was becoming very difficult.

We had to do many calculations.

1. Power bill, internet bill, news paper etc should be shared across all ppl.
2. Only 3 of us drink milk, so the milk amount should be shared by only 3 of us.
3. Two of us go out for break fast and two of us prepare at home. The amount spent will be different and should be shared properly.

Let us take A, B, C, D as roommates names.
We had to do totally 11 calculations

ABCD(shared by all), ABC, ACD, ABD, BCD, AB, AC, AD, BC, BD, CD

One day, after doing all calculations, my roommate told me that some numbers are wrong. As a result I had to do all the calculations again.
So, I thought of creating an excel sheet which eases my calculation work.

-----------> Background ends

Explanation of the sheet:

I assumed a person (Finance), who maintains our accounts.

Open the excel sheet.
First column is Date, second column is Item.
3rd, 4th, 5th (Amt Spt, To Fin, YES/NO) column belongs to person #1.

Enter the amount spent by person #1 in 3rd column.
Select YES in the 5th column for the same row.
Select YES for the people who are all involved in spending the amount.



In the above figure, Person#1 pays internet bill which is 750. It is shared by all ppl. So,

every ones share is 150. All ppl except person #1 has to pay 150 to finance(which is showed in red). Person #1 gets 600 from finance (highlighted in green).



Person #1, #2, #3 went for lunch. Now, person #2 pays 300.
It has to be divided across 3 ppl. Person #4, #5 are not involved in this.
So, select YES for person #1, #2, #3. Do not select anything for person #4, #5 (or select NO).

Above figure shows, person #2 gets 200 form finance and Person #2 has to give 150 to finance for internet bill.
So, person #2 gets a total of -150+200 = +50 from finance. Which is indicated in the last row named Total.

After adding the days dinner expenses, the excel sheet looks like below.


After some more entries, the excel sheet looks like



Download the excel sheet here (for 5ppl), excel sheet for 4 ppl. (Note: Enable macros)

Other Features:
1. Enter the item name, the date will be automatically taken as current date. You can modify the date.
2. Enter the amount spent by Person #x, YES will be selected for the person #x in the corresponding row.

Known Limitation:
It is not allowed to enter the details where Person A spends money for personal item of Person B. (where person A spends money, but YES/NO will have NO as person A does not share the item.)
But, there is a round about method:
Multiply the amount by 2 and enter the amount in Person A column and select YES for person A and person B. (in case 2 other ppl are involved, then multiply by 3 and select YES for 3 ppl)

Please share it with your friends.
I would love to hear from you for any doubts or suggestions :-)

Update: There are many sites offering excellent expense management features. www.buxfer.com www.mint.com http://quicken.intuit.com/
Many of the above sites has options to link to your bank account and categorize your spending.
You can continue to use this excel sheet if you do not have internet connection  available all the time.