Projects:MultiTaxReport/Functional Specification
Functional Specifications
Functional Specifications sign-off
Role | Name | Sign-off date |
Dev | David Alsasua | |
GPS | Juan Pablo Calvente | |
GPS | Jaime Chamorro | |
QA | Pablo Sarobe |
Section 1: Overview & Justification
Purpose
The purpose of this document is to explain how existing "Invoice Taxes Report" must be enhanced to help the users to manually fill in and submit some Tax Reports such as few Spanish ones.
"Invoice Taxes Report" enhancements are going to be implemented as a new commercial module (extension module) which will require OB Commercial License as a new report named "Multidimensional Tax Report".
The "Invoice Tax Report" enhancements described in this document could be extensible to others countries regardless this analysis is based on the Spanish Tax Requirements and the Spanish "GPS" team input, because the main goal of those enhancements is to allow the end-user to exploit the tax information registered in the system.
Overview and Justification
There are two types of Tax Reports:
1) “Statement” Tax Reports.
There are some tax reports that are complex to fill-in and submit to the tax authorities because they need to retrieve or take into account data from many sources besides the tax records saved in the system.
At the same time, the information required while being generated is also complex and cannot be easily handled by what we call a “Multidimensional Tax Report”. "Multidimensional Tax Report" concept is explained later.
This is the case of M-347, M-349, M-340 tax reports.
These reports must be implemented through the Tax Report Launcher, therefore those ones can be generated as a valid file to be submitted to the tax authorities in the AEAT web-site.
2) “Settlement” Tax Reports.
These reports are not so complex than the previous ones because its main purpose is to get a tax settlement based on tax records saved in the system.
These reports can be handle by using a “Multidimensional Tax Report” which can help the end-user to get the basis information in order to fill-in manually these tax reports in the AEAT web-site.
This is the case of M-303 and M-110 tax reports, M-303 is a VAT settlement tax report and M-110 is a Withtholding settlement tax report (of some kind of withholdings).
In addition, the "Multidimensional Tax Report" could also help the end-user to manually fill in few other tax reports that are not planned to be delivered now as part of the next version of the SPLP such as M-190, (which is the M-110 anual version) M-390 (which is the -303 annual version); and few others such as M-115, M-216, M-123 withholding tax reports. Anyway, an additional research is required to better understand if they can be filled-in manually by the end-user by using the "Multidimensional Tax Report" or not and how.
Section 2 : Scope
The approach Localization team is going to take in order to get (2) "Settlement Tax Reports" is to enhance the standard Openbravo "Invoice Taxes Report" as a Multidimensional Tax Report.
Invoice Taxes Report can be found now at the application path: Financial Management // Receivable & Payables // Analysis Tools // Invoice Taxes Report.
New Report could be found at the application path:Financial Management // Receivable & Payables // Analysis Tools // Multidimensional Tax Report.
Section 3 - Functional Requirements
Prior to any Functional Requirement definition, it is important to highlight that:
- Tax Rates such as a VAT, which are the taxes calculated and posted when an Invoice is posted:
- can be setup at the application path: "Financial Management || Accounting || Setup || Tax Rate || Tax"
- and can either be purchase or sales tax type and therefore will be shown in the Multidimensional Tax Report as either part of the Sales/Receipt Transactions or the Purchase/Payments Transactions.
- Withholding Rates (or Withholding on Invoice) calculated and posted when an Invoice or a Manual Settlement is posted:
- can be setup at the application path: "Financial Management || Accounting || Setup || Tax Rate || Tax", setup as "Withholding Tax".
- and can either be purchase or sales tax type and therefore will be shown in the Multidimensional Tax Report as either part of the Sales/Receipt Transactions or the Purchase/Payments Transactions.
- Withholdings (or Withholding on Settlement) calculated and posted when an Invoice or a Manual Settlement is settle and posted:
- this type can be setup at the application path: "Master Data Management || Business Partner Setup || Withholding || Withholding" and once created can be:
- assigned to a Vendor BP at the application path: Master Data Management || Business Partner || Business Partner >> Withholding
- In this scenario, this type of withholdings can only be purchase type and therefore will be shown in the Multidimensional Tax Report just a part of the Purchase/Payments Transactions
- and linked to a GL item at the application path: Financial Management || Accounting || Setup || G/L Item ||
- In this scenario, this type of withholdings can be used either while creating or creating/posting a Manual Settlement, or while posting a Bank Statement or a Cash Journal.
- assigned to a Vendor BP at the application path: Master Data Management || Business Partner || Business Partner >> Withholding
- this type can be setup at the application path: "Master Data Management || Business Partner Setup || Withholding || Withholding" and once created can be:
Overall, there are 2 possible setup:
- End-user setting up and using Tax and Withholding Rates for purchase/sales invoices.
- End-user setting up and using Tax Rates for purchase/sales invoices and Withholdings just for purchase transactions.
- Withholdings on payment can be used for rest of document types (Manual Settlements, ...)
Above means it is not likely end-user is going to use both Withholding Rates and Withholdings within the same Entity.
As described in above sections the Functional Requirements of this feature implementation are a List of "Invoice Taxes Report" enhancements in order to get a Multidimensional Tax Report:
Functional Requirement 1 - Tables
"Invoice Taxes Report" data will be retrieved from the management tables listed below (Not from the accounting table FACT_ACCT) :
- c_invoice
- c_invoicetax
- c_debt_payment
- c_bankstatmentline
- c_cashline
- gl_journalline
- c_debt_payment_balancing
Functional Requirement 2 - Data
Overall It is important to remark that G/L items do not linked to either a "Withholding Tax" or a "Withholding" will not be taken into account.
Therefore either Bank Statement/Cash Journals do not linked to a Payment but to a G/L item will not be taken into account unless those G/L items are linked to either a Withholding Tax or a Withholding.
Besides, an in order to get reliable tax data we need to take into account below documents/transactions:
- AP/AR Invoices & AP/AR Credit Memos used to post received and issued invoices/credit memos, which could contain:
- Taxes such as VAT and Withholding Taxes, in the Invoice Line field named "Tax"
- Note = 1 above should be captured at Invoice posting (Tax tab), for more info see Use Case 1
- Withholdings, in the Invoice Header
- Note = 2 above should be capture at Settlement (Cancelled Payments tab), for more info see Use Case 2
- Taxes such as VAT and Withholding Taxes, in the Invoice Line field named "Tax"
- Manual Settlement used to create transactions to be settle not coming from an Invoice, which could contain:
- A G/L item linked to a "Tax Rate" (we can not think on a real scenario for this) and/or a G/L item linked to a "Withholding Rate", for more info see Use Case 3.
- A G/L item linked to a "Tax Rate" (we can not think on a real scenario for this) and/or a G/L item linked to a "Withholding", for more info see Use Case 4.
- Bank Statement used to reconcile bank statements, which could contain:
- An Invoice linked to a "Withholding Rate"
- Note = 1 above should be captured at Invoice Posting (Tax Tab), for more info see Use Case 1.
- An Invoice linked to a "Withholding"
- Note = 2 above should be captured at Settlement (Cancelled Payments tab), for more info see Use Case 2.
- A Manual Settlement linked to a "Withholding Rate"
- Note = 3 above should be captured at Manual Settlement Posting, for more info see Use Case 3.
- A Manual Settlement linked to a "Withholding"
- Note = 4 above should be captured at Settlement (Cancelled Payments tab), for more info see Use Case 4.
- A G/L item linked to either a "Tax Rate" (we can not think on a real scenario for this) or a "Withholding Rate" or a "Withholding"
- Note = 5 above should be captured at Settlement (Cancelled Payments tab), for more info see Use Case 5
- An Invoice linked to a "Withholding Rate"
- Cash Journal used to manage company's cash transactions, which could contain:
- An Invoice linked to a "Withholding on Invoice" (Cash Type = Debt-Payment)
- Note = 1 above should be captured at Invoice Posting (Tax Tab), for more info see Use Case 1.
- An Invoice Payment linked to a "Withholding on Settlment" (Cash Type = Debt-Payment)
- Note = 2 above should be captured at Settlement (Cancelled Payments tab), for more info see Use Case 2
- A Manual Settlement linked to a "Withholding Rate" (Cash Type = Debt-Payment)
- Note = 3 above should be captured at Manual Settlement Posting, for more info see Use Case 3.
- A Manual Settlement linked to a "Witholding" (Cash Type = Debt-Payment)
- Note = 4 above should be captured at Settlement (Cancelled Payment tab), for more info see Use Case 4.
- A G/L item linked to either a "Tax Rate" (we can not think on a real scenario for this) or a "Withholding Rate" or a "Withholding"
- Note = 5 above should be captured at Settlement (Cancelled Payments tab), for more info see Use Case 5
- An Invoice linked to a "Withholding on Invoice" (Cash Type = Debt-Payment)
- Settlements in case of payment cancellation including a withholding coming from a Cash Journal or a Bank Statement.
- Remittance in case of a payment/payments including withholding to be collected in a Remittance (application path: Financial Management // Receivables & Payables // Transactions // Remittance) the corresponding withholding must be post at the time/date those payments are cancelled which means set to "Paid=Yes"; the fact of collecting payments into a remittance will not imply any withholding posting because that do not implies any payment/receipt but just a payment management.
- Payment Status Management does not get any impact on this development as payment management do not imply those payments are paid and that is the key for us to take into account in case of payments containing a withholding; only when those are set to Paid=Yes will be taken into account.
- In case payment management means that the payment is moved or settle against a different Business Partner, that one will be taken into account instead of the original one set up at Invoice level.
It is important to remark that in case of "Tax Payment Settlement" end user should follow below steps:
- Create a G/L Journal to post the corresponding Tax Settlement (4720 vs 4770 account in case of VAT, by example)
- Create a G/L item named "Tax Payment Settlement" linked to 4750 account and do not linked to any Tax or Withholding Rate or Withholding
- Settle that G/L item through a Bank Statement or Cash Journal (Bank Statement or Cash type = G/L item), see Use Case 5.
Functional Requirement 3 - Accounting Date
Transaction/Document Accounting date must be the date to take into account while retrieving tax transactions (*) see FR4 - Existing filters.
Functional Requirement 4 - Filters
There are a few new filters to be implemented:
Invoice Tax Reports has the following filter parameters:
| | | | |
From Date (*) | | | "From Date" to be entered by the end-user, system will retrieve tax transactions taken into account the "From Date" entered by the end-user. | Document/Transaction Accounting Date is the date to be taken into account. |
To Date (*) | | | "To Date" to be entered by the end-user, system will retrieve tax transactions taken into account the "To Date" entered by the end-user. | Document/Transaction Accounting Date is the date to be taken into account. |
Organization | | | "Organization" to be selected by the end-user from a reference list, system will retrieve tax transactions for the Organization selected. | none |
Show Invoice info | | | In case the end-user selects this check box below information must be displayed:
| This check must be renamed as: "Show Detail Info" as it should also allow the end user to get information about:
Besides:
|
Purchase Transaction | | | In case the end-user selects this check system will retrieve tax transactions linked to purchase transactions | This check must be renamed as: "Purchase/Payment Transaction" because not only invoices must be taken into account but also payments |
Sales Transaction | | | In case the end-user selects this check system will retrieve tax transactions linked to sales transactions | This check must be renamed as: "Sales/Receipt Transaction" because not only invoices must be taken into account but also receipts |
Both Transaction | | | In case the end-user selects this check system will retrieve tax transactions regardless its type. | none |
Following new filters are required to be implemented additionally, as shown in the screen below, (including the existing ones to be changed):
| | | |
Tax
Withholding | | | Two options to split between Tax or Withholding.
In case end-user selects "Withholding", the system will retrieve tax transactions linked to Tax Rates setup as withholdings or Withholding, depending on Withholding selectors described below. |
Tax | | | In case the end-user selects "Tax" option in the "Tax or Withholding" radio button, system will allow the end user to select a Tax Rate; otherwise any Tax Rate will be taken into account while retrieving tax transactions. |
Withholding on Invoice | | | In case the end-user selects a "Withholding on Invoice" or "Withholding rates", system will retrieve tax transactions linked to that specific tax Rates setup as Withholding Rates; otherwise system will retrieve tax transaction regardless its Withholding rate or type.In case end-user selects a Withholding on Invoice rate, "Withholding on Settlement" selector will be set to not editable. |
Withholding on Settlement | | | In case the end-user selects a "Withholding on Settlement" tax rate, system will retrieve tax transactions linked that specific withholding; otherwise system will retrieve tax transactions regardless its Withholding rate or type.In case end-user selects a Withholding on Settelment rate, "Withholding on Invoice" selector will be set to not editable. |
Business Partner | | | This multiple selector will allow the end-user to select a specific group of BP’s, therefore the system will retrieve the tax transactions linked to them.
|
Group by Business Partner | | | It this check is marked the report will show the tax information grouped by BP. |
Functional Requirement 5 - Report Layout
The current tax information displayed is not clear enough and it does not help the end-user to manually filled-in tax reports; therefore output layout must be re-designed the way it is described below:
Report - Header
Invoice Taxes Report must clearly state the transaction type for which tax information/transactions is being retrieved, therefore Header section must show any of the tax transaction types listed below:
- Sales/Receipts
- Purchase/Payments
Report - Lines
Multidimensional Tax Report must contain below information at lines level per each Tax or Withholding Rate, in case "Show Details info" is checked:
| | |
Name | | Tax or Withholding Name |
Rate | | Tax Rate or Withholding rate in % |
Doc No | | Invoice number in case of invoice transactions, document number in case of payments/receipts |
Doc Type | | Document type: AP/AR invoice, Manual Settlement, etc.. |
Doc Date | | Invoice date in case of invoice transactions, document date in case of payments/receipts |
Acct Date | | Accounting date |
BP | | Business Partner |
BP Country | | Business Partner Country |
BP Region | | Business Partner Region |
Taxable Amount (to be renamed as "Tax Base Amount") | | Tax Base Amount |
Tax Amount | | Tax or Withholding Amount |
Total Amount | | Total amount including tax and/or Withholding |
Report - Sub-Totals
Above "Number" type columns but "Rate" must be sum-up in an additional report line in order to get below Sub-Totals:
- Total Tax Base Amount
- Total Tax Amount
- Total Amount
Report - Totals per Tax Category
Multidimensional Tax Report must also show under Sub-Totals line and before the next Report Summary section a "Totals" section per Tax Category.
End-user will be able to get information about:
The Summed-up Tax Base Amount, Tax Amount and number of BP per each Tax Category; split by Tax Rate (%), which means in the case of Spain:
- Tax Categories = VAT16%, VAT16%Services, VAT16%RealEstate, VAT7%, VAT7%Services, VAT7%RealEstate, VAT4%, VAT4%Services
- Tax Rates =
- VAT Rates = 16%, 7%, 4%, 0,5%, 1%, 4% and 0%
- Withholding Rates = 7%, 15%, 19% and 24%
Tax Category / Tax Name | Rate | Tax Base Amt | Tax Amt | |
VAT 16% | 16
| |||
IVA 16% Import
| 16
| |||
IVA 16% National Purchase
| 16
| |||
IVA 16% National Purchase (RE 4%)
| 4
| |||
....others... | ||||
VAT 7% | 7
| |||
IVA 7% Import
| 7
| |||
IVA 7% National Purchase
| 7
| |||
IVA 7% National Purchase (RE 1%)
| 1
| |||
....otros... |
New!!! = There should also be a Business Partner summary per tax/withholding rate %, therefore end-user is informed about the number of BP related to a specific rate.
This information is useful for the Spanish tax report 110 in which the Number of BP related to a specific type of withholdings (either employment incomes or economics/professional services) must be submitted.
Report - Totals per Tax/Withholding rate (%)
Regardless the value selected in the "Tax or Withholding" radio button, Multidimensional Tax report must show below summary information at the end of the report, grouped first by Tax or Withholding Rate (including Tax Rate setup as "Withholding Tax" and "Withholdings") and besides by Business Partner in case the end-user selects that Filter:
| | |
Name | | Tax or Withholding Name |
Rate | | Tax or Withholding Rate |
Total Tax Base Amount | Number | Total Tax Base amount per Tax or Withholding Rate |
Tax Amount | | Total tax amount |
Total Amount | | Total amount including Tax or Withholding amounts. |
Number of BP | | Total sum of BP with the specific Tax or Withholding Rate (if a BP has several invoices, for example, with the same withholding the BP counts only once). |
Report - Lines
Multidimensional Tax Report must contain below information at lines level per each Tax or Withholding Rate, in case "Show Deno Taxtails info" is NOT checked:
| | |
Name | | Tax or Withholding Name |
Rate | | Tax or Withholding Rate |
Tax Base Amount | | Tax Base amount per Tax or Withholding Rate |
Tax Amount | | Total tax amount per Tax or Withholding Rate |
Total Amount | | Total amount including Tax or Withholding amounts. |
Number of BP | | Total Sum of BP with the specific Tax or Withholding Rate (if a BP has several invoices, for example, with the same withholding the BP counts only once). |
Multidimensional Tax Report must be export to Excel format by checking the corresponding "Menu" button"
Functional Requirement 6 - Tax and Withholding management
Tax and Withholding management as either sales/purchase rates in case of not Invoice related transactions:
- Withholdings are going to be handle as purchase/payment withholding tax.
- Tax and Withholding Rates are going to be handle as purchase/payment tax in case of Purchase Tax type being setup.
- Tax and Withholding Rates are going to be handle as sales/receipt tax in case of Sales Tax type parameter being setup.
Uses Cases
Use Case 1 - A Purchase/Sales Invoice including Tax Rate/s such as VAT and Withholding Rates
In this scenario the end-user needs to enter in the system a Purchase Invoice coming from a vendor/supplier (same would apply to Sales Invoices) containing VAT and/or Withholding Rates, for getting that done end-user should follow below steps:
- Go to Procurement Management // Transactions // Purchase Invoice // Header and create a new purchase invoice (Transaction Document = AP invoice)
- Go to Lines, create a new line by entering a services and make sure field Tax get a Tax Rate including VAT and a Withholding tax rate, by example "Prestación de Servicios Nacional 16% - 15% R"
- Once the Purchase Invoice is completed/posted, both the VAT and the Withholding amount are calculated and posted and can be found in the Tab = Tax
- Puchase Invoice Posting should look like:
250.00 Expense (60000) | 252.50 Vendor (40000) |
40.00 VAT (47200) | 37.50 Withholding (47510) |
- For getting the invoice paid, end user could use either a Bank Statement or a Cash Journal.
- Once any of those is posted and the corresponding "Settlement - Cancellation Payment" is created by the system, below posting must be shown:
252.50 Pending (55500) | 252.50 Bank (57200) |
252.50 Vendor (40000) | 252.50 Pending (55500) |
For this scenario both VAT and Withholding must be taken into account and therefore shown in the Multidimensional Tax Report by the time the Invoice is posted, which means the Invoice Accounting Date.
Same applies to Sales Invoices.
Use Case 2 - A Purchase Invoice including Tax Rate/s such as VAT and Withholding on Settlement
In this scenario the end-user needs to enter in the system a Purchase Invoice coming from a vendor/supplier containing VAT and/or a Withholding, for getting that done end-user should follow below steps:
- Go to Master Data Management || Business Partner Setup || Withholding || Withholding and create a new withholding, by example "Withholding 15%"
- Go to Master Data Management || Business Partner || Business Partner and select a vendor and make sure that vendor is linked to "Withholding 15%" under "Withholding" tab.
- Go to Procurement Management // Transactions // Purchase Invoice // Header and create a new purchase invoice (Transaction Document = AP Invoice)
- Go to Lines, create a new line by entering a service and make sure field Tax get a Tax Rate including just VAT, by example "Adquisiciones IVA 16%"
- Go back to Purchase - Header, field "Withholding" and select "Withholding 15%"
- Once the Purchase Invoice is completed/posted, only VAT amount is calculated and posted and can be found in the Tab = Tax
- Purchase Invoice Posting should look like:
250.00 Expense (60000) | 290.00 Vendor (40000) |
40.00 VAT (47200) |
- For getting the invoice paid, end user could use either a Bank Statement or a Cash Journal.
- Once any of those is posted and the corresponding "Settlement-Cancellation Payment" is posted, below posting must be shown:
252.50 Pending (55500) | 252.50 Bank (57200) |
290.00 Vendor (44000) | 252.50 Pending (55500) |
37.50 Withholding (457100) |
For this scenario:
- VAT amount (40.00) must be taken into account and therefore shown in the Multidimensional Tax Report at the time the Invoice is posted, which means by the Invoice Accounting Date.
- but Withholding amount (-37.50) must be taken into account and therefore shown in the Multidimensional Tax Report at the time of payment settlement, which means by the Bank Statement or Cash Journal Accounting Date.
- New!! It is important to remark that Invoice Total Amount should have into account VAT and Withholding, which means Invoice Total Amount = Payment or Settlement Amount.
Use Case 3 - A Manual Settlement including a G/L item linked to a "Tax Rate" and a G/L item linked to a "Withholding Rate".
Direct Posting = Yes.
In this scenario the end-user needs to enter in the system a Payment/Receipt which could contain either a tax rate (VAT and/or Withholding Tax Rate).
It is important to highlight that Sales/Purchase Invoices must be entered in the system in below application paths:
Procurement Management || Transactions || Purchase Invoice || Header - as AP Invoices/Credit Memos Document Types
Sales Management || Transactions || Sales Invoice || Header - as AR Invoices/Credit Memos Document Types
regardless Payments/Receipts can be manually entered as Manual Settlements
It is also important that:
- Payments must be entered as "Positive" Manual Settlements by do not selecting "Receipt" flag and by getting VAT as a debit amount and Withholdings as credit amount.
- In case of Payments - VAT and Withholding must be shown in the Multidimensional Tax Report for Purchase/Payment transactions as described below:
- VAT - positive
- Withholding - negative
- Receipts must be entered as "Positive" Manual Settlements by selecting "Receipt" flag and by getting VAT as a credit amount and Withholdings as debit amount.
- In case of Receipts - VAT and Withholding must be shown in the Multidimensional Tax Report for Sales/Receipt transactions as described below:
- VAT - positive
- Withholding - negative
Based on the above End-user could follow below steps in case of a payment (it will work the other way around in case of a receipt):
- Create the corresponding G/L items by navigating to Financial Management // Accounting // Setup // G/L item
- There should be at least below G/L items:
- Name = Expense - Account = 6000
- Name = VAT - Tax = "Prestacion Servicios 16% - 15%R (16%)- Account = 4720
- Name = Withholding Tax Rate - Tax = "Prestacion Servicios 16% - 15%R (-15%) - Account = 4750
- Name = Pending Balance - Account = 5550
- After that end-user must enter a Manual Settlement by navigating to Financial Management // Receivables and Payables // Transactions // Manual Settlement
- Once there end-user must create a new one by entering both the "Transaction Date" and the "Accounting Date";
- At "Create Payment" tab:
- end user selects a BP
- enters amount (by example 1010.00)
- removes "Receipt" flag as this is a payment to a supplier or vendor
- and marks "Direct Posting"
- At "Balance Payment" tab:
- end user enters G/L item Expense = Debit Amount = 1000.00
- enters G/L item VAT = Debit Amount = 160.00 (linked to a Tax Rate = 16%)
- and enters G/L item Withholding Tax Rate = Credit Amount = 150.00 (linked to a Withholding Rate = -15%)
- Go Back to Create Payment Tab and enters next to Direct Posting flag the G/L item "Pendientes" (5550)
- Once the Manual Settlement is processed and posted, posting must look like:
1000.00 Expense (6000) | 1010.00 Pending (5550) |
16.00 VAT (4720) | 150.00 Withholding (4750) |
For this scenario:
- VAT amount (160,00) and Withholding amount (-150.00) must be taken into account and therefore shown in the Multidimensional Tax Report by the time the Manual Settlement is posted, which means by the Manual Settlement Accounting Date
- Tax base amount (1.000,00) will be calculated taking into account the Tax % and the Tax Amount entered by the end-user and same applies to withholding amount.
After the above:
- Then end-user needs to take the manual settlement into a Bank Statement or Cash Journal as a Payment.
- Once the Bank Statement is completed and post, and the corresponding "Settlement-Cancellation Payment" is post; below posting must be shown:
1010.00 Pending (5550) | 1010.00 Bank (5720) |
1010.00 Pending (5550) | 1010.00 Pending (5550) |
Direct Posting = No.
End-user needs to be aware that in this case both the VAT and the Withholding tax are not posted and therefore shown in the report by the time Manual Settlement is processed but by the time the corresponding Settlement is posted in the application path: Financial Management || Receivables & Payables || Transactions || Settlement || Settlement .
End-user must follow below steps:
- Create the corresponding G/L items by navigating to Financial Management // Accounting // Setup // G/L item
- There should be at least below G/L items:
- Name = Expense - Debit/Credit Account = 6000
- Name = VAT - Tax = "Prestacion Servicios 16% - 15%R (16%) - Debit/Credit Account = 4720
- Name = Withholding Tax Rate - Tax = "Prestacion Servicios 16% - 15%R (-15%) - Debit/Credit Account = 4750
- Name = Pending Balance - Debit/Credit Account = 5550
- After that end-user must enter a Manual Settlement by navigating to Financial Management // Receivables and Payables // Transactions // Manual Settlement
- Once there end-user must create a new one by entering both the "Transaction Date" and the "Accounting Date"
- At "Create Payment" tab:
- end user selects a BP
- enters Amount (by example 1010.00)
- removes "Receipt" flag as this is a payment to a supplier or vendor
- and do NOT mark "Direct Posting"
- At "Balance Payment" tab:
- end user enters G/L item Expense = Debit Amount = 1000.00
- enters G/L item VAT = Debit Amount = 160.00 (linked to a tax rate = 16%)
- and enters G/L item Withholding = Credit Amount = 150.00 (linked to a withholding = -15%)
- Go Back to Manual Settlement Tab and process
- No transaction must be shown in the report.
After the above:
- Then end-user needs to take the manual settlement into a Bank Statement or Cash Journal as a Payment. Process and Post the bank statement, below posting will be shown:
1010.00 Pending (5550) | 1010.00 Bank (5720) |
- No transaction must be shown in the report.
- Once the corresponding "Settlement-Cancellation Payment" is posted in the application path: Financial Management || Receivables & Payables || Transactions || Settlement || Settlement , below posting will be shown which means that both VAT and Withholding rate are posted at settlement:
1000.00 Expense (6000) | 1010.00 Pending (5550) |
160.00 VAT (4720) | 150.00 Withholding (4751) |
For this scenario:
- VAT amount (160,00) and Withholding amount (-150.00) will be taken into account and therefore shown in the Multidimensional Tax Report by the time the payment is cancelled or settle, which means by the Bank Statement or Cash Journal Accounting Date.
- Tax base amount (1.000,00) will be calculated taking into account the Tax % and the Tax Amount entered by the end-user and same applies to withholding amount.
Use Case 4 - A Manual Settlement including a G/L item linked to a "Tax Rate" and a G/L item linked to a "Withholding".
Direct Posting = Yes.
It is important to highlight that Sales/Purchase Invoices must be entered in the system in below application paths:
- Procurement Management || Transactions || Purchase Invoice || Header - as AP I nvoices/Credit Memos Document Types
- Sales Management || Transactions || Sales Invoice || Header - as AR Invoices/Credit Memos Document Types
regardless Payments/Receipts can be manually entered as Manual Settlements
It is also important that:
- Payments must be entered as "Positive" Manual Settlements by do not selecting "Receipt" flag and by getting VAT as a debit amount and Withholdings as credit amount.
- In case of Payments - VAT and Withholding must be shown in the Multidimensional Tax Report for Purchase/Payment transactions as described below:
- VAT - positive
- Withholding - negative
It is not possible to enter "Receipts" linked to a withholding as those ones do only work on Purchase/Payment transactions.
Based on the above End-user could follow below steps in case of a payment (it will have to work the other way around in case of a receipt):
- Create the corresponding G/L items by navigating to Financial Management // Accounting // Setup // G/L item
- There should be at least below G/L items:
- Name = Expense - Accounting = 6000
- Name = VAT - Tax = "Prestación Servicios 16%" - Accounting =4720
- Name = Withholding - Withholding = W15% - Accounting = 4751 - This time a withholding on settlement.
- Name = Pending - Accounting = 5550
- After that end-user enters a Manual Settlement
- Once there a new one must be created and end-user needs to enter both "Transaction Date" and "Accounting Date"
- At "Create Payment" tab:
- End-user selects a BP
- Enters transaction amount (by example 1010.00)
- removes "Receipt" flag as this is a vendor payment
- and marks "Direct Posting"
- At "Balance Payment" tab:
- end user selects G/L item Expense = Debit Amount = 1000.00
- enters G/L item VAT = Debit Amount = 160.00
- and enters G/L item Withholding = Credit Amount = 150.00
- Go back to Create Payment tab and enters next to Direct Posting flag the G/L item Pendientes (5550)
- Once the Manual Settlement is processed and posted, posting looks like:
1000.00 Expense (6000) | 1010.00 Pending (5550) |
160.00 VAT (4720) | 150.00 Withholding (4751) |
For this scenario:
- VAT amount (160,00) and Withholding amount (-150.00) will be taken into account and therefore shown in the Multidimensional Tax Report by the time the Manual Settlement is posted, which means by the Manual Settlement Accounting Date
- Tax base amount (1.000,00) will be calculated taking into account the Tax % and the Tax Amount entered by the end-user and same applies to withholding amount.
After the above:
- Then end-user needs to take the manual settlement into a Bank Statement or Cash Journal as a Payment.
- Once the Bank Statement is completed/posted, and the corresponding "Settlement - Cancellation Payment" is created by the system, posting should look like:
1010.00 Pending (5550) | 1010.00 Bank (5720) |
1010.00 Pending (5550) | 1010.00 Pending (5550) |
Direct Posting = NO.
End-user needs to be aware that in this case both the VAT and the Withholding will not be posted and therefore shown in the report by the time Manual Settlement is processed but by the time the corresponding Settlement is posted.
End user must follows below steps:
- Create the corresponding G/L items by navigating to Financial Management // Accounting // Setup // G/L item
- There should be at least below G/L items:
- Name = Expense - Accounting = 6000
- Name = VAT - Tax = "Prestación Servicios 16%" - Accounting =4720
- Name = Withholding - Withholding = W15% - Accounting = 4751
- Name = Pending - Accounting = 5550
- After that end-user enters a Manual Settlement
- Once there a new one must be created and end-user needs to enter both "Transaction Date" and "Accounting Date"
- At "Create Payment" tab:
- End-user selects a BP
- Enters transaction amount (by example 1010.00)
- removes "Receipt" flag as this is a vendor payment
- and do NOT mark "Direct Posting"
- At "Balance Payment" tab:
- end user selects G/L item Expense = Debit Amount = 1000.00
- enters G/L item VAT = Debit Amount = 160.00
- and enters G/L item Withholding = Credit Amount = 150.00
- Go Back to Manual Settlement Tab
- Once there Manual Settlement is processed and there is no posting.
- Then end-user needs to take the manual settlement into a Bank Statement or Cash Journal as a Payment.
- Once the Bank Statement is post, below posting is shown:
1010.00 Pending (5550) | 1010.00 Bank (5720) |
- No transaction must appear in the report.
- Finally, once the "Settlement-Cancellation Payment" is posted in the application path: Financial Management || Receivables & Payables || Transactions || Settlement || Settlement , below posting must be shown:
1000.00 Expense (6000) | 1010.00 Pending (5550) |
160.00 VAT (4720) | 150.00 Withholding (4751) |
For this scenario:
- VAT amount (160,00) and Withholding amount (-150.00) will be taken into account and therefore shown in the Multidimensional Tax Report by the time the payment is cancelled, which means by the Bank Statement or Cash Journal Accounting Date.
- Tax base amount (1000) will be calculated taking into account the Tax % and the Tax Amount entered by the end-user and same applies to withholding amount.
Use Case 5 - A Bank Statement or a Cash Journal including a G/L item do not linked to either a "Tax Rate" and/or a "Withholding Rate" or a "Withholding"
We can not think on a real scenario for a G/L item linked to either a "Tax Rate" and/or a "Withholding Rate" or a "Withholding" but the other way around.
In this scenario end user might follow below steps:
- Go to Financial Management // Receivable and Payables // Transactions // Bank Statement // Header and create a new Bank Statement by entering below data:
- Name = BStatement G/L item
- Transaction Date = 25-06-2010
- Begining Balance = 15800.00
- Then he/she goes to Lines Tab, creates a new one and enters below information:
- G/L item = Tax Payment Settlement - Debit/Credit Account = 4750
- Go back to Header, process and post. System will show below posting:
15800.00 Bank (5720) | 15800.00 VAT Settlement (4750) |
Tax Report examples (M-303,M-110 and M-115)
As described above "Invoice Taxes Report" enhancements are based on Spanish Tax Requirements and Spanish "GPS" team input, regardless it could also be applied to other countries as the main goal is to provide the end-user with a tool to exploit tax information saved in the system.
As a real example or use case scenario, there are two Spanish Tax Report M-303 and M-110 end-user could manually filled in by using the Invoice Taxes Report enhanced as "Multidimensional Tax Report"
M-303 Use Case Scenario
M-303 tax report is a "VAT Settlement" tax report
It can be manually submitted by filling in the form which can be found here:
https://www2.agenciatributaria.gob.es/es13/h/ie93030b.html
M-303 tax report requires below data.
Overall Data section
End-user must enter/select information detailed below:
- Tax ID
- Company Name
- Period (Referece List : 1T, 2T, 3T or 4T)
- Year
Payable VAT section (in Spanish "IVA Devengado")
This section collects the Payable VAT coming from:
a) National sales invoices of goods & services split by VAT Rate (16%, 7% and 4%).
b) National sales invoices of goods & services under Equivalence Charge regimen split by VAT Rate (4%, 1%, 0,5%)
c) Intra-community purchase of goods summed-up and do not split by VAT Rate
For getting above information end-user should run Multidimensional Tax Report by selecting:
- "Tax" Radio button selected.
- No Tax in the "Tax Selector"
- No BP in the "BP Selector"
- "Show Details Info" flag selected
- "Grouped by BP" flag do not selected
- "Sales/Collection Transactions" Radio button selected.
a) National Sales invoices of goods&services split by VAT Rate (16%, 7% and 4%).
End-user should get and fill in Sales VAT information: Tax Base Amount and Tax Amount , coming from any sales transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up and split by VAT rate (16, 7 or 4) and taking into account the Invoice Accounting Date.
Tax Categories :
- VAT16%, VAT7%, VAT4%, excluding Intra-Community VAT related tax rates (both Intra-Community Purchase and Intra-Community Sales tax rates)
- VAT16%Services, VAT7%Services, VAT4%Services excluding "Reversed Charge" rates (In Spanish "Inversión de Sujeto Pasivo").
- VAT16%RealEstate, VAT7%RealEstate.
b) National Sales invoices of goods&services under Equivalence Charge regimen summed-up and split by VAT Rate (4%, 1%, 0,5%)
End-user should get and fill in Sales VAT information: Tax Base Amount and Tax Amount , coming from any sales transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up and split by VAT rate (4%, 1%, 0,5%) and taking into account Invoice/Manual Settlement Accounting Date.
Tax Categories:
- VAT16%, VAT7%, VAT4%.
- VAT16%Services, VAT7%Services, VAT4%Services if any
c) Intra-community purchase of goods summed-up and do not split by VAT Rate.
End-user should get and fill in Purchase VAT information: Tax Base Amount and Tax Amount , coming from any purchase transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up regardless VAT rate (16, 7 or 4)
Tax Categories:
- VAT16%, VAT7%, VAT4%, just for Intracommunity Purchase VAT Rates.
Deductible VAT section (in Spanish "IVA Deducible"):
This section collects the VAT which can be deductible 100% (otherwise VAT proportion rules must be applied) related to:
a) National Purchase of Goods/Services
b) National Purchase of Real Estate
c) Import of Goods/Services
d) Import of Real Estate
e) Intra-community Purchase of Goods/Services
f) Intra-community Purchase of Real Estate
For getting above information end-user should run Multidimensional Tax Report by selecting:
- "Tax" Radio button
- No Tax in the "Tax Selector"
- No BP in the "BP Selector"
- "Show Details Info" flag selected
- "Grouped by BP" flag do not selected
- "Purchase/Payment Transactions" flag selected.
a) National Purchase of Goods/Services:
End-user should get and fill in Purchase VAT information: Tax Base Amount and Tax Amount , coming from any purchase transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up regardless of VAT rates (16, 7 or 4)
Tax Categories:
- VAT16%, VAT7%, VAT4% , excluding Intra-Community Purchase VAT rates and Import VAT rates.
- VAT16%Services, VAT7%Services and VAT4%Services, Excluding "Reversed Charge" rates (In Spanish "Inversión de Sujeto Pasivo").
b) National Purchase of Real Estate:
End-user should get and fill in Purchase VAT information: Tax Base Amount and Tax Amount, coming from any purchase transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up regardless of VAT rates (16, 7 or 4)
Tax Categories:
- VAT16%RealEstate, VAT7%RealEstate
c) Import of Goods/Services:
End-user should get and fill in Purchase VAT information: Tax Base Amount and Tax Amount c, coming from any purchase transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up regardless of VAT rates (16, 7 or 4)
Tax Categories:
- VAT16%, VAT7%, VAT4%, excluding National Purchase rates and Intra-community Purchase rates and excluding "Reversed Charge" rates (In Spanish "Inversión de Sujeto Pasivo").
d) Import of Real Estate:
End-user should get and fill in Purchase VAT information: Tax Base Amount and Tax Amount , coming from any purchase transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up regardless of VAT rates (16, 7 or 4)
Tax Categories:
- VAT16%RealEstate, VAT7%RealEstate
e) Intra-community Purchase of Goods
End-user should get and fill in Purchase VAT information: Tax Base Amount and Tax Amount, coming from any purchase transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up regardless of VAT rates (16, 7 or 4)
Tax Categories:
- VAT16%, VAT7%, VAT4%, excluding National Purchase rates and Import Purchase rates.
f) Intra-community Purchase of Real Estate
End-user should get and fill in Purchase VAT information: Tax Base Amount and Tax Amount, coming from any purchase transaction linked to the VAT rates belonging to the Tax Categories shown below, summed-up regardless of the VAT rates (16, 7 or 4)
Tax Categories:
- VAT16%RealEstate, VAT7%RealEstate, excluding National Purchase rates and Import Purchase rates.
VAT Settlement section (In Spanish "Liquidación"):
This sections collect exempt or not-subject to VAT transactions like:
a) Intracommunity Sales of Goods
b) Export of Goods
c) Reverse Charge
For getting above information end-user should run Multidimensional Tax Report by selecting:
- "Tax" Radio button
- No Tax in the "Tax Selector"
- No BP in the "BP Selector"
- "Show Details Info" flag selected
- "Grouped by BP" flag do not selected
- "Both Transactions" flag selected.
a) Intra-Community Sales of Goods:
End-user should get and fill in Sales VAT information: Tax Base Amount as these are VAT Exempt Transactions, coming from any sales transaction linked to the VAT rates belonging to the Tax Categories shown below:
Tax Categories:
- Exempt, excluding National Sales rates and Export rates.
- VAT16%, VAT7%, VAT4% , excluding National Sales rates and Export rates.
b) Export of Goods:
End-user should get and fill in Sales VAT information: Tax Base Amount as these are VAT Exempt Transactions, coming from any sales transaction linked to the VAT rates belonging to the Tax Categories shown below:
Tax Categories:
- Exempt, excluding National Sales rates and Intra-Community Sales rates.
- VAT16%, VAT7%, VAT4% , excluding National Sales rates and Intra-Community Sales rates.
c) Reverse Charge:
End-user should get and fill in Purchase VAT information: Tax Base Amount as these are not-subject to VAT Transactions, coming from any purchase transaction linked to the VAT rates belonging to the Tax Categories shown below.
Tax Categories:
- VAT16%Services, VAT7%Services, VAT4%Services, just in case of Reverse Charge.
- VAT16%, VAT7%, VAT4%, just in case of Reverse Charge.
Rest of Form fields are calculated fields within Tax Form.
M-110 Use Case Scenario
M-110 is a "Withholding Tax Report" which contains information about certain Monetary and Non-Monetary Incomes subject to withhold.
It can be manually submitted by filling in the form which can be found here:
M-110 tax report requires below data.
Overall Data section
End-user must enter information detailed below:
- Tax ID
- Company Name
- Period
- Year
Withholding Settlement section (In Spanish "Liquidación")
This sections collects withholdings grouped based on incomes type, which are listed below:
a) Employment monetary and non-monetary income
- Organizations are required to withhold a certain % at the time these incomes are paid to their employees.
b) Economic activities monetary and non-monetary incomes
- Professionals or Companies are required to withhold a certain % at the time these activities take place and therefore are invoiced.
- Later on the amount to be paid will be less as it will be decreased by the withholding amount.
c) Prizes monetary and non-monetary incomes are also subject to withholding
d) Capital gains monetary and non-monetary incomes are also subject to withholding
e) Image rights transfers monetary and non-monetary incomes are also subject to withholding
Withholding information/transactions must be grouped by withholding or withholding rate, therefore end-user must create:
- Different withholdings per each income type (employment)
- Different withholdings or withholding rates per each income type (economic activities, prizes, capital gains and image rights)
- And besides different withholding rates or withholdings for monetary incomes and for non-monetary incomes of each income type.
Withholding setup example:
- Withholding = Monetary Employment Withholding (salaries) 25%
- Withholding = Non-Monetary Employment Withholding (courses) 15%
- Withholding = Monetary Employment Withholding (board of directors) 35%
- Withholding = Non-Monetary Economic Withholding (board of directors) 35%
- Withholding Rate = Monetary Economic Activity Withholding 15% (-15%)
- Withholding Rate = Non-Monetary Economic Activity Withholding 15% (-15%)
- Withholding Rate = Monetary Economic Activity Withholding 7% (-7%)
- Withholding Rate = Non-Monetary Economic Activity Withholding 7% (-7%)
- and so on so forth.....
Let's explain most common ones:
a) Employment monetary and in kind incomes
Example: Payroll being paid to an employee.
It is important to remark that in the case of Salary Posting, end-user should enter salaries per each employee and therefore withholding rate (%), as there is no way to get what should be the Taxable amount in case of mixing several withholding rates (%).
This kind of transactions could be entered in the system by using a Manual Settlement.
Manual Settlement:
End-user must follow below steps:
- Create the corresponding G/L items by navigating to Financial Management // Accounting // Setup // G/L item
- There should be at least below G/L items:
- Name = Peter Salary March - Account Debit /Credit = 6499
- Name = Social Security - Account Debit /Credit = 4760
- Name = Monetary Employment Withholding (salaries) 25% - Withholding = "Monetary Employment Withholding (salaries) 25%"; Account Debit/Credit = 4730
- After that end-user must enter a Manual Settlement by navigating to Financial Management // Receivables and Payables // Transactions // Manual Settlement
- Once there end-user must create a new one by entering both the "Transaction Date" and the "Accounting Date"
- At "Create Payment" tab:
- end user selects a BP or employee
- enters transaction amount (by example 2025.00)
- removes "Receipt" flag as this is a payment to an employee
- and do not marks "Direct Posting"
- At "Balance Payment" tab:
- end user enters G/L item "Peter Salary" = Debit Amount = 3000.00
- enters G/L item "Social Security" = Credit Amount = 225.00
- and enters G/L item "Monetary Employment Withholding (salaries) 25%" = Credit Amount = 750.00
- Go Back to Manual Settlement Tab and process it, no posting take place
- Then end-user needs to take the manual settlement into a Bank Statement
- Once the Manual Settlement is processed and posted, posting must look like:
3000.00 Salaries (6499) | 2025.00 Pending Balance (5550) |
225.00 Social Security (4760) | |
750.00 Monetary Employment Withholding (salaries) 25% (4730) |
- and
2025.00 Pending (5550) | 2025.00 Bank (5720) |
For this scenario:
- Withholding amount (750.00) must be taken into account and therefore shown in the Multidimensional Tax Report by the time the payment is cancelled, which means by the Bank Statement or Cash Journal Accounting Date.
b) Economic activities monetary and in kind incomes.
Example: Professional Services or any other kind of economic activity supplied by a BP
This kind of transactions can be entered in the system by using either a purchase invoice or a manual settlement.
Purchase Invoice - See Use Case 1 and 2.
Manual Settlement - See Use Case 3 and 4.
For getting above information end-user should run Multidimensional Tax Report by selecting:
- "Withholding" Radio button
- No Withholding in either "Withholding on Invoice" or "Withholding on Settlement", therefore the system will show tax transactions linked to both withholding types.
- No BP in the "BP Selector"
- "Show Details Info" flag selected
- "Grouped by BP" flag selected
- "Both Transactions" flag selected.
a) Employment Section
End-user should get and fill in below information per withholding rate (by example = Monetary Employment Withholding (salaries) 25%) or withholding (by example = Non-Monetary Employment Withholding (courses) 15%
- Total number of BP
- Total Tax Base amount
- Total Withholding amount
b) Economic activities Section
End-user should get and fill in below information per withholding rate (by example = Monetary Economic Activity Withholding 15%) or withholding (by example = Non-Monetary Economic Activity Withholding 15%)
- Total number of BP
- Total Tax Base amount
- Total Withholding amount
M-115 Use Case Scenario:
M-115 is a "Withholding Tax Report" which contains information about renting incomes subject to withholding
It can be manually submitted by filling in the form which can be found here:
M-115 tax report requires below data.
Overall Data section
End-user must enter information detailed below:
- Tax ID
- Company Name
- Period
- Year
Withholding Settlement section (In Spanish "Liquidación")
- Total number of BP
- Total Tax Base amount
- Total Withholding amount
Withholding information required as shown above must be grouped by withholding or withholding rate. It is required that the end-user creates different withholding rates or withholdings for each renting income subject to a different withholding %Withholding setup example:
- Withholding = Renting 19%
- Withholding = Renting 9% (Ceuta o Melilla)
This kind of transactions can be entered in the system by using either a Manual Settlement or a purchase invoice.
Manual Settlement
End-user must follow below steps:
- Create the corresponding G/L items by navigating to Financial Management // Accounting // Setup // G/L item
- There should be at least below G/L items:
- Name = Renting Building X - Account Debit /Credit = 6488
- Name = Renting 19% Withholding = "Renting 19%"; - Account Debit/Credit = 4730
- After that end-user must enter a Manual Settlement by navigating to Financial Management // Receivables and Payables // Transactions // Manual Settlement
- Once there end-user must create a new one by entering both the "Transaction Date" and the "Accounting Date"
- At "Create Payment" tab:
- end user selects a employee
- enters transaction amount (by example 5870.00)
- removes "Receipt" flag as this is a payment
- and do not marks "Direct Posting"
- At "Balance Payment" tab:
- end user enters G/L item "Renting Building X" = Debit Amount = 5870.00
- and enters G/L item "Renting 19%" = Credit Amount = 1115.30
- Go Back to Manual Settlement Tab and process it, no posting take place
- Then end-user needs to take the manual settlement into a Bank Statement
- Once the Manual Settlement is processed and posted, posting must look like:
5870.00 Renting Building X (6488) | 4754.70 Pending Balance (5550) |
1115.30 Renting 19% withholding (4730) |
- and
4754.070 Pending (5550) | 4754.70 Bank (5720) |
For this scenario:
- Withholding amount (1115.30) must be taken into account and therefore shown in the Multidimensional Tax Report by the time the payment is cancelled, which means by the Bank Statement or Cash Journal Accounting Date.
For getting above information end-user should run Multidimensional Tax Report by selecting:
- "Withholding" Radio button
- No Withholding in either "Withholding on Invoice" or "Withholding on Settlement", therefore system will show tax transactions linked to both withholding types.
- No BP in the "BP Selector"
- "Show Details Info" flag selected
- "Grouped by BP" flag selected
- "Both Transactions" flag selected.
a) Settlement Section
End-user should get and fill in below information per withholding rate (by example = Renting 19%) or withholding (by example =Renting 9%)
- Total number of BP
- Total Tax Base amount
- Total Withholding amount
Research on tax Spanish Tax Reports M-180
Research on tax Spanish Tax Reports M-180
M-180 is the Annual version of M-115
For this one end-user should define year range, grouped by BP and showing details.