Moving ERP from spreadsheets to IBM® Planning Analytics

Following our overview of our ICAEW presentation, we have selected some specific pain points to explore in more detail.

The first of these is the source data enterprise resource planning (ERP) used in regional business planning spreadsheets.

We are considering situations where the source of business planning data is various ERP systems and manual inputs from different areas of a business. In the case of Robertson Group, this data was accessed from disparate spreadsheets within a Citrix environment. As part of our implementation with the business, this approach has now been replaced with IBM® Cloud and a centralised Planning Analytics model.

This has had an impact on multiple elements of the planning process:

Loading data

Then: ERP data was loaded periodically into the business planning sheets by the responsible user for every region. This was not only time consuming but also carried an inherent risk from the manual nature of the loading process and having tabular data which could be corrupted or changed open within a spreadsheet.

Now: IBM® Planning Analytics has solved this through using open database connectivity (ODC), which automates the flow of ERP data into predefined, secure cubes. This process can be triggered manually or scheduled to run at set times. The cubes then immediately feed data into the rest of the planning model where system rules are in place or, alternatively, flows can be on demand using application processes.

Structure change

Then: The components forming the basis of structures such as contracts within regions were held in the ERP systems. Changes to show new contracts required replication within all of the business planning sheets within a workbook.

Now: IBM® Planning Analytics uses its extract transform load (ETL) tool to add new contracts to a single dimension which is used throughout the business planning model. Processes were configured to alert the relevant stakeholders of new elements, such as additional ledger accounts coming in from the ERP holding the financial accounting data.

Consolidation

Then: Amalgamation of multiple regions into a group spreadsheet was extremely difficult and time consuming.

Now: By design, IBM® Planning Analytics holds a ‘rolled up’ company structure in one dimension used by the entire planning model. If a company moves between rollups it now takes seconds to change and every part of the system reliant on that original dimension is subsequently updated.

A final key benefit of implementing IBM® Planning Analytics for this segment of the planning process is the ability to mine the source data – making a link between dependent sheets to find the underlying information. Planning Analytics can drill back from any data point to the source ERP system, giving the auditor a seamless experience. These drills can be opened as cube views until the source data cube is reached, this then opens an ERP table.

Contact us today to discover how Spitfire Analytics can help alleviate the pain points in your business.