Excel is a great tool and over 80% of companies are using Excel as their budgeting solution. However, in our 20+ years of experience working with financial systems, we’ve seen some challenges in using Excel. So we’ve compiled our Top 5 and what you can to do about them.
1) Budget templates are accountant-minded
Most people that budget templates are accountants and they think like accountants. However, most people participating in the budget process and contributing to the budget are not accountants and don’t think the same way. They don’t think in terms of debits and credits or GL accounts – they think in terms of what they need to spend to get the job done, and not how that spending flushes out on a financial statement. The problem is that most input templates are designed to facilitate use by accountants.
Since the templates are not designed with the user in mind, the risk is that users will create their own spreadsheet and will use it to calculate the total costs based on all of their trips and then just copy the aggregated numbers into the main template. You have created extra work for the contributor, you have increased the risk of bad data due to the user creating calculations and the extra step of cutting and pasting the data. Plus that spreadsheet is only known about by the person who created it and nobody else can use that data for budget versus actual comparisons later on.
2) The data validation function in Excel is not being used
Apart from the merging of the data, one of the most tedious aspects of doing the budget is confirming if the numbers provided by contributors are reasonable. Did that user mean to type in $1,000,000 for new sales or did they really mean to type in $100,000? Is your payroll for January really going to be $40,000 or is it $400,000? Why are you forecasting hiring “#DIV/0!” new employees for January and “bob$$%12” for June?
A huge amount of time is wasted tracking down “typos” in the budget process. However, there is a solution that can help you out. Excel has a built in data validation function that allows you to create rules for reasonableness for different fields. If you know that certain fields should never be below or above certain thresholds, then you can put those rules in place and catch the issues up front. You can also ensure that values entered are dollars when you want dollars and text when you want text.
3) Budget contributors aren’t given any guidance
Most organizations create an input template in Excel and simply email it out to the contributors with a subject line entitled something like “here is your budget form” and a due date in the body and pretty much nothing else. They are then surprised when they get flooded with questions and comments about the process. Probably the quickest return on investment you can make during the budgeting cycle is to take a couple of extra minutes and write up instructions on how to use the particular input template in question, as well as any budget guidance you can provide such as allowable increases or mandatory decreases in certain categories. Be sure to use terminology that the end-users understand. If you are using Excel, make the first tab an Instructions tab. It won’t stop the questions, confusion and complaints entirely, but it will help reduce them.
4) The divide by 12 mentality mindset is the norm
So you take the time to prepare a budget input template and give people spaces to put in monthly numbers and what do they do? They come up in their heads what they are going to spend during the year, and divide that amount equally by twelve and put one-twelfth into each cell. The problem with this, is that with a few exceptions such as rents, leases and insurance payments, expenses and revenue are seldom the same even amount month by month. Yet the budget is. So you go to compare budget to actual and what do you get – big variances each month and you fall back on the “it will work itself out next month” excuse. But how do you plan for cash flow this way? Yes, at the end of the year the actuals might match the budget, but unfortunately you ran out of cash in September.
5) The team does not collaborate
Nobody should work on their budget in a vacuum. First of all, it is hard to breathe and secondly, you get substandard data. Contributors and reviewers should work together on the budget as it is being developed. Contributors should collaborate across different areas, sharing knowledge and even sharing data where it makes sense. By doing the budget in this fashion there should be fewer surprises. The review process should go smoother and the quality of the data should increase. So, try to include time in your budget cycle for people to meet and share knowledge as much as possible.
As a side note, try to avoid using email as your collaboration tool. All the emails flying back and forth and the dreaded “reply all” can quickly overwhelm inboxes. Instead, take a look at some different collaboration platforms. SharePoint is one option as it has the ability to host discussion boards. There is also Yammer which is a private social network just for your organization which, as a bonus, has a free version which is very robust.
If one or more of these budgeting mistakes sound familiar, you MUST download our free eBook!