GST Payments Basis scripts

We have created some SQL scripts to handle the NZ GST payments basis. For those who are reading this from outside of New Zealand, this means that we only need to pay to Inland Revenue (IRD) the GST portion on transactions once we have actually received the money from the client (in the case of A/R invoices that have GST charged on them) and can only claim GST from the IRD on A/P vouchers once we have actually paid for them. GST can also be accounted for on the accrual basis (mostly at the election of the taxpayer).

Most accounting systems are accrual based (including xTuple) so this makes the GST payments basis system hard to implement. Nevertheless, in an organisation where most of our A/P payments are up-front and we can wait for many months to get money in through our A/R, utilising the GST payments basis makes good cash flow sense.

I am sharing our scripts that we have created. They are very rough and were designed purely for our own use. You WILL need to modify them to be useful in your environment. They are SQL statements that can be executed from, e.g., the PGAdmin III SQL system on the appropriate xTuple database. They make no changes to the underlying xTuple database and only extract information. They were designed for xTuple 3.2.1, but may work on other versions too. I strongly recommend that only people with SQL experience make use of these scripts. The scripts take account of A/P part-payments and A/R part-receipts. The results of the scripts need to be imported into a spreadsheet to complete the calculation process (I did say this was rough, didn't I :-) ). I attach an example spreadsheet as well.

Accounting issues:
1. The scripts take no account of GST entries made via a system outside of the A/R invoicing system and A/P vouchering system. So manual Journal Entries, for example, are not covered. All A/R invoices and A/P vouchers must therefore specify the GST component in the invoice itself. Any entries outside of the A/R and A/P systems have to be separately (manually) accounted for. This includes adjustments made to the original voucher/invoice by Credit Memos and Debit Memos (C/Ms are always treated as payments so a C/M that, say, is posted to bad debts or prompt payment discounts or that reverses a transaction entirely will still be treated as a payment by this script, which must be contraed in the spreadsheet = a poor result. We are working on a solution to this, but it is some time off).
2. (Hopefully it goes without saying that) the extracted transactions need to be restricted to the appropriate date range to cover the appropriate GST period only.
3. (Again hopefully it goes without saying that) you must correctly account in xTuple for the date that you received money and paid money out so the correct values for arapply_distdate and apapply_postdate are used.
4. The A/P GST answer returned at the end has to be multiplied by 9 to get the GST supplies total as there is no practical way of extracting this information from the vouchering system other than by using the GST amount charged. This is not technically the correct answer as it should be worked the other way round for the GST return (taxable supplies calculate the GST paid), but it is near enough for our purposes (out by mere cents in any return). We have a number of vouchers that have GST standard items in them and GST zero rated items, so doing a calculation of the taxable supplies any other way becomes impractical. I also know from experience that other commercial accounting systems calculate this in the same way.

Technical things you need to do to make the scripts work for you:
1. Change the "(vodist_expcat_id=35 OR vodist_accnt_id=141)" portion of the A/P extraction script to match the appropriate GST expense category/ies (see the expcat table and get the approriate expcat_id. Ours happened to be 35) and Account (see the accnt table to find the appropriate accnt_id. Ours happened to be 141), respectively.
2. Modify the A/R extraction script or leave the script as-is and ensure that you account for A/R GST such that only the tax rate A field is used for GST (i.e. do not use rate B or rate C fields).

Technical things to watch out for:
The scripts extract all transactions (no date range restrictions). This can be time consuming and will fill up a spreadsheet quickly, so a WHERE clause date range specifying the arapply_distdate and apapply_postdate in the A/R and A/P scripts, respectively, could be used to limit the transactions to the appropriate period.

The way the scripts work:
A/R:
Script extracts all A/R applications and tracks these back to invoices issued.
The following fields are provided in the sample spreadsheet and correspond to the fields extracted from the A/R script:
1. Payment date = arapply_distdate
2. Payment amount = payment made in base currency (for us this is NZD)
3. Inv number = invoice number of the payment application
4. GST amount = Total amount of GST in the invoice
5. Inv Amount Zero rated = Non-standard GST amounts charged in the invoice
6. Inv. Amount Standard GST incl GST = Amounts charged (including GST) that were Standard rated.
7. Currency = What currency was the invoice issued in?
8. Invoice date = What date was the invoice issued?
9. Forex rate = What was the currency exchange rate between the invoice and the
10. Proportion paid = What proportion of the invoice was paid by this application.

Additionally, the spreadsheet provides further calculations to reach the final result. These are:
11. NZD GST amount = Calculates the GST amount in the Invoice in base currency using the GST Amount (4) and Forex rate (9)
12. Inv NZD Zero Rated = Calculates the Invoice Zero Rated total amount in base currency using the Inv. Amount Zero rated (5) and Forex rate (9)
13. Inv NZD Standard GST incl. GST = Calculates the Invoice Standard Rated total amount (incl. GST) in base currency using the Inv. Amount Standard GST incl GST (6) and Forex rate (9)
14. NZD GST amount proportion received = Calculates the amount of GST actually received in base currency in the payment using proportion paid (10) and NZD GST amount (11)
15. Inv NZD Zero Rated proportion received = Calculates the amount of GST actually received in base currency in the payment using proportion paid (10) and Inv NZD Zero Rated (12)
16. Inv NZD Standard GST incl. GST proportion received = Calculates the amount of standard rated (GST inclusive) sales actually received in base currency in the payment using proportion paid (10) and Inv NZD Standard GST incl. GST (13)

Fields 14, 15 and 16 are used in the the GST return itself (each field totalled for all transactions).

A/P:
1. Date paid = apapply_postdate
2. Voucher no. = self explanatory
3. Vendor name = self explanatory
4. Vendor inv.info = self explanatory
5. Reference of voucher = self explanatory
6. Total voucher amount incl GST = Total amount of the voucher including any GST, if applicable.
7. Voucher GST amount = Add up all of the line items in the voucher that are distributed to the GST expense category or to the GST account
8. Voucher Currency = self explanatory
9. Voucher date = self explanatory
10. Exchange rate of voucher = What exchange rate is necessary to convert the voucher to base currency (determined as at the voucher date)?
11. Payment amount = How much was paid in the payment currency?
12. Currency of payment = What currency was the payment made in?
13. Exchange rate of payment = Exchange rate of the payment currency to the base currency as at the date of the payment.

Additionally, the spreadsheet provides further calculations to reach the final result. These are:
14. Proportion of voucher paid = If the payment currency (12) and the voucher currency (8) are the same then divide the payment amount (11) by the voucher amount (6), otherwise first convert the payment amount (11) and voucher amount (6) to base currency using their forex rates (fields 13 and 10, respectively) and then do the calculation.
15. GST paid = voucher GST amount (7) multiplied by proportion paid (14) divided by exchange rate of payment (13) gives the amount of GST paid out in base currency.

Item 15 is then multiplied by 9 to get the GST supplies total as there is no practical way of extracting this information from the vouchering system other than by using the GST amount charged.

Final comments:
1. We have used the scripts to file our GST returns, so if you find anything wrong with these scripts or the underlying theory, PLEASE share your experiences so we can correct our returns!
2. Hopefully, this can become part of a shiny GUI in xTuple at some stage in the future. However, I do not have the time or the resources to create the necessary code to pretty this up. It works sufficiently well for us and hopefully it will be useful to others as well. If others want to make it part of xTuple proper, then they can pony up with the necessary time and resource to do it.
3. I am more than happy to respond to any questions, but please bear in mind that I am not paid for this.

Legal comments:
Use the materials (scripts and spreadsheet and this discussion) entirely at your own risk and expense. I and my organisation make no representations as to their correctness or fitness for purpose and refuse to be liable for any direct, indirect or consequential damages arising out of the use of these scripts and this supporting discussion. This means, for example, that if the IRD presents you with a NOPA requiring you to pay $1 million because the materials got it wrong, you are entirely on your own! If the materials manage to wreck your database/your computer/your life or your complexion begins to resemble the colour of pea soup after receiving said NOPA, again you are entirely on your own!
The scripts are released under the same licence as xTuple Postbooks Edition 3.2.1 is released under. You are licenced to change the scripts and spreadsheet and rewrite them in part or entirely, at your election. I will not be knocking on your door for royalty payments.