Ordering with Quantity Discounts at Sams Bookstore

1
Example 2.4

• Ordering with Quantity Discounts at Sams
  Bookstore

2
Background Information

• Sams Bookstore, with many locations across the
  United States, places orders for all of the
  latest books and then distributes them to
  individual bookstores.
• Sams needs a model to help it order the
  appropriate number of any title.
• For example, it plans to order a hot new hardback
  novel, which it will sell for 30. It can
  purchase any number of this book from the
  publisher, but due to quantity discounts, the
  unit cost for all books it orders depends on the
  number ordered.

3
Background Information -- continued

• Specifically
• If the number ordered is less than 1000, the unit
  cost is 24
• For at least 1000 copies the price is 23
• For at least 2000 copies the price is 22.25
• For at least 3000 copies the price is 21.75
• For at least 4000 copies the price is 21.30
• Sams is very uncertain about the demand for this
  book it estimates that demand could be anywhere
  from 500-4500.

4
Background Information -- continued

• Also, as with most hardback novels, this one will
  eventually come out in paperback.
• Therefore, if Sams has any hardbacks left when
  the paperback comes out, it will put them on sale
  for 10, at which price it believes all leftovers
  will be sold.
• How many copies of this hardback novel should
  Sams order from the publisher?

5
The Solution

• First we develop a model to calculate Sams
  profit for any order quantity and any possible
  demand.
• Then we will perform a sensitivity analysis to
  see how profit depends on these two quantities.
• Finally we will indicate one possible method Sam
  might use to choose the best order quantity.

6
QuantityDiscounts.xls

• The profit model shown on the next slide can be
  found in this file.
• Note that the Order_quantity and Demand cells are
  trial values. We can put any values in these
  cells, just to test the logic of the model.
• Also note how we have used a table to indicate
  quantity discounts for ordering. After entering
  the inputs and trial values of order quantity and
  demand, use the following steps to complete the
  model.

7
The Profit Model
8
Developing the Model

• Inputs and range names. Enter all inputs and name
  the ranges as indicated.
• Revenues. Sams can sell only what it has, and it
  will sell any leftovers at the sale price.
  Therefore enter the formulas MIN(Order_qunatity
  ,Demand) IF(Order_quantitygtDemand,
  Order_quantity-Demand,0), Units_sold_at_regular
  priceRegular_priceUnits_sold_at_leftover
  priceLeftover_price
• in cells B15, B16 and B17.

9
Developing the Model -- continued

• Total ordering cost. Depending on order quantity,
  we find the appropriate unit cost from the unit
  cost table and multiply it by the order quantity
  to obtain the total ordering cost. This could be
  accomplished with a complex nested IF formula,
  but a much better way is to use the VLOOKUP
  function. Specifically, enter the formula
  VLOOKUP(Order_quantity,CostLookup,2)Order_quant
  ity
• in the Cost cell.
• Profit. Calculate the profit with the formula
  Revenue-Cost

10
Creating a Data Table

• The next step is to create a data table for
  profit as a function of the order quantity and
  demand. The data table is shown here.

11
Creating a Data Table -- continued

• This table shows that profit depends heavily on
  both order quantity and demand, and how higher
  demands lead to larger profits.
• But is it still unclear which order quantity
  Sams should select.
• Remember that Sams has complete control over the
  order quantity, but it also has no direct control
  over demand.

12
Answering the Question

• The ordering decision depends not only on which
  demands are possible, but on which demands are
  likely to occur.
• The usual way to express this information is with
  a set of probabilities that sum to 1.
• Sams would need to estimate these probabilities,
  possibly on the basis of other similar novels it
  has sold in the past.
• These probabilities can be used to find an
  expected profit for each quantity.

13
Answering the Question -- continued
14
Answering the Question -- continued

• The ones shown indicate that Sams believes the
  most likely demands are 2000 and 2500, with other
  values on either side less likely.
• These probabilities can be used to find an
  expected profit for each order quantity.
• This expected profit is a weighted average of the
  profits in any row in the data table, using the
  probabilities as the weights. The easiest way to
  do this is to enter the formulaSUMPRODUCT(B23J2
  3,Probabilities) in cell B38 and copy it down to
  cell B46.

15
Answering the Question -- continued

• The largest of the expected profits, 12,250,
  corresponds to an order quantity of 2000, so we
  would recommend that Sams order 2000 copies of
  the book.
• This does not guarantee that Sams will make a
  profit of 12,250 the actual profit depends on
  the eventual demand but it represents a
  reasonable way to proceed in the face of
  uncertain demand.