Using Auto Calculate in NetSuite’s Advanced Inventory Management

By | June 14, 2017

NetSuite’s Advanced Inventory Management allows the user to fine tune their ordering process to carry the desired amount of inventory and order before stock will run out. Using the Auto Calculate feature, NetSuite will constantly change stock levels, reorder points and lead times based on previous sales and the user’s desires for stock levels.

To begin with, the defaults should be setup in Setup > Accounting > Inventory Management Preferences.

The following is a discussion of how it all works together.

 

Preferred Stock Level Days

The total number of days of inventory you want in stock.

Preferred Stock Level
= Daily Demand * Preferred Stock Level Days

Daily Demand is calculated by the system looking at sales over a period of time (set in the defaults mentioned earlier) and averaging those sales to calculate daily demand.

 

Safety Stock Days

The Safety Stock is the amount of an item we want to keep in stock at all times. The Safety Stock Days will always be a number smaller than the Preferred Stock Level Days. If Safety Stock Days is 30, it means the system will always try to make sure a new order is placed and received before 30 days of inventory is reached.

 

Lead Time

The average number of days between ordering this item from the vendor and receiving it. This should be set manually for overseas items or for items that aren’t ordered monthly. If there aren’t enough POs received in the period of time the system looks back at, it won’t be able to calculate a good number.

 

Reorder Point

The quantity level at which we need to reorder to meet the Safety Stock requirements.
Reorder Point = (Average Lead Time Days + Safety Stock Days) * Daily Demand

EXAMPLES

Normal Product Sales

Let’s say over the past 5 months, the average monthly sales are 60 per month. Daily Demand = 60 per month / 30 days = 2.
If we set Preferred Stock Level Days to 60, the system will calculate the Preferred Stock Level as Daily Demand * Preferred Stock Level Days = 2 * 60 = 120.

Now let’s assume Lead Time Days of 10 and Safety Stock Days of 30.

The Reorder Point will then be: (Lead Time Days + Safety Stock Days) * Daily Demand =  (10 + 30) * 2 = 80.

Every time you get 80 or less in stock, the system will notify you to order again to bring the total up to 120.

Something to Remember

Let’s say Reorder Multiple = 40 because that is a pallet’s worth of the item. With a Preferred Stock Level of 120, you would have 3 pallets in the warehouse. If a sales order is placed that leaves 79 in stock, the system will want you to order 80 units to get to or above 120 since the Reorder Multiple forces it to reorder in multiples of that number.

Now how many will be on the shelf when the order arrives? With daily demand of 2, you should sell 20 units (lead time * daily demand) before the new order arrives into inventory. This is calculated as Number of Units in Stock – Sold after PO but Before Receiving + Amount Ordered =  79-20+80 = 139 units or 3.475 pallets.

This is why if we set a Reorder Multiple, we always have to allow for more space in the warehouse than the Preferred Stock Level number.

 

Spikey Product Sales

Let’s say over the past 5 months, the average monthly sales are 60 per month but one customer occasionally orders 45 units.
If we use the settings above, we’ll be okay. At the lowest point, which is the day before you receive the PO, you’ll have 79 – 20 = 59 units.

But if we do the same thing with 45 days as the Preferred Stock Level Days, we’re screwed.

Again our Daily Demand = 60 per month / 30 days = 2.
If your Preferred Stock Level Days is 45: Preferred Stock Level = Daily Demand * Preferred Stock Level Days = 2 * 45 = 90.

Now let’s assume Lead Time Days of 10 and Safety Stock of 15.

The Reorder Point will then be: (10 + 15) * 2 = 50.

At the point you have 50 or fewer in stock, the system will suggest that you order again. Assuming you order right away, the lowest point of inventory in this case will be 50 – 20 = 30 units. Not enough to handle the occasional order of 45 units should that order come in after the PO is created but before it is received. This is the 10 day lead time.

For items that are spikey, the Preferred Stock Days and Safety Stock Days need to be larger to take the spike into account. To make the process easier I created a spreadsheet that allows me to easily calculate the Safety Stock Days to handle products that spike in big ways.

 

Okay class, your inventory instruction is over for the day. They’ll be a test tomorrow!

Leave a Reply

Your email address will not be published. Required fields are marked *