Share with your friends










Submit

Analytics Magazine

Monte Carlo for the masses

Free tools from ProbabilityManagement.org 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 Management.org 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 ProbabilityManagement.org 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, ProbabilityManagement.org 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 ProbabilityManagement.org, 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 ProbabilityManagement.org. 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. Random.org 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, Random.org 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 (sam@probabilitymanagement.org) 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 ProbabilityManagement.org, 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.

REFERENCES
1.     Sam Savage, Stefan Scholtes and Daniel Zweidler, 2006, “Probability Management,” OR/MS Today, February 2006, Vol. 33, No. 1 (http://probabilitymanagement.org/library/Probability_Management_Part1s.pdf).
2.     Sam Savage, Stefan Scholtes and Daniel Zweidler, 2006, “Probability Management Part 2,” OR/MS Today, April 2006, Vol. 33, No. 2 (http://probabilitymanagement.org/library/Probability_Management_Part2s.pdf).
3.     Sam Savage, 2012, “Distribution Processing and the Arithmetic of Uncertainty, Analytics magazine, November/December 2012 (http://viewer.zmags.com/publication/90ffcc6b#/90ffcc6b/29).
4.     Melissa Kirmse and Sam Savage, 2014, “Probability Management 2.0, OR/MS Today, October 2014, Vol. 41 No. 5 (http://viewer.zmags.com/publication/ad9e976e#/ad9e976e/32)
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 (http://www.informs-sim.org/wsc15papers/467.pdf).
6.     Shayne Kavanagh and Sam Savage, 2016, “Speaking Uncertainty to Power,” Government Finance Review, April 2016 (http://www.gfoa.org/sites/default/files/GFR041618.pdf).

Save

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, probabilitymanagement.org, 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
  • 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
  • 32
    By Gal Horvitz IoT (Internet of Things) devices have become increasingly popular in recent years. They are all around us – from fitness trackers on our wrists to smart thermostats in our homes – and adoption will only continue to grow in the coming years. In fact, Gartner, Inc. reported…
    Tags: data, process, management

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.


Headlines

Gaining distribution in small retail formats brings big payoffs

Small retail formats with limited assortments such as Save-A-Lot and Aldi and neighborhood stores like Target Express have been growing in popularity in the United States and around the world. For brands, the limited assortments mean greater competition for shelf-space, raising the question of whether it is worth expending marketing effort and slotting allowances to get on to their shelves. According to a forthcoming study in a leading INFORMS scholarly marketing journal, Marketing Science, the answer is “yes.” Read more →

Cognitive computing a disruptive force, but are CMOs ready?

While marketing and sales professionals increasingly find themselves drowning in data, a new IBM study finds that 64 percent of surveyed CMOs and sales leaders believe their industries will be ready to adopt cognitive technologies in the next three years. However, despite this stated readiness, the study finds that only 24 percent of those surveyed believe they have strategy in place to implement these technologies today. Read more →

How weather can impact consumer purchase response to mobile ads

Among the many factors that impact digital marketing and online advertising strategy, a new study in the INFORMS journal Marketing Science provides insight to a growing trend among firms and big brands: weather-based advertising. According to the study, certain weather conditions are more amenable for consumer responses to mobile marketing efforts, while the tone of the ad content can either help or hurt such response depending on the current local weather. Read more →

UPCOMING ANALYTICS EVENTS

INFORMS-SPONSORED EVENTS

Essential Practice Skills for High-Impact Analytics Projects
Sept. 26-27, Executive Conference Center, Arlington, Va.

Foundations of Modern Predictive Analytics
Oct. 2-3, VT Executive Briefing Center, Arlington, Va.

2017 INFORMS Annual Meeting
October 22-25, 2017, Houston

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

CAP® EXAM SCHEDULE

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 
https://www.certifiedanalytics.org.