PDA

View Full Version : VBA code for distributing available stock



Rampage123
01-19-2014, 04:22 AM
Hi all,

I need help on code for performing the following function

1. I have multiple warehouses and requirement from each warehouse in terms of tonnage (by different stocking units / SKU)
2. I have the supplying source plant and stock available (in tonnage) at the source plant
3. I have ranked the warehouses basis the stock penetration (i.e how far the current stock has fallen below required norm)
4. Basis the ranking, I need to allocate the stock available at plant to the warehouses with higher stock penetration % being the first warehouse to be served.
5. The allocation has to continue till all warehouses are served or till all stock at the plant is exhausted (the code has to stop once either of these happens)

I am attaching the input sheet and the output sheet (the way I envisage the output).

Thanks in advance for the help.

GTO
01-19-2014, 05:42 AM
Greetings Rampage and welcome to VBAX :hi:,

I am confident that you will be happy you joined, as there are good folks here who will go out of their way to assist in furthering our knowledge and provide good help in fixing or bettering the code one has written in part, but hit a "wall" in.

As to your current issue, what code or worksheet functions have you tried thus far?

Mark

Rampage123
01-19-2014, 06:47 AM
Greetings Rampage and welcome to VBAX :hi:,

I am confident that you will be happy you joined, as there are good folks here who will go out of their way to assist in furthering our knowledge and provide good help in fixing or bettering the code one has written in part, but hit a "wall" in.

As to your current issue, what code or worksheet functions have you tried thus far?

Mark

Thanks GTO.

I have thus far written the code for the initial ranking part. However, I am unclear about the logic i should use to solve the problem I mentioned in my opening post.

I am looking for someone to help me with the logic, if not the code.

Thanks.

Paul_Hossler
01-19-2014, 08:31 AM
Couple of suggestions regarding structuring, but the VBA aspects are probably not very complicated.

Most of the time, I've found that it's the logic or the architecture aspects that take the most time, and once they're all sorted out correctly and effeciently, the programming part is much easier (just IMHO)

1. I re-arranged Output sheet columns

2. Sorted by SKU (A-Z) then Penetration (H to L) and then Warehouse (A-Z). It made it easier for me to see what I thought you were looking for (I could guess wrong)

3. I don't think you need the ranking col

4. I added a 'helper' column but if you did a complete VBA solution you could keep track internally and stop when the available stock is depleted


Paul

SamT
01-19-2014, 01:15 PM
After looking at your requirements and sample from both a programmer's and an Inventory Management perspective, this is how I would lay out the data.

The workflow envisioned is


Update the Qty On Hand per SKU per Warehouse and Plant

Can be done manually by sheet;
Automatically from digital reports;
Or manually by UserForm


Run Macro to create order form(s) per plant per warehouse.


Note that only sheets named like "ABC Data" need updating during normal course of business.

This structure allows for changes in business without changing any code:

The Use of the SKU Source Data sheet envisions the time when you have more than one source (Plant)
The Use of different sheets for each warehouse provides for changes in the number of warehouses.
The use of identical SKU lists on all Data Sheets

A Quantity of 0 indicates that SKU is not used in that facility.



Hashed out cells are suggestions for future use. They will require little or no changes in code.

The use of Min Qty Required and Max Qty Allowed, along with Minimum Order Desired allows for Proportional deliveries.
Example (per SKU):

If the Total available from all Plants is 30% less that the Total desired from all Warehouses, each warehouse can be shipped 70% of it's desired qty.
If the Total available is greater than the total desired, each warehouse can be shipped a qty larger than their desired Qty up to their Max allowed.