Are you wanting to set up a budget for your company but don’t want to spend any money on budgeting software?
As a finance director at a publicly traded company, I can tell you that you can do your company budgets using a combination of QuickBooks and Excel.
I love watching videos that show you how to do things. To see me actually go through the following company budgeting process and earn free CPE for CPAs while you’re at it, check out this link.
If you would rather learn the process without a video, check out the below steps. If you know, at least roughly, how to use Excel, then you can do this.
Download the budget templates in the following links:
Customize the Departmental Expense File template to fit your general ledger account setup. For each high-level account group you should have a tab in this file. Also, you should have a separate tab for headcount.
Make a copy of the departmental expense file template for each of your departments, and rename each file to be that of each department (defined as classes in QuickBooks). For example, you might have the following files for your company.
Prepare a detail of all of the transactions that have posted to your general ledger. Don't worry. This isn't as hard as it sounds. Just watch the video referenced above, and pause it if you need to catch up. Or, you can follow the below steps if you have a pretty good handle on Excel already.
Paste the vendor names from the detail that you created in step 4 into the spaces that are allowed for vendors in your budget file that you downloaded in step 1 and renamed in step 3.
Populate the headcount tab, if you have employees, based on your latest payroll register.
Send each departmental expense file to the applicable department head making sure that the individuals receiving these files are allowed to see the payroll information that each file contains.
Go through the expenses with each department head to confirm that you are in agreement with the planned spend. This step is way oversimplified here. In fact, at a larger company, this is where you will spend most of your budgeting time. If you want to see the budgeting process that a lot publicly traded companies follow, then check out this post.
Build out the consolidated budget roll-up file maintained at the above noted link.
Link in the department expense reports to the budgeted roll up file. I have found that v-lookups work the best.
Upload your budgets back into QuickBooks.
In QB: File menu > Utilities, Export, Lists. Checkmark Budget. Save this .iif file.
Open it using Excel and update it, including changing the date.
In the QB file: File menu > Utilities, Import, Lists. Browse to that .iif file.
or find a video on YouTube about it.
A couple of tips to keep in mind for uploading your budget from Excel back into QuickBooks.
Sit Back And Relax. Now, it's time to enjoy the fruits of your labor. Your funds will be prudently spent on the things that matter most to you. That is, if you can make people stay within these budgets.
About the author
Patrick Truesdell is a CPA working at a publicly traded biotech company as the Director of SEC Reporting and Budgeting and has been a QuickBooks poweruser for over ten years. Patrick is also the founder of the Public Company Community, which provides Free CPE for CPAs and tons of useful articles about budgeting and SEC Reporting.
This is a terrific and very thoughtful article. I love the visuals and how you broke down the process step-by-step. You do a great job of making what seems to be an insurmountable challenge feel like a manageable task (especially step 4, everyone, read step 4!).
I was working on an article specifically about the QB budgeting feature. I think I can really get to the meat of what that piece will be about here - the in-product budget tool works well if (a) you have complete financials from previous years and (b) you tracked everything, including assets and expenses, into QuickBooks because you can use historical data from previous years to create new budgets in subsequent years.
It may feel like a bit of up-front labor getting everything into QuickBooks, but I can't even begin to tell folks how much more effective their financial forecasting will be with complete and accurate data. All this work getting your financials together to get an accurate view of your business' health is in service to so many functions in QuickBooks, not just budgeting (think a "stress-less" tax season).
Gif for how to create a budget from historicals in about 15 seconds:
Great work, Patrick. I've enjoyed some of your other work on your blog as well!
Let’s talk about your business – “Growing your business”
Let’s talk about QuickBooks – “Do more with QuickBooks”
Ask a product question – “Questions about QuickBooks”
In QuickBooks desktop (this article), you can upload your budgets back into QuickBooks. You can't do that in QuickBooks Online.
In QuickBooks desktop, you can also create your budgets for Balance Sheet and Profit and Loss whereas, in QuickBooks Online, the budget is for Profit and Loss only.
In QuickBooks desktop, they also have:
- Cash Flow Projector
- P&L Budget Performance
- Budget vs. Actual Graph
Hope QBO will have the same (or better) valuable tools some day.
Thank you for your input on QBDT Budgeting features, I am less familiar with it - there's so much to the Desktop version.
In your experience, how useful is it to use the data in QuickBooks to create a true, accurate business budget?
Great question @vpcontroller . I can speak from experience on this matter. QuickBooks, along with even the large ERPs, can store the budget numbers that are created. But they are not good tools for actually creating the budgeted amounts in the first place.
1) You need to be able to easily copy forward amounts. For example rent will be $3,000 a month for the next three years.
2) You need to have multiple details under each general ledger account. For example you will spend $300 a month for LinkedIn and $500 a month for Indeed. Both of these transactions will go into the the general ledger account #7010 Recruiting, but you can only enter one amount into QuickBooks for the budgeted amount for #7010. So, when the actuals start posting next year and recruiting is higher or lower than budget, you won't know whether it's LinkedIn driving this variance or Indeed.
3) You generally need collaboration on the budget process. In fact, if you have multiple departments, collaboration is pretty much mandatory. That means that you are going to need to set up access for every budget collaborator if you want them to get into QuickBooks to see the budget. That can be expensive, time consuming, and QuickBooks, while awsome, has some form of a learning curve for non-accountants.
4) You need to track the various versions of your budget, so you can see pass#1, pass#2, etc. QuickBooks, as far as I know, can only store one version per year.
QuickBooks, and other uber expensive ERPs, are perfectly fine for storing budgeted amounts per GL and reporting budget to actuals on a general ledger level basis. But these programs are not designed for a company to be able to actually create budgets. You need Excel, Adaptive Insights, or Host Analytics.
I want to focus back down to users - the store owner who does accounting for 30 min in the evenings, the accounting student who moonlights as bookkeeper, the fellow who just became an LLC and needs to create a quick budget to get an idea of where to go based on the last 6 months of excel data he has (because that's the purpose of a budget).
@ptruesdell, I like where you're going with this and your passion/insight on the topic is obvious. Do you think these tools are accessible to folks who are ultra-conscious of time and spend? Or are the tools available "good enough" to get a basic idea?
I am always concerned about simplicity and connecting folks with tools that are a good fit. As someone who loves data, I can get into this more robust process and going down the rabbit hole, but is it for everyone?
I think that the built in budgeting tools for QuickBooks are great for a company that is just starting out their budgeting process. So many times, I see companies say that budgeting is too hard, and then they don't do any budgeting at all. I think the built in QuickBooks tools, like in the meme that you displayed above, are awsome for those business owners that don't have a lot of Excel experience or the time or desire to put together a really elaborate budget for their companies.
Exactly. I am sure @vpcontroller would agree with this assessment. That's the knowledge gap that holds back many folks who aren't comfortable with or have trouble learning technology, whether that means computers or cameras or anything really.
If it looks too difficult, sounds too difficult, they just won't do it. They ignore it completely. Then the challenges pile up and up. Before they know it, they've completely overlooked the task and become overwhelmed by the results.
How best to bridge this gap? You have a lot of experience. I'd love your perspective on how we can encourage more business owners to do budgets which help them be more successful, whether that solution is new technology or a new approach to technology.