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.

Wednesday, 21 August 2013

Sage treatment for invoices from suppliers who are not registered for VAT

The following is an excerpt from the TAX_CODE table for the demo company in a Sage Instant Accounts setup:
Zero rated

Standard Rate

Exempt transactions



Sales to customers in EC

Lower rate



Zero rated purchases from suppliers in EC


Standard rated purchases from suppliers in EC

Non-Vatable Tax Code

The Value Added Tax Act 1994, 1994 c. 23, Part I Supply of goods or services in the United Kingdom, Section 4 contains the following:

Scope of VAT on taxable supplies.

(1) VAT shall be charged on any supply of goods or services made in the United Kingdom, where it is a taxable supply made by a taxable person in the course or furtherance of any business carried on by him.

(2) A taxable supply is a supply of goods or services made in the United Kingdom other than an exempt supply.

Part I, Imposition and rate of VAT, Section 3 of the same act defines a taxable person as follows:

Taxable persons and registration.

(1) A person is a taxable person for the purposes of this Act while he is, or is required to be, registered under this Act.

The HMRC guidance (http://www.hmrc.gov.uk/vat/forms-rates/rates/rates.htm#5) says (emphasis added): "Goods and services that are outside the scope of UK VAT includes anything you: sell (or otherwise supply) when you're not registered for VAT - and you don't need to be registered...".

So, if a supplier is not required to be registered for VAT, and has not done so voluntarily, they are not  a taxable person and any transactions undertaken are outside the scope of VAT on taxable supplies in the UK.

If goods/services are supplied "outside the scope" it follows that they are received "outside the scope". T9 is the code to use for supplies made "outside the scope" of VAT. It is the only code with a 0 in the VAT_INCLUDE column. The 0 means it is omitted from VAT returns altogether.

I've seen all sorts of other approaches that will get the correct net VAT amount (use T0; use T1 but zero out the VAT; use T2) even though the "stats" boxes will incorrectly include purchase amounts "outside the scope" of VAT.  The VAT inspectors may well be relaxed about this but why risk it when T9 will get the job done properly? 

In essence, both the status of the supplier and the nature of the supply determine whether something is VATable - not just the nature of the supply. It is a two stage test.

Possible types of Sage T code Sage T code
supply (ignoring if supplier if supplier is
supplier VAT reg status) is VAT reg'd not VAT reg'd
Standard T1 T9
Reduced T5 T9
Zero T0 T9
Exempt T2 T9
Outside Scope T9 T9