Bulletproof Budgeting with airCFO's Budget Build Template

Hi team, in this video I am going to show the process for standing up an expense budget build for our clients that we can then add into our client data HQ and push through into our month end reporting package as well as, our stand alone budget versus actuals dashboard.

So to get started, we will pull up our expense expense budget build template here and we will make a copy.

I'm going to add this into the folder for demo deliverables that will go on our website. I can go ahead and close out the template.

I'm also going to just grab our budget versus actuals. . Um. . . . . . . . . . . . . . . . . . . . . . . . . . . . . Once we have our budget build created for the client, we're going to need to update the homepage as the first step.

So we're going to change the name. I'm going to use air CFO demo deliverable data. For this. And then we're going to open up.

I'm going to open up app script. What is going on? Okay, there we go. We're going to start by running our time-based trigger.

It's going to ask us to. Review permissions. . And we're going to accept those. Then we are going to pull in.

Out. Our notion data to the homepage. You will see this information start to update. Soon as this is finished executing, we can close out the app script window.

Execution completed. Okay, great. I think I still have, yep, there's an old version of the day. I'm going to go over that here, and then I'll make sure that that is updated in notion as well.

I'll go do that over on my other screen here. . All right. So once we have the new data HQ in that cell, we're going to show you how to do this.

All right. So, we're going to connect it to our budget build worksheet by hovering over the error and clicking connect.

Just give that a second to access the data. The HQ workbook and pull that information in. Now once permissions are added here, this workbook will be able to look at our clients historical financials and it will constrain the struct a couple of worksheets that our client and us should be able to review

in order to give us a sense of our historical spend. So then we can use that information to. Set our budget for the next 12 to 24 months.

So permissions are adding. There we go. It's been updated. And after we give it a couple of seconds. To grab that data, we can then look at these three worksheets here.

So it's not to the budget summary. Probably has hasn't pulled through yet. Nope. Here on the budget summary. Three, the first thing you're going to select is which month we want to start our budget in.

And so we'll say we're going to start in April of 2023. All right. Now you can see the history. Historical information starting to be pulled in so we can see our cash flow.

Historicals as well as our P and L summary. There we go. This is a good high level view of the company's historicals.

But when we want to go a layer deeper, we can also look to our P and L detail tab so you can see individual.

Multiple months and individual transactions inside of accounts for that month. And we can drill down into individual departments to see historicals as well as.

Individual accounts to see vendor spend. So these are three tabs to give us context. And then before starting the budget build, we also want to look at this worksheet as well.

This. This worksheet shows our top 200 vendors for the historical period. And it gives us an opportunity to re categorize expenses if they're being bucketed to the incorrect account.

You can do that by changing account name inside of this column. And so we should go through line by line and have our client update any vendors that they would like to be booked.

To different accounts. So for example, if this one is going to be booked from, we want to change it from sales to con to marketing contractors.

We enter that here and then it's conditionally formatted in this column and it's also pulled up to the top. So.

Before we set the budget, we should have this table at the top populated and then go back into quick books and recategorize those vendors that the historicals reflect the way that we want to bucket vendors for the budget.

I'm going to create a separate loom to show the steps we should take in order to update assumptions around this budget to create something that's bespoke.

For an individual client. But to start, when you go to the assumptions tab, you'll notice that we are loading in a set of default assumptions for the.

For each account in the P and L that will give us sort of a high level budget upfront. So we're just assuming monthly percentage growth for the majority of our revenue and expense assumptions.

Except for. The payroll related accounts, which are being pulled from the hiring roadmap. So one thing that we'll need to do in order to set up this budget bill, this come to the hiring plan worksheet and you'll notice that the.

The hiring plan here is being pulled from our client data HQ hiring roadmap. Worksheet. So over here in our client data HQ, we have our hiring roadmap and this exact thing is being pulled into our expense budget build.

So we don't actually want to make any changes to the employee salary detail section of this and I'm going to.

This range is going to be protected so that people cannot make changes to it, but what we do need to do on the hiring plan is come down here and map up or make assignments of each headcount.

And then we have a section for salary, we have a section for payroll taxes, bonuses, benefits, and then dental vision, which don't come into play as often anymore, but the last.

When it's contractors. And so, for example, we see that wages has already been assigned to the salary, GNA, row payroll taxes should be assigned to the taxes, GNA, row, and went.

Each of these accounts are assigned, then the projections or the calculations from the hiring plan begin to get pulled in to the assumptions tab here.

So, mapping up the accounts on the hiring plan is an important step in this process. Once all of those hiring plan accounts are mapped up, then we will sort of have a preliminary budget that is being calculated based on 0%.

Monthly percentage growth off of the clients' historicals. And you can see that full budget on this tab right here. So you can see each of our revenue.

New expense accounts with the information being pulled directly from the assumptions tab. And so at this point, we have our initial kind of shell of a budget.

The last step that we need to take. Take an order to connect this budget build worksheet to our month end reporting package and our budget versus actuals dashboard is we need to come to our data HQ and we are going to drop the link to the budget build.

Into this budget table in the budget worksheet. So I'm going to drop the link and this step tends to gum up Google sheets a little bit so it may take a minute.

In order to for the budget link to be recognized by Google sheets. But once Google sheets recognizes this budget link, we will need to connect this budget to the data HQ, just like we connect the.

Month end reporting package to the data HQ so that the full budget worksheet from the budget build workbook can be pulled into the column.

And rows that you see down at the bottom of this budget worksheet. So we'll just give it a couple more seconds to process.

Go ahead and hit wait. Okay. So it's now recognizing the budget build and this is going to show an error to start because we need to allow access.

So, so once we allow access, add those permissions. Again, we're pushing the limits of Google Sheets is processing powers here.

So it'll take, you know, 30 seconds to a minute for the. The permissions to be added. You may also need to re add permissions.

Nope. Okay. Looks like it worked there. And once the budget build has been connected to the. New data HQ. We will see the individual line items from the budget build.

Get pulled into our data HQ workbook. . Mm. Mm. Still taking its time, but you can see that the numbers are now being pulled in here.

And so this is our budget. We obviously need to fine tune the budget build assumptions, which, like I said, I will cover in a different video, but now that the budget is.

In the data HQ, just to show you how it flows through into our month and walk through tab. The month and walk through is looking at the client data HQ.

And it is pulling the budget numbers from the budget worksheet of the data HQ and populating those in here so that as we go forward, the budget will be compared against the access.

Actuals and will be able to calculate variance against that budget. Last thing I'm going to do here is I'm going to show how.

The budget versus actuals dashboard is calculated and, and just in the same way. This dashboard is automatically populated based on the client data HQ.

So I have a new client data HQ that I need to connect. So once this finishes loading, just go ahead.

And close out the data HQ to clear out some of the processing. We're eventually going to see the ref error here which means that the new data HQ is being, being recognized by the Google Sheet and that we can allow access to connect to it.

Come on. Go ahead and close that out and just read. Load it, try to shake it loose. There we go.

It's a nice little tip for whenever Google sheets gets gummed up. By the way, if you just close out the workbook and reopen it, it tends to get things working again.

Okay. So now we can allow access. And as soon as these permissions are added. There we go, alright. Permissions are added.

And now you can see the budget being pulled in to, our budget versus actuals dashboard. And we have actuals through three thirty one, but we only have our, or, but we have our budget through the end of the year.

So as I. Our actuals get updated inside of our data HQ. We will be able to compare actuals first budget.

And then we can go to the budget versus actuals detail tab to view individual line item. And at a much deeper level of granularity here.

So let me just make sure that we've got things pulling in correctly. So two 24 is our budget revenue number for April.

And if we look at our. Or full budget tab in the budget build, you can see it to 24. 163 81.

Yes. Okay. So everything is being pulled from the budget build through the data HQ into the budget versus actually. We're going to be able to get a new budget.

So we're going to be able to get a new budget build dashboard. And this is a pretty quick process that we'll be able to stand up in order to give our clients that budget that they can track against.

Hope this was helpful. Please let me know if any questions. Thank you.