Help

Re: Budgets? can i do actual budgets in airtable?

2673 1
cancel
Showing results for 
Search instead for 
Did you mean: 
kera_mchugh
6 - Interface Innovator
6 - Interface Innovator

My guess is maybe NO because the topics that come up on searching “budget” have essentially NO responses…

I looked at a template base called Event Planning & Budgeting…which i thought would help… but i’m simply not following…
I’ve experimented with rollups and things, but it seems like it’s more work and time to do it in airtable if i have to do double entry to get the view i need - eg.enter an expense with its various attributes in one table, and then go and add that expense to the corresponding rollup field in another table…

am i doing it wrong? is there not a way to automatically aggregate based on an IF statement? surely i’m missing something…

my vision is this:

One table for overall budget
one table for expenses
one table for revenues

budget table would aggregate from the other two, with a select field “type” column of revenue or expense which would then be used for the grid view…
budget table would have the high level DEPTs in each type…
then the revenue and expense tables would have: “item ID (primary)”, “item” “amount” “dept” “vendor”

dept rows in budget table would then aggregate the totals…

does that even make sense? I’m not even sure now! lol
My budgets aren’t complicated, but we’re really WANTING to make a wholesale switch over to airtable and these are a BIG piece of our puzzle. We don’t have to calculate projections (much) or do anything particularly fancy… just regular old math… but we do have multi-sheet budgets in excel that usually have an “overall” sheet that aggregates the different pieces of each budget…
and then one massive one that aggregates all THOSE budgets… :slightly_smiling_face:

are we barking up the wrong tree?

14 Replies 14

It’s hard to answer clearly without seeing an example of what you currently use, as the sins one can commit using a spreadsheet are myriad and manyfold.* From 30,000 feet, it sounds quite doable — and there is a growing body of chthonic Airtable wisdom that can make one’s database get up and tap-dance—

–but at the same time, there are a few astoundingly simple functions nearly impossible to perform. I understand being reluctant to share too much information — even if highly redacted — in a public forum, but feel free to contact me here through private messaging, and we can discuss what you’re hoping to do in a little more detail.
. __________

* I once took over a company that had quadruple-entry bookkeeping, at least. Every time the previous owner experienced a serious-enough failure of his business processes, rather than fix the damn process, he ginned up another couple hundred lines of Excel script designed to catch the failure when it next occurred. It took nearly a full-time position simply to enter the same data multiple places, mash it all together, filter out the errors, determine whether the errors represented a process failure or simply fat-fingered bookkeeping, correct the latter, filter again… I ended up having to keep him on the payroll for nearly a year as we delicately unwound his creation’s tentacles everywhere they’d taken hold throughout the business…

Thanks. That gives me some hope! I’m sure I’m just impatient on my learning curve… I tend to expect instant results based on my tenure working with technology, but sometimes… well… it just doesn’t come.

I’d mostly like a little more detailed, or perhaps just CLEAR, instruction on how to get data aggregated from one table to another… rollup fields SEEMED to be the optimal direction, but if there is a better way to track expenses by dept and display a total per dept in a budget table without having to enter the expense item itself TWICE (essentially), that would be terrific.

still not quite clear how to share a table - redacted - so bear with me… i may just recreate in a new base to get my confusion across more clearly.

HammerOz
7 - App Architect
7 - App Architect

Yes, budgets will work very well. Your approach is right. You will be able to build good insights with airtable.

Michael_Berman
6 - Interface Innovator
6 - Interface Innovator

What do you need beyond the basics? This is a simple budget that you can throw in each of your items from the “Budget summary” page and it will keep track of all separately. Obviously there are no bells and whistles here, but it seems to behave as you request…

Hope that helps
Michael

Thomas_Jaccino
6 - Interface Innovator
6 - Interface Innovator

The easier way to do this would be to make your actual nuts and bolts budget vision on paper and figure out how to make it work in airtable, rather than figure it all out in one pass.

Here’s an example of one I did for a budget that has a section for line items which are then added in a project view by project, and a final departmental view for summary of the entire organization’s projects.

Anyways there’s an idea for inspiration

@Thomas_Jaccino I am attempting to do just what you are mentioning here, though I cannot see your screen shot. Would you mine sharing?

Ivan_Carlson
7 - App Architect
7 - App Architect

Yes it can be done in Airtable. Airtable can track actual financial transactions, so it can also track any other type of transaction such as budget, order, commitment, etc. It all depends how you flag or tag the transaction line item. In short, one can tag the double-bookkeeping entry transaction type as Actual, Budget, etc. and then do reporting based on filtering, rollups, etc. This may give you some ideas from this template Accounts - Airtable Universe

As you will see here, I am close, but unable to get my linked expense record transactions to SUM total when populating the budget record. There is either an issue with my formula under the actual expenses field or in the way in which I set up the linked record - though that seems to be working fine. Screen Shot 2019-01-01 at 8.02.22 AM.png

I’m looking to do the exact same thing as Delaney_Keating. Does anyone here have any insight into how we can get this working?

Here’s an example of what we want to have happen:

  1. Expenses Table: We log a new expense, tagging it with the “Office” category (which is done by linking it to the “Office” record from the “Budget” table).
  2. Budget Table: The “Actual Expenses” field automatically updates to add our logged expense in the “Office” record. This, in turn, also automatically updates the “Variance” field in the “Office” record.

Hi @Delaney_Keating,

This is a pretty old post, so you may have already figured this out, but I think you just need to convert that “Actual Expenses” field to a “Rollup” rather than a “Formula” field.

When you change it to type “Rollup”, it will ask you which “Linked record” field you want to Rollup — you’ll select your “Expenses” field. Then it will ask you which “Field” from the “Expenses” table you want to rollup — you’ll select your “Cost” field. Then it will ask you what “Rollup Function” to use — you’ll use SUM(values).

When you’ve done this, it will sum all of the “Costs” of the “Expenses” you’ve linked to in that Budget category (row). Then you can use that sum to calculate your “Variance”.

After reading this, I think its similar to what I am trying to do, but I’m still lost.

I have 2 tables: Event Budgets and Event Costs.

In the Event Budget I have every Event for the year listed (approx 70 events). On that table the Columns go as follows: Event Name, Budget, Estimates, Actual, Remaining. In this table, the only number I would (ideally) put in is the Budget (ex: 10,000).

In the Event Costs is every service I paid for related to the event. The columns go as follows: Event Name, Service (printing easel), Service Type (Branding), Estimate, Actual, Date paid…etc. In this table, I would be manually entering every number.

What I would like to do, is have Event Budget look to Event Costs and find all costs related to 1 event, and put that sum into the Event Budget-Estimates (or Actual).

I would like to manage even more things using Airtable, including checklists, staff names etc, because the grid view looks really nice. However its taking me so long to figure it out!

Hello Natasha,

I think what would help here is a rollup field., which will allow you to sum all costs related to an event. Then you can use fields to calculate the difference between the budget and the actual.

I hope this helps.

Ivan_Carlson
7 - App Architect
7 - App Architect

Yes you can use Airtable to enter a financial budget. Maybe this base will give you some ideas. Accounts - Airtable Universe