Effective Inventory Item Ranking

Jon Schreibfeder is an inventory guru who has written several white papers for Microsoft and provides many of his articles for free on his website. If you are responsible for inventory in your organization, these articles can make you look like a genius.

His article on item ranking proposes a 3 way ranking system for items based on total cost of goods sold, number of hits, and profitability. Dynamics GP only stores one rank and provides 4 methods of calculating this rank using the Item ABC Analysis Routine:

The Usage Value option would rank item based on total cost of goods sold for a time period but to apply the other rankings requires some SQL work.

Hits: The number of times an item has been sold is calculated from the SOP30300 table using only non-voided invoices.

select items.itemnmbr, items.itemdesc, isnull(soplinesum.hits,0)
from iv00101 as items
left join (select COUNT(itemnmbr) as hits, itemnmbr from sop30300 as a
join sop30200 as b on a.soptype = b.soptype and a.sopnumbe = b.sopnumbe
where b.soptype = 3 and b.voidstts = 0
group by a.itemnmbr) as soplinesum
on items.itemnmbr = soplinesum.itemnmbr

Profitability: The query to calculate item profitability is similar but uses extended price and cost to return a sum of gross profit by item.

select items.itemnmbr, items.itemdesc, isnull(soplinesum.profit,0)
from iv00101 as items
left join (select sum(a.xtndprce-a.extdcost) as profit, itemnmbr from sop30300 as a
join sop30200 as b on a.soptype = b.soptype and a.sopnumbe = b.sopnumbe
where b.soptype = 3 and b.voidstts = 0
group by a.itemnmbr) as soplinesum
on items.itemnmbr = soplinesum.itemnmbr

Now the real fun begins. The results from these queries can be exported to Excel for analysis. Create a formula that returns the percentage of total hits or profit for each item and then sort by that percentage. This will give you a basis to begin assigning ABC ranks by hits and profitability.

If you want to store all 3 ranks in Dynamics GP consider using inventory categories or extender fields.

You may also like

Trinity Purchasing Suite FAQs
on August 7, 2019
business software

Hundreds of Microsoft Dynamics GP customers have enhanced their ERP system with the advanced ...

commentIcon 0 Comments
Staff Author
How Late Payments Impact Smaller ...
on August 7, 2019
business software

Imagine if you were shopping and instead of paying for your items, you announced to the store clerk ...

commentIcon 0 Comments
Staff Author
6 Remarkable Benefits of Accounts ...
on August 7, 2019
business software

Business is great, product is moving out the door, and accounts receivable is way up. However, ...

commentIcon 0 Comments
Staff Author
Join Dynavistics in Helping a ...
on July 23, 2019
business software

On July 4th 0f 2018, fellow Microsoft MVP, GPUG All-Star, Dynamics GP blogger, and past ...

commentIcon 0 Comments
Staff Author
How Business Seasonality Impacts ...
on August 7, 2019
business software

We all know that leaves turn, snow falls, and seasons change. But did you know that your customer ...

commentIcon 0 Comments
Staff Author
Tackling Food Distribution ...
on August 7, 2019
business software

Demands placed on food distribution companies can be intense. Whether you’re in the general ...

commentIcon 0 Comments
Staff Author