The four-block layout every worksheet in Chapter3Models.xlsx shares
The classic sheet, its units column, and the reorder point as one MOD call
The production sheet: the order model with a different holding rate
Discount schedules as computed columns, and an evaluator that is not an optimizer
What the worksheets do not do
The software: editable inputs and frozen results, one pipeline for six models, sealed types, and an answer that carries its reasoning
The Four-Block Layout
Block
Contents
Role
Inputs
\(\lambda\), \(c\), \(i\), \(k\), \(L\), and \(p\) where the model has one, each with its units beside it
the only cells anyone types into
Intermediate calculations
quantities derived from the inputs alone, such as \(h = ic\) and \(1 - \lambda/p\)
names the pieces the formulas are built from
Outputs for any \(Q\)
frequency, cycle time, average inventory, each cost, at a quantity that is typed in
the evaluator
Outputs for \(Q^{*}\)
the same rows recomputed at the optimum
the optimizer
Three rows join the two output blocks: the ratio, the difference and the relative error of the evaluated cost against the optimum. They are the sensitivity analysis built into the sheet: type any quantity and its penalty appears.
Every input carries a units label in the adjacent cell, and every quantity has a ROUNDUP companion, so the integer that would be ordered sits beside the number the formula produced.
The Classic EOQ Sheet
Set up in months: \(\lambda = 100\), \(c = \$8\), 25% a year entered as \(0.25/12\), \(k = \$20\), \(L = 0.25\) month.
Typing 155 into the evaluator returns a relative error of \(1.4\times 10^{-7}\); 150 or 160 return 0.052%; 200 returns 3.3%.
If 0.25 had been entered against a monthly demand, \(h\) would be twelve times too large and \(Q^{*}\) would be 44.7, wrong by a factor of \(\sqrt{12}\). Nothing in the arithmetic signals it. The units label is the only guard.
The Formula View, and One MOD
The reorder point is one cell:
= MOD(lead time, cycle time) * demand rate
MOD(L, T) returns \(L - \lfloor L/T \rfloor T\), the part of the lead time not covered by orders already outstanding. When \(L < T\) the floor is zero and it collapses to \(\lambda L\). One cell handles both cases with no IF.
Notice one detail only the formula view reveals: the evaluator’s order quantity is a reference to the optimizer’s \(Q^{*}\), not a typed number. The evaluator opens at the optimum, and typing over that one cell is the intended way to use it.
The Production Sheet: No New Formula
The sheet does not implement the production quantity at all. It builds
and applies the ordinary EOQ formulas to \(h'\): the production model is the order model with a different holding rate.
A guard sits beside the production rate:
= IF(demand rate >= production rate,
"Infeasible production rate",
"Production rate feasible")
At \(p = \lambda\), \(h' = 0\) and a division by zero; below it, a square root of a negative number. Neither failure explains itself, so the sheet explains it first.
The check: the machine runs \(0.0745/0.2981 = 0.25\) of the time, which is \(\lambda/p\), as it must be. That check catches an error in either the cycle length or the replenishment period.
Discount Schedules as Columns
The price schedule is a table, one row per level, and each step of the procedure is a computed column:
Column
Formula
Step
EOQ
\(\sqrt{2k\lambda/(ic_j)}\)
1, the candidate at each price
TC(EOQ)
\(c_j\lambda + \sqrt{2k\lambda ic_j}\)
its cost
Feasible?
IF(AND(lower <= EOQ, EOQ <= upper), "Yes", "No")
2, is the price available there
TC(Lower Limit)
\(c_j\lambda + k\lambda/q_j + ic_jq_j/2\)
3, the cost at the break point
Reading the All-Units Sheet
\(\lambda = 600\), \(k = \$8\), \(i = 0.20\):
Level
\(q_j\)
\(c_j\)
\(Q_j^{*}\)
Feasible?
\(\mathit{TC}(Q_j^{*})\)
\(\mathit{TC}(q_j)\)
1
0
$0.30
400.00
Yes
204.00
2
500
$0.29
406.84
No
198.10
3
1000
$0.28
414.04
No
200.80
Order 500. Level 3 is the instructive row: its unconstrained candidate would cost $191.19, lower than anything in the table, but 414 units cannot be bought at $0.28, and the 1,000 needed to reach that price cost more to hold than the price cut returns.
Stopping at the first feasible level would choose $204.00; taking the lowest number would choose a quantity that is not for sale. The columns enumerate the candidates and cannot miss one. A solver searches, and on a cost that drops at each break point it stops at whichever interval it started in.
The Incremental Sheet, and a Warning
The sheet builds the fixed part of the price recursively, without forming \(R_j\):
Two levels survive: 400 at $204.00 and 518.62 at $204.58. Order 400. The sheet has no candidate columns, so its evaluator prices whichever is typed in, and both look plausible.
What the Worksheets Do Not Do
They evaluate; they do not choose. Every sheet prices an entered quantity faithfully. Only the all-units sheet enumerates alternatives, and only because someone laid the enumeration out in columns.
They do not know their own assumptions. The production sheet guards \(p > \lambda\) and nothing else. Nothing checks that demand is constant, the lead time known, or backorders permitted.
They round after the fact. The ROUNDUP column shows the integer to be ordered, but the costs are computed at the unrounded quantity. That is negligible for the classic item, and not for an item sold only in cases.
Those three are reasons to write a program. They are not a design. The design is four decisions, each a question that returns in any model you write.
What Changes, and What Must Not
A worksheet is a thing you edit: the inputs must be editable, and the answer you already took down must stay where it was. Those pull in opposite directions, so the code uses three types.
CostParameters is the sheet: every property assignable, each validated on assignment, so no invalid parameter set ever exists.
ParameterSnapshot is the printout, unchangeable once taken.
CostParametersIfc is the read-only view every model depends on.
valanalysis= EconomicOrderQuantity.optimize(sheet)// sheet.orderCost is 20.0sheet.orderCost =40.0analysis.parameters.orderCost // still 20.0
When something must be editable and something must be stable, do not compromise between them on one type. Separate what can change from what has been recorded.
One Pipeline, Six Models
The six models agree on the geometry of the cycle, the cost assembly, the reorder point and the service measures. They disagree about one thing, the policy.
optimize and evaluate are final: a model may decide what to order, and may not decide what a cycle is, what it costs, or when to reorder.
The classical EOQ is the general model in a limit, but it is not a subclass of it. A subtype must be usable wherever its parent is, and the classical model has no backorder cost to be given. “B is a special case of A” in the mathematics is a different claim from “B can stand in for A” in code.
Sealed Types for Closed Sets
Replenishment is instantaneous or at a rate; shortages are forbidden or backordered; a schedule is flat, all-units or incremental. Each is a small closed set, written as a sealed hierarchy, and the special-cases table becomes code:
There is no else. Add a third kind of replenishment and the build stops here. And NotPermitted and Backordered(b) are different types, with b > 0 required, so the “\(b = 0\) forbids shortages” mistake cannot be written down.
An Answer That Carries Its Reasoning
A model asked to work outside its assumptions has three replies, not two:
Applying the order quantity to an item with a finite production rate is a deliberate approximation: allowed, with the discarded assumption riding on the result. Asking the planned backorder model for a policy when shortages are forbidden is refused.
QuantityChoice records how the quantity was reached: a closed form, a boundary with a reason, a ranked list of candidates, or a rounding with both neighbors evaluated.
The cycle’s identities are written into its constructor, so they cannot quietly fail:
checkClose(segments.total, length,"the segments sum to the cycle length")checkClose(maxOnHand + maxBackorder, policy.orderQuantity * survivingFraction,"the peaks account for the surviving part of the order")
Economic order quantity
Order quantity 154.9193 units (155 rounded up)
Cycle length 1.5492 month
Order frequency 0.6455 orders per month
Average on hand 77.4597 units
Reorder point 25.0000 units (25 rounded up)
Ordering cost 12.91 per month
Holding cost 12.91 per month
Relevant cost 25.82 per month
Purchase cost 800.00 per month
Total cost 825.82 per month
Every figure is the sheet’s. The units column has become a constructor argument, timeUnit, so an annual charge cannot be dropped silently beside a monthly demand. The rounded companions are the ROUNDUP column, and evaluate with penaltyAgainst are the evaluator and its three joining rows.
Discounts and Refusals in Code
level break price candidate feasible total cost chosen
1 0 0.30 400.0000 yes 204.0000
2 500 0.29 500.0000 yes 198.1000 <--
3 1000 0.28 1000.0000 yes 200.8000
order 500.0000 at a total cost of 198.1000
level break price candidate feasible total cost chosen
1 0 0.30 400.0000 yes 204.0000 <--
2 500 0.29 518.6189 yes 204.5799
order 400.0000 at a total cost of 204.0000
The candidate columns have become the candidate list the answer carries, and ranking the incremental levels is the operation itself: the 58 cents is not left to the reader to notice.
a zero backorder cost:
A backorder cost of zero makes shortages free rather than forbidden. Use ShortagePolicy.NotPermitted, which is the limit as the cost grows without bound. See @sec-eoq-nobackorders.
a rate below the demand rate:
The replenishment rate must exceed the demand rate, but 80.0 is not greater than 100.0
What We Did Today
Every worksheet has four blocks: inputs with units, intermediate calculations, an evaluator and an optimizer, joined by three sensitivity rows.
One MOD call is the whole reorder point, and the production sheet is the order model with \(h' = h(1 - \lambda/p)\).
Discount columns enumerate candidates; a solver searches, and an evaluator prices whatever it is given.
The code separates editable inputs from frozen results, runs six models through one pipeline, seals the closed sets so the special-cases table is checked by the compiler, and returns an answer that carries its reasoning and refuses what it cannot model.
For Next Time
Read before next session:When Items Stop Being Independent and A Constrained Lot Sizing Problem, in the multi-item chapter.
Today’s material was Building These Models in a Spreadsheet, Designing the Software and The Same Models in Code.
Next session:
When items share a scarce resource, and must be decided together
The Lagrange multiplier as a surcharge on holding, found by bisection
What a budget limit costs, and why it shrinks every item by the same factor
Every item next session is a classical EOQ. Bring the square-root formula.
