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

Edit: I wrote milk in my example, but it is actually the example for butter, I missread your table. But this shouldn't matter for the computation.

This might look a bit lengthy, but I think that it should be easy to follow the steps.

Let us only consider milk consumption, since it should be a complex enough example. Note, that the total number of days sums up to $360$ Student$\cdot$Days and that is what the last column is refering to.

\begin{array}{|c|c|c|c|} \hline & \text{Milk consumption in %} & \text{Days present} & \text{Days present in % } \\ \hline A&50 &20 &5.555\\ \hline B& 30 &80 &22.222\\ \hline C& 0 &90 &25.0\\ \hline D& 10 &100 &27.777\\ \hline E& 10 &70 &19.444\\ \hline \end{array} Since roommate C does not consume any milk, he is not relevant for this calculation.

Since it doesn't matter, when the roommates are present, but only for how long, I'm going to assume, that roommate A is present the first 20 days and then leaves, roommate B is present the first 80 days and then leaves, etc.

We now have to calculate the percentage of milk consumption during the different periods. Furthermore, we need weights $w_i$ that are basically the Student$\cdot$Days during that time.

1. Day 1-20. Everybody is there. $w_1=100/360$ \begin{array}{|c|c|c|c|} \hline & \text{Milk in %} \\ \hline A&50 \\ \hline B&30\\ \hline C&0 \\ \hline D&10 \\ \hline E&10 \\ \hline \end{array}

2. Day 21-70. A leaves. $w_2=200/360$

   Roommate B D and E make up all the milk consumption, since B consumes three times as much milk as D and E we get \begin{array}{|c|c|c|c|} \hline & \text{Milk in %} \\ \hline A&0 \\ \hline B&60\\ \hline C&0 \\ \hline D&20 \\ \hline E&20 \\ \hline \end{array}

3. Day 81-90. E leaves. $w_3=30/360$ \begin{array}{|c|c|c|c|} \hline & \text{Milk in %} \\ \hline A&0 \\ \hline B&75\\ \hline C&0 \\ \hline D&25 \\ \hline E&0 \\ \hline \end{array}
4. Day 81-90. B leaves. $w_4=20/360$ \begin{array}{|c|c|c|c|} \hline & \text{Milk in %} \\ \hline A&0 \\ \hline B&0\\ \hline C&0 \\ \hline D&100 \\ \hline E&0 \\ \hline \end{array}
5. Day 91-100. C leaves. $w_4=10/360$ \begin{array}{|c|c|c|c|} \hline & \text{Milk in %} \\ \hline A&0 \\ \hline B&0\\ \hline C&0 \\ \hline D&100 \\ \hline E&0 \\ \hline \end{array}

Now we have the consumption during the different periods and the corresponding weights. We simply need to add up everything. I'm going to write this as a matrix-vector product for compactness.

Let $C\in \mathbb{R}^5$ be the consumption of every roommate considering their consumption and the time they are present (thus your desired quantity). Let $M \in \mathbb{R}^{5\times 5}$ denote the percentage during each period, and $W\in \mathbb{R}^5$ accounts for the weights. We then write \begin{align} C &= M\cdot W \\ &= \begin{pmatrix} 50 & 0 & 0 & 0 & 0 \\ 30 & 60 & 75 & 0 & 0 \\ 0 & 0 & 0 & 0 & 0 \\ 10 & 20 & 25 & 100 & 100 \\ 10 & 20 & 0 & 0 & 0 \\ \end{pmatrix} \begin{pmatrix} 100/360 \\ 200/360 \\ 30/360 \\ 20/360 \\ 10/360 \end{pmatrix}\\ &= \begin{pmatrix} 13.888 \\ 47.91666 \\ 0 \\ 24.30555 \\ 13.888 \end{pmatrix} \end{align}

This means, that given the assumption that you paid $100\$$ for milk during the time period, roommate A has to pay $13.9\$$, roommate B has to pay $47.9\$$, etc.

The vector $W$ stays the same for the other products, but you need to calculate the matrices $M$.