Highlighted
Established Member

Budgeting with QuickBooks

Cover Page.jpgAre 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.

 

Step 1

Download the budget templates in the following links:

Departmental Expense File

Consolidated Budget Rollup

Resulting GL Detail Pivot Table File Developed In the Course

  

Step 2

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.

 

Step 3

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.

 

picture2.png

 

Step 4

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.

 

  • Run the P&L (Reports > Company & Financial > Profit and Loss Standard
  • Select last year as the date range
  • Double click the Total Expense Line
  • Customize > uncheck “Clr” > uncheck “Split” > uncheck “Debit” > uncheck “Credit” > uncheck “Balance” > check “Amount”
  • Export this to Excel
  • In Excel, format the file so that the account is copied down the left column and remove all totals and subtotals. I do this by utilizing filters.
  • Make sure your columns all have some type of header and run a pivot table. Your pivot table should look something like the following.

picture3.png 

 

Step 5

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.

 

Step 6

Populate the headcount tab, if you have employees, based on your latest payroll register.

 

Step 7

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.

 

Step 8

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.

 

Step 9

Build out the consolidated budget roll-up file maintained at the above noted link.

 

Step 10

Link in the department expense reports to the budgeted roll up file.  I have found that v-lookups work the best.

 

Step 11

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.

 

  • QuickBooks is very specific about the file format being uploaded. What I have found is that you can’t save it as an .xls and then go back and save it as an .iif.  If QuickBooks doesn’t like your file format, you may need to download a fresh template, and paste the values in.
  • You can only have one value for each GL-Class combination. QuickBooks will keep the last one that appears in the .iif file.

picture4.jpg 

 

Step 12

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.

 

8 Comments
Established Member

Re: Budgeting with QuickBooks

@JamesOng  I would be interested to hear your insight on this budgeting in QuickBooks article.

Community Product Host

Re: Budgeting with QuickBooks

Hey @ptruesdell,

 

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:

 

Budgets-from-actual-data.gif

 

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

Frequent Contributor

Re: Budgeting with 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:
- Forecast
- Cash Flow Projector
- P&L Budget Performance
- Budget vs. Actual Graph

 

Hope QBO will have the same (or better) valuable tools some day.

Community Product Host

Re: Budgeting with QuickBooks

@vpcontroller

 

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?

 

 

Established Member

Re: Budgeting with QuickBooks

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.

 

Reasons why

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.

 

Summary.

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.

Community Product Host

Re: Budgeting with QuickBooks

@ptruesdell and @vpcontroller, great thoughts all around.

 

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?

Established Member

Re: Budgeting with QuickBooks

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.

Community Product Host

Re: Budgeting with QuickBooks

@ptruesdell,

 

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


 Explore the QB Community > Home > Product Help > Talk about your business