Shared Expenses Base

I’m developing a base that will calculate shares of utility bills amongst the consumers of those utilities in a household. It’s pretty straight forward with one caveat: The percentage of responsibility for the bill changes based on the month. There are two roommates and one business that share the expenses and the business is only open for 6 months out of the year, so for half the year some expense are spilt evenly between the two roommates and the other half each roommate pays 25% of the bill and the business pays the other 50% (it’s actually a bit more complicated than this, as some utilities are split 40% 40% 10% half the year and 30% 30% 40% the other half). Here’s what I have so far for tables:

Utilities (water, gas electricity, etc) - contains a link to company table, method of payment, who pays and if it’s auto or manual
Companies - who gets paid with address, a link to the utilities table
Bills - a place to record each month’s bill with amount and a link to the utility and company table.
Entities - who is responsible for a portion of the payment (roommates + business) with links the last table which I am having a hard time with. This last table is meant to keep track of what percentage of the bill each entity is responsible for on any given month. I can’t decide if I should key off of the month or the utility. Any design ideas would be appreciated. Once this table is designed, I will build formulas to populate the amounts due for each entity once the main bill is entered in the bills company, and create a payments table to ensure that all payments have been received by each of the three entities.

thanks in advance for design assistance.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.