Idea #340 -- Budgeting/accounting Forecasting Software

Idea #340 – Budgeting/accounting Forecasting Software

Report Status: Fully Reviewed

Researched by: Vicki Amalfitano and Matt Gasbarro

Original Idea as Submitted

The Town and School Department should investigate purchasing a budgeting/accounting and forecasting software solution. All the current work is done in Excel and it is very cumbersome and takes significant effort to update as well as generate tables and graphics. On the Town side, the Excel 5-year financial model takes a significant time to update because of all the formulas and links and is prone to error when updating. On the Schools side, the Excel budget book is also difficult to update and prone to error but also does not allow for the easy creation of tables and other visuals. There would be upfront costs but that would be paid back in significant time savings as well as the ability to provide much more transparency with an easy-to-use reporting and visitation tool. Finally, a software solution would allow for more sophisticated budgeting and forecasting such as being able to exactly and easily report the turnover costs of existing staff and in turn produce more accurate budgeting.

Other ideas included in this report

None

Idea intent

This idea focuses on replacing with contemporary financial planning software the time-consuming and cumbersome Excel workbooks used by the Town and the School Department.

Weighted Final Score: 53 (Financial Impact:1, Operational Impact: 9 , Time Scale: 2 , Ease of Implementation: 3)

Background Information

The current Excel workbooks are used only for financial projections and modeling, and were developed by the Collins Center, which has done this same work for other towns similar to Belmont. There are two Excel workbooks. The one for the Town is the responsibility of the Town Administrator, and the one for the School Department is the responsibility of the Superintendent. Accuracy is maintained through version control and limited editors of the workbooks.

For the Town, the Town Administration Office is the chief user of the Excel workbook. For the School Department, it is the Superintendent’s office. Entering the data and formulas which drive the calculations in the workbooks must be done manually, as well as running routine and special reports. Some of these reports, particularly for expenditures, originate from MUNIS, are exported to Excel, and the information is reformatted into meaningful totals and subtotals.

Like several towns in Massachusetts, Belmont uses MUNIS, an enterprise resource planning (ERP) software offered by Tyler Technologies. The vendor also offers numerous add-on modules to augment the ERP many of which have been purchased by the Town. One module not procured is a Budgeting module which offers financial projections. However, trials by the Town Administration Office have revealed the module not to be user-friendly and the Superintendent’s Office’s evaluation found specific issues such as:

There are different needs between creating a budget for next year vs. projecting costs within the current year
Teachers on leave this year (which would be excluded from an intra-year projection; but included in a next year’s budget calculation) and the reciprocal of substitute teachers filling in this year for the permanent teachers on leave.
Seasonal stipends, such as athletic coaches and fine arts and club advisors (e.g., an intra-year projection in the winter would need to exclude fall coaching stipends; but they would need to be included in a calculation for next year’s budget)

The Town Administration Office is not aware of other projection software that other towns are using, except for the Excel workbook that the Collins Center created through their “Best Practices” Program.

Purchased MUNIS modules
Accounting (GL,BG,AP)
Accounts Payable
Accounts Receivable
Applicant Tracking
CAMA Bridge
Cash Management
Central Property File
Fixed Assets
General Billing
General Ledger
HR Management (suite of modules)
MA Excise
MA Tax
MA Tax Title
MapLink GIS Integration
Project & Grant Accounting
Purchase Orders
Requisitions
Role Taylored Dashboard
Student Activity Accounting
TylerForms

The Financial Task Force II produced a list of “Long Range Strategic Planning Recommendations” as part of its 2021 Final Recommendations report. One such recommendation was adopting a budgeting and forecasting software that would be more flexible and efficient than the current Excel workbooks. The process for choosing a software package is the same procurement process the Town must follow for all goods and services. It would entail soliciting bids from companies that provide the software solution and choosing the product that best meets the Town’s needs at the best price. The Town Administrator’s Office and the Accounting Office would be the major stakeholders in this process for the Town as well as the Superintendent’s Office for the School Department.

The MUNIS Budgeting module, or another software option, would require funding for purchase/licensing and annual fees, as well as staff training and data migration.

Recommendations

The Town Administrator’s Office and the School Department should investigate modernizing their systems for financial forecasting. A MUNIS module or software which integrates with MUNIS would eliminate manual data entry and report generation, as well as errors inherent in this approach.

Next Steps

Since the Town is already using MUNIS for other finance functions, the Town Administrator’s Office should revisit the latest version of the MUNIS Budgeting module via a demonstration and check on performance with other towns that have licensed this module.
At the same time, the Town should reach out to towns to see if they are using systems other than MUNIS or the Collins Group Excel workbook, and review this in light of the MUNIS option.
Based on this information, the Town Administrator’s Office and School Department should develop a scope of services for moving to financial projections software and begin the required procurement process.