Under the hood

How Quantik works

Quantik answers one question: given everything we are unsure about, how much could this project cost, and how confident can we be in a number? It does that by running your Excel estimate thousands of times, each time with a different, plausible set of values.

01 · The idea in one minute

A single-point estimate says "earthworks will cost $6.7M". Everyone knows it might be $6.2M or $7.6M. A risk register says "there is a 40% chance we hit rock". Adding the worst cases together gives a number nobody believes; adding the most likely values ignores the risk.

Monte Carlo simulation does something better. For each iteration it picks one value for every uncertain item from its range, decides whether each risk happens, and lets Excel recalculate the total. After 10,000 iterations you have 10,000 equally plausible totals. Sort them and you can read off:

• P50: the total that half the iterations came in under (the median);
• P90: the total that 90% of the iterations came in under;
• contingency: P50 or P90 less the base estimate.

02 · The simulation pipeline

Quantik drives Excel's own recalculation, so any formula you can write in Excel works in a model: lookups, IF statements, links between sheets. A run has six steps.

1. 1DiscoverA full recalculation in which every Quantik function registers its cell, name, arguments and correlation group.
2. 2SampleOne column of random numbers per input, drawn by Latin Hypercube in blocks of 1,000, each from the input's own stream.
3. 3CorrelateIman–Conover reorders the columns so related inputs move together, without disturbing each input's distribution.
4. 4IterateFor each iteration Excel recalculates; each input returns the value at its random number, using its arguments as they are at that moment.
5. 5RestoreThe workbook goes back to its static values (mean, most likely or P50, as you choose).
6. 6ReportStatistics, percentiles, precision, sensitivity, charts, the contingency sheet and a run log entry.

Before a run starts, Quantik refuses to go ahead if any distribution cell has invalid arguments (for example a minimum above the maximum). During the run, an iteration whose output is an Excel error is excluded and counted, never recorded as $0. Only the active workbook is simulated; Quantik functions in other open workbooks hold their static values.

03 · Latin Hypercube sampling

Plain Monte Carlo picks random numbers independently, so by chance some parts of a range are over-sampled and the tails are under-sampled. Latin Hypercube sampling (LHS) splits each input's 0–1 probability scale into equal strata and draws exactly one value in each stratum, then shuffles the order. Every part of every range is represented, so percentiles settle with fewer iterations.

Quantik draws the hypercube in blocks of 1,000 iterations. Each block is a complete Latin Hypercube on its own, which is what lets Quantik measure its own precision (batch means across blocks) and stop early without breaking the stratification. You can switch to plain Monte Carlo in Settings.

The random number generator is xoshiro256** (Blackman and Vigna), with a separate, non-overlapping stream for every input. Values come from each distribution's inverse cumulative distribution function, so a random number of 0.9 always gives that input's P90.

04 · Identity streams (common random numbers)

Each input's random numbers are tied to its identity, its name and function, not its position in the workbook. That has a practical consequence: adding, removing or switching off one risk does not change any other input's samples.

This is what makes the Revision Bridge, Scenarios and the correlation test show real differences rather than resampling noise. In testing, switching off one risk left the inherent cost and the opportunities exactly unchanged. Settings → Random streams → By position restores the earlier behaviour if you need it.

05 · Correlation in the run

Correlation is applied to the random numbers, before any value is calculated. Quantik uses the Iman–Conover method: it reorders each input's column of LHS draws so that their rank correlation matches what you asked for. Because it only reorders, every input keeps exactly its own distribution and every stratum is still sampled once.

• Within a group (for example all bridge rates), inputs share a correlation ρ_w, set with SYN.CORR("group", ρ) or the Correlate button.
• Between groups, a two-level factor model adds a correlation ρ_b (default 0.3). It grows linearly with the number of inputs, so it scales to large BOQs.
• Impossible combinations (for example ρ = ±1, or inconsistent pairs) are repaired to the nearest valid matrix, and the run says so.
• The achieved rank correlation, within and between groups, is reported after every run.

Why this matters so much is covered in Risk modelling: correlation.

06 · From iterations to results

Once the iterations are done, Quantik sorts each output and reports:

07 · Reproducible by design

A reviewer should be able to open the workbook, press Run and get the same answer. Quantik makes that possible in four ways:

• Fixed seed. The seed is always reported, and a fixed seed gives the same result every time.
• Settings in the workbook. Every setting is saved inside the workbook, so it travels with the file.
• Run log. Every reported run is appended to the Quantik Run Log sheet: time, user, version, iterations, sampling, streams, seed, mean, P50, P90 and P90 precision.
• Settings hash and input fingerprint. Two short hashes in the log change whenever a setting, a distribution, a range or a name changes. Equal values mean the same model and settings, so the same seed reproduces the run exactly.

08 · Speed and limits

Because every iteration is a real Excel recalculation, speed depends on the workbook. Measured speeds are 450–730 iterations per second on 29–56-input QRA workbooks and about 400 per second on the 137-input highway job, so 10,000 iterations take roughly 15–25 seconds. Close other workbooks while running: Excel recalculates every open workbook.