Embracing Excel Economics for Enterprise Planning
Author: Wayne Keinick, Product Manager – Business Planning, 3esi-Enersight
My name is Wayne Keinick and I’m the product manager for our integrated planning products here at 3esi-Enersight. I’ve spent the greater part of the past 15 years helping solution providers deliver and support planning software for oil companies across the globe. Time and time again, the companies I have had the pleasure of working with, have had planning process and workflows deeply connected with Excel, and time and time again, the problems they have faced have been tied directly or indirectly to that flexible tool. Now, I know what many of you might be thinking, “Once again, this commercial vendor is going to tell us all the horror stories of using excel and why we should switch to their product”. But that is not what I want to share today. This is something different.
There is a right way to leverage excel in your planning processes, and what I want to suggest is that the key may be to embrace excel, not replace it.
From Spreadsheets to Enterprise Systems and Back Again!
Let me begin by looking back at how the Excel situation all started.
In the beginning, end users built economic and financial models with the tools they had available to them: Spreadsheets: Excel, Lotus 123, Simple DB apps: Access and other proprietary systems.
The economists, who were the creators of these systems, not only owned the models, but had the ability to vet and modify them. One consequence of this approach was that corporate groups were reliant on these creators to run the models and hence, they would need to submit data to them.
Since these creators were building the models for internal purposes, the accuracy of the models relied on the creators and therefore the systems were limited to internal validation.
These Do-It-Yourself systems, had both favorable and unfavorable aspects. Some positive attributes were that the calculations were designed specifically for the task, they were transparent, easily understandable and they allowed for flexibility and adaptation. The users themselves could create formulas and ultimately model contracts and agreements. Figure 1 outlines the ups and downs of the ‘do it yourself’ systems.
The introduction of enterprise solutions did shore up some issues with spreadsheet models, but external and internal factors began to add pressure on these systems.
As the industry expanded to different geographic regions, new fiscal agreements were constantly added and negotiated. Existing and already complex regimes like what we have in North America were often subject to change. Within the most recent economic climate, the users responsible for calculation accuracy were having to run multiple scenarios. All calculations, budgets, and spending were put under a microscope. Under these circumstances, the pros of robust enterprise systems were becoming potentially outweighed by the cons.
A “Mixed Environment”
Some groups opted for a solution somewhere between their flexible spreadsheets and the commercial applications that best served them. With increasing financial pressure, the finance groups would lean towards models that they controlled and owned. In a similar fashion, the economists wanted visibility and control of their economic models so they too were often seen migrating back to the spreadsheet method.
The operations groups dealt with extremely large data sets and needed to exchange this data with partners. They did not necessarily want to build models themselves so they would naturally opt for the enterprise systems.
In this mixed environment, errors are bound to happen when transferring data to and from excel and commercial applications. So, the chaos that was associated with a free-form approach started to creep back in.
Combining the best of both worlds
The idea here is a simple one; keep the good, mitigate the bad.
With Excel, the power comes from flexibility and from the agility to model different economic situations and modify them when needed. It would be ideal however, to eliminate the need for manual data transfers between applications for rollup and consolidation. We also do not want to rely on the spreadsheet owners or experts because this can present delays and introduce risk.
With respect to the Enterprise system, we want to retain the positive attributes; centralized data in one place that is separate from the calculation model and eliminate ‘black box’ calculations. We want to fully understand what happens internally to generate the output of the system.
We now have a system that does the following:
- Provides centralized data that is secure and separate from calculation logic.
- Gives users the power and ability to modify the models when needed but does not sacrifice the security of the data and calculations.
- Allows users to modify models which means the calculations are now readable and auditable by all.
This all sounds great from a high level, but before we combine these two concepts, we need to consider some important points. Because of the flexibility of the excel models, we need to recognize the types of models that will be created.
The enterprise system will provide operations data, economic inputs, and other information to the spreadsheet model. The model itself may be something “simple” like a Tax/Royalty structure that is somewhat linear in nature. The spreadsheet may also be used to model more complex regimes like Production Sharing Contracts (PSCs), involving parallel type calculations. In either case, the enterprise system will need to consume outputs of the structure and the model itself will require security.
Other important considerations are where and how the excel models will be used in accordance to each company’s corporate hierarchy. Every level, from assets through to corporate has their own unique needs. At a jurisdiction level, such as individual states, we need specific calculations and royalty models need to be applied whether they be a tax royalty model, ring fence, or country wide.
Figure 5 represents how things work from a data handling/ flow and calculation perspective.
In the picture, above, the bigger box represents the enterprise system. The project input data, master data, and the project structure is managed by the enterprise system. At calculation time, the inputs are processed per excel defined calculations and the output is fed back to the system for use in reporting, charting and other output formats.
At the end of day, wen we carefully combine the flexibility and readability of excel models with the security and robustness of an enterprise system we can all the good stuff within each:
- Calculations are fit for purpose,
- It’s easy to use because everyone speaks “Excel-ese”,
- Our system is adaptable to fit any fiscal or economic model,
- Our calculations are transparent,
- Structure is given to projects over and above calculations,
- Security is enhanced around the calculations and data,
- Calculations can be vetted by experts and industry peer groups,
- And the enterprise solution has the potential to be expanded upon.
That’s all for this blog, thank you for reading!