! Sales Territories Design (SaleTerrDsgn.lng);
! We want to group customers into territories or regions so that:
1) The sales potential for each region is similar that of others,
2) The workload of each region is similar to that of others,
3) Each region is fairly compact so the sales rep assigned to
a region does not incur a lot of travel time.
The simplest estimate of sales potential of a customer is past sales.
The estimated workload of a customer is the estimated hours per week
that the sales rep should spend with the customer.
One measure of compactness of a region is the sum of the distances
of all customers from the most central customer of the region.
A simple measure of distance is the straightline distance based
on the latitude and longitude of each customer.
Qualitatively, the model is:
Minimize the Maximum distance from centroid over regions,
subject to:
Workload in each region <= target workload,
Sales potential in each region >= target sales potential;
! Keywords: Marketing, Districting, Sales districting,
Cluster analysis, Territory design;
SETS:
CUSTOMER: WORK, SALESV,
WORKA, DISTA, SALESA,
LATI, LNGT;
CXC( CUSTOMER, CUSTOMER): DIST, Z;
CXCSUB( CXC): DCITY, ACITY, ARROHD;
ENDSETS
DATA:
NDS = 3; ! Number of regions to construct;
SALEST = 181; ! Sales minimum target for each region;
WORKT = 25; ! Work maximum target for each region;
SUMDWGT= 0.75; ! "Epsilon" weight assigned to minimizing
total distance from centroids in objective, in
addition to main objective of minimizing
maximum distance in any region;
! Some cities in Texas with given
workload, sales value potential, latitude and longitude;
CUSTOMER, WORK, SALESV, LATI, LNGT =
AMARILLO 7 46 35.2 -101.81
BEAUMONT 3 31 30.08 -94.14
BROWNSVILLE 6 42 25.92 -97.48
BRACKETTVIL 1 13 29.31 -100.41
CLARKSVILLE 3 17 33.61 -95.05
CORPUS_CHR 6 39 27.8 -97.39
EL_PASO 9 64 31.84 -106.43
GALVESTON 5 39 29.3 -94.79
KLONDIKE 4 54 33.32 -95.75
LUBBOCK 5 44 33.57 -101.87
MARFA 3 46 30.31 -104.02
PECOS 5 43 31.4 -103.5
TEXARKANA 5 33 33.44 -94.07
TEXHOMA 1 17 36.5 -101.78
TEXLINE 1 12 36.37 -103.01
WACO 3 21 31.47 -97.24;
ENDDATA
SUBMODEL SALESASGN:
! Variables:
Z(i,j) = 1 is customer i is assigned to centroid j, where
the centroid of a region is the most central
customer of the region, thus
Z(j,j) = 1 if customer j is the centroid of its region;
! Each customer i must be assigned to a region
centered at some customer j;
@FOR( CUSTOMER(i):
@SUM( CXC(i,j): Z(i,j)) = 1;
);
@FOR( CXC(i,j):
@BIN( Z(i,j)); ! The Z(i,j) are binary, 0 or 1;
! If i assigned to j, then j is a centroid;
Z(i,j) <= Z(j,j);
);
@FOR( CUSTOMER(j):
! Calculate Work assigned to region centered at j;
WORKA(j) = @SUM( CXC(i,j): WORK(i)*Z(i,j));
! Calculate Sales Value assigned to j;
SALESA(j) = @SUM( CXC(i,j): SALESV(i)*Z(i,j));
! Calculate distance assigned to j;
DISTA(j) = @SUM( CXC(i,j): DIST(i,j)*Z(i,j));
);
! Minimize weighted combination of maximum distance in any
region + total distance over all regions;
OBJV = DISTMX + SUMDWGT*@SUM( CUSTOMER(j): DISTA(j));
MIN = OBJV;
! Limit on number of districts;
@SUM( CUSTOMER(i): Z(i,i)) <= NDS;
@FOR( CUSTOMER(j):
! Sales minimum target for each region;
SALESA(j) >= SALEST*Z(j,j);
! Work maximum target for each region;
WORKA(j) <= WORKT*Z(j,j);
! Maximum total distance incurred in any region;
DISTMX >= DISTA(j);
);
ENDSUBMODEL
CALC:
! Prepare distance matrix;
! This portion calculates a distance matrix DIST(i,j);
D2R=@PI()/180; ! Degrees to radians conversion factor;
! Compute Great Circle Distances. Radius of earth = 6371 km.
Notice this simplifies if LATI(i) = LATI(j) or LNGT(i) = LNGT(j);
@FOR( CXC(i,j):
@IFC( i #EQ# j: DIST(i,j) = 0;! Get rid of trivial roundoff;
@ELSE
DIST( i,j) = 6371*@acos(@SIN(D2R*LATI(i))*@SIN(D2R*LATI(j))+@COS(D2R*LATI(i))*@COS(D2R*LATI(j))
*@COS(@ABS(D2R*(LNGT(i)-LNGT(j)))));
);
);
@SOLVE( SALESASGN);
! Construct the subset, CXCSUB(i,j), of arcs selected;
@FOR( CXC( i,j) | Z(i,j) #GT# 0.5:
@INSERT( CXCSUB, i, j);
DCITY(i,j) = i; ! Departure city;
ACITY(i,j) = j; ! Arrival city;
ARROHD(i,j) = 0; ! No arrowheads on this arc;
);
! Display a little report;
@WRITE( 'District Design Analysis', @NEWLINE(1));
@WRITE( ' Input data summary:', @NEWLINE(1));
@WRITE( @FORMAT( NDS, '10.0f'), '= Number districts.', @NEWLINE(1));
@WRITE( @FORMAT( @SUM( CUSTOMER(i): WORK(i))/ NDS, '10.2f'), '= Average work/district. ',
@FORMAT( WORKT, '10.2f'),'= Max allowed.', @NEWLINE(1));
@WRITE( @FORMAT( @SUM( CUSTOMER(i): SALESV(i))/ NDS, '10.2f'), '= Average sales value/district.',
@FORMAT( SALEST, '10.2f'),'= Min allowed.', @NEWLINE(1));
@WRITE( @NEWLINE(1),' Solution summary:', @NEWLINE(1));
@WRITE(' District Centroid Workload Sales_potential Distance_from_centroid', @NEWLINE(1));
id = 0;
@FOR( CUSTOMER( j) | Z(j,j) #GT# 0.5:
id = id + 1;
@WRITE( @FORMAT( id, '6.0f'),' ',@FORMAT( CUSTOMER(j),'12s'), @FORMAT( WORKA(j),'12.1f'), @FORMAT( SALESA(j),'14.2f'),
@FORMAT( DISTA(j), '17.2f'), @NEWLINE(1));
);
@WRITE(@NEWLINE(1),' Assignment detail:',@NEWLINE(1),
' Centroid Customer Assigned',@NEWLINE(1));
@FOR( CUSTOMER(j) | Z(j,j) #GT# 0.5:
@FOR( CXC(i,j) | Z(i,j) #GT# 0:
@WRITE( @FORMAT( CUSTOMER(j), '15s'), @FORMAT( CUSTOMER(i), '15s'), @NEWLINE(1));
);
);
! Display a network diagram of the assignment to centroids;
@CHARTNETNODE(
' Sales Regions and their Centroids' ! Title of chart;
, 'Latitude', 'Longitude' ! Labels for horizontal and vertical;
, 'Customer' ! Legend for arc set 1;
, LNGT, LATI ! Coordinates of the nodes;
, DCITY, ACITY); ! Node pairs of arcs actually used;
ENDCALC
|
