ERPs and Budgeting—What the Excel?!
How two Persistent Conditions Define Budget Planning in Enterprise Resource Planning (ERP) systems
There are two remarkably persistent conditions in the world of Finance Technology that concern budgeting and ERPs. It’s surprising, because the complexities that Finance departments must address have increased dramatically over time. Ask a Finance professional involved in FP&A, performance management, budgeting, forecasting or any exercise that includes data planning (with corresponding analytics and reporting) and you will almost certainly validate the following Two Persistent Things:
- Excel is used as a key tool in all planning exercises.
- ERP systems are basic.
While making significant advances with other capabilities, ERPs remain, at best, poorly equipped to address today’s planning requirements; indeed, they have hardly advanced in respect to how they operated years ago, and can address only the most basic budgeting and planning needs.
Not surprisingly, “Persistent Thing #2” reinforces “Persistent Thing #1”, so, it makes sense to start with asking, How do ERP systems tackle budgeting?
By either allowing:
(a) single-transaction manual entry directly into a budget version in ERP tables, or
(b) via a bulk file export, aka copy, of a set of numbers into a spreadsheet(s), and; then data entry into the spreadsheet(s); followed by a bulk import(s) into the budget version.
Is there a problem with these methods?
Not for smaller firms that do not have complicated, customized requirements. The built in ERP capabilities enforce a rigid, systematic approach designed for a very small user group (often a single user). Plan numbers end up in the statutory ERP system, from which they can be reported versus the actual results.
Where things become problematic is in medium-to-large enterprises that have complex planning requirements. The very rigidity of the ERP-based budget version—which typically begins as a wholesale copy of a prior period budget or actual set of accounts—quickly thwarts the effort required of nimble plan-modeling that must be responsive to a sizable group of plan contributors. The budget modeling capabilities that larger firms require, and the planning applications that go beyond simple budgeting, cannot be supported in the overall ERP- based budget superstructure or its limited set of embedded calculation/other functionalities.
What kind of capabilities do large firms need to accomplish their complex planning needs?
From a “macro” viewpoint, many in planning roles look for the following capabilities in an industry strength planning system:
- Multi-versioning – the ability to have multiple versions in the same model (multiple budget and forecast versions);
- Team-based collaboration - accommodation of larger groups of contributors who are connected dynamically (so calculated results can be seen/changed/commented upon immediately);
- What if - the capability to quickly incorporate any new kind of “what if” modeling “adds” into a current model (a prospective new product, for example),
- Sub-models – the ability flexibly add entirely new component areas (financing, HR, inventory, etc.) that feed into the larger planning model application.
From a “micro” (embedded functionalities) perspective: larger firms need capabilities like:
- Centralized formula capability - limitlessly adjustable internal formula capabilities that drive plan calculations (for budget/plan growth drivers, interest rates, forex rates, etc.);
- Forms and Formatting - customizable forms and formatting,
- Security – Security that can be implemented at various levels within a model so that all users have role appropriate visibility to data and functionality.
Now is a good time to turn to the persistence of Excel in budgeting applications (aka Persistent Condition #1). For the smallest firms, Excel is their budgeting application. Furthermore, Excel is an elemental component of how they provision their most robust budgeting capabilities. ERPs allow users to “Export [the budget] to Excel.” This is touted as a benefit…and, for smaller firms it is.
But for medium and larger sized firms the implications, and the real-world practice, involves: export of spreadsheet upon spreadsheet, whether different versions of the single budget application, and exports of “parsed” templates to multiple contributors. These contributors must send back their disconnected entries for re-import into the budget: what concern, then, must budget managers have that, say, a single row or column has been moved…or a previously filled-in template has been sent back in error?
There is concern in the very fact that these disconnected spreadsheets are…disconnected.
How much math is done “to the side” in Excel (or in new tabs or even in different spreadsheets) to arrive at the final submission of plan numbers? As a consequence, the very core of the budget logic begins a new life—separate and disconnected—in the unstructured world of Excel.
Moreover, as every spreadsheet user (and every firm using Excel—which means all firms) knows, the probability of consequential errors multiplies with the number of users, the sequence of submissions, the amount of calculations done “to the side,” and the number of cells in which these calculations are made.
In sum, the persistent use of Excel for budgeting is, for all the risks involved, basically a “given” for firms of all sizes: for smaller firms a spreadsheet-based budget may suffice; for medium-to-large firms using standard ERP systems, Excel is part and parcel of the best solution they have.
Is there a remedy for this? If the “problem” is overuse of spreadsheets, then is the solution really to abandon Excel in favor of a third-party solution?
Those products that “replace” Excel likely address some of the other limitations in how ERPs do budgeting. Then again, who in the Finance world wants to abandon Excel? (Stage directions: deafening silence ensues here…)
What is needed is a middle way, that can simultaneously:
- Address all the limitations of ERP-based budgeting;
- Handle the most massively complex planning requirements (i.e., not just budgeting—corporate objectives like continuous forecasting);
- Provide a dynamic data flow—no exports and imports, please—both from and to the statutory/ERP environment, as well as to BI applications (think: Power BI, Tableau, Qlik); and
- Include the everyday, good ol’ spreadsheet in the mix, as a dynamic front end itself.
This ERP + Excel problem tends to be quite invisible in business today. Many professionals just accept the methods that have always been, because no matter how “turn of the [last] century,” some very good things persist! We’d like to shine a light on what’s happening, and let professionals know that there is better way around all of this “What the Excel?!”
If you like this topic, read more here: Can we Really Stop Using Excel?
Identify your path to CFO success by taking our CFO Readiness Assessmentᵀᴹ.
For the most up to date and relevant accounting, finance, treasury and leadership headlines all in one place subscribe to The Balanced Digest.
Follow us on Linkedin!