Question

Barney’s Bookstore

Barney’s Bookstore plans to order a popular new hardback novel. It can purchase any number of this book from the publisher, but the unit cost of the book depends on the number ordered as shown below:

Order Qty Cost/Unit

1000+ $15

2000+ $14

3000+ $13

4000+ $12

Barney’s believes that demand for the book will depend on price. The lower the price, the higher the demand. Based on past data it estimates the following demand:

Price Demand

$30 2000

$28 2400

$26 3000

$24 3800

$22 4800

Also, as with most hardback novels, this one will eventually come out in paperback. Therefore, if Barney’s has any hardbacks left when the paperback comes out, it will put them on sale for $8, at which price it believes all leftovers will be sold.   In this situation Barney’s has to decide on both the price to charge and the quantity to order from the publisher.

Build an Excel model to calculate total profit for any given price and order quantity.

In the above model create a 2-way data table of price versus order quantity. Price should vary as shown in the price-demand table. Order quantity should be the 5 values shown in the price-demand table. This table should give you total profit for any combination of price and order quantity.

Note: Use good structure and formatting in all your Excel models. Don’t display excessive number of decimal places. Anyone reading your Excel model should easily be able to figure out what you are doing.

Answer 1

Barney's Bookstore
Order Qty	Cost/Unit ($)		Leftover hardbacks sold at $8 per book
1000+	15
2000+	14
3000+	13
4000+	12
Price of Book ($)	Demand in units
30	2000
28	2400
26	3000
24	3800
22	4800
Table depicting total profit for any combination of price and order quantity
Price ($)	Order Quantity	Total Revenue	Order Cost per unit ($)	Total Order Cost ($)	Total Profit ($)
(a)	(b)	(c) = (a)*(b)	(d)	(e) = (d)*(b)	(f) = (c) - (e)
30	2000	60000	14	28000	32000
28	2400	67200	14	33600	33600
26	3000	78000	13	39000	39000
24	3800	91200	13	49400	41800
22	4800	105600	12	57600	48000
Hence the most profitable option for Barney's Bookstore would be to order 4800 books at $12 per unit; and sell them at $22 per unit; hence making a total profit of $48000