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:

TAX_CODE	DESCRIPTION	TAX_RATE	VAT_INCLUDE	EURO_SALES	EURO_PURCHASE	RELATED_CODE	RELATED_RATE
T0	Zero rated	0	1
T1	Standard Rate	20	1
T2	Exempt transactions	0	1
T3		0	1
T4	Sales to customers in EC	0	1	1
T5	Lower rate	5	1
T6		0	1
T7	Zero rated purchases from suppliers in EC	0	1		1
T8	Standard rated purchases from suppliers in EC	0	1		1	1	20
T9	Non-Vatable Tax Code	0	0

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

DSN-less ODBC connection for Sage

Sometimes you would prefer to have a DSN-less ODBC connection to your Sage data.

In Excel 2003, if you execute the following vba snippet, you'll get a DSN-less connection to your Sage demonstration company and a QueryTable containing the company name of the data at that location.

ActiveSheet.QueryTables.Add( _
    Connection:="ODBC;" & _
        "Driver={Sage Line 50 v18};" & _
        "DIR=C:\ProgramData\Sage\Accounts\2012\DemoData\accdata", _
    Destination:=Range("A1"), _
    Sql:="select Name from Company").Refresh

You should edit the snippet where it is highlighted in yellow to reflect your Sage version (e.g. Sage 2010 = v16, Sage 2011 = v17, Sage 2012 = v18, Sage 2013 = v19, etc ...) 

Once the QueryTable is created, you can edit the datapath in the Connection Dialog to access other company data and edit the query to access other tables.

Happy browsing!

Sage ODBC schema

I've found this to be a very useful resource. Respect to Rick!

http://rickvause.com/post/23345383645/sage-line-50-odbc-schema#disqus_thread

Sage Charts of Accounts by Business Type

Maybe I'm missing something but I'd have thought it would have been relatively easy to see details of the various Charts of Accounts supplied as standard with Sage accounting products. I haven't been able to find any so I went through the process of taking a backup and then completely destroying my company data to load each of the twelve charts of accounts in succession and recording the results.

I hope you will find the contents of the attached PDF useful in deciding which Chart of Accounts, if any, will be best for you.

Changing the font of OpenCart mail communications

Opencart has a "newsletter" email facility to communicate with customers. I recently used it for the first time and discovered that, whilst in drafting mode the font of the email text had looked something like Arial (acceptable to me), the text of the email was Times New Roman (I just can't like this font).

There then ensued a search for how to control the default font type of the outgoing email html.  I don't want to have to hand code the source HTML for every communication. 

I eventually found how to do it and share it here in the hopes that it will save someone else the long and frustrating search I had to locate something I would have hoped would be easily found online. Not so!

1. Locate the file admin/controller/sale/contact.php.
2. Go to line 230 in that file. The line reads: $message .= '  <body>' . html_entity_decode($this->request->post['message'], ENT_QUOTES, 'UTF-8') . '</body>' . "\n";
3. Change the text: '<body>' to '<body style="font-family: Arial, Helvetica, sans-serif; font-size: 12px; color: #000000;">' .
4. Save the file

The full line should read: $message .= '  <body style="font-family: Arial, Helvetica, sans-serif; font-size: 12px; color: #000000;">' . html_entity_decode($this->request->post['message'], ENT_QUOTES, 'UTF-8') . '</body>' . "\n";

Your communications should now be in the font of your choice by default.

Excel #VALUE! error in simple formula

I've come across the dreaded "space in a cell" scenario quite a few times in client Excel spreadsheets.  Some seem to think that [space]+[enter] is the same as [delete]. The trouble is that if you try to perform any arithmetic using the cell reference alone you'll get a #VALUE! error.
With the formula =A2+B2-C2 you'll get the error if you put non-numeric text in any of the three cells.

There are several different strategies I employ in these types of scenario. 

One observation is that the Sum() function will treat a space (or any text for that matter) as zero. So, instead of A2+B2-C2 you can write the formula as Sum(A2)+Sum(B2)-Sum(C2) or Sum(A2,B2)-Sum(C2). Note that you cannot write Sum(A2,B2,-C2) or a space character in cell C2 will provoke a #VALUE! error - it tries to calculate the negative of a space character before the Sum function has had a chance to turn it into a zero for arithmetic purposes. An element of error proofing is thus applied to your spreadsheets if you always wrap individual cell references with Sum().

However, you'll often come across this where it may be very time consuming to change an existing spreadsheet to employ this strategy.  In these cases I find that the Edit | Goto (F5 key) followed by Special... and then selecting the Constants radio button in combination with only the Text tick box identifies all cells with text in them.  The ones that look empty are the ones with spaces in them.  Delete the spaces and things should start working again.

Last, but not least, if there ought not to be any valid space characters in the highlighted cells, I would use search and replace.  A single space character goes in the "Find what:" field and the "Replace with:" field is left empty. 

I hope this helps.

Replacement function for GETPIVOTDATA

Why is GETPIVOTDATA so cumbersome to work with!  I'm always having to do stuff like:
=if(iserror(GETPIVOTDATA([parameter set])),0,GETPIVOTDATA([parameter set]))

It gets to be a real pain as I have to edit both [parameter set]s to have it work properly and there's always the possibility of making a mistake that is not immediately apparent.

I finally decided I'd create my own function that returned 0 instead of erroring:

Function GETMYPIVOTDATA(data_field, pivot_table, _
    Optional field1, Optional item1, _
    Optional field2, Optional item2, _
    Optional field3, Optional item3, _
    Optional field4, Optional item4, _
    Optional field5, Optional item5, _
    Optional field6, Optional item6, _
    Optional field7, Optional item7, _
    Optional field8, Optional item8, _
    Optional field9, Optional item9, _
    Optional field10, Optional item10, _
    Optional field11, Optional item11, _
    Optional field12, Optional item12, _
    Optional field13, Optional item13, _
    Optional field14, Optional item14)
Dim retval
Err.Clear
On Error Resume Next
retval = pivot_table.pivottable.GetPivotData(data_field, _
    field1, item1, field2, item2, field3, item3, field4, item4, _
    field5, item5, field6, item6, field7, item7, field8, item8, _
    field9, item9, field10, item10, field11, item11, field12, item12, _
    field13, item13, field14, item14 _
    )
On Error GoTo 0
GETMYPIVOTDATA = IIf(IsError(retval), 0, retval)
End Function

Now I just use =GETMYPIVOTDATA([parameter set])