Shelby County Clerk Report 2024

Shelby County Clerk

DiviSion of

loCal Government auDit

Jason E. Mumpower

Comptroller of the Treasury

marCh 21, 2024

Revenue Report Reconciliation and Operational Analysis

SHELBY COUNTY CLERK

Revenue Report Reconciliation and Operational Analysis

EXECUTIVE SUMMARY

Following the failure to submit accurate and timely revenue reports by the Shelby County

Clerk to the Shelby County Trustee’s Office, which was jeopardizing the ability of the Shelby

County Commission to prepare and submit an accurate budget for fiscal year 2025, members

of the Division of Local Government Audit (LGA) performed revenue reconciliations for the

period July 1, 2023 – February 29, 2024.

During this reconciliation process, LGA identified the portion of the county wheel tax which

was collected due to a resolution approved by the Shelby County Commission on June 26,

2023, which increased the county wheel tax by $25. This additional $25 was to be allocated

to the Debt Service Fund for payment on county infrastructure needs, including but not

limited to the rebuilding of the campus of Regional One Health.

LGA was able to reproduce the monthly County Clerk revenue reports for July 2023 through

February 2024. The revised reports included all revenue activity, a breakout of the $25

portion of the wheel tax, along with other fees and commissions that had been collected. The

County Clerk submitted these revised reports to the Shelby County Trustee on March 11,

2024.

In addition, LGA performed a limited review of the procedures of the County Clerk’s Office,

to the extent necessary, to provide recommendations for improvement and to ensure that a

process would be implemented to properly submit revenue reports going forward.

PROCEDURES PERFORMED

1. Determined revenue collected due to the $25 increase in wheel tax.

LGA reviewed the title application and renewal balance reports generated through

the County Clerk’s Business Information Systems (BIS) software to obtain a count of

the number of wheel tax transactions recorded at each possible collection rate.

According to the previous fee schedule, wheel tax rates were $50 for Passenger

Vehicles, $20 for Motorcycles, $80 for Commercial Vehicles, and $25 for Non-Profit

Vehicles. On July 1, 2023, these amounts were to be increased to $75, $45, $105, and

$50, respectively; however, the County Clerk’s Office did not officially implement

these changes until August 1, 2023. The County Clerk advised LGA that she was

unaware that the county commission had passed a resolution to increase the wheel

tax until July 17, 2023, and it took her office a couple of weeks to implement the

change. Based on the wheel tax transaction listings, LGA determined that all

collections for $75, $45, or $105 included the $25 increase. The only exception to this

were $50 transactions which could have applied to either a passenger vehicle under

the old wheel tax rate or a non-profit vehicle under the new rate. An employee of the

County Clerk’s Office was able to generate additional reports to assist LGA to further

break down the $50 amounts to determine which of those transactions included the

$25 increase. There were also some prorated amounts included on the reports due to

partial month collections, which LGA was able to separate based on a weighted

average calculation.

LGA determined the total wheel tax collections from July 1, 2023, through February

29, 2024, net of commissions was $29,942,173. During this timeframe, $21,225,589

was collected for the regular wheel tax and $8,716,584 was collected due to the $25

increase.

On March 12, 2024, the County Clerk informed LGA that she had been in touch with

BIS and that they were in the process of updating her software to allow the wheel tax

increase to be identified separately. Once that process is completed, the County

Clerk’s system will automatically separate the $25 amount into a different account.

LGA provided the county clerk with a template that can be used to separate the

amounts for March, if necessary.

2. Performed revenue report reconciliations.

LGA determined through conversations and observations that the County Clerk’s

Office is not posting all receipt transactions to the BIS software’s general ledger. The

BIS system reflects receipt transactions that are processed through one of five reports

(i.e., business license, marriage license, renewals, title applications, and

miscellaneous receipts). LGA reviewed these five reports for each month to determine

how much revenue had been processed in the BIS system. LGA also reviewed bank

records to look for other revenue activity that was not receipted in the system. It was

determined there is a monthly ACH that is received from the State of Tennessee which

includes Shelby County’s portion of some of the fees collected that should have been

receipted along with the interest from the Trustee’s statement of banking activity

monthly report. Once LGA identified all revenue activity, they were able to calculate

the fees and commissions for each revenue collected.

On Monday, March 11, 2024, LGA met with the County Clerk and some of her staff to

discuss the revenue amounts LGA had calculated, including the breakdown of wheel

tax collections. The staff asked questions and appeared to understand and to be

satisfied with the reports. The revised revenue reports were submitted to the Trustee

later that day. LGA also provided the County Clerk with a revenue report template

that can be used in the short term until a more appropriate long-term solution can be

implemented.

3. Obtained an understanding of how transactions are processed in the County

Clerk’s Office and identified ways they can provide accurate and timely

revenue reports for the Trustee.

A. Analysis of bank transactions

LGA reviewed the County Clerk’s deposit and disbursement bank accounts.

All receipted funds, including credit card payments, are to be deposited into

the deposit account. The only funds currently disbursed from this account are

the ACHs to the State of Tennessee for title and registration fees. The

disbursement account processes all other disbursements of the office. This

account is primarily used for refund checks, payments to the State of

Tennessee, and payments to other entities such as the Tennessee Organ Donor

Awareness Foundation.

Both bank accounts maintain a $0 balance at the end of each day. Collections

in the deposit account are swept to an account maintained by the Trustee. The

Trustee temporarily places those funds into Fund 540, which acts as a clearing

account for the County Clerk’s banking activity. These funds are invested

throughout the month. When a monthly revenue report is submitted by the

County Clerk, the Trustee will then receipt the previously swept funds which

moves those funds out of Fund 540 and allocates them to the appropriate

county fund(s) and account(s) based on the amounts reported by the County

Clerk. LGA reviewed all bank statements from July 2023 through February

2024 for both accounts.

B. Receipts to Deposits

LGA tested a sample of daily collection reports in September 2023 and

December 2023 to understand the flow of funds and to determine how much

time it typically takes for collections to be deposited into the office bank

account. LGA observed from the sample that it generally takes two to three

business days from the collection date to the deposit date for funds to be

deposited into the County Clerk’s bank account. This complies with the three-

day deposit law as outlined in Section 5-8-207, Tennessee Code Annotated.

However, LGA had to rely on paperwork maintained by the County Clerk’s

staff to match the collections to the deposits on the bank statements. LGA was

unable to reconcile the deposits between the BIS general ledger and the bank

statements because the County Clerk’s Office generally checks out their daily

collections by collection site. The total amounts are then automatically posted

to the general ledger by each application (i.e., marriage license, business tax,

renewals, etc.). The deposits typically go to the bank by collection site but may

include any combination of sites in a single deposit, and the office may have

anywhere from five to ten deposits per day. Due to this process, it is extremely

difficult to match the collections to the bank deposits. LGA also noted that the

County Clerk’s Office does not maintain the cash in bank transactions in the

BIS software. When the amounts for each application are automatically posted

to the general ledger they are posted to the cash on hand account. However,

when the deposits are prepared, no entries are made in the BIS software to

post the deposits and move the collections from the cash on hand account to

the cash in bank account. As a result, the balance continues to increase in the

cash on hand account and is inaccurate.

C. Disbursements

The County Clerk’s disbursements are not being processed in the BIS software.

They are handled through their Sage software. The office also utilizes a

positive pay service through their bank, which allows them to submit a list of

checks issued to the bank. Any checks presented for payment against the bank

account that do not match the provided list would have to be approved for

payment. LGA asked employees of the County Clerk’s Office if they reconciled

this check list, and it does not appear anyone has attempted to reconcile the

list in several years. An accurate list of outstanding items is necessary to

balance the activity of the office back to the Trustee’s Fund 540 account;

therefore, LGA manually traced all checks issued to the checks paid on the

bank account from July 1, 2023, through February 29, 2024, and prepared a

list of outstanding checks. LGA also noted material differences between the

positive pay unpaid listing and the disbursements that were outstanding. This

was due to many disbursements, which have either cleared the bank or have

been voided, but are still included on the positive pay list. The positive pay

listing as of February 29, 2024, shows an unpaid balance of $52,261,862;

however, LGA estimates that the actual outstanding checks total to be

approximately $43,545. LGA identified these differences, and a list was

provided to the County Clerk’s Office.

D. Reconciliation to Trustee

LGA attempted to reconcile the activity in the Trustee Fund 540 through

February 29, 2024. They determined an estimate of all outstanding items,

based on the available information, and considered amounts due to be paid the

following month, as well as amounts from the revised revenue reports that the

Trustee posted in March 2024. There appears to be an unidentified balance of

approximately $3,523,229 that is being carried in this fund. This balance

likely represents a combination of revenues that were collected in prior years

but have yet to be reported and allocated correctly along with posting errors

that have gone undetected due to the lack of reconciliations.

E. Sample of wheel tax transactions at old and new collection rates

While separating the $25 wheel tax increase, LGA observed on the monthly

title application and renewal balance reports generated from the BIS software

that there were title application and tag renewal transactions that occurred

after the clerk’s implementation date of August 1, 2023, but were processed at

the previous rates. LGA inquired about those transactions. The County Clerk’s

staff explained that some of these transactions were due to title applications

with purchase dates prior to August 1, 2023, and tag renewals with expiration

dates prior to August 31, 2023. These late title applications and tag renewals

were not to be charged the $25 additional wheel tax. Since LGA relied on the

rates that had been charged to determine which transactions included the $25

increase, a sample of 25 transactions were tested to determine that the proper

rates had been collected. The correct rates were collected for 23 of 25

transactions. One of the exceptions related to a passenger vehicle and was

processed at the old rate of $50 instead of $75. The other exception was for a

motorcycle/scooter/ATV renewal that was processed at the old rate of $20

instead of $45. The number of transactions that are being processed at the old

rates each month do appear to be decreasing. Based on the sample tested, any

potential errors would be considered nominal considering the volume of title

application and renewal transactions performed in the office.

4. Obtained a general understanding of the operations and internal controls of

the office of the Shelby County Clerk to determine how their processes may

affect the transactions in the BIS software.

A. BIS defines daily transaction reports as invoices. LGA observed during their

review that 34 invoices in the BIS software were open at one time, including

five dating back to 2023. Test work identified an instance where the office’s

original reports for a specific invoice did not match the reports regenerated for

that invoice at the time of LGA’s review. Additional cash and check collections

in the amount of $2,977 were receipted to that invoice after the daily work was

balanced. Upon review of the application records, LGA observed that invoices

remained open up to 31 days past the invoice date before the first initial

closing. Subsequent reopening and closing of invoices are common practices in

the County Clerk’s Office, often months after the invoice date. It was observed

that some invoices had been reopened up to 28 times.

B. Upon review of the application records, it was observed that employees

routinely delete their own transactions. LGA also observed that the

authorization feature in the BIS system is not being utilized. If utilized, the

employee who entered their credentials to delete the transaction is considered

as “authorizing” the deletion and would be recorded on an audit report.

RECOMMENDATIONS

Based on LGA’s limited review of the activity of the Office of Shelby County Clerk, we have

the following recommendations:

1. Receipting Process

All monies received by the office should be receipted using one of the five

applications of the BIS system. This would include the interest allocated by the

Trustee’s Office and the state ACH deposits which are currently not being

accounted for in the system. Collections should also be receipted as soon as they

are received. There should be no unreceipted collections in the office. Maintaining

a complete and accurate accounting for all collections is necessary for the County

Clerk’s Office to be able to perform accurate monthly reconciliations to ensure that

all funds are accounted for.

2. Disbursement Process

The official general ledger should accurately reflect all financial transactions of

the County Clerk’s Office; therefore, LGA recommends that all disbursements be

reflected in the BIS software. A list of outstanding disbursements should be

prepared each month. The positive pay listing should be maintained regularly,

and the unpaid balance report should match the actual list of outstanding items.

There are currently checks as old as November 2010 included on the positive pay

listing. The County Clerk’s Office should work with the bank to correct any errors.

The positive pay listing as of February 29, 2024, shows an unpaid balance of

$52,261,862; however, LGA estimates the actual outstanding checks total to be

approximately $43,545. After the appropriate corrections are made, any remaining

outstanding checks that are older than one year should be reported and paid to

the state as unclaimed funds as required by Section 66-29-113, Tennessee Code

Annotated.

3. Reconciliation Process

The office currently does not perform any documented reconciliations of the

banking processes regarding deposits, bad checks, or disbursements. It also

appears that there are no formal reconciliations of the items that are swept in and

out of the County Clerk’s bank accounts to the Trustee’s Fund 540 account.

Specifically, management should develop a methodology that allows them to

determine that deposits are accurate, complete, and made in a timely manner.

Additionally, credit card transactions should be verified that they were processed

and received by the bank in a timely manner. This would also assist in

determining which items have been collected in the month but not deposited into

the bank until the subsequent month. If all information is properly recorded in the

BIS general ledger, and accurate listings of outstanding items are prepared, then

the financial activity reports of the office should reconcile with the Trustee’s Fund

540 account once the reconciling items are considered. Also, the office should

attempt to identify prior errors in what has been reported to the Trustee so that

the unallocated and unidentified funds of approximately $3,523,229 can be

allocated appropriately.

4. Reporting Process

Revenue reports submitted to the Trustee’s Office should include all activity

remitted to Shelby County, including a breakdown of the wheel tax amounts. The

reports should be accurate and submitted to the Trustee’s Office by the tenth of

each month as required by a resolution approved by the Shelby County

Commission.

5. Internal Controls Process

The County Clerk is using a signature stamp to sign checks that are issued from

the disbursement account. A check registry is maintained, and the County Clerk

signs the listing, but the actual checks are stamped. Her assistant also initials

each stamped check. While it does appear that there are some internal controls in

place, LGA recommends that the County Clerk’s Office discontinue the use of the

signature stamp and that the stamp be destroyed. All checks should be signed by

the County Clerk or a designated individual in the office.

The office should implement controls for the opening and closing of invoices to

ensure a minimal number of invoices are open at one time. The office should

develop a policy for handling transactions inadvertently receipted to the incorrect

day’s invoice, and this should be communicated to all employees. Additionally, a

procedure should be in place for the re-opening and subsequent closing of invoices

for scanning backlogged documentation. The office should consider further

restricting the number of users who can open and close invoices. This capability

should be restricted to only those employees whose job responsibility dictates they

can open, close, or re-open invoices.

Segregation of duties should be improved as it relates to deleting transactions. The

act of deleting one’s own transactions is highly discouraged. The office should

implement the authorization feature in the BIS software and attempt to limit the

number of users who can delete transactions. This will require identifying

employees at each site who will oversee this functionality, and deletions should

only be performed by those employees whose job responsibilities dictate they

perform deletions.

The BIS software used by the office allows users to perform deletions, alterations,

and voids after transactions are receipted. Several audit logs generated by the

system track these changes. Because these logs provide the only audit trail of

these transactions, the logs should be routinely reviewed for inappropriate

activity. Documentation acknowledging the audit log reviews should be

maintained.

6. Other General Recommendations

The office should reach out to BIS and the County Technical Assistance Service

(CTAS) to request assistance as they work through the implementation of these

recommendations. BIS can assist in getting all transactions properly separated

and posted in the software, and CTAS can assist with the reconciling and internal

control issues noted.

The County Clerk’s Office, as a normal part of business, has a large number of

insufficient funds checks (bad checks) that are returned by the bank for

insufficient funds or bank chargebacks for e-checks. The County Clerk’s Office

attempts to collect the bad checks; however, a receivable for any bad checks is not

posted to the general ledger. When a customer comes into the County Clerk’s

Office to pay a bad check, a miscellaneous receipt is generated for the amount

collected. This amount is receipted to a receivable account in the BIS software;

however, the initial entry to post the receivable for the bad check is not included.

Therefore, the office cannot provide an accurate listing of bad checks receivable or

the amount of bad checks outstanding.

CONCLUSION

The Shelby County Clerk’s Office has several operational and internal control deficiencies.

LGA recommends the County Clerk seek assistance and training to improve the service being

provided to the citizens of Shelby County while ensuring the integrity and accuracy of all

funds being collected and disbursed by the office.