Forecasting Technology
The State of the Market
By Elizabeth Fus
olutionsGovernments can
choose from a number
of software packages to
use in forecasting, and
choosing the right one
for any given jurisdiction
involves a number
of factors.
T
echnology has been a boon to many of the essential functions of the government finance offi-cer — accounting, payroll, accounts payable, and more. However, some func-tions have not benefited from tech-nology to quite the same degree, and one notable instance is forecasting. The challenge is that forecasting is an art as well as a science, not a highly structured, routine process in the way that process-ing a paycheck or makprocess-ing a journal entry is. This means that it is difficult to develop a mass-market technology solu-tion for forecasting.Nevertheless, software solutions are available, but they vary widely. This article provides an overview of the capabilities of three general catego-ries of software solutions traditionally employed by local governments and of dedicated forecasting software used by several vanguard local governments:.
n Excel and Excel add-ins for
fore-casting.
n Statistical software packages. n Dedicated forecasting applications.
Note that these categories do not represent a comprehensive catalog of potential forecasting solutions — they focus on “pure play” solutions intended specifically for forecasting. Excluded from these categories are solutions such as budgeting software and business intelligence systems, which often
pro-vide forecasting capabilities, but within a much broader array of functionality. Hence, significantly greater time and money would be required to imple-ment them than the solutions consid-ered here. The solutions described in this article serve as illustrative examples for their respective categories, and the use of these examples does not imply a GFOA endorsement.1
EXCEL AND EXCEL ADD-INS Excel, despite its accessibility, has critics who challenge its role in fore-casting because of the software’s limi-tations. Users typically go about fore-casting in Excel by reviewing the data to identify and evaluating appropriate forecasting methods. And this is one limitation — sometimes the dataset isn’t telling and statistical analysis is needed to help determine an appro-priate forecasting method. Statistical analysis in Excel can involve manually entering functions. However, the Excel Analysis ToolPak, an add-in that comes standard with the software, can be used to make better use of Excel’s analytical potential. Activating the add-in pro-vides users with 19 analysis tools, acces-sible via a Data Analysis icon under the Data tab (see Exhibit 1.)
Another tool forecasters common-ly use in Excel is graphs. Users will graph a time series dataset and then add a trendline, which is a curve that
attempts to “fit” a given dataset to fore-cast future values. The disadvantage is that Excel offers just six types of trend-lines (although more advanced users can manually compute more complex regression analysis in Excel), and users have to exercise their own judgment as to which one should be used based on the data.
Dedicated Excel models are another option. The City of Atlanta, Georgia, uses MuniCast to streamline its five-year general fund revenue forecast from multiple spreadsheets to one model. The city input seven years of monthly revenue data to identify pat-terns attributed to payment cycles as well as economic cycles. The city also uses quantitative research to enhance its forecast. For example, it has com-piled a set of economic metrics for key revenue sources, including the Case-Shiller index for the city’s residen-tial assessed value growth. Taking the information together, the city updates the model monthly, after each period close.
TRADITIONAL STATISTICAL SOFTWARE
More advanced statistical solutions offer greater capabilities than Excel. They generally offer an easier environ-ment for forecasters to explore and conduct other types of regression tech-niques. Some also automate the meth-od selection process, sparing users the
prerequisite work described earlier to identify an appropriate method. There are traditional statistical software pack-ages such as IBM SPSS Statistics, SAS Business Intelligence and Analytics, IHS EViews, and R, a free product from the R Project for Statistical Computing. These products differ in ease of use. While SPSS Statistics and SAS have become more user-friendly for the beginner, users will benefit from an understanding of programming and statistics to refine and validate the models. EViews and R, on the other hand, require users to input their respective command language to effec-tively forecast. Users can easily find information on R’s language online, as it is open source.
Much like Excel, users input or import the data into the software, analyze the data to identify the appropriate forecasting method, and ultimately forecast using the identified method. Exhibit 1: Additional Data Analysis Functions Provided By Excel
Exhibit 2: Sensitivity Analysis, Monthly Trends Generated Using MuniCast
For R users, this will require more use of manual commands to tell R what procedures to follow. SPSS Statistics and SAS offer specific forecasting capabilities. SPSS Forecasting is SPSS Statistics’ forecasting module, and SAS offers SAS Forecasting Server and SAS Forecasting for Desktop. These solutions can automate the selection process by determining a model it identifies as the most appropriate, given the historical data the user inputs, often called an expert method. A novice user will likely find this feature helpful. An experienced forecaster is more likely to benefit from the ability to select from the methods and parameters, or to refine an expert selection. For instance, an experienced forecaster may adjust the parameters (e.g. maximum and minimum boundaries) of an expert selection to capture the effects of a new tax increase.
FORECASTING SOLUTIONS Many forecasting-specific software packages aim to help forecasters select an appropriate method, much like the traditional statistical software, but through a more user-friendly environ-ment. Dedicated forecasting software has its roots in manufacturing and sup-ply chain management, where it is used to forecast consumer demand and help with inventory planning, but it is now used across all industries.
Like most traditional statistical pack-ages, forecasting software is typical-ly more expensive than Excel, but it offers additional forecasting techniques than Excel add-ins. Having some level of familiarity with statistics will help users tweak models, but users can also
choose to have the software identify the model and forecast based on the data entered (as some statistical software does). These dedicated forecasting software packages offer different inter-faces (desktop or Internet-based) and capability options, such the number of variables or observations the solution can handle.
Users of Autobox forecasting ware input historic data and the soft-ware looks for relationships and pat-terns before customizing a model for the data set. Its early warning system reports help users identify unusual instances for further exploration. When identifying relationships and patterns, Autobox detects and automatically adjusts for interventions such as out-liers, local time, seasonality trends, and variance and parameter changes. Exhibit 3 shows a level shift analysis in Autobox. The analysis identified an event in the second half of 1997 that affected sales, and the forecast adjusted
for it. Autobox offers both interactive and batch interfaces, and three ver-sions of the solution are available. The most basic version allows for 100 his-torical observations and six causal vari-ables to be incorporated in the model, while the most advanced allows for up to 10,000 historical observations and 150 causal variables.
Another forecasting software package is Forecast Pro, which allows users to choose from a set of forecasting methods and to collaborate with others to establish a final forecast. It allows users to easily rearrange hierarchies and to monitor forecast performance. Forecast Pro is available in three editions, ranging from a desktop tool that allows users to forecast up to 100 series at a time to an advanced edition that enables users to forecast unlimited series (though a computer will need sufficient memory to run all the forecasts), run exception reports (generated when data are not within Exhibit 3: Level Shift Analysis in Autobox
expected parameters, or outside the normal range) and to customize forecast worksheets. For all three editions, there are no limits to the number of historic observations allowed.
The City of Mesa, Arizona, uses Forecast Pro and points out the soft-ware’s expert selection feature, which helps automate the technical part of the analysis. Forecast Pro reviews the data and identifies a best pick based on an item-by-item algorithm. This fea-ture allows users to review the fore-cast report, which describes the logic behind the selected method and pro-vides details on the model as well as the actual forecast. Exhibit 4 shows a Forecast Pro expert analysis, with the bottom window detailing the selection on the chosen method.
Jurisdictions that use specialized forecasting software haven’t necessar-ily abandoned Excel altogether. For example, the South Dakota Legislative Research Council, an Autobox user, and the City of Mesa both forecast more consistent revenue streams in Excel
because it’s easier and quicker for a simple forecast.
THE ART OF FORECASTING The software a jurisdiction uses can expedite and refine its forecasting process, but that isn’t the whole story. Forecasting well requires good information and forecasting expertise. The importance of identifying good data and resources and using both qualitative and quantitative methods cannot be overstated. Forecasts depend on consistent data series as well as external resources such as economic indicators to help enhance the forecast. Forecasting software cannot supply this information.
Another pertinent piece of the forecasting process is communications. The budget office at the City of Scottsdale, Arizona, works with other city departments, seeking input from the field staff throughout the year to prepare the revenue forecasts. In fact, 18 people from various departments participate in the revenue forecasting for the city’s general fund. This process allows them to benefit from the knowledge of internal experts on specific revenue streams. It also helps everyone involved to better understand the underlying assumptions that went into the forecast.
Scottsdale blends its qualitative techniques of consensus and expert forecasting, such as collaborating with experts from around the city’s depart-ment, with quantitative methods. The quantitative information comes from internal experts and data like building permits and taxpayer reporting histo-ries as well as external resources for macro, regional, and industry-specific trends like consumer spending reports, the USDA Cost of Food index, and Smith Travel Reports. Combining these qualitative and quantitative methods, the city’s budget office performs fore-casting without the aid of dedicated forecasting software; it uses Excel to help identify any trends or changes from prior years and relies on expert judgment and qualitative information to identify trends or changes from prior years and model accordingly.
Mesa participates in the Forecasting Project at the University of Arizona’s Economic and Business Research Center. Participants have access to quarterly economic forecasts, forecast-Exhibit 4: Forecast Pro’s Expert Analysis
Forecasting is an art as well as a
science, not a highly structured,
routine process, which means
that it is difficult to develop
a mass-market technology
solution for forecasting.
ed variables, and economic indica-tors for the state and metro areas. Participants also meet to discuss the data each quarter. With this informa-tion in hand, Mesa begins the data massaging process — adjusting aggre-gated metro information to make it more specific for the city. Mesa also performs a regression analysis in Forecast Pro, reviewing the infor-mation against historic data. These quantitative techniques are supple-mented with qualitative techniques. For example, once the forecasting team arrives at a forecast, they col-laborate on a sensitivity analysis that refines it further.
CONCLUSIONS
There are many software packages on the market that governments can use in forecasting; this article has described only a handful. Choosing the software that’s right for any given jurisdiction involves a number of fac-tors. What level of statistical proficien-cy — and even Excel proficienproficien-cy — is needed? How variable are the revenue streams—are they affected by season-ality or are they relatively consistent? Would they require more advanced forecasting techniques? If so, which software packages offer those
techniques? Does the jurisdiction’s existing budgeting and/or finance sys-tem provide useful options, e.g. A3 Modeling, Oracle Hyperion, Questica Budget, etc.? And once a jurisdiction decides to pursue a software solution, it should do a test run before making the purchase. Many solutions offer trial versions, so it makes sense to test the product using the govern-ment’s data to make sure it has capa-bilities that meet the jurisdiction’s specific needs.
As governments work to refine their forecasting methods, they are finding that software can save time and effort — although no solution can replace consistent data series to perform the analysis or supply important supplemental information to refine the forecast. y
Note
1. For more detailed lists of forecasting solu-tions, see the resources and publications provided by analytics and forecasting orga-nizations such as the Institute for Operations Research and the Management Sciences and International Institute of Forecasters.
ELIZABETH FU is a consultant with the GFOA’s Research and Consulting Center in Chicago, Illinois.
She would like to thank the following for their contributions to this article: Gary Donaldson, Revenue Chief, City of Atlanta, Georgia; Judy Doyle, Budget Director, City of Scottsdale, Arizona; Peter Klimoski, Budget Coordinator, City of Mesa, Arizona; Aaron Olson, Principal Fiscal Analyst, South Dakota Legislative Research Council; Tom Reilly, CEO, Automatic Forecasting Systems; Erik Subatis, Director of Sales, Forecast Pro; Christopher Swanson, Founder, Government Finance Research Group; and Jack Yurkiewicz, Professor of Management, Pace University.
The software a jurisdiction
uses can expedite and refine
its forecasting process, but
that isn’t the whole story.
Forecasting well requires good
information and expertise.
Transform Your
Approach to Financial
Management
Learn more about long-term financial planning and how the GFOA can help you
with this process. Readers of Financing the Future will discover key features of a successful
long-term financial plan; phases pivotal to plan implementation; and how to involve elected
officials, staff, and citizens to create a plan that gets results that are valuable to their
community. With this publication, you will learn how to achieve and maintain
long-term financial sustainability.
Please visit www.gfoa.org/ltfp for more information.
Questions? E-mail [email protected]
