Has anyone got any experience of identifying slow moving stock via
queries?

On Oct 11, 7:29*am, BobbyDazzler <david.a.mitch... (AT) inbox (DOT) com> wrote:
If you have the ability to query data at different points in time,
perhaps you could calculate an Inventory Turnover rate? You would
need the inventory value as of a begin date, the inventory value as of
an end date, and the Cost of Goods Sold during that period (average
cost per unit) x (# Units sold). The Inventory Turnover = CGS /
(Average Inventory Value). The higher the number, the faster the
turnover (faster moving goods). The lower the turnover identifies the
slower moving stock.

The easiest way I have found is to work out the rate of sale. This is
done on a per item per unit time basis. This is common practice in
Category Management and retail in general. What you will typically find
is that the rate changes over time, especially with so with slow moving
items.

What I would suggest is to decide on a suitable unit of time to see the
stock movements in. For example per week, per month, per quarter etc...
This then allows you to see how the stock moves over time from one
period to another. Sum up the number of items sold for each particular
item (or group of items), per period (the one you decided on) and then
you can see the rate of sale over time.

To make this easier I find it helpful to create a 'Time' dimension, a
table that lists the start and end dates of each time period you wish to
consider (eg/ time_id, year, month, week, start date, end date), giving
each a unique key. Add a foreign key to the sales table (or perhaps do
this via a query if you only need it as a one off), that links the
individual item sales to the time dimension by the date they are sold.
Your queries can now be made very easily. It is also helpful to order
the results of the query by the rate of sale in descending order.

For more advanced sales modelling you need to implement more structure
to the data, such as categorisation of the items, store the sales
results belong to (for multi store operations), possibly even the
physical position in the store (eg/ isle number, endcap, checkout /
register impulse buy, etc). You will find that some items sell better in
certain areas, or in combination with other items, while some are simply
dead stock. Doing this type of analysis can be expanded in many ways, as
I am sure you can imagine, but I would start with the rate of sale first
and if you need to then build from there.
--
Cheers

The Frog