Dynamic Lot Sizing in a Worksheet and in Code

Agenda

• The five sheets of Chapter5Models.xlsx, and the cells that carry the mathematics
• The window costs, the recursion and the traceback laid out with nothing collapsed
• The three rules side by side, and the mixed integer program for Solver
• What the worksheet cannot do
• The software: a schedule that owns the window cost, one procedure for three rules, and the rule that does not fit
• The rolling horizon, the class that exists because a worksheet cannot do it

The Five Sheets

The periods run down the rows of one block, and every sheet reads that block by name. Blue cells are inputs, gray cells are computed, and yellow marks a cell a method picked: a period an order starts in, or a period a stopping rule chose.

Data: Three Inputs and Everything Else

Data: the Requirements Schedule

Column B is the only blue block. Columns C, D and E point at the input cells above, so a constant-cost problem is typed once, and a period-varying one needs no second workbook: type $54 over one cell of the unit cost column and every window cost in the workbook accounts for the price rise.

Notice column G, empty except in month 5, where 420 belts exceed \(k/h = 300\) and the forced-order test fires. Columns F and G are tinted, because they are derived and not data.

Plans: the Ledger, Made General

Four plans side by side, each an order column you type into and an ending-inventory column computing = previous end + order - requirement. The three shipped plans reproduce the three plans without theory exactly. Notice the purchase cost: $92,000 under all four plans, the purchase term made visible.

WagnerWhitin: the Answer

WagnerWhitin: the Window Triangle

This is the six-month window table extended to twelve; read row 1 across and the first six entries are that table. Notice columns 8 and 9: equal in every row, because month 9 requires nothing, so a window reaching it costs what one ending at month 8 costs.

Notice also how fast the upper right grows. Covering the whole year from month 1 costs $10,420 against $300 for a single month, and that ratio is the whole reason an optimal plan orders more than once.

WagnerWhitin: the Recursion and the Traceback

The candidate block holds \(V(t-1) + w(t,u)\) for every \(t\) and \(u\), with nothing collapsed: \(V(u)\) is the MIN down column \(u\) and \(S(u)\) is the MATCH of that minimum. Notice \(V(8) = V(9) = \$1{,}700\) with \(S = 6\) at both. The yellow row is the traceback, walked backward by INDEX on the \(S\) row, and read right to left it orders in months 1, 4, 5, 6, 10 and 12.

WagnerWhitin: the Plan

Heuristics: Silver-Meal’s Scores and Its Stopping Test

Every score is a window cost divided by the length of its window. Row 5 is where the rule loses its money: $300.00, $280.00, $266.67, $230.00, $184.00, and only then a rise. Beside the scores, a triangle of zeros and ones asks “does the score rise at the next period?”, and MATCH(1, ...) finds the stop. Notice the yellow column: 2, 3, 4, 4, then 9 four times over.

Heuristics: Least Unit Cost and Part-Period Balancing

Heuristics: the Chains and the Plans

Each chain starts in month 1, and each order after it starts where the previous one stopped. The rules order in months 1, 3, 5, 10, 12; in 1, 4, 6, 8, 12; and in 1, 4, 6, 10, 12. All three place five orders and no two agree on where. The totals are $1,500 of setup in every case against carrying of $1,160, $1,540 and $1,100.

Solver: the Mixed Integer Program

What the Worksheet Cannot Do

The rolling horizon is not in the workbook, and that is not an oversight.

The rolling-horizon table is the cost of a sequence of twelve plans, eleven of which were computed, discarded and never executed. The study of 2,000 random schedules needs each solved twelve times under two rules:

\[ 2{,}000 \times 12 \times 2 = 48{,}000 \text{ solves}. \]

A worksheet asked to do that stops being a thing anyone can read.

What the Software Must Do, and Its Nouns

Window and order are the instructive rejections. A window has no behavior and no life of its own: \(w(t,u)\) is a function of the schedule and two integers. An order is \(Q_t\), a number in a position, and every question about it is a question about a plan.

What Each Thing Knows and Does

The schedule owns the window cost. Every rule prices the same windows, so computing them in one place means the heuristics and the algorithm are compared on identical numbers.

Notice what the plan does not store: its ending inventory. The balance equation determines it from the orders, so storing both would allow a plan whose inventory no sequence of orders produces. And the plan refuses a shortage in its constructor, so every plan anywhere in the program is feasible.

The Schedule Prices Every Window

fun windowCost(from: Int, through: Int): Double {
    requireWindow(from, through)
    val key = (from - 1) * horizon + (through - 1)
    cached[key]?.let { return it }
    val value = k[from - 1] + (from..through).sumOf { unitCostHeld(from, it) * d[it - 1] }
    cached[key] = value
    return value
}

fun relevantWindowCost(from: Int, through: Int): Double =
    orderCostIn(from) + carryingCostOver(from, through)

windowCost is \(w(t,u) = k_t + \sum_s d_s\,\hat{c}(t,s)\), with unitCostHeld as \(\hat{c}\), memoized because every rule asks for the same \(N(N+1)/2\) values.

Three Rules Are One Rule

If the three heuristics are one procedure, the code should contain it once:

abstract class WindowRule(override val name: String) : LotSizingRule {
    protected abstract fun scoreOf(schedule: RequirementsSchedule, from: Int, through: Int): Double?

    final override fun plan(schedule: RequirementsSchedule): LotSizingPlan {
        val orders = mutableListOf<Int>()
        var from = 1
        while (from <= schedule.horizon) {
            orders.add(from)
            from = 1 + lastPeriodCovered(schedule, from)
        }
        return schedule.planFrom(orders)
    }

    fun lastPeriodCovered(schedule: RequirementsSchedule, from: Int): Int {
        var best = from
        var previous: Double? = null
        for (through in from..schedule.horizon) {
            val score = scoreOf(schedule, from, through) ?: continue
            if (previous != null && score > previous + TOLERANCE) break
            previous = score
            best = through
        }
        return best
    }
}

lastPeriodCovered is the walk: extend, score, stop at the first rise, and keep the period before it. A null score is skipped, not compared.

Two Rules in Three Lines Each

object SilverMeal : WindowRule("Silver-Meal") {
    override fun scoreOf(schedule: RequirementsSchedule, from: Int, through: Int): Double =
        schedule.relevantWindowCost(from, through) / (through - from + 1)
}

object LeastUnitCost : WindowRule("Least unit cost") {
    override fun scoreOf(schedule: RequirementsSchedule, from: Int, through: Int): Double? {
        val units = schedule.requirementOver(from, through)
        return if (units <= 0.0) null else schedule.relevantWindowCost(from, through) / units
    }
}

That is the rule table, executable. The nullable return is least unit cost’s one irregularity: a window covering no units has no cost per unit, so the rule steps over it. Month 9 of the belt is such a period, and it is the cell the worksheet had to guard.

The Rule That Does Not Fit

object PartPeriodBalancing : LotSizingRule {
    fun lastPeriodCovered(schedule: RequirementsSchedule, from: Int): Int {
        val setup = schedule.orderCostIn(from)
        var best = from
        for (through in from + 1..schedule.horizon) {
            val carried = schedule.carryingCostOver(from, through)
            if (carried > setup) {
                val below = schedule.carryingCostOver(from, through - 1)
                return if (abs(carried - setup) < abs(below - setup)) through else through - 1
            }
            best = through
        }
        return best
    }
}

The Algorithm Is Not a Heuristic

object WagnerWhitin : LotSizingRule {
    fun solve(schedule: RequirementsSchedule): Solution {
        val n = schedule.horizon
        val value = DoubleArray(n + 1)
        val from = IntArray(n + 1)
        for (t in 1..n) {
            var best = Double.MAX_VALUE
            var argument = 1
            for (s in 1..t) {
                val candidate = value[s - 1] + schedule.windowCost(s, t)
                if (candidate < best - TOLERANCE) {
                    best = candidate
                    argument = s
                }
            }
            value[t] = best
            from[t] = argument
        }
        ...
    }
}

value is \(V\) and from is \(S\); the strict comparison keeps the earliest period on a tie, the same one MATCH finds on the worksheet. solve returns \(V\) and \(S\) as well as the plan, because a reader checking the traceback needs the pointers.

How the Objects Solve It

Notice that the rule computes no cost of its own. It decides which costs to ask for. That is what lets the comparison table rank the rules on their decisions and not on their arithmetic.

Rolling the Horizon

fun realizedPlan(rule: LotSizingRule): LotSizingPlan {
    val n = actual.horizon
    val placed = DoubleArray(n)
    var period = 1
    while (period <= n) {
        val onHand = carriedInto(placed, period)
        val committedTo = minOf(period + freeze - 1, n)
        val net = netRequirements(period, minOf(period + planningHorizon - 1, n), onHand)
        if (net != null) {
            val plan = rule.plan(net.window)
            for (t in period..committedTo) {
                val offset = t - net.firstPlanned + 1
                if (offset in 1..net.window.horizon) placed[t - 1] = plan.orderIn(offset)
            }
        }
        period = committedTo + 1
    }
    return LotSizingPlan(actual, placed.toList())
}

The subtle part is netRequirements. The planner may be holding stock from an earlier order, and a schedule starts empty, so the stock on hand is netted against the nearest requirements, as a requirements record nets it. Getting this wrong produces a plan that orders on top of stock it already has.

Solving the Chapter’s Examples

val belt = RequirementsSchedule.constantCosts(
    label = "Mower deck belt",
    requirements = listOf(40.0, 60.0, 120.0, 300.0, 420.0, 260.0,
                          120.0, 40.0, 0.0, 80.0, 180.0, 220.0),
    orderCost = 300.0, unitCost = 50.0, carryingCharge = 0.02,
)
println(belt.variabilityCoefficient)   // 0.6191
println(belt.forcedOrderPeriod())      // 5

val optimum = WagnerWhitin.plan(belt)
for (r in listOf(LotForLot, AdjustedEconomicOrderQuantity(), PeriodicOrderQuantity(),
                 LeastUnitCost, PartPeriodBalancing, SilverMeal, WagnerWhitin)) {
    val p = r.plan(belt)
    println("%-32s %6d %9.0f %9.0f %9.0f %8.2f%%".format(
        r.name, p.orderCount, p.setupCost, p.carryingCost, p.relevantCost,
        p.penaltyAgainst(optimum).relativeError * 100.0))
}

for (window in 2..6) {
    val sm = RollingHorizon(belt, window, freeze = 1).realizedPlan(SilverMeal)
    val ww = RollingHorizon(belt, window, freeze = 1).realizedPlan(WagnerWhitin)
    println("%d %.0f %.0f".format(window, sm.relevantCost, ww.relevantCost))
}

The comparison of every method is a loop over rules, and that is what the design was for. The rolling-horizon table is two lines per row.

What We Did Today

The workbook lays every method out with nothing collapsed: the window triangle, the candidate block, the traceback, and the three rules’ scores, each checked against the chapter.

The schedule owns the window cost, so every rule is compared on identical numbers, and the heuristics divide the relevant window cost.

Silver-Meal and least unit cost are one procedure, a template with a score; part-period balancing can step back and implements the interface directly, in code and in the worksheet both.

Wagner-Whitin is not a heuristic, and returns \(V\) and \(S\) as well as the plan.

The rolling horizon is a class because a worksheet holds one instance of a model and cannot run 48,000.

For Next Time

Read before next session: Demand You Should Not Forecast, The Bill of Material and The MRP Record, in the requirements planning chapter.

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

Next session:

• Where requirements come from when they are calculated rather than forecast
• The bill of material, and why an item needs a low-level code
• The six-row MRP record, worked by hand