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:
- Header-based Last Purchase Price: globally, across locations, supply the user with the current known last purchase price.
- 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:
- 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.
- 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.
- 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.
- Non-Inventory and Service Items: we can also determine the last purchase price for items that are not inventory based.
- 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:
- 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.
- 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, you can get the UOM ratio from 2 saved search fields:
{Quantity)/NULLIF{quantityuom,0}
There is a SuiteAnswers article on price in transaction units that shows this trick.
Thank you Nick. Unfortunately, my testing shows that we don’t get the values on the purchase transactions in that aggregate that we need to support this. It’s similar to the challenge with the way the database presents the landed costs elements. I can’t access the ratio correctly in the saved search. But I am confident I can do it in SuiteQL.
SuiteAnswers ID 28524 indicates the following:
The ability to display the Quantity column in actual unit of measure used in the Transaction is still an enhancement (160480 – Item Record > History > Transactions > For the system to show the units used and not the base units). Quantity in Transaction Units is already available in the saved search, and it displays the quantities in sales units on a saved search. To get the other Quantities to use sales units as well, use these formulas.
They then show formulas similar to what you shared.
The formula I provided is also useful for Rate In Transaction units since saved search only offers Quantity in Transaction Units.
Tim Dietrich provides a slick method to populate a custom subtab with SuiteQL and live JQuery table!
https://timdietrich.me/blog/netsuite-suiteql-query-results-custom-tabs/
Thank you Nick for the offer and reference to Tim’s article. Our firm also offers a tool that renders SQL on tabs: https://blog.prolecto.com/2021/11/27/render-netsuite-sql-queries-like-saved-searches/
Marty
My company has been using NetSuite for a long time but I’m a new user. I’m trying to see how to see the “Last Purchase Price per each” since “Last Purchase Price” could be for a pack of 20 or a pack of 100. It’s not very usefully on its own.
I know there is “Location Average Cost” Which is per unit, but it’s only an average, so it might be off if costs have gone up recently.
Any help would be greatly appreciated. Thanks,
Hana
Hello Hana,
This is a good question. Just recently, talking with one of my seniors, when there is unit of measure consideration for looking up this information. The saved search system does not quite give us what we need. But he did discover that using NetSuite SuiteQL was working — meaning, he saw a unit of measure factor that would allo him to do the math AND it seemed that the SQL was giving him the values already factored. Have you tried using the Analytics Workbooks which are effectively using the SQL tools under the covers. I speculate that it will give you the extra power you are looking for.
Marty