! Assortment planning with customers ( AssortPlan.lng)
who have a strict preference ordering.
A vendor must decide which products to carry, taking into account
how customers will choose among the available products.
It costs money to introduce lots of products so
given consumer preferences, product profitability,
and cost of carrying each product,
which set of products should the vendor offer?
Each consumer market segment, s, has a size, NUM( s), which is an upper
limit on the total unit sales to that segment.
Each consumer market segment, s, has a relative preference ordering
RP( s, p) for buying product type p.
The base choice of buying nothing has a preference 0
A consumer will buy that single product of those
offered for which the segment has the highest preference.
Specifically if the vendor carries only product p,
then the unit sales to segment s will be
NUM(s), assuming RP( s, p) > 0;
! Keywords: Assortment planning, Consumer choice,
Demand Substitution, Marketing, Preference ordering;
! Ref:
Farias V. S. Jagabathula and D. Shah 2013 A Nonparametric
Approach to Modeling Choice with Limited "Data" Management Science
vol. 59 no. 2 pp. 305322;
SETS:
SEG: NUM, Z; ! Market segments;
PROD: PC, FXC, ZSTOCK, VOL; ! Products possible;
SXP( SEG, PROD): RP, PR, SOLD2;! Combinations;
ENDSETS DATA:
SEG = SEG1 SEG2 SEG3 SEG4 SEG5 SEG6 SEG7; ! The market segments;
! and their sizes, say in 1000's;
NUM = 19 13 24 39 11 12 14;
PROD = PROD1 PROD2 PROD3 PROD4 PROD5 PROD6; ! The products;
! Profit contribution per unit sold of each product;
PC = 16 16 13 18 14 19;
! Fixed cost of introducing each product;
FXC = 100 120 80 115 110 165;
! Relative preference of customer segment i
for product j. Bigger => more preferred;
RP =
4 2 3 2 6 1
0 0 3 6 0 2
0 3 6 2 3 1
0 0 3 0 4 0
0 5 1 4 3 2
4 0 1 0 1 1
3 4 2 0 0 0;! Segment 7;
NPT = 3; ! We want to carry at most this many products;
! Read from the (only) open spreadsheet, based on Range names;
! SEG = @OLE();
! NUM = @OLE();
! PROD = @OLE();
! PC = @OLE();
! FXC = @OLE();
! RP = @OLE();
! NPT = @OLE();
ENDDATA
SUBMODEL ASSORT_BINARY:
! Parameters:
NUM(s) = number people in segment s
PC(p) = profit contribution per unit of
product p sold
FXC(p) = fixed cost of introducing product p
RP(s p) = relative attractiveness to segment s
of product p higher => more preferred.
NPT = number products that can be carried.
Variables:
ZSTOCK(p) = 1 if we carry product p else 0
VOL(p) = volume or units sold of product p
PR(s p) = proportion (0 or 1 in this case) of
customers from segment s who will buy product p given
the set of products available;
! Maximize profit contribution from sales minus
fixed cost of introducing the products;
MAX = PROFITC  FIXEDCOST;
PROFITC = @SUM( PROD(p): PC(p)* VOL(p));
FIXEDCOST = @SUM( PROD( p): FXC(p)* ZSTOCK(p));
! ZSTOCK(p) = 1 if we introduce product p else 0;
@FOR( PROD(p): @BIN( ZSTOCK(p)));
! Set PR(s p) = 0 if segment s does not buy product p.
Compute portion/probability of/that segment s buys
product p. Cannot buy it if not introduced;
@FOR( SXP(s, p):
! If s buys product p then p must be offered;
PR(s, p) <= ZSTOCK(p);
! Consumer s will not buy p if some more preferred product q is offered;
@FOR( PROD( q)  RP(s, q) #GT# RP(s, p):
PR(s, p) + ZSTOCK(q) <= 1;
);
! Segment s Will not buy p if s prefers to buy nothing better;
PR(s, p) <= ( RP(s, p) #GT# 0);
);
! Buys at most one product;
@FOR( SEG( s):
@SUM( PROD( p): PR( s, p)) <= 1;
);
! Volume sold of product p;
@FOR( PROD(p):
VOL( p) = @SUM( SEG(i): NUM( i)* PR( i, p));
);
! Limit on products carried;
@SUM( PROD(p): ZSTOCK(p)) <= NPT;
ENDSUBMODEL
CALC:
@SET( 'TERSEO', 2); ! Output level (0:verb 1:terse 2:only errors 3:none);
@SOLVE( ASSORT_BINARY);
@FOR( SEG( s):
@FOR( PROD( p):
SOLD2( s, p) = NUM(s)* PR(s,p);
);
);
NETPROF = PROFITC  FIXEDCOST;
@WRITE(' Profit contribution= ', @FORMAT( PROFITC, '10.2f'), @NEWLINE( 1));
@WRITE(' Fixed costs= ', @FORMAT( FIXEDCOST, '10.2f'), @NEWLINE( 1));
@WRITE(' Net= ', @FORMAT( PROFITC FIXEDCOST, '10.2f'), @NEWLINE( 1));
@WRITE(' The products to stock are:', @NEWLINE( 1));
@WRITE(' ');
@FOR( PROD( p)  ZSTOCK( p) #GT# 0.5:
@WRITE( @FORMAT( PROD( p), '9s'), ' ');
);
@WRITE( @NEWLINE( 1));
@WRITE(' Segment : Amount bought of each product', @NEWLINE( 1));
@FOR( SEG( s):
@WRITE( @FORMAT( SEG( s), '8s'), ':');
@FOR( PROD( p)  ZSTOCK( p) #GT# 0.5:
@WRITE( @FORMAT( NUM(s)* PR(s, p), '9.1f'), ' ');
);
@WRITE( @NEWLINE( 1));
);
! Write results to the (only) open spreadsheet, based on Range names;
!@OLE ( ) = ZSTOCK;
!@OLE ( ) = SOLD2;
!@OLE ( ) = PROFITC;
!@OLE ( ) = FIXEDCOST;
!@OLE ( ) = NETPROF;
ENDCALC
