Thursday, October 10, 2013

Internal project budgets - Streamlined!

I became so excited the other night when I took a multi-spreadsheet THING that my client was referring to as a 'marketing budget' and forced it into QuickBooks. This thing was not pretty. The budget had it's own set of items specific to the short term project at hand, and since QB would not track the budget the way that the marketing team needed to track it, there was a constant need to update the spreadsheet manually and hope that nothing got missed.

Before I give you the rundown of how I achieved success, let me just point out that this is not intended to be instructional for a basic user to repeat the process. I am going to make some assumptions that most people reading this are experienced enough in QB to get the point, and that you will contact me with any questions before attempting this at home!

The budget that I received had the short term marketing project broken down into 6 categories. QuickBooks offers me a budget by class (and this client does have a Marketing class set up), but we needed to see the expenses line by line more specifically than the account level offered by a P&L by class.

Create a Budget 'Customer'
To start, I created a Customer named 'Marketing Budget'. This is the only way that I could get QuickBooks to recognize my budget in a way that would provide me with an estimate vs. actual report.

Build your budget item list

Next, I created an item called 'Marketing Budget Expense'. This is my parent item for everything else in the budget. Under that, I created items for the 6 categories on the budget and numbered them as the initial outline was written by the marketing team. Though the original list was not numbered exactly how I would have set it up, there is no sense forcing your client to change things that don't really matter. We are here to make their lives easier, remember?

Each category item then became the parent for all of the line items in it's group. I was able to map each line item to the proper expense account and the hierarchy was lovely! 

Enter your budget 'Estimate'
To begin the process of tracking the budget, I went to my client ('Marketing Budget') and entered an estimate with all of my budget items and amounts. This is what QB will use to produce your Estimate vs Actuals report.

Record budget expenses as Job Costs
Next I was able to record expenses by assigning them to the proper items and went back to add items in to previous expenses. Every expense must be recorded as an item and the proper budget (or 'customer') name assigned (in this case it was the Marketing Budget customer) for the report to come out accurate.

Everything was going GREAT, until I hit an expense reimbursement check...

So... what do we do when a budget expense/receipt qualifies as ONE item but needs to be broken up into TWO or more accounts? Well I'm glad you asked... 

My real-life example was running into an expense reimbursement. The reimbursement needed to go under the 'Travel' line item in the marketing budget, but the sum of the expenses needed to be broken out into multiple accounts (not just the default travel account set up with the item). For this, we simply enter the total on the items side to the Travel item (so that it maps to the correct item on our budget report), then we CREDIT the expense account associated with the item by recording the amount as a negative on the expenses tab. Lastly, we break it out by DEBITING each individual expense account the amount that needs to assigned by recording positive amounts on the expense tab. 

At this point, you are either thoroughly confused, nodding because you've done this before, or smiling uncontrollably because of the time saving tips you've just added to your knowledge base. 

But wait, there's more!

None of this means anything until you run your report! This is the easy part :-)

Customize your budget report
Run an 'Estimates vs. Actuals' report and customize to filter for your specific job (here it was 'Marketing Budget'). Next, filter the items to only show your budget items. If you set up your item hierarchy properly, this should be a quick step. Customize your report title according to what your report is about, and if you would like you can uncheck the last 3 columns (I did that for my budget report because the columns were irrelevant for my purposes).


The last step is to send to your client so that they can be impressed that you fixed something in one hour that they have been working on for three months. Woo hoo!