How To Produce Additional Transactional Financial Information When Learned Late in Process

This article is relevant if you need to produce enhanced NetSuite revenue reporting with greater detailed dimensions or other attribute data but you don’t have detailed classification information until late in your operational practice.

Background

During a NetSuite implementation, one of the information design elements is to map key information into the department and class dimension. The mapping exercise is required to drive powerful classification reporting. From a financial statement perspective, the department and class are generalizations which only become meaningful when you map the data into an invoice (or other construct with posting impact) which will then show up as a transactional reporting element.

This consideration for mapping reporting elements is standard implementation planning work. However, we have encountered client situations where information that is very important for rollups and filtering is not easily known early in the sales cycle; When information is learned late, the conventional way that NetSuite wants to organize transaction lines, the key construct to route information to the general ledger, is insufficient. This will be clearer when I express a real client situation.

One of our clients in the media business offering digital marketing expertise. They sell advertising services that represent brand impressions and other targeted end user calls to action. In our client’s case, the Sales Order (SO) was renamed to be called an Insertion Order (IO). Once the client has an Insertion Order (SO) for their customer’s marketing campaign, they then need to deliver the promised campaign services. During the delivery process (typically denoted by the item fulfillment structure), the client learns many things which they could not see until actually delivery. In our client’s case, there were two key revenue reporting attributes desired: The “Country of Origin” and the “Computing Device Platform”. It is here where this gets interesting. But first I must help you understand NetSuite’s standard mechanism to get data from a Sales Order to an Invoice to help you see the challenge.

NetSuite Transaction Lines from Sales Order, through Item Fulfillment, to Invoicing

NetSuite’s sales order model is to provide transaction lines each represented by a respective item reference (see related image). The sales order represents the party’s agreement in terms of an item’s quantity and price. All good. NetSuite further supports the concept of multiple item fulfillments to allow for delivery on promises made to customers. This too is good because it may take multiple fulfillments to deliver. An item fulfillment comes into existence effectively through a sales order copy construct (technically called a Transform in NetSuite) into an item fulfillment. Each line on the sales order will effectively have a corresponding line on its respective child item fulfillment. The purpose of the item fulfillment is to indicate the quantities of the item that were delivered on a specific date.

When an item fulfillment line item is shipped (delivered), a respective line counter on the parent sales order gets incremented. This allows the sales order to hold the quantity promised and the quantity shipped as a control structure. If you introduce another item fulfillment, you continue to increment the sales order quantity shipped until effectively it meets the order quantity. This design is good in a conventional sense.

Going further, when it is time to produce an invoice, the sales order effectively becomes a template for the invoice structure. The invoice quantity is a factor of the number of unbilled sales order quantities (yet another line counter) which is derived from the quantity shipped counter. Structurally, you are offered one line on the invoice for every line on the sales order.

Finally, these structures are loosely linked. What this means is that lines on the item fulfillment and the invoice are not tightly linked, one-to-one, on the sales order. Lines on an invoice have a relationship to items on the item fulfillment — but they are distant from each other.  The meaning of this will become apparent in the following sections.

The Need for More than a Single Respective NetSuite Invoice Line to Sales Order Line

With that background, now comes the interesting challenge. We need to produce a single invoice to the customer in a conventional sense (a single line of information for the item), yet we need much more information to route to the income statement for financial reporting than can be provided on a single invoice line. One might think: “just add more lines to the invoice to capture the extra information”. However, if you simply add more line items to an invoice, which is indeed easy, those new invoice lines are not connected to the parent sales order and thus they will not increment your sales order quantity billed counter. Thus your control structure disappears and this will affect your billing operations. Furthermore, additional lines on an invoice, with no relevant distinguishing customer information, is not the way the customers wants to receive a bill — it will produce questions and drive up customer service costs.

Capturing More Line Information

The first matter we needed to take care of is the need to actually capture the additional information. To do this, we added two new custom transaction columns for “Country of Origin” and “Computing Device Platform”. During item fulfillment, we developed a practice where each combination of “Country of Origin” and “Computing Device Platform” produced a single item fulfillment transaction. With this rule, the sales order counters remain reliable. The effort though demands the production of multiple item fulfillments (in our client’s case, this can be automated by item fulfillment generator program: a separate topic).

Prior to billing, we now have a list of item fulfillments with the extra information we care about. While not specific to our client, the item fulfillment information may have GL impact (especially in inventory operations for cost of goods sold) and it has the extra custom dimensions. Our challenge now is to bring these items fulfillments to the invoice in a manner that takes care of our revenue reporting AND takes care of our bill presentation while keeping our billing operations smooth.

The Practice to Bring Multiple Enhanced Item Fulfillment Lines to the Invoice

As discussed, NetSuite will automatically summarize all the item fulfillments when generating the invoice due to the loosely linked nature of the item fulfillment lines. NetSuite’s summarization feature effectively loses the extra information we captured on the item fulfillment. The Invoice line is only coupled to the Item Fulfillment through the Sales Order shipped quantity counter. The lines that NetSuite provides during the Sales Order copy (technically, a transform) to an invoice must be on the invoice to control the sales order counters and bring over sales order price information.

Yet, we need that extra information we captured on the Item Fulfillment. The first thing we must do is add respective negative lines to reverse out the effect of NetSuite’s standard line behavior. When we reverse out the lines, the invoice amount due is now zero. Yet, our sales order control counters are still correct.

We then do some interesting SuiteScript handi-work. We must go back to each unbilled item fulfillment and find all the information about “Country of Origin” and “Computing Device Platform” respecting shipped quantities. This information is then grouped by item, “Country of Origin” and “Computing Device Platform” summarizing on shipped quantity. The result then is added as new lines to the invoice. Once this is done, we now have the proper revenue reporting and the invoice total is correct.  See related image for the invoice structure.

However, while we solved our reporting challenge, our invoice now has many extra lines that are not organized well for customer presentation.

NetSuite Bill Presentment to Consolidate Similar Line Items

Here is where we leverage the Content Rendering Engine (CRE)  to give us powerful control over the NetSuite Advanced PDF engine. The lines that NetSuite initially created are fine for customer presentation. We just don’t want the other lines to show up. One approach would be to build logic into the native NetSuite Advanced PDF template to refine the line item loop structure to exclude the extra lines we placed. In our client’s case, because this was a One World implementation and there are known issues in getting proper subsidiary information in the native Advanced PDF template (yet another topic), we used the CRE tool to perform saved searches to get just the line data we needed. The Advanced PDF template then has simple looping logic without respect for excluding non-printing lines. We also can then control and confirm that line based totals equal header information because we can include invoice summary saved searches in our Advanced PDF logic.

The end result is perfect financial reporting with perfect customer bill presentment. All parties win.

Demand More from NetSuite: Get It Done Right

My goal with the article is to illustrate that the NetSuite business platform is quite adaptable to meet customer business requirements. Yet, capacity is nothing unless there is a set of actors who offer creativity and tactical competency to invent and ultimately deliver on innovative solutions. Should you have a complex business or technical situation that demands NetSuite thought leadership, let’s have a conversation.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's 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 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to setup a conversation.

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

| Tags: , , | Category: Accounting, ERP, NetSuite, Reporting | 2 Comments

2 Comments

  1. Phoebe
    Posted April 13, 2016 at 4:04 am | Permalink

    Hi Marty,

    Good article to share the practice on how to add unknown information from IF to the invoice.

    I do have few questions about this implementation:

    1. You said during IF, you combine the same custom column items together. Is this happening after the IF is saved or when the IF is first open? If it’s combined when it first open, wouldn’t the unknown information has not been added yet?

    2. In your example, if for 1 line item, for example with quantity of 100 on the SO, and when creating the IF, 36 of this item belong to “country of origin”=Canada and the 64 of this item belong to “country of origin”=US, in this case two IFs should be created?

    3. On the invoice image you provided, what are the first 4 lines? Are those the line items that haven’t been fulfilled yet? If they haven’t been fulfilled yet, especially in your case, shouldn’t they not to be invoiced until it is fulfilled? If that’s the case, why can’t you direct use the nature setup in NS to exclude unfulfilled lines from invoice so that those lines will not show up?

    Thanks
    Phoebe

  2. Posted April 17, 2016 at 3:11 pm | Permalink

    Hi Phoebe,

    Let me respond to each questions: I think I may have misled you.

    1. We need a separate item fulfillment if there will be different extra column data to capture. We can’t add more lines on the item fulfillment.
    2. Yes, you have it. We need two item fulfillments to capture both the Canada and US country of origin.
    3. The first two lines of the invoice are coming from NetSuite invoice operations and have been partially fulfilled. Note how they show back ordered? This invoice is for the fulfilled portion. The following two lines are added via script to reverse out the effects of NetSuite native work. Yet, we have to go back and split up the lines because we have item fulfillments from different countries.

    I hope that clarifies. This topic is confusing.

    Marty

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>