Creating a Budgeting and Expense Tracking System

[mnielsen]

Hi there! We are trying to move our corporate budget tracking to Airtable. As an example, say we have $400 to spend, the sum of the rate would need to be subtracted from $400 ongoingly. Do you have any ideas on how to best do this without manually subtracting the sum from $400 every time?

[jme]

Hello! A few suggestions for setting up your base to best track the budget...

1. In an Airtable database, one type of data goes in one table. So, for these rates, I would suggest naming the table "Rates" or "Income" and then tracking the quarter (Q1, Q2, etc..) in the table. You can use views to filter between different quarters, or other variables.
2. For tracking the budget, I would suggest creating a second table called "Campaigns" or even just "Budget" and then linking the individual Rates to the budget record. From there, you can access the Rates data via a Lookup or Rollup in the Budget table, and use a formula to subtract from the budget amount.
3. If setup correctly, linking a Rate record to a budget can automatically subtract it from that budget.

[DisraeliGears01]

Inspired by a discussion of the same topic on Reddit, here's an alt solution compressing into one table (I can't add screenshots in reply to the Reddit topic).

Screenshot 1 is a basic example of the base layout, and you add a self referencing link to the same table, titled here as "Spent". This makes the spending records children of the budget record (so you only use it on budget records). Then you rollup the Spend field, summing values, and finally you add a formula Budget-Spend Rollup. 

That's the basics, but two other additions can spice this up more. One is making the children records only assignable to the correct budget, using dynamic record filtering. You can see an example in screenshot 2, so only spending records with a matching tag are assignable (and you could expand that by adding more single select fields, for instance making Q1, Q2, Q3, Q4 tags instead). 

The other addition would be creating separate views for budget and spending, so you can filter out the irrelevant fields. 

If you're building out a tool to use for the next 5 years, I'd probably go with the multi-table solution, as that's more properly structured long term, but you could roll it into one if you'd like.