7 Replies Latest reply: Jun 4, 2013 8:07 AM by 1006600 RSS

    Who can help build out my CTA requirements in HFM?  [is this possible]

    1006600
      I believe in infinite possibilities and I believe that HFM is infinitely superior to using an excel consolidation so I'm left to believe that the snazzy CTA and dollar override system built up in our excel consolidation can be integrated into our HFM application - which is nearly up and running except for this CTA bit. So here it goes:

      Applicable according to LS Accounting Policy, as prescribed by US GAAP.
      Arises due to the following:
      Investment in Subsidiaries
      Share Capital
      Current Year Retained Earnings
      Prior Year Retained Earnings
      Dollar override system is utilized, i.e.
      Applies when the base or entity currency differs from the entity’s Parent currency.
      Local books are kept in the local base or “entity” currency.
      Dollar overrides are set in Parent Currency at the date of the transaction or at the month end, as the case may be.
      Dollar overrides applied to base entities and consolidation entities.
      [only when the base currency and the parent currency differ]

      Tracked according to source, account; i.e.
      Investment in Subsidiaries
      Share Capital
      Current Year Retained Earnings
      Prior Year Retained Earnings
      Calculated in Consolidation, currently in excel.
      Maintenance is simple and only requires changes to local currency amounts.

      Calculating the “Dollar Override Amounts”:
      + Prior Period override amount in Parent Currency
      + ((Change in Local Currency amount) x Current pd FX End Rate)
      = “Dollar Override” Amount in Parent Currency

      Calculating CTA in Parent Currency:
      + Dollar Override Amount in Parent Currency
      - (Local Amount x Current pd FX Rate)
      = CTA in Parent Currency

      Calculating CTA in Parent Currency - For Consolidation entities:
      + Dollar Override Amount in Parent Currency
      + Current Period “Loaded” CTA
      - (Local Amount x Current pd FX Rate)
      = CTA in Parent Currency


      Current Period “Loaded” CTA Arises at consolidation entities when the parent currency is the third (or greater) currency used in the string of consolidation.
      Example
      New Zealand Base Currency is NZD and Parent Currency is AUD.
      CTA is generated when NZD is translated to AUD and appears in consolidation in AUD.
      New Zealand rolls into a parent who’s Base Currency is AUD and who’s Parent currency is USD.
      When the parent is consolidated,
      it is first rolled up in AUD, to include the CTA which was calculated at New Zealand
      and then translated to USD, whereagain CTA arises and is layered on top of the CTA which was consolidated with New Zealand’s inclusion.

      Benefits of Formulas [existing system]:
      Low Maintenance
      Accounting is prepared by local subsidiaries in local currencies.
      Consolidation calculates Dollar overrides and CTA amounts automatically.
      Accounting time is spent on analyzing resulting CTA balances for reasonability.
      Manual Rollforward of Retained Earnings required at year end.
      Consistent with LivingSocial Accounting Policy
      Tested and true
      Has survived multiple audits.
      Provides transparency throughout consolidation.

      HFM System development Recommendations:
      Automate “Dollar” Override system in HFM so that the Override figures are automatically calculated and are based on the local currency values provided by the local subsidiaries.
      Importance is placed upon Local currency accounting and less on system calculated formulas.
      Consistent with existing excel based system
      Allow for CTA to be calculated at and within “Consolidation Entities” in HFM.
      Eliminates the need for manual, human intervention in the consolidation process.
      Provides for a natural flow of CTA calculations within the legal entity structure
      Allows for consolidation to occur according to LS Accounting Policy.

      Presentation of CTA in HFM COA:
      Applies to amounts calculated by HFM
      Use control accounts
      i.e. restrict activity in these accounts to hold only the numbers that are generated by HFM in the consolidation process.
      4 separate accounts should be created and utilized to present CTA calculated by HFM:
      CTA – Investment in Subsidiaries
      CTA – Share Capital
      CTA – Current Year Retained Earnings
      CTA – Prior Year Retained Earnings
      These 4 control accounts should roll up to a “Total CTA – calculated” account
      This account should be a child of “Accum Othr Inc_Loss – Accumulated Othr Income-Loss”
      The Account structure requested above would look like:
      “Accum Othr Inc_Loss – Accumulated Othr Income-Loss”
      “Total CTA – calculated” account
      CTA – Investment in Subsidiaries
      CTA – Share Capital
      CTA – Current Year Retained Earnings
      CTA – Prior Year Retained Earnings


      CAN THIS BE DONE? Does the earth travel around the sun?
        • 1. Re: Who can help build out my CTA requirements in HFM?  [is this possible]
          Jeo123
          Yes, it can certainly be done, but this is the kind of thing you likely need to hire a consultant for as it's much more in depth than anyone can easily do over a message board.

          Not to mention, a lot of people on here are consultants and would be out of a job if they did it all for free.
          • 2. Re: Who can help build out my CTA requirements in HFM?  [is this possible]
            1006600
            great! It is good to know that I'm not going insane - now it's just difficult to find out why my consultant wouldn't want to do this for our application. Thanks for the response, and by the way - I never mentioned anything about doing this for free, but if you are up for providing free [quality] work then I will take you up on the offer! $:) I sort of thought this board may also be a good place to reach out and find quality consultants to work with if need be. appreciate the response.
            • 3. Re: Who can help build out my CTA requirements in HFM?  [is this possible]
              James.zhang
              Hi,

              I think your CTA can also be done at HFM. Normally, we put CTA as a movement item of account. In this way, CTA are normally at Custom dimension.

              For example, Cash on hands has following movement items at Custom4 (Beginning Balance at Jan1, Movement Item A, Movement Item B ..., CTA, Closing Balance).

              I think your requirement is to get accounts' CTA total and put this value to another CTA account "CTA- Interest in Subsidrary"

              HFM can do this.

              Thanks.

              James Zhang
              • 4. Re: Who can help build out my CTA requirements in HFM?  [is this possible]
                LukasHerb
                Usually CTA is an account in the balance sheet on which you load the differential of all the historic rates used for your other equity accounts and the closing rate. To achieve this, you should best calculate the CTA impact for each of your equity accounts. There are sereval ways of doing it, but I agree to the last post that using a movement table is probably the best.

                When doing this there are two components:
                All "regular" movements (additions, disposals, changes in scope, etc...) should be open to be converted with a specific (historic) rate. So you could cross your account with the movement table in one custom dimension and an element to store the respective rate in a second custom dimension (we always used custom3 for movements, custom4 for the rate).
                Then you program a HS.Trans to use the rate in that Custom4 element to convert the respective movement and to use the average rate whenever no specific rate is entered (just to be sure).

                Then you add additional movements for the CTA effect. There you program another HS-Trans which takes the differential between historic rate and closing rate.

                The nice thing about this approach is that - depending on how you add up your movements - you will be able to see each equity account at historic rate and at closing rate (which gives you a bit more comfort that your conversion actually works and allows you much more detailes analysis on how your CTA is calculated -> a favourite auditor's question).

                The CTA ACCOUNT that you finally show in your Balance sheet is then simply the addition of all the CTA movements in on the other accounts.

                I hope this was helpful. If you feel that it was even valuable I'm happy to give you my account number :-)

                Regarding your other more general comments. HFM is certainly able to respond to all requirements a regular consolidation has (independent of the standard). Sometimes you need to leverage between the complexity and the added value, but CTA is certainly an item, which should be calculated by the system.

                I have been a consultant for many years, so if are struggling to find the right support and can maybe recommend you some of my ex-colleagues which are still in consulting.

                Good luck,

                Lukas
                • 5. Re: Who can help build out my CTA requirements in HFM?  [is this possible]
                  1006600
                  Lukas - hehe; thank you very much. Apparently despite our well communicated requirements - our developer took it upon himself to build up another solution according to his own specs - granted the solution he built up didn't work given our complex, layered legal entity structure and various foreign currencies, we have ended up with a hybrid solution which combines the [from what I understand is the] out of the box solution and our own idea about overrides - but at the end of the day we're left with a consolidated balance sheet which plugs [what an ugly word] CTA rather than calculates the CTA based on the local currency values and the underlying override figure. Honestly, our excel solution for consolidating, calculating and presenting CTA is more robust and reliable than this plug system that is now in HFM. Why our original requirements couldn't be fulfilled - because the developer would have to go back and make many changes to our HFM rule system, which he didn't want to do [so this translates to: the developer didn't build our our requirements and now we'll be left to forever prove out that all that CTA is being calculated, errr plugged, correctly through a manual offline process for the rest of time - further this translates into a lack of efficiency, and valuable time, money and resources that could be focused elsewhere rather than having to constantly prove out CTA.  At least - after considerable talks, we have been able to reconfigure the developers solution so that CTA is reflected appropriately through out our legal entity structure - now at least it seems that we'll be able to make appropriate statutory filings for many of our legal entities out in the world.  What a debacle.  Thank you all for your responses - your answers just support my original theory which was - if we can consolidate and calculate CTA in Excel then we should be able to configure HFM to calculate the components of CTA also, as it's not that difficult.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                  • 6. Re: Who can help build out my CTA requirements in HFM?  [is this possible]
                    LukasHerb
                    Without judging something I haven't seen, it's a pain if a bad implementation spoils the capabilities of an amazing system.
                    If you want to, u can send me MD and rules and I can have a quick look - a bit of own curiosity possibly as well :-)
                    • 7. Re: Who can help build out my CTA requirements in HFM?  [is this possible]
                      1006600
                      Not even sure how to go about doing that Lukas :) Please feel free to email me directly - email is on my profile here.