Splitting an allocation between stores based on the current stock level

[JackG333]

Hi,

I am currently trying to write a formula to be able to automate allocating stock between certain shops/stores. I have to allocate the stock based on the current stock holdings in those stores in relation to the sales that they receive. For example if one store sells 10 every week and the product will only last for 10 days then I don't want to send more than 10 at any one time. Realistically i would like to allocate the stock based on the lowest days cover that the stores have. (Days cover - Sales divided by current amount of stock)

Does someone know a way that I could type 200 units in a cell, which will then automatically populate the 200 units between the stores that have the lowest amount of stock cover in relation to sales? So if one store has 2 days cover, and another has 1.5 days cover, i need it to allocate 1 or more units to the store that has 1.5 days cover until it reaches 2.1 days cover, from which it would then start allocating 1 unit to the store that has 2 days cover and so on?

Any help would be much appreciated!

Thanks
Jack

 

[KRice]

Have a look at this and let me know if it makes sense. Your inputs in the blue cells. Formulas determine a target "days cover" just before the next replenishment cycle, and this is used to apportion the "stock to allocate" across the stores. Since you are likely going to be dealing with integers, you'll have to round the results (see yellow cells), and that will lead to some rounding error that might require adding or subtracting one from selected stores. I'm assuming your posted "days cover" values were incorrect...see column D for the calculation of the current day's cover.

ErlangC_20220801.xlsx
	A	B	C	D	E	F	G	H
1			days btwn replenishments-->	7
2			stock to allocate-->	200
3			constant days cover target-->	24.80882
4
5			total allocated-->	200				201
6	Store Number	Last 7 Days Sales	Total Stock on Hand	To Send	Current Days Cover	Projected Stock on Hand just before next replenishment	Proj Days Cover just before next replenishment
7	112	14	32	31.61765	16	49.61764706	24.80882353	32
8	113	22	25	74.97059	7.954545455	77.97058824	24.80882353	75
9	114	9	11	29.89706	8.555555556	31.89705882	24.80882353	30
10	115	12	22	32.52941	12.83333333	42.52941176	24.80882353	33
11	116	11	19	30.98529	12.09090909	38.98529412	24.80882353	31
Sheet1

Cell Formulas
Range	Formula
D3	D3	=7/SUM(B7:B11)*($D$2+SUM(C7:C11)-$D$1/7*SUM(B7:B11))
D5,H5	D5	=SUM(D7:D11)
D7:D11	D7	=$D$3*B7/7+$D$1/7*B7-C7
E7:E11	E7	=C7*7/B7
F7:F11	F7	=C7-B7/7*$D$1+D7
G7:G11	G7	=F7*7/B7
H7:H11	H7	=ROUND(D7:D11,0)
Dynamic array formulas.

 

[KRice]

I little more intuitive way to show the D7 formula:

=B7/7*($D$3+$D$1)-C7

Sum the number of days between replenishment and the maximum number of cover days (such that all stores would be projected to have the same number of cover days) and multiply that sum by the daily sales rate...which then tells us the number of items that could be sold at the current sales rate over that period of time. Then subtract the stock on hand. The trickier part is determining the target value for the "days cover" value in D3.