ERPs and Budgeting—What the Excel?!

by | Feb 7, 2020 | Budgeting, Data Analysis, Finance and Banking, Ms Excel

How 2 Persistent Conditions Define Budget Planning in ERPs

There are 2 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 2 Persistent Things:

1. Excel is used as a key tool in all planning exercises.
2. ERP systems are basic.

While making significant advances with other capabilities, ERPs remain—at best—limitedly capable in addressing planning requirements; indeed, they have hardly advanced in respect to how they operated years ago, and can address only basic budgeting/planning needs.

Not surprisingly, “Persistent Thing #2” reinforces “Persistent Thing #1”—and so it makes sense to start with asking, How do ERP systems tackle budgeting? (And, after all, it’s typically only budgeting that they do tackle, not other planning exercises.) Essentially 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 necessarily, at least for smaller firms that do not have complicated, customized requirements. They—these in-built ERP capabilities—enforce a rigid, systematic approach, almost certainly for a very small user group (often a single user) whereby plan numbers end up in the statutory ERP system, from which budget plan numbers can be reported on versus Actuals.

That said, where things really become problematic is in medium-to-large-to-enterprise firms that have complex budgeting and other 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, capabilities like multi-versioning in the same model (multiple budget and forecast versions); accommodation of larger groups of contributors who are connected dynamically (so calculated results can be seen/changed/commented upon immediately); the capability to quickly incorporate any new kind of “what if” modeling “adds” into a current model (a prospective new product, for example), and; the flexibility to 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 limitlessly adjustable internal formula capabilities that drive plan calculations (for budget/plan growth drivers, interest rates, forex rates, etc.); customizable forms and formatting, and; changeable security rules, so that contributors can access only the plan data for which they are responsible.

Now is a good time to turn to the persistence of Excel in budgeting applications (aka Persistent Condition #1). Probably we should add “ubiquity”:  for the smallest firms, provided they do any budgeting at all, Excel is their budgeting application. But more to the point in relation to most ERP systems, 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, as per above, for smaller firms it doubtless is.

But for larger 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’s 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.

Furthermore, 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—or is a remedy even necessary? 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:

  1. Address all the limitations of ERP-based budgeting;
  2. Handle the most massively complex planning requirements (i.e., not just budgeting—corporate objectives like continuous forecasting);
  3. 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
  4. 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 in this blog: Can we Really Stop Using Excel? Or, reach PARIS directly about streamlining between your ERP and Excel for Planning.

 

New call-to-action
New Call-to-action

Categories

Archives

Enter your email address:

Delivered by FeedBurner