Why not create a full fledged consolidation solution that runs natively on SAP Analytics Cloud!
Something that has the same functionalities and features as SAP BPC, but built natively on SAC… and then go beyond that and get creative!
It took me roughly 7 months to develop everything, and it works beautifully. We can customize the interfaces any way we want using SAP Analytic Designer, and if you are really good in Java Scripting, Advanced Formulas and developing custom widgets, you will be able to exceed SAP BPC’s capabilities. You can virtually do anything you want through these combinations, if your architecture is solid enough to have a dynamic, flexible and scalable solution.
In this blog series, I will be describing the models, logic and functionalities provided by InsightCubes’ Consolidation Extension for SAP Analytics Cloud. I will not dig into the stories, Analytic Applications, Java Scripting or Advanced Formulas that are part of the solution because it is impossible to fit them in this blog series, but I will be providing links to technical blogs on how to develop each part of this Consolidation solution, covering the models, their architecture, the advanced formulas (detailed currency translations, methods, eliminations and adjustments, Journals, Ownership logic and more), custom widgets, a myriad of java scripts and many more technical and functional goodies. This will be a work in progress, so the content will keep getting updated and published on a daily basis.
The blog series consists of the following:
- Consolidation Extension for SAP Analytics Cloud – Overview
- Consolidation Extension for SAP Analytics Cloud – Data Acquisition, Transformation and conversion
- Consolidation Extension for SAP Analytics Cloud – Data preparation (Opening, variance and other logic)
- Consolidation Extension for SAP Analytics Cloud – Detailed Currency Conversion
- Consolidation Extension for SAP Analytics Cloud – Automated Eliminations and Adjustments (part 1)
- Consolidation Extension for SAP Analytics Cloud – Automated Eliminations and Adjustments (part 2)
- Consolidation Extension for SAP Analytics Cloud – Automated Eliminations and Adjustments (part 3)
- Consolidation Extension for SAP Analytics Cloud – Automated Eliminations and Adjustments (part 4)
- Consolidation Extension for SAP Analytics Cloud – Automated Eliminations and Adjustments (part 5)
- Consolidation Extension for SAP Analytics Cloud – IC Matching and booking
- Consolidation Extension for SAP Analytics Cloud – Automated treatment of Scope changes
- Consolidation Extension for SAP Analytics Cloud – Staging method consolidation
- Consolidation Extension for SAP Analytics Cloud – Managing Ownership Structure
- Consolidation Extension for SAP Analytics Cloud – Entering Journal adjustments
The Cloud Consolidation Extension for SAP Analytics Cloud comes with six models, 38 stories (each including multiple tabs) and 12 very detailed and dynamic analytic applications. so, in this blog series, ill describe the overall capabilities of the Cloud Consolidation Extension and will be providing links to external blogs that go into technical details covering development of Analytic Applications, external widgets, APIs and Java Scripts, and Advanced Formulas. But for now, i will stick with over all explanation of the Cloud Consolidation extension throughout this blog series and describe the functionalities.
Cloud Consolidation Model
Detailed and scalable IFRS Consolidation using the native capabilities of SAP Analytics Cloud
The InsightCubes Cloud Consolidation is a set of models, advanced formulas, 3rd party widgets and Dashboards that provide full fledged consolidation solution for SAP Analytics Cloud. The custom solution will allow enterprises to generate comprehensive consolidated financial statements, enter journals, adjust ownership percentages and methods.
- Detailed Currency conversion to include opening, incoming, dividend, closing, historical and CTA (global and entity specific rates)
- Custom third-party widgets to enable the Ownership & journal interface, overcoming current SAP Analytics Cloud limitations
- Multiple scopes and methods of consolidation (Holding, full, equity, proportionate and custom)
- Matrix, Staging and roll up methods of consolidation
- Scope change treatment
- Intercompany elimination of any group of accounts
- Elimination of dividends, Shares in earning treatment, investment, capital, share and Declaration of non controlling interest for subsidiaries
- Equity Method consolidation and equity pickup rules for affiliates
- Journal interface with debit/credit posting and parking features
- Interactive dashboards and EPM style analytic application through a unified Web Cockpit using analytic application
- Scalable deployment: Leveraging on the capabilities of Advanced Formula, additional logic can be sequenced after the “Baseline” logic to generate desired results.
- Multiple chains of Advanced formulas for different consolidation needs
Intercompany Matching Model
Providing self service reconciliation to subsidiaries for faster close
The Intercompany matching model includes a set of Advanced formulas, Data packages, Dashboards and stories that enables reconciliation of intercompany transactions between subsidiaries without relying on Head Office.
The InsightCubes Intercompany Matching Model records the declarations and reported balances by other entities against a particular entity. It allows business users within each reporting entity to run a report that matches all of its declarations and reported balances against the balances of the rest of the entities, without having to assign to each owner read-access for other entities. Bookings that make the Intercompany declarations match are generated automatically, and details can be posted to the consolidation model.
The model is designed to easily match “their” books with “our” books.
However, the possibilities go beyond matching of intercompany account values:
- Products: Quantities delivered vs Quantities received
- Forecasts: Demand and supply
- Human Resourcing: Talent matching and secondment
- Production: Inventory items and production requirements.
The Intercompany matching model will include IC Booking logic based on Seller/Buyer/greater, to further provide familiar SAP BPC capabilities.
Create Consolidation Scopes, map relationships between the scopes, add entities scopes, assign the entities’ ownership methods and percentages – manage ownership structures of your group with ease.
The model is designed to easily add Entities, with their ownership, percentage, own methods and consolidation percentage per scope.
- The table lists all the directly and indirectly owned entities under the selected scope. Meanwhile, the scope selection list shows the scopes and sub-scopes, in addition to the directly owned entities u der each scope.
- The Ownership Dashboard is composed of native and custom SAC widgets, increasing the native functionalities and features of SAC
- Allows users to enter percentage of consolidation, percentage of ownership and select methods of consolidation. Holding, full and equity.
- The ownership model is a driving model for the consolidation model.
- This interface allows users to add and remove entities from scopes.
- It also allows users to create new scopes and sub-scopes, assign group currencies and staging entities per scope.
SAP Analytics Cloud does not provide detailed currency translation; the native currency translation considers closing and average, per account, but doesn’t allow translation of balance sheet movements. To extend the capabilities of SAC, a currency model is required to act as a driving model to the consolidation model, converting the financial statements for each company, per local currency, to the multiple reporting and group currencies. The table lists all the directly and indirectly owned entities under the selected scope. Meanwhile, the scope selection list shows the scopes and sub-scopes, in addition to the directly owned entities u der each scope.
- The currency model allows the creation and storage of different rates for any currency, and calculates the detailed movement of the financial statement.
- Add any currency (USD, EUR, SAR, AED) and define the rates (Closing, Dividend, Incoming, Historical)
- Assign the Rate to the Movement (Closing balance will be converted at closing rate, Dividend paid/received at dividend rate, etc…)
- Create global rates per currency or entity specific rates per currency
- Run currency translation to generate the currency converted values per account and movement, along with the CTA per each account.
No programing is required to run the currency conversion, even if new currencies and rates are being added. The system cross checks the Local Currency of the entity, fetches it from the currency model and begins to convert to the reporting currencies per individual rate. You can trigger currency conversion based on Entity Specific Rates or Global Rates, add as many rates and currencies without requiring to write a line of code. Opening Rates per currency are taken from last year closing rates, and CTA are calculated automatically.