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

Analytics Blog

Electoral College put to the math test

With the campaign two months behind us and the inauguration of Donald Trump two days away, isn’t it time to put the 2016 U.S. presidential election to bed and focus on issues that have yet to be decided? Of course not.


SAS, IVMF team up to help veterans transition to analytics careers

Employers from every industry are clamoring for people with analytics skills to help them turn data into meaningful information to make better decisions. Military veterans and family members can pursue the hottest careers today, thanks to a collaboration between the Institute for Veterans and Military Families (IVMF) and analytics provider SAS. Read more →

Lehigh Univ., Pennsylvania Department of Corrections win Wagner Prize

Nearly 100 unique factors have to be considered during the complicated task of assigning inmates to any of the Pennsylvania Department of Corrections’ 25 facilities. What once took seven employees nearly a week to accomplish can now be completed in less than 10 minutes at an expected savings of nearly $3 million, thanks to an algorithm created by a team of Lehigh University students and professors and the Pennsylvania Department of Corrections. Read more →



2017 Winter Simulation Conference (WSC 2017)
Dec. 3-6, 2017, Las Vegas


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