Training Dynamo's Survival Guide for Microsoft Dynamics GP and the New 1099-NEC Form
As 2020 winds to an end, I wanted to give you some special tips to help you so that the Year-End Close for Microsoft Dynamics GP goes as smoothly as possible. A few years ago, I wrote my "Year-End Closing Workbook!". If you don't have a copy, you can use code: GPBLOG to save 30%!
I wanted to make sure you are prepared for the challenge of dealing with the new 1099 form for Non-Employee Compensation. Like crimping your hair, wearing jelly shoes, and parachute pants...it is something from the 80's that we wish didn't make a comeback...but it did...and you need a plan to deal with it!
Option 1: Install GP Upgrade or Update
Microsoft has released a new version of GP that addresses this change. If you are running GP2016, you will need to install an "update". If you are running GP2018 and newer, you will be "upgraded" to the latest version (as of writing, that is GP 18.3___). If you are running an older version, you will need to utilize options 2 or 3. For more information and FAQ's, checkout this post from Microsoft: Microsoft Dynamics GP Year-End Update 2020: Payables Management form changes including the NEW 1099-NEC!!
I have worked on a couple projects that have done this update. Please make sure that your partner works with you to TEST this upgrade. There are changes to the Vendor Maintenance window, and an update process that runs to move vendors from 1099-MISC box 7 to the new type (1099-NEC). If you have customized the Vendor Maintenance window or if you import AP Vendors or Transactions, as of today (End of December 2020), the new NEC tax type/boxes have not been added to Integration Manager or eConnect. Vote to make this change to GP here! A couple clients that I worked with had some issues (especially with 3rd Party Tools). Also, a client had an issue with printing Inventory Transaction Edit Lists and posting Inventory Transactions. Make sure you TEST any critical reports and processes whenever you do an update or upgrade! Thankfully, all issues have been resolved! I just don't want you to have these issues affect your day-to-day work!
If you are not running a supported version, you'll need to have a plan on how to deal with 1099s. Here are some ideas:
Option 2: Greenshades
If you are not able to do the update from Microsoft this year and you want to work with a product that can pull the information directly from GP, I highly recommend contacting Greenshades. They have a product called the "Greenshades Tax Filing Center" that will pull the information from GP. It works for all versions of GP (even the new version with the NEC option). If you are running an older version of GP, their product will automatically map any Vendor setup as 1099-Misc/box 7/Non-Employee Compensation to the 1099-NEC form. They can work with you and your partner to come up with the best option for your company. Here is a demo of the Tax Filing Center in action!
Option 3: Create an Export and Work with an Online 1099 Processing Company
You will need to install the 1099 Year End Edit - SQL View for Dynamics GP from Shawn Dorward. This will help you look at each vendor and the amount that you've paid them by year. This SQL View is for any company running a version before GP 18.3___ (Fall Release 2020). The amounts and types will be based on the OLD options in GP (i.e. Non-Employee Compensation will be listed as Miscellaneous Box 7). You may need to export and modify the column order in Excel to match the requirements for the online processor. They also many require that you update all 1099-MISC/Box 7 to the new value of 1099-NEC. Check with the processor and if needed open a support case with them to confirm their requirements. You can also choose to modify the SQL View or modify using SmartList Designer or SmartList Builder. For tips on how to install a SQL View and use with either SmartList Builder or SmartList Designer, visit my YouTube channel or the Video Tips section on my website.
If you don't have an Online 1099 Processing Company, here are a couple that allow you to upload from Excel sheets (For legal reasons, I don't endorse or recommend any of these solutions) E-File My Forms, Tax1099.com, and 1099Pro. If you have had any experience with any of these services, let me know! I am sure there are other sites out there that will work with an export from GP. If you have any recommendations, let me know about those as well.
WARNING ABOUT BUILT IN YTD AND LYR FIELDS IN SMARTLIST
I cover the timing of the AP Year-End close process in my Year-End Workbook. One of the key issues to be aware of is that standard SmartLists show "Amount Since Last Close" in the YTD and LYR columns!! Do not send these to the IRS!!! Use the SQL view!! I had a client who used the standard 1099 LYR column and they had to file corrections for ALL OF THEIR 1099's!!!
Cleaning Up the 1099 Period Detail Table (PM00204)
When I installed the SQL View, I noticed that the data was wacky on my test company. A lot of Vendors were not showing up. Later when I installed for some clients, I noticed that they had a lot of extra rows for the 1099 Dividend type. In either case, the fix required running a built-in GP Utility. If you are going to run a utility, my rule is STOP and RUN A BACKUP!! If you don't have the ability to run a backup, give this task and the installation of the SQL View Task to your Partner!
To access the Reconcile window, go to: Purchasing > Utilities > Reconcile. Change the option to Reconcile the Calendar Year. I ran it for All Amounts and All Years for my clients, but work with your partner to see if running only for the current year and/or 1099 Amounts will work best for your company. I ran this for my clients on their TEST company first to ensure that no wacky changes happened to their data. I recommend trying on test before running on production. Also, DO NOT RUN UTILIITES WHEN OTHER PEOPLE ARE WORKING IN THE SYSTEM!!
BONUS - New SQL View for Upgraded GP (Includes New 1099-NEC Option)
I want to give credit to Shawn Dorward for his original 1099 SQL View! I updated to include the new Non-Employee Compensation option. This will only work with GP 2016 (that has the 2020 Year-End Update installed) and GP 18.3 and newer! I removed the Tax Registration Number field as this does not print on 1099's. If you need that field, let me know and I will revise the view below. If you have never installed a SQL View, checkout the tips on my website. You will learn how to install and use SQL Views with either SmartList Builder or SmartList Designer.
CREATE view [dbo].[view_1099_includes_1099NEC]
as
SELECT A.VENDORID Vendor_ID,
B.VENDNAME Vendor_Name,
B.VNDCHKNM Vendor_Check_Name,
B.VNDCLSID Class_ID,
case B.VENDSTTS
when 1 then 'Active'
when 2 then 'Inactive'
when 3 then 'Temporary'
end Vendor_Status,
case A.TEN99TYPE
when 1 then 'Not a 1099 Vendor'
when 2 then 'Dividend'
when 3 then 'Interest'
when 4 then 'Miscellaneous'
when 5 then 'Nonemployee Compensation'
end Type,
case --Set 1099 Box Number Values
when A.TEN99TYPE = 1 then 'Not a 1099 Vendor'
when A.TEN99BOXNUMBER = 0 then 'Not a 1099 Vendor'
--1099 Type Dividend Box Numbers
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 1 then '1a - Ordinary Dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 2 then '1b - Qualified Dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 3 then '2a - Capital Gain Dist.'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 4 then '2b - Unrecap. 1250 Gain'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 5 then '2c - Section 1202 Gain'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 6 then '2d - 28% Rate Gain'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 7 then '3 - Nontaxable Dist.'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 8 then '4 - Federal Tax Withheld'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 9 then '5 - Section 199A dividends'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 10 then '6 - Investment Expense'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 11 then '7 - Foreign Tax Paid'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 12 then '9 - Cash'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 13 then '10 - Noncash'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 14 then '11 - Exempt Interest Dividend'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 15 then '12 - Specified Private Activity'
when A.TEN99TYPE = 2 and A.TEN99BOXNUMBER = 16 then '15 - State Tax Withheld'
--1099 Type Interest Box Numbers
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 1 then '1 - Interest'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 2 then '2 - Early Withdrawal'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 3 then '3 - U.S. Savings Bond Int.'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 4 then '4 - Federal Tax Withheld'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 5 then '5 - Investment Expense'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 6 then '6 - Foreign Tax Paid'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 7 then '8 - Tax-Exempt Interest'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 8 then '9 - Private Activity Bond Int.'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 9 then '10 - Market Discount'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 10 then '11 - Bond Premium'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 11 then '12 - Bond premium on Treasury obligations'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 12 then '13 - Bond premium on Tax-exempt bond'
when A.TEN99TYPE = 3 and A.TEN99BOXNUMBER = 13 then '17 - State Tax Withheld'
--1099 Type Miscellaneous Box Numbers
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 1 then '1 - Rents'
When A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 2 then '2 - Royalties'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 3 then '3 - Other Income'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 4 then '4 - Federal Tax Withheld'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 5 then '5 - Fishing Boat Proceeds'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 6 then '6 - Medical Payments'
--The 1099 Box 7 is now a checkbox on the form. Update value in: Purchasing > Cards > 1099 Details
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 7 then '8 - Substitute Payments'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 8 then '9 – Crop Insurance'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 9 then '10 - Attorney Proceeds'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 10 then '12 - 409A Deferrals'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 11 then '13 - Golden Parachute'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 12 then '15a – Section 409A Income'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 13 then '16 - State Tax Withheld'
when A.TEN99TYPE = 4 and A.TEN99BOXNUMBER = 14 then '18 - State Income'
--1099 Type NEC Box Numbers
when A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 1 then '1 - Nonemployee Compensation'
When A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 2 then '2 - Federal Tax Withheld'
when A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 3 then '3 - State Tax'
when A.TEN99TYPE = 5 and A.TEN99BOXNUMBER = 4 then '4 - State Income'
end [1099_Box_Number],
B.PYMTRMID Payment_Terms_ID,
A.YEAR1 [Year],
sum(A.TEN99AMNT) Amount_Paid_1099,
--NEW DBA FIELD
B.VENDDBA Vendor_DBA,
B.TXIDNMBR Tax_ID,
B.ADDRESS1 Address_1,
B.ADDRESS2 Address_2,
B.ADDRESS3 Address_3,
B.CITY City,
B.[STATE] [State],
B.ZIPCODE Zip_Code,
B.COUNTRY Country
FROM PM00204 A
INNER JOIN PM00200 B ON A.VENDORID = B.VENDORID
GROUP BY A.VENDORID, B.VENDNAME, B.VNDCLSID,
B.VENDSTTS, A.TEN99TYPE, B.PYMTRMID, A.YEAR1, B.ADDRESS1, B.ADDRESS2,
B.ADDRESS3, B.CITY, B.[STATE], B.ZIPCODE, B.COUNTRY, B.TXIDNMBR, B.VNDCHKNM, A.TEN99BOXNUMBER,B.VENDDBA
GO
grant select on [view_1099_includes_1099NEC] TO DYNGRP
1099 Deadlines
1099-NEC - distribute to recipients and file with IRS on or before the last day of January (February 1, 2021, since January 31 is a Sunday). Deadline applies to both paper and electronic filing. No automatic extension is available for Form 1099-NEC.
1099-MISC - distribute to recipients on or before the last day of January (February 1, 2021, since January 31 is a Sunday). File with the IRS on or before March 1, 2021 for paper filing. On or before March 31, 2021 for electronic filing.
Visit irs.gov for the latest announcements and requirements for all tax forms.
In Closing....
I hope this post helps you! If you have any questions or comments, let me know. I will be posting a video that covers this topic as well so be sure to subscribe to my YouTube Channel!
UPDATE: I uploaded a video on YouTube that covers this information.