Excel is undoubtedly the most ubiquitous business tool in existence. Just about everyone has it installed on their PC and it can be used for an enormous range of tasks within any organisation.
Such tasks include budgeting, forecasting and planning (often collectively referred to as Performance Management). Excel makes this real easy – design a template, send it out to a few managers, get them to input some numbers, get the spreadsheets back and consolidate the numbers for senior management. Simple.
However, when using Excel for Performance Management, Excel can quickly turn into your worst enemy. Many organisations start with some kind of Excel-based approach for their forecasting or budgeting activities, but this ‘simplicity’ can turn into a real Frankenstein’s Monster and can actually expose your business to quite a degree of risk.
A few years ago, a major consulting firm performed a study of over 10,000 spreadsheets used across a wide range of organisations, and showed that errors in those Excel-based applications affected the bottom-line by an average of 5%.
So how is it that we’re using (or misusing) Excel that causes so many problems? Here’s some reasons why:-
Excel Is Not Multi-User
Excel is a single-user tool. If you want more than one person to participate in a spreadsheet-based application, then you must give them a copy of the spreadsheet and wait for them to finish with it before you (or someone else) can continue. This is a real pain for any situation where collaboration is required. Sure, you can send out individual spreadsheets, but then you have the task of consolidating them all when they come back. And, of course, there are certain assumptions, rules or calculations that you don’t want everyone to have access to. Yes, it is possible to protect these certain elements, but it’s a one-size-fits-all; they either have access or they don’t. So what happens when you have differing and varying degrees of security and need to allow access to different parts of the spreadsheet? Typically, a consultant is hired in or a staff member who can dabble in VBA and macros is put on the job to modify the back-end code of the spreadsheet. However, it still remains a single user interface.
Distribution Problems
Ok, so you’re sending out individual spreadsheets. How? Emailing out a spreadsheet means it can end up literally anywhere. Not good if it contains confidential company information. If your spreadsheet is particularly large, emailing would become problematic. I recall a customer whose budget spreadsheet had grown to over 100MB and thus unwieldy for email. Therefore, it had to reside on the ‘Budget PC’ – a computer that budget contributors had to physically go to in order to update their budget details! In any event, you want to keep your data safe and secure, and that’s not going to happen while it resides in an Excel spreadsheet.
Multiple Spreadsheet Versions
Now you have lots of spreadsheets flying around, you have the added problem of managing which version is the most current. It’s a fact, contributors will often copy their spreadsheets to create different scenarios (best case, worst case, ‘what-if’, etc.) along with a ‘final’ version. Lots of versions can lead to further descent into ‘spreadsheet hell’.
Understand the Differences for Various Industries
You need to be prepared to deal with a variety of differences in industries. If you are working in the construction industry, the practices are going to be different. You need to understand the rules of construction budgeting and financial forecasting, which are covered in the infographic below.
Spreadsheets Can (and Will) Be Broken
So you’ve created a beautiful template with macros and formulae to make life easier for your contributors to enter their figures. The problem is that someone will ALWAYS break the template! They may insert a line or a column, or jam in some information somewhere where they shouldn’t. The net result is, your consolidation process is scotched until someone fixes the issues. I have seen problems like this add months to a budgeting cycle, where the template didn’t accommodate the requirements completely, or was confusing for the user to understand, and there were more than eighty users sending back broken spreadsheets.
Workflow
Once your spreadsheets have gone out, you have no means to track them or monitor the progress of your contributors. Typically, in larger organisations, it may be necessary to send a budget to a manager for approval. Excel has no native means by which to manage this. This means you need to keep some kind of list of who’s got what and where, and if the spreadsheet is fixed and ready to be consolidated.
These reasons add up to one conclusion. Excel is not your friend for budgeting and forecasting when you have larger numbers of contributors. There will come a point where you will spend more time fighting to get the numbers, rather than analysing them and gleaning valuable insights from them.
And that’s not good for you or your business and a primary reason why most companies using Excel have not made the necessary leap from an antiquated annual budget process to the essential competitive monthly forecasting process, which is more accurate, responsive and beneficial to running the business.
Summary
Excel is great because everyone understands it and it is so simple and quick to get things done. However, there does come a point where the limitations of Excel do start to show and reverse the productivity gains to burdens, and it is when you need to collaborate and collect data from many different contributors. There are dedicated budgeting and forecasting applications which excel at managing all the problems mentioned here, and some offer a fast ROI simply through the time and aggravation saved.
Many of them readily integrate with Business Intelligence tools, thereby offering improved reporting with dashboards and alerts.
If you are at the coalface of such budgeting issues, then your problem will be in convincing your management to invest in an appropriate modern budgeting tool. They may get their budgeting and variance reports without appreciating the trouble and strife required to achieve them. Therefore, your business case should focus on time and effort saved, along with the ability to respond rapidly to unexpected opportunities and threats.