Budget Spreadsheet Errors

Jenkintown Matters,

The following information is the result of our examination of the Borough’s most recent budgets, identifying significant and minor errors. These discrepancies do not necessarily suggest wrongdoing within Borough Hall. After discussions with Borough officials, the mistakes found here seem due to human error and a failure to adhere to proper data management procedures, such as failure to lock spreadsheets before distribution. This can cause multiple versions of the original data. Without a clear picture of its accounting, it creates points of failure that leave the Borough vulnerable to malfeasance.

This is an ongoing investigation.

All tables link to larger versions of these screen shots.

Act 511 Taxes Miscalculation

ACT 511 Tax is a Per Capita Tax that can be levied at a maximum rate of $10.00. It can be levied by a municipality and/or school district on residents and businesses. If both do so it is shared 50/50. In current spreadsheet for the Borough’s General Fund, The Act 511 Taxes for 2018 is shown as $1,745,046. The actual amount is $1,725,646. The two tables below show errors in the Borough’s arithmetic, leading to an overstatement of $19,400. Unfortunately, this error was carried over into the following year. We have to assume this is an Excel error, possibly due to the inclusion of another cell into this calculation.

Debt Service Carryover Variance

The two screens below show differing ending balances for the Borough’s debt fund as of 2020, or the amount of debt it carries for that period. The first table shows an ending balance in 2020 of $5,685,999 yet the beginning balance for 2021 is $109,807.   

2020 Ending Balance  – $5,685,999

2021 Beginning Balance – $ 109,807

Variance – $5,576,192

The reason for this error is that in 2015, the Borough borrowed $2,789,603 for the Leedom Street parking lot completed in 2010. Normally when a loan is added, we see a Revenue line item (Proceeds of General Long Term Debt) and the Expense line item (Debt Principal). These figures typically cancel each other out. The Borough’s Excel file ended up adding this transaction to the Revenue twice, likely by adding both the line item and the sum, doubling the the fund amount. This amount was then carried forward until 2020, when it caught and corrected the error.

In a discussion with Borough Manager George Locke, he did acknowledge the error in the Excel sheet formula that has since been corrected. In an email we received from Mr. Locke, he writes:

This exaggerated Fund Balance in the 20 Debt Fund was caused by a excel cell error in 2015. Once corrected the fund balances for 2018 – 2023 are shown below.

201820192020202120222023
$131,802$139,359$127,953$109,807$39,299$175

Police Pension Actuals vs. Trial Balance

The Borough’s budget for 2024 also carries over a discrepancy in the Police Pension budget. In the line item for Gain/Loss on sale of investments (account number 60-343-015) shows an end-of-year actual balance of $405,788.

As we see in this Trial Balance generated for us by the Borough, the number carried over and reported in is $1,183,719.

The Trial balance was a report generated from the Borough’s accounting system for 2023. A trial balance is a financial report showing the closing balances of all accounts in the general ledger at a point in time. Creating a trial balance is the first step in closing the books at the end of an accounting period. 

The table below shows the Trial Balance for Year end 2023, with a difference in the Gain/Loss on sale of investments of $777,931.

These figures highlighted in each table are supposed to match.

We bring these to everyone’s attention not because we want to blame anyone in particular for making these errors, but a borough with a budget over $5 million should know how to properly use an Excel spreadsheet. When we find mistakes like these, can we really trust what they’re telling us if their own numbers are not correct?

Analysis News