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!

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

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
Available	Sum

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Location[/TD]
[TD="bgcolor: #FAFAFA"]Average Monthly Sales (cases)[/TD]
[TD="bgcolor: #FAFAFA"]Inventory on Hand (cases)[/TD]
[TD="bgcolor: #FAFAFA"]DSI = (30/AMS)*IOH[/TD]
[TD="bgcolor: #FAFAFA"]QUANTITY TO ALLOCATE[/TD]
[TD="bgcolor: #FAFAFA"]Final DSI[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"](days)[/TD]
[TD="bgcolor: #FAFAFA"](cases)[/TD]
[TD="bgcolor: #FAFAFA"](days)[/TD]

[TD="bgcolor: #FAFAFA"]Standard Deviation of Final DSI[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20[/TD]
[TD="bgcolor: #FAFAFA, align: right"]85[/TD]
[TD="bgcolor: #FAFAFA, align: right"]127.5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]127.5[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]28.65898[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]31[/TD]
[TD="bgcolor: #FAFAFA, align: right"]45[/TD]
[TD="bgcolor: #FAFAFA, align: right"]43.548387[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]45.483871[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]56[/TD]
[TD="bgcolor: #FAFAFA, align: right"]45[/TD]
[TD="bgcolor: #FAFAFA, align: right"]24.107143[/TD]
[TD="bgcolor: #FAFAFA, align: right"]29[/TD]
[TD="bgcolor: #FAFAFA, align: right"]39.642857[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]100[/TD]
[TD="bgcolor: #FAFAFA, align: right"]68[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20.4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]28[/TD]
[TD="bgcolor: #FAFAFA, align: right"]28.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]30[/TD]
[TD="bgcolor: #FAFAFA, align: right"]33[/TD]
[TD="bgcolor: #FAFAFA, align: right"]33[/TD]
[TD="bgcolor: #FAFAFA, align: right"]13[/TD]
[TD="bgcolor: #FAFAFA, align: right"]46[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]42[/TD]
[TD="bgcolor: #FAFAFA, align: right"]37[/TD]
[TD="bgcolor: #FAFAFA, align: right"]26.428571[/TD]
[TD="bgcolor: #FAFAFA, align: right"]23[/TD]
[TD="bgcolor: #FAFAFA, align: right"]42.857143[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20[/TD]
[TD="bgcolor: #FAFAFA, align: right"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]27[/TD]
[TD="bgcolor: #FAFAFA, align: right"]22[/TD]
[TD="bgcolor: #FAFAFA, align: right"]24.444444[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20[/TD]
[TD="bgcolor: #FAFAFA, align: right"]46.666667[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=30/B3*C3[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]=30/B3*(C3+E3)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"]=SUM(E3:E10)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I3[/TH]
[TD="align: left"]=STDEV.P(F3:F10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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!

[TABLE="width: 122"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]QUANTITY[/TD]
[TD] DSI[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]127.50[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]43.55[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD="align: right"]38.04[/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[TD="align: right"]38.40[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]38.00[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]38.57[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]38.00[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]
38.89

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!