Summary of the activities involved in the consolidation process, from data import and preparation to currency conversion, ownership management, IC matching, eliminations and journals.
In this blog we will explain step by step how-to use the Cloud Consolidation Extension for SAP Analytics Cloud as an End to end process. Although you can run all the processes in one click through the All in One data action trigger, however, we will show the execution of each step individually to show you the detailed results.
Data Preparation Steps
Data loading and preparation
1. Upload data from source systems
To import data, first step is to import the trial balances of all the companies from all the source systems for a specific date.
Once the upload is concluded, the statement of financial position (Balance Sheet) and Profit and loss statement for each company are stored on the consolidation model in the local currencies of each respective company.
2. Generate opening balances (closing of prior year end values for balance sheet will be copied to opening of current year)
Generating the Opening balance for these companies balance sheet statements is now possible through running the generate Opening data action trigger, which will take the previous year end closing balance and post it on the opening flow of current years’ months.
3. Run calculation of Variance. The system will calculate the Variance between the values of opening with movement against closing values.
Running the Calculate Movement data action trigger will execute the calculation of the net variance flow which is basically the difference between the sum of the opening with all the movements against closing.
Now our statement of financial position has the opening values, movements, closing, and net variance. Also a specific flow in the retained earning has the YTD value of the net income from the profit and loss statement.
The same data is reflected in this Ad-hoc report
Entering Currency Conversion Rates
Global Rates
After generating the financial statements we convert the values from the local currency of each entity by entering the Currency Conversion Rates for each reporting currency. These rates are automatically stores in the rates model (Driver model for currency conversion logic in the consolidation model).
Executing the currency conversion data action trigger will automatically convert the financial statements from the local currencies of the companies to any desired set of our reporting currencies.
Entering the Global rates on the InsightCubes Currency Model – Global Rates (1) in Mass Data Entry Mode, then clicking the “Process Data” button as shown in the screenshot below.
After processing the Data entered, the data can be published “Publish Data” (1), a popup with the details of the data records is open, click “Publish” (2).
After publishing the Data click the “Run Global Currency Conversion” Data Action Trigger button, a popup will appear allowing the user to finalize the selections, we entered the currencies that we entered the rates for in the “Select Reporting Currency” filter (2), then click “Run” (3).
Refresh the data, and the report will be generated with all the entered conversion rates as shown in the screenshot below. Notice that each movement in the balance sheet was converted with its assigned rate for the currency and a translation adjustment has been generated for each account on a specific flow.
The number of rates and currencies to be added is unlimited, without the need to write a single line of code, for it is achieved through assignment.
Entity Specific Rates (optional)
If some of the entities have unique conversion rates we can use the entity specific rate. After unpublishing the currency converted values, enter the rates for each currency for the entities with unique rates, save and publish the entity specific rates in the rate model, then trigger the “Run Entity Specific Currency Conversion” Data Action Trigger. The system will first convert the financial statements of these specific entities using their corresponding entity specific rates and then convert the financial statements for the remaining entities using the global currency rates.
Ownership Manager
Before running the consolidation, we manage the Ownership Structure. We can create as many scopes and sub-scopes as needed with the ownership interface.
Adding Entities to Scopes, processing the selection, publishing the data and running the Copy Data.
In the above screenshot we highlighted the steps of adding an Entity to a selected Scope in sequence.
First (1), the Scope must be selected from the Scope Selection panel, then click the “Add/Remove Entities” (2) button, a popup will appear with the list of entities (3) on the left side of the popup. Select the specific entities and click the right pointing arrow (4), they will be added to the report. Click close on the popup.
After adding the specific Entities we will be able to start filling in the Consolidation Method, Consolidation Rate, and Ownership Rate for each entity.
As shown in the above screenshot; the Method is chosen from a dropdown, whether it is None, Holding, Full, Equity, or Proportionate. Then the percentages are added in the Consolidation Rate and Ownership rate Columns and the data is “Saved and Published”. The same is done for the sub-scopes.
The selected Entities are now added to the Scope. Another set of entities may be added to another scope following the same steps.
As an option, the full ownership structure can be copied (The consolidation methods, ownership rates, consolidation rates and the relationship between entities and scopes from one period to many destination periods). To explain this optional process, clicking on the “Copy Data” button the data action trigger popup is opened.
The Data Action popup will have the Target Version, Scope Filter, Select Destination Time all preselected as per where the Entities were published to, the user may chose the designated time from the “Select Source Time” filter (2) , then click “Run” (3). Upon success, a message will appear stating that “The data action finished running on Version “Actual” in our case. Then click “Publish Data” (4) another message will appear “You’ve successfully published the version”.
Run Intercompany Matching
Conducting the intercompany matching prior to running the consolidation, and before managing the ownership structure, will result in a faster reconciliation. The Run IC Matching data action trigger will prompt the system to match the intercompany transactions between the entities, which in turn ensures that each entity can check and match what their partner companies have booked against them, and reconcile the differences rapidly. Note the differences can be booked per account on the seller, buyer, or whoever has the positive or negative difference.
The Audit Trails in the columns labeled Their Liabilities/Expenses and Their Assets/Income in the screenshot below show what the partner company booked against our company.
Run Consolidation
After checking the intercompany matching results, we proceed to run the consolidation process, this process will take the consolidation method into consideration, each method has a specific set of automated eliminations and adjustment rules.
The user may want to edit the filters in the Run Consolidation popup. Then click run two message boxes will appear stating that “The Data Action ‘InsightCubes-Consolidation’ is Scheduled to Run on version “Actual” then “The Data Action ‘InsightCubes-Consolidation’ finished running on version “Actual” and the results were published, Please refresh to see the results”; refresh the sheet and the figures will be generated in the report as shown in the screenshot below.
In this ad-hoc report
