Tales from the BudComm – an automated budget tracker.

BudgetAs Y’all know, I’ve been trying to better understand the RSAs (NH Statutes, especially RSA 32 and RSA 40 that govern Budgeting and Budget Committees) this past year.  Read the RSAs, talk with a couple of lawyers, talk with Legislators, talk with the Dept of Revenue Administration – I learned that our BudComm was not obeying the RSA that said that we SHALL do year-round budget oversight.  Well, being a programmer, I am the quintessential one – work really heard in order to “be lazy” later on (yeah, that second part never seems to come around but it’s nice to lie to ourselves once in a while).  So, instead of actually going to a meeting (time, my friends, time), I figured that automating it would be better – getting atomic level data at the GL account level, storing it off, creating both snapshots, time sequenced, and analytic results and then just send out the “run of the mill” and “anomaly” reports out automatically.

So, in order to do that, one needs the data. If you’re interested, from either as a programmer/engineer/analyst/BudComm member, here’s the spec I set up for both the town and school board to send/email to “the system” (or download here: Transactional Budget Data Definitions):

Transactional Record Data Definitions
By the Gilford Budget Committee
for
On-going Budget Oversight of the Municipal and School District Budgets

Purpose:

Historically, the SB2 authorized and empowered Gilford Budget Committee has only done half of its responsibilities in recent time: that of working with both the Town and School District elected and appointed officials as well as department heads in creating the final budgets that are presented to the Town’s Legislative Body (the voters in Gilford) during the Public Hearings and the Deliberative Sessions). These are the budgets that are actually voted up during the second part of the Deliberative Session – secret ballot voting.

There is no doubt that the Budget Committee (“BudComm”) has handled this responsibility over the years – sometimes mildly and without much rancor and sometimes quite contentious and with heated discussions on both (three? Four?) sides of many or most financial decisions concerning the budgets.

However, it is clear that there is one area in which the BudComm has failed miserably in carrying out a major responsibility – that of on-going budget oversight. Yes, it has had a major hand in crafting the presented budgets (even if it decides not to change one or more specific line items) but it is clear that once done, hands are clapped, backs are slapped, budget books and papers are put away to gather (in most cases) dust and the BudComm goes (mostly) quiet into that good night until the next season begins at the behest of the BudCom Chair. Then, and only then, are budgets a major focus again.

I have listed a few of the authorizing NH statutes / RSAs concerning the BudComm. It is clear from RSA 32:22 (See Appendix A, Authorizing Statutes) where the BudComm has failed:

The budget committee shall meet periodically to review such statements

To my knowledge, there has been no systematic or regular review of either budget on an on-going basis. Both the Chair and the Vice-Chair of the BudComm have decided to rectify this starting after the voting on the budgets.
Budget Committe Oversight:

Many on the Budget Committee are on it because of the advice that “it is only a part time process during a small part of the year”. Moving to fully carry out its responsibilities through out the fiscal year(s) will require more time thruout the calendar year (even as the “crunch” of the budget season will remain. This will be a radical change for all three entities involved: BudComm, the Town, and the School District. However, it cannot be avoided because of that legislative word “shall” that makes this function mandatory

When the word “review” is used, it is traditionally an accepted meaning that the BudComm would hold a physical meeting along with the relevant officials and employees of the Town and School District at some regularly set times for meeting to review the snapshots of revenues, expenditures, and transfers that have occurred either in full (i.e., from the start of the relevant fiscal year) or on an incremental basis (i.e., from the last such meeting). Much could be said of the time involved, the paperwork involved, the preparation needed – and that’s not including the time of the meeting itself nor time spent on issues identified during such a meeting.

Please note the use of the word “physical” in the above paragraph. Frankly, much of the oversight can done in another fashion and may well have a number of positive attributes to it:

  • Cut down on the preparation time
  • Eliminate the need for a number of the meetings
  • Reduce the possibility of contentiousness during the “regular budget season” as BudComm members would already know what is going on (both incumbent and new members to the BudComm).

Is there a way to mitigate the amount that this will require?

BudComm Oversight System:

In performing the required oversight, the BudComm will be creating its own system that will gather data from both the Town and School District financial systems and put them into a database maintained by the BudComm, that would allow for reports of various types to be built around that data from comparisons to the Legislative approved budget. These will be automatically be sent to the BudComm members for perusal and if a fiscal anomaly arises, a meeting could be called (or just a couple of phone calls) then.

There will be no direct contact between the BudComm system and the Town or School Districts financial systems. Security in this area will be provided by one way use of transactional data records that will be sent from the Town / School District to a mailbox with the frequency of such record creation and distribution to be decided (where sooner is far better than not). Also, the actual physical format of the records will be determined (fixed length records, data types, and the like – although comma delimited files (CSV files) are almost always a default record output capability of any modern systems). What this paper outlines is the LOGICAL definition of five kinds of electronic data transaction types that the BudComm will require of both the Town and School District:

  • Revenue
  • Expenditures
  • Transfers
  • GL account: adding a new GL account
  • GL account: deleting an existing account

Along with the voted in budgets to act as a baseline, these transaction types will allow the BudComm members to have oversight over the fiscal movements of both entities.

The rest of this document will outline the logical data requirements for each data record. These five basic transactions will be presented to the BudComm system at the GL account level (not at the MS Form level) as presented to the BudComm during the budget process.
Main Transaction Data Record Definitions

1. GL account: adding a new GL account

  • Entity
    • Data Type: Character / String
    • DataSize: 2
    • Constraints:
      • “TG” – Town of Gilford
      • “SD” – School District
  • Record Type:
    • Data Type: Character / String
    • Data Size: 4 characters
    • Constraint: value must be “GLA ”
  • Date
    • Data Type: Date
    • Format: MMDDYYYY
  • Amount
    • Default Amount: 0
    • Data Type: Numeric / Currency
    • DataSize: Digit (10,2) / XXXXXXXX.XX
    • Note: This should be the default value above
  • GL
    • Data Type: Character / String
    • Size: 25 characters
    • Constraints
      • Must be in the set (a-z | A-Z| 0-9)
      • Must not be an existing GL account in the budget to which this transaction belongs (Town, School District)
  • Purpose / Reason
    • Data Type: Character / String
    • Data Size: 150 characters
    • Note: Denotes the reason for adding this GL Account

 

2. GL account: deleting an existing account

  • Entity
  • Data Type: Character / String
  • Data Size: 2
  • Constraints:
    • “TG” – Town of Gilford
    • “SD” – School District
  • Record Type:
    • Data Type: Character / String
    • Data Size: 4 characters
    • Constraint: value must be “GLD ”
  • Date
    • Data Type: Date
    • Format: MMDDYYYY
  • Amount
    • Default Amount: 0
    • Data Type: Numeric / Currency
    • DataSize: Digit (10,2) / XXXXXXXX.XX
    • Note: This is used if there are any funds in it if not already 0, else use the default value above
  • GL
    • Data Type: Character / String
    • Data Size: 25 characters
    • Constraints
      • Must be in the set (a-z | A-Z| 0-9)
      • Must be an existing GL account in the budget to which this transaction belongs (Town, School District)
  • Purpose / Reason
    • Data Type: Character / String
    • Data Size: 150 characters
    • Note: Denotes the reason for deleting this GL Account

3. Revenue – incremental revenues (e.g., from taxation, grants, services rendered, sales, et al)

  • Entity
    • Data Type: Character / String
    • Data Size: 2
    • Constraints:
      • “TG” – Town of Gilford
      • “SD” – School District
  • Record Type:
    • Data Type: Character / String
    • Data Size: 4 characters
    • Constraint: value must be “REV”
  • Date
    • Data Type: Date
    • Format: MMDDYYYY
  • Amount
    • Default Amount: 0
    • Data Type: Numeric / Currency
    • Data Size: Digit (10,2) / XXXXXXXX.XX
    • Note: The amount being added to this GL account
  • GL
    • Data Type: Character / String
    • Data Size: 25 characters
    • Constraints
      • Must be in the set (a-z | A-Z| 0-9)
      • Must be an existing GL account in the budget to which this transaction belongs (Town, School District)
  • Purpose / Reason
    • Data Type: Character / String
    • Data Size: 150 characters
    • Note: Denotes the reason for the additional funds
  • Funding Source
    • Data Type: Character / String
    • Data Size: 150 characters
    • Note: Denotes the source of these additional funds.

4. Expenditures

  • Entity
    • Data Type: Character / String
    • Data Size: 2
    • Constraints:
      • TG” – Town of Gilford
      • “SD” – School District
  • Record Type:
    • Data Type: Character / String
    • Data Size: 4 characters
    • Constraint: value must be “EXP ”
  • Date
    • Data Type: Date
    • Format: MMDDYYYY
  • Amount
    • Default Amount: 0
    • Data Type: Numeric / Currency
    • Data Size: Digit (10,2) / XXXXXXXX.XX
    • Note: The amount being added to this GL account
  • GL
    • Data Type: Character / String
    • Data Size: 25 characters
    • Constraints
      • Must be in the set (a-z | A-Z| 0-9)
      • Must be an existing GL account in the budget to which this transaction belongs (Town, School District)
  • Purpose / Reason
    • Data Type: Character / String
    • Data Size: 150 characters
    • Note: Denotes the reason for the additional funds
  • Expenditure Receiver
    • Data Type: Character / String
    • Data Size: 150 characters
    • Note: Who is getting these monies (e.g., Person | Company)
  • PettyCashFlag
    • Data Type: Character
    • Data Size: 1 character
    • Constraint: To be in the set (1,0) where 1 signifies “from Petty Cash” and “0” signifies “not from Petty Cash”
    • Note: Who is getting these monies (e.g., Person | Company | Other)
  • PONum
    • Data Type: Character / String
    • Data Size: 15
    • Constraint:
      • Must be non-null if PettyCashFlag is “0”
      • Must not be an existing PO already in the system to which this transaction belongs (Town, School District) unless this is an EXPM record where the PO MUST be in the system
    • Note: The Purchase Order number
  • POTotal
    • Data Type: Numeric / Currency
    • Data Size: Digit (10,2) / XXXXXXXX.XX
    • Constraint:
      • Must be non-null if PettyCashFlag is “0”

5. GL Account Transfers

  • Entity
    • Data Type: Character / String
    • Data Size: 2
    • Constraints:
      • “TG” – Town of Gilford
      • “SD” – School District
  • Record Type:
    • Data Type: Character / String
    • Data Size: 4 characters
    • Constraint: value must be “TGL ”
  • Date
    • Data Type: Date
    • Format: MMDDYYYY
  • Amount1
    • Default Amount: 0
    • Data Type: Numeric / Currency
    • Data Size: Digit (10,2) / XXXXXXXX.XX
    • Note: The amount being added to this GL account
  • GL1
    • Data Type: Character / String
    • Data Size: 25 characters
    • Constraints
      • Must be in the set (a-z | A-Z| 0-9)
      • Must be an existing GL account in the budget to which this transaction belongs (Town, School District)
  • Purpose / Reason
    • Data Type: Character / String
    • Data Size: 150 characters
    • Note: Denotes the reason for the transfer of funds from one GL account to another
  • Amount2
    • Default Amount: 0
    • Data Type: Numeric / Currency
    • Data Size: Digit (10,2) / XXXXXXXX.XX
    • Note: The amount being added to this GL account
  • GL2
    • Data Type: Character / String
    • Data Size: 25 characters
    • Constraints
      • Must be in the set (a-z | A-Z| 0-9)
      • Must be an existing GL account in the budget to which this transaction belongs (Town, School District)

Other Transactions

There are three outstanding transactions that the above main transaction types do not cover:

  • The occasion where a Revenue amount would be split among different GL accounts. This can be handled by either:
    • Extending the Revenue record type with multiple GL accounts / amounts at the end of the record similar to how the GL Transfer record is structured
    • Creating a new sub-transaction record type, “REVM” that would be similar to the REV record but would reference a new additional field called “Revenue Transaction ID” but with a different GL account and amount.
  • The very real happening where a PO has multiple PO line items; similar scheme could be applied here as well. In either case, the total of the PO Line item amount MUST be equal to the PO total.
  • The third would be PO Change orders – where lines are deleted, new ones added, existing lines have quantity, pricing , or scheduled release changes

Discussions about these three realities should happen based on the capabilities of the respective systems.

Respectfully,

David “Skip” Murphy
Vice-Chair, Gilford Budget Committee

 

Appendix A: Authorizing Statutes:

CHAPTER 32
MUNICIPAL BUDGET LAW

Preparation of Budgets – Section 32:4

32:4 Estimate of Expenditures and Revenues. –All municipal officers, administrative officials and department heads, including officers of such self-sustaining departments as water, sewer, and electric departments, shall prepare statements of estimated expenditures and revenues for the ensuing fiscal year, and shall submit such statements to their respective governing bodies, at such times and in such detail as the governing body may require.

Section 32:10 – Expenditures

32:10 Transfer of Appropriations. –

I. If changes arise during the year following the annual meeting that make it necessary to expend more than the amount appropriated for a specific purpose, the governing body may transfer to that appropriation an unexpended balance remaining in some other appropriation, provided, however, that:

(a) The total amount spent shall not exceed the total amount appropriated at the town or district meeting.
(b) Records shall be kept by the governing body, such that the budget committee, if any, or any citizen requesting such records pursuant to RSA 91-A:4, may ascertain the purposes of appropriations to which, and from which, amounts have been transferred; provided, however, that neither the budget committee nor other citizens shall have any authority to dispute or challenge the discretion of the governing body in making such transfers.

Section 32:16 – Budget Committee

32:16 Duties and Authority of the Budget Committee. – In any town which has adopted the provisions of this subdivision, the budget committee shall have the following duties and responsibilities:

I. To prepare the budget as provided in RSA 32:5, and if authorized under RSA 40:14-b, a default budget under RSA 40:13, IX(b) for submission to each annual or special meeting of the voters of the municipality, and, if the municipality is a town, the budgets of any school district or village district wholly within the town, unless the warrant for such meeting does not propose any appropriation.
II. To confer with the governing body or bodies and with other officers, department heads and other officials, relative to estimated costs, revenues anticipated, and services performed to the extent deemed necessary by the budget committee. It shall be the duty of all such officers and other persons to furnish such pertinent information to the budget committee.

Section 32:22 – Review of Expenditures

32:22 Upon request by the budget committee, the governing body of the town or district, or the town manager or other administrative official, shall forthwith submit to the budget committee a comparative statement of all appropriations and all expenditures by them made in such detail as the budget committee may require. The budget committee shall meet periodically to review such statements. The provisions of this section shall not be construed to mean that the budget committee, or any member of the committee, shall have any authority to dispute or challenge the discretion of other officials over current town or district expenditures, except as provided in RSA 32:23 (Initiation of Removal Proceedings)

The above quotes from Chapter 32 of the Municipal Budget Law are the authorizing statutes that form the basis of this document.
Version 1.0
2018-02-08

by Skip

Co-founder of GraniteGrok, my concern is around Individual Liberty and Freedom (and how Government is taking that away from us). My fight, from a Conservative (with small “L” libertarian leanings) and evangelical Christian perspective, is with the Progressives that are forcing a collectivized and secular humanistic future upon us. As TEA Party activist, citizen journalist (and pundit!), my goal is to use the New Media to advance the radical notions of America’s Founders back into our culture again.