Skip to main content

Use AirTable instead of Excel to manage a large budget

A

Hello all

I am pretty sure I could achieve what I would like to do in Airtable, but I know it will take a bit of time to set up - so wanted to double check and gauge opinion on here first.

I am currently managing a large budget in Excel and it’s a mess. I don’t get access to the live accounting system my client uses, so rely on inputting invoices as they are sent to me for approval. I then cross check this against reports sent from the finance department monthly - to make sure nothing has been missed.

I need to be able to set up budget areas on two levels - For example :

Main budgets could be : Set up costs, ongoing costs, venue specific costs
Secondary budgets could be : Lighting, Scenic, Props etc

I then need to be able to assign transactions to the relevant budget (ie - setup costs>Lighting and ongoing cost>Scenic)

I’ll need to be able to produce reports (and I think this is where it gets tricky in Airtable) to send to the Finance Director showing -

  • Current budget with Budget v actual
  • monthly cashflow
  • Spend per supplier

I am sure this must be possible in Airtable, but as I say, I think it would just take time. For the reports, I think I would need interface designer or maybe page designer?

Any pointers and advice much appreciated.

Thanks in advance

Regards
Andrew

2 replies

W

Hi @Andrew_Davies,

I also believe what you want is achievable in Airtable. I know tracking a budget and linking expenses, invoices, etc is achievable in Airtable (as I have set those up), and the reporting is totally doable in Airtable. The are a couple of considerations I recommend that will help the transition from Excel to Airtable. Also, if any of what I say below you already know, please take it for what it is.

  1. The most important one I recommend considering, especially for setting it up, is a fundamental difference between Excel (spreadsheet) and Airtable (database). While many people structure their data in Excel to mimic records (using rows for all related info), Excel doesn’t hold you to that. What that allows for in Excel is being able to calculate or use formulas very openly when it comes to layout. Airtable being a database structure means all rows are records and all columns are fields, so formulas and calculations will work a bit differently. All that said, I’ve seem just about any type of calculation done in Excel done in Airtable. There are exceptions and those can often be either reworked, rethought, or linked externally to Airtable.
  2. Who will interface with the data and how? This is a big one, especially as you may want to involve others, even eventually. This would cover everything for inputs and outputs. As for inputs, you mentioned you have invoices sent to you from clients. How are they sent (format) and what would you need to make your life easier with them? As for reporting, either to finance or to directors, how do you need it to look. There are many options in Airtable when it comes to reporting so here I recommend outlining or mapping your ideal scenario (live reporting, static reports like PDFs, etc.) and then finding the Airtable option that fits best. In my experience, this gets people very close because there truly are so many options when it comes to Airtable.
  3. What is your timeline to transition and do you want additional help to get it going? Ideally, you will have a seamless transition from Excel to Airtable. In my experience, to make it seamless, you will want to invest enough time and resource to get Airtable where you want it, integrate on a small scale to test, and then train anyone who may need it. Many people are very familiar with Excel and are comfortable with it, on some level. When it comes to Airtable, the familiarity may not be there, so I would definitely consider time to come up to speed with it. It will be totally worth it as Airtable will give you great capabilities, especially if you bring others on to collaborate.

I hope this helps. If you want to talk specifics, feel free to reach out to me by email or at my site. I’m happy to work with you to get your goals met.

Thanks,
Chris

A
  • Andrew_Davies
  • Author
  • Inspiring
  • 65 replies
  • December 15, 2021
Williams_Innova wrote:

Hi @Andrew_Davies,

I also believe what you want is achievable in Airtable. I know tracking a budget and linking expenses, invoices, etc is achievable in Airtable (as I have set those up), and the reporting is totally doable in Airtable. The are a couple of considerations I recommend that will help the transition from Excel to Airtable. Also, if any of what I say below you already know, please take it for what it is.

  1. The most important one I recommend considering, especially for setting it up, is a fundamental difference between Excel (spreadsheet) and Airtable (database). While many people structure their data in Excel to mimic records (using rows for all related info), Excel doesn’t hold you to that. What that allows for in Excel is being able to calculate or use formulas very openly when it comes to layout. Airtable being a database structure means all rows are records and all columns are fields, so formulas and calculations will work a bit differently. All that said, I’ve seem just about any type of calculation done in Excel done in Airtable. There are exceptions and those can often be either reworked, rethought, or linked externally to Airtable.
  2. Who will interface with the data and how? This is a big one, especially as you may want to involve others, even eventually. This would cover everything for inputs and outputs. As for inputs, you mentioned you have invoices sent to you from clients. How are they sent (format) and what would you need to make your life easier with them? As for reporting, either to finance or to directors, how do you need it to look. There are many options in Airtable when it comes to reporting so here I recommend outlining or mapping your ideal scenario (live reporting, static reports like PDFs, etc.) and then finding the Airtable option that fits best. In my experience, this gets people very close because there truly are so many options when it comes to Airtable.
  3. What is your timeline to transition and do you want additional help to get it going? Ideally, you will have a seamless transition from Excel to Airtable. In my experience, to make it seamless, you will want to invest enough time and resource to get Airtable where you want it, integrate on a small scale to test, and then train anyone who may need it. Many people are very familiar with Excel and are comfortable with it, on some level. When it comes to Airtable, the familiarity may not be there, so I would definitely consider time to come up to speed with it. It will be totally worth it as Airtable will give you great capabilities, especially if you bring others on to collaborate.

I hope this helps. If you want to talk specifics, feel free to reach out to me by email or at my site. I’m happy to work with you to get your goals met.

Thanks,
Chris


Morning Chris

This is so helpful and encouraging - thank you.

To go through your 3 points.

1 - This makes sense. I have some experience of databases but more of Excel. I find it more and more frustrating when work colleagues / clients use spreadsheets for everything from Gannt charts to Issue trackers and schedules when there are much better tools available.

2 - I would be the only one using it. I am a freelance Project Manager and need to report data back to my client only, so a static PDF report would suffice. The project I am running runs for 5 years, so it is worth getting it right. My client are rather stuck with using Word and Excel for virtually everything and getting them to use a new tool would be a challenge (and pay for another licence). Everything is emailed around as attachments which drives me nuts, but that’s another story.

I am sent invoices for approval, which I then email back to the finance department for payment.

For the PDF reports, I’d like to be able to produce a couple of different types. (Ie high level budget v spend, cashflow forecast etc). Lots of detail wouldn’t be necessary in the report - they just want to see the big picture. Emphasis here would be on clarity as they are for internal use only, but I would like them to look nice and professional. (I have used Page Designer in Airtable before. Would be nice to be able to use that, but not sure if it can do what I need. I think that’s more for record by record display)

3 - As I am the only user, the transition period is down to me really. The company is not relying on this at all, invoices will continue to be paid etc. It’s really for my benefit only as project manager so that I can keep track of figures and report back to the board whenever they ask.

Thanks again for your reply - any more pointers you can give me would be well received.

Regards,
Andrew
andrew@flair.london

Reply


Most helpful members this week

Terms & Conditions