An Ordering Decision with Quantity Discounts - PowerPoint PPT Presentation

1 / 15
About This Presentation
Title:

An Ordering Decision with Quantity Discounts

Description:

Sam's Bookstore, with many locations across the United States, places orders for ... Sam's needs a model to help it order the appropriate number of any title. ... – PowerPoint PPT presentation

Number of Views:113
Avg rating:3.0/5.0
Slides: 16
Provided by: lisa284
Category:

less

Transcript and Presenter's Notes

Title: 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.
Write a Comment
User Comments (0)
About PowerShow.com