Formula to determine size curve based off current inventory and Purchase quantities.

Hi Guru's

I would like to create a formula that will look at current Stock On Hand (SOH), sales associated to SOH and then calculate the correct Purchase quantities to get SOH - sales into the correct inventory/size curve.

Example workbook attached.

However, I am finding trouble when trying to calculate the desired inventory position when there is already current SOH. For example there is no need to purchase size S as there is already 50 units in stock which is above the desired size curve %.
View attachment Excel Guru Example.xlsx

It's not easy to help without knowing what your expected results are. Please add these to your workbook manually (just below the cells wgere you want them to appear) and post again.

Hi AliGW,

Thanks for your reply and apologies for the delay in getting back to you.

Essentially the result needs to be as close to the desired size curve outlined in row 17 of the attached. However, as the current SOH size curve is skewed (Row 4 of the attached) the formula needs to be smart enough to recognize where not to order a certain size as their is already sufficient inventory/SOH of that size. For example in the attached there is already 40 units of XS however the allocated inventory % for that size is 0%, therefore no size XS should be ordered.

For the attached example the formula will not be able to get the exact result (desired size curve in row 17) however it should help correct the size curve as best as possible. Hope this helps clarify things

Thanks