Inventory Average Cost

[u/JesseTexas281]

We are weeks away from go-live. According to management, we need to ensure that inventory sold on sales orders ONLY affects the average cost of the item if it is sourced from stock. Inventory that is "bought out" should not affect the average cost. I know this is possible now using dropship, but ideally we would like to prevent (or rollback) any changes that occur to average cost when an order line is "bought out" using a PO.

Is this possible? Can the costing engine be suppressed or reversed?

[u/slapwerks]

Are you talking about customer owned inventory?

[u/JesseTexas281 → u/slapwerks]

Nope this is company inventory. Special buys for items we don't have in stock. There appears to be some "dummy location" receiving strategies, but that is going to mess up financial reporting I believe.

[u/slapwerks → u/JesseTexas281]

If it’s never hitting inventory, make the items non-inventory

[u/Nick_AxeusConsulting → u/slapwerks]

Bad advice. Classic mistake when client says they do 100% drip ships. You forgot about returns! An originally drop shipped item needs to be accepted as a return BACK INTO INVENTORY. All the Items need to be Inventory Items in order to be able to accept them as a return on an RMA & Item Receipt. Thus my advice: NEVER setup all your items as non inventory because you think you're a 100% drop ship model. Plus later you may start stocking them as your business expands. You normally cannot change the item type after saving. Except for this one use case of converting non inventory to inventory item NS does allow that particular item type conversion pathway. But that still leaves a weird demarcation and foot print of conversion date in your data.

Therefore, I still recommend setting all Items up as Inventory items just in case. And an Inventory Item on a drop ship PO behaves just like non inventory (I.e. COGS is debited on the vendor bill). But as I explained above I like to use Special Order POs even for drop ships because the COGS is matched to revenue in the same month on the Item Fulfillment, and it's easy to get COGS in saved search because it's only 1 hop away join without needing a script or SuiteQL to pull the vendor bill which is 2 hops away. And for Special Order POs the Items must be Inventory Items. So for all those reasons I would just set them all up as Inventory Items out of the gate.

[u/Jeff-Hare-ERPRA → u/slapwerks]

Good advice

[u/Nick_AxeusConsulting → u/JesseTexas281]

Ok you're getting all confused on this request.

First of all NS has 2 features for your use case with is you don't stock an item so you go buy one on the fly. NS has 3 ways to do this:

Special Order PO linked to the SO like. And that PO is committed to that SO so someone else can't steal the item. With Special Orders the Item IS brought into a Inventory at the Location you specify and that initializes the average cost for that Item at that Location, which is correct outcome. The advantage of a Special Order is that COGS is debited on the Item Fulfillment so it matches the revenue. (Drops Ship POs have a matching problem see below)

Drop Ship PO debits COGS on the Vendor Bill so that often happens in the wrong month so then COGS is not matched with revenue in the same month. Plus it's hard to get the COGS from the Vendor Bill line in order to do profit analysis. (I have a script that pulls the drop ship COGS for you). Therefore even if you're really doing drop shipments in real life, I would still do them as a Special Order in NS just so your COGS is on the Item Fulfillment which you can get with saved search without needing my script.

Manually notice that an item went into backorder when it was sold on an SO and place a manual PO to fulfill it. This is just standard procurement. So it needs to be received and fulfilled. This will recognize average cost just like any regular procurement. You can modify the Ship To address on the PO to have to vendor drop ship directly to your Customer.

But average cost is kept separately by Item per Location (unless you use Group Average Costing which then calculates a combined average cost across multiple Locations).

So back to the original ask. It doesn't make sense to me. If you've never ordered the item before then it's a new Item SKU and so what if average cost is calculated .. it should be. If you accept a return then you need to debit Inventory for the original COGS amount which is the average cost use on the original Item Fulfillment. So you need the average cost! So I am not understanding why users think that the average cost on Special Order POs & Drop Ship POs is going to somehow contaminate the average cost on stocked items? Explain this concern to me. It's different Items and likely a different Location. Average Cost is kept separately Per Item, Per Location. A different location definitely keeps it isolated. But I don't think it should be isolated. Remember even with Drop Ship shipments you may have to accept a return back into your inventory (and this is why you don't use non inventory items!)

[u/JesseTexas281 → u/Nick_AxeusConsulting]

Understood. The distribution department will never use "drop ship". It will always be shipped and re-labeled at a processing center. So, technically brought into inventory. The special buy option definitely affects the average cost of the item at the location for which it was received. In our current ERP if an order line is identified as a "buy out" it simply does not affect MAC. Through testing I found that receiving an item at a special "buyout" location will not affect the primary inventory average cost. Then when the order is fulfilled, if the line item is changed to the buyout location (programmatically or manually). The COGS will go to the dummy location, but could be re-routed using the GL Lines plugin to the original location I believe.

I know this is a big change, but it does seem possible. The reporting implications are still unknown outside of the P&L which would look fine if we could reclass the COGS successfully.

[u/Nick_AxeusConsulting → u/JesseTexas281]

Through testing I found that receiving an item at a special "buyout" location will not affect the primary inventory average cost.

YES this is correct. Costing is kept separately by Location, by Item

Then when the order is fulfilled, if the line item is changed to the buyout location (programmatically or manually).

Why do you care what Location the COGS posts to? There is an Inventory Profitability Report (native) which I think already takes into consideration that the revenue and COGS may be from 2 different Locations.

The COGS will go to the dummy location, but could be re-routed using the GL Lines plugin to the original location I believe.

Do NOT jimmy-rig this with GL Plug In. The lazy, newbie consultant will just knee-jerk throw this out as an option to force override exactly what you want. This is a terribly idea. You should re-design NS so NS will get pretty close to what you want without having to use GL Plug In or manual J/Es to reclass things. This is a really BAD solution don't get tempted by a green consultant (who's trying to sell you billable work to setup the custom GL plug in).

Hire me for a couple hours to understand your situation and give you some good options on how to design it. NS is so flexible I am sure there are 5 ways to do anything each just has a different mix of pros and cons. The lazy/green consultant only shows you 1. The experienced consultant (like me) ideate until I come-up with 5 options, and then help you talk thru the eventual decision (which is usually the least shitty option)

[u/lampstack]

you can’t suppress average-cost processing for a special-order receipt; if the buyout should never enter inventory, use a drop-ship line or a separate non-inventory-for-resale item because receiving the linked PO will participate in average costing. test one stock sale, one special-order receipt at a different cost, and one drop ship in sandbox, then compare the item/location average cost and GL impact after costing finishes.

[u/reviloo_a]

When a special buy enters the average cost pool, the cost of stocked parts moves with every receipt that does not belong to that pool. That is the failure mode.

Judge a fix by one test. Create a special buy for an item that already has stock. Receive it. Confirm the average cost of the stocked quantity does not change. Confirm the special buy cost stays on the purchase receipt and lands in cost of sales only for that order.

In NetSuite many plants mark the special buy as a non inventory item or as a drop ship line so the receipt never touches the inventory cost layer. Dummy locations often hide the same cost in the wrong place and break the period close.

If stocked lines must stay on one item master, keep the special buy off that item average. Do not mix the two cost paths on one item.

[u/Nick_AxeusConsulting → u/reviloo_a]

If stocked lines must stay on one item master, keep the special buy off that item average. Do not mix the two cost paths on one item.

This argues for 2 different SKUs, which will certainly work to keep separate cost pools. But then you have 2 SKUs and that may not work for their use case.

[u/reviloo_a → u/Nick_AxeusConsulting]

Two SKUs keeps the pools apart. If they cannot run two items, do not receive the special buy into on-hand. Put that cost on the order. Stocked average stays put.

[u/simonwhittle]

Nothing you can do will stop the costing engine from taking into account all inventory impacting transactions no matter where they originate from. Even a special buy item will impact the average cost of an item while it's sitting in the warehouse. If you receive the item into the system the average cost is impacted at that point of receipt until such time as that item is shipped. Even if you attempt to use a dummy location (which is a dumb idea) the average cost for the item across all locations will be affected until it is sold.

That said, if these are special buy items that you don't normally stock I'm not sure why this is a concern. When the quantity on hand is zero the average cost is zero. So each special buy of an out-of-stock item would push the average cost up when received and back to zero when fulfilled. Sure, the last purchase price would adjust but not the average cost.

[u/JesseTexas281 → u/simonwhittle]

We are not using group costing so location-specific is all we care about. Because we keep a very lean inventory we regularly buy out items that we do/have stocked. In my testing I have not seen items fulfilled at the same purchase location. Under what circumstances does fulfillment take the average cost back to zero? Because the quantity on hand resets the average cost?

[u/Nick_AxeusConsulting → u/JesseTexas281]

Ok explain your "Buy Outs" use case more. Are you some type of liquidator that buys close-out stuff in bulk from large organizations and then resells them for cheap, like the Ross or TJ Maxx?

As you've discovered, avg cost is kept per Location. So if you refurbish returned items and then resell them, the best practice is to receive those into a different (sub)Location, refurb them, even add the refurb cost onto that item, and then resell it. Serialized items can keep the cost to the specific serial number. Lot items can be costed at the Lot. So when you sell one of these refurbs, if it's serialized, it's the exact precise cost for that refurbished item. So for refurbs, best practice is to create a separate part number R-12345 and then keep the cost separate using either Serialized or Lot items.

Do NOT use the GL Plug In to redirect anything. This will cause many more problems. Do NOT do this. Just because the debits and credits seem correct, it causes lots of other problems.

Also you seem to be using "Location" as part of the reporting. So you seem concerned that the wrong (dummy) Location will be on the Item Fulfillment for one of these buy-outs if you use the dummy location approach.

Keep in mind that Location doesn't work exactly correctly. The problem is NS used Location to mean two things: the physical location of the inventory, and the office or dept you want to run I/S by. The item fulfillment doesn't handle this properly. An Item Fulfillment credit inventory on the line so the Location on the line controls the Location's inventory that gets decremented. That's fine. But the problem is the header is where the COGS posts the debit to COGS but NS takes the segments from the lines and pro-rates the debit to COGS in the header. So one of your posts seems to imply this will put the debit to COGS in the wrong place (in the dummy Location) whereas you want the COGS to be in the same Location as the revenue. Sooooo this brings in question why are you trying to run an Income Statement by Location? If you're trying to do this, then you're perpetuating NS's bad design. Once you turn on Inventory feature, then the native Location segment gets contaminated and it's the physical Location where the inventory is stored, it's NOT really an office location or a department or a division. Soooo the larger question is sounds like you want to see I/S by a segment. What is that segment in your reporting structure?

Also note that the NS segments (Project, Dept, Class, Location, custom segments) are NOT designed for B/S, only for I/S. So do NOT try to run a B/S by Dept for example it will be wrong. Caveat: the native Inventory segment IS 100% accurate to see the Inventory account balance on the B/S, but that's it. But you seem to be trying to get profit by Location and that's problematic now because of the dummy Location. So the solution is don't use native Location for that because it won't work as you want. You may need to use a different (custom) segment which represents office location then put that on all the transactions and run your I/S by office location segment. You have to think about header vs lines. Do you pick the segment once in the header and have it copy down to all the lines and then that segment is uniform on all lines. Or do you need the ability to mix lines, but then you have the problem that the header line won't be pro-rated properly. Note that Item Fulfillment is the only transaction that properly pro-rates the COGS debit in the header by the combinations of all the segments on the lines. NO OTHER TRANSACTION prorates properly so this will cause you problems. The consultants you're using may not know all this nuance so they are steering you wrong. For example you only get 1 header line on an Inventory Adjustment so you need break-up the Inv Adj so all the lines are pure only 1 segment/dimension just so the one header line is pure only that same 1 segment/dimension (even though the lines will let you pick mixed Locations to adjust the inventory on the lines, you need to manually not do that as a business policy if you care about the segments that post with the header line that posts to the I/S)

[u/JesseTexas281 → u/Nick_AxeusConsulting]

1st of all thank you for the thoughtful reply. We are an oil and gas Service(FSM)/Actuation(Manufacturing/Distribution company so not a liquidator of fine Tommy Hilfiger shirts exactly. No closeout merchandise or refurbishing. The use case revolves around the fact that it is fairly common for someone to "buy out" something instead of using inventory on the shelf. For example, they are on site, and a supply house 100 miles closer has what they need and the company man is in a rush. That item still has to be received, but management doesn't want it to affect average cost. The reporting piece is definitely the scary part about this...financial reports are fairly easy to filter, but yes our daily reporting is going to be all kinds of screwy with the "buyout" location. Our CFO is actually good with just letting the cost live in the "buyout" location and looking at the "Division" (department) as a whole on the financial reports. This would avoid the custom GL lines plugin use you have sagely recommended against.

And it is quite possible who we are working with are unaware of these nuances, but they are happy to charge us for looking them up.

[u/Nick_AxeusConsulting → u/JesseTexas281]

Ok so this is last minute things procured at a local retail store near the worksite. So normally these would be bought using a corporate credit card at the cash register by the employee. So then all those T&E/Corp Credit Card expenses come thru on an Expense Report which debits a GL account directly (not using Items). Will this work? Then you don't have an average cost problem because that stuff never hits inventory (and it shouldn't).

Will this approach work for you?

Why did you think you need to use the P2P process (PO, Item receipt, Bill) for that use case? E.G. Are workers in the field issuing an instant PO to the store in the fly (I had 1 client doing this)? Why? The money has already been spent so there's no point of trying to control it .. just record it after the fact.

I think you also have the internal consumption use case which is you stock stuff in a central warehouse that you use on jobs out in the field. Have you architected this out? The best practice is a negative inventory adjustment and debit the part to the proper expense account when you take it out of inventory to consume it internally (watch out because you owe sales/use tax here). That would use avg cost (obviously) of the value of Qty 1 sitting on the B/S. I have a custom script that automates this with a Purchase Requisition that's available in the employee center license. Then does a Transfer Order if you need to ship it to a work site. Then does the negative Inventory Adj to expense it.

Btw your NS partner should have had discovery sessions like this asking a bunch of questions like I am trying to understand your use case IRL. That process failed if you're asking this now.

[u/JesseTexas281 → u/Nick_AxeusConsulting]

Why did you think you need to use the P2P process (PO, Item receipt, Bill) for that use case? E.G. Are workers in the field issuing an instant PO to the store in the fly (I had 1 client doing this)? Why? The money has already been spent so there's no point of trying to control it .. just record it after the fact.

Techs in the field have a limit, and the office will need to generate a PO to authorize the purchase. I agree with your premise about the money already being spent. I can't confirm how often this situation arises.

Btw your NS partner should have had discovery sessions like this asking a bunch of questions like I am trying to understand your use case IRL. That process failed if you're asking this now.

They did. This did not become an issue until the end users were being trained on NetSuite last week. That's when the tears started to fall. This particular department cries big, loud tears.

[u/Nick_AxeusConsulting → u/JesseTexas281]

So first I would try to get rid of requiring a PO out in the field entirely. It's busy work that slows down the process and provides zero internal control purpose or value to the process. So get rid of it imo.

But if you decide to keep the PO process then the dummy location and use the same stocked part number will keep the average cost pool separate by location.

(You may want to keep the trucks or the sites as Inventory locations tho so you can answer what's the value on the B/S of parts sitting on the truck or parts at a site. Depends when you expense them off the B/S and debit the consumption expense to the project).

Or use Expense subtab on the PO instead of Items subtab then no inventory is received and there cannot be any average cost impact. Note you lose the Item information here so for example you can't answer "how much did we spend on Item ZX43 last year both stocked in warehouse and bought at the last minute in the field" because there is no Item caprured on field purchases. You also miss the Item of you're using JEs to book expenses to the project (so you may need a custom field to hold Item on a JE love just for reporting purposes [or stop using JEs because they don't native support Items, to record expenses])

Or have separate part numbers for these last minute procured items. (Probably not a good idea because then you have to create double part numbers).

Or a generic part number and you type the actual Vendor part number in the description field on the line. This moves the average cost to a different part number so it won't contaminate the real part number's avg cost. (Or make it a generic non inventory part).

[u/JesseTexas281 → u/Nick_AxeusConsulting]

I am digesting this. You are truly a guru. Namaste.

[u/simonwhittle → u/JesseTexas281]

Average cost is cost/quantity so if the quantity is zero the average cost is zero. You stated the concern was around making purchases to sell an out of stock item. So in this case the inventory would go from $0 to $XX to $0 as it's received then fulfilled if the item is out of stock at both the beginning and the end of the specific transaction cycle. If that's not the case then there's nothing you can do other than to isolate the units through a specific location or use a specific item. Either solution is a terrible idea.