Ordering decision with quantity discounts
Text Box: VLOOKUP function =VLOOKUP(OrderQuan,CostLookup,2)*OrderQuan The VLOOKUP part of this formula says to compare the order quantity to the first (leftmost) column of the table in the CostLookup range and return the corresponding value in the second column (because the last argument is 2). Two Important Things: 1. the leftmost column is always the column used for comparison 2. the entries in this column must be arranged in increasing order from top to bottom
Text Box: Range names used: UnitPrice - B5 SalePrice - B6 CostLookup - D5:E9 OrderQuan - B8 Demand - B11 SoldReg - B12 SoldSale - B13 Revenue - B14 Cost - B15 Profit - B16 Probabilities - B32:J32
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 =VLOOKUP(OrderQuan,CostLookup,2)*OrderQuan
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
Text Box: The expected profit is a weighted average of the profits in any row in the data table, using the probabilities as the weights =SUMPRODUCT(B20:J20,Probabilities) The SUMPRODUCT takes two range arguments, which must be exactly the same size and shape, and sums the products of the corresponding cell values in these two ranges.
1000 $6,750
1500 $9,500
Text Box: Order 2000 to maximize the expected profit.
2000 $12,250
2500 $11,375
3000 $9,500
3500 $4,875
4000 $1,350
4500 -$4,150