Need help devising a Budget Report for a Theatre

[James_Lanius]

Hey Guys,

Looking for some suggestions/guidance on how to create an expense tracking system that reports on expenses against budgeted amount.

Our company is a theatre. We produce 6 shows per year. Each show we spend money in about 100 accounts. Our accountants use ‘accounts’ and ‘CLASSES’ where classes are the shows.

I’m interested in building a system with:

Expense Entry
Budgeted Amounts Entry (by Show, by account)
A Report page that summarizes expenses {by show, by account} AND subtracts that total from the Budgeted amount, to show how much is spent against the budget.

I’ve figured out how to view the summary {by show, by account} using “Group”, but I don’t know how to then use that info to do math, against budgeted amount.

It looks possible to do this with the PivotTable Block, but I’m trying to keep to the budget version of AirTable.

I’d link to my working draft airtable, but I’m not sure how.

Any insight is appreciated.

Thanks!

[David_Skinner]

You should be able to achieve this with 3 tables - Shows, Accounts, Expenses.

You can have target budget fields in the Shows and Accounts tables.

Every expense in the Expenses table would be linked to a Show and an Account. You can then roll up the expenses for each show in a roll up field in the Shows table (the same applies to the Accounts table). You can then use a formula field to subtract this rolled up value from the target budget field.

[James_Lanius]

Thanks David.

The issue is that every budget amount is per show & per account.

Show 1 - Sets - $1000
Show 1 - Costumes - $500
Show 1 - Props - $250
Show 2 - Sets - $1500
Show 2 - Costumes - $750
Show 2 - Props - $300

Which mean each Budget value & Each expense needs a Show and an Account. I don’t know how to setup a LookUp field with two parameters. Does that make sense?
Maybe I’m missing something.

[JonathanBowen]

Hi @James_Lanius - just to expand on @David_Skinner’s solution, you could do the following:

Have a “Budget & Expenses” table with each record linking to a show and an account. Each record will have a budget amount and an expenditure amount and you can calculate the difference. You can also rollup the budget and expenditure amounts against the account (across multiple shows) or against the show (across multiple accounts);

If you have multiple expenses in each show/account category, you could create a lower level “expenditure item” table:

and then roll these up into the “budgets & expenses” table:

JB

[James_Lanius]

Thanks Jonathan,

This is super helpful. I’m just learning what AirTable can do.
I think I see a way to get this done, based on what you both suggest. I’ll play with it and post the results in this thread.

Thank you!

[David_Skinner · Accepted solution]

I think you probably want 4 tables then. Shows, Accounts, Budgets, Expenses.

The Budgets table is where you set the budgets. Each record would link to a Show and an Account. So your budgets would look like the table you suggested:

Show 1 - Sets - $1000
Show 1 - Costumes - $500
Show 1 - Props - $250
Show 2 - Sets - $1500
Show 2 - Costumes - $750
Show 2 - Props - $300

Every expense would then be recorded in the Expenses table and linked to the relevant budget.

[James_Lanius]

@David_Skinner That’s exactly what I ended up doing.
It’s not 100% ideal, because to enter an expense you have to type the combined “SHOW & ACCOUNT” lookup value, but it works. Hoping it’ll work nicely in an e-mailed form version for my department heads.

Thanks for the tips.
Screengrabs below: