What is negative inventory (or ‘underwater inventory’), how does it impact costs and how does NetSuite’s Cloud platform handle it? Head to our article on underwater selling to bring yourself up to speed.
Some of the main challenges facing a warehouse manager or inventory controller are:
- Stock control
- Timely ordering of items
- Preventing ‘underwater inventory’ (when the system takes the quantity of the items below zero)
This problem often occurs because of:
- Incorrect data entry
- No proper notification
- Lack of functionality in the existing system
- Lack of options to control and fix negative inventory
I have come across a lot of articles, tutorial, and explanations given by NetSuite and my colleagues. They all have helped me get a better and stronger technical and functional understanding of NetSuite functionalities.
One of the articles that I still keep in my armor is how NetSuite looks into Underwater inventory. I use this a lot when I deal with new and existing customers. Here, let me share this with you. (Source NetSuite article: 28074)
Selling an item when the data shows that you do not have the item in stock is known as an “underwater” sale. Inventory is in an underwater state when the on-hand quantity of the item is below zero. You should avoid entering an item on a sale transaction if the on-hand count of the item is zero or a negative amount.
Problems can arise if you enter sale transactions over a period when an item is underwater because NetSuite has difficulty calculating the cost of the item on those sale transactions. These cost calculations from underwater sales can lead to skewed results for reports and inventory data.
During the time that the on-hand count is below zero, on sales transactions that include the item, costing information at the time of the sale is shifted to another date. The estimated cost is calculated based on the last known cost when the item was in stock.
At a later date when more of the item is received into stock, the true cost can be calculated, and an adjustment can be posted to correct for transaction lines entered when the item was underwater. However, because the adjustment posts with the item receipt, the correction does not post with the original estimated cost.
For example, consider the following scenario where inventory is sold underwater:
- On March 30, an invoice is entered that sells 100 widgets and brings the on hand count of widgets to -100 widgets.
- Based on historical costing, the cost of the widgets is estimated at $10 each. Therefore, the cost of goods sold for the 100 widgets = ($10 x 100) = $1000.
- On April 2, a purchase is entered for 100 widgets at a cost of $12 each. The actual cost of the widgets is ($12 x 100) = $1200. An adjustment of $200 is entered to correct the purchase price of the widgets.
After you have entered a purchase that brings an underwater item count back above zero, the costing adjustments that are entered do balance totals across affected periods. However, when viewing reports by period, if a corresponding sale and purchase post in different periods, you may still see the results of costs shifted from one period to another.
If you consider the balance for March and April together, then the total is balanced because the sale in March and the purchase in April that includes the adjustment are both considered.
However, if you consider only sales in the month of March, the total is off by $200 because the adjustment that posted with the item receipt does not show in the totals. Likewise, if you consider only purchases for the month of April, the total is off by $200 because the adjustment is included in the total without the sale.
If you do not enter purchases to bring the item count back above zero, the costing estimates remain in the system. For example, a costing estimate can be $0.00 if there is no purchase history and can cause larger adjustments after you enter a purchase to bring the item count back above zero.
Important: The longer the period between the underwater sale of the item and the purchase that brings the count back above zero, the more skewed your data and reports can be.
For example, if an item is underwater only three days before you enter a purchase that brings it back above zero, reports and data will be less skewed than if an item is underwater for three months.
Why should I care about selling underwater inventory?
- Data is less accurate– When you have underwater items in your inventory, your inventory data is less accurate. Inaccurate inventory data can affect other aspects of your integrated NetSuite account and can potentially skew data in your chart of accounts, reports, and other areas
- Transactions are more confusing– Purchases that include costing estimates are often confusing for users who are not familiar with the costing workflow for underwater sales. Trying to sort out transactions associated with underwater sales can be time consuming and counterproductive
- Records are less fit for audits– Because transactions that include underwater items also include estimates and adjustments, they are less likely to be well received by auditors
How can I tell if I am selling underwater inventory?
- The sale transaction shows a negative on-hand count of the item
- You receive a warning that the item is not in stock
How can I avoid selling underwater inventory?
You can avoid being in an underwater state with inventory items by following these tips:
- Insist on a prompt entry of item receipts
- Require item receipts to be entered with the date of receipt instead of the entry date if the receipt is not entered the same day it is received
- Always use sales orders to sell inventory
- Always fulfil orders from sales orders
- Set the Fulfil Based on Commitment preference to Limit to Committed.
- Avoid entering standalone cash sales and invoices. Standalone transactions have no commitments or checks and balances to prevent you from selling out or going underwater.
- Use the Inventory Level Warnings preference. This preference gives you instant feedback on each transaction as to whether or not you have items in stock.
- Perform a physical inventory count on a regular basis to assess whether your inventory data is consistent with the physical count of your stock.
If the physical count is higher or lower than the quantity on hand based on the item record, the count needs to be reconciled to match actual inventory levels.
- Use the Review Negative Inventory page to identify inventory items that are underwater
About Jebin Varghese
Jebin is IBT’s Oracle NetSuite Lead Solution Consultant.
Jebin is an ERP Consultant with over 3 years of experience delivering over 50 projects and implementations for SME’s with Oracle NetSuite. Previously he had 9 yrs of experience in IT Systems and Database administration before turning to ERP Consultation. With a Bachelor’s Degree in Computer Engineering, Jebin uses his IT knowledge to extend his skills on implementations, customisations of NetSuite, SuiteAnalytics and SuiteFlow. Jebin has been exposed to various industries such as Wholesale Distribution, Manufacturing, General Business and Professional Services.
For further practical hints and tips from Jebin on how to get the most from NetSuite, you can find his ‘Ask Jebin’ Q&A series in our NetSuite Resources page or in IBT’s Blog/News.