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:
- Member Quantity
- Member Base Price
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 to 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:
- 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.
- 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.
- 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.
2022 Addendum
Meir B., a senior analyst on the Prolecto Resources team, has produced some new kit costing outcomes using the NetSuite SuiteQL (SQL) set of tools. Please see his article for further information. He can be reached via his blog.
Get Expert NetSuite Help
See Related Articles
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
