Simulation & Monte Carlo Risk Analysis

Model uncertainty with Latin Hypercube Sampling (LHS), Beta-PERT distributions, Value at Risk (VaR), Expected Shortfall, and sensitivity tornado charts in Excel.

Single-point spreadsheet forecasts hide the probability and severity of adverse outcomes. Whether forecasting new product profitability, capital expenditure budgets, or supply chain lead times, business assumptions are uncertain.

Boardflare embeds scientific Python (numpy, scipy.stats, scipy.stats.qmc) directly within Excel to run full Monte Carlo risk simulations on the client side. Interactive models execute 5,000 to 20,000 trials in under two seconds across Windows, Mac, and Excel Online with no cloud data transmission.

Mathematical Foundations & Methods

Simulation templates replace static cell values with probability distributions, propagate uncertainty across complex business relationships, and quantify downside risk:

• Latin Hypercube Sampling (LHS): Drastically reduces sampling variance compared to raw pseudo-random draws. By stratifying the cumulative probability domain into \(N\) equal intervals per input, 5,000 LHS trials achieve the quantile stability of 20,000 standard trials (McKay, Beckman, & Conover, 1979).
• Core Parametric Distributions:
  • Normal & Lognormal: For additive market drivers and multiplicative growth processes.
  • Triangular & Beta-PERT: 3-point estimates (Min, Most Likely, Max). Beta-PERT applies \(4\times\) weighting to the mode with smooth asymptotic tails, avoiding the corner bias of triangular models (Malcolm et al., 1959).
  • Uniform, Discrete, and Fixed: For bounded uncertainty and discrete decision scenarios.
• Iman-Conover Rank Correlation: Couples dependent inputs (e.g., unit sales and selling price) using Cholesky-transformed normal scores without distorting specified marginal distributions (Iman & Conover, 1982).
• Tail Risk Measures:
  • Value at Risk (\(\text{VaR}_\alpha\)): Quantifies threshold downside risk at given confidence levels (e.g., 5% or 10%).
  • Conditional Value at Risk (\(\text{CVaR}_\alpha\) / Expected Shortfall): Measures the average severity of outcomes in the worst \(\alpha\) tail.
  • Target Breach Probabilities: Explicit calculation of \(P(\text{Profit} < \text{Target})\) or \(P(\text{Cost} > \text{Budget})\).
• Tornado Sensitivity Analytics: Multivariable rank regression and Spearman rank correlation \(\rho_s(X_i, Y)\) rank critical drivers and variance contributions to identify the highest-leverage risks.

Free Excel Alternative Comparison

Feature	Excel Formulas (RAND(), Data Tables)	Boardflare Simulation Templates
Recalculation behavior	Volatile; spins on every keystroke	Deterministic; recalculates only when inputs change
Sampling efficiency	Pseudo-random; requires >20,000 trials	Latin Hypercube Sampling (LHS); stable at 5,000 trials
Input correlation	None without custom complex copula math	Built-in Iman-Conover rank correlation induction
Distribution support	Limited native inverse functions	Normal, Lognormal, Uniform, Triangular, Beta-PERT, Discrete
Sensitivity analysis	Manual single-variable sensitivity tables	Automated Spearman correlation & variance-share tornado charts
Web compatibility	Data Tables freeze and fail in Excel Online	Universal client-side execution via WebAssembly/Pyodide
Analytical validation	None	Built-in closed-form benchmark validation sheet

Available Templates