How To: Generate NetSuite Item Weighted Price from Kits

This article is relevant if you are using NetSuite item kits but you need to determine what the value of each member item relative to the kit price.

Background

Recently, a new client was implemented by a team that may have not read my article, “Understand NetSuite Item Groups vs. Kits to Produce Superior Reporting’. ¬†The client’s business is heavily dependent on item based sales and management needs to understand real item sales, cost, and margin. ¬†The trouble is that the client was advised to use Item Kits versus Item Groups to produce their item bundles. ¬†As such, until they worked with us, they were not able to properly get information on item sales because the kit would obscure the underlying item revenue.

This article helps to take care of the first concern: namely, the weighted price of the member items.

Generating NetSuite Kit Member Item Values

The key to this problem is to see that that kit members are based on two key factors:

  1. Member Quantity
  2. Member Base Price
Accordingly, it is reasonable to determine the weighted average value of each member item relative to the kit price. ¬†While this is not exact in terms of the actual member item price, it properly allows you to break apart a kit into its constituent components so that additional analysis can be performed. ¬†The real opening to this solution is in my article, “Solving the NetSuite Cumulative Saved Search Tally Challenge’. ¬†Here, the trick is to learn how to drive an Oracle based aggregate search into a standard non summary search by tricking NetSuite’s SQL parser.
Here is the NetSuite formula that will determine if an item is a kit and if so, it will provide the weighted average value:
CASE {type}
  WHEN 'Kit/Package' THEN {memberquantity}* {memberitem.price}/sum/* comment */({memberquantity}* {memberitem.price}) OVER (PARTITION BY {name} ORDER BY {internalid})*{price}
  ELSE {price}
END

The real work is being done in the special aggregate function because it gives us the relative value of the sum of the member items without respect of the kit price. The sum value of the member items becomes the denominator where the extended price of each member item becomes the numerator. That gives us the weight. We then can multiple the weight by the kit price and we thus have the respective value of the member item.  Click the image to see more detail on the saved search.

Not Perfect but a Reasonable Compromise

As noted in the article between Item Groups and Kits, I caution people to use kits.  However, they are in use in the community and as such, it may be challenging to migrate away from them for better reporting.  Accordingly, with this approach, we are producing a compromise as follows:

  1. The kit member item relative value may not really be how you want to assign value.   At least this approach comes at no extra effort.
  2. If prices change for underlying kit member values over time, the historical analysis will then change.  This is because we have no built-in way to snapshot what the base price of a member item was at the time of a sale.
  3. The crafty search technique does not allow us to aggregate our work.  For this, we need to use our Content Renderer Engine (CRE), if practical, to give us aggregated results.

Get Expert NetSuite Help

My hope is that this article will help many organizations running NetSuite so that we collectively strengthen our resolve to get more from the platform. ¬†Much of the client work my firm does is advise on the high performance use of NetSuite. ¬†Ideally, we avoid making compromises and architect the implementation well from the start. ¬†Yet, ERP implementations are complex which involve countless design decisions. ¬†Many times, people can not see the situation they will be in until they are in the middle of it. ¬†If you believe your organization can benefit by working with high caliber NetSuite talent, let’s talk about your specific concern.
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, NetSuite, Reporting | Leave a comment

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>