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!