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

#### bholmes9

##### New Member
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

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Eric W

##### MrExcel MVP
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.

ABCDEFGHI
1LocationAverage Monthly Sales (cases)Inventory on Hand (cases)DSI = (30/AMS)*IOHQUANTITY TO ALLOCATEFinal DSI
2(days)(cases)(days)AvailableSumStandard Deviation of Final DSI
312085127.50127.513013028.65898
42314543.548387245.483871
53564524.1071432939.642857
641006820.42828.8
753033331346
86423726.4285712342.857143
971510201550
108272224.4444442046.666667
11

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
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

##### New Member
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

##### New Member
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

##### MrExcel MVP
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!

Last edited:

Replies
4
Views
493

1,191,005
Messages
5,984,120
Members
439,872
Latest member
noaman79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back