Author: Financial Management Resource Bureau
As the largest portion of the municipal annual spending plan, budgeting salaries and wages are essential. However, equally important is the ability to cost out and forecast the implications of any increases granted by promotion, individual contract, compensation plan, collective bargaining agreement, or personnel bylaws/ordinances. One way to accomplish these three tasks—budgeting, costing and forecasting—is to establish a position cost spreadsheet.
A position control spreadsheet is used to organize and track budgeted positions detailed by employee (or groups of employees), by department to determine contractual or payroll-related costs. By developing and maintaining a position cost spreadsheet, officials can track staffing levels and ensure proper funding for annual budgets, while gaining a better understanding of what is driving salary and wage increases. Once established, the position control spreadsheet can then be used to forecast future costs.
At a minimum, the spreadsheet should identify the contract or collective bargaining agreement schedule tied to each position and calculate the base salary with the number of hours per week on a 52.2-week basis. Using 52.2 weeks in the calculation accurately reflects the calendar year thereby avoiding the need to make year-end transfers for any shortfalls or, worse, funding a 53rd week every five years. To reflect all costs, the spreadsheet should also include all other compensation such as education incentives that could range up to 25% of the base salary, along with overtime, holiday premiums, shift differentials, and various stipends.
Position Cost Spreadsheet Example
Once the initial year is constructed by department and reconciled to the current budget, future years can be added. Any forecasted years should reflect only increases by position associated with a particular contract, collective bargaining agreement, non-union/classification plan, or to comply with the community’s personnel bylaws/ordinances. These include known provisions such as negotiated cost of living adjustments (COLA’s), as well as steps and longevity adjustments based on a scheduled or anniversary date. In addition to determining the total for each department by fiscal year, we recommend that pay categories be subtotaled by type such as overtime, education, longevity, and other amounts to clearly demonstrate the current and projected costs.
Position Cost Forecast Example
Using the position cost spreadsheet will enable a community to better forecast payroll related costs in relation to the total operating budget. The summary data also allows officials to consider different scenarios with the compensation subject to COLA by employee category to project the potential future financial impacts when negotiating new contracts and collective bargaining agreements. As with the other components of a financial forecast, the position cost should be regularly reviewed and updated with new information, when successor agreements are ratified, other changes occur, and after the close of a fiscal year.
COLA Financial Impact Analysis Example
Establishing a position cost spreadsheet for the first time can be a time-consuming process, but it is a critical tool in budgeting, forecasting, and understanding the largest expense a community faces.
Helpful Resources
City & Town is brought to you by:
Editor: Dan Bertrand
Editorial Board: Tracy Callahan, Sean Cronin, Janie Dretler, Emily Izzo, Christopher Ketchen, Paula King, Jen McAllister, Jessica Sizer and Tony Rassias
| Date published: | February 20, 2025 |
|---|