Weekly Dynamic: Inventory Adjustment Totals

With Convergence almost here, I expect the next several Weekly Dynamics to highlight new DynamicsGP 9.0 features so we'll look at an old frustration today.

When making an inventory adjustment, GP doesn't tell you what the total dollar value of the adjustment is and negative adjustments are shown as $0. Yeah, it's pretty frustrating. I understand why, but that doesn't make it better. When doing a full inventory count, it's really a pain. Here's the why:

GP maintains the inventory layers (what the cost of an individual product was) even when your costing method does not require it. This is what allows you to switch between different inventory methods. So, if you use Average Cost, which doesn't have layers, and want to move to FIFO, which does, the data is there and you can make the change. A simple example, I buy 2 of the same thing, one for a $1, one for $3. The average is $2. With average cost, I sell one, and $2 leaves inventory, $2 is left. Same scenario with FIFO, I sell one, the first one is sold for a $1 and $3 is left in inventory.

Why do you care? Because when you have a negative inventory adjustment, GP shows zero because the adjustment could span multiple layers and it can't give you a single unit cost. There may be multiple unit costs. Increase adjustments show the cost because GP assumes that you're increasing it based on what the last cost was. If not, GP assumes you're smart enough to change it. GP doesn't assume that you'll get the layer cost data right on a decrease adjustment.

The frustration is that after counting inventory, you can't tell if you have a $2,000 adjustment or a $200,000 adjustment until it gets to the GL. That difference might be a simple unit of measure problem, a bad count, or your career headed down the toilet. You might want to know that before it hits the GL. Sorry for the long explanation but explaining this has helped me calm down a few CFO's.

Here's my workaround.

1) Enter the Inventory adjustment transaction.
2) Create a smartlist that only shows that transaction.
3) Make sure to include quantity, u of m, extended cost and current cost.
4) Export to Excel.
5) Add a column showing quantity x current cost and call it EST ADJ.

This gives you an estimate of the adjustment based on what the item costs today. It's not perfect, but normally I'm looking for a numbers around a materiality threshold and this will typically get you materially close.

6) Total the EST ADJ column

You now have your estimated adjustement total and you can also sort by the EST ADJ column to find the highest cost variances for additional investigation. This will help you find that bad count or whacky U of M before you call a headhunter.