An Ordering Decision with Quantity Discounts

1
Example 2.3

• An Ordering Decision with Quantity Discounts

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
QUANTITYDISCOUNT.XLS

• The profit model shown on the next slide can be
  found in this file.
• Note that the OrderQuan 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
Completing the Model

• After entering the inputs and trial values of
  order quantity and demand, proceed through the
  following steps to complete the model.
• Revenues. Sams can sell only what it has, and it
  will sell any leftovers at the sale price.
  Therefore enter the formulas MIN(OrderQuan,Dem
  and), IF(OrderQuanDemand, OrderQuan-Demand,0),
  and SoldRegUnitPriceSoldSaleSalePrice
  in the SoldReg, SoldSale and Revenue cells.

9
Completing 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
  formulaVLOOKUP(OrderQuan,CostLookup,2)OrderQuan
  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 clear 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 just 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. We show one
  possible set on the next slide.
• Sams would need to estimate these probabilies,
  possibly on the basis of other similar novels it
  has sold in the past.

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.
• Now we can use these probabilities 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(B20J2
  0,Probabilities) in cell B35 and copy it down to
  cell B43.

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.