Barney’s Bookstore Barney’s Bookstore plans to order a popular new hardback nove

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.

Explanation / Answer

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

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.