Your manager calls you with a concern regarding the European inventory. There are two KPI's which are regularly reviewed: 1.) Total inventory in KG and USD by plant and 2.) desDays Of Sale. Both KPI's show increased values versus prior year end and versus the same period last year. You know one of the reasons for the increase in inventory is the manufacturing insourcing project, but there are many other factors that influence the monthly inventory & DOS values like forecast accuracy, manufacturing lot sizes, safety stock, and so on.

The question he asks you is:

"Can you can come up with an action plan to reduce the inventory towards year end? But please ensure that we do not jeopardize our SIFOT service commitment of 97%"

A colleague has brought up the idea to split the inventory in the following buckets:

• Blocked stock,
• Safety stock,
• Batch size related stock,
• excess and obsolete and
• Other inventory.

You like his idea and think it would really help you both in your daily stock management and to define a list of possible actions to achieve a healthy inventory reduction by year end. You believe you can quickly build some sort of report based on existing data.

Question 1

Please describe in sufficient detail which tools and data you would use to prepare this analysis and how you would construct it. The analysis should give you an aggregated overview of the inventory by plant / region. The analysis needs to contain a graphical representation of the inventory split and the tool you build / analysis you do should be reusable.

After accepting the 'split inventory proposal' from a colleague I realized some steps (possible actions) in an effort to reduce the overall total inventory in Europe are as follows:

1. Obsolete inventory portion will need to be taken out. It needs to be written off/disposed since material doesn't have an outlet (cannot be recertified any longer due to reaching the end of its life cycle completely).

2. Since blocked stock refers to material that shows on hand, but cannot be accounted for (e.g. residual percentage/qty in tanks/tankers/silos; 3 to 5% of any given bulk material stored in tanks should be subtracted for inventory accuracy, resulting in a fixed reduction percentage in the overall inventory total.

3. Recalculation of safety stocks every quarter, so that it can be up to date fully aligned with the current demand based and DOS variation.

4. J.I.T.  Implementation not only on semi-finished goods (that cannot be kept in stock), but on medium to low rotation materials (products type B and C). This will definitely produce immediate impact on total inventory reduction, minimizing the supply risk for high rotation materials (products type A) due to stocking priority being given to these.

5. Aged Material Purchasing Rule: implementation of a tool that flags any aged material against any purchase order placed in order to avoid material duplication/excess and overall total inventory increase.

Question 2

a. If the DOS = 30 days for both plants, what would be your observation and action plan?

b. If the DOS = 80 days for both plants, would your observation and action plan be different?

Attachment:- Assignment.rar