P&L planning solution for a leading Swiss retail companyRetail & Consumer Products
In order to succeed in this competitive economic environment, the existing Excel-based tool needed to be redesigned. SAP BI-IP was chosen as the technological basis and EPM Data has been asked to develop and implement the new solution. The final solution vastly improved the company’s planning and forecasting capabilities.
challenge
Prior to the start of the project, the planning methodology has been redesigned by the controlling department. The new methodology heavily relied on flexible, MS Excel-based planning layouts with lots of interdependent formulas.
The main challenge was to develop a SAP BI-IP-based solution, that literally emulates the high-sophisticated offline templates while providing the additional benefits of SAP BI:
- High system performance and flexibility to enable “quick simulation runs”
The solution should be suitable to support several planning rounds. The performance needed to be high, large amounts of data records should be processed within seconds to gain user acceptance. - Provide an integrated database to avoid the need for manual preparation efforts
With the old planning application, current actual and budget data had to be manually imported into an Access database to prepare the next forecasting process. This manual process caused a lot of effort, while the underlying dataset could not be updated easily during the planning process.
solution
The final solution made use of the formula mode of SAP BO Analysis for Office-Frontend. The SAP BI data modeling has been implemented in SAP BW 7.5 using the Eclipse Neon version of the HANA Studio development environment.
- Formula mode of SAP BO Analysis for Office
Complex cell calculations are technically feasible with the SAP BEx Query Design tool but the resulting BEx queries would be very complicated and difficult to maintain. It has therefore been decided to use the ´formula mode´ within the Excel-based SAP BO Analysis for Office frontend. The final solution therefore used a combination of technical possibilities of the BI-IP backend and SAP BO frontend. - SAP BW 7.5 / HANA Studio
During the project, the system was upgraded from SAP BW 7.4 to SAP BW 7.5 on HANA. The underlying planning data model has been quickly transformed into a HANA-optimized data model. Planning-enabled Advanced DSOs were used instead of the former Real-time-InfoCubes. The MultiProvider was exchanged with a HANA Composite Provider. - FOX Formula Extension
Parts of the planning functions were implemented using SAPs FOX macro language. While ABAP-based planning functions are always executed in the application server, FOX-based planning functions can be executed directly in the HANA database thus providing superior performance even for large datasets.
results
The controlling department is now more empowered. Flexible planning layouts as requested backed by an integrated, reliable and stable database.
Actual/Plan/Forecasting reports can be instantaneously created whenever needed. They can now make decisions on the fly based on the latest real time data.
The effort vastly improved the company’s planning and forecasting functions, streamlined the planning process and reduced manual efforts.