Rob Here is the toy model in excel attached herewith. As you recommended, I have take 1 week, 2 days and 3 vendors. Very small scale model. I have written my model in excel. The input are ISN (SHA_ATL_V1_D1_W1, SHA_ATL_V2_D1_W1 and SHA_ATL_V8_D2_W1) and the weight and volume are the same from what you have. The box specs are the same from what you have. The rates are same from what you have except 1 small difference. I use a $4 per VolUOM instead of $20 so that it allows me to test both CFS and CY. If you can just updated this one number in your Rate Sheet, that is all the change you need. Apart from this change, everything else is same. You will see all these input in the one sheet attached (called "toy"). From cells A1 to U6, I evaluate the costs if each of these ISNs go individually. The total cost of doing this CYS way is $4,350. Now, cells A9 to U11 assumes that everything gets consolidated at the end of the week and goes CFS way. The cost is $4,292. The next thing that I do is to compare the CFS costs and CYS costs for each ISNs from cells cells A13 to Y16. As we can see for this ISN "SHA_ATL_V1_D1_W1" CYS costs is $2,450 and CFS costs is expensive ($3,225). For arriving at CFS costs, I take the volume share of this vendor (130/173)= 0.75 and then times it with the total CFS costs of $4,292. If I do this for all three ISNs , we can see that for the other 2 ISNs, CFSWay proves to be cheaper. With this, what I do, is just cull out "SHA_ATL_V2_D1_W1" and "SHA_ATL_V8_D2_W1" for a consolidated volume of 43 (Cells A19 to X23). For this i find out the number of boxes (manually said 1X40F is fine). and then I have costed it out. So the first ISN that is SHA_ATL_V1_D1_W1, goes via CYS way (that is this vendor ships all by himself in a 2X40H) for a total cost of $2,450 (2x$1,200) + ($50). The other 2 ISNs go via CFS way at the end of the week for cost of $1,145+$27= $1,172 as given in the excel. Columns A19 to X23 gives the answer . We know that if it is a CFS move, then the weight and volume numbers are all aggregated weekly but the #of Boxes and Costs are all prorated(by Volume because CFS gives importance to Vol based costs) accordingly and played back in the output at the ISN level.. I have my SAS Code as well here with . I am able to run the model. Model runs but the answer is not right. . Do we need to run this twice, first to figure out which ISNs are cheaper via CYS and for those ISNs which are deemed "cheaper" to go via CFS ,should the optimum asset determination run again. Am just thinking loud. I am at a cross roads now. Appreciate your help as always. Thank you. proc optmodel;
set <str> ORG;
read data CASUSER.Unique_ORG into ORG = [ORG];
set <str> DES;
read data CASUSER.Unique_DES into DES = [DES];
set <str> BOX;
read data CASUSER.Unique_BOX into BOX = [BOX];
set <str> MOL;
read data CASUSER.Unique_MOL into MOL = [MOL];
set <str> T1;
read data CASUSER.Unique_T1 into T1 = [T1];
set <str> T2;
read data CASUSER.Unique_T2 into T2 = [T2];
/*Read ISN Wt and Volume for a given ISN* - START */
set <str> ISN;
str Org_ISN {ISN};
str Des_ISN {ISN};
str T1_ISN {ISN};
str T2_ISN {ISN};
num Volume_ISN {ISN} init 0;
num Weight_ISN {ISN} init 0;
read data CASUSER.InputData_AssetMix into ISN=[ISN] Org_ISN=ORG Des_ISN=DES Volume_ISN=Volume Weight_ISN=Weight T1_ISN=T1 T2_ISN=T2;
num T2_Volume{T2} init 0;
read data CASUSER.Unique_T2 into [T2] T2_Volume=T2_Volume;
num T2_Weight{T2} init 0;
read data CASUSER.Unique_T2 into [T2] T2_Weight=T2_Weight;
NUM BigM;
READ DATA CASUSER.BigM INTO BigM=Volume;
/*Read ISN Wt and Volume for a given ISN* - END*/
/*Read Wt and Volume Capacity for a given ORG, DES, BOX - START */
num Volume_Capacity {ORG,DES,BOX,MOL,T1,T2} init 0; num Volume_Min {ORG,DES,BOX,MOL,T1,T2} init 0;
num Weight_Capacity {ORG,DES,BOX,MOL,T1,T2} init 0; num Weight_Min {ORG,DES,BOX,MOL,T1,T2} init 0;
read data CASUSER.BOXSPECS into [ORG DES BOX MOL T1 T2]
Volume_Capacity=Volume_Capacity
Weight_Capacity=Weight_Capacity
Volume_Min=Volume_Min
Weight_Min=Weight_Min;
/*Read Wt and Volume Capacity for a given ORG, DES, BOX - END */
/* A Binary variable that is 0 or 1 for a given asset for a given lane, 0 indicated not available, 1- available */
num Is_BoxAvalable_for_a_lane {ORG,DES,BOX,MOL,T1,T2};
read data CASUSER.BOXSpecs into [ORG Des Box Mol T1 T2] Is_BoxAvalable_for_a_lane = BoxAvailability;
/* Decision Variable and Associated Impvars - START */
var BoxesNeeded_CYS {ISN,BOX,MOL,T1,T2} >= 0 integer;
var Proportion_CYS {ISN,BOX,MOL,T1,T2} >= 0 <= 1;
var BoxesNeeded_CFS {BOX,MOL,T2} >= 0 integer;
var Proportion_CFS {BOX,MOL,T2} >= 0 <= 1;
impvar VolumeInsideBox_CYS {i in ISN, b in BOX,m in {'CYS'},d in T1,w in T2} = Volume_ISN[i] * Proportion_CYS[i,b,m,d,w];
impvar WeightInsideBox_CYS {i in ISN, b in BOX,m in {'CYS'},d in T1,w in T2} = Weight_ISN[i] * Proportion_CYS[i,b,m,d,w];
impvar VolumeInsideBox_CFS {b in BOX,m in {'CFS'},w in T2} = sum {i in ISN} Volume_ISN[i] * Proportion_CFS[b,m,w];
impvar WeightInsideBox_CFS {b in BOX,m in {'CFS'},w in T2} = sum {i in ISN} Weight_ISN[i] * Proportion_CFS[b,m,w];
var Is_ISN_MOL {ISN,MOL} binary;
var Is_MOL {MOL} binary;
/* Decision Variable and Associated Impvars - END */
/*Define Rates and Implicit Variables - START */
set <str,str,str,str,str,str> PerBox_Based_Rate_NoZeroes;
num PerBox_Based_Rate {PerBox_Based_Rate_NoZeroes};
read data CASUSER.BOXRATE
(where=(RateBasis="PerBox" and Ratetype='Linehaul' and Rate>0))
into PerBox_Based_Rate_NoZeroes=[ORG DES BOX MOL T1 T2] PerBox_Based_Rate=Rate;
impvar PerBox_Based_Costs_CYS = sum {i in ISN, b in BOX,m in MOL,d in T1,w in T2: <Org_ISN[i],Des_ISN[i],b,m,d,w> in PerBox_Based_Rate_NoZeroes}
PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,d,w] * BoxesNeeded_CYS[i,b,m,d,w];
impvar PerBox_Based_Costs_CFS = sum {i in ISN, b in BOX,m in MOL,d in T1,w in T2: <Org_ISN[i],Des_ISN[i],b,m,d,w> in PerBox_Based_Rate_NoZeroes}
PerBox_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,d,w] * BoxesNeeded_CFS[b,m,w];
set <str,str,str,str,str,str> PerVol_Based_Rate_NoZeroes;
num PerVol_Based_Rate {PerVol_Based_Rate_NoZeroes};
read data CASUSER.BOXRATE
(where=(RateBasis="PerVolUOM" and Ratetype='Carrier_BOL' and Rate>0))
into PerVol_Based_Rate_NoZeroes=[ORG DES MOL BOX T1 T2] PerVol_Based_Rate=Rate;
impvar PerVol_Based_Costs_CYS = sum {i in ISN, b in BOX, m in MOL,d in T1,w in T2: <Org_ISN[i],Des_ISN[i],b,m,d,w> in PerVol_Based_Rate_NoZeroes}
PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,d,w] * VolumeInsideBox_CYS[i,b,m,d,w];
impvar PerVol_Based_Costs_CFS = sum {i in ISN, b in BOX, m in MOL,d in T1,w in T2: <Org_ISN[i],Des_ISN[i],b,m,d,w> in PerVol_Based_Rate_NoZeroes}
PerVol_Based_Rate[Org_ISN[i],Des_ISN[i],b,m,d,w] * VolumeInsideBox_CFS[b,m,w];
set <str,str,str,str,str,str> PerShp_Based_Rate_NoZeroes_Entry;
num PerShp_Based_Rate_Entry {PerShp_Based_Rate_NoZeroes_Entry};
read data CASUSER.BOXRATE
(where=(RateBasis="PerShipment" and Ratetype='Export_Declaration' and Rate>0))
into PerShp_Based_Rate_NoZeroes_Entry=[ORG DES MOL BOX T1 T2] PerShp_Based_Rate_Entry=Rate;
impvar PerShp_Based_Costs_Entry= sum {i in ISN, b in BOX, m in MOL,d in T1,w in T2: <Org_ISN[i],Des_ISN[i],b,m,d,w> in PerShp_Based_Rate_NoZeroes_Entry}
PerShp_Based_Rate_Entry[Org_ISN[i],Des_ISN[i],b,m,d,w];
/*Define Implicit Variables - END */
Min TotalCost =
PerBox_Based_Costs_CYS + PerVol_Based_Costs_CYS +
PerBox_Based_Costs_CFS + PerVol_Based_Costs_CFS +
PerShp_Based_Costs_Entry;
/*General Constraints */
con OneMOL{i in ISN}:
sum{M in MOL} Is_ISN_MOL[i,m] = 1; /* This is to make sure one ISN can go either CYS or CFS */
con InEqualOut_Volume:
sum {i in ISN, b in BOX, m in MOL, d in T1, w in T2} VolumeInsideBox_CYS[i,b,'CYS',T1_ISN[i],T2_ISN[i]] +
sum {i in ISN, b in BOX, m in MOL, w in T2} VolumeInsideBox_CFS[b,'CFS',T2_ISN[i]] =
sum {i in ISN, m in MOL}Volume_ISN[i]*Is_ISN_MOL[i,m];
con InEqualOut_Weight:
sum {i in ISN, b in BOX, m in MOL, d in T1, w in T2} WeightInsideBox_CYS[i,b,'CYS',T1_ISN[i],T2_ISN[i]] +
sum {i in ISN, b in BOX, m in MOL, w in T2} WeightInsideBox_CFS[b,'CFS',T2_ISN[i]] =
sum {i in ISN, m in MOL}Weight_ISN[i]*Is_ISN_MOL[i,m];
/*End of General Constraints */
/*CYS Specific Constraints */
for {i in ISN, b in BOX,m in MOL,d in T1,w in T2: Is_BoxAvalable_for_a_lane[Org_ISN[i],Des_ISN[i],b,m,d,w] = 0}
fix BoxesNeeded_CYS[i,b,m,d,w] = 0;
con SumProportion_ToOne_CYS {i in ISN,m in {'CYS'}}:
sum {b in BOX} Proportion_CYS[i,b,'CYS',T1_ISN[i],T2_ISN[i]] <= 1;
con Volume_Constraint_CYS {i in ISN, b in BOX, m in {'CYS'}}:
Volume_Capacity[Org_ISN[i],Des_ISN[i],b,m,T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CYS[i,b,m,T1_ISN[i],T2_ISN[i]] >= VolumeInsideBox_CYS[i,b,m,T1_ISN[i],T2_ISN[i]];
con Volume_Constraint_MinThresh_CYS {i in ISN, b in BOX, m in {'CYS'}}:
Volume_Min[Org_ISN[i],Des_ISN[i],b,m,T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CYS[i,b,m,T1_ISN[i],T2_ISN[i]] <= VolumeInsideBox_CYS[i,b,m,T1_ISN[i],T2_ISN[i]];
con Weight_Constraint_CYS {i in ISN, b in BOX, m in {'CYS'}}:
Weight_Capacity[Org_ISN[i],Des_ISN[i],b,m,T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CYS[i,b,m,T1_ISN[i],T2_ISN[i]] >= WeightInsideBox_CYS[i,b,m,T1_ISN[i],T2_ISN[i]];
con Weight_Constraint_MinThresh_CYS {i in ISN, b in BOX, m in {'CYS'}}:
Weight_Min[Org_ISN[i],Des_ISN[i],b,m,T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CYS[i,b,m,T1_ISN[i],T2_ISN[i]] <= WeightInsideBox_CYS[i,b,m,T1_ISN[i],T2_ISN[i]];
con CYSSpecific_Volume {i in ISN,m in {'CYS'}}:
sum{b in BOX} VolumeInsideBox_CYS[i,b,m,T1_ISN[i],T2_ISN[i]]=Volume_ISN[i]*Is_ISN_MOL[i,'CYS'];
con CYSSpecific_Weight {i in ISN,m in {'CYS'}}:
sum{b in BOX} WeightInsideBox_CYS[i,b,m,T1_ISN[i],T2_ISN[i]]=Weight_ISN[i]*Is_ISN_MOL[i,'CYS'];
/* End of CYS Specific Constraints*/
/* CFS Specific Constraints*/
for {i in ISN, b in BOX,m in MOL,d in T1,w in T2: Is_BoxAvalable_for_a_lane[Org_ISN[i],Des_ISN[i],b,m,d,w] = 0}
fix BoxesNeeded_CFS[b,m,w] = 0;
con SumProportion_ToOne_CFS {w in T2}:
sum {b in BOX, m in {'CFS'}} Proportion_CFS[b,'CFS',w] <= 1;
con Volume_Constraint_CFS {b in BOX, m in {'CFS'},w in T2}:
sum{i in ISN}Volume_Capacity[Org_ISN[i],Des_ISN[i],b,'CFS',T1_ISN[i],T2_ISN[i]] *
BoxesNeeded_CFS[b,'CFS',w] >= VolumeInsideBox_CFS[b,'CFS',w];
con Weight_Constraint_CFS {b in BOX, m in {'CFS'},w in T2}:
sum {i in ISN} Weight_Capacity[Org_ISN[i],Des_ISN[i],b,'CFS',T1_ISN[i],T2_ISN[i]] *
BoxesNeeded_CFS[b,'CFS',w] >= WeightInsideBox_CFS[b,'CFS',w];
con Volume_Constraint_MinThresh_CFS {b in BOX, m in {'CFS'},w in T2}:
sum{i in ISN }Volume_Min[Org_ISN[i],Des_ISN[i],b,'CFS',T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CFS[b,'CFS',w] <= VolumeInsideBox_CFS[b,'CFS',w];
con Weight_Constraint_MinThresh_CFS {b in BOX, m in {'CFS'},w in T2}:
sum{i in ISN }Weight_Min[Org_ISN[i],Des_ISN[i],b,'CFS',T1_ISN[i],T2_ISN[i]] * BoxesNeeded_CFS[b,'CFS',w] <= WeightInsideBox_CFS[b,'CFS',w];
con CFSSpecific_Volume {w in T2, m in {'CFS'}}:
sum{b in BOX} VolumeInsideBox_CFS[b,'CFS',w]<=T2_Volume[w]*Is_MOL['CFS'];
con CFSSpecific_Weight {w in T2, m in {'CFS'}}:
sum{b in BOX} WeightInsideBox_CFS[b,'CFS',w]<=T2_Weight[w]*Is_MOL['CFS'];
solve with milp;
print Is_ISN_MOL;
print IS_MOL;
print PerBox_Based_Costs_CYS;
print PerBox_Based_Costs_CFS;
print PerVol_Based_Costs_CYS;
print PerVol_Based_Costs_CFS;
print PerShp_Based_Costs_Entry;
print BoxesNeeded_CFS;
print BoxesNeeded_CYS;
print PerBox_Based_Rate;
... View more