At Kilimanjaro Consulting we are constantly working with our clients to provide support and elevate their use of MYOB Exo. We noticed that the process of reconciling Sub-Ledger to General Ledger reports was not efficient, and so we set out to find a solution.
What was wrong?
The problem was that MYOB standard reports would not always reconcile to their respective General Ledger Control Accounts. After investigating further, we found that the standard reports will run normally in a non-foreign currency environment, as far as debtors and creditors are concerned. In a system where foreign currency does play a role, the gain and loss reports would highlight potential discrepancies, and function correctly, when looking at the current period. However, when looking at data outside of the current period, the gain and loss reports would not work properly, and could not be relied upon to accurately describe the position.
The reason why stock reports do not always reconcile to the General Ledger is that they are not fully retrospective. In an MYOB Exo stock valuation report, the quantity is calculated retrospectively but the dollar value is not. The report is completed using the current average cost in the system. On sites where the average cost does change, or there is a problem and the average cost is out of sync, this can cause variances even in the current period. It only takes a stock on hand quantity to be out of sync, and stock to be receipted in while that stock figure is out, to cause an average cost problem. This is not always easy to identify when that has happened unless you keep a very close eye on your stock transactions.
How did we fix it?
We built a new set of reports in MYOB Exo to reconcile retrospectively. These reports were designed to use the same information that is being posted to the General Ledger, instead of being derived from other summary reports. Calculating the report in this way, from first principles, may highlight reconciliation issues that were not previously evident. In terms of stock, the reports are based off the transactions themselves that are being posted to the General Ledger. Debtors and creditors age balance reports are also based off the debtor and creditor transactions and payment allocations.
Allocating payments to the respective invoices is key when it comes to the foreign currency elements. Without the allocation you will not know whether the amount has actually been realised.
What are the new reports?
Looking at a point of difference for our new reporting, MYOB’s standard reports are solely a sub-ledger report. However, all but the last page of Kilimanjaro’s new reports is at the sub-ledger level. The new last page gives you a General Ledger Summary comparing the Sub-ledger totals to the General Ledger Control Account.
- Debtors Aged Balance Summary Report: The last page of the Summary report includes a valuable Sub-Ledger to General Ledger Reconciliation Summary and can be customised to show Unrealised Foreign Exchange Gain/Loss where required. This has also been built as a Creditors Aged Balance Summary Report.
- Debtors Aged Balance Detailed Report, and Creditors Aged Balance Detailed Report
- Stock Ledger Summary – Inventory Locations Report: Summarised by stock item, the last page of the report includes a brief Sub-Ledger to General Ledger Reconciliation
Who will benefit from this fix?
- Anyone that trades in multiple currencies
- Those who run perpetual stock
- Anyone that prefers simpler reports; especially noting that you would not have to open the General Ledger to check that everything in your summary is correct, as there is a comparison as part of the last page of the report.
While the standard MYOB reports are still functional in these environments, they will only work properly for the current period. However, making use of this option is much less popular as almost all our clients prefer to continually transact, instead of having to close off one period before they start on another, and thus the need to run these reports retrospectively arose.