The company had an immediate need for a process improvement, accounting and Excel skills due to the sudden and unexpected departure of a key person. This key employee, of several decades, was responsible for the FAS 143 Asset Retirement Obligations accounting which encompassed hundreds of $ Millions in future liabilities.
There were 30+ interrelated spreadsheets that comprised the accounting system. However the spreadsheets were not linked. Data had been “cut and pasted” between spreadsheets and workbooks. Thus the tactical methodology of operating the spreadsheet system was lost when the key employee was lost.
The company’s ARO accounting process resulted from at least 3 different company mergers. Accounting methodologies and legacy data was inconsistent. As a result, there had been recent discoveries of individual liability variances in excess of $10 Million each.
The current IT system did not support the ARO accounting process. More than 350 separate projects spanning 20+ years were maintained on several series of enormous spreadsheets.
Process Improvement / Efficiency & Effectiveness:
The monthly accounting process took about one man week. It involved several data collection and validation steps between the engineering and accounting departments. Some calculations were very complex, (i.e. a 3 dimensional matrix allocation), while others involved 20 or more steps. Because of the process complexity and month-end time requirements, accounting was performed one month in arrears.
As with most consulting projects, we quickly assessed the project risk and established our list of immediate deliverables. Audit risk related to the complicated FAS 143 accounting requirements necessitated a quick study of the accounting concepts and requirements.
Quarter end financial statements for publically traded companies endure extra scrutiny. Thus we needed to quickly recreate the current process and demonstrate the same level of confidence to which the client had become accustomed.
Through a series of interviews with coworkers and file searches we were able to reconstruct the current accounting process. Additionally we thoroughly tested and documented the current spreadsheet process which provided management with greater understanding and confidence.
Professional Financial Modeling Techniques:
We developed new MS Excel financial models using professional standards. For those familiar with such professional standards, we used our own proprietary standards similar to those suggested in the PricewaterhouseCoopers white paper “The Use of Spreadsheets: Considerations for Section 404 of the Sarbanes-Oxley Act.”
We developed the new model in accordance with the Professional Business Intelligence standards “How to audit proof your financial models.” Each workspace, workbook and spreadsheet contains explicit documentation of purpose, data inter-relationships and links. Self-auditing formula were employed within each spreadsheet, workbook and between all workbooks that made up the combined workspaces. Thus, despite incredible complexity, the modeling was quick and easy to audit and validate.
In all, more than 600, 000 formulas were used of which about 150,000 were automatic data validation and testing formula. As we continued to add to the model over the years, the number of formula grew to total more than 750,000 with approximately 188,000 providing automatic data validation.
BPM and Automation:
The initial business process contained two significant constraints. First, there was the accountants’ data collection from the engineers. Then there was the manual / visual allocation of costs from 350+ projects across a 5 dimensional matrix over a 20 year period.
An iterative data collection and reporting process required multiple interactions with the engineering group. During which the accountants performed a series of calculations and engaged in additional querying of the engineers.
This iterative data collection and reporting process was one of the system’s constraints. We applied lean manufacturing principles and eliminated the iterative process by building in automatic data validation with the data collection. Data testing algorithms provided instant feed-back to the engineers via colored cells and conditional formatting rules. Thus data collection and testing occurred only once and the iterative process that consumed so many man-hours was eliminated.
Allocation of costs is a necessary part of the FAS 143 layering process. It this case it involved a nearly impossible manual / visual allocation process for which each annual forecast all 350+ projects was subjected to 5 interrelated validation tests. A few last minute changes to a few project estimates could add several hours of processing time.
We were able to codify the allocation process within a MS Excel Smart Business Application that simultaneously assigned costs to layers and individual years for all the projects, layers, and years.
As a result of these two process improvements we reduced the process time from several days to a few hours. Consequently the accountants were able to make entries within the current month’s data and the one month lag on the financial reporting was eliminated.
Data Integrity and Accessibility:
At the beginning of the engagement, the accounting department’s primary concern was managing the Asset Retirement Obligation Accounting (FAS 143). After implementation of the data collection models, and cost allocation codification, compliance with Sarbanes Oxley was the next focus. The next step became current data integrity and maintenance of legacy project management data
Large individual project variances (in excess of $10 MM) had recently become evident. These large variances were due to some of the legacy project data that was inconsistent with current accounting practices.
Obviously, maintaining such a large ARO accounting reporting system within a series of business objects such as workbooks creates several of its own hazards and risks. To alleviate some of these risks, a database was developed to manage not only current data but to maintain the integrity and accessibility of the legacy data. Legacy data was collected from a variety of information systems including JD Edwards, COGNOS, and SAP.
First the legacy data was collected and assimilated into the database / it solution. Then business processes were developed to efficiently collect data and maintain the ARO database going forward. Detailed procedures, including automation, were institutionalized. A direct link with SAP was not available, thus a “soft link” was developed. Data was manually downloaded from SAP. Then the transformation and assimilation into the database was automated.
Enhanced Project Performance Management:
A benefit of the data collection models was instantaneous forecast variance analysis for project managers. The next step was to exploit the new real-time feedback and provide business analytics and reporting dashboards. A live link was established with the COGNOS purchasing system. We then developed and implemented a variety of KPI’s, forecasts and project reporting tools.
Expenditure analyses covered a variety of angles. Ad hoc / real-time, business analysis were available to all the project managers. Thus spending by project, vendor, expenditure type or service, was available in real-time. Budget variances and business opportunities could be explained quickly and easily.
The final results of our engagement were remarkable. We have continued to support our client and the applications we put into place.
- Quarter end, Year end and budget accounting completed on time without overtime or incident. KPMG audit completed without any issues.
- Accounting process time cut from one week to a few hours. Current month data utilized rather than one month in arrears.
- Data management via a database has improved long term data security and integrity, especially as compared prior series of minimal documentation spreadsheets.
- Communication and collaboration between engineers and accountants improved. Engineering department’s focus has shifted to value added activities due to better, user friendly and timely reports. Project variances are proactively identified.
- An unexpected benefit was that sales tax audit reports were provided from the combined legacy and current system data.
Yet, even with a great success we realize that our smart business applications that use MS Excel are not a permanent solution. Thus we have advised our client to seek a more robust, database based system or allow us to redesign the MS Excel portion as a database solution.
After an exhaustive search and analysis of 9 months, our client was not able to find any solutions that fully matched the accounting and project performance management capabilities of our applications. One large company offered an accounting solution only that cost in excess of $1 MM. Since our entire engagement cost less than $40,000 this was quickly dismissed as absurd.
At Professional Business Intelligence of Tampa Bay, Florida, we have been the CFO’s and Line Managers that use IT solutions. We’ve also developed lean and cheap tools that get the job done quickly. We’ve implemented ERP systems that allow growing companies to prosper too. Let us perform your business process evaluation and you’ll have peace of mind that you’re moving down the right path.