| Sams Bookstore is located all across the United States in which
places orders for all the latest books and distributes them to local
bookstores. |
|
|
| By
developing a spreadsheet model, Sam's Bookstore is capable of calculating
profit for order quantities and the possible
demands. |
|
|
| Ordering
Decision with Quantity Discounts |
|
|
| Inputs |
|
Unit cost as a function
of quantity ordered |
|
| Unit cost - see table to right |
|
At least |
Unit cost |
|
|
|
| Unit price |
$30 |
|
0 |
$24.00 |
|
|
| Sale price for leftovers |
$10 |
|
1000 |
$23.00 |
|
|
|
2000 |
$22.25 |
|
|
| Order quantity (trial value) |
2500 |
|
3000 |
$21.75 |
|
|
|
4000 |
$21.30 |
|
|
| Profit model |
|
|
| Demand (trial value) |
2000 |
|
|
| Units sold at regular price |
2000 |
|
|
| Units sold at sale price |
500 |
|
|
| Revenue |
$65,000 |
|
|
| Cost |
$55,625 |
|
|
| Profit |
$9,375 |
|
|
|
|
|
|
| Data
table of profit as a function of order quantity (along side) and demand
(along top) |
|
| $9,375 |
500 |
1000 |
1500 |
2000 |
2500 |
3000 |
3500 |
4000 |
4500 |
|
|
|
| 500 |
$3,000 |
$3,000 |
$3,000 |
$3,000 |
$3,000 |
$3,000 |
$3,000 |
$3,000 |
$3,000 |
|
|
| 1000 |
-$3,000 |
$7,000 |
$7,000 |
$7,000 |
$7,000 |
$7,000 |
$7,000 |
$7,000 |
$7,000 |
|
| 1500 |
-$9,500 |
$500 |
$10,500 |
$10,500 |
$10,500 |
$10,500 |
$10,500 |
$10,500 |
$10,500 |
|
| 2000 |
-$14,500 |
-$4,500 |
$5,500 |
$15,500 |
$15,500 |
$15,500 |
$15,500 |
$15,500 |
$15,500 |
|
| 2500 |
-$20,625 |
-$10,625 |
-$625 |
$9,375 |
$19,375 |
$19,375 |
$19,375 |
$19,375 |
$19,375 |
|
| 3000 |
-$25,250 |
-$15,250 |
-$5,250 |
$4,750 |
$14,750 |
$24,750 |
$24,750 |
$24,750 |
$24,750 |
|
| 3500 |
-$31,125 |
-$21,125 |
-$11,125 |
-$1,125 |
$8,875 |
$18,875 |
$28,875 |
$28,875 |
$28,875 |
|
| 4000 |
-$35,200 |
-$25,200 |
-$15,200 |
-$5,200 |
$4,800 |
$14,800 |
$24,800 |
$34,800 |
$34,800 |
|
| 4500 |
-$40,850 |
-$30,850 |
-$20,850 |
-$10,850 |
-$850 |
$9,150 |
$19,150 |
$29,150 |
$39,150 |
|
|
|
|
| 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 |
|
|
Sum of probabilities --> |
1 |
|
| Order quantity |
Expected profit |
|
| 500 |
$3,000 |
|
| 1000 |
$6,750 |
|
|
| 1500 |
$9,500 |
|
| 2000 |
$12,250 |
|
| 2500 |
$11,375 |
|
| 3000 |
$9,500 |
|
| 3500 |
$4,875 |
|
| 4000 |
$1,350 |
|
| 4500 |
-$4,150 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
