From over a decade of consulting with clients, we have seen thousands of Excel spreadsheets – some good, some bad and some ugly! Here are the 9 most common pitfalls we’ve encountered with Excel models – and how they can be overcome with a Corporate Performance Management (CPM) solution.
1. No error prone spreadsheets
Error prone spreadsheets are a recurrent problem not limited to finance teams undertaking planning and reporting activities. Even the most carefully constructed Excel models with in-built “checks and balances” can have errors.
Different people get involved, the owner of the Excel model goes on holidays (or resigns!) or the business has a restructure. These factors can dilute the reliability of the models over time and errors become more commonplace and prominent.
With a well architected CPM solution, the robustness is there from the start and is maintained throughout.
2. Keeping models in sync with the business
“Business as usual” activities can mean new GL accounts, or other dimensions such as products or cost centres, are added to source systems regularly as business needs dictate.
Unfortunately, the Excel models remain in their static state. The finance team must then continually rework formulas or create additional tabs to ensure the model remains in sync and everything totals correctly.
When a CPM solution is used, new GL accounts and dimensions are integrated into the model in an automated fashion and internal logic will ensure everything is always in balance – there is no reliance on formulas to calculate totals.
When formulas and calculations are required, they are managed in a central area and defined once – not in multiple workbooks or tabs.
3. In-memory engine with reliable calculations, in real-time
We have all been there – waiting minutes for Excel workbooks to be saved or to recalculate. We once had a client tell us she would press “refresh”, then get a cup of tea because it was more productive than looking at a spinning wheel.
With a CPM solution, an in-memory engine calculates everything in real time and ensures updates are quickly refreshed throughout the model, irrespective of the number of calculations and dependencies that may exist.
4. Integration with source systems
Source systems (particularly general ledger) really need to integrate with your Excel models, whether they be for reporting or planning purposes. To have a pre-populated plan with baseline data or a seamlessly prepared 3 + 9 reforecast, actual general ledger data is necessary.
Recognising this fact, finance teams either rekey data manually or create “integration” through references (eg, vlookups) to “data dumps” from source systems. The latter maybe an acceptable workaround but is again prone to error and can quickly denigrate the performance of the model.
Alternatively, a CPM solution will provide the ability to integrate with any number of source systems so that data refreshes can be made on demand and can be completed in seconds.
5. Collaboration and security
Planning is powerful when many stakeholders can plan at the same time, sharing the same models. Unfortunately, Excel allows for single use access only, so copies of Excel models are often distributed across the business before being re-consolidated, or users are continually competing to access the same workbook.
A CPM solution not only allows users to collaborate on the same models at the same time; security can be put in place to ensure users only access the models and departments they are responsible for.
With all inputs consolidating and dependencies flowing through the model in real time, the benefits of collaboration are quickly realised.
6. Expanded what-if capability
Again, when it comes to Excel models, it is difficult to cut a new planning version. When there are multiple linked sheets and workbooks, hiving off a new version can be a big exercise.
Once a few versions have been laboriously created, it is exceedingly difficult to compare those versions quickly and easily, with key metrics spread across potentially dozens of workbooks.
A CPM solution will enable the creation of a new planning version in seconds, so efforts are focused on running different scenarios and tweaking key drivers to understand sensitivities. Better still, differences between scenarios can be quickly reported and analysed.
7. Full auditability and version control
We have all been there. Your boss points out that the number has inexplicably changed on the budget or forecast. Then you scramble around in various Excel files trying to trace formulas and understand linkages to get to the source of the problem.
Unfortunately, Excel provides no way of indicating when a specific number was changed (other than the date/time stamp on the whole file) or who did it, so you are left none the wiser.
A CPM solution will track who made a change, what change was made, and when it was made. This is not for just the last change; it stores a history of all changes. The best thing is, a CPM solution will also enable you to lock versions so the risk of last-minute changes is removed.
8. Ability to drill down and understand context
It is quite possible that a well-built Excel model may provide the necessary sophistication to undertake organisational planning. However, once that budget or forecast is complete, it is necessary to easily explore drivers and assumptions that sit behind the numbers in a budget or forecast.
In Excel, the only choice is to “trace” formulas and calculations, but this becomes complicated when there are many layers to the calculations sourced from multiple sheets or workbooks.
A CPM solution will address this by providing the ability to seamlessly drill down on a number to explore key drivers and assumptions from which that number was derived. This drill through is enabled through multiple layers until the source variable or input sheet has been identified, so the context is never lost.
9. Visibility of workflows, contributors, deadlines
In larger organisations where many stakeholders are involved in planning, it is very challenging to manage the process and understand where things are at.
You may have emailed 30 worksheets to cost centre managers, but have they commenced their plan? Are they waiting on other people? Have they completed it but forgot to send it back to you? Of course, Excel will not help with any of this.
With many contributors, tight deadlines and many potential iterations, visibility in the process is critical.
A CPM solution will enable the creation of workflows and dependencies between contributors. It will provide a snapshot of who has not started, who is working on it, and who has submitted their budgets or forecasts. This allows you better visibility and oversight on the whole planning process.
Finance teams are choosing planning software over Excel
While Excel has been the most widely used corporate planning tool for years, it is clear why many finance teams are making the switch to Corporate Performance Management solutions.
By removing the limitations and challenges posed from using Excel for planning and reporting purposes, you free up the team’s time and skills to be focused on more value-adding activities.
You open up opportunities to budget, forecast and report better. Planning becomes more accurate, flexible and fast – and finance can shift its focus from fixing spreadsheets to strategic analysis and business partnering.
The above has been just a brief introduction as to how CPM solutions solve everyday Excel challenges for finance teams and their organisations. If you would like to see an example of a CPM solution in action, have a look at these videos of Workday Adaptive Planning.
QMetrix consultants are finance professionals, with the majority holding professional accounting qualifications (CA/CPA). We can provide a demo of fit-for-purpose Corporate Performance Management software and make a recommendation on which solution might suit your unique business. Contact us to enquire.