Monte Carlo simulation in a web app, from the Data Tables in your workbook
A Monte Carlo simulation is only as useful as the number of people who can run it. If the model already uses Excel Data Tables, a published application executes them on the server for people who will never open the file, and returns the whole range rather than one answer.
Run one before you read any further
A term life insurance calculator with Data Tables behind it, built from an ordinary workbook and running right now.
Term life calculator with Data Tables
Enter the inputs, press the button, and the Data Tables in the workbook are executed on the server. The grid of results comes back into the page. Nothing is downloaded, and the model behind it is never exposed.
Monte Carlo simulation and sensitivity grids, one mechanism
Both are Excel Data Tables. What separates them is whether the inputs you sweep are deliberate or random.
Every combination of two inputs, at once
A one-variable table sweeps a single input down a column. A two-variable table sweeps one input down and another across, filling a grid with the result at every intersection.
This is what turns a quote into a rate card, a projection into a range, and a single answer into something a customer can actually read. Premium by age and coverage, margin by volume and discount, payment by rate and term.
Thousands of trials, then the distribution
The standard Excel technique is a Data Table whose input column is ignored, so every row recalculates the model with fresh random draws. Five thousand rows is five thousand trials.
Everything that needs is supported: RANDRANDBETWEENRANDARRAY NORM.INVNORM.S.INV to generate the draws, and AVERAGESTDEV PERCENTILECOUNTIFFREQUENCY to summarise them.
Why publishing it changes what it is worth
A simulation that only its author can run is an analysis. A simulation anyone can run is a product.
The recalculation happens on a server
Thousands of trials are exactly the workload a laptop struggles with and a spreadsheet handles slowly. Moving it server-side is not a compromise for the sake of the browser; customers report it running faster than the desktop version did.
Your assumptions stay behind the glass
Distributions, correlations, loadings and reserve factors are the model. Users see the range of outcomes and never the parameters that generated it.
A range persuades where a number does not
One figure invites argument about the assumption behind it. A distribution, or a grid across plausible inputs, answers the follow-up question before it is asked.
Four things to know before you build
Short list, because there is almost no configuration. These are the behaviours that occasionally surprise people.
Full behaviour in the Simulation event documentation.
Thousands of simulations, in production
Broker Village build software for investment product distribution, used by financial advisers and trusted by FINRA. Their Trade and Compliance Analyzer evaluates mutual fund and ETF trades on account value, fees and commissions, and returns a recommendation.
It is hard enough to model complex investment products and decisions. Embedding the models into a web application takes complexity to the next level. And until recently, being able to tailor your models to the unique requirements of each firm, and deploy complex mathematical changes in near-real-time, was a near impossibility. With SpreadsheetWeb we now can address needs that nobody else can meet.
Two details from that write-up are worth pulling out. The application runs the thousands of simulations on the server rather than on the adviser’s device, which they report as faster than running it in Excel on a desktop. And a major regulatory change that would previously have taken three to six months to reach the web version took days.
Where this sits next to the other iterative tools
Three different questions, three different events.
What happens across the range?
Simulation. Sweep the inputs, or draw them at random, and return the whole picture rather than one point on it. Every plan.
What is the best answer within the rules?
Solver optimises an objective across several variables subject to constraints. Private Cloud and server licences only.
What input gets me to this number?
Goal Seek adjusts one input until a target is met. Lighter than Solver, and also restricted to Private Cloud and server licences.
Upload the workbook with the Data Table in it
This one you really can try yourself. The free tier includes simulation, so you can publish a model and run it before speaking to anyone. If you would rather see it against something closer to your own, book a call.