From spreadsheets to Anaplan

Have you ever worked on a team where spreadsheets are used everywhere and for everything? Have you seen multiple versions of a company’s financial forecast flowing through email from one person to another, back and forth? Have you validated versions of the same file to capture any possible accidental or unwanted changes?

This is the daily routine for many financial specialists, data analysts, and reporting gurus. Nowadays, despite the massive development of business intelligence and data analysis tools, spreadsheet technologies are still commonly used across different businesses. Based on 2018 benchmarking, the Accounting & Finance Function report (Robert Half/FERF), 63% of US executives answered that Excel is still their company’s primary budgeting and planning tool.

There are several important areas where spreadsheet technologies are most inefficient:

• Security
• Data integrity
• Access management
• Calculation efficiency
• Multidimensional modeling
• Data visualization
• Real-time collaboration
• Scalability

Now, let me tell you a story about a spreadsheet. A spreadsheet used for providing financial analysis and status of a company’s budget. The structure of the file was as follows:

• One main input/calculation sheet (~10-15k of rows, ~400-800 columns), mostly simple calculations without lookups
• Two additional input/calculation sheets (~500 rows, ~50 columns each)
• One dictionary/mapping sheet, used in formulas across the file
• One main reporting sheet (~200 rows, ~200 columns), many lookups collecting data from other sheets
• Two additional reporting sheets (~50 rows, ~30 columns), with dynamic selections, recalculating the content based on chosen values
• Three technical sheets for data export tables 
• VBA scripts preparing data packs in ZIP (multiple data packs for different offices)

Methodology of use:

• There were four instances of the file, each instance for a different business unit.
• Each instance managed by a different financial analyst.
• Input data coming from external systems (copy & paste and referencing external files).
• Cooperation of file owners to validate data and eliminate human errors.
• Monthly consolidation of results for management.

This file was the main tool for providing required calculations and reports for the team, built element by element, as required by the business. Not overcomplicated, its size at mid-year year was approximately 50Mb (xlsx), and the processing/recalculation time was so long that it was highly recommended to recalculate values only after inputs were completed. As the file was randomly crashing, the team decided to continue using the file for the remainder of the year by removing past calculations.

Due to data confidentiality and the large audience consuming the file, security rules were applied to the file and its usage. The file could be stored only in a secured space, that was password protected and distributed only to an approved list of people.

File outputs were sent to data recipients as copies of tables or imported into external systems as data feeds.

Enter Anaplan

After getting the appropriate agreements, a proposal for Anaplan transformation was accepted, resulting in the following list of accomplishments:

• All hierarchies were connected with the master data source eliminating human errors and data mismatches.
• All data imports were standardized and automated improving the process clarity and streamlining it significantly.
• All calculations were transformed into multi-dimensional hyper block/Polaris calculations enhancing the speed and eliminating unexpected software crashes.
• Reporting UI was designed and modernized making the solution more user-friendly and improving the overall visual and functional impression of the process.
• VBA scripts were replaced by actions and processes letting non-programmers manage them efficiently.
• Workspace/model/module design was built to reflect the process prerequisites taking into consideration future scalability.
• User management was prepared to provide full flexibility in defining audience groups and to be able to dynamically react to changes in the current process.

All designed with the highest regard for the user story specifications.

Using features of Anaplan, the team incorporated several additional elements of the process, which enhanced the overall usability of the solution and streamlined the process end to end. This transformation took a few months, during which members of the team, with support from Anaplan, were trained in maintaining the solution as well as in further development. However, this was only a start of a journey that continues today.

Based on this initial success story, the company established its own Anaplan CoE, Certified in-house Master Anaplanners, and continues to reap benefits from the many Anaplan advantages transforming new processes that are compatible with the entire Anaplan-based infrastructure.

It was quite the transformation! Is your story similar? Leave a comment!