Intelligent Financial Planning Solution enabled by SAP HANA

I had mentioned some time back, my journey towards an Intelligent Financial Planning Solution enabled by SAP HANA and how this can help clients achieve highest level of automation using standard BPC and SAC functionality.

Read the case study to learn more on how to enable your business with real time reporting and planning capabilities using Financial Planning & Analytics (FP&A) solution from SAP with SAP BW on HANA, BPC Embedded along with S/4HANA and Reporting with AFO while keeping it ready to be integrated with SAP Analytics Cloud capabilities.

Overview:

One of the large media corporation had been using the Financial Planning & Analytics solution leveraging the earlier implementation of SAP BW, BPC Classic based on ECC and other Non-SAP systems as the source systems. Over a period of years the landscape started becoming more complex and most of the processes were manual and the users had to wait anywhere from a day to two days to be able to report on the planning data which ended up causing reconciliation issues and offline activities for the business to get through the validation, due to which there were multiple models created within BPC and data manually loaded to complete the consolidation and the remaining reporting activities not waiting for the siloed based approach.

Below is a quick summary of the typical user challenges from the earlier solution:

Note: The names and images used are for representative purpose and no intent to cause any conflict of interest and are only for illustration purpose of the real roles and problems the client had.

Client Vision:

The client rather needed a solution which can consolidate all of these standalone systems into one data warehouse driven solution which can allow them both to have optimized data loading process when it comes to their actuals and a streamlined solution which can provide real time planning related data into their Balance Sheet and P&L reports without having to wait for the overnight loads to be completed while being able to leverage the best of reporting which SAP can offer both with Analysis for Office Excel and SAP Analytics Cloud.

Project Scope:

The requirement was to build an integrated and scalable budgeting, forecasting, consolidation and reporting solution. A key outcome for this project was to help the client minimize the amount of manual processes and data loads across their finance systems as these were causing delays in their processes and frequent system downtime. It was also essential to move from manual offline workings to using SAP BPC based solution to hold more of the information behind their forecast and budget numbers leading to an integrated way of working.

Below is a quick summary of various scenarios which needed to be addressed as part of the project:

For each of the scenarios above, the ask was to put together multiple templates both for forecast, budgeting and for consolidation which can address all required activities performed by the business thus helping the data be prepared for the reporting purposes.

Forecast / Budgeting process:

-???????Input templates for Cost Center & HR

-???????Input templates for Capital project planning

-???????Input templates for Programme Projects

-???????Input templates for P&L

-???????Input templates for Balance Sheet

-???????Automated rules to balance the balance sheet to handle the offsetting for adjustments or data inputs made

-???????Automated rules in place to carry out AUC settlement

-???????Automated rules in place to carry out workflow of project submission all the way to project approval for forecast ready status

-???????Automated rules in place for HR based calculations

-???????Automated rules for auto calculation and adjustment taking into consideration the commitments and deriving the estimated time to completion

-???????Master Data on the fly

-???????Templates for adjustments to be made for the opening and closing balance

-???????Templates for balance carry forward generation both for periodic and cumulative data set

-???????Automated provisioning to allow users to enter data around periodic for I&E and cumulative for balance sheet accounts but still provide a consistent data set for consolidation

Reporting:

-???????Reports to provide Balance Sheet, Income Statement and Cash Flow Statement

-???????Reports to be able to compare Actuals vs Planning data real time

-???????Comparative P&L (MTD, QTD, YTD and variances)

-???????Master data scenarios

-???????Reports across multiple business scenarios

Project Challenges

-???????The users wanted to have the flexibility of being able to enter the I&E based data in periodic format and Balance Sheet related accounts in cumulative format

-???????Every posting would need a specific business calculation to be carried out within the planning templates real time to always keep the balance sheet balanced

-???????Flexibility around master data creation on the fly instead of having dependency with the IT team for every planning scenario to be performed

-???????Real time view of actuals vs planning data at a consolidated view for preparing towards the journals ahead of time rather than waiting for the close of all the planning related inputs

-???????Automating estimated time of completion for projects as real time processing compared to over night calculation

Project Solution:

Based on the functional and non-functional requirements provided, we at IBM came up with FOAK adoption leveraging SAP BW on HANA (BW/4HANA was in its starting stage but the challenge thrown at us was to ensure every object used in the latest version of BW on HANA was compatible with BW/4HANA to make sure the future upgrade to BW/4HANA would be a technical upgrade rather than a functional upgrade with object conversion needed) BPC Embedded 10.1 for both planning and enterprise consolidation along with SAP BI Analysis for Office Excel (A4O) and WebIntelligence along with SAP Analytics Cloud (limited examples) while integrating it with multiple SAP and Non-SAP solutions (Including S/4HANA for group consolidation data requirement).

Below is a quick summary of the Enterprise Architecture both conceptual and physical architecture models leveraged:

Let’s now review the approach taken

-???????LSA++ based patterns were put together ensuring the BW/4HANA based simplification list has been adhered to for making sure the objects implemented were compatible with BW/4HANA as well.

-???????Had to put together planning functions along with function modules to support the master data on the fly capability for the end user while ensuring the consistency checks are in place for the values created and to avoid any potential lock out in the templates

-???????Evaluated the “Default Save” option given the restriction when trying to use default planning sequence option which would be enabled for all planning sequences on the model and not just limited to the changed data but to all the data applicable on the selection defined within the planning filter.

-???????Reporting to support both IT defined reports and self service based reporting as well by providing a “Super Query” to be able to reduce the overall reports from 3000+ to 400+ ensuring there is no duplication of the reports just to support different scenarios.

The novelty of the solution being:

-???????Allow users to input I&E related data periodically and Balance Sheet level data as cumulative.

-???????Allow users to generate Master data on the fly to support the business planning activities not having to wait for IT to create the master data but still have a strong governance around what values can be generated.

-???????Delta enablement of the records changed by the business users, and given the standard planning function cannot process only records changed (default logic across the entire model is currently allowed by out of box functionality) creating a custom approach which can based on the delta flag set will only process those records changed allowing

-???????the entire AUC/Cost Centre/Project settlement live ensuring business can see the impact downstream not having to wait for overnight job schedule for immediate reconciliation.

-???????auto balancing of the balance sheet ensuring at any given point in time, the business knows the impact on the cash flow and any required journals to be posted.

-???????Rollup of data from HR into Cost Centre for further roll up into profit centre for a higher level aggregation for overall financial budgeting & planning data status with a click of a button.

-???????Allow users to selectively load the data into consolidation post validating against multiple business rules and data quality checks to avoid any issues and delay of month end with a click of a button not having to technically know how to access & trigger in the backend.

-???????Roll up of the periodic data on the fly while loading into Consolidation especially both I&E and Balance Sheet data to be at Cumulative level.

-???????“Super Query” concept with pre-calculated KPIs across different hierarchies to ensure the users don’t have to rely on canned reports for month end financial packages to be prepared. The users are now able to drag and drop different KPIs and views needed to deliver 60+ views with a single AFO provided.

-???????Intuitive customized dashboards both in LUMIRA (to be converted to SAC) & Webi to support the business aspiration towards integrated “What if” scenarios.

-???????Trigger the approval workflow for project approvals from the initial submission validation (on who can submit vs with what inputs it can be submitted vs ensuring the same is locked during the time when the approval process is ongoing or when approved).

-???????Have implemented custom control table approach which ensures there is a complete workflow end to end triggered with all controls and checks put in place.

-???????A global approval template with 8 status selections based on the selection a different planning sequence processes to be triggered off along with different control tables downstream to be updated.

-???????Extensive VB code integrated with AFO for Excel based functions provided by SAP to achieve the “Scenario-based” processing based on the user input providing real time validations as well.

-???????There are about 4600+ projects on a daily basis for which the “Estimated time to complete” needs to be re-calculated based on different attributes along with the remaining cost to be distributed based on the initial forecast vs actuals spent vs commitments made.

-???????Given the time to complete processing for all 4600 projects could be about 3 business days due to different planning scenarios to be processed, parallelized the approach with auto selection of the project list along with characteristic relationship and planning locks to get the process completed with auto deployment of scaled process chain count (depending on the number of projects as per auto selection the process chain count can scale down and scale up).

-???????This brought down the overall processing to 3 hours from 3 business days.

-???????Multiple assets built as part of the project to automate and optimize the effort involved

-???????Auto monitoring of the control tables for progress made on the process chains along with auto triggering of the process chains

-???????Able to move the planning locks configuration across the landscape (standard functionality needs it manually moved across the landscape)

-???????Inactive object checker in the landscape post moving any transports

-???????Lookup or pattern identification for hardcoded values used

In this white paper, we will specifically focus on the approach taken towards delta determination which helped with the capability to enable all of the above functional scenarios providing business a solution where they can post data and expect real time calculation and roll up into higher aggregation level of data reducing the timeline of planning cycle closure related dependencies.

In a typical planning scenario, there are multiple activities performed by the business or by the IT COE

-???????Planning data input from front end: Based on offline planning performed, users enter the data directly into the planning input templates or through mass data upload which then gets saved back into the BW on HANA objects

-???????Based on type of object selected, the data is overwritten when not using of the cube type which would mean for a given data intersection when data is entered by the business the data gets handled as cumulative entry. If the backend object selected is cube type then it ends being periodic in the system

-???????Selective processing of changed data from the backend objects

-???????Based on the data entered by the users in the input template you would need to process the data and have subsequent entries being posted

-???????This currently is not a standard option provided by BPC and hence would need options to be considered

-???????Examples being, contra entries posted for funding model and other areas

-???????Group processing of data

-???????This would mean irrespective of the data entered, for the same selection you would need group processing of data to be performed such as actualization and so forth

-???????Decision criteria for whether to go with selective processing or for group processing

-???????Evaluation would be based on if the data to be processed is irrespective of what user posts / changes

Given the above scenarios, we will quickly summarize what is the general behavior and various options evaluated

But to achieve what was set out as the requirement, the above will not be sufficient and hence the below options were considered

Option 1: An SAP API by leveraging the enhancement spot being used

Option 2: Use Characteristic Relationship to set the initial set of records changed by the user for change and then process only those records as per business logic agreed upon

Option 3: Use a backup query within the input template and use calculations in Macro to update the backup query based on inputs made and then save both the initial data and the changed data at the same time

Based on the analysis performed, Option 1 is not viable due to the native sql code to be written and is to modify the SAP standard API directly.?Option 3 as well double’s the effort involved as every input query requiring the delta processing would need a duplicate query with the keyfigure updating back into the ADSO.

Hence option 2 has been adopted where the delta value based on conditional terms when it needs to be enabled is set to Y and then the subsequent planning function filters with delta = Y to pick up only the specific changed records. By going ahead with option 2 we have been able to ensure the data processed is as per the criteria defined in characteristic relationship and with the required selection filter for the logic to be applied. Using InfoCube type ADSO for planning and with delta caused an issue and hence had to use Direct update ADSO for the same.

Business Benefits:

Business Related:

-???????Replacement of existing BW, BPC (Microsoft version) with multiple standalone instances by a single robust solution provides business a consistent, real time planning capability.

-???????Planning data for both user input and report consumption (Real time from overnight / weekly schedule) à Users now not only are able to enter the plan data but are also able to carry out the AUC & Project based settlements along with Project ETC validation real time.

-???????Through out the month end, the balance sheet is balanced for entries made highlighting the impact on Group Consolidation real time along with 20x report performance improvement.

-???????Use advanced analytics-based reporting functionality along with self service “Super Queries” to significantly improve project planning and HR based allocations for any potential issues along with extended cash split into further level real time.

-???????All templates in compliance with accessibility standards.

-???????Master data on the fly capability for business.

IT Related:

-???????70% reduction in month end support activities, IT no more has to support four different systems and is all replaced by a single system with BW on HANA and BPC Embedded components along with best-in-class SAP AFO, LUMIRA & Webi for reporting (SAC on horizon with PoC completed).

-???????80% of the month end support is automated in terms of moving across BPC based configuration setting related changes which otherwise would have to be done manually, control table concept for monitoring multiple tables for potential dependency entries, 4500+ projects to be processed for ETC calculation by 10+ process chains with automated individual value selection.

-???????60% reduction of the BODS usage replaced by SDA for real time data consumption thus leading to reduction in IT support & in alignment with IT strategy.

-???????All data reconciliation issues in the earlier instance are resolved with a repeatable reconciliation process in place.

-???????Less impact on HANA usage by leveraging system wide virtual reporting process along with context-based planning sequence execution capability.

Human Empowerment:

-???????3000+ reports to 460+ reports post rationalization, and upon the system setup, further reduced to 260 Reports, 90+ AFO input templates.

-???????Business team now has “Super Queries” which are super charged version of the “Self Service” queries where along with standard functionality required for majority of the queries, there are also pre-calculated characteristics, keyfigures, so that the business can pick up them up and execute them.

-???????Manual processing around HR, External Cash, Cost Center along with year end schedule management are now streamlined with the automation put in place.

-???????Cutting-edge techniques to upskill IT resources and attract new talents.

-???????Direct access to data real time helps and motivates business to work on data quality and processes.

Usage of modern tools with ready to use data increase attractiveness of operational jobs.

In Summary,

What does the client users say about the solution,

Next Steps & Future readiness:

-???????As mentioned earlier in the solution section, BW on HANA based functionality compatible with BW/4HANA was leveraged to ensure in the future when BW/4HANA upgrade is to be performed, the effort is only as a technical upgrade.

-???????Manual input for the forecast version was optimized by copying the earlier forecast version or budget version as a starting point instead of entering the data all over for every account where more often than not there won’t be a change involved.

-???????As an enhancement, the plan is to use SAP Analytics Cloud planning component to generate the forecast and budget with more intelligent business planning functions through the predictive functionality (forecasting series) to provide a better version of initial version compared to just copy of the data from earlier version.

For any further questions or inputs about the solution and how we can help you on your journey towards an “Intelligent Financial Planning” solution please reach out to [email protected] .