How do you develop a Financial Spreadsheet? Listed below are steps that a city may follow to build a Financial Spreadsheet.

The first step involved building a spreadsheet that included a financial history of the city’s water and sewer funds for the previous two to three years (or longer), the current year, and projections for future years. The history provided a foundation for projections. The spreadsheet format should be similar to the Statement of Revenues, Expenses and Changes in Fund Net Position as shown in the city’s audit. It should include revenues, operating and maintenance expenses, depreciation, interest earnings and interest expense. Grants and monies received as contributed capital are now included on the income statement as non-operating revenue. Contributed capital includes items such as tap fees, payments from developers, and any other fees paid to directly offset the capital costs of the utility. Not included on this statement are principal payments on long-term debt and the amount of capital projects completed. Those two items will be included in the cash flow analysis.

Next, staff dealt with the current year. The finance director took the current month-end financial statements and projected them to year end. Some items were fixed and pretty easy to project (i.e., depreciation and debt interest). For the variable items, revenues and expenses, the director looked to the current year’s trends and the history of the last few years. Events such as weather-related usage and large customers being added or deleted had to be taken into account. The director reviewed expenses with the water/sewer manager by asking questions such as: Is there a large one-time expense that affects any of the trends? Are there areas we know will encounter a significant increase or decrease in revenues or expenses? For example, if you expect that the amount the city pays for employee health insurance to increase 10 percent next year, that factor impacts projections. Or, perhaps a new area has been put into service and the city expects to see a greater than normal increase in revenues. The staff considered all of these factors to project the current year.

Next, they projected future revenues and expenses. They considered the history and looked at growth of revenues. They decided on a modest 1 percent growth rate for water sales and a 0.5 percent growth rate for sewer service sales. Realistically, expenses continue to rise. The staff would likely receive cost-of-living increases. Insurance costs would rise faster than inflation rates. After consideration, staff settled on a ~3 percent annual increase in operating and maintenance expenses.

“The dreaded D word: depreciation.”
Often, one of the largest single expenses is depreciation. Since depreciation is the annual expensing of the utility’s assets over their useful life, staff looked to the depreciation schedule for assets on hand and to the capital projects costs projections. Using projected costs as a guide, staff developed a depreciation schedule for the addition of these new assets. They knew that it was likely that all of the projects would not be completed in the time period listed. But they also knew that other projects would take their place. Projections are made using the best information available at the time, realizing that some changes are likely. Armed with the depreciation schedule for assets on hand plus the projected depreciation expense of new projects, the staff could now project depreciation expense.

“Why is the operating income important?”
Operating income is the difference between operating revenues and operating expenses. If the water and sewer system is not realizing sufficient revenues to fund operating expenses and depreciation there is a serious financial problem. With the accounting change that makes contributed capital a current revenue, cities may find themselves showing healthy changes in net assets when, in fact, there are not enough revenues to fund expenses. Since grants and other similar monies can be used only for the capital projects they are intended to build, grants can provide no infusion of cash to pay ordinary operating expenses.

Any City anticipated receiving $768,000 in grants next fiscal year, and the staff realized that the grants should be included in the projection. They made a note to bring operating income to the attention of the governing body. Any City’s spreadsheet for water and sewer revenues and expenses is shown in Table 3.

 

Net Assets has been changed to Net Position per GASB.