- User Journeys
- Changes required to the core
- Financial Account Allow Tax up to 99.9999%
- Add 'Sales Tax Account is' to the possible list of relationships
- Allow multiple sales tax accounts to be linked to a single financial type
- Require Sales Tax accounts to be tax accounts
- Include Tax on display forms
- Keep the total tax amount against the contribution
- Generate additional trxn lines if a tax_amount is set
- Show Tax on View Contribution
- Show Tax on Contribution Receipts
- Allow Hook to amend the liability accounts used to calculate tax and the amount of tax
To be able to record a tax percentage against purchasable entities such as memberships, event fees and other financial types and produce the relevant accounting lines within CiviCRM.
- Tax is normally posted to a liability account
- Multiple tax’s can be applied to a single item such as state tax and city tax
- Customers need to be made aware of the tax element and associated costs on their purchase
- Additional Country/jurisdiction specific tax rules will be implemented via hooks or a full extension per country/jurisdiction
- There are benefits to having all extensions store their data in the same core fields even if each country's tax rules are in separate extensions
- There are benefits to having all extensions implement rules / hooks in a similar way
CiviAccounts has a variety of features that can be leveraged to allow the recording of tax related information for the relevant entities
The following are proposals for the steps a user would be expected to complete in order to indicate an item is taxable. Three alternate setups have been proposed.
[Sales Tax User Journey - Proposal 1] - Parvez Saleh
[Sales Tax User Journey - Proposal 3] - Lola Slade
Batching contributions for Export
I think the screens for creating the finance batches need some TLC. Some suggestions would be
- Batch Totals should be based on credit/debit amounts not just a summation of all contributions. In my case ($120 contribution including $5 fees, $20 Tax) the batch assigned total is showing $145. This seems wrong as its greater than the total value. If debits/credits where shown with account type summary it may make more sense. Whichever route is chosen the total should never exceed the total contribution value.
JM: Normally the fees are supposed to be deducted from the total amount of the transaction. Taxes as I understand it should be included in the Total. So the Total Amount should be $140 in the example (where the 'contribution' is actually a payment for a good or service that is taxable).
Output for Accounting Exports
An example of an export file is as below
Transaction Date Debit Account Debit Account Name Debit Account Type Debit Account Amount (Unsplit) Transaction ID (Unsplit) Payment Instrument Check Number Source Currency Transaction Status Amount Credit Account Credit Account Name Credit Account Type Item Description
01/05/2013 23:27 1150 Payment Processor Account BANK 120 Credit Card USD Completed 100 4400 Member Dues INC Contribution
01/05/2013 16:32 1150 Payment Processor Account BANK 120 Credit Card USD Completed 20 2202 VAT 50% Rate VAT50 VAT
01/05/2013 23:27 5200 Banking Fees EXP 5 Credit Card USD Completed 5 1150 Payment Processor Account BANK Fee
The above is based on
- Contribution: $100
- Tax $20
- Total payment $120 ($100 + Tax 20%)
- Credit Card Fees $5
JM: I have changed this to ensure there is a revenue account and balanced transactions. This results in $100 flowing to a revenue account, $20 to VAT account, with both matched against a payment processor account (balancing $120 against $120). See CiviAccounts Data Flow#OnlineorofflinecontributionwithFEE for prototype for how fees should be stored (nothing changes due to Sales Taxes being applied in transaction). See CiviAccounts Data Flow#ContributionwithSalesTax for description of additional records and changes to handle Sales Tax from default contribution case. To clarify with some sample records corresponding to the above:
civicrm_financial_trxn: id=x, amount=120, to_financial_account_id derived as per CiviAccounts Data Flow#Onlinecompletedcontribution (ie retrieve payment processor's financial account via linked civicrm_entity_financial_account), from_financial_account_id: NULL
civicrm_line_item: id=n, line_total=100.00 (NB: NOT 120, and there is NO additional line item for the sales tax, so for contributions with Sales Tax it is NOT true that sum of line_total for line_items = civicrm_contribution.total_amount)
civicrm_financial_item: two are created, both linked to civicrm_financial_trxn.id=x record as follow:
id=y (as spec'd for default contribution)
- entity_table: civicrm_line_item
- entity_id: n
- amount: 100.00
- financial_account_id: corresponding to member dues 4400 account
id=y+1 (new for sales tax)
- entity_table: civicrm_line_item
- entity_id: n
- amount: 20.00
- financial_account_id: corresponding to VAT50 2202 account
Also, add an additional civicrm_entity_financial_trxn record to link the civicrm_financial_trxn record with id=x, total_amount=120 to the new sales tax financial_item: entity_table = 'civicrm_financial_item', entity_id = y+1, amount = 20.00.
Interaction with Webforms payments
Webforms 4.4+ now allows to take payments for events / membership.
Given that all payments for webforms are processed through contribution pages we must check that the Tax rules apply to any payments made through webforms. (The contribution page should dictate the financial type of the transaction and therefor the same rules as above should apply - but this should be confirmed and if not working properly fixed).
From looking at the way the forms are set up I think it will only be possible to apply the same tax rate to all of the amounts on any single webform as there is no way to specify a financial type for each of the "amount" field elements. This will be fine for now but Coleman may in future want to visit this.
Users can write in the label of the appropriate Inclusive/exclusive from tax on the webform itself so no need to modify webforms to split this out.
Changes required to the core
Financial Account Allow Tax up to 99.9999%
The table definition of civicrm_financial_account now allows 8 decimal places (see
Add 'Sales Tax Account is' to the possible list of relationships
A separate Account Relationship is set up for each place in code where something different occurs depends on the linkage to the financial type. CiviCRM can support other types of liability accounts, such as accounts payable accounts for grants (coded via extensions) and refunds. Different Account Relationships will allow these different types of accounts payable accounts to be specified.
In order to link the tax account to the financial type an appropriate account payable relationship type is required
(@option_group_id_arel, 'Sales Tax Account is', 10, 'Sales Tax Account is', NULL, 0, 0, 10, 'Sales Tax Account is', 0, 1, 1, 2, NULL),
Add option to CRM_Financial_Page_AJAX to ensure liability accounts are returned when payable option is chosen
$financialAccountType = array(
'5' => 5, //expense
'3' => 1, //AR relation
'1' => 3, //revenue
'6' => 1, // asset
'7' => 4, //cost of sales
'8' => 1, //premium inventory
'9' => 3, //discount account is
'10' => 2, //sales tax liability
Allow multiple sales tax accounts to be linked to a single financial type
This will allow multiple tax rates to be applied to a single entity for example a state tax and a city tax.*
Note, to support putting two sales taxes in a specific order on forms, we will add a weight column.
Change xml/schema/Financial/EntityFinancialAccount.xml to include a weight field, and on upgrade add a weight field and fill with current id of each record. For field definition, something like:
ALTER TABLE civicrm_entity_financial_account ADD weight int(10) unsigned NOT NULL COMMENT 'determines order with higher weights lower on page and to the right';
Change the code in:
a) civicrm/admin/financial/financialType/accounts?action=update&id=9&aid=3&reset=1 to allow more than one relationship to be specified for 'Sales Tax Account is', and to expose the new weight field for editing, and
b) civicrm/admin/financial/financialType/accounts to support a weight column with standard links arrows up or down to rearrange entries in table.
JN - *To confirm that we would only require up to 2 sales accounts to a linked FT.
Require Sales Tax accounts to be tax accounts
When a Sales Tax Account is relationship is specified on civicrm/admin/financial/financialType/accounts, require the Financial Account to have Is Tax? enabled.
Include Tax on display forms
Keep the total tax amount against the contribution
Although there would be some data duplication, for reporting purposes it was agreed this would be best.
Generate additional trxn lines if a tax_amount is set
Add new method to CRM_Core_BAO_FinancialTrxn::recordTax($params). Note this method differs from the other methods, e.g. recordFees, as it will need to iterate through more than one liability tax account.
Call from CRM_Contribute_BAO_Contribution::recordFinancialAccounts
Conditional on CRM_Utils_Array::value('tax_amount', $params)
If all changes are made we should end up with the following additional data
- An extra row in civicrm_financial_trxn per liability account with the correct percentage break down
- JM: this seems wrong to me. There should be a single civicrm_finanacial_trxn linked to financial_items. See http://accounting-simplified.com/accounting-for-sales-tax.html for the basics on how to account for a sales tax.
- An extra row in civicrm_financial_item per new civicrm_financial_trxn record linking the transactions together
- JM: this seems wrong to me - if there is a state tax and city tax on each line item then there should two * number of line items more in the financial_item table, and no additional civicrm_financial_trxn records.
- Two record per liability in civicrm_entity_financial_trxn per liability account linking the contribution to the generated line items
- Correct errors above, and follow existing pattern for linking a financial_trxn to a financial_item (so there will be 2 * number of line items more entity_financial_trxn if each line item has two taxes).
Show Tax on View Contribution
Currently the view contribution page shows total/net/fee amounts. It should also show the tax amount(s) charged for the contribution, with the total for each tax on a separate line.
Show Tax on Contribution Receipts
The contribution receipts should also show the tax amount(s) charged for the contribution, with the total for each tax on a separate line.
Allow Hook to amend the liability accounts used to calculate tax and the amount of tax
In some situations a change to the sales tax accounts/rate is required. For instance in some countries which tax account represents the applicable provincial or municipal tax varies according to the purchase contact's region, which is derived from their address. Similarly, there may be a need to modify the default calculation of a sales tax based from being a percentage associated with the type of sales tax.