• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pls help design one inventory model

P

Pradeep Pradhan

Member
Hello sirs,

I am uploading a file that contains inventory data that run across thousand rows.

My twin objective is to optimize stk quantity to help production department work smoothly without bothering for shortages and at same time there should not be any excess stk lying idle in inventory.

so i want a forecasting mechanism in excel to optimize inventory. Pls have a look at file and help me design a model.

Thank u.

pradeep
 

Attachments

  • Inventory Model.xlsx
    682.7 KB · Views: 16
Hi Pradeep,

Besides this model have you considered/examined any seasonality in you data. or some thing like how much qty is consumed and how much is being ordered?
 
Hi Faseeh,
Nice to hear from you. Act RM Purchased Qty,Cons Qty and Stk Qty is given from Apr - Dec'14. Based on this, forecasting is to be made for holding stk . As i understand inventory optimisation is linked to sales variation but here variation can caputured by historical rm cons pattern also. It may be assumed that this industry is not subject to any seasonal variation for our analysis.

What i require from these data:--

1) Forecasting how much is optimized inventory qty based on cons pattern.
2) What should be safety stock level based on lead time and cons pattern.
3) Figuring out instantly which materials are above optimization level.
4)Which are stocks that moves faster and who are laggards?
5) Quantifying the deviation in RM consumption pattern.
5) Carrying out ABC analysis and pareto analysis.
Any other things which you want to add.

Regards,

pradeep pradhan
 
Hi Pardeep,

In my opinion there could be no general formula for forecasting all the material, instead it will be handled on the basis of families or individual products. I have tried on the basis of individual products. See My Plan.

P.S: I just did major in Supply Chain Management and am working on my thesis. This could be learning point for me if you can present this to your management get feedback from them. Theoretical Source of information is Introduction to Materials Management by Tony & Arnold, 6th Ed. Page 281-293. Thanks
 

Attachments

  • Inventory Model.xlsx
    799.7 KB · Views: 43
You must log in or register to reply here.
Back
Top