```! 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. 305-322; SETS: SEG: NUM, Z; ! Market segments; PROD: PC, FXC, ZSTOCK, VOL; ! Products possible; SXP( SEG, PROD): RP, PR, SOLD2;! Combinations; ENDSETSDATA: 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 ```