Aspect: Integrating Accounting Systems with ETRM Software
Posted: 3 July 2017 | Author: Adam Rice | Source: Aspect Enterprise Solutions
At ResourceFueled, we are often contracted by a client to implement custom interfaces to meet a company's specific needs. We recently implemented an interface of Aspect's ETRM software into an accounting system. The accounting system in this instance was SunSystems, but it doesn’t make much difference what the accounting system is.
In this case our client had contracted us to build the interface. The most eye-catching part of the interface is the building of the reports that the journal entries consist of. But this happens near the end of the project and, because we have done interfaces before, the writing of these reports are relatively quickly done. Aspect’s excellent script writing functionality that is available to customers makes this a well-defined task which is quite fun to do for those of us who like getting into ‘the zone’. Before writing the reports, there is a lot of analysis, explanation to the client of options, and decision taking to be done.
Since you don’t want to enter customer details into two systems, we first had to move information from the database which holds data in a detailed form into a database that holds it in a sparse form. In this case, we decided to first implement a semi-automatic process for a few months before launching a completely automatic system. This allowed for the accountants to have the chance to get used to the rhythm of the process of information flowing from their ETRM software to their accounting system. Once the process settled down and everyone was sure that it was working for them, the business got comfortable with running the second stage of the project which was to deliver full automation with web-services, which is what we did next.
Our client asked for an interface to be built for them, but they didn’t necessarily know their processes or what journals need posting. Here are some of the questions we had to address before integrating the ETRM software with the accounting system:
- How will the data be reported in the two systems, and how will it be compared and reconciled?
- What are the requirement for month end accruals?
- How will the bank reconciliation be carried out?
- How will route 1 ancillary costs be accounted?
- What's the strategy hierarchy?
- How will transactions be deleted?
- Which controls can be implemented to ensure that assumptions we make cannot be contradicted?
- Where should balance sheet reports like debtor/ creditor reports be generated?
- How will cash be entered?
- How will prepayments be handled?
Another important consideration is accounting policy. There are three relevant options:
- Fair Value Accounting: Valuing everything at an objectively verifiable value. This means that forward physical trades are not given a value and all hedges on those forward trades are valued.
- Hedge Accounting: Not accounting for those hedges that are hedging forward trades.
- Mark to Market Accounting (MtM): Everything is valued as we would do in management reporting.
There was a mutual agreement between our team and our client’s team, that posts would occur in SUN using M2M so that the PnL in Aspect and SUN was equal. A reconciliation process was put into place for month-end reports to prove that the profit and positions in each of the two systems agreed.
Completeness, Accuracy and Validity
Once the process was agreed upon, it was time to design the interface.
It was now time to write the reports in Aspect's ETRM software. This is something that can be done fairly quickly in scripts because these things have been written before and most of the tricky problems already have solutions. But it is devilishly difficult to be accurate the first time here and extensive testing had to be done to confirm that the output was correct. The reports that were created needed to be transformed into a format that could be uploaded into the accounting system. We did this using a macro that is available to users as an add-in on everyone’s computer. Users run reports that they want posting in that session. The macro takes each report and sorts and formats it so that it is ready to be uploaded. It also adds journal details to a journal log which records when it was posted, which type of journal it was, which period it covered, and which legal entity etc. It then closed the original report so that it wasn’t posted again. The user just runs the macro/ posts until, one by one, all journals are complete and reports closed.
Keeping Data Clean
It is important that the data is kept in the trading system in a way that works for accounting. Within the ETRM software, you want to keep all your transaction data at ‘leaf node’ level, make sure reference data is complete, account for all out-turn losses, and make sure that stock matches reality. Users can set up preventative and detective controls to make sure that this happens.