Financial Consolidation and Close

Get Involved. Join the Conversation.

Topic

    Edward Bennett
    YTD (LTD) Data Load Approach
    Topic posted July 17, 2018 by Edward BennettGreen Ribbon: 100+ Points, tagged Balance Sheet, Cash Flow, Data Integration, Dimensions 
    786 Views, 5 Comments
    Title:
    YTD (LTD) Data Load Approach
    Summary:
    Does FCCS allow us to load YTD (LTD) data and still leverage movement correctly?
    Content:

    Our client is hesitant to switch from YTD (life to date) balance sheet balances to periodic. They rely on loading YTD (life to date) as a control to catch issues where a ledger was opened and an adjustment was snuck into a prior year (i.e. their minority interest entity). Loading periodic data won't catch this risk if it was in a prior year. The client is transitioning from HFM so they are used to this type of load. We understand the tool is a periodic based tool but the client does not want to load periodic data.

    We are loading YTD (life to date) data in the app and still leveraging the movement dimension. If we look at the top of the house movement dimension, opening balance is captured twice since under closing balance, opening balance is a child. The closing balance already loaded includes the opening balance since we are loading life to date.

    Changes in balances should still calculate the same (assuming no one went into a ledger and posted a prior period adjustment) from either loading period or life to date since the opening balance is the same number in the current period as the closing balance in the prior period. The client just can't look at FCCS_Movements since that double counts the opening balance. Instead we are looking at FCCS_Movements_Total. Please refer to image for a screenshot of our Movement dimension.

    Has anyone else tried this approach? Is there a better approach - I saw a topic with a similar ask but that was posted last year. Not sure if any of the recent updates has addressed this concern. We are testing this approach now to see if there is any impact on fx, cash flow, etc.

    Image:

    Comment

     

    • Wayne F

      Hi Edward,

       

      Keith has posted some example calc scripts in the following comment:

      https://cloudcustomerconnect.oracle.com/comment/112387

       

      I have not tested the performance impact.

       

      There is also the ability to upload the periodic and YTD values into different view and movement members(FCC_ClosingBalance_input) and let FCCS valuate any differences between the YTD and the accumulated periodic values.  Using this method raises another issue, Data Management does not allow you to load both YTD and periodic via the same location, the view dimension and view dimension mappings gets changed when Oracle do monthly updates. So you will need a YTD location and a periodic location and the users will need to upload two files.

    • Keith Glide

      The following blurb will be added to the 18.08 Admin Guide, providing a little more guidance on YTD and LTD data entry:

      ----------------------------------

      Data can be loaded to FCCS on a Year-to-date (YTD) basis. If data is loaded to the FCCS_YTD_Input View dimension member instead of the FCCS_Periodic member, then FCCS will populate the periodic member such that the YTD amount matches the input amount. In all periods except the first period, the prior period YTD amount is subtracted from the YTD Input amount and the result is written to the Periodic View. In the first period, the YTD Input amount is written to the Periodic View. The YTD Input amount is then cleared. Note that this population of the periodic amount is based on a YTD input amount and NOT a Closing Balance input amount (although for Income Statement accounts only, these are the same). Closing Balance includes the impact of Opening Balance while YTD excludes Opening Balance and encompasses movements for the current year only excluding Opening Balance.

      Closing Balance data can be loaded to the FCCS_ClosingBalance_Input member of the Movement dimension. The purpose of loading data to this movement member is to identify any discrepancy between the aggregation of the Opening Balance plus periodic movements to the parent Closing Balance member and the required closing balance amount at entity currency. Closing Balance Input is not translated or consolidated. This member can however be used at a data entry level in a data entry form to compare with the aggregated Closing Balance member, and can also be used in Configurable Calculation rules to populate a default movement member with the difference between the current and required closing balance (e.g. <default movement> = Closing Balance Input – Closing Balance + <default movement>).  Closing Balance Input can be loaded to either FCCS_Periodic or FCCS_YTD_Input, but will ultimately be stored in FCCS_Periodic, from which any calculations should be sourced.

      ----------------------------

      If you want to load YTD P&L amounts and Balance Sheet LTD amounts in a single process, you should be able to load to the FCCS_YTD_Input View... with the P&L accounts mapped to the Net Income movement and the B/S accounts mapped to FCCS_ClosingBalance_Input movement, but you will need to add the rules necessary to automatically populate the required B/S movement(s) for the LTD Closing Balance entries.

    • Keith Glide

      Beware that by loading Closing Balance to a movement, and relying on FCCS_Mvmts_Total to reflect the Closing Balance (rather than using the FCCS_ClosingBalance member) you will probably encounter issues at translated currency (assuming you have a multi-currency application). The FX Opening and FX Movements movement dimension members (included in FCCS_Mvmts_Total) are calculated based on the Opening Balance and Total Movements respectively... and for Historical accounts will also generate your CTA entry. The system FX rules are designed to ensure that the aggregated Closing Balance member reflects an Ending Rate translation, but relies on the correct population of both the Opening Balance and movements members.

    • Ricardo Musquiz

      My client is currently loading LTD using the scripts mentioned before.  We tagged each BS account with a UDA which references a movement member.  We modified the Oracle example by optimizing the FIX and referencing the UDA.  Using this approach did not impact performance at all.

      • Mihajlo Belosevic

        Hello Ricardo,

        Would you be so kind sending me part of the script, since we are facing the same request from our clients? I'm not actually a technical consultant, but I absolutely understand what needs to be done, so I think that it would be very helpflul for me. I would really appreciate it :) Here's my mail in case you would like to send me: mihajlob1991@gmail.com

        Thanks, BR, Mihajlo

    • Sudhir Ananthuni

      Hi Keith,

      We are currently loading LTD balances to the closing balance input member and are leveraging the calculation to squeeze the movement to a custom movement. This works fine when we load the entity currency balances. however, when we load the USD balances to the override accounts (tagged with amount override) it only appears in the Periodic view at the Input currency and nothing appears in the YTD view. Is there a limitation on the override accounts to not move the data to "FCCS_YTD" when the data is loaded to Input currency "USD"?

      Thanks,

      Sudhir