Statistical
Newsvendor model formula: Critical Fractile & optimal perishable stock level
Calculate a single-period critical fractile and select an order quantity from five equally likely demand scenarios. Distinguish empirical demand quantiles from interpolated percentiles.
Match the order quantile to the assumed demand distribution
In this single-period model, selling price exceeds unit cost, which exceeds salvage value. Underage cost is price minus cost and overage cost is cost minus salvage. Assume linear costs, lost sales, no replenishment, and no capacity or minimum-order constraints. The critical fractile is Cu/(Cu+Co); a 50th percentile is a median, not generally a mean.
Treat the five supplied demand observations as equally likely scenarios. Choose the smallest demand whose empirical cumulative probability reaches the critical fractile: SMALL at rank ROUNDUP(5*fractile,0). At 2/3 the fourth demand is 160. PERCENTILE.INC interpolates and would give about 153.33, which is not the empirical optimum for these five scenarios. Neither model guarantees the best future order: demand data must represent the intended selling period.
| Newsvendor Parameter ($28 Price, $12 Cost, $4 Salvage) | Formula | Value |
|---|---|---|
| Underage Stockout Cost (Cu) | Price - Cost = $28 - $12 | $16.00 lost profit / unit |
| Overage Leftover Cost (Co) | Cost - Salvage = $12 - $4 | $8.00 spoilage loss / unit |
| Critical fractile (model target) | Cu / (Cu + Co) = 16 / 24 | 0.6667 (66.67th percentile) |
| Empirical-scenario order quantity | SMALL(Demand, ROUNDUP(5*CF,0)) | 160 units |
Calculate Newsvendor Critical Fractile Cu / (Cu + Co)
A2=SellingPrice ($28), B2=UnitCost ($12), C2=SalvageValue ($4).
=IF(OR(COUNT(A2:C2)<>3,C2<0,A2<=B2,B2<=C2),"Invalid inputs",LET(cu, A2 - B2, co, B2 - C2, ROUND(cu / (cu + co), 4)))Order quantity under the five-scenario empirical model
E2:E6 contains exactly five nonnegative demand observations, each given probability 1/5. For this whole-item example enter whole-unit demands. The rank calculation uses the unrounded critical fractile.
=IF(OR(COUNT(A2:C2)<>3,C2<0,A2<=B2,B2<=C2,COUNT(E2:E6)<>5,MIN(E2:E6)<0,E2<>INT(E2),E3<>INT(E3),E4<>INT(E4),E5<>INT(E5),E6<>INT(E6)),"Invalid inputs",SMALL(E2:E6,ROUNDUP(ROWS(E2:E6)*(A2-B2)/(A2-C2),0)))Try the example
Use the original values or change them to test your understanding.