Microsoft Dynamics NAV News & Tips
Cut Costs, Raise Profits, and Improve Service with Inventory Ranking
from Jeremy DeVries

I recently attended an “Effective Inventory Management” course, taught by Mr. Jon Schreibfeder, a leading inventory consultant. He defines Effective Inventory Management as follows: “.. it enables a company to meet or exceed customers’ expectations of product availability with the amount of each item that will maximize net profits.”

My goal for the class was to take the principles and best practices described in the course, and develop an easy to follow set of methodologies for doing these exact things in Dynamics NAV. The goal is to help our clients realize the benefits that come with effectively managing inventory (minimized inventory costs, higher profits, better customer service, fewer back orders), while using the Dynamics NAV software package that our clients know and understand.

Many very interesting topics were covered in the course: Maintaining accurate on-hand stock, forecasting, forecasting in a down market, performing physical inventory, managing “dead” inventory items, warehouse product placement, etc. However, there is one tool that is really central to managing your inventory effectively. And that is Inventory Ranking.

You’ve probably heard of the Pareto Principle before, also called the 80/20 rule. It basically means that a few (20 percent) of something are vital, while many (80 percent) are trivial. For example:

• 80% of a company’s total profits are generated from 20% of its inventory items
• 20% of your stock takes up 80% of your total warehouse space
• 20% of your salespeople generate 80% of your sales

So what does the Pareto principle have to do with effectively managing inventory? In a very basic sense, we need a daily reminder to focus 80% of our time and energy on the 20% of our inventory items that are really important. You don’t want to just work smart, you want to work smart on the right things. This is where a change of thinking needs to come in. And this is where inventory ranking comes into play.

While most companies have a very good idea of what their vital inventory items are, you may be thinking in terms of only one metric. For instance, items that generate the largest profit, or have the largest COGS. In order to effectively rank your inventory, best practice is to rank inventory items by two different metrics, by “hits” (activity), and by COGS or profit, and combine the ranks together to form a sort of matrix .

ABC’s :
We start by ranking each inventory item on an A, B, C, D, X scale. “A” items make up 80% of the total annual COGS (or hits). “B” items make up 15% of that same total, “C” items make up 4% of the total, “D” items make up 1% of the total, and X items make up the bottom tier, <1% of the total. Once you know where each inventory item lands in terms of the percentage of total breakdown specified above, you can categorize each as an A,B,C, D or X item. I’ll explain how to do this with NAV later, but just know that these are fairly straightforward calculations that can be done in a spreadsheet. The end result of the ranking will look as follows:

Item01 – A
Item02 – C
Item03 – D
Item04 – A
Etc…

Now, when you’re done the ranking for COGS, you will do the same ranking based on “hits” for each item. A “hit” is really a count of the number of times this particular item has been picked, or sold during a year. Each item will land in an A, B, C, D, or X bracket based upon the percentage of the total that particular item lands. Hits ranking is equally, if not more important than COGS ranking, since it’s essential to making stocking decisions.

After ranking both hits and COGS, we want to combine the rankings. If an item is an “A” COGS item, and a “B” hits item, we combine the two to make an “AB” item. You do this for each inventory item. The possibilities are described in the matrix below. Keep in mind that when we do this ranking, you may not see the combination in a graphical sense. You will see AA, AB, etc. Just understand what the possibilities look like.

What does this tell us?
Better yet, what do we do know with this information? Well first of all, this allows you to identify the truly crucial items in your inventory and thereby allows you to concentrate most of your time on the inventory items that really matter. For instance:

• AA items are high cost and have high activity. You will want to micro-manage these items.
• DA items have high hits, but low COGS. You can safely spend less time managing these items. They don’t cost a lot of money, so you can safely order more stock on these than you actually need, freeing up your time to forecast and order for other items. They will always sell.
• AD Items are very expensive and rarely sell. The person responsible for bringing these into stock should be questioned. Why did we bring these into inventory in the first place? A key to minimizing your cash outlay is to minimize these sorts of items into your stock.

What functions of your business should be impacted by the new rankings?

• Physical Inventory. Mr. Schreibfeder argues that he doesn’t care if a company takes physical inventory of C and D items only once a year or less. Spend less time counting the items that don’t matter as much (since they either have low cost or don’t sell very often), and spend more time counting the inventory that does, and is crucial to your business. This is called Cycle counting.
• Forecasting. Clearly the subject of another article. However, having an accurate ranking structure allows you to forecast mainly those items that matter the most, rather than items that have sporadic usage and are difficult to forecast.
• Order Quantities. By minimizing your stock quantities of “D” items, you can save large amounts of money, with few customer service ramifications.
• Liquidating Inventory. Yes, this means it’s okay to liquidate some of your “D” items. Along the lines of a Gordon Gecko quote from the movie Wall Street: Don’t get emotional about your inventory, it clouds your judgment. Just because you’ve invested a large amount of money in the inventory in the past is no reason to hold on to it now, especially if it gets more obsolete as time moves on.
• Determination of items to stock. This allows you to determine whether or not you should even carry certain items. Go through the list, one by one. If the “D” items aren’t crucial for other A & B sales, meaning, if you didn’t carry the D’s, it wouldn’t affect any of your other sales, why are you carrying them? If you are planning on carrying an item, and it is very similar to another D item that you already have in stock.. should you even carry this one as well? This also allows for salesperson follow-ups. “Why did we bring this item into stock? We’ve only sold X this year. “

So how can we do all of this in NAV?
First and foremost, there is no embedded ranking routine located in the standard NAV product. Before you say “Oh no!”, we can still accommodate all of these rankings, fairly easily. You have several options.

• Purchase a NAV module called “Advanced Forecasting & Procurement.” This module is meant to automate these sorts of rankings and incorporate them into your actual forecasting and purchasing processes.
• Jet Reports. If you are an active Jet Reports user, then you know how easy it is to generate your own reports out of NAV and bring them into an easy to understand spreadsheet. If you wanted to do this yourself, what numbers would we need to pull from NAV to get the above rankings?
  • First, bring out a list of every inventory item in your system. Easy. (Item table).
  • Next, you will need to obtain the COGS for each inventory item. Easy. Sum all “Cost Amount (Actual)” fields from the item ledger entry table where type = Sale for the item number.
  • Now, to calculate each item’s “hits”, you will need to pull a count of all records from the “Item Ledger Entry” table with the following criteria: The “Type” field must be “Item”, “Quantity” field must be negative (so we don’t double count credit memos), and the “Entry Type” is “Sale”.
  • You should now have the numbers that you need from NAV.
• If I’ve lost you on the above, it’s OK. We have pre-written a Jet Report that can generate these numbers for you. Even if you aren’t a Jet Reports user, we have ways of manually pulling the data for you into a spreadsheet that will calculate your rankings.
• The end result is a list of each inventory item, and their respective Ranks
• We have developed tools to even bring these rankings back into your live system, and attach the current item ranking to each item record. This will allow you to easily filter your ranks in reports, forms and routines.

Ranking in this way can completely change your way of thinking about your inventory. The good news is that this is fairly simple to do. However, this is just a start - the next question is what to do with the insight you've gained. Your actions on these new rankings are where the rubber meets the road.

Contact Mike Packard, Integrated Computer Systems Support, at 425-820-6120 for help or to learn more.

 

Inventory ranking allows you to identify the truly crucial items in your inventory. It thereby allows you to concentrate most of your time on the inventory items that really matter.