Excel is a snapshot of the past. BI helps you see the future

Profile picture for user Matthias Thurner By Matthias Thurner October 1, 2018
Doing forecasts and planning in Excel slows you down - integrated BI and CPM are the modern way to do FP&A, writes Unit4's Matthias Thurner

Young business man looks out of full height window over river cityscape © GaudiLab - shutterstock
Successful financial planning and analytics (FP&A) helps organizations make better, more informed decisions about the future. Finance teams help keep the business on a stable and profitable trajectory by blending the actuals of past performance and then making assumptions about achievable business outcomes in the future. But getting it right is becoming more and more difficult.

The problem is, understanding what worked (or didn’t work) last year, last quarter, or last month is only half the battle. The pace of change in the economy and the rise of digital business models means last year’s conditions can rapidly lose relevance with the passage of time. The assumptions that forecasts are based on, can go quickly out of date, meaning the budgets and plans associated with them are formulated on a distorted view of what’s really happening in markets.

Over-reliance on spreadsheets exacerbates the problem. Excel has been with us since 1985 and while spreadsheets will likely always be part of the operational picture in finance, they remain a tool best suited for capturing the details of what’s already happened. With business data volumes on the rise, spreadsheets’ main answer to more data is ‘create more spreadsheets’.

Your actuals, historical data, and comparison scenarios all have to be kept in separate sheets. Actuals may have to be gathered and input by multiple users in different geographies and different time zones. Multiple companies, entities, and product lines also cause spreadsheets to multiply. That means a deluge of cells, rows and columns to manage and more individuals to collaborate with in the management of information.

The spreadsheets used in an FP&A process also tend to contain multiple internal links. Linked spreadsheet files make distribution not only complex to manage but difficult to execute, creating more manual processes that need time and attention – sometimes spent testing links rather than managing data. The FP&A process requires multiple versions of actual, historical, and forecast scenario data, making management of the spreadsheets and links an onerous task requiring loads of work.

It all adds up to a lot of cumulative effort and hours spent simply gathering, troubleshooting and bringing together numbers from the period just past. The future, so to speak, has to wait.

All tomorrow’s potential outcomes

With all the data businesses are now capturing, demands to analyze and draw insights from that data are rising too. The inflexibility and two-dimensional and non-hierarchical nature of spreadsheet-based models is simply not up to expectations shaped in the era of big data.

Finance teams need forecasting models that can scale and ensure security and data quality. If the data they use lives in spreadsheets, it will be spread across thousands of individual cells rather than a central database. And if complexity of a model increases, the spreadsheet’s internal logic may become comprehensible only to the person who built it.

That’s why any future view of financial outcomes has to unravel the spaghetti of spreadsheet-based models and data capture, and augment or even replace them with business intelligence (BI) that runs within the ERP system and in combination with corporate performance management (CPM) software.

The advantages of using BI for FP&A begin with the fact that it draws data directly from the underlying ERP. Users across the business, regardless of geography, department, or time zone, work from a single source of truth – always with the most up-to-date data. With spreadsheets data is untethered, it can be overwritten, and version control is notoriously difficult.

Traditional BI applications do have limitations related to their emphasis on historical information. They can help identify and define issues, but the insights they uncover may well imply more questions. An FP&A-ready business intelligence tool will enable the creation of forecasting models, understanding scenarios that can utilise all the business information available in the finance system.

Business intelligence then needs to integrate with corporate performance management (CPM) systems that enable finance teams to create multi-dimensional models, see the hidden relationships between numbers, assign predictive scores for individual entities like customers, SKUs, employees and more.

CPM tools work from one integrated database to store actual, historical, budget and forecast data all in one place. When BI is added they provide a unified FP&A environment that strengthens the controllability of the process, adding confidence in the numbers while reducing the time and resources needed.

Finance teams can create and manage forecast scenarios, simulate the potential impact of future events, then project those impacts down to different levels of analysis; for example, by product, entity, geography, customer/client, or business unit.

With CPM and BI integrated in one solution, planning for future outcomes becomes a more granular, and yet, simpler and faster process, for everyone involved. With results and decisions of significantly higher quality.