How Probistica works

How Probistica reads your Excel model, calculates trials in memory across processor cores, and keeps results repeatable.

What it does

Probistica is an Excel add-in for Monte Carlo simulation. You write normal formulas, but some cells hold uncertainty:

B2:  =SIM.NORMAL(1000, 150)          sales volume
B3:  =SIM.TRIANGULAR(20, 25, 35)     unit cost
B4:  50                              price
B5:  =B2*(B4-B3)                     margin
B6:  =SIM.OUTPUT("Profit")+IF(B5>0, B5*0.75, B5)

You mark an output with SIM.OUTPUT and click Run. The add-in plays the model many times. Each trial draws new values for B2 and B3 and records the profit. The report shows the mean, the spread, the percentiles, and the chance of a loss.

Why it can be fast

Many simulation tools ask Excel to recalculate the workbook for each trial. That costs time on every trial.

Probistica reads the model from Excel once. It turns each formula into a small tree of objects that compute the formula directly. Cells that do not depend on a random input are computed once, before the run. Each trial then computes only the cells that change. In the default in-memory engine mode, Excel does not recalculate each trial and multiple processor cores can run trials in parallel (Excel trial mode recalculates in Excel when needed, such as for per-trial macros).

Why correct results come first

A fast wrong answer is worse than no answer. Three rules protect you:

  1. Unsupported formulas are never guessed. In the in-memory engine mode, if a formula feeding an output uses a function the engine does not support, the run stops and names the cell and function. In Auto trial mode from the ribbon, Probistica asks whether to run the model in Excel trial mode instead (showing the formula count and estimated run time); Excel trial mode recalculates those formulas in Excel on each trial.
  2. Excel is the judge. Each rule in the engine was tested on reference models. Some Excel rules are surprising. For example, Excel treats 0.1*3 and 0.3 as equal, and it shows =0.1*3-0.3 as exactly 0. Probistica copies these rules.
  3. A failed run shows no statistics. If an output becomes an error in any trial, the run stops. The message names the cell and the trial.

Repeatable results

The same model, seed, and settings give the same results, bit for bit, on the supported runtime. The number of processor cores does not change the result. Each report records the settings that produced it.

What you see in Excel

  • A ribbon tab: Define Output, Run, Stop, Settings, Results, Issues, Charts, Compare. The charts pane shows a histogram, a tornado chart, or convergence charts, and can pop out into its own window.
  • Distribution cells show the mean of their distribution. They do not change when you press F9. The simulation reads the formulas, not these display values.
  • Results go to a new sheet, such as Probistica Results 1. The add-in never overwrites a sheet that it did not create.
  • Settings are stored in the workbook.

Words

  • Trial: one play of the model.
  • Input or sample point: a SIM.* distribution call in a formula.
  • Output: a cell marked with SIM.OUTPUT.
  • Seed: the start number of the random generator.
  • Static cell: a cell that does not depend on any input. Probistica computes it once.
  • Latin Hypercube sampling (LHS): a method that spreads the draws of each input evenly over its range. For the example, at the same trial count, the typical error of the estimated mean is about 7 times smaller than with plain Monte Carlo.