I'm a supply chain planner from China. I need calculate the inventory coverage each month to monitor the inventory trend. Without a good formulation in excel, I have to do this job manually which caused me so much time and I even can't ensure the result is correct since I calculated one by one manually.
I don't know how to attach a file, just can explain my request as below
column A is the product name
column B is ending inventory by last month
column C is where I need put the formulation, it will tell me the inventory coverage ( how long the current inv can be convered --- by month )
column D -- I is the sales forecast
I calculate the coverage based on the sales forecast. For example, C product inventory is 46835kgs, by adding column D-G, it's 32383kgs, but if I add column H, it will be more than the current inv., so, I use 4+((B-D-E-F-G)/H), 4 means the count from D-G, the balance after cover D-G is 46835-32383=14452kgs, it's 71% of H column, so the result in C is 4.71 month. This is the way I'm now using, very stupid but really useful for me to monitor the inventory level !
Could you pls help to design a formulation to solve this problem? I've seek many help on Chinese website but no valid solution.
I'm really looking forward your kind help, of course, if I need pay money on the consultation, pls let me know beforehand....
Appreicated in advance!
I don't know how to attach a file, just can explain my request as below
column A is the product name
column B is ending inventory by last month
column C is where I need put the formulation, it will tell me the inventory coverage ( how long the current inv can be convered --- by month )
column D -- I is the sales forecast
I calculate the coverage based on the sales forecast. For example, C product inventory is 46835kgs, by adding column D-G, it's 32383kgs, but if I add column H, it will be more than the current inv., so, I use 4+((B-D-E-F-G)/H), 4 means the count from D-G, the balance after cover D-G is 46835-32383=14452kgs, it's 71% of H column, so the result in C is 4.71 month. This is the way I'm now using, very stupid but really useful for me to monitor the inventory level !
Could you pls help to design a formulation to solve this problem? I've seek many help on Chinese website but no valid solution.
I'm really looking forward your kind help, of course, if I need pay money on the consultation, pls let me know beforehand....
Appreicated in advance!