The main idea behind this article is to help speed up the searching process. Instead of breaking down the problem piece by piece until we understand where the discrepencies come from, maybe we can get right to the heart of the matter with some simple checks.
Since the Total Sales figure doesn't mean total taxable sales, the G.S.T. Collected number will not necessarily be 5% of the Total Sales figure. The trouble then is how do we tell what the differences are? I suggest starting with the tax summary report as it can report on some of our non-taxable numbers, and provide some key indicators to help us reconcile the difference.
Tax summary report checks:
- Review the Summary tab looking for the total of Non-Taxable items. This should be removed from the total sales figure it you want to calculate the total non-taxable sales.
- In the report look for invoices containing INTRST. These are interest invoices that normally increase the Total Sales figure. The reason they increase the total sales figure is because they are income from interest posting to a 4000 series account. The account number 4050 is usually a default.
- Check the total of Parts + the total of Labor against the Total Sales value on the G.S.T Report. If there is a difference here it is likely because there is a sell to GL item that is not in the 4000 level accounts. One example of this would be writing off bad debts. If for example we couldn't collect on an invoice we could write it off against account 6015 bad debts (the GL number on your data may vary). There is a way to find transactions from invoices that are in the 6000+ range. Run a transaction listing and browse the left most column “Type” If you see any I's in that column you have found an invoice that posts to a 6000 level account.
- Another scenario very simular to the one above that will also cause a difference in the Total Sales Figure is: Some categories may be configured to post the income side of their transaction to an account other than income. One such example is for trade ins. Trade in setup dictates that the trade be posted to 6999 for income and expense accounts. This means that even though we essentially returned goods to our store for a valid reason (the trade in) we did not record this in the 4000 level income accounts. We will have to take this into account if trying to manually calculate the total non-taxable sales from the Total Sales number.
- Please check each category and see if there are any income accounts outside the 4000 series. Please check these accounts for the period in question to see if these might also be throwing out the Total sales figure. Note: You will probably want to check the activity of these accounts for the period you are reconciling. Again, you will need to look for any transactions with the “I” type column.
- IMPORTANT: T type invoices that are not at cost will also cause discrepancies in the income statement. Please review all of your T type invoices to make sure that they are at cost. Click the Sub Total button on the invoice when it is open and verify that the profit is $0.
- If you perform an invoice report filtering for only T Type invoices and choosing profitability, you should get $0 profit for all of these. Any of these not at cost may have to be considered when looking at the income numbers as they are recorded as 100% profit or 100% loss.
- The reason why T type invoices show as 100% profit is that they post both the income and the cost to the income account instead of the cost going to cost of goods sold. T type invoices have controls that force them to default to cost, but it needs to be able to process not at cost due to certain uses of these invoices.
Reconciling Tender Information to the Total Sales of the Tax Report
- If you are strictly doing over the counter sales, then it is likely that the total amount tendered would match up to the sales made for a particular period
- Any items that do not post to the 4000 series for income would have to be accounted for seperately.
- There is value in having the ability to break down GL activity on a Per Day basis for the 4000 account. This currently does not exist.
Things that will make the HST report wrong
The Total HST Collected number will be wrong
- If any bills post to the HST Payable account (normally 2080) the HST report will now be wrong.
- You will notice that the HST collected on this report does not match the HST collected on the Tax Area report.
- This is because the Taxes collected number is a total of all activity for the period including any journal entries or bills that post to that account. If you post bills to this account it no longer contains invoices only, and will not give you a correct number. The only other thing that should post to this account is the journal entries that record your payments to the government.
Reconciling Total Sales on HST against Total Parts and Total Labour on Tax Area report
- Any Bills or journals that post to the 4000 range of accounts will cause you problems. The real trick here is to know what is posting to that series of accounts and taking it into account.
What reports should reconcile
- The HST Collected on the HST report should match the Total tax collected on the Tax Area report.
- The total sales on the HST report does not match the Total Sales (Parts + Labour) on the Tax Area report for most businesses.
- You must prevent any bills, and most journal entries from posting to the 4000 range of accounts for the above to reconcile
- Information: The HST report and the tax area report report on 2 different things because the HST report reports on the general ledger activity while the tax area report reports only on invoice activity which is part of what is posted to the general ledger. You can only really reconcile these 2 reports if you do things in such a way that non invoice activity doesn't go to the 4000 series of accounts and make sure invoice related activity is only entered via invoices.
- Sell to GL on invoices is a special case scenario
- There are some invoice types that can post to the GL, but may not show on your Tax Area Report unless you select them. Completed RMA invoices are an example of this. Special Note: completed RMA invoices still have a “final completion” that should occur in which the invoice becomes a T type invoice.
More on reporting
- Need details on the non taxable sales? The category item sales detail report is another report that can help a lot of customers get details on the sales for non-taxable items.
- You must have lookup words attached to the non-taxable parts you want to report on
- You open the category item sales detail report, select a date range, and then click select by item
- In the part find screen, type in the lookup word that you attached to the non-taxable items and perform the search
- A list of parts show up. Click on the header that says multi-select. This will select all of the parts you just searched for.
- Now add these to the report by clicking OK, and then view the report. It gives you the dollar value of the parts you just selected.
- Note: This method works great for a simple scenario, but for more detailed reporting on multiple taxes, you may have to run the report multiple times with different lookup word filters.
- Need details on the exempt invoices? That is already on the tax report. Go to the exempt tab. You can double click on any invoice there and you will see items that should be taxed that are not.
- For more detail: The invoice report can be used to get exempt invoices only. That is if you are looking for invoices that are tax 1 exempt.
- Just make sure the “(Tax 1) Exempts Invoices Only” check box is checked.
Ideas for Tools
- Integrity checks that might help:
- Submitted as incident#36683(Dec2012-cm): Warning for any journals that post to 4000. Make a note on the warning that this could cause issues with reconciling your tax reports
- Submitted as incident#36684(Dec2012-cm): When checking invoices warn for any postings for income outside the 4000 level
- Submitted as incident#36685(Dec2012-cm): When checking T Type invoices check to make sure all lines are at cost. Make a note on the warning that this could cause issues with artificially inflated/deflated incomes and can mess up your tax reconciliation reporting as it should not effect the income.
Note: There may be some categories like gift cards that are not posting to 4000 level accounts that we consider on invoice reports to better report revenue. Are gift cards really revenue if they post to a liability?