Sam’s Bookstore, with many locations across the United States, places orders for all of the latest books and then distributes them to its individual bookstores. Sam’s needs a model to help it order the appropriate number of any title. For example, Sam’s plans to order a popular 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. Specifically, if the number ordered is less than 1000, the unit cost is $24. After each 1000, the unit cost drops: to $23 for at least 1000 copies, to $22.25 for at least 2000, to $21.75 for at least 3000, and to $21.30 (the lowest possible unit cost) for at least 4000. For example, if Sam’s orders 2500 books, its total cost is $22.25(2500) =$55,625. Sam’s is very uncertain about the demand for this book—it estimates that demand could be anywhere from 500 to 4500. Also, as with most hardback novels, this one will eventually come out in paperback. Therefore, if Sam’s 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 Sam’s order from the publisher?
Business Objectives To create a model to determine the company’s profit, given fixed values of demand and the order quantity, and then to model the demand uncertainty explicitly and to choose the expected profit maximizing (“best”) order quantity.
Excel Objectives To learn how to build in complex logic with IF formulas, to get online help about Excel functions with the fx
button, to learn how to use lookup functions, to see how two-way data tables allow you to answer more extensive what-if questions, and to introduce Excel’s SUMPRODUCT function.