Creating a Budgeting and Expense Tracking System

[ZLEVY]

Okay I think this might be a tricky one but let me know if you have any answers. 

Here's the basic concept. I'm producing a summer concert series and I want to create a budget and expense tracker. I want have a couple things I'm trying to accomplish.

First, I want to be able to create a budget and allocate an amount of the budget to a certain category (labor, rentals, food, etc.).

Next, I want to be able to track how much we are spending on each of those categories for each show. (Show 1: $1,000 on labor, $5,000 on rentals, $150 on food, etc.)

Then, I want to link those together. For example, I want the show totals for each major category to then update the overall season long budget (the first item). 

Ideally, there is an interface with three pages. One is the Overview dashboard which shows the total budget, total spend, and remaining total budget. It would also show each individual category with total budget, spend & remaining. The second page would be a page to edit the categories and budgets for those categories as well as view the spend per show within a specific category. The third would be the individual show pages where I can input the spend on each category for that specific show. 

  

I've tried a couple different things but I can't seem to figure out how to link the show specific with the overall budget. I started by having one table for the full season budget with the categories and their respective budgets and then a separate table with each show being an item in the table. I also tried having a table for each show. I'll attach some pictures. Hope you can help me. Thanks! 

[VikasVimal]

Yeah. Have a 'Shows' table and skip having a different table for each show. In a database, one type of data goes in one table. You can link each entry to the correct Show, and Category.

Then in an interface, use list mode, to shows entries, grouped by Category and then by Show. That should be it. In List mode, you can show the rollups of budgeted and expensed by each Budget Category and Each Show. Mix and match to get the numbers you require.

[Mike_AutomaticN]

Hey @ZLEVY!

I agree with @VikasVimal's answer, which will result in a lean -although somehow limited- system. If you are looking for something more robust for getting better insights, and using your data in different ways you might want to explore having the following:

1. Seasons -assuming you'll have multiple seasons throughout the years to come (each season being a unique record on your Seasons table).
2. Shows (each Show being a unique record on your Shows table).
3. Categories (each budget/expense category being a unique record on your Categories table)
4. Show Categories (each record is a combination of Show and Category: e.g. Show 1 - Food)
5. Transactions (each expense being a unique record on your Transactions table).

Shows should have a linked field to Seasons, so you can identify to which Season each Show corresponds.
Show Categories should have a linked field to Shows and a linked field to Categories.
Transactions should have a linked field to Show Categories table. In such way, you can identify to which show and and to which category your expense corresponds.

Now, you can have the following rollups:
1. In Show Categories, you can rollup total SUM() of transaction amounts.
2. In Categories, you can rollup total SUM() of Show Categories amounts.
3. In Shows, you can rollup total SUM() of Show Categories amounts.
4. In Seasons you can rollup total SUM() of Shows amounts.

If at some point in time you'd need the rollup per Season Categories for example, that would require once again a new table such as your Show Categories which relates each show to one category (but used for Seasons).

Having this level of granular data will allow you to build a super flexible and scalable database. However, if you need something way leaner, building something simpler and applying filters for each category each time you want to see total values would work as well.

If you'd like to go through it on a quick call, you can grab a slot using this link and I'd be happy to guide you around.

Mike, Consultant @ Automatic Nation