Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Friday, 30 August 2013

Sage year end processing

A common bugbear for Sage users is how to deal with "late" adjustments to the accounts. Should they defer running the Year End process until the audit report has been signed (impacting significantly on "normal" Sage based reporting for the new year) or run the Year End process and deal with any late adjustments manually (how do you process late adjustments anyway?)?  

This post outlines the mechanics of the Sage year end process and proposes a methodology that promotes robust reporting.

Sage analyses transactions to one of 14 balance fields in the NOMINAL_LEDGER table. The transaction date determines which balance field is updated by a transaction. If the transaction date is before the currently defined start of the financial year, the BALANCE_BF field is updated.  If the transaction date is after the currently defined financial year end, the BALANCE_FUTURE field is updated.  If the transaction date is within the currently defined financial year, the appropriate BALANCE_MTH[xx] field is updated. Every transaction also updates the BALANCE field, making it an aggregate of the other 14 balance fields.
What does Sage do when you run the year end process?

Firstly, the start and end points of the “current” financial year are incremented by one year.

For each nominal code, inter alia, the following processes seem to be carried out:

  1. The balance as at the old financial year month 12 (BALANCE minus BALANCE_FUTURE) is placed in the BALANCE_BF and BALANCE fields.
  2. All BALANCE_MTH[xx] fields are copied to the PRIOR_YR_MTH[xx] fields (flipping the sign on any balance where the nominal code is assigned to Categories 1, 7, 8 or 9) and then all BALANCE_MTH[xx] fields are cleared.
  3. The BALANCE_FUTURE field is cleared and each future dated transaction from the old financial year is posted to the appropriate BALANCE_MTH[xx] or BALANCE_FUTURE field, also updating the BALANCE field. 
  4. A “Ledger Year End” journal, dated the last day of the old financial year, is generated to cancel the P&L amounts (where the nominal code is assigned to Categories 1, 2, 3, 4 or 10) in the BALANCE_BF field with the balancing entry coded to Retained Earnings.  The date on this journal will cause it to post to the BALANCE_BF field. As usual, the BALANCE field is updated too.

In an ideal world that would be the end of it, but, as noted in the introduction, we’ve all come across situations where there are P&L transactions that we need to record after the year end process has been run. The following describes a robust methodology for doing this:

  • Use Data Import of "Audit Trail transactions" to upload late journals to the appropriate accounts (if possible, no transactions should be dated on the last day – the last day should be reserved for Ledger Year End transactions (see following) so that Sage transaction based reports can isolate them) .  As the year end process has already been run we need to manually generate the equivalent “Ledger Year End” entries as part of the upload to clear the P&L balances to Retained Earnings.  All these journals will analyse to the BALANCE_BF field because the transaction date is prior to the current financial year.  This is the required outcome for the “Ledger Year End” entries but not for the entries that should have been analysed to the appropriate PRIOR_YR_MTH[xx] field as well as the BALANCE_BF field.  
  • The balances on these fields can be corrected using Data Import of "Nominal accounts" (keeping in mind the reverse signage of Categories 1, 7, 8 and 9 mentioned above). If you prefer you can make adjustments manually in Modules | Nominal Ledger | Record.
  • As always, backup, backup and backup!

What about Balance Sheet roll-ups at the year end?  For example, it is not uncommon for fixed assets costs to be recorded in separate opening balance, additions and disposals nominal codes. The best way to deal with these sorts of aggregations is to process “Ledger Year End” entries, after performing the Year End process, to move the amounts from the additions and disposals codes into the opening balance code ready for the next year.

A brief example may help with understanding:

Say I've already run my Year End for 2012 and the auditors insist on an accrual of £10,000 for Goods Received not Invoiced at the year end. I'd create a journal as Dr Purchases (5000) £10,000 Cr Accruals (2109) £10,000, dated 30th December 2012 (as previously explained), and then create a journal named as "Ledger Year End", with "Ledger Year End" also in the Details field, dated 31st December 2012, to mimic what Sage would have done with the first journal if it had been posted prior to the year end process being run. The "Ledger Year End" journal would be Dr P&L Reserves (3200) £10,0000 and Cr Purchases (5000) £10,000. After processing the journals, the prior year balance adjustments described above keep the prior year balances in line with the transactional data for each month (excluding the Ledger Year End transactions processed as 31 December 2012). Keeping all "normal" transactions away from 31 December 2012 allows you to run transactional reports to 30 December 2012 that agree with the balance based reports - only the Ledger Year End transactions not processed to the prior financial year balances are excluded.

Congratulations if you're still with me!

If you follow the guidance above you should be able to achieve the same robust reporting outcomes from either Sage transactional reports or Sage balance based reports.  Bear in mind that most Sage balance based reports do not report on anything other than the current and prior years.  The Onion product (see banner at the top of the page) allows the same robust transaction based reporting whether the Sage Year End process has been run or not.  Many find that using Onion removes the need to perform the Sage Year End process earlier than they would otherwise choose, just so they can start to report on the current year.

No comments:

Post a Comment