Talking Tech: Simplify Your Data Loads and Cash Flow with Oracle's New Calculate Movement Rule

Let’s Talk Tech.

In Oracle’s April 2020 patch (EPM Cloud Release 20.04), a new system rule, Calculate Movements, has been added. This rule allows you to load YTD data to the Closing Balance Input member in the movements dimension, rather than periodic data directly to the various movements. From there, the system will calculate the periodic movement and write that value to a specified movement member to help streamline and facilitate out of the box cash flow functionality. The periodic movements from Closing Balance Input are based on newly added attributes to both the Account and Movement dimensions.

Why is this so important?

Prior to this patch, Oracle’s Financial Consolidation and Close Cloud (FCC) allowed YTD data loads for balance sheet data; however, using the out of the box cash flow logic would have been a highly customized process. Users had the option to load and write data to the Closing Balance Input member in the movement dimension, but any movement calculations based on this Closing Balance Input member would have required users to write custom calculations and on-demand rules.

The next few steps are going to walk you through how to set up your FCC application to correctly load YTD data for your balance sheet and utilize the system functionality for cash flow. These steps include updating the GL import and mappings in Data Management, enabling the Calculate Movements rule, and updating the attributes for both the account and movement dimensions to move the balance sheet accounts to the correct movement members for cash flow purposes.

Step 1

The first step is to update the import format in Data Management. Since we are no longer loading periodic data, we need to update the View dimension to load data to the FCCS_YTD_Input member. Data loaded to this member is not ultimately stored here, but is used to back into periodic values to store in the FCCS_Periodic member, which then derives the FCCS_YTD value.

Step 2

Next, we need to update our movement mappings for all Balance Sheet accounts to the out of the box FCCS_ClosingBalance_Input member. In our example, we are assuming a traditional numbering for the chart of accounts (i.e. 1 = Asset, 2 = Liability, 3 = Equity, etc.). P&L accounts will continue to be mapped to their existing movements.

Step 3

Once data Management is updated, the Calculate Movement rule must be enabled. On the Consolidation screen (Navigator -> Application -> Consolidation -> Local Currency), highlight the Calculate Movements rule and on the right panel, toggle Enabled to “Yes”.

Note: This rule will not appear in the Local Currency section of the Consolidation Process screen until custom movement members have been added. This rule is currently not supported for any movement members with the “FCCS_” prefix.

Step 4

Once the Calculate Movements rule is enabled, the attributes for both the movement and account dimensions can be configured. First, we need to specify all default movements. Under the Attribute Values section of the metadata in the Movements dimension, the “Is Default Movement” should be marked Yes for any movement that can be used as a default for a balance sheet account. A default movement can only be a level 0 descendant of “Movements Subtotal”. Rather than cherry pick the movements needed to be tagged with this attribute, all movements can have this classification with zero negative impact.

After the movement dimension is updated and the correct movements have been tagged, it is now time to assign an applicable default movement to each balance sheet account. Under the Attribute Values section of the Account dimension, you will see all tagged default movements on the screen to the left. Once you locate the correct default movement for a given account, you move the member to the right side and save your metadata. Once this process is complete for all balance sheet accounts, the database must be refreshed.

Step 5

It is finally time to see your results, but first a consolidation must be run. During the consolidation, the opening balance and any year-to-date movements are subtracted from the entered or loaded closing balance amount and the difference is posted to the default movement for the current period. In the below example, December 2018 is the first period in the application. As a result, the total amount loaded to the Closing Balance Input member is directly written to Additions to PPE (the default movement). When we move to January, you can see that only $26,922 is written to the default movement member. This the difference between the $5,257,464 loaded to Closing Balance Input in January 2019 and the $5,230,542 loaded in December 2018.

You will also notice that Closing Balance Input is in a separate hierarchy to Total Movements. Data loaded to the Closing Balance Input member is stored even after the value is written to the default movement member and is used as a check to ensure that the Closing Balance in the Total Movements hierarchy ties to the amount loaded to the Closing Balance Input member

A few items to note:

If data is previously entered to the Closing Balance Input member, but is later cleared, any subsequent re-consolidation will not adjust the amount already posted to the default movement. The default movement member amount that is populated from the Calculate movements rule will be treated as if it has been entered directly by the user.

If the default movement members defined in the metadata are changed, then a consolidation must be re-run in order to update to the new settings. Changing the default movement settings will not “impact” any existing data.

Finally, one additional item to note is the need to load “hard zeroes” in order to zero out an intersection. FCC, unlike HFM, does not interpret missing to be the same as zero. If an account balance goes to zero and the data load file does not include a zero record (or the zero is suppressed), the account balance will remain the same as the prior period.

This blog highlights the power of the new features and enhancements that have been and are being built into Oracle Financial Consolidation and Close. If you have any questions on FCC, Oracle EPM Cloud or how your company can make its transition to the cloud do not hesitate to contact us.