Multi-Item Models in a Worksheet and in Code

Agenda

• Chapter4Models.xlsx: one sheet per model, and what each layout says about the model
• The constrained sheet: named ranges, a status cell, and a button you may trust
• The exchange curve, joint replenishment, and the distribution scan
• Why there is no serial worksheet
• The software: measures and constraints, interval rules, supply networks
• The chapter’s examples, run

Three Questions Every Sheet Answers

Every model in this chapter breaks the single-item layout: the decision is a vector, the items are tied by something that belongs to none of them, and two of the models are algorithms, not formulas. Each sheet answers three questions.

Is this a property of an item, or of the problem? The multiplier is one cell; a unit’s volume is a column. A multiplier repeated down a column computes the same numbers while asserting that each item has its own shadow price.

Is this difference data, or a different sheet? A budget and a space limit differ in one column of coefficients, so the constraint is implemented once.

When may a button be trusted? Goal Seek is safe on the constrained sheet because the resource function has exactly one root. Solver is not safe on the joint sheet, because the objective kinks wherever a multiplier steps, so that sheet plots the curve.

The Constrained Sheet

Named Ranges, and No Division

Every summary is a SUM of one column or a SUMPRODUCT of several. It never divides one range by another, which would evaluate zero over zero on every empty row of a hundred-row range. Where a ratio per item is needed, replenishments a year, the ratio gets its own column and the summary sums it.

The Item Block, and the Button

Column I is \(Q_j(\theta) = \sqrt{2k_j\lambda_j/(h_j + 2\theta a_j)}\): =SQRT(2*D43*B43/(F43+2*$B$14*G43)). Notice that only $B$14, the multiplier, carries dollar signs. Dollar signs are the spreadsheet’s only way to say what belongs to an item and what belongs to the problem. Column G, =IF($B$7="Budget",C43/2,E43), is the only thing distinguishing a budget from a space limit. The item block goes last, so adding an item is typing in the first empty row.

The Exchange Curve Sheet

Tracing the Curve

The curve is not a search: each row evaluates a closed form, and column D is the same product twelve times. Nine items sit in the item block against three on the constrained sheet, and nothing above it changed.

Joint Replenishment: One Continuous Decision

The Plot Is Mandatory Here

The objective is piecewise smooth: it kinks wherever a multiplier jumps. Watch the last multiplier step from 6 to 5 and the cost column turn a corner in the same row. A hill climber can halt at a kink, so plotting the objective is the only evidence that Solver stopped in the right basin.

The Distribution Scan

The scan reproduces the pinned-set table row for row: the candidate’s ratio beside the pooled ratio, and the verdict =IF(E32>H32,"add","stop"). The pooled columns are running totals, so there is no iteration, no Solver and no macro.

Why There Is No Serial Sheet

The distribution scan has a fixed number of steps known in advance, one per region, and each depends only on the one before. That is a table.

The serial partition has none of those properties. The number of blocks is not known until the walk is finished, and a merge changes blocks already built. The absence of a serial worksheet is an answer, not an omission.

A worksheet cannot bracket its own root or widen its own search; it cannot hold a structure whose size is not known until the computation finishes; and it cannot be audited at three thousand items.

What the Software Must Do

Notice that R3 to R5 name a limit on a measure, not a budget: an organization runs short of four different things.

The nouns that pass the warehouse test: SKU, portfolio, aggregate measure, constraint, replenishment plan, order family, interval rule, supply network. Shared resource fails: nobody has one. They have a capital account, a floor and a purchasing department, each a measure with a ceiling.

Measuring, and Limiting a Measure

Relevant cost is measured constantly and constrained never, so the interface splits: AggregateMeasure is what can be measured, and ConstrainableMeasure adds what a limit needs. The stationarity condition is \(-k_j\lambda_j/Q_j^{2} + h_j/2 + \theta\,\partial M/\partial Q_j = 0\), so a measure that reports its marginal can always be solved numerically:

interface ConstrainableMeasure : AggregateMeasure {
    fun marginalAt(sku: SKU, orderQuantity: Double): Double
    fun quantityAt(sku: SKU, shadowPrice: Double): Double { /* bisect the condition */ }
}

object AverageInvestment : ConstrainableMeasure {
    override fun marginalAt(sku: SKU, orderQuantity: Double): Double = sku.unitValue / 2.0
    override fun quantityAt(sku: SKU, shadowPrice: Double): Double =
        sqrt(2.0 * sku.orderCost * sku.demandRate / (sku.holdingRate + shadowPrice * sku.unitValue))
}

object ReplenishmentWorkload : ConstrainableMeasure {
    override fun marginalAt(sku: SKU, orderQuantity: Double): Double =
        -sku.demandRate / (orderQuantity * orderQuantity)
    override fun quantityAt(sku: SKU, shadowPrice: Double): Double =
        sqrt(2.0 * (sku.orderCost + shadowPrice) * sku.demandRate / sku.holdingRate)
}

AverageInvestment.quantityAt is \(Q_j(\theta)\) with \(a_j = c_j/2\), the only place the formula appears. A workload’s marginal is negative and depends on the quantity, which no single coefficient \(a_j\) can express.

One Search for Every Limit

class LagrangianSearch(private val precision: Double = 1.0E-12,
                       private val maximumIterations: Int = 400) : LotSizingMethod {
    override fun solve(portfolio: Portfolio, constraint: Constraint): ReplenishmentPlan {
        val measure = constraint.measure
        var lo = 0.0
        var hi = 1.0
        while (constraint.slackIn(portfolio.planAt(measure, hi)) < 0.0) {
            hi *= 2.0
            check(hi < 1.0E12) { "No price high enough to meet the ${measure.name} limit" }
        }
        repeat(maximumIterations) {
            val mid = 0.5 * (lo + hi)
            if (hi - lo < precision) return@repeat
            if (constraint.slackIn(portfolio.planAt(measure, mid)) < 0.0) lo = mid else hi = mid
        }
        return portfolio.planAt(measure, 0.5 * (lo + hi))
    }
}

Notice what the search does not know. It never writes \(Q_j(\theta)\), never names a budget, and never asks what the measure is. It knows only that raising the price lowers consumption, which the chapter proves. The first loop widens the bracket until the slack changes sign, the step Goal Seek does not take.

Interval Rules and Supply Networks

interface IntervalRule {
    val basePeriod: Double
    fun implementable(preferredInterval: Double): Double
    fun multiplierFor(preferredInterval: Double): Int =
        max(1, Math.round(implementable(preferredInterval) / basePeriod).toInt())
}

abstract class SupplyNetwork(val skus: List<SKU>, private val suppliedBy: Map<SKU, SKU>) {
    fun echelonRate(sku: SKU): Double =
        sku.holdingRate - (suppliedBy[sku]?.holdingRate ?: 0.0)
    abstract fun relaxedIntervals(): List<Double>
    fun plan(rule: IntervalRule): List<Double> = relaxedIntervals().map { rule.implementable(it) }
}

Coordinating a family and rounding an interval look like two jobs; they are one job with different answers, so one interface serves a SKU in a family, a block in a chain, and a location in a network. echelonRate gives \(h_i - h_{i+1}\) down a chain, \(h_i - h_0\) across a network, and a location with no supplier its own rate, from one expression.

The Chapter’s Examples, Run

val study = ConstrainedLotSizing(storeroom, Constraint(AverageInvestment, limit = 10000.0))
study.binds                                  // true
val held = study.solve()
held.shadowPrice                             //     0.146430
held.measuredBy(AverageInvestment)           // 10000.0000
held.measuredBy(RelevantCost)                //  7464.2984
study.priceOfTheConstraint()                 // difference 145.0375, 1.9816%
study.quantityRatios()                       // 0.819755 three times

Changing the constraint changes one word, Constraint(ReplenishmentWorkload, limit = 50.0):

  shadow price             64.3293 per order
  Item 1   k+theta 114.3293   held  676.2523   ratio 1.5121
  Item 2   k+theta  84.3293   held 1185.5328   ratio 2.0534
  Item 3   k+theta 144.3293   held  716.3594   ratio 1.3432
  orders per year          50.0000
  relevant cost          7621.2652
  the limit cost          805.9328  (11.8253%)

Under the budget every item shrank by 0.819755; here they grow by 1.51, 2.05 and 1.34. The code reproduces the difference between rescaling and reallocating, and no type in the program knows that the difference exists.

Families, Chains and Networks, Run

  independent              8422.39
  common cycle             6497.54   T = 0.14621 year (53.4 days)
  integer multiple         6402.06   T = 0.14214 year (51.882 days)   m = [1, 1, 2, 6]
  power of two             6403.34   T = 0.14192 year (51.800 days)   m = [1, 1, 2, 8]
  nesting costs             0.0200%
  the schedule nests    true

val chain = SerialChain(stages)
chain.blocks()                               // {1,2} at 0.5933, {3,4,5} at 0.6234
chain.costOf(chain.relaxedIntervals())       // 183.25
val weekly = PowerOfTwoMultiple(1.0 / 52.0)
chain.costOf(chain.plan(weekly))             // 183.31
val network = DistributionNetwork(warehouse, regions)
network.pinnedSet()                          // 6, 4, 1, 2
network.centralInterval()                    // 0.343049
network.plan(weekly)                         // every location at 0.307692

The same weekly object goes to a chain and to a network, and neither asks what it is. The last line of the family output checks the chapter’s argument instead of accepting it: nestsOn asks the answer whether it nests.

What We Did Today

Each sheet’s layout is a statement about the model: one cell for a fact about the problem, a column for a fact about an item or for the axis being varied.

A button may be trusted when the chapter proves it: Goal Seek on one root, Solver with the curve plotted beside it.

The serial partition has no sheet, because its number of steps is not known until it finishes.

The software limits a measure, not a budget, solves every limit with one search, and serves families, chains and networks with one interval rule.

Every number in the chapter comes from running it, and each is checked a second way.

For Next Time

Read before next session: Dynamic Lot Sizing, through What a Plan Costs.

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

Next session:

• What happens when the demand rate changes from period to period
• Why the economic order quantity has nothing to minimize, and what replaces it
• Three plans without any theory, costed by hand