Share with your friends










Submit

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).

CFO/CPO

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 ProbabilityManagement.org.
Figure 3: The model in action. The file is available for download from the models page of ProbabilityManagement.org.

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 (savage@stanford.edu) is executive director of ProbabilityManagement.org, 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 (skavanagh@gfoa.org) is senior manager-research at the Government Finance Officers Association in Chicago. © Copyright 2013, Sam Savage and Shayne Kavanagh.

References

  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

 

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

2017 Tech Trends: the kinetic enterprise

From dark analytics to mixed reality, machine intelligence and blockchain, Deloitte’s annual Technology Trends report analyzes the trends that could disrupt businesses in the next 18-24 months. CIOs who can harness the possibilities of these technologies will be better positioned to shape the future of their business. Read more →

FICO: Financial crime trends for 2017

If the world seemed dangerous from a financial crime perspective last year, FICO experts predict an even more challenging 2017. In a new paper, four of the leaders in the company’s fraud and financial crime group laid out 17 predictions, ranging from killer devices in the home to hacked fingerprints. Read more →

UPCOMING ANALYTICS EVENTS

INFORMS-SPONSORED EVENTS

CONFERENCES

2017 INFORMS Healthcare Conference
July 26-28, 2017, Rotterdam, the Netherlands

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.