Share with your friends


Analytics Magazine

Monte Carlo for the masses

Free tools from automate the data table process for Excel users.

Sam SavageBy Sam Savage

Monte Carlo simulation has been used to model uncertainties since the Manhattan atomic bomb project by blasting randomly generated inputs through mathematical models. Monte Carlo spreadsheet add-ins for personal computers have been available since the mid-1980s, however, it has not been a killer app because it takes specialized training to generate the required random inputs. Actually, a great invention of the 19th century, the light bulb, was never widely adopted because it took specialized training to generate the required electricity (just kidding). Thomas Edison subsequently proposed that experts with generators provide electricity for non-experts with light bulbs, and the result was the power-distribution network.

The discipline of probability management was formalized in 2006 to do for probability distributions what Edison had done for electricity. That is, enable experts with generators to provide random inputs for non-experts with business problems, creating a distribution-distribution network [1, 2]. Instead of electricity, this power grid of probability carries stochastic information packets (SIPs), arrays of Monte Carlo or historical realizations of uncertainties. Performing vector calculations with these arrays is known as SIPmath. A number of breakthroughs over the past decade have led to a tipping point, and the Gartner 2016 Hype Cycle for Data Science recently recognized probability management as “transformational.”

Free SIPmath Modeler Tools

Free SIPmath Modeler ToolsThe free SIPmath Modeler Tools from nonprofit Probability help build blazingly fast interactive Monte Carlo models that run in native Excel. Based on the Data Table command, these models do not require macros or add-ins. They may be shared with hundreds of millions of spreadsheet users and linked with simulations in other platforms to aggregate risk across the enterprise. They are available on the Tools page of the website, along with full documentation and tutorial videos. The Models page contains numerous ready to run examples.


The Data Table, the Cold Fusion of Simulation

One of the most powerful commands of the electronic spreadsheet is the Data Table command, which dates back to Lotus 1-2-3. It “vectorizes” the spreadsheet, running an array of input data through a particular cell, while tracking the results of one or more output cells. This makes it possible, for example, to run several different discount rates through a financial model and track the resulting net present value for each rate. In the past few years, the data table command in Excel (found on the data ribbon under what-if analysis) has become powerful enough to instantly perform SIPmath with 10,000 trials per keystroke, as discussed in Analytics magazine in 2012 [3]. This “gamification” of Monte Carlo provides a more intuitive experience than most commercial packages, which still require a run command and short wait for results between model edits.

In 2014, nonprofit released the open SIPmath™ 2.0 Standard, which allows SIP libraries to be stored in Excel, XML and CSV format [4]. The output SIPs of a Monte Carlo on any platform may be used as actionable input data by any other Monte Carlo. Furthermore, if the statistical dependence between SIPs is maintained (that is, if they come from a stochastic library unit with relationships preserved, or SLURP), then the SIP of a sum of variables is the sum of the SIPs, that is, they are additive [5]. This allows simulations to be aggregated across the enterprise; for example, to roll up operational risk. SIPs are auditable because they consist of data with provenance. SIPmath may be performed in virtually any computing environment that handles arrays, including, in theory, 10,000 people with abacuses. Now that’s agnostic.

The Goose that Lays Golden Eggs

Figure 1.

Figure 1.

Because the data table is tedious to create, developed a set of Excel tools to automate the process, as well as import SIPs generated in other analytical environments. This creates dashboards that do not require the tools to run, and therefore may be shared with any Excel user. For example, a risk-based budgeting model is currently being tested by the city of Redmond, Wash. [6] (see sidebar). It assesses the risk of line item shortfall resulting from uncertain tax revenues.

John von Neumann, generally acknowledged as the greatest mathematician of the 20th century and inventor of the modern computer (which launched Monte Carlo simulation), once said, “Anyone who attempts to generate random numbers by deterministic means is, of course, living in a state of sin.” He correctly predicted the future demise of many promising random number generators. When Doug Hubbard, author of the book, “How to Measure Anything” and founder of Hubbard Decision Research, started experimenting with SIPmath in Excel, he wanted auditable results without being forced to use external SIPs as data. Since the RAND formula is not seed-able, he plunged into the shark-infested waters of random number generation himself. The result is a family of random access pseudo random number generators that can be written as native Excel formulas into a single cell, and which re-compute with each simulation trial.

SIPmath Risk-Based Budget Calculator

The financial officers of Redmond, Wash., have been experimenting with a SIPmath Risk-Based Budget Calculator in Excel. Given an uncertain tax revenue forecast, it instantly calculates the chances of meeting prioritized budget items as follows.
Figure 1: The financial officer enters past forecasts and actuals along with the current forecast. A regression of actuals against forecasts is instantly run with the Excel LINEST formula, including an estimate of the standard error. This results in a SIP of simulated forecast trials with a graphical representation of distribution and prediction interval.

Figure 1: The financial officer enters past forecasts and actuals along with the current forecast. A regression of actuals against forecasts is instantly run with the Excel LINEST formula, including an estimate of the standard error. This results in a SIP of simulated forecast trials with a graphical representation of distribution and prediction interval.

Figure 2: As budget line items are entered or edited on the left the chance of shortfall is instantly calculated.

The Hubbard Decision Research (HDR) generators allow the 3.0 SIPmath tools to generate numerous seed-able probability distributions, including correlated variables. Dave Empey, director of software development at, even crafted Poisson random variables with native Excel formulas, as described on the nonprofit’s website.

Caveat Gentor (Let he who generates random numbers beware)

Although Hubbard’s current generators have passed the basic random number tests, he is counting on the wisdom of crowds to come up with even better open Excel-based generators, and is heading up an initiative in this area at Furthermore, the 3.0 tools allow for SIPs of externally generated random numbers from any source. There are built in commands for creating SIP libraries directly from existing models in Palisade’s @Risk or Oracle Crystal Ball, allowing these popular Monte Carlo packages to be leveraged to impact much larger audiences. Still not satisfied? Read on.

In 1824, the French scientist Carnot formulated the famous 2nd Law of Thermodynamics, which states in effect that you cannot extract useful energy from the random motion of air molecules. was founded in 1998 by Mads Haahr, a computer scientist at Trinity College in Dublin, Ireland, to generate numbers from exactly this source of randomness – atmospheric noise. They now offer random number downloads in SIPmath format that can be accessed by the 3.0 Tools.

Figure 2.

Figure 2.

By the way, helps support its operations by selling random numbers to power lotteries, sweepstakes and online games. If they use that money to buy gasoline for their car, they have not only created repeatable, truly random numbers, but have also violated the spirit, if not the letter, of the 2nd law of thermodynamics. Take that, von Neumann and Carnot!

Sam Savage ( is the author of “The Flaw of Averages: Why We Underestimate Risk in the Face of Uncertainty” (John Wiley & Sons, 2009, 2012), the book that started the Probability Management revolution. He is executive director of, a 501(c)(3) nonprofit that has been cited in the MIT’s Sloan Management Review for “improving communication of uncertainty.” He is also a consulting professor at Stanford University, and a fellow of the Judge Business School at Cambridge University. Savage is the inventor of the stochastic information packet (SIP), a data structure that lets simulations communicate with each other, and is the chief architect of the SIPmath Modeler Tools. He holds a Ph.D. in the area of computational complexity from Yale University. He is a longtime member of INFORMS.

1.     Sam Savage, Stefan Scholtes and Daniel Zweidler, 2006, “Probability Management,” OR/MS Today, February 2006, Vol. 33, No. 1 (
2.     Sam Savage, Stefan Scholtes and Daniel Zweidler, 2006, “Probability Management Part 2,” OR/MS Today, April 2006, Vol. 33, No. 2 (
3.     Sam Savage, 2012, “Distribution Processing and the Arithmetic of Uncertainty, Analytics magazine, November/December 2012 (
4.     Melissa Kirmse and Sam Savage, 2014, “Probability Management 2.0, OR/MS Today, October 2014, Vol. 41 No. 5 (
5.     Sam Savage and Marc Thibault, 2015, “Towards a Simulation Network – or – The Medium is the Monte Carlo (with apologies to Marshall McLuhan),” Proceedings of the 2015 Winter Simulation Conference (
6.     Shayne Kavanagh and Sam Savage, 2016, “Speaking Uncertainty to Power,” Government Finance Review, April 2016 (


Related Posts

  • 52
    FEATURES There’s no such thing as unstructured data By Chuck Densinger and Mark Gonzales How to get around the elephant in the room: Four keys to giving structure to unstructured data initiatives. Making effective decisions in real time By Ron Stein Situational intelligence brings together analytics, data visualization and IoT…
    Tags: data, risk, table, carlo, monte,, tools, savage, process, excel
  • 48
    Frontline Solvers is now shipping Solver SDK (Pro and Platform) V2017, a new version of its popular software development kit for solving Monte Carlo simulation and conventional and stochastic optimization models.
    Tags: models, simulation, data, carlo, monte
  • 39
    Features Eyes on the road, not dashboards How automated analytics help detect significant business incidents; each anomaly creates an opportunity to save or earn money. By Patrick Vernon Basic Sales Analysis So much data, so little insight. Twelve ideas for anyone assigned the task of analyzing a firm’s sales data.…
    Tags: data, simulation, model, table, probability
  • 36
    Many organizations have noticed that the data they own and how they use it can make them different than others to innovate, to compete better and to stay in business. That’s why organizations try to collect and process as much data as possible, transform it into meaningful information with data-driven…
    Tags: data, management
  • 34
    March/April 2015 By Vijay Mehrotra Sometime back in the last century, when I was a disgruntled graduate student, I managed to wangle a part-time job at a semiconductor fabrication facility. My job was to gather data, build simulation models and conduct analyses to help management understand capacity, bottlenecks and cycle…
    Tags: data, process, management, number, simulation


Study: The magic of animated movies not tied to latest technology

In the nearly 60 years between the 1939 release of Hollywood’s first full-length animated movie, “Snow White and the Seven Dwarfs” and modern hits like “Toy Story,” “Shrek” and more, advances in animation technology have revolutionized not only animation techniques, but moviemaking as a whole. However, a new study in the INFORMS journal Organization Science found that employing the latest technology doesn’t always ensure creative success for a film. Read more →

Six finalists named for Edelman Award

INFORMS selected a diverse group of six finalists for the 47th annual Franz Edelman Award for Achievements in Operations Research and Management Science, the world’s most prestigious award for achievement in the practice of analytics and O.R. The 2018 finalists, who will present their work before a panel of judges at the INFORMS Conference on Analytics & Operations Research in Baltimore on April 15-17, included innovative applications in broadcasting, healthcare, communication, inventory management, vehicle fleet management and alternative energy. Read more →

Are Super Bowl ads worth it? New research suggests benefits persist

On Feb. 4, more than 40 percent of U.S. households will watch the 2018 Super Bowl game on TV. Advertisers will pay up to $4 million for a 30-second spot during the telecast. Is the high cost of advertising worth it? A new study finds that the benefits from Super Bowl ads persist well into the year with increased sales during other sporting events. Further, the research finds that the gains in sales are much more substantial when the advertiser is the sole advertiser from its market category or niche in a particular event. Read more →



2018 INFORMS Conference on Business Analytics and Operations Research
April 15-17, 2018, Baltimore


CAP® Exam computer-based testing sites are available in 700 locations worldwide. Take the exam close to home and on your schedule:

For more information, go to