Hey guys, I've got a new challenge in my quest to create a base for tracking personal finances. A quick review:
Tables
- Categories (I call them buckets)
- Transactions
- Designations
Transactions are either entered manually or bulk imported via csv from my bank. Each transaction is assigned at least one category via the designations join table.
In the Categories table, I have assigned each category a monthly budget. Then I roll up the designations amounts filtered by month (this is my current setup - I'm working on a different option). Now, you would think that a simple third formula field for [ budget - total spent (roll-up) ] would give me the balance for each of my budgets for the month. Should tell me whether I've received all my salary for the month or if I've gone over budget for eating out, for example.
Here's my confusion: there are situations in which categories may have counter-intuitive balances. For instance, we have a category for gifts received like birthday money. We categorize all related income AND expenses to the same income category so we can see at all times how much "birthday" money we have left to spend. $100 birthday gift goes in, category balance is $100. Spend $50 and now the balance is $50, right? My kids, however, spent some gift money before we had a chance to deposit it in the bank which created an interesting scenario in which the balance of the gift category (an income category) was negative. Here's what the record looked like:
So as you can see, the bucket total calculates correctly: a negative total because the transactions are debits, even though it's an income account. But then when I calculate the bucket balance, things go awry. The bucket balance should be -$71 because we've overspent the $0 estimated budget. Then once we deposit the kids gift money, the bucket total and bucket balance should both be +$29. And there are similarly complicated issues on the expense category side, haha.
So, what's the correct formula here? Am I looking at long series of nested IF statements???