Formula/VBA Help

Copper Contributor

Dear Saviour,


Requirement 1) Find excess Qty for an item in store with a condition that item WOC (weeks of cover) should be greater than 18, and SOH (stock on hand) is greater than 6.(Excess Qty)
Requirement 2) Find how many stores has stock with less than 18 WOC (weeks of cover).(short in WOC)
Requirement 3) find the qty for an item to maintain minimum 18 WOC (weeks of cover)

WOC: Week of cover
SOH: Stock in hand
WROS: Week rate of sales (QTY sale in a week)


First 3 Requirements already solved in data sheet based on the method  @Riny_van_Eekelen  has helped me with few days ago.

Requirement 4) We need to fulfil the stock requirement on an item for any store where their current stock cannot cover next 18 weeks.
I've alraedy calculated the Excessive stock in stores, Stores where their is not enough store to cover 18 weeks,
and qty required to fulfil their Week of cover based on above mentioned criterias.

We have to prioritize the store based on their ranking/grades(receiving stocks).
2nd Demand can be fulfilled by stores in same sub regions.

For example: on sheet 7, for item# 102793929, some stores has excessive qty and some need more. here we take example of store #11017
who need quantity for next 16.3 Weeks i.e. 38 quantities (based on their WROS). but store 11021 also has the requirement
where they need 22 qty for 11.3 weeks, here we can fulfil the requiement from stores who have excessive qty but first
we will fulfil the requirement of 11017 because they have high ranking 3 vs 4.

Your help or alternate suggestion/solution will be highly appriciated. I hope i was able to convey the requirement.

2 Replies

@sherikhan88 I'm glad I could help you earlier, but that was on a quite small scale. Looked at your schedule and have difficulty to even start understanding what I'm looking at and what you need. I'm not able to put a lot of free time into understanding/analysing your problem. Sorry!

Sir @Riny_van_Eekelen, I appreciate your support very much and thank you for your help. I understand you and will take that as a feedback for good.

Have a really nice evening!