! Assortment planning/ Consumer Choice with customers ( MultiNomi.lng).
who have a Multinomial Logit(MNL) preferece function.
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 customer segment s has a relative preference
RP( s, p), for buying product type p.
The base choice of buying nothing has a preference 1.
The MNL model assumes the probability of consumer of type s buying
product p, among the available products, is
proportional to RP( s, p). E.g., if the vendor carries only product p,
then the unit sales to segment s will be
NUM( s)* RP( s, p)/(1+ RP( s, p));
! Keywords: Assortment Planning, Consumer Choice, Demand substitution,
Marketing, Multinomial Logit;
SETS:
SEG: NUM, Z; ! Customer/market segments;
PROD: PC, FXC, ZSTOCK, VOL; ! Products possible;
SXP( SEG, PROD): RP, PR, SOLD2;! Combinations;
ENDSETS
DATA:
! The market segments;
SEG = SEG1 SEG2 SEG3 SEG4 SEG5 SEG6 SEG7;
! and their sizes, say in 1000's;
NUM = 19 13 24 39 11 12 14;
! The products;
PROD = PROD1 PROD2 PROD3 PROD4 PROD5 PROD6;
! 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 = 5 3 4 2 6 1
0 0 3 6 1 2
2 3 6 0 0 1
0 1 3 0 4 0
0 1 1 4 3 3
4 0 1 0 1 2
2 2 4 1 2 1;
NPT = 3; ! We want to carry at most NPT 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_FRAC:
! Parameters:
NUM(i) = number people in segment i,
PC(j) = profit contribution per unit of
product j sold,
FXC(j) = fixed cost of introducing product j,
RP(i,j) = relative attractiveness to segment i
of product j,
NPT = number products that can be carried.
Variables:
ZSTOCK(j) = 1 if we carry product j, else 0,
VOL(j) = volume, or units, sold of product j,
PR(i,j) = proportion of customers from
segment i who will buy product j, given
the set of products available.
Z(i) = total attractiveness of all available
products to segment i. ;
! 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)));
! Compute portion/probability of/that segment i buys
product p. Cannot buy it if not introduced;
@FOR( SXP(i,p):
! The basic multinomial logit model of attractiveness
of a product, given other products available, option 0 ;
! PR(i,p) = ZSTOCK(p)*RP(i,p)/(1 + @SUM(PROD(k): ZSTOCK(k)*RP(i,k)));
! Reformulation to almost linear form ;
Z(i) = 1 + @SUM( PROD(k): ZSTOCK( k)*RP( i, k)) ;
! The next constraint is nonlinear;
PR(i, p)*Z( i) = ZSTOCK(p) * RP(i, p) ;
);
! 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_FRAC);
@FOR( SEG( s):
@FOR( PROD( p):
SOLD2( s, p) = NUM(s)* PR(s,p);
);
);
NETPROF = PROFITC - FIXEDCOST;
! If we want to display a text report;
@WRITE(' Profit contribution= ', @FORMAT( PROFITC, '10.2f'), @NEWLINE( 1));
@WRITE(' Fixed costs= ', @FORMAT( FIXEDCOST, '10.2f'), @NEWLINE( 1));
@WRITE(' Net= ', @FORMAT( NETPROF, '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
|
