Assuming that you do not have a WMS or have one that is without the functionality which prompts for re-profiling of the pick face, the ABC model, in Excel, requiring some basic data inputs and simple actions to follow to get your answers. What you need is a Computer with at least an i5 processor and 8GB of memory operating with a 64 bit version of Excel in a version from Office 10 or later, also that you have some fairly basic knowledge of Excel and can follow some simple instructions.

The inputs required are periodic (your choice such as weekly, bi-weekly, monthly ……) files of your stock holding (in Sales Units) by SKU and your sales (despatches, either in units or in Order Lines and units) by SKU. The period as we say is up to you. It all depends on how often you want or need to review how your sales /despatches are going and feel there would be benefit in re-profiling your pick face.

The model allows you to see your sales Pareto, which can either be by quantity or accessions (order lines) and gauge at what points you identify your “A”, “B” and “C” movers to be. The limits are variable allowing you to set the numbers of each category you have to match your pick face layout.

Once the splits are decided on the model will give you a variety of outputs:

  • Identify (also by Product Category) how many SKUS there are of each “ABC” Profile and what your product range Pareto is looking like.
  • Suggestions to move SKUs from pick locations based on their new category, either slow to fast or vice versa.
  • Identify SKUs that were moving and that have reduced their ABC category (for instance, a SKU has gone from being an “A” to now be a “C” mover) because of no stock availability. It then allows these SKUs to remain in their previous profiles pick position awaiting new deliveries.
  • Identify in particular those slower moving SKUs, or, come to that fast moving SKUs, that have excessive stock cover based on a user input stock cover.

If you have a product range where a noticeable proportion have multiples sold on each order line than we would advise that you base the sale and Pareto analysis on Order lines as opposed to sales units. You do not want a SKU that has 2 orders in a period that consists of 50 units each having the same profile as a SKU selling 100 units over 100 orders.

The data input methods can be augmented, if wanted, to give you the ability to input:

  • New SKUs especially where there is a certainty that they will be fast moving and so will be located accordingly allowing them a faster identification as to whether they are achieving their predicted sales profile, or not.
  • Sales items in order to modify their previous profile and suggest a change of pick location as appropriate.

The Model can also provides an option to warn goods receiving of any actions you may require to be carried out on different product categories or Profiles.

All recommendations for changes of location are just that, a recommendation and stock, sales and seasonality details are provided to allow you to judge whether or not it is beneficial to move a product or not. For instance, you would not want to move a SKU from a slow “C” location to a fast moving “A” location if there was just 1 left in location. You would replenish to the faster location but pick the last one to exhaustion in the old location.

Should you have any issues downloading, would like more than 5,000 SKUs or have questions in the use of the model please contact:

Graeme Smith

[email protected]

+44 07971 884225

We are also able to assist with:-

  • Identification of correctly sized pick location for each SKU in order to optimise your available cube and manage the frequency of replenishment.
  • Optimising your storage layout
  • Functional requirements and procurement of a WMS.