Shared groceries expenses between roommates to be divided as per specific consumption ratio and attendance

Question

My apologies if this question is in the wrong section.

Couple of my roommates & I (total 5 people) share the groceries expenses. We record the purchases in an Excel sheet, and also have the ratio of consumption of individual items specified for each member.

For eg.

- Roommate A doesn't consume Milk and Jam, but consumes Bread equally and Butter most, so
    1. Milk = 0%
    2. Jam = 0%
    3. Bread = 20% (total expense for Bread equally divided between 5)
    4. Butter = 50% (total expense for Butter as per consumption ratio)
- Roommate B doesn't consume Milk, but consumes Jam and Bread equally and Butter more, so
    1. Milk = 0%
    2. Jam = 25% (total expense for Jam equally divided between 4)
    3. Bread = 20% (total expense for Bread equally divided between 5)
    4. Butter = 30% (total expense for Butter divided as per consumption ratio)
- Roommate C doesn't consume Butter but consumes Milk, Jam and Bread equally, so
    1. Milk = 50% (total expense for Milk equally divided between 2)
    2. Jam = 25% (total expense for Jam equally divided between 4)
    3. Bread = 20% (total expense for Bread equally divided between 5)
    4. Butter = 0%
- Roommate D doesn't consume Milk, but consumes Jam and Bread equally and Butter less, so
    1. Milk = 0%
    2. Jam = 25% (total expense for Jam divided by 4)
    3. Bread = 20% (total expense for Bread divided by 5)
    4. Butter = 10% (total expense for Butter divide as per consumption ratio)
- Roommate E consumes Milk, Jam and Bread equally and Butter less, so
    1. Milk = 50% (total expense for Milk equally divided between 2)
    2. Jam = 25% (total expense for Jam divided by 4)
    3. Bread = 20% (total expense for Bread divided by 5)
    4. Butter = 10% (total expense for Butter divided as per consumption ratio)

Calculating individual member's share up to this stage is relatively easy, but if the duration is considered, especially when some member may not be present for the entire duration, the calculation is a

For eg.

Considering 100 days of calculation period, 

- Roommate A was present only for 20 days
- Roommate B was present only for 80 days
- Roommate C was present only for 90 days
- Roommate D was present for the entire 100 days
- Roommate # was present only for 70 days

So, I am able to calculate only for the first 2 scenarios listed below, but not for the third one, which would be the most accurate.

1. Share based on consumption ratio only
2. Share based on attendance only
3. Share based on consumption ratio AND attendance

I have really tried hard to come up with the logic for the formula, and I'm sure this is a fairly common calculation method but I just can't wrap my head around it (I don't even know the correct mathematical term for this type of calculation).

I would be very happy if someone helped me with this.

Thanks in advance.

Answer 1

The solution to this problem is somewhat like grading on a curve. Use the consumption ratio multiplied by the attendance (which is also a ratio, out of 100 days) to calculate how much each person owes. This will leave you short. Then add together all of the shares in a category, determine the % increase required to get to the actual cost of that category, and increase all the shares by that %.

Example, for butter:

- Roommate A, 50% butter,  20 days.
- Roommate B, 30% butter,  80 days.
- Roommate C,  0% butter,  90 days.
- Roommate D, 10% butter, 100 days.
- Roommate E, 10% butter,  70 days.

Say you spend $100 on butter.
Step 1:

- Roommate A, 50% of  20% of $100 is $10
- Roommate B, 30% of  80% of $100 is $24
- Roommate C,  0% of  90% of $100 is  $0
- Roommate D, 10% of 100% of $100 is $10
- Roommate E, 10% of  70% of $100 is  $7

Now, that all adds up to $51.
To get to $100, you need to multiply $51 by approx 196%.
So, you multiply each of those shares by 1.96:

- Roommate A, $19.60
- Roommate B, $47.04
- Roommate C, $ 0.00
- Roommate D, $19.60
- Roommate E, $13.72
- Total:      $99.96 (four cents got lost due to rounding)

Answer 2

Bren's comment is right on the mark. The typical solution is to divide all bills by 5, and for special items, the person buying it just marks his name that it's not community food. Your attempt at a granularity level this detailed is admirable, but produces false results. What happens when I claim to be a zero percent milk drinker but when someone gives me cookies, I have a glass of milk? The effort to get true accuracy will cost far more in time spent than the results are worth.

Answer 3

1. For a personal finance forum, this is too complicated for sustained use and you should find a simpler solution.

2. For a mathematical exercise, you are missing information required to do the split fairly. You have to know who overlaps and when to know how to do the splits. For an extreme example, take your dates given:

Considering 100 days of calculation period,

• Roommate A was present only for 20 days
• Roommate B was present only for 80 days
• Roommate C was present only for 90 days
• Roommate D was present for the entire 100 days
• Roommate E was present only for 70 days

If Roommate D was the only person present for the last 10 days, they should pay 100% of the grocery bill as they are the only one eating. From your initial data set, you can't know who should be splitting the tab for any given day.

To do this mathematically, you'd need:

1. A schedule of who is present on which days.
2. A ratio of how many "shares" of each item someone uses. More like this instead of using percentages:
   • Roommate A uses Milk: 1, Jam: 0, Bread: 1
   • Roommate B uses Milk: 0, Jam: 1, Bread: 2
   • Roommate C uses Milk: 2, Jam: 1, Bread: 1.5
3. For a time period where Roommates A & B are present, you need to sum the "shares of bread" (3 in this case) and assign the cost based on the proportion of bread a roommate uses to the total amount of bread consumed. If Roommates A & B are the only ones present and $15 is spent on bread, Roommate A owes $5 (1/3*$15) and Roommate B owes $10 (2/3*$15).
4. Repeat the calculation for every unique interval with different roommates.

But don't forget "In Theory, Theory works. In Practice, Practice works."

Good theory would say make a large, complicated spreadsheet as described above.

Good practice would be to split up the costs in a much, much simpler way.

Answer 4

When I was in grad school (at an engineering school) my apartment-mates and I came up with this formula:

1. We each bought 100% of the food we intended to consume.
2. We each consumed 0% of the food that we did not buy ourselves.

Worked marvelously.

Answer 5

So your whole approach, and the attempt to scale this is flawed. You will alienate roomates, provoke arguments, and make everyone's life more difficult. There are too many variables and unforeseen possibilities. For instance:

• "Why should I have to pay for Joe to go buy the expensive organic milk when I'm fine with the cheap stuff?"

• "I planned on being here for 20 days, but was gone that long weekend, recalculate everything please."

• "I already paid for this month, but now you're asking for more because James wanted to recalculate for a long weekend?"

The right way to do this is to set up loose, reasonable agreement among the participants and treat that as a contract, but with some flexibility/mercy on small dollar amount items.

For instance: There are 5 of us, so everyone provides food (and shops/cooks) one night a week. We're solo on Friday and Saturday (people eat out more then anyway), and everyone puts in $10/week (or whatever) for breakfast cereal, snacks, etc. If you can't be here on your night, work out to trade with someone. If you miss out on a meal... oh well.

As long as people feel like they have a say in the discussion generating this and it's not dictated to them, then most of the time this is far superior. If people need this level of detail, then perhaps those people should live alone or move in with Sheldon Cooper from "The Big Bang Theory".

Answer 6

I asked how often grocery purchases are made in a comment, but I'm going to assume weekly for simplicity.

If a roommate is present during the week following a grocery purchase, then they owe a share according to their preferences as you outlined them above. You will have to track the grocery cost by category for that week and calculate the balance owed by the person for that week. If there is a partial week where most expect to leave for a holiday or otherwise, then fewer groceries should be purchased for that week, and the cost of shares will decrease accordingly.

One need only indicate preferences once, and weekly attendance thereafter.

The only issue remaining is to determine how to record shares. If a normal person consumes 3 shares of milk, and .5 shares of butter, and so on, you simply add up all of the milk shares for the week and divide the milk bill by those shares. Same with the butter.

The downside of this method is that you have to predict consumption in advance, so you may instead calculate by consumption after the fact with a deposit paid by all to create the initial grocery supply which will be refunded when that person leaves the grocery purchase co-op, and shares are calculated by who participated in the week prior to the grocery purchase. This also allows for a mid-week refresh if any commodity incurs higher than expected consumption, with the mid-week bill being added to the end of week refresh trip.