This article is relevant if you are looking to a) learn about NetSuite’s new SuiteAnalytics Workbooks and b) gain access to better 1099 reporting.

Background

Over the last few years, NetSuite has dropped support for its 1099 reporting capacities. In many respects, I understand (my speculation for) the strategy. As NetSuite continues to grow globally and it keeps opening the system to the extensibility platform, it can let go of potentially challenging functionalities and leave them to the partnering community to step-in. As a NetSuite Systems Integrator, my practice thrives in all the various places where our clients need core functionality know-how and real platform skill to drive their business requirements.

I have written an article before about 1099 processing and even in 2015 for the 2014 tax year, we offered a conventional 1099 saved search download to shore up what NetSuite gave to help the general NetSuite community jump-start what they may need to extract information out of NetSuite to drive their unique 1099 report requirements. As NetSuite started to drop support for 1099 reporting, third party 1099 reporting vendors, such as Yearli, Track1099, and Sovos offered saved search bundles to effectively extract information so that data could be uploaded into their reporting systems.

As I watched this scenario unfold, I realized all along the inherent limitations of the conventional saved search engine to truly extract out the data needed to properly report 1099 information. As our NetSuite Systems Integration practice, Prolecto Resources, also utilizes contract services, we too had requirements to properly report 1099 information. Finally, in 2018, NetSuite dropped support for the built-in 1099 report and we were indeed forced to take action.

Understanding the Fundamental Challenge in 1099 Reporting

It’s important to understand the fundamental challenge in 1099 reporting so you can appreciate and learn why we need powerful tools to work with NetSuite data. This will lead me to the Analytics Workbook with some qualifications.

According to Internal Revenue Service, 1099 reporting requirements are on a cash basis. NetSuite is inherently accrual basis and to report on a cash basis is challenging due to the way the transactional database has been designed. In the most conventional sense, you record vendor bills in the period for which goods and services have been received. For example, you received services in December 2017. However, you may pay the vendor bill in 2018. Thus, on a cash basis, the expense was not incurred in 2017, it was incurred in 2018.

The 1099 saved searches we offered and others by the third party vendors do not take care of this cash basis concern. Due to the limitations of the Saved Search technology with the well-known multi-join issue, you must start your search from the Vendor Bill and use date criteria, such as within the year 2018.  Given you can’t join right to the underlying payments for the year they were actually paid, the Vendor Bill searches are typically coded with a status of “Vendor Bill: Paid In Full” to approximate what happened in the subject year. This search criterion will miss the mark in the following ways:

1. Understatements: 2017 Vendor Bills paid in 2018 will not be included. This will understate 1099 reportable amounts.
2. Overstatements: 2018 Vendor Bills paid partially through Vendor Bill Credits. Since these credits are non-cash basis, this will have the effect of overstating the 1099 reportable amounts.

The Deeper Structural NetSuite 1099 Reporting Challenge

Another nuance must be understood to gain a full appreciation of the challenge. This concern is not easy to solve and reveals the real deeper challenge in the Accrual to Cash Basis issue. Vendor Bills are linked to General Accounts which are then flagged with 1099 categories (such as Box 7: Non-Employee Compensation). This approach is generally sound. Vendor Bills can naturally contain multiple lines of which may have different 1099 categories. We need to get to that 1099 information on each Vendor Bill line. Yet, Vendor bills are paid at the header (or also known as mainline in NetSuite nomenclature) with Vendor Bill Payments and Vendor Bill Payment Credits. Vendor Bills may be paid partially and across year boundaries. Vendor bills may also be paid using other non-cash basis transactions. Thus, to determine how much each 1099 line has been paid may be subject to interpretation.

An Approach to Solve the 1099 Reporting Challenge

Since NetSuite let go of the 1099 report and my firm, Prolecto Resources, was in the situation, I knew we had to take action. Like in all challenges, weighing the estimated time and effort that it is going to take to get to an end result is how good managers and executives make sound investment decisions. Many times, the first time we encounter a problem, it takes longer to solve because we are learning. Knowing that we were going to have to invent to get to a solution, we choose to employ a strategy to build a number of saved searches to extract information into a desktop reporting tool (here, we chose the older Microsoft Access to do the job). The pattern is valuable for thinking about the data access strategy:

1. Get Vendor Bill Lines with 1099 Related Information: with a vendor bill search that can use a very wide date range, we can go after all the vendor bills where the accounts have 1099 information connected. We can also elect to only select vendor bills where the vendor qualifies as a type 1099 vendor.
2. Get Related Vendor Bill Payments in Subject Year: Since we are cash basis, we want to find all the Vendor Bill payments that are connected to vendors that qualify as 1099 vendors in the subject year. We will need the related lines as we need to link these to the Vendor Bills.
3. Get 1099 Tax and Address Information: Assuming the vendor information is current, pull all the address, legal name and tax identification information for 1099 type vendors.
4. Link Payments with Related Vendor Bills: now join the Vendor Bill Payments with the related Vendor Bills focusing on the lines. Now we have to handle the situation where the bill payments are different than the total of the linked Vendor Bills as this is an indication that we must allocate the cash payment appropriately. One approach is to perform a weighted average of the Vendor Bill line values and then allocate these to the amount of actual Vendor Bill Payment applied.
5. Summarize and Link to Tax and Address information: Once you have the detail you need, then summarize the information on Vendor and link it to Address, Legal Name, and Tax ID information.
6. Consider Other Transaction Types: I did not discuss it here but Checks should also be considered. These too can be used to pay 1099 vendors and they can have 1099 line information. Furthermore, depending on the organization’s accounting practices, it is possible even more transactions types must be considered.

This is the approach we used to solve the problem. We ultimately took the information and supplied it to a third party 1099 reporting service and were generally satisfied with the result. However, I was not at all satisfied that I had to do this processing outside of NetSuite nor was I satisfied that the community was using offerings that produced potentially incorrect results. I sat in wonder if others noticed the 1099 concerns I have raised here?

NetSuite Suite Analytics Challenge

The information was due to the Internal Revenue Service by January 31, 2019. Our Prolecto NetSuite account finally upgraded to 2019.1 on February 14, 2019 and Suite Analytics was now officially out of Beta. Thus, I thought I would use the opportunity to see how far I could get with the new reporting technology to produce the desired 1099 results since I had a control number to compare to that I now had built outside of NetSuite. While I was able to get further than the old Saved Search technology, I was not able to produce the end result. See related images to get a feel for where I was able to connect to previous year Vendor Bills and bring them in the subject year Bill Payments. Using the new Pivot feature, I could also detect situations where we had bill payments (cash basis) that were different from the Vendor Bill lines. I also had some trouble connecting to get the Tax ID information.

I sent some screenshots and feedback to the NetSuite SuiteAnalytics team — indeed, the new Suite Analytics product looks very promising. Still, at this point in time, I would need to export the data and further massage it in order to ultimately report my 1099 information.

Addendum April 26, 2019

This article inspired the NetSuite Suite Analytics team and they sponsored a desktop share to review this concern with me in detail.  The call was productive and the team recognizes the challenges.  I was pleased they understood the concern and that upcoming releases will expand our capacities.  My hope is that by January 2020, when preparing for 2019 1099 reporting, we can use the Suite Analytics Workbook feature.   As I learn more information, I will update or craft a new article.

Potential Full Solution to 1099 Output Requirements

Readers of my blog know that we have developed a tool, called the Content Renderer Engine (CRE), designed to hook together multiple saved searches. I am confident that I could use our own tool to solve this specific challenge because I could follow the data access strategy as outlined above by defining all the linked searches together and then using our technology to merge it all together into a final ready-to-go output. But just like all business managers who need to manage scarce resources, the specific problem of 1099 reporting only comes around once a year and we have now solved it for our 2018 reporting requirements. Thus, the urgency is now gone until early 2020 when we need to report 2019 1099 tax information. Since I wanted to learn what NetSuite’s new data Suite Analytics could offer, and I can see that we are not quite there, I will have to come back and reevaluate the situation near the end of 2019 to see if we need to go-all-the-way with our Content Renderer Engine.

Solved! Addendum January 25, 2020

The challenge has now been solved. Instead of using our Content Renderer Engine, I elected to create a cash basis general ledger generator instead.  See article, Finally Solved: NetSuite 1099 Cash Basis Reporting.

Streamlined!  Addendum March 16, 2024

This article provides all the context about the 1099 challenge. With the advent of NetSuite SuiteQL, another approach was used that streamlined the effort.  Please refer to my 2024 article, True NetSuite 1099 Reporting using SuiteQL Engine.

Summary of NetSuite Reporting Challenges

My hope is this article helped you see that your NetSuite 1099 reporting may have some challenges and it may be wise to “look a bit further” at the information being provided to you from other parties. It also is a great example of how real-world problems need to be methodically simplified, analyzed, and strategized to help produce the ultimate solution. In many respects, the process outlined in this article is how we think about our client challenges. It’s important to not let NetSuite represent the boundaries of what can be done — yet, we all have to use some judgment when it comes to assessing the amount of effort needed to produce the desired outcome relative to the anticipated benefit. Should you have a challenge that demands strong thinking and discernment, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube