2. Pages Bookstore, with many locations across the United States, places orders

Question

2. Pages Bookstore, with many locations across the United States, places orders for all the latest books and then distributes them to its individual bookstores. Pages needs a model to help it order the appropriate number of any title.

For example, Pages’ plans to order a popular new hardback novel, which will sell for $26. It can purchase any number of this book from the publisher, the unit cost will be $14. The purchasing manager is very uncertain about the demand for this book and also, this one will eventually come out in paperback. If there are any hardbacks left when the paperback comes out, the leftover hardbacks will be put on sale for $6, at which price all leftovers are expected to be sold.

a. Set up an Excel spreadsheet model that will determine profit for Pages with the two uncertain variables, customer demand (for the hardback) and quantity of hardbacks ordered from the publisher.

b. Create a two-way table (using Excel Data/What-If/Data Table) to show how profit responds to varying amounts for order quantity and demand.

Put order quantity as the row input, use quantities of 100, 200, 300, 400

Put demand as the column input, use quantities of 150, 250, 350, 450

Explanation / Answer

P = (D)x26 + (O-D)x6 - (O)x12

6 possible combinations are as shown in the below table

- Profit that Pages will make at certain demand and order Qty. = P - Let us consider the demand of the Novel = D - Qty of hardbacks ordered from the publisher = O - Price per Unit at with the pages will place an order with publisher = 14 $ - Price per Unit at with the Novel will be sold from the stores = 26 $ - Net margin per book (26-14) 12 $ - As stated above, the Novels with Hardback will be sold at 26$ till the - Novel with soft back will be published. Once the Novel with softback - will be published, the left out Novels will be sold at = 6 $ a) Excel spreadsheet model to determine profit

P = (D)x26 + (O-D)x6 - (O)x12

b) How profit responds to the varying Qty. of Order and Demand There are 4 possible values for Order Qty. - 100, 200, 300, 400 And 4 possible values for Demand Qty. - 150, 250, 350, 450 So total 4 x 4 = 16 combinations are possible for profit as per various Demand Qty. and Order Qty. But to calculate Profit, if the demand is more than order Qty, it can't be catered. Thus out of 16 combinations, we have to take combinations where O is greater than or equal to D -

6 possible combinations are as shown in the below table

Sr. No. Order Qty. (O) Demand Qty. (D) Profit as per above equation (P) 1 200 150 1400 $ 2 300 150 600 $ 3 300 250 2600 $ 4 400 150 -200 $ 5 400 250 1800 $ 6 400 350 3800 $

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

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