Allocate a number (quantity) evenly, based on variable criteria (demand/quantity on hand)

[bholmes9]

Hi everyone!

I am looking to allocate a quantity evenly between several locations. Each location has a different average monthly sales and on hand quantity. I want to evenly distribute a quantity based on the location's demand and on hand quantity so that the final "Days Supply of Inventory" (DSI) is as evenly distributed as possible while utilizing the "quantity to allocate" of 130. I am convinced that there is a formula that can accomplish this however my searches have come up empty thus far!

Location	Average Monthly Sales (cases)	Inventory on Hand (cases)	DSI = (30/AMS)*IOH (days)	QUANTITY TO ALLOCATE (cases)	Final DSI (days)
1	20	85	127.5	?	?
2	31	45	43.55	?	?
3	56	45	24.11	?	?
4	100	68	20.40	?	?
5	30	33	33	?	?
6	42	37	26.43	?	?
7	15	10	20	?	?
8	27	22	24.44	?	?
				Quantity to Allocate: 130

<tbody>
</tbody>

Thank you for your time and any help you can offer!

Cheers,

Ben

 

[Eric W]

Welcome to the forum.

I'm not sure there is an easy formula. However, Excel has a tool that might work for you called the Solver. Set up your sheet as below. It's essentially the same as your sample, but I put in the F3 formula and dragged down, I added the H3 formula to sum the values in column E, and I added the I3 formula. Standard Deviation is a measure of how spread out a group of numbers is. The smaller the standard deviation, the closer they are to each other, which is what you want to see.

	A	B	C	D	E	F	G	H	I
1	Location	Average Monthly Sales (cases)	Inventory on Hand (cases)	DSI = (30/AMS)*IOH	QUANTITY TO ALLOCATE	Final DSI
2				(days)	(cases)	(days)	Available	Sum	Standard Deviation of Final DSI
3	1	20	85	127.5	0	127.5	130	130	28.65898
4	2	31	45	43.548387	2	45.483871
5	3	56	45	24.107143	29	39.642857
6	4	100	68	20.4	28	28.8
7	5	30	33	33	13	46
8	6	42	37	26.428571	23	42.857143
9	7	15	10	20	15	50
10	8	27	22	24.444444	20	46.666667
11

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula D3 =30/B3*C3 F3 =30/B3*(C3+E3) H3 =SUM(E3:E10) I3 =STDEV.P(F3:F10) <tbody> </tbody>

<tbody>
</tbody>

Start with all the values in E3:E10 empty or set to 0. Put 130 in G3.

Go to the Data tab and click on Solver. If you don't have Solver showing, go to File > Options > Add-ins > click Go... on the bottom of the screen with Excel Add-ins in the drop-down > then check the Solver Add-in box and click OK.

In the Solver dialog box, set the options as follows:
Set Objective: $I$3
To: Min
By Changing Variable Cells: $E$3:$E$10
Subject to the Constraints:
$E$3:$E$10 = integer
$E$3:$E$10 >=0
$H$3=$G$3
Select a Solving Method: GRG Nonlinear

Then click Solve. It will think for a few seconds, and you should get the values shown in my screen shot. In this particular example, you're not going to get a great answer, since location 1 already has a DSI much greater than the rest and no matter how you allocate the inventory, the rest can't catch up.

Let me know if this helps!

 

[bholmes9]

Eric,

Thanks for the quick response! I was getting on the right track and had already installed the solver add in when I got your post. However the standard deviation piece I surely would have missed. This solves my problem! Thank you, and have a great New Year!

Cheers,

Ben

 

[bholmes9]

Eric,

Is there anyway you could account for the fact that the first location already has enough so that the quantity to be allocated is split more evenly among the locations that need it? The following (manually entered) quantities would achieve this but I am looking to automate that manual process if possible. Let me know your thoughts. Thanks!

QUANTITY	DSI
0	127.50
0	43.55
26	38.04
60	38.40
5	38.00
17	38.57
9	38.00
13	38.89

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

There are several ways to check "closeness" but I think standard deviation is probably the simplest in this case.

Glad you got it working! Happy New Year!

Edit: if you want to exclude location 1, just change the cell references in the H3 and I3 formulas to exclude row 3, and change the constraints in the Solver as well. When I did that, I got numbers very similar to the ones you manually obtained.

Also, as a curiosity, for the original layout, I took 60 cases away from location 1 (changed C3 to 25), and added them to the available count (changed G3 to 190), then ran the Solver. The final DSI for all locations was between 44 and 45, which is pretty close!