Quarry count, haul-cycle cost & total supply optimisation
A worked example of the Directed Acyclic Graph, AST formula engine and topological recalculation described in the Heavy Civil white paper. The hero resource is the total delivered-aggregate cost for a 160 km highway corridor; every figure is driven by named variables, a published piecewise rate function, and live resource references.
This page translates Case Study A of the Heavy Civil white paper into the same worksheet style used for the commercial-building Global Variables examples. A 160 km highway corridor (case-study framing) requires 1,200,000 tonnes of crushed aggregate. Each additional quarry reduces average haul distance but incurs establishment, royalty and mobilisation costs. The question the model answers is not “what is the aggregate supply rate?” but “how many quarries produce the lowest total delivered cost once both material-plus-haul and fixed establishment costs are accounted for?”
Assumes unconstrained quarry centroids evenly spaced along the corridor, 100% specification-compliant material at every source, and unlimited crushing / loading capacity. No site-access restrictions, weather downtime, or competing demand from other projects are modelled in this baseline.
Live audit: number of quarries (N)
Drag the slider to force any discrete N. All downstream nodes (average haul, piecewise rate band, establishment cost, material+haul and total) recalculate instantly through the same dependency graph the engine uses in production.
The hero resource: Quarry Sourcing Cost Optimiser
Everything else exists to price this one decision. The model evaluates six discrete scenarios (1–6 quarries). Average haul distance is governed by the corridor-length formula; effective unit rate is a published piecewise function of that distance. Changing any upstream variable (quarry establishment cost, diesel price, truck fleet productivity, total tonnage) recalculates every scenario through the dependency graph.
The chain: decision → haul formula → scenario table
Reading top to bottom: each card is a full resource. The line joining cards is a resource reference, not a copied number. Edit the diesel price or the quarry establishment cost and the entire trade-off curve moves.
Zooming into the haul logic: distance → published rate function
The white-paper average-haul formula and the published piecewise rate function are expressed directly in the worksheet language:
D_haul(N) = L_ROAD_GV / (4 × N)
HAUL_COMP = RATE_FN(D_haul) × (DIESEL_GV / DIESEL_BASE_GV)
RATE_DEL = MAT_BASE + HAUL_COMP
COMB(N) = N · ESTAB_COST_GV + TONNES_GV · RATE_DEL
| Average Haul Distance | Haul component ($/t) | Approx. $/t/km |
|---|---|---|
| 0 to under 2 km | $1.80 | $0.90–∞ |
| 2 to under 5 km | $2.20 | $0.44–0.55 |
| 5 to under 10 km | $2.40 | $0.24–0.30 |
| 10 to under 15 km | $2.68 | $0.18–0.27 |
| 15 to under 25 km | $4.50 | $0.18–0.30 |
| 25 to under 40 km | $8.40 | $0.21–0.34 |
| 40 to under 60 km | $14.50 | $0.24–0.36 |
A linear approximation inside the 5–15 km band can be used for continuous audit: RATE_FN ≈ 2.12 + 0.056 × D_HAUL. The stepped table above remains the authoritative source used by the model.
Scenario results (all formula-driven)
Every column is an evaluated expression. Changing the quarry establishment cost (ESTAB_COST_GV) or the diesel-driven haul component recalculates the entire table through the dependency graph. The row corresponding to the current slider value is highlighted.
| Quarries (N) | Avg Haul (km) | Effective Rate ($/t) | Material + Haul | Establishment | Total Cost |
|---|---|---|---|---|---|
| 1 | 40.0 | 30.50 | $36,600,000 | $1,850,000 | $38,450,000 |
| 2 | 20.0 | 20.50 | $24,600,000 | $3,700,000 | $28,300,000 |
| 3 (optimum) | 13.3 | 18.68 | $22,416,000 | $5,550,000 | $27,966,000 |
| 4 | 10.0 | 18.68 | $22,416,000 | $7,400,000 | $29,816,000 |
| 5 | 8.0 | 18.40 | $22,080,000 | $9,250,000 | $31,330,000 |
| 6 | 6.7 | 18.40 | $22,080,000 | $11,100,000 | $33,180,000 |
DAG Trade-off: Establishment vs. Haul Cost
The DAG evaluates COMB(N) = N·ESTAB_COST_GV + TONNES_GV·RATE_DEL(N) for N=1..6. Adding quarries raises fixed establishment (rust) but shortens average haul, cutting the variable haul component (navy). The combined cost is U-shaped — optimum at N=3 ($27.966 M).
D_haul = L_ROAD / (4·N)N=1–3: Haul distance dominates — total cost falls rapidly as average haul drops from 40 km to 13 km.
N=4–6: Establishment cost becomes binding — curve rises again.
Optimum: FOR(1,6) evaluates COMB(N) and keeps the minimum. Changing DIESEL_GV or a single Q1_CHAINAGE_GV moves the entire U-curve.
The full build-up, resource by resource
Each tab is one exported worksheet. Highlighted rows are live resource references. Variable codes in navy are local; codes ending in _GV are global and shared across the entire estimate.
Sized to a 160 km corridor requiring 1.2 Mt of aggregate. The optimum is selected by evaluating the total-cost expression for each discrete N and retaining the minimum.
| Description | Resource rate | Unit | Variable | Equation | Value | Amount |
|---|---|---|---|---|---|---|
| Decision variables | ||||||
| Number of quarries | – | No. | N | 3 | 3.00 | 0.00 |
| Quarry establishment cost | 1850000 | $/quarry | ESTAB_COST_GV | [ESTAB_COST_GV] | 1850000 | 0.00 |
| Establishment cost of N quarries | – | $ | ESTAB | [N]*[ESTAB_COST_GV] | 5550000 | 5,550,000 |
| Delivered material cost | ||||||
| Effective delivered rate | – | $/t | RATE_DEL | Haul Cycle & Rate Engine | 18.68 | 0.00 |
| Total aggregate tonnage | – | t | TONNES | [TONNES_GV] | 1200000 | 0.00 |
| Material + haul cost | – | $ | MATHAUL | [TONNES]*[RATE_DEL] | 22416000 | 22,416,000 |
| Total project cost (establishment + material/haul) | $27,966,000 | |||||
Reading the variable codes
Each bracketed code is a live pointer. Changing a global variable once updates every dependent node in topological order; the estimate never contains orphaned or stale values.
NNumber of active quarries for the current scenario (driven by the live slider above).D_HAULAverage one-way haul distance = corridor length / (4 × N).RATE_DELEffective delivered unit rate after the published piecewise function and diesel index are applied.L_ROAD_GVTotal corridor length. Changing this scales average haul for every N automatically.TONNES_GVDesign aggregate quantity. Scales the variable (material + haul) component of total cost.DIESEL_GVFuel price. Indexed into HAUL_COMP so a change scales the haul component of every rate.ESTAB / MATHAULIntermediate results: fixed establishment cost of N quarries and total material-plus-haul cost.RATE_FN(D)Published stepped function that returns the appropriate haul component ($/t) for a given average haul distance.MAT_BASEBase material price ex-quarry ($/t). Project-scoped constant used in RATE_DEL.T_FIXEDFixed portion of the haul cycle (load + dump + delay, minutes). Used only in the cycle-time illustration.DAGDirected Acyclic Graph — the dependency network of named variables and formula nodes. Recalculation walks the graph in topological order so no value is ever stale.argmin / FORThe engine evaluates COMB(N) for each discrete N and retains the minimum (the true optimum). Equivalent to a one-pass scan over the scenario set.TRUCK_HIRE_RATE_GV / PAYLOAD_GVFleet inputs used to build the bottom-up trucking cost per tonne. Feeds RATE_BOTTOMUP for comparison; not consumed by the published RATE_DEL.RATE_BOTTOMUPDelivered rate built from base material plus trucking cost per tonne. Shown alongside the published RATE_DEL for comparison; not summed into the total.Dynamic recalculation when a quarry location is finalised
Part-way through tender the geotechnical team confirms the actual location of Quarry 1. In a traditional spreadsheet the estimator must re-derive distance, cycle time, fleet size and cost extensions by hand. In this model only one variable is edited:
Q1_CHAINAGE_GV → 40.0 km becomes 52.4 km
| Metric | Baseline (assumed centroids) | Revised (surveyed chainage) |
|---|---|---|
| Optimal quarry count | 3 quarries | 3 quarries |
| Avg haul at optimum | 13.3 km | 21.9 km (weighted) |
| Effective rate at optimum | $18.68 /t | $20.50 /t |
| Total project cost | $27.966 M | $30.150 M |
Because the variable feeds the distance node, which feeds the cycle-time and rate nodes, which feed the scenario table and the total-cost expression, the entire trade-off recalculates in a single topological pass. The new optimum and the revised total cost appear without any other cell being touched.
Why this matters beyond one corridor
The same pattern — named variables, reusable formula nodes, piecewise / MIN / MAX bottleneck functions, and a maintained dependency graph — scales from a single sourcing decision to an entire heavy-civil estimate containing hundreds of assemblies (pavement, earthworks, drainage, structures). A change in diesel price, a locked-in quarry GPS coordinate, or a revised design tonnage is written once at global or project scope and cascades automatically. That is the structural difference between a pricing database and a computational estimating engine.
Figures shown are illustrative of the calculation structure described in the Heavy Civil technical white paper and are not a rate schedule for any live project. Companion to the Heavy Civil Estimating white paper and the Global Variables methodology series.