Why Forecasting with Excel won't let your Organization Excel

Nicola van Rijn

Forecasting with Excel is riddled with problems. It does not produce reliable forecasts because data quickly gets outdated, and the knowledge about the spreadsheet is often held by only one person within the organization. In many cases, the spreadsheet can only handle the scenarios for which it was originally built.

The result? "Overworked employees or a drain on your finance department" says Edgar de Wit of XLReporting. "Or, and this is much more distressing, organizations just stop forecasting because it takes too much time and effort." A shame, says the financial expert because there are online forecasting solutions on the market that overcome the drawbacks of Excel as a forecasting tool. "Those solutions enable you to generate solid predictions, what-if scenarios, and analyses and reporting of your data with much less effort in a shorter amount of time."

Patchwork and quick-fixes

Edgar has seen it often enough: a bulky Excel sheet full of values, hidden logic, formulas and references. Previously, he was regularly hired as an interim controller when a financial employee had left an organization or suffered from burnout. "It was then up to me to figure out these types of forecasting files" smiles Edgar. "I understand this use of Excel because it is an incredibly flexible and practical tool that every finance person understands to some level. So if you get a request from your boss to deliver a certain report or make a forecast, it is enticing to start an empty worksheet and build a model that piles up the requested data."

There is nothing wrong with that for one-off analysis, argues the financial expert. The problems only start when such a spreadsheet becomes "the system" and is continually expanding and changed. "Before you know it, you have a huge file with dozens of worksheets and all sorts of references, formulas, and hard-coded data" he says. "Keeping a grip on such a file and all those cross-references is impossible. Moreover, errors are bound to creep in, and they are challenging to track down (or audit) because of all the references in these files."

Manual work = errors

Yet, Edgar still encounters these situations regularly. "Excel is just not suitable for recurring forecasting." According to him, it is often used because many organizations believe their processes and working methods are unique, and therefore need a tailor-made tool. "Yes, every organization is unique, but usually not necessarily in their processes and working methods. Yet they all reinvent the wheel piece by piece in their own Excel sheet." By definition, such sheets contain outdated information because the numbers are copied from other systems. "An endless cycle of cutting, copying, and pasting" says Edgar. "Tedious manual work that is also very error-prone. Surely highly skilled financials can think of more challenging work."

Time for more challenging work

Much time and effort goes into devising, developing, calculating and checking Excel sheets that are used for forecasting. This leaves little time for analyzing and interpreting the outcomes and results. "Quite often, someone from the finance department has spent an entire weekend sweating to extract a report from their Excel files, which is then viewed by management for 5 seconds before being pushed aside. Nothing is more demotivating than that," Edgar argues. Moreover, it is a waste to spend so much time on customization that can also be obtained and deployed in a standardized way within a forecasting solution. Such a solution leaves finance professionals with more time for more challenging work. "Surely it is much more challenging for a financial to analyze and report on investment issues, growth strategies and how the organization can respond to opportunities and threats?"

Separating data from logic

When forecasting becomes too time-consuming, or when an organization looses grip on the models due to the departure or work pressure of a finance professional, it often falls back to annual budgets. Unfortunately, this merely looks back at a company's performance and provides little information to look forward. With an online solution built specifically for forecasting, data and logic are kept separate. This makes it possible to re-use data and visualize information differently.

"Such a solution puts the data at the center from which you can then derive relevant information, reports, and forecasts," says Edgar. "As a result, you only have one version of the truth, you always work with up-to-date numbers, and each department can run the reports or analyses that are relevant to them." Various models and logic can be built on that data. "Updating a model produces different results but does not change the data. As opposed to what often happens in Excel because, for example, a hard value is entered somewhere. Making a change on a worksheet can cause your entire Excel sheet to collapse. And you are facing the gruesome task of finding that particular bug."

Investment quickly recouped

So, forecasting software has many advantages over Excel, the most important being that almost everyone in the organization (even the least digitally proficient employee) can use it. "You can set security and validation controls, and ensure that data inputs meet specific requirements. You can also control that only the right people have access rights to certain data and information. Something you could never manage with a huge Excel file on a shared drive", says Edgar. Of course, there are costs associated with a software solution. However, the return on investment is high, as an organization can quickly calculate scenarios more easily, create reports effortlessly, and get a fast and solid insight into which way it is heading or should go.

Wondering how forecasting software can benefit your organization? Request a personal demo of XLReporting.