Projects / Which supplier bids to accept in a packaging auction

Which supplier bids to accept in a packaging auction

Course project, Michigan Ross MBA. Set-covering integer program in Excel Solver, checked by full enumeration in Python. Not client or employer work.

ExcelPython
Excel modelSolver model with the cost of each sourcing policyPython checkAll 4,096 plans, enumerated
SituationA confectionery company buys 12 packaging items through an online auction. Seven suppliers sent 12 bids.
ComplicationEach bid is a bundle: some of the items at one all-or-nothing price, and the bundles overlap. Procurement also wants to know the cost of each sourcing policy before it commits.
QuestionWhich bids buy all 12 items at the lowest cost, and what does each policy add?
AnswerFour of 12 bids buy every item for $4,470. A strategic-partner policy adds 27%; a local-supplier policy adds nothing.

The price of each bid is fixed, so the cost depends on which bids are accepted and on the rules they must meet

  • Total price of the accepted bids$4,470 in the answer
    • Bids accepted
      • Which bids to acceptThe lever: 12 yes/no choices

        Is a mix of bids cheaper than one bundle from one supplier?

        Yes. $4,470 against $4,639.

      • Price of each bidFixed: set by the suppliers in the auction
    • Rules that the plan must meet
      • Each item is coveredThe limit: 12 items, each in one accepted bid or more
      • Sourcing policiesLocal supplier, second source, strategic partners

        What does each policy add?

        $0 to $1,199. Local supplier: $0. Second source: $999. Strategic partners: $1,199.

      • Minimum order of a supplierSupplier 1 wants $500 or more, or nothing

        Does the minimum change the plan?

        Yes. The one-supplier bundle becomes the lowest cost, at $4,639.

Lever what the decision changes Limit what the answer must respect Fixed outside the decision

Lowest cost to buy all 12 items

$4,470

bids 4, 9, 10, 12, from four suppliers

Saving against the one-supplier bundle

$169

3.6% below bid 5 at $4,639

Cost of the strategic-partner policy

+$1,199

+27%, the most expensive policy tested

Four bids cover all 12 items, and only one small item is bought twice

Items in each bid. Each bid is all-or-nothing at one price.
Bid123456789101112
SupplierS1S2S3S1S4S2S5S6S3S7S2S5
Brand A, large 1,000●●●●
Brand A, medium 2,500●●●
Brand A, small 4,000●●●●
Brand B, large 800●●●
Brand B, medium 1,500●●●●
Brand B, small 2,500●●●●●
Brand C, large 400●●●
Brand C, medium 750●●
Brand C, small 1,200●●●
Brand D, large 100●●●
Brand D, medium 100●●●●
Brand D, small 50●●●●●●
Bid price$2,050$350$1,199$220$4,639$199$1,199$1,350$350$2,200$999$1,700

4 accepted bid ● item bought through this bid ● item offered, bid not accepted

A local supplier is free, but the partner policy adds 27% to the cost

The model

Decision

Accept or reject each of the 12 bids from 7 suppliers (12 yes/no variables).

Objective

The lowest total price of the accepted bids.

Constraints

Each of the 12 items is in at least one accepted bid. Each sourcing policy adds one or more linear constraints.

  • The local-supplier policy has no cost, because the lowest-cost plan already has a bid from supplier 7.
  • A second source for Brand B medium costs $999 more (22%).
  • With a $500 minimum order for supplier 1, the $220 bid from that supplier is not available alone. The one-supplier bundle then has the lowest cost, at $4,639.
  • The minimum-order rule needs one more yes/no variable: do business with supplier 1 or not.