Dynamics NAV 2016: Unrealized Sales Tax for Cash Based Companies
In a recent implementation I was involved with, ArcherPoint was tasked with combining two separate entities, one cash based and one accrual based, into one NAV company (not one database) while still being able to run financials separately for each entity within the company.
Easy, right?
No! An incredible challenge is a bit more accurate way to describe this, but given the incredible talent pool we have at ArcherPoint, we were up for the challenge. As the lead consultant on this account, I got my hands dirty in every aspect of the project (and I’m still trying to clean them). One task I had to overcome was finding a way to handle the client’s sales tax reporting requirements.
All of their sales tax reporting was done through the cash based company so they paid taxes based on cash received from their customers. Being my first cash based company implementation (only having worked with accrual based reporting before), I thought that we were going to have to modify the Sales Taxes Collected report to show the payments and calculate the tax due. In an effort not to duplicate work, I turned to our internal Yammer site and posted a question to my Tribe to find out who might have done a modification like this before. Lo and behold, I was advised by one of our Tribal leaders, Dan “The Medicine Man” Sass, to take a look at the Unrealized Sales Tax functionality that NAV offers out of the box. I am calling Dan “The Medicine Man” because he cured a future ailment of endless hours of wasted design and development before it even happened with a mere one sentence reply. Thanks again Dan!
So here I am, amazed at the fact that I can work with a system for almost one and half decades and still learn something new on an almost weekly basis. I guess I have to finally admit that I don’t know everything… kind of like admitting that I am never wrong. Actually, one time I thought I was wrong, however I was mistaken, but that is beside the point. Let’s get our hands dirty and take a look at this fantastic functionality that is incredibly easy to setup and use…and requires no modification whatsoever!
First step is to turn the functionality on in General Ledger Setup by setting the field “Unrealized Tax” to True:
Figure 1. General Ledger Setup General FastTab.
Next, for each tax jurisdiction that you would like to track the Unrealized Tax in, you need to setup two additional fields. The first is the G/L Account to use for the Unreal. Tax Acc. (Sales) and the second is the Unrealized Tax Type. Note that these fields are not shown as columns by default.
Figure 2. Tax Jurisdictions.
The NAV Help topic for the Unrealized Tax Type field provides the following descriptions:
| OPTION | DESCRIPTION |
|---|---|
| Blank | Select this option if you do not want the program to use the unrealized tax feature for the current tax jurisdiction. |
| Percentage | When you select this option, each payment covers both tax and invoice amounts in proportion to the payment’s percentage of the remaining invoice amount. The paid tax amount is transferred from the unrealized tax account to the tax account. |
| First | When you select this option, payments cover tax first and then invoice the amount. |
| Last | This case, no amount will be transferred from the unrealized tax account to the tax account until the total invoice amount, exclusive of tax, has been paid. |
| First (Fully Paid) | When you select this option, payments will cover tax first (as in the First option), but no amount will be transferred to the tax account until the full tax amount has been paid. |
| Last (Fully Paid) | When you select this option, payments will cover the invoice amount first (as in the Last option), but no amount will be transferred to the tax account until the full tax amount has been paid. |
In my example, I have chosen to post the unrealized sales tax to a new G/L Account – 22650 – “Unrealized Sales Taxes Payable” and I have chosen my Unrealized Tax Type to be “Percentage”.
OK, so that is it for the setup. Pretty easy right? Let’s now see how this works using an example. First I am going to post a sales invoice using the Tax Jurisdiction FLMIAMI. Below are the General Ledger Entries that were created during posting. As you can see the $30.00 of tax posted to our Unrealized Sales Tax Payable account:
Figure 3. General Ledger Entries of the Posted Invoice.
When we run the Sales Taxes Collected report for this Jurisdiction, you can see that the invoice shows up on the report however no tax is due. This is because we haven’t received any payments against this invoice as of yet:
Figure 4. Sales Taxes Collected Report prior to payment received.
Next, I will post a partial payment against the invoice to demonstrate how the Unrealized Tax Type of “Percentage” prorates the sales taxes due. Upon posting the payment and applying it against the invoice through the normal course of business, the General Ledger Entries that are created are the following:
Figure 5. General Ledger Entries of the Posted Payment.
As you can see, the system prorated the tax amount due based on the partial payment and moved that amount from the Unrealized Sales Tax Payable account to the Sales Tax Payable account.
When we re-run the Sales Taxes Collected report, we now see the payment below the posted invoice along with the calculated sales tax that is now due:
Figure 6. Sales Taxes Collected Report after payment is received.
That’s it, the whole kit and caboodle! Amazingly simple, effective, and out of the box. So the moral of this blog is that despite the fact that I don’t know everything, I can say with utmost certainty that this is something that I will never forget, allowing me to claim that I do know everything about this, until somebody tells me I am wrong, in which case I will remember that time I thought I was wrong but was mistaken, and tell them to ask Dan “The Medicine Man” because he has all the answers.
For more information on how to manage cash based sales tax reporting in Microsoft Dynamics NAV, please contact the Experts in Microsoft Dynamics NAV at Archerpoint.
Read more “How To” blogs from ArcherPoint for practical advice on using Microsoft Dynamics NAV.