4 Common Problems in Budgeting & Forecasting with Excel & How to Fix Them
Updated: Nov 16, 2022
There are over 6,500 languages spoken in the world today, three of which have over one billion speakers. In third place is Mandarin, with 1.117 billion speakers, in second place is English, with 1.132 billion speakers, and in first place… is Microsoft Excel, with 1.2 billion users worldwide.
It is for this reason, among others, that Excel is such a great tool across a variety of professions. Excel is a universally well understood application, and is particularly useful for finance professionals when creating forecasts and budgets. Finance teams may apply Excel to a multitude of tasks, including financial modeling, visualization, data analysis, data storage, textual and quantitative data, and regression/statistical analysis.
Although Excel does offer these advantages when budgeting and forecasting, it has been fairly pointed out that Excel does have certain disadvantages. Manually trudging through spreadsheets is time consuming and prone to human error, and Excel does not offer excellent control or data traceability to its users.
Taking these disadvantages into account does not mean FP&A professionals should give up Excel altogether. However, in order to get more out of budgeting and forecasting in Excel (and in general), when considering the drawbacks of Excel, other tools must be used to make up for Excel’s disadvantages. It is critical to be aware of the commonly encountered problems in budgeting and forecasting with Excel, what forecasting tools can complement Excel use, and execute the tasks Excel can’t.
Commonly Encountered Problems When Budgeting & Forecasting with Excel
1. Lack of Efficiency:
Due to the current digitalization, most companies are dealing with a new reality, in which they have to exercise a very high standard of time management in order to save as much time as possible, as time is the only resource no one can afford to waste. This specifically stresses a finance team when budgeting and forecasting; they both are time sensitive tasks.
Excel is one of the most time-consuming aspects of business that employees are facing every day. “Excel is slow” is a common complaint for more and more employees. Excel is not used nor intended to tackle large data sets. Looking back to the roots of Excel history, Excel’s framework was not made for big data processing. That said, Excel isn’t just slow when collecting and analyzing large data sets, but is inherently error prone with them.
2. Broken Templates & Human Error Issues:
This is the biggest issue with forecasting in Excel. As good as the members of a finance team may be, they are only human, hence prone to error. Mistakes are easy to make in Excel, especially when importing data or when using complex or unfamiliar spreadsheets. And the features of Excel – like hiding data – can cause huge issues, as it did for Barclay’s in 2008. Going hand in hand with errors and mistakes is the time it takes for knowledgeable accounting teams to review and correct problems. Due to the amount of problems encountered with broken Excel templates, organizations must set aside time for teams to review important work, or run the risk of sharing incorrect financial data.
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.
Even the largest companies with a lot of workers and careful workflow are not immune to infrequent permutations of numbers, calculation errors, and typing mistakes. It is already proved that almost 90% of all Excel spreadsheets contain errors. Because of the badly thought-out use of Excel software, many companies struggle with a range of issues. A particularly disastrous example in 2020 was Public Health England having a dramatic problem caused by an Excel mistake. The problem caused by the Excel mistake led to 16,000 coronavirus cases going unreported in England, due to that single Excel error.
3. Excel as a Single-User Tool
Excel is a single-user tool. If more than one person is needed to participate in a spreadsheet-based application, they must be given a copy of the spreadsheet, and then you must wait for them to finish with it before you (or someone else) can continue. This can be painful for any situation where collaboration is required. Although it is possible to send out individual spreadsheets, you then are up against the tedious task of consolidating all of these spreadsheets when they come back. There are also certain assumptions, rules or calculations that you don’t want everyone to have access to. Although it is possible to protect these certain elements, the spreadsheet is 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.
4. Excel lacks Data Security
Excel accounting gives a wrong sense of security with its password protection. If there is no audit trail in Excel, fraud is very hard to prevent. The numbers are easily changed and if verification is not done independently, there is no way to catch the mistake. And the real danger is that Excel spreadsheets do not supply any audit trail or any other proper security. This can lead to inaccurate or false information in documents, which in turn brings the company to huge risks and the danger of fraud.
Addressing Excel Disadvantages with the Right Tool
Budgeting is a time sensitive task that must be done accurately and in a secure manner, in which the datasets can be smoothly accessed by all members of a finance team. Although Excel is the language of finance professionals, it should be used alongside another tool which can make up for Excel’s drawbacks. FP&A software offers the following remedies to all four of the aforementioned issues with Excel:
Enterprise grade security, hence reducing the risk of fraud
Automation of processes in order to eliminate inefficiencies and inaccuracies
Unification and accessibility of cross-departmental data
Any finance team can maximize the productivity of their budgeting and forecasting processes through integrating FP&A software and Excel use. There are even FP&A software which are intended to work in conjunction with Excel, which provides an even greater advantage.