Hi all,
I'm looking for help with a stock forecasting model via formulae or macro
The model currently requires manual allocation of either 'B' or 'ZA' stock each day based on net demand (called 'Net Number' for each type in the spreadsheet), prioritising larger Net Number then 'B' type if Net Numbers are equal. After allocation, the Stock Level and Net Number will reduce based on Usage and then the same occurs for the following day. Stock levels will also increase when Deliveries land (columns G and H)
'Usage' depends on a few rules:
- Maximum daily usage - 'B' allocation takes two days to complete so daily usage can be either 0.5 'B' type or '1 'ZA' type per day. If allocating B, next allocation must be 0.5 'B' type (i.e. to finish the previous day's allocation)
- Stock Level - only use when stock level >=1 (unless 2nd day of 'B' type, where stock can be >=0.5) and cannot be less than 0
- Usage prioritises larger Net Number value, then 'B' type
- Avoiding weekends and public holidays (rows highlighted in red)
I've also attached a screenshot of a couple of working examples
Hopefully that explains what I'm after, happy to provide more info if anything's unclear.
Appreciate all and any help, so thanks in advance!
Bookmarks