in this video we're going to calculate
the kpi slope for slow moving and
obsolete inventory this is one of the
two most important kpi for your supply
chain and I'm going to show you a
step-by-step tutorial in Excel that you
can download below the video and the
idea is really to track these kpis and
make sure that you have a simulation
profitable and a sustainable business
with as little Obsolete and slow moving
stock as possible
[Music]
all right so if you want to have a
profitable stipulation in business you
need to focus on three main pillars
service cost and inventory and in this
specific video we're going to focus on
the slop I really recommend you to check
my inventory return of a ratio before to
focus on the global stock level but in
this one we're going to focus
specifically in the slow moving and
obsolete inventory and that's two
different apis but just to keep it very
simple the percentage of slab is the
quality of your inventory and the lower
is the better and the idea is really to
focus first on tracking this kPa then
you can analyze Focus optimize and if
necessary Implement Innovations to
improve the performance of your supply
chain alright so before going to excel
it's very important to understand the
difference between Obsolete and slow
moving the alsolete we can also call it
discontinued inventory or dead inventory
this is this kind of products like this
banana you don't want to have this
banana or this milk or eggs with an
expiry date in the in the past same for
like Medical products I was working the
in the Cosmetic Industries and we had we
had a lot of problem with like like
expired product that we could not ship
to the Distributors and we had to
destroy this product or donate if it was
possible at the end you have a huge
impact on the business if you cannot
sell these products because you have to
pay for it you have to pay the logistics
the inventory request and at the end you
cannot sell them and you're gonna lose
up to 100 of the value or even more if
you have to pay for the destruction okay
you can also like in the fashion
industry for example this is the summer
collection and we are moving to the
winter collection it will be much more
difficult to sell this beautiful flower
dress and we called it like obsolete
inventory because this is the the past
season or collection and we have to do
like a promotion thirty percent fifty
percent another time this is a big cause
for the for your company and we have to
minimize this value as much as possible
same for the industry you could have for
example maintenance products so let's
say you you are like engine
for the car industry and you had a
diesel Vision I know everyone is moving
to the the electric version and yeah no
one wants to buy the diesel version so
you'll have to do maybe a 80 or 90
promotions with another time and an
impact like for your business and the
profitability of your company then we
have slow moving inventory so we can
also call it access inventory Edge
inventory or leftover inventory these
kind of products you you need these
products today they're not like the the
previous collection if I take for
example these Nike shoes you do need
Nike shoes this is the current
collection and my optimal stock will be
four quantities to Section 8 and 88 of
service but your actual stock is not
four it's like 24 so you have a lot of
excess and unfortunately you don't need
that much stock and this excess maybe at
one point you'll have to pay extra cost
maybe for Logistics you would probably
do maybe future discount because you
have maybe a six months one year two
years inventory and you want to switch
to the new collection so that's an extra
cost for your business that you don't
need to guarantee like a good level of
service so you realize me okay but how
do you calculate this I really recommend
you after this video to really double
check my inventory turnover ratio
because one of the best way and we're
gonna do that in Excel just after is to
calculate the stock turn of your
products to really identify okay my
first product is only 61 days and the
other one is 183 days I have a full
step-by-step tutorial in this video it's
not about the value it's about how many
days of inventory do you have like to
cover the demand of your customers okay
so if you want to also know more about
how to order the optimal stock I was
talking about okay but how do I Define
four quantities I recommend you to also
check my videos eoq and Wilson formula
and also what is the best Safety stock
calculation for your business okay so
now let's go into Excel you can download
below the video you have a link and then
you can practice with me to implement
this kpi immediately for your company
alright so back to excel as always you
can download this Excel and we do have
10 products in this phase so first of
all I'm going to explain to you how to
focus on Obsolete and then the slow
moving products so you need to Define to
know what is obsolete you need to have a
status like you can see active on
Obsolete you can do it manually or you
could also use what we call a date to
really like do it automatically in most
of the company I'm going to show you
this right now we do have a start index
and an ending date for every product it
could be like a collection it could be
like coming from your product management
team from your sales team with like
maybe responsible to Define what will be
the the best moment to sell your
products it could be also an expiry date
for every single products the only
difference would be you will have to
duplicate this rows to have like one row
per batch of products you could have
like one production from yesterday
another one from today etc etc but at
the end my formula is quite simple and
you can download and check the formula
it would be much easier for you but you
need to Define what is your date of
today so you could also use the formula
today like this today there we go and
then you can just Define if if my date
of today is before my starting date then
the product is new if my date of today
is after the ending date then my project
is Obsolete and if it's in between my
product is active Okay so you have this
automatic formula and at the end you
just copy and paste this formula like
this
attack and we do have the study for
every single products I do also have a
conditional formatting that you can do
like this I like to have I like this
kind of alert to make sure that I can
see quickly what is the toxic stock for
my business and that's the way to have
my absolute stock then we do have the
stock value you can use the what we call
the Cox value you can check my inventory
to another video if you want to know
more but this stock valuation and to
calculate the obsolete value it's quite
simple you just have to do the sum of
all this up series stuff we have 4
450 dollars divided by the total you can
do it manually you can use a semi
formula or you can use a pivot table we
can do a pivot table like this it's
quite simple let's do like this
inset
pivot table
T existing worksheets
there we go and we go for status
we go for stock
and what you can do you can do it
manually or like this I like this way
you click here value settings
when you go for percentage of grand
total
here we go we do have
the percentage 10.7 percent of obsolete
inventory and this is the this kind of
Capia you want to track every single
week amounts to make sure that it's
going as low as possible because this
kind of project can be all completely
destroyed because it has no value for
example if you work for the food or
pharmaceutical industry or that this
kind of free that you need to do
promotion to make sure like if you can
still sell it to make sure you are not
losing too much money in the next few
months alright so that was for the
obsolete kpi that's a very simple one
but very important and I can see many
companies not tracking properly and
regularly this kPa and the second one is
what we call the slow moving for the
slow moving we need to calculate what we
call the inventory turnover or the stock
turn I have a complete tutorial for that
I gave you the link before and in this
one we're gonna calculate okay what is
my inventory turnover for every single
product we just have to divide
if you check my formula the stock
divided by the cells with the same
valuation so we use the correct value
evaluation multiplied by the period in
this specific case I'm using the last 90
days I recommend if you don't have much
volatility and seasonality to use maybe
the last two months or three months and
not the last 12 months unless you drive
very very low volumes and in this case
we do have the stock turn in days you
can also use the forecast if you have
the focus you can use the coverage but
I'm going to share that in another
tutorial all right so now we have the
stock turn it's much easier you can also
add another conditional formatting it's
much easier to identify which one I have
lag too much inventory not necessary
obsolete's inventory this one is
absolute but not this one and now you
have to Define your rule to say okay
which one I need to focus first and the
one you have to focus first are the
biggest value obviously and also the
biggest value in terms of inventory
level so I would definitely focus on
this one and try to improve this one and
if you want to categorize your products
the question is okay Dua but do I need
to focus above 50 60 70 100 just keep it
very simple if you average like this is
the total average please check my
formula to make sure this is correct but
if you have like an average for the
global level at 35 I would say like
starting from maybe you can do twice the
average at the beginning so let's go to
70. you won't change anything for this
specific case my products are defined as
excess because I have way too much
versus the average so keep it very
simple you can obviously play with this
number I could say 40 but it would be
probably too much so I really want to
focus on my biggest value first and then
I just have a formula if my Stockton is
above my parameters then you can change
then it's defined as access so in this
specific example we do have three
products with too much inventory like
slow moving inventory and we have to
focus on these three to make sure that
we can improve that so that's a very
simple way then you can do a percentage
like which one like you could do the
same or the percentage of the the 210
inventory like I did for the obsolete I
like to keep it very simple so in for my
personal case I will focus only on the
obsolete inventory and then I will focus
on the on the global level of inventory
turnover but every every week I will ask
to my demand planning team to really
focus on the top value of inventory and
with like more than x of X days of
inventory turnover I'm going a bit fast
but this is just to give you a few
insights and I'm going to tell you after
how to go deeper in this subject this is
like a very simple solution what you can
do as well you can also use because one
of the limitations could be like okay
but these products as for example maybe
six months of lead time with the
supplier and this one only one day so if
you want to go deeper you could also
compare this with the safety and where
the point recommended level it's a more
complex methodology but if you want to
go deeper you can check my other
tutorials and the workshop if you want
to go deeper on this subject but this is
a very good way to start start with the
absolute value 10.7 percent track this
value every single week and really push
all the department to do everything
possible before it's too late then Focus
piece on your top like her highest stock
turn and inventory to another value just
keep it simple with your with your
parameters to really focus on the
biggest value first and then if you want
to go deeper you can also check safety
stock and where the point recommended
level and when you feel ready you can
implement this kpi not only with 10
products but with 10 or 20 000 products
like this asymmetric dashboard to see
like clearly what is your absolute stock
for the global level per country or
region I always recommend also to have
like an absolute level per category of
products to make sure you focus really
on the most important first like for
example in this specific case brand one
all right so how to reduce your slop and
improve your service this is the one of
the biggest challenges of my life and of
course if you don't improve that you can
go to demography many business go to
demography because they buy too much and
then they have too much Obsolete and
slow moving stock and at 1.11 of cash
flow to secure the business if you
really want to go deeper whatever you're
working from roller material
manufacturing to retail I really
recommend you first to check my YouTube
videos like for example this one or to
optimize your inventory and then if you
want to go to the next level I have a
free Workshop to track first of all the
performance of the whole supply chain if
you want to do like this cool dashboard
with me and I also have another one to
reduce the shortage and the Overstock of
your company with certain parameters so
I have two free workshops you have all
the link below the videos if you want to
go deeper with me and become an expert I
hope you enjoyed this video let me know
in the comments if you have any
questions regarding these kpis and I
will be super happy to help you give me
a like subscribe and I see you very soon
for another tutorial
[Music]
foreign
[Music]