The Newsvendor on a Worksheet and in Code

Agenda

• The five sheets of Chapter7Models.xlsx, and the cells that carry the mathematics
• Reading the sheets on the final buy, and pricing a scrap dealer’s offer
• What the worksheet does not do
• The software: a cost structure, an exact route and a sampled route that must agree
• The final buy in code, and a salvage rule no formula takes

The Five Sheets

The decision is a single number; the work is in producing the distribution it is a quantile of. The division between the sheets is the chapter’s argument made visible.

The sheets are linked: Fit reads Data, Newsvendor reads its moments from Fit, and Fit and Horizon read the critical ratio back from Newsvendor. Only Discrete stands alone.

Fit: the Procedure on a Sheet

Horizon: Walking the Exact Model

The fourteen-week total is negative binomial with \(p = 0.054222\) and \(r = 14 \times 1.9057 = 26.6798\). Excel’s NEGBINOM.DIST will not take a number of successes that is not whole, so the sheet builds the mass function by recursion:

\[ \begin{aligned} g(0) &= p^{r} &&\text{start}\\ g(x) &= g(x-1)\,\frac{(x + r - 1)(1 - p)}{x} &&\text{each row from the row above} \end{aligned} \]

and everything after it is a running sum.

The staircase rule stops at 469, where the first order loss function reads 35.1478 belts of expected shortage: the figure every other model on the workbook is priced against.

Newsvendor: the Cells That Carry the Mathematics

Notice that the loss function is a formula, not a macro: Excel has none, and needs none, since \(\Phi^{1}\) is two built-in functions and a subtraction. And the arithmetic check of the final buy is wired into the sheet.

Reading the Sheet, With a Check by Hand

The sheet ships holding the belt. B17 and B18 return \(\mu = 465.3654\) and \(\sigma = 92.6420\), every digit carried; B22 returns \(Q^{*} = 475.1761\); B27 returns an expected shortage of 32.2605.

The check, by hand. The normal loss table steps \(z\) by tenths: \(\Phi^{1}(0.1) = 0.3509\) and \(\Phi^{1}(0.2) = 0.3069\). Interpolating to \(z = 0.1059\):

\[ \begin{aligned} \Phi^{1}(0.1059) &\approx 0.3509 - 0.059(0.3509 - 0.3069) = 0.3483 &&\text{linear interpolation}\\ G^{1}(Q^{*}) &= 92.6420(0.3483) = 32.27 \text{ belts} &&\text{scale by } \sigma \end{aligned} \]

against B27’s 32.2605 from the exact 0.3482: a hundredth of a belt, the interpolation. A loss function that has lost its \(\sigma\) is off by a factor of 92, and no interpolation error looks like that.

B32 and B33 both return $3,050.43, B34 reads zero, and B35 returns $17,891.01: the final buy’s figures to the cent.

A Scrap Offer of $20

The scrap dealer offers $20 a belt rather than $12 for whatever is left. What is the offer worth, and should the order change? One cell answers both: set B7 to 20.

\[ c_o = \$30, \qquad \frac{45}{75} = 0.6000, \qquad Q^{*} = 488.84, \qquad E[G] = \$18{,}257.08 \]

The expected profit rises $366.06. How much of that comes from re-optimizing the order?

Your Own Item, and What the Worksheet Does Not Do

Pointing the sheets at a new item. Paste a history into column B of Data and every sheet but Discrete follows it, family verdicts included; the period length is Fit!B26. Or type a known mean and standard deviation over Newsvendor!B17 and B18, which breaks the link to Fit deliberately. Discrete walks 0 to 15 units; if no row reaches the ratio, B12 returns #N/A rather than a wrong answer.

The code has neither limit.

Designing the Software: the Nouns

Five requirements: solve exactly for continuous and discrete models; evaluate any quantity, not only the best; estimate a quantity’s profit by sampling with the same cost function; refuse a problem with no finite positive answer; take stock on hand.

• A demand model: a distribution with a quantile and a first order loss function. Not a new class; the KSL’s LossFunctionDistributionIfc already is one, and the first decision is not to reimplement it.
• A single-period cost structure: \(c_o\), \(c_u\) and the mapping from \(s\), \(c\), \(u\), \(\pi\). It owns the critical ratio and the refusal, rejecting a nonpositive cost at construction.
• A solution: a quantity and what it implies, computed for any quantity.

The Cost Structure, and the Exact Route

data class SinglePeriodCosts(val price: Double, val cost: Double,
                             val salvage: Double, val penalty: Double = 0.0) {
    val overageCost: Double get() = cost - salvage
    val underageCost: Double get() = price - cost + penalty
    val criticalRatio: Double get() = underageCost / (underageCost + overageCost)
    fun profit(q: Double, d: Double): Double =
        price * min(d, q) + salvage * max(q - d, 0.0) - cost * q - penalty * max(d - q, 0.0)
}

fun optimalLevel(costs: SinglePeriodCosts, demand: DistributionFunctionIfc): Double {
    val ratio = costs.criticalRatio
    if (demand !is DiscreteDistributionIfc) return demand.invCDF(ratio)
    var x = max(floor(demand.invCDF(ratio)), 0.0)
    while (x > 0.0 && demand.cdf(x - 1.0) >= ratio) x -= 1.0
    while (demand.cdf(x) < ratio) x += 1.0
    return x
}

overageCost and underageCost are \(c_o = c - u\) and \(c_u = s - c + \pi\); profit is the profit for one realized demand d. For a discrete demand, the two loops make optimalLevel the staircase rule exactly, whatever convention the library’s quantile uses. copy(penalty = 40.0) is the $40 penalty in one line.

The Sampled Route, and Why the Routes Must Agree

fun estimate(demand: RVariableIfc, q: Double, replications: Int,
             profit: (q: Double, d: Double) -> Double): Statistic {
    val stat = Statistic("profit at %.2f".format(q))
    demand.resetStartStream()
    repeat(replications) { stat.collect(profit(q, demand.value)) }
    return stat
}
fun estimate(costs: SinglePeriodCosts, demand: RVariableIfc, q: Double, replications: Int) =
    estimate(demand, q, replications, costs::profit)

The simulation loop with its free variables named. The second form passes costs::profit, the very function the cost structure defines; the first takes any profit function.

The Final Buy in Code

val belt = SinglePeriodCosts(price = 95.0, cost = 50.0, salvage = 12.0)
val horizon = DemandFitting.fittedHorizon()
println(solve(belt, Normal(horizon.mean(), horizon.variance())))
println(solve(belt, horizon))

order 475.18 units, to a level of 475.18 with 0.00 on hand
  critical ratio        0.542169   F(level) 0.542169
  expected shortage       32.2605 units
  expected leftover       42.0712 units
  expected cost           3050.43
  expected profit        17891.01
order 469.00 units, to a level of 469.00 with 0.00 on hand
  critical ratio        0.542169   F(level) 0.543428
  expected shortage       35.1478 units
  expected leftover       38.7824 units
  expected cost           3055.38
  expected profit        17886.06

The check against the worksheet. The first block is B22, B27, B28, B32 and B35 of Newsvendor to every digit; the second is Horizon’s 469. Notice F(level): equal to the ratio against the normal, a little above it against the negative binomial. That is the staircase.

A Salvage Rule No Formula Takes

The scrap dealer takes the first 100 belts at $12 and the rest at $4. Only the profit function changes:

val twoTier = { q: Double, d: Double ->
    val left = max(q - d, 0.0)
    belt.price * min(d, q) + 12.0 * min(left, tier) + 4.0 * max(left - tier, 0.0) - belt.cost * q
}

    Q     estimate half-width       summed
  420     17443.54      17.64     17440.64
  430     17598.94      19.50     17595.40
  440     17717.80      21.41     17713.30
  450     17799.34      23.33     17793.51
  460     17842.54      25.26     17835.67
  470     17847.14      27.18     17839.83
  480     17813.90      29.07     17806.45
  490     17743.39      30.91     17736.37
  500     17637.05      32.70     17630.75
best on the grid: Q = 470
best by the summed profit, one belt at a time: Q = 466, profit 17842.70; at 469 it is 17841.11

Validation. Every summed value lies inside its interval. Every estimate sits above its summed value, which is not a bias: the rows face the same demands and share one sampling error, so comparisons across rows are far sharper than one half-width suggests.

What it means. The best order is 466 against 469; keeping 469 costs $1.59. The rule barely moves the order. With the tier at 25 belts it bites: the best order falls to 460, and staying at 469 costs $14.69.

What We Did Today, and in This Chapter

The workbook is the chapter’s argument made visible: look, fit, aggregate, decide, with the checks wired into cells that read zero.

One cell priced a scrap offer: $366.06, of which $29.49 came from changing the order. The optimum is flat.

The software shares one cost function between an exact route and a sampled one, so each checks the other, and the sampled route keeps working where the formula stops.

The chapter in one line. A single period is defined by leftovers with no future value; the critical ratio is the probability of having too much that the costs make optimal; and the distribution carries more of the answer than the formula does.

For Next Time

Read before next session: the opening of Continuous and Periodic Review Systems, One Item, Watched Continuously and What to Measure.

Today’s material was Building These Models in a Worksheet and Designing the Software.

Next session:

• An item stocked permanently, demand arriving forever, a decision available at every instant
• The \((r, Q)\) policy, worked by hand on the storeroom’s transformer