Ordering with Quantity Discounts

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 order depends on the number ordered.

If the number ordered is less than 1000, then the unit cost is $24. After each 1000, the unit cost (cost per book) drops to: $23 for at least 1000 copies; $22.25 for at least 2000 copies; $21.75 for at least 3000 copies; and $21.30 for at least 4000 copies. For example, if Sam's order 2500 books, its total cost is 2500*$22.25 = $55,625.

Sam's is 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, a price at which Sam's believes all the leftovers will be sold.

How many copies of this hardback should Sam's order from the publisher?

Modeling Assignment 4

Question 1.

At what value of order quantity is the maximum profit expected to occur with the given probabilities for demand?

Answer:

Question 2.

For a demand value of 2000, what order quantity will result in a breakeven profit ($0 profit)? What will happen to the profit at order quantities higher than this value? Hint: Use goal seek.

Answer:

Question 3.

At what order quantity value does the maximum profit occur when the probabilities for all the possible demand values are equal to one another?

Answer:

please use excel to solve

Model of expected demands
Demand	500	1000	1500	2000	2500	3000	3500	4000	4500
Probability	0.025	0.05	0.15	0.25	0.25	0.15	0.07	0.04	0.015