There are a few remaining roadblocks that are preventing HFM users from getting on board to migrate to Oracle FCCS (Financial Close and Consolidation Cloud Service). When legacy HFM users hear that FCCS utilizes Essbase as the underlying database and only stores data on a periodic basis, they often express apprehension to fully adopt to the streamlined solution, and would prefer to wait for ‘full HFM parity’. One of the primary drivers for this hesitation is that many organizations do not even have the capability to export periodic activity from their General Ledger system. Through our implementations, we have found that configuring a few easy FCCS settings can get around this roadblock, and we will walk through how to load closing-balance balance sheet data depending on your organization’s needs.
Inherently, FCCS is set up to accept periodic or year-to-date (YTD) data. Utilizing the YTD method, a YTD_Input member in the View dimension is used to load YTD data and back calculate the periodic values to store data in the system. Using a simple Smart View ad hoc as an example below, data for Cash and Cash Equivalents in Jan is loaded to the FCCS_YTD Input member, and FCCS calculates and stores the FCCS_Periodic value for Jan. For Feb, FCCS does the same thing. It loads the ‘1000’ to the FCCS_YTD Input member in the View dimension for Cash and Cash Equivalents and calculates the periodic activity based on the activity that occurred earlier in the year: Feb -> FCCS_Periodic = Feb-> FCCS_YTD Input – Jan->FCCS_Periodic. The same process works for Revenue.
Note: For simplicity purposes, the Smart View below shows data in the FCCS_YTD Input member. In reality, data is not stored in this member after the data is loaded. It is used as a holding spot to calculate the periodic value and is cleared.
Easy enough, right?
The problem with this approach is that it doesn’t take into account previous year activity. The FCCS_YTD Input member TRULY means YTD, not life-to-date (LTD) balances (which is how balance sheet data is typically captured: the closing balance in the account at a point in time).
In this same example, if I pull in Dec of the previous year, you can see that the Cash and Cash Equivalents and Revenue accounts are not taking into account the balances in Dec in the previous year. For Revenue, this works perfectly. All Net Income accounts roll into Retained Earnings at the end of the year. Therefore, you would only need true YTD data to be loaded. For balance sheet, since balance sheet data is typically extracted from your GL in LTD or Closing Balance format, this poses a problem.
Note: For simplicity purposes, the starting point for this entity is Dec FY17.
The next few steps are going to walk through how to set up your FCCS application to correctly load closing balance or LTD data for your balance sheet. This includes updating the GL import from Data Management into FCCS and adding a custom calculation to move the balance sheet accounts to the correct movement members for cash flow purposes.
As mentioned above, by default, FCCS loads data to the FCCS_YTD Input member of the View dimension. If your organization is utilizing cash flow (and loading to the movement members in the Movement dimension), the Movement dimension mappings will need to be updated to map all balance sheet data to the ‘FCCS_ClosingBalance_Input’ member. Most organizations will use the account segment from their GL to map the appropriate accounts to the appropriate movements for the cash flow. Ex: Map cash accounts to FCCS_Mvmts_Cash in the Movement dimension.
Step 1: The screenshot below is the import format of our sample GL data load that ‘maps’ the account segment in the GL file to the Movement dimension:
Step 2: Navigate to your Movement mappings for the same location, and map all balance sheet accounts to the ‘FCCS_ClosingBalance_Input’ member:
The mappings above assume a standard numbering scheme for GL accounts, where anything that starts with a 1 is an asset, 2 is liabilities and 3 is equity. Therefore, all accounts that start with a 1, 2, or 3 can map to ‘FCCS_ClosingBalance_Input’. Your remaining P&L accounts (typically 4-8) can continue to map to their respective movements since the FCCS_YTD Input loading method works correctly for P&L accounts. If your organization follows a different numbering scheme, the mappings will need to be adjusted accordingly. The end goal is for all balance accounts to map to the ‘FCCS_ClosingBalance_Input’ member and for your Net Income account movement mappings to remain the same.
Once that is set up, if we navigate to the dimension editor, we can see that ‘FCCS_ClosingBalance_Input’ sits outside of the ‘FCCS_ClosingBalance’ reporting hierarchy, and therefore will not populate for your organization’s financials and reports:
Therefore, we need to write a custom calculation to move and calculate the loaded closing balance input data into the correct movement members underneath the ‘FCCS_ClosingBalance’ member in the Movement dimension for cash flow and financial reporting.
Step 3: Navigate to Application -> Consolidation -> Consolidation: Process tab and select the ‘After Opening Balance Carry Forward tab’:
Step 4: After calculation manager opens, expand Planning -> (your app name) -> Consol -> Rules -> FCCS_10_After Opening Balance Carry Forward_Local Currency and open the rule. If you haven’t added any custom calculations to your application yet, your rule will appear with a few suggested FIX statements commented out:
Step 5: We are going to add custom logic in the ‘Entity Currency’, ‘FCCS_Entity Input’ section, to move base data from the ‘FCCS_ClosingBalance_Input’ member to the correct Movement member based on subsets of balance sheet accounts. If you are unfamiliar with basic FCCS script writing, please refer to the FCCS Oracle guide as they have great basic tips and tricks.
The logic below is essentially taking all the data that we’ve loaded (in this example I am fixing on the ‘FCCS_ManagedData’ Data Source member, but you can fix on more than one if this is applicable to multiple Data Source members for your organization) and breaking it out into different sections of accounts that need to be written to different Movement members to support the cash flow statement. For example, I want to map all of my cash accounts to ‘FCCS_Mvmts_Cash’.
To break it down more clearly, the first time the consolidation runs in Dec FY17, there is 700 in ‘FCCS_ClosingBalance_Input’. Therefore the calculation will read as follows: ‘FCCS_Mvmts_Cash’ = 700 – 0 + 0, since there is no balance in the ‘FCCS_ClosingBalance’ or ‘FCCS_Mvmts_Cash’ member. The end result is ‘FCCS_Mvmts_Cash’ = 700.
The second time the consolidation runs, the calculation will read as follows: ‘FCCS_Mvmts_Cash’ = 700 – 700 + 700, which has the same end result: ‘FCCS_Mvmts_Cash’ = 700. The same logic applies for each following month since the closing balance of the previous month rolls into the opening balance of the next month.
The end product for the calculation of January 2018 is as follows: ‘FCCS_Mvmts_Cash’ = 800 – 700 + 0, therefore calculating ‘FCCS_Mvmts_Cash’ as 100, and so on. This ensures that the ‘FCCS_ClosingBalance_Input’ member ties to the ‘FCCS_ClosingBalance’ member that is used for reporting purposes.
In order to verify this is working correctly, validate and deploy the rule, load all the GL data and execute a consolidation. Once this is completed, if I navigate back to Smart View, my refreshed data will appear as follows:
To show the detail of the movements, I moved the Movement dimension to the rows of the Smart View query. The ‘FCCS_ClosingBalanceVariance’ member can be utilized to verify that the loaded ‘FCCS_ClosingBalance_Input’ data equals the calculated ‘FCCS_ClosingBalance’ member that is used for reporting on the bottom half of the spreadsheet. In addition, the top portion shows the details of the Movement dimension and how the system will calculate the periodic activity for Jan and Feb based on the Closing Balance + the account loaded to in the same period.
Once you are comfortable with testing the results, you can expand the custom calculation to include more balance sheet account members, with the Accounts Receivable accounts used as an example below, until you have moved all balance sheet data to a specific movement:
Now that your FCCS application can accept the format of your GL data and can calculate the periodic activity to store the data correctly in the application, cash flow and financial reporting may resume!
Note: Currently there is a bug in FCCS (Bug #28352381) that may cause the life-to-date data loaded not to consolidate. If you are experiencing similar circumstances, a workaround is to refresh the database after the data is loaded and then reconsolidate.
For more information on our team’s expertise with Financial Consolidation & Close, or to speak with a team member directly, please reach out to us via our website.
