Build Your Budget Tool in FileMaker

QuickBooks is a great accounting software for most small to mid-size companies. It’s relatively cheap, easy to learn, and if you use classes, departments, and jobs can produce truly insightful financial reports. The thing QuickBooks doesn’t do well is budgets. I learned this while trying to manage the budgets of multiple teams as a finance exec. We could never find a tool that would give us what we needed when we needed. Some came close, but then they wouldn’t integrate with QuickBooks requiring the accounting staff to reenter information.

For budgets to be useful to an organization, they need to be structured to make the most sense to the people running them. And that is usually at odds with how the accounting department wants things organized. Accounting and QuickBooks are structured around GL accounts and producing Income Statements and Balance Sheets for paying taxes and making management decisions. Budgets should be structured around making day-to-day decisions to keep operations on target.

The QuickBooks budgeting tool only allows you to use the chart of accounts to make budget lines. It also does a terrible job of tracking changes you make to the budget. For example, let’s say you find out mid-year your rent has to go up by 20%. You can’t move things around while still keeping the original budget for reference. You have to make an entirely new budget.

Work Seamlessly with QuickBooks

Some years ago, FileMaker Pro came up in my search when I was trying to find a budget solution. It proved to be an excelled tool for building budgets that make sense for operations while seamlessly integrating into QuickBooks. Our budget integration tool reported information into five basic columns:

1. Original Budget
2. Adjustments Made
3. Adjusted Budget
4. Submitted Expenses
5. Left to Spend

Original Budget

The Original Budget, is just that, the budget approved at the start of the project, or time frame. It doesn’t change and is always available for reference. Because it is built outside of QuickBooks, the budget lines can be anything the person running the budget wants them to be. These lines will still report into QuickBooks the way the accounting department needs them to be. 

For example, let’s say you give your company’s receptionist a budget for ordering food at the beginning of the year. To make things easy for themselves, the receptionist wants to track in distinct categories:

• Monthly all staff meetings
• Weekly executive breakfasts
• Quarterly all staff + customer wine and cheese

With lines for:

• Catering
• Grocery
• Alcohol

Having this level of detail helps the receptionist make decisions and keep track of their spending across each kind of meeting. But the accounting department does not want all of this information coming into the GL. The budget tool can map the lines in the budget to as many or as few GL accounts as necessary.

Adjustments Made & Adjusted Budget Column

Continuing with our receptionist and their food budget, let’s say the executive team decides to skip weekly breakfasts for the month of July while most of the team is on vacation. Our receptionist would like to allocate the funds from those meals to the quarterly staff meeting. So that anyone looking at the budget will see the decision, we built our budget tool to track changes in the Adjustments Made column.

Our receptionist would subtract from the Weekly Executive Breakfasts lines and then add to the Quarterly All Staff lines. The amounts added and subtracted would be represented in the Adjustments Made column. Because we feel it’s important to track the reasoning behind a decision, we built a budget tool with a decision log. 

The decision log allows our receptionist to detail why they made the change, and it is date stamped for easy reference later. This will come in handy when at the end of the year, when no one can remember why you moved things around. It is an invaluable reference when trying to make budgets for the next year or the next project. 

The third column in our budget is the Adjusted Budget Column. This column shows the results the adjustments had on the budget and totals it up just like the Original Budget column. This is a quick and easy way to ensure you are staying on budget overall. Our receptionist can review the Adjusted Budget Column, and know that even after they have moved funds around, they will still be within the approved budget total for the year.

Submitted Expenses

Because we have built our budget tool to work seamlessly with QuickBooks, our receptionist will enter invoices and expenses she has received directly into the budget. When a caterer sends an invoice, our receptionist will record it. Each line in our budget will have a little plus icon. When the icon is pressed a screen will pop up, allowing the vendor name, invoice number, invoice date, and amount owed to be entered. We also like to attach a PDF of the invoice for easy reference. 

Once this information has been saved, the amount will be added to anything previously entered and totaled in the Submitted Expenses column. This gives our receptionist real-time reporting on their budget. They’ll always know how much they have left to spend because our next column will update automatically.

Left to Spend 

Left to Spend column is: Adjusted Budget – Submitted Expenses = Left to Spend

This information will update whenever an invoice or expense is entered into the budget. Our receptionist will never have to wait on accounting to let them know how much money they have left in their budget.

Now that our receptionist has added some invoices into their budget, the accounting department can pull the information into QuickBooks with a click of a button. The budget tool has a mapping interface with QuickBooks. This allows the accounting department to define how the invoice from budgets will be recorded in QuickBooks. For example, transactions from our receptionists budget can be pulled into QuickBooks with the following account and class information:

• Account: Food & Beverage
• Class: Reception

All the appropriate vendor invoice information will be pulled in as well, without the accounting department having to enter by hand.

Defining Permissions 

Another great feature of FileMaker gives our budget tool a robust interface for defining permissions. You don’t want to give every person in your organization access to QuickBooks, but you can give anyone who needs it access to your budget tool.

Let’s say our receptionist gets a little behind entering invoices. You can let an intern help get all of those entered without the intern having access to all your company budgets. By setting permissions in FileMaker, you can easily define what users can see and do within the budget tool. A manager can access all the budgets they are responsible for, and an intern can only see the lines in which they can enter invoices and expenses.

Better Business Decisions

Using FileMaker to build a budgeting tool is a great way to give your operations team members the information they need to make better decisions. While we used a fairly simple example here, our budgeting tool was used to manage projects with budgets as high as a million dollars with hundreds of transactions. With every column updating in real time as information was entered, the operations teams never had to wait on accounting to generate a report for them. They knew in real time the impact of the decisions they were making to our bottom line.

While there are out of the box budgeting tools that can be purchased, none offered the flexibility we needed to run our organization. The investment we made in building a custom tool paid back in record time as our profitability went up project by project. Having real time budget information improves decision making and empowers employees to do their best work.