Sales Tax Logic

Understanding Sales Tax in Abaque

Managing sales tax shouldn’t require manual calculations for every entry. Abaque is designed to automate this process by linking your transactions directly to regional tax rules.

This page explains how the system identifies your tax jurisdiction, the underlying logic used to compute amounts, and how to configure your CoA and Journal to support your specific requirements—whether you are managing a tax-exempt business, simple regional sales taxes, or complex multi-jurisdictional VAT systems.

Jurisdiction Selection

Sales tax behavior in Abaque is determined by the jurisdiction selected in the Journal.

  • International Templates use the STJUR column.
  • Canadian Templates use the PROV column.

The jurisdiction controls:

  • Which tax rates apply.
  • The tax structure: Standard Sales Tax and VAT for International Templates, and GST/HST and PST/RST/QST for Canadian Templates.
  • Which CoA accounts receive collected and paid tax amounts.
  • Whether the jurisdiction requires single-account tracking (Standard Sales Tax, PST, or RST) or two-way posting (VAT, GST/HST, or QST).

Tax Calculation Logic

All tax calculations in Abaque are based on the value entered in the Journal’s AMOUNT column. Templates interpret this value as a pre-tax amount.

International Templates

In International Templates, sales tax behavior is not predefined. Instead, it is driven entirely by the configuration string selected in the Journal’s STJUR column. This model allows Abaque to adapt to any global tax region without changing the underlying spreadsheet code.

The STJUR Configuration String

Each item in the STJUR dropdown encodes four essential elements separated by specific delimiters:

  • Jurisdiction ID: Two uppercase letters (e.g., CA, UK, DE).
  • Account ID: The primary account in the CoA for collected tax.
  • Tax Type: A flag where 0 = Standard Sales Tax and 1 = VAT.
  • Tax Rate: The percentage expressed as a decimal or percentage (e.g., 7.25 or 20).

These elements are combined into a single configuration string. For example, a jurisdiction with code CA, collected-tax account 2300, Standard Sales Tax type 0, and rate 7.25% is represented as:

CA␋2300/0/7.25

The Hidden String: To keep the Journal clean, Abaque uses a vertical tab character (represented graphically as ␋). This ensures that only the Jurisdiction ID is visible in the cell after selection, while the full logic string remains available to formulas.

Note: Because the ␋ character (Vertical Tab) requires specific OS-level keystrokes to generate manually, do not attempt to copy the example above. Instead, copy an existing entry directly from the Data validation rules sidebar for range G2:G in the Journal. Modify the IDs, type or rate as needed, ensuring this hidden delimiter remains untouched.

Account Usage

International sales tax accounts (the second element in the string) must be created within the reserved liability range 2300–2399. These accounts are mapped to the liability GIFI code 2680 — Taxes payable and are linked to the right sales tax jurisdiction based on the Journal’s STJUR configuration string.

VAT vs. Standard Logic

The Tax Type flag (the third element in the string) dictates how Abaque handles the accounting entries:

  • Standard (Type 0): Used for non-refundable taxes. The engine posts only to the single Collected account ID specified in the string (within the 2300-2399 range).
  • VAT (Type 1): Used for refundable taxes. The engine expects two consecutive accounts in the CoA: the account specified in the string (e.g., 2304) for VAT Collected, and the next account number (Account + 1, e.g., 2305) for VAT Paid.

Default Samples

International Templates include four pre-configured samples (two Standard, two VAT). These serve as boilerplate strings for users to delete, modify, or extend their own regional tax requirements within the STJUR Data validation rules sidebar.

Tax Column Logic

The Journal’s STAX column uses the following formula to calculate tax using the STAX_RATE() Syntax: = STAX_RATE() Parameters: None (Extracts rate from Column G). Return: Decimal (e.g. 0.0725) Implementation: = REGEXEXTRACT(INDIRECT('G' & ROW()), '[\d\.,]*$') / 100 Example: = ROUND($E2 * STAX_RATE(), 2) named function:

= ROUND($E2 * STAX_RATE(), 2)

Canadian Templates

Abaque utilizes the Journal’s PROV column to trigger federal and provincial named functions. This model manages the complexities of GST, HST, and various provincial taxes (PST, RST, and QST) through a combination of automated logic and predefined account structures.

Canadian Tax Model

Canada employs a dual federal-provincial tax system. While the engine handles most calculations automatically, the treatment of provincial taxes depends on their "Refundable" status:

  • Refundable Taxes (GST/HST & QST): These are tracked separately for sales and purchases. The engine calculates the amount and posts to specific Collected or Paid accounts (e.g., 2400 vs 2405).
  • Non-Refundable Taxes (PST in BC/SK, RST in MB): These are treated as a cost of doing business. For sales, the tax is collected (e.g., 2431); for purchases, the tax is simply included as part of the total expense rather than being tracked in a separate tax-paid account.

Account Usage

Accounts share the liability GIFI code 2680 — Taxes payable for both collected (positive balance) and paid (negative balance). The account numbers provide the functional distinction required by the engine:

Accounts Description
2400 / 2405 GST/HST Collected on Sales / Paid on Purchases
2430 / 2435 QST Collected on Sales / Paid on Purchases
2431 – 2433 PST/RST Collected

Tax Columns Logic

Refer to the table below to understand how taxes are computed based on the PROV column. The general usage for formulas is:

= ROUND($E2 * <NAMED_FUNCTION>, 2)

Named Function Column Description
GST_HST_RATE() Syntax: = GST_HST_RATE() Parameters: None (reads PROV column). Return: Decimal (5%, 13%, 14%, or 15%) Implementation: = LET(prov, INDIRECT('H' & ROW()), rate, IFS(OR(prov='NB', prov='NL', prov='PE'), 15%, prov='NS', 14%, prov='ON', 13%, prov='', 0, TRUE, 5%), rate) Example: = ROUND($E2 * GST_HST_RATE(), 2) GST/HST Returns the federal GST or HST rate based on the province selected in the Journal.
GST_RATE_ON_PST_INCLUDED() Syntax: = GST_RATE_ON_PST_INCLUDED() Parameters: None. Return: Adjusted GST rate (e.g., 0.0467) Implementation: = GST_HST_RATE() / (1 + PST_RATE()) Purpose: Back-calculates net amount before GST in BC, MB, and SK. GST/HST Used to “back-calculate” the GST amount when a purchase price already includes non-refundable provincial tax (BC, MB, SK). This represents the only exception to the pre-tax AMOUNT rule mentioned at the beginning of this section.
PST_RATE() Syntax: = PST_RATE() Parameters: None (reads PROV column). Return: Decimal (7%, 9.975%, or 6%) Implementation: = LET(prov, INDIRECT('H' & ROW()), rate, IFS(OR(prov='BC', prov='MB'), 7%, prov='QC', 9.975%, prov='SK', 6%, TRUE, 0), rate) Example: = ROUND($E2 * PST_RATE(), 2) PST Returns the applicable PST, RST, or QST rate.

Reset Tax Logic

A sales tax reset marks the beginning of a new tax filing cycle and must be the first transaction recorded after the previous period closes (e.g., a reset for a period ending March 31 should be the first entry dated April 1). This transaction zeroes out accumulated tax accounts by moving the net balance to either 1166 (Refund due) or 2438 (Payment due).

The Filing Workflow

Managing sales tax in Abaque follows a three-step process to ensure technical accuracy and clear reporting for any tax jurisdiction:

  1. Perform the Reset and Capture Balances:
    • Before entering the reset transaction, you must note the current balances of all individual tax accounts (e.g., Tax Collected, Tax Paid, etc.). These are your captured balances.
    • Record the reset transaction in the Journal on the first day of the new period. This moves the total net amount to 1166 or 2438, zeroing out the individual tax accounts so they can begin tracking the new period from scratch.
  2. Report and File: Use your captured balances to fill out the lines of your tax return document. Once the filing is submitted to the authorities, record a journal entry to move the amount from the reset accounts to 1060 | 1100 | Accounts Receivable (for refunds) or 2621 | 2100 | Accounts Payable (for payments).
  3. Settle the Balance:
    • Refunds: When the reimbursement is received in your bank account, debit the bank account and credit 1100.
    • Payments: When the payment is sent to the tax authority, debit 2100 and credit your bank account.

Recognition Pattern

  • Debit account: 1066 | 1166 | Sales Tax Refund
  • Credit account: 2680 | 2438 | Sales Tax Payable
  • AMOUNT column: Leave blank.
  • Tax column(s): Enter the total reset amount(s).

Validation

Abaque verifies that the reset amount matches the total balance of the tax accounts in the CoA. If there is a discrepancy, the reset is rejected and an error message is displayed.

Rounding

All tax formulas use ROUND(value, 2) to ensure that tax account balances align perfectly with the currency amounts used during resets and filings.

Sales Tax Configuration

The adjustments outlined below are only required if you wish to permanently remove unused CoA accounts and Journal’s sales tax related columns to streamline your interface, or if you must configure specific international tax attributes to meet local requirements. If you proceed with any modifications, it is critical that the sales tax accounts within the Liability section of your Balance Sheet remain perfectly aligned with your specific tax obligations by following the scenarios below that match your template type and configuration action.

Note: If your business is not registered with a tax authority, these changes are optional. You can leave the STJUR or PROV columns blank or enter ‘0’. Abaque will treat these as non-taxable entries without requiring any back-end modifications.

Internationational Templates

If your business does not handle sales tax, you can simplify the Abaque interface by reconfiguring the engine to ignore international tax metadata and removing the tax related columns from the Journal ​​​​​​.

  • Step 1: Update the CoA
    • Custom Function Arguments: You must update the GIFI_21_BALANCE function call in cells I3 and K3:
      • Range Adjustment: Change the second parameter (the Journal reference) from '<Journal>'!C2:G to '<Journal>'!C2:E.
      • Flag Removal: Remove the HEX2DEC(80) bit flag passed to the fourth parameter. This reverts the engine to default sales tax processing logic.
    • Account Removal: Delete the sample liability rows for accounts 2300 through 2307. Additionally, remove the tax reset accounts: 1066 | 1166 | Sales Tax Refund and 2680 | 2438 | Sales Tax Payable.
  • Step 2: Cleanup the Journal
    • Column Removal: Delete columns F, G, and H (STAX, STJUR, and TOTAL). This leaves your Journal with the following standard columns: DATE, ITEM, DEBIT, CREDIT, AMOUNT, and REMARKS.

For businesses that collect sales tax or VAT, the international template must be customized to reflect the specific authorities with which you are registered. Since the default template includes sample rows for multiple jurisdictions, you should prune and repurpose these to reflect your actual filing requirements.

  • Step 1: Update the CoA
    • Review Liability Rows: Examine the account number range 2300–2399:
      • To Remove: Delete any rows for tax types you do not collect. Note: Always delete the entire row.
      • To Repurpose: Rename the remaining rows to match your local tax names (e.g., “VAT 15% Collected”).
      • VAT Requirement: For VAT, you must use two consecutive account numbers: one for Collected and the very next for Paid (e.g., 2304 and 2305). Refer to the VAT vs. Standard Logic section for the “Account + 1” mapping.
      • To Add: If you require more rows, use GIFI code 2680 — Taxes payable, ensuring all account numbers in Col E remain in strictly ascending order.
  • Step 2: Update the Journal
    • Data Validation: Update the Data Validation rules for the STJUR column (Col G) based on your sales tax liabilities as per The STJUR Configuration String instructions above.

Canadian Templates

If your business does not handle sales tax, you can simplify the Abaque interface by reconfiguring the engine to ignore Canadian and provincial tax metadata and removing the tax related columns from the Journal.

  • Step 1: Update the CoA
    • Custom Function Arguments: You must update the GIFI_21_BALANCE function call in cells I3 and K3 by changing the second parameter (the Journal reference) from '<Journal>'!C2:H to '<Journal>'!C2:E.
    • Account Removal: Delete the sales tax liability rows for accounts 2400 through 2435. Additionally, remove the tax reset accounts: 1066 | 1166 | Sales Tax Refund and 2680 | 2438 | Sales Tax Payable.
  • Step 2: Cleanup the Journal
    • Column Removal: Delete columns F, G, H, and I (GST/HST, PST, PROV, and TOTAL). This leaves your Journal with the following standard columns: DATE, ITEM, DEBIT, CREDIT, AMOUNT, and REMARKS.

If your business only handles GST/HST, you can simplify the Abaque interface by reconfiguring the engine to ignore the PST and PROV columns as well as removing the PST and potentially the PROV columns from the Journal.

  • Step 1: Update the CoA
    • Custom Function Arguments: You must update the GIFI_21_BALANCE function call in cells I3 and K3 by changing the second parameter (the Journal reference) from '<Journal>'!C2:H to '<Journal>'!C2:F.
    • Account Removal: Delete the provincial sales tax liability rows for accounts 2430 through 2435 used for QST/PST/RST.
  • Step 2: Update the Journal
    • Province Override: If you intend to delete the PROV column because you are doing business in a single province where only GST/HST applies, you must update the GST_HST_RATE() named function to set the province explicitly. For instance, if you are in Ontario, click on Data → Named Function, select the function just mentioned and change this portion of its implementation:
      LET(prov, INDIRECT("H" & ROW()))
      to
      LET(prov, "ON")
    • Column Removal: Delete column G (PST). If you performed the modification to the named function mentioned above, you must also delete column H (PROV).

If your business handles a single provincial sales tax (PST, QST, or RST), you can simplify the Abaque interface by reconfiguring the engine to ignore the PROV column and removing it from the Journal.

  • Step 1: Update the CoA
    • Custom Function Arguments: You must update the GIFI_21_BALANCE function call in cells I3 and K3:
      • Range Adjustment: Change the second parameter (the Journal reference) from '<Journal>'!C2:H to '<Journal>'!C2:G.
      • Flag Addition: If your province of taxation is not Quebec (the default), you must pass the corresponding jurisdiction ID to the fourth parameter:
        • BC: HEX2DEC(1)
        • MB: HEX2DEC(2)
        • SK: HEX2DEC(3)
      E.g.
      • Cell I3: =GIFI_21_BALANCE(D3:E, INDIRECT("Journal!C2:G"), M3:M, HEX2DEC(1))
      • Cell K3 (Trial Balance): =GIFI_21_BALANCE(INDIRECT("D3:E" & TEXT(MAX(ARRAYFORMULA(IF(LEN(D:D), ROW(D:D), 0))) - 1, "0")), INDIRECT("Journal!C2:G"), , BITOR(HEX2DEC(1), HEX2DEC(400)))
        Note: HEX2DEC(400) is used here to return the trial balance instead of accounting items.
    • Account Removal: Delete the provincial sales tax liability rows that are not needed. In the range 2430 through 2435, you might keep only 2431 if you are exclusively collecting BC Provincial Sales Tax.
  • Step 2: Update the Journal
    • Province Override: You must update the GST_HST_RATE() and PST_RATE() named functions to set the province explicitly. For instance, if we continue with the BC example, click on Data → Named Function, select the functions just mentioned and change this portion of their implementations:
      LET(prov, INDIRECT("H" & ROW()))
      to
      LET(prov, "BC")
      Note: You may also update the “Function description” field to reflect that this is now a hardcoded regional setting.
    • Column Removal: Delete column H (PROV) from your Journal.

By default, Abaque supports all provincial sales taxes. If your business handles multiple provincial taxes (PST, QST, or RST) but not all of them, you can simplify your interface by removing unused liability accounts and their corresponding entries in the Journal.

  • Step 1: Update the CoA
    • Account Removal: Delete the provincial sales tax liability rows that are not needed within the range 2430 through 2435.
  • Step 2: Update the Journal
    • Data Validation: Update the Data Validation rules (Data → Data Validation) for the PROV column (Col H). Remove the province codes for jurisdictions where you do not collect tax. This must match the deletions made in the CoA in Step 1 to ensure the engine remains synchronized.