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
  • 38
    Features Why optimization models fail By Patricia Randall Supply chain and manufacturing: How to avoid chaos in the field by combining simulation and real-time optimization. AI: Path to an intelligent enterprise By Joseph Byrum Imagine a future guided by artificial intelligence: Augmenting human decision-making at the enterprise level to generate…
    Tags: data, process, simulation, models, table, generate
  • 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


Using machine learning and optimization to improve refugee integration

Andrew C. Trapp, a professor at the Foisie Business School at Worcester Polytechnic Institute (WPI), received a $320,000 National Science Foundation (NSF) grant to develop a computational tool to help humanitarian aid organizations significantly improve refugees’ chances of successfully resettling and integrating into a new country. Built upon ongoing work with an international team of computer scientists and economists, the tool integrates machine learning and optimization algorithms, along with complex computation of data, to match refugees to communities where they will find appropriate resources, including employment opportunities. Read more →

Gartner releases Healthcare Supply Chain Top 25 rankings

Gartner, Inc. has released its 10th annual Healthcare Supply Chain Top 25 ranking. The rankings recognize organizations across the healthcare value chain that demonstrate leadership in improving human life at sustainable costs. “Healthcare supply chains today face a multitude of challenges: increasing cost pressures and patient expectations, as well as the need to keep up with rapid technology advancement, to name just a few,” says Stephen Meyer, senior director at Gartner. Read more →

Meet CIMON, the first AI-powered astronaut assistant

CIMON, the world’s first artificial intelligence-enabled astronaut assistant, made its debut aboard the International Space Station. The ISS’s newest crew member, developed and built in Germany, was called into action on Nov. 15 with the command, “Wake up, CIMON!,” by German ESA astronaut Alexander Gerst, who has been living and working on the ISS since June 8. Read more →



INFORMS Computing Society Conference
Jan. 6-8, 2019; Knoxville, Tenn.

INFORMS Conference on Business Analytics & Operations Research
April 14-16, 2019; Austin, Texas

INFORMS International Conference
June 9-12, 2019; Cancun, Mexico

INFORMS Marketing Science Conference
June 20-22; Rome, Italy

INFORMS Applied Probability Conference
July 2-4, 2019; Brisbane, Australia

INFORMS Healthcare Conference
July 27-29, 2019; Boston, Mass.

2019 INFORMS Annual Meeting
Oct. 20-23, 2019; Seattle, Wash.

Winter Simulation Conference
Dec. 8-11, 2019: National Harbor, Md.


Advancing the Analytics-Driven Organization
Jan. 28–31, 2019, 1 p.m.– 5 p.m. (live online)


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