New Municipal Financial Data Tool Enables Historic Comparison

By Betsy McClain

Municipal managers and policy makers have labored over the gathering, analysis and presentation of local government financial data for as long as anyone can remember. Sound historic financial data is the underlying foundation of effective financial forecasting, decision-making and local policy development. Only with a comprehensive understanding of their past and current financial circumstances can municipalities formulate effective policies for navigating the choppy financial waters ahead.

In 2008, the New Hampshire Government Finance Officers Association initiated the New Hampshire Public Finance Consortium (NHPFC) to develop a tool to address this need. Following two years of work, the NHPFC is pleased to present its New Hampshire municipal finance data model, providing for the first time a comprehensive, easy-to-use, web-based tool for the graphing and downloading of key municipal financial data elements. For example, one can now easily compare the tax rates among certain New Hampshire municipalities or review the trend of net assessed valuations in a single community over time. Ready access to this information will help to:

  • Underscore and clarify for our residents, our legislators and for ourselves as municipal officials important emerging trends in municipal finance so that we can address relevant issues proactively;
  • Provide a tool for municipal finance officers, elected officials and the general public to compare certain financial indicators across selected municipalities to help identify and foster best fiscal management practices;
  • Move the financial data from the finance officer's filing cabinet onto the web to promote transparency and full disclosure to our public;
  • Highlight the need for improved financial data management from consistent creation to accessible reporting, filing and retrieval; and
  • Sow the seeds for improved data quality that takes into consideration the unique needs of New Hampshire cities and towns, as well as national standards and uses.

Building the Data Model
The building blocks of the data model were already publicly available through a variety of online data sources, most notably the New Hampshire Department of Revenue Administration (DRA) website. However, the NHPFC data model makes this information easier to access and gives users a powerful tool to analyze certain financial data. With generous initial sponsorship and ongoing support from the Consortium partners-New Hampshire Local Government Center, New Hampshire Municipal Bond Bank, New Hampshire Municipal Management Association and New Hampshire Government Finance Officers Association-this data model will be updated as needed. This exciting project has triggered productive discussion with the DRA for ready access to more timely and more consistent data elements, and-looking forward-for expanding the available data set to include school and county data to provide a comprehensive financial portrait of New Hampshire communities in one easy-to-access location.

Accessing the Data Model
There are two primary data display functions built into the NHPFC website. The first of these is a graphing capability, based on 14 pre-defined financial ratios and graphs, which members of the Consortium feel are most likely to be universally applicable and useful. The second function is a data download feature, which allows the user to select and download any data set from any source in the website's database, from any city or town, from any year back to 2003.  Visit NHPGC website for an example.

From the left side of the NHPFC home page, choose "Reporting & Graphs" to access the pre-defined graphing page. The 14 ratios that can be graphed from this page are named and defined as follows:

  1. Total Equalized Assessed Valuation Per Capita: Calculates the municipality's total equalized assessed valuation from the DRA Equalization Survey, divided by the municipality's population from the New Hampshire Office of Energy and Planning (OEP) population file.
  2. Total Non-Taxable Property Value: Calculates the municipality's non-taxable land and building value, as reported in the DRA-supplied Tax Exempt Property data.
  3. Total Property Tax Commitment: Calculates the municipality's total property tax commitment, as reported on the DRA Tables by County report.
  4. Percentage Change in Total Property Tax Commitment: Calculates the percentage of change in the municipality's total property tax commitment, as reported on the DRA Tables by County report.
  5. Total Property Tax Commitment per Capita: Calculates the municipality's total property tax commitment, as reported on the DRA Tables by County report, divided by the municipality's population from the OEP population file.
  6. Components of Gross Valuation: Calculates in a stacked-bar graph format the total residential property value, including land and building as compared to the total commercial/industrial property value including land and building, as reported in the DRA Tables by County.
  7. Municipal Unreserved General Fund Balance as a Percent of Total Property Tax Commitment: Calculates the municipality's unreserved general fund balance, as reported on the form MS-5, as a percentage of the total property tax commitment, taken from the DRA Tax Rate Calculation Form (which includes all municipal, school and county net appropriations).
  8. Municipal Unreserved General Fund Balance as a Percent of Municipal Expenditures: Calculates the municipality's unreserved general fund balance as a percentage of total actual expenditures (which includes payments to the school and county), all as reported on the form MS-5.
  9. Direct Tax-Supported Municipal Debt as a Percent of Base Valuation for Debt Limit: Calculates the municipality's total outstanding debt as reported on the form MS-5, as a percentage of the municipality's base valuation for debt limit, per DRA Debt Limit data. The graph depicts the municipality's percentage compared to the statutory 3 percent debt limit.
  10. Direct Tax-Supported Municipal Debt as a Percent of Municipal Expenditures: Calculates the municipality's total outstanding debt as a percentage of total actual expenditures (including payments to the school and county), all as reported on the form MS-5.
  11. Direct Tax-Supported Municipal Debt Per Capita: Calculates the municipality's total outstanding debt as reported on the form MS-5, divided by the municipality's population from the OEP population file.
  12. Uncollected Taxes at Year-End as a Percent of Total Property Tax Commitment: Calculates the municipality's uncollected property taxes, as reported on the form MS-5, as a percentage of the total property tax commitment, as reported on the DRA Tables by County.
  13. Unrestricted General State Aid as a Percent of Municipal Property Tax Commitment: Calculates the municipality's State Revenues (Shared Revenues, Meals and Rooms Tax Distribution and Highway Block Grant), as reported on the form MS-4, as a percentage of the Town/City Tax Effort as reported on the DRA Tax Rate Calculation form.
  14. Percentage Change Year-to-Year of Municipal Appropriations: Calculates the percentage change over the previous year in the municipality's budgeted appropriations as reported on the form MS-2.

Selecting any of these ratios brings up a new page where one or more municipalities and years may be chosen for inclusion in your graph.

Theoretically, there is no limit to the number of cities or towns you can select for one graph. However, the realities of reading a graph with too many lines or bars suggest that this number be kept to a manageable few. Although all years may be selected, data for the current year may not be available simultaneously from DRA. Therefore, you may encounter a "hole" in your graph, depending on which data set is being drawn from to create your graph. This is especially the case for MS-5 data; availability of these data lag by two years compared to other DRA-supplied data sets.

Once your chosen municipalities and years have been selected, you may also choose to show the average for your graph, along with the statewide average for this ratio. If so, there are two check boxes near the bottom of the page for this purpose. Finally, click "Generate Graph," and the chosen data will appear instantly below in a line or bar graph.

If you care to download the underlying data corresponding to your graph, click "Download Graph Data." This results in an Excel-readable .csv file, which opens automatically upon download. The graph may be inserted into a document; simply right-click the graph, choose "Copy Image," then paste elsewhere.

Additional Data Download Options
NHPFC's pre-defined graphing capability will provide you with hours of fun and discovery. But, seriously, however comprehensive this page may seem, you may come up with data needs and comparative ratios that were not pre-defined by the Consortium. For this reason, the website's second major function was created, accessed by choosing "Data Download" from the left margin of any page.

This opens a new page containing three columns of selection possibilities-financial data variables, municipalities and years. Each data variable is identified by its source, usually a DRA form name, along with an abbreviated description. By clicking the [ i ] next to the label, a more detailed description of the variable appears, which matches DRA nomenclature found on the original paper version of the DRA data collection form.

Unlike the physical constraints imposed by the graphs, the data download function is capable of issuing all variables by all towns by all years, if you're so inclined. Typically, you may have a few variables, a few towns, and several years worth of data that you're interested in. Once all your selections are made, click the "Download" button and the results will once again appear magically in Excel-readable .csv format.

Imagine how difficult and time-consuming these graphing and downloading procedures would have been before the dawning of the NHPFC website!

Next Steps
After a round of data validation testing by dedicated volunteers from the communities of Derry, Durham, Gilford, Hollis, Peterborough, Seabrook and Stratham, the NHPFC is now ready for prime-time. The NHPFC will host a session at the LGC Annual Conference to demonstrate the features of the data model and discuss the possibilities of how this tool can make municipal financial management and forecasting more effective. Please join us! At preliminary presentations of the data model to municipal managers and finance officers, we heard comments such as: "… very impressive data that will be useful in our community" (Glen Smith, Town Administrator, Northfield, NH) and "… will make budget analysis less time consuming and more accurate" (Bill Raymond, Town Administrator, Dublin, NH). As more users access the data model, we hope to hear additional positive feedback such as this, and-just as importantly-constructive feedback and ideas for new ways to access and manipulate the data. The NHPFC encourages suggestions regarding development of additional pre-defined graphs, what additional data elements should be added and how the data consistency across towns and cities can be improved. We invite you to contact us and become involved in the important future work of the NHPFC and the ongoing evolution of the municipal financial data model.

Betsy McClain is director of administrative services, finance director and deputy town clerk for the Town of Hanover and chair of the New Hampshire Public Finance Consortium. She can be reached by phone at 603.640.3203 or e-mail.

Article contributors also include Jim Howard, retired deputy city manager and finance director for the City of Concord, and Chris Porter, researcher for the New Hampshire Local Government Center. For questions regarding data available on the NHPFC website, contact Chris at 603.224.7447, ext. 138, or by email.

Visit the NHGFOA booth at the LGC Annual Conference Exhibit Hall to preview the NHPFC data model, and don't miss the conference session for a thorough overview of data model capabilities. Visit the Annual Conference section at our website to preview the conference schedule of events.