Share with your friends


Analytics Magazine

The ‘Sequestetron’: Spreadsheet model helps municipalities cope with risk management, forecasting, budget uncertainties during turbulent times.

November/December 2013

Spreadsheet model helps municipalities cope with risk management, forecasting, budget uncertainties during turbulent times. By Sam Savage, CAP, (left) and Shayne KavanaghBy Sam Savage, CAP, (left) and Shayne Kavanagh

Managing the finances of a municipality is difficult enough when the federal government is functioning, but the current environment introduces a host of new uncertainties. For example, communities with a large federal workforce may see a reduction in sales tax revenue, or there may be doubts about the continued viability of programs on which local governments have traditionally relied, leaving municipalities to pick up the slack.

Facing these problems head on is the Government Finance Officials Association (GFOA), a professional association of more than 17,000 state/provincial and local finance officers in the United States and Canada. The members of this century-old organization typically have training in finance and statistics, but they do not have access to departments of risk management that might lend a hand in large corporate environment. Nonetheless, they must forecast uncertain tax revenues to meet a budget that generally covers fixed obligations such as payment on debt, operating expenses such as police and fire protection, and discretionary spending such as building a new library, for example.

A conceptual spreadsheet model reflecting this situation has been developed using the principles of probability management. It allows the user to specify the uncertainty in revenue and required levels of spending, whereupon it instantly simulates the chances of meeting each obligation (see accompanying sidebar story on page 55).


Recently, some CFOs and city managers have begun to openly communicate the financial uncertainties they face to the members of their city councils, who generally have little training in statistics. For example, Kara Skinner, the CFO of the City of Colorado Springs, Colo., has presented the probability distribution resulting from a time series model the city uses to forecast sales and use tax (Figure 1). When the city made a concerted effort to rebuild reserves, she recommended a sales tax forecast for budgeting purposes that the city had two-to-one odds of meeting or exceeding. The city council was receptive to this discussion and understood the recommendation with respect to managing risk [1]. In the discipline of probability management, Skinner was playing the role of CPO (chief probability officer) – the individual who authorizes the scope, definition and use of uncertainties in a particular setting [2].

Figure 1: The City of Colorado Springs forecasting model.
Figure 1: The City of Colorado Springs forecasting model.

Risks Don’t Add Up

 Figure 2: The addition of two SIPs of single die rolls to create the SIP of a pair of dice.
Figure 2: The addition of two SIPs of single die rolls
to create the SIP of a pair of dice.

Pall Gudgeirsson, city manager of San Clemente, Calif., is exploring probabilistic methods for estimating reserve requirements across various risk categories, such as earthquake, infrastructure and storm surge damage to the city pier. A numerical approach to this problem is to bucket the risks, estimate a reserve for each bucket, then sum them to get total reserve requirements. However, suppose there were 10 risk categories, and reserves were set to cover 95 percent of the anticipated outcomes for each. Then total reserves would equal the sum of ten 95th percentiles. If the risks are statistically independent and if money can be moved freely between accounts, then through diversification, the chance of exceeding total reserves is roughly 1 in 10 million. This is a version of the “Flaw of Averages” known as the “Flaw of the Extremes” [3]. In this case the resulting reserves reflect a risk tolerance commensurate with building shelters to protect the city from meteor strike.

Probability management allows the proper additions of uncertainties by representing them as columns of outcomes known as stochastic information packets or SIPs. When the individual uncertainties of each category are represented in this way, their SIPs may be summed, element by element, to create an output SIP of total risk. For example, if you added the SIPs representing rolls of two dice, the resulting SIP would have more 7s in it than 2s or 12s (Figure 2).

Shortfall Model.xlsx

Shortfall Model.xlsx demonstrates the potential use of probability management in financial planning. It calculates the chance of shortfall across three prioritized financial obligations in the face of uncertain tax revenue. Level 1 is assumed to be fixed payment on debt and holds the highest priority. The next priority is comprised of operating expenses such as police and fire. Finally, special projects, such as a new library, make up the lowest priority. The estimated tax revenue and 95 percent confidence interval, along with the expenses, are entered as inputs. Ten thousand revenue scenarios are then instantly run through the model, resulting in the simulated chance of shortfall by area.

Where Do SIPs Come From?

A central concept of Probability Management is the separation of data, in the form of SIP libraries, which may be generated in one software environment, and models to access SIP libraries, which may be developed in another environment. Models such as SHORTFALL.XLSX may be created in native Excel for use as interactive stochastic dashboards by potentially millions of managers.

However, generating SIP libraries typically requires more powerful software. For example, there are several powerful Monte Carlo packages for Excel, which can output their results for use in SIPmath models. Furthermore, there are a number of stand-alone analytical programs that are superior for generating more complex and higher dimensional SIP libraries such as those arising from forecasting multiple time series.

Figure 3: The model in action. The file is available for download from the models page of
Figure 3: The model in action. The file is available for download from the models page of

Libraries of SIPs may be used within simulation models such as the one described in the sidebar to illuminate issues that are not apparent in straight numerical models. For example, one can test the sensitivity of total risk to the coupling of risk categories (think earthquake, flooding and Fukushima). In addition one can explore the value of the pooling of accounts. That is, if you are prohibited from covering a shortfall in fund A with Fund B, and vice versa, then the model will indicate the additional reserves you must keep in each.

“I hear and I forget. I see and I remember. I do and I understand.” – Confucius

The latest versions of Excel running on today’s computers are capable of dependably processing SIPs of thousands of elements, using the built-in Data Table function [4]. SIPmath takes managers beyond the “hear and forget” of the conference call and the “see and remember” of PowerPoint to an interactive environment in which they can “do and understand.” According to Matthew Raphaelson, head of Strategic Planning & Finance at Wells Fargo’s Community Banking Group, “SIPmath could be the tipping point in helping Excel users diagnose and cure the Flaw of Averages.”

Shortfall Model.xlsx, informally referred to as the “Sequestetron,” was developed in conjunction with the GFOA as an example of such an interactive model (see sidebar). If Confucius were alive, he would recommend downloading the model and experiencing the immediate gratification of 10,000 scenarios surging through Excel with each keystroke. ?

Sam L. Savage ( is executive director of, author of “The Flaw of Averages: Why We Underestimate Risk in the Face of Uncertainty,” consulting professor at Stanford University and a Fellow of the Judge Business School at the University of Cambridge. He is a senior member of INFORMS and a Certified Analytics Professional (CAP). Shayne C. Kavanagh ( is senior manager-research at the Government Finance Officers Association in Chicago. © Copyright 2013, Sam Savage and Shayne Kavanagh.


  1. A Risk-Based Analysis of General Fund Reserve Requirements: A Case Study of the City of Colorado Springs,” Shayne Kavanagh, Government Finance Officers Association, Chicago, 2012.
  2. Probability Management,” Sam Savage, Stefan Scholtes and Daniel Zweidler, OR/MS Today, February 2006, Vol. 33. No. 1.
  3. The Flaw of Averages: Why We Underestimate Risk in the Face of Uncertainty,” Sam L. Savage, John Wiley & Sons, 2009, 2012.
  4. Distribution Processing and the Arithmetic of Uncertainty,” Sam L. Savage, Analytics Magazine, November/December 2012.

business analytics news and articles



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