The purpose of this ‘optimisation’ routine is to establish a single
figure that represents the cheapest cost possible in distributing the cases of
goods from the Distribution Centres (DCs) to the Stores.
Some facts and rules
The customer about 772 stores and 9 distribution centres around the UK.
The costs involved in distribution are easily calculated and are fed
into the proposed modelling tool via an import routine.
Stores can only be served by one of the three closest DCs.
The existing distribution method dictates a ‘maximum weekly cases’ that
each DC can handle. So, any optimised solution must ensure that this maximum is
not exceded (although a margin is added to allow some flexibility. See
spreadsheet set at 3%)
This is a sample from the attached spreadsheet. This XLS contains all
the data required (and some that is not!). The important data is in blue, the
rest is just for reference.
|
DC_ID |
DC_Avg_Wk_Cases |
DC_Can_Excede_By |
DC_Allowed_Avg_Wk_Cases |
Store_ID |
Total_CPC |
Store_Avg_Wk_Cases |
Total_Weekly_Cost |
|
115 |
1685106.837 |
3.00% |
1685612 |
2004 |
0.46656 |
3021.730957 |
1691.784 |
|
125 |
1532227.784 |
3.00% |
1532687 |
2004 |
0.4944 |
3021.730957 |
1792.728 |
|
175 |
1431390.086 |
3.00% |
1431820 |
2004 |
0.42576 |
3021.730957 |
1543.836 |
|
115 |
1685106.837 |
3.00% |
1685612 |
2005 |
0.4476 |
1721.017445 |
924.396 |
|
125 |
1532227.784 |
3.00% |
1532687 |
2005 |
0.60864 |
1721.017445 |
1256.976 |
|
195 |
1663566.679 |
3.00% |
1664066 |
2005 |
0.4206 |
1721.017445 |
868.632 |
|
105 |
1640150.414 |
3.00% |
1640642 |
2006 |
0.36804 |
6142.5371 |
2712.84 |
|
115 |
1685106.837 |
3.00% |
1685612 |
2006 |
0.57588 |
6142.5371 |
4244.832 |
|
195 |
1663566.679 |
3.00% |
1664066 |
2006 |
0.68496 |
6142.5371 |
5048.868 |
There are 3 Store records for each DC (3 closest DCs x 772 stores=2316
records). The part that I am having trouble with is the process that selects
every possible combination of 772 stores from the 3 available records for each
store.
The logic required, as I see it at least, is basically as follows,
1.
Generate First/Next combination of 772 store records.
2.
Group by DC and sum the Store_Avg_Wk_Cases figures.
3.
If this sum > DC_Allowed_Avg_Wk_Cases (for that DC) then disregard
this combination and goto1
Else
If this sum <= DC_Allowed_Avg_Wk_Cases (for that DC) then sum Total_Wk_Cost
for all Stores in that DC group and compare to previous ‘best stored total’, if
it’s cheaper keep it and keep the Store/DC combination in a temp table, if it’s
not cheaper then disregard it.
4.
goto 1 until all combinations are done.
The result of this process should be a ‘best figure’ and the list of
DC/Store combinations that resulted in that figure. However, the number of
Store/DC combinations is massive and, on a PC at least, would take forever to
compute.
Is there another way of doing this?
Hopefully this is all the info that you need but should you require any
additional data then let me know ASAP.