Sales Tax Handling in Keystone 2.3

Sales Tax handling in Keystone 2.3 builds on the changes from Keystone 2.2, where sales tax transactions are created for all invoices. In 2.3, sales tax changes resulting from cash posting transactions are also included in the tax transaction table. This is the latest design to accurately calculate sales tax based on collections, and for reporting sales tax adjustments for customers who report sales tax on invoices.

Invoice Transactions

I transactions result from invoices.

I transactions are consider taxable to customers who pay tax on invoicing, but not to customers who pay tax on receipts.

Cash Transactions

C Transactions include:

Cash Payment

Discounts (Note: if Discounts reduce sales tax, the tax incurred from paying the invoice will be reported as tax incurred, and the the resulting tax adjustment will be reported separately)

Allowances that are not exempt, and portions some Exempt Alowances.

C transactions are consider taxable to customers who pay tax on receipts, but not to customers who pay tax on invoicing.

Tax Adjustment Transactions

T Transactions are used to represent a change in Tax, Taxable and Exempt amounts, resulting from either a "Exempt" allowance or a "Tax Only" Allowance.

T transactions are considered as a sales tax adjustment to customers who pay tax on invoicing, but ignored for customers who pay tax on receipts. The changes from T transactions affect future C transactions, and in come cases will result in adjusting C transactions.

Allowance Transactions

A transactions are the allowance component for a cash posting Allowance.

Allowances that have no tax adjustment are treated similar to a Cash transaction.

Allowances that affect Tax Balances result in adjusting "C" type transactions to bring the tax collected balance in line with the invoice. One "C" transaction reverses all A, C and D transactions, then another reapplies the A,C and D total with based on the new adjusted tax balance.

Discount Transactions

For sales tax based on collection Discount transactions are treated similarly to cash transactions.

Discount Tax Adjustments are handled as a separate tax calculation.

Calculations

Common Cash Posting Case

For most cases, the calculation for tax collections is a simple ratio: Tax Collected=Invoice Tax X (Amount Paid / Invoice Gross) Taxable Sales Collected=Invoice Taxable X (Amount Paid / Invoice Gross)

For example a 1000 sale with 5% tax totals to 1050. If you receive a $500 payment then:

Tax Collected=50*(500/1050) --> $23.80952 which rounds to $23.81.

Taxable Sales Collected=1000 x (500/1050) --> $476.1905 -->$476.19.

So the numbers reported to the state would be a $476.19 sale with $23.81 tax.

For a partially taxable invoice the same rules apply, but now we'll need to calculate the exempt sales collected too. Exempt Sales Collected=Invoice Exempt X (Amount Paid / Invoice Gross)

For example a 1000 sale with 5% tax, $600 taxable and $400 exempt totals to 1030. If you receive a $500 payment then:

Tax Collected=30*(500/1030) --> $14.56311 which rounds to $14.56

Taxable Sales Collected=500 x (600 /1030) --> $291.2621 -->$291.26

Exempt Sales Collected=500 x (400 /1030) --> $194.1748 -->$194.17

So the numbers reported to the state would be 291.26 taxable, 194.17 exempt sales with $14.56 tax.

Discount Tax Adjustments

When a discount is posted to an invoice, two distinct transactions can occur...

• For systems with "Sales Tax Based on Collections" set, the portion of the invoice paid will be reported as paid along with the corresponding portion of sales tax due.
• For all systems, a sales tax adjustment calculation will be performed to determin if a tax reduction applies.

Discount Tax Fields

At the time of invoicing, the discount tax fields are calculated and entered into an "I" tax transaction record whenever a discount is offered. Every line of the invoice is analyzed.

• If either the "Discount Includes Tax" option is set in Tax Rate Maintenance, or a discount is on an exempt line, then the whole discount will be entered into the discount exempt line.
• If the "Discount Includes Tax" option is set in Tax Rate Maintenance then all discounts on taxable lines will included in the Disc Taxable and the Disc Tax fields. (The discount is apportioned based on the tax rate between the Disc Taxable and Disc Tax fields)

For example a 1000 sale with 5% tax, $600 taxable and $400 exempt totals to 1030. If a $21 discount is offered on taxable items then then Disc Exempt= $0, Disc Taxable =$20.00 and Disc Tax=$1.00

For example a 1000 sale with 5% tax, $600 taxable and $400 exempt totals to 1030. If a $20 discount is offered on exempt items then then Disc Exempt= $20.00, Disc Taxable =$0.00 and Disc Tax=$0.00

Calculating the Adjustment

At the time a discount is taken, the system calculates the percentage of discount taken, and determines the amound if discount tax adjustment. By applying bounds checking, the total amount of discount taken on an invoice is limited to between 0-100% of the original discount offered.

Boundary Checking

Sales tax boundary checking is the method the system never reports either negative more tax than was originally charged on an invoice (for positive invoices). This is to handle cases where invoices are either overpaid, or where a negative payment is applied to a postive invoice.

The basic rules (for a postive invoice)

• Tax collected is between 0 and tax charged.
• Taxable is between 0 and invoice sale amount (Original Invoice Amount lest Invoice tax charged).
• Exempt total is between 0 and invoice sale amount.
• Exempt 1-9 must be between 0 and invoice sale amount.

Diagnostics

Sales Tax History for a Customer/Invoice:

select i.cust_no,i.invoice_no,tx.*,i.credit_amount,i.debit_amount,i.sales_tax_due,I.DISC_OFFERED
  from artinv i
inner join arttaxtrx tx on  TX.ar_session_no=I.SESSION_no and tx.AR_trans_no=I.trans_no
where i.cust_no=:cust_no and invoice_no=:inv_no