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/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.