Store Allocation Optimisation Modelling

 

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%)

 

The basic data

 

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.