This article is relevant if you need to understand how NetSuite calculates an item’s Last Purchase Price.

Background

During a working session with our client’s Controller, we discussed the challenges of negative (underwater) inventory, product returns and the impacts on NetSuite group-based location costing. Indeed, maintaining inventory practices is important to produce reliable item costs and related margins. I discuss this in my 2018 article, Learn how to Reliably Measure NetSuite Gross Profit and Margin, and numerous related articles.

In this case, because the client would accept returns not connected to original sales, there would be questions about the cost when the item was returned to stock.  Returns and costing can be a complex topic because it requires consideration for accounting philosophy and strategies to refurbish, disassemble, or destroy stock.

The Controller desired to use NetSuite’s Last Purchase Price in his returns that were not connected to original sales.   However, he expressed concerns that he could not fully understand how the Last Purchase Price value is derived. Like my work to help explain daily changes in inventory quantity and value, I offered to craft searches to help explain Last Purchase Prices.

Producing Saved Search to Explain NetSuite’s Last Purchase Price

NetSuite supplies two key values on the item card:

1. Header-based Last Purchase Price: globally, across locations, supply the user with the current known last purchase price.
2. Location-based Last Purchase Price: for each location, provide the current last purchase price. NetSuite also provides a handy URL link to the transactions that derive that value.

Per NetSuite Help, “This price is determined by the most recent transaction for the item that added positive inventory”.  Most of our clients use multiple-location inventory. In those cases, NetSuite Help offers, “If multiple purchases are made on the same day with different prices and locations, then the highest price paid on that day becomes the last purchase price.”

Hence, this is the clue to developing the saved search.  Click on the image to see how the solution looks.

Last Purchase Price Saved Search Definition

The good news is that a Saved Search can be crafted to explain NetSuite’s Last Purchase Price. SuiteAnswers #92071 offers an approach. But I find it deficient. It does not cover cases with standalone vendor bills, nor does it properly help isolate the value where there are multiple purchases on the same day.

Click the images for the Saved Search definition’s two primary screenshots (criteria and results).

NetSuite’s Last Purchase Price Explanation Capacities

Here are some important capacities of this Saved Search:

1. Discover Location-Based Last Purchase Price: see how NetSuite calculates the last purchase price. I provide links to related transactions to help analysts inspect more closely.
2. Highlight Transactions Matching Global Last Purchase Price: We can quickly find the transaction producing NetSuite’s last purchase price using the saved search highlight feature.
3. Historical Last Purchase Price History: important for analysts is the ability to go back and determine the last purchase price for a given point in time.
4. Non-Inventory and Service Items: we can also determine the last purchase price for items that are not inventory based.
5. Item Sublist: conveniently view the Last Purchase Price explanation using a subtab connected to the item record.

Important Last Purchase Price Saved Search Considerations

There are indeed some challenges with NetSuite Saved Search technologies that come forth with this explanation approach:

1. Landed Costs: depending on the accounting preference setting, “Include Landed Cost In Last Purchase Price”, landed costs may be included in the calculation. Saved search notoriously has challenges here because of the way the database presents items and respective lines.
2. Unit of Measure: frustratingly, saved search does not present the unit of measure factors in transactions. Thus, we can’t do the math we need to tie out the information that we see NetSuite present.  While I can prove we are getting the right last purchase price value, for those that use a different purchase unit, the search shows it in base units — this will likely confuse analysts.

Nonetheless, I can solve these considerations using the SuiteQL tool.  Our firm has produced amazing client results with SQL. Readers may be interested in my 2021 article, Render NetSuite SQL Queries Like Saved Searches.

Get the Last Purchase Price Saved Search

I packaged the search to help the NetSuite community overcome this challenge and included it in our Prolecto Financial Saved Search Library. This bundle is available to all NetSuite End Users by simply making a request.

Naturally, if you are challenged with the landed cost or unit of measure element, we can go further by breaking out the NetSuite SQL Query Tool. Generally, starting with the Saved Search is good because it is simple to get working quickly.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you would like access to the Saved Search library, make a request and 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