HomeMy WebLinkAboutReport
E,ECEIVE~
A~JG 2 6 2003
City of Meridian
City Clerk Office
CITY OF MERIDIAN
UTILITY BILLING SYSTEM -CONTROL
REVIEW ASSESSMENT
Meridian, Idaho
August 8, 2003
EFFERSONV(/ELLS ~.
J INTERNATIONAL
1125 17th Street, Suite 2300
Denver, Colorado 80202
Table of Contents
Section A Executive Summary
Section B Rrocesses, Procedures and Controls Review
Section C SySze~n Security
Section ~ Misappropriation of Assets
'-~
SECTION A -EXECUTIVE SUMMARY
BACKGROUND
The City of Meridian ("City") provides water, sewer and garbage services to the
residence and businesses in Meridian. The customer base and users of these services has
increased in the last six years from approximately 6,000 customers to 15,000. In June
1999, the City implemented the Caselle operating software as its utility billing system.
Caselle is separate from and is not integrated with the accounting software package,
which is MIP.
SCOPE OF WORK PERFORMED
Jefferson Wells International ("Jefferson Wells") was engaged by the City of Meridian
through the Meridian City Council on June 17, 2003 to perform a controls review and
assessment of the Municipal Utility Billing System ("MUBS") and to determine the cost
effectiveness of embarking on an in depth (forensic) investigation to fully quantify any
suspected loss of prior misappropriations. We began our audit test work on June 18,
2003, which was completed on July 3, 2003.
OBJECTIVI~.S
The audit objectives were as follows:
• To determine whether the current billing environment has adequate business
process controls, system controls and segregation of duties.
• To identify internal control weaknesses based upon the results of the review
performed.
• To make suggestions for internal control improvements based upon the results of
the review performed.
• To review and analyze available records and determine the cost effectiveness. of
embarking on an ire depth (forensic) investigation to fully quantify the suspected
loss.
OVERALL CONCLUSION
Based on our review, the City of Meridian appears to have in place many of the systems,
processes, procedures and controls .required for a utility billing system of its size and
complexity. We received full cooperation from the City personnel we worked with and
appreciate the time provided by the City's employees.
Our review included an evaluation of the MLIBS process and related controls, the system
controls and security and a limited review of the potential misappropriation of assets.
Our process and controls review indicated that there are control weaknesses due to the
poor segregation of duties, as well as a lack of documentation indicating functions have
been performed and a lack of documentation indicating reviews have been performed.
This is further discussed in Section B of this report.
Our review also uncovered general control weaknesses regarding the security over the
computer system. The City uses specific municipal management software modules from
Caselle, Inc. for its utility billing system software. Our testing indicates that there are
security issues primarily related to protecting the applications database and in application
password controls. The results of the systems security review are further discussed in
Section C of this report.
We reviewed a limited number of selected transactions for irregularities. We did not
discover any unknown misappropriation of assets. However, this does not mean that
additional misappropriation of assets did not occur. As part of our review, we did
identify a number of areas where potential misappropriations could have occurred. See
Section D of this report for further details.
It should be noted that the City has worked to improve the processes, controls and
procedures over the last six months.
4
SECTION B -PROCESSES PROCEDURES AND CONTROLS REVIEW
BACKGROUND
The City of Meridian provides water, sewer and garbage services to the residents and
businesses in Meridian. The customer base and users of these services has increased in
the last six years from approximately 6,000 customers to 15,000. In June 1999 the City
of Meridian implemented the Caselle operating software as its utility billing system.
Caselle is separate from and is not integrated with the accounting software package,
which is MIP.
The City of Meridian has acquired and uses the following Modules within Caselle:
• Utility Management
• Cash Receipt
• Utility Billing
• Utility Reading
• Work Orders
As noted above, Caselle is a separate operating system from the MIP accounting system.
MIP provides the applications for payroll, check disbursements and general ledger.
Journal entries are prepared by the Accounting Department to record transactions in the
general ledger for the results from Caselle.
OBJECTIVES
The audit objectives were as follows:
• To determine whether the current billing environment has adequate business
process controls and segregation of duties.
• To identify internal control weaknesses based upon the results of the review
performed.
• To make suggestions for internal control improvements based upon the results of
the review performed.
PROCEDURES
We performed the following procedures:
• Documented the processes within the utility billing system for the following
areas:
o utility billing
o title company transactions
5
o cash receipts
o disbursements
o integration with the accounting system
Performed limited control testing relating to the procedures for selected time
periods to validate the processes.
Identified control weaknesses during our documentation and testing.
RESULTS OF REVIEW
FINDING 1: Lack of Se re ation of Duties in the Cash Recei t Process
'The City currently has five employees working in the MUBS office. Frequently,
customers will make cash payments at the MUBS office. Generally, all of the MUBS
employees can take the customer's payment, enter the payment into the customer's
account and then place the funds into a single cash drawer. Differences between the cash
drawer and transactions posted to the system cannot be identified to a specific employee
or transaction.
Recommendation:
One individual should be assigned responsibility for the front desk located at the MUBS
office. This employee would be responsible for the following:
• Collecting cash payments.
• Entering the payments into the employee's account so a receipt can be generated.
• Providing the customer with a receipt.
• Reconciling the cash box at the end of the business day.
• Preparing daily account reconciliation and signing the reconciliation form.
The cash box and receipts should then be provided to another employee who will also
reconcile the cash and checks received to the daily receipts and the funds in the cash
box. This employee will then approve the reconciliation document. The daily activity
should then be updated into the system.
FINDING 2: Lack of Manual Entrv Review
Manual entries are made into the Casell modules to invoice customers for miscellaneous
services such as false alarms, firs hydrant usage, SSC special charges and sewer usage.
The Billing Supervisor and Billing Manager make manual envies into Caselle to record
these transactions, however, the transactions and supporting documents are not reviewed
and approved by someone other than the preparer.
6
Recommendation:
All transactions should be reviewed and approved by someone other than the originator
of the transaction and the review should be documented.
FINDING 3: Turnover in the MUBS De artment
Based an information provided to us, the employee turnover rate in MUBS from 1998
through 2002 has averaged 31 % per year. The lowest turnover rate was 17% while the
highest was 50% in 2001. This turnover rate is high for this type of operation. Turnover
decreases the efficiency of the operations and increases the potential for errors.
Recommendation:
We recommend the City attempt to determine the possible root cause of the excessive
turnover and take corrective action.
FINDING 4: Lack of Controls within the Caselle Software
When an employee makes an entry into Caselle, the system does not have the capability
to determine who made the entry into the system. In addition, when transactions are
made to record the miscellaneous services (see Finding 2), the system does not allow the
entries to be entered as a batch. This requires employees to spend more time reviewing
the accuracy of the transactions.
Recommendation:
At this time Caselle does not have the capability to identify the employee who made an
entry into the system. Caselle is considering issuing a new version of Utility Billings,
which would provide this feature, however there is no definitive date on when this may
be released. In the meantime, we recommend that the City continue to regularly review
the user requirements.
Caselle has also indicated that there are no immediate planned changes to the software
that would allow for batch processing of the miscellaneous services. This will continue
to require an adequate review of the entries made for miscellaneous services (see Finding
2).
FINDING 5: Monthly Billings
Currently MUBS processes all the billings for utility services on a monthly basis at the
end of the month. Therefore, there is an increased workload for some people during the
7
month. For example, a significant amount of time is spent receiving and processing
payments during the middle of the month when payments are received. To accommodate
this workload, several employees may be involved in a process during that time period,
resulting in inadequate segregation of duties.
Recommendation:
We recommend the City consider an additional billing cycle during the month to level the
workload.
FINDING 6: Lack of SuaAOrtin~ Documentation for Manual Entries
As mentioned in Finding 2, manual entries are made into Casell to invoice customers for
miscellaneous services for the water used by accessing fire hydrants. Entries are made
into Caselle based on a summary sheet indicating the amount due. No other
documentation is provided or review is performed on the amount included on the
summary sheet.
Recommendation:
MUBS should be provided with the detailed documentation on the water usage to ensure
the proper amounts are invoiced to the fire hydrant users.
FINDING 7: Lack of Supporting Documentation for Journal Entries
The Caselle Utility Billing System is separate from the MIP general ledger and financial
reporting system. Therefore, journal entries are used to record the activity from Caselle
into the MIP general ledger system. For several transactions, the accounting department
does not receive the full report, which provides the supporting documentation for the
entry.
Recommendation:
Accounting should receive the full report and supporting documentation to ensure the
accuracy of the entry.
FINDING 8: Documenting the Close Process
We noted in our review that the MUBS and Accounting department did hot have a formal
documented monthly close process. Such a process would indicate who performed the
procedure, provide supporting documentation for the procedure, and documentation that
the procedure was reviewed and approved.
8
Recommendation:
A monthly close process should be documented to ensure that the systems reconcile and
the procedures have been completed and reviewed. We also recommend that a monthly
closing checklist be prepared which lists all the closing processes. When each process is
completed, it should be signed by the individual who performed the procedure. An
appropriate supervisory review should be performed and the signature of the employee
who reviewed the work should be included on the document.
Management should be the custodian of the completed monthly close procedures. We
have provided the Finance Director with an example of and a baseline for developing the
closing checklist.
FINDING 9: Access to Caselle Applications
During our review we noted that employees had access to Caselle applications that they
do not use. In addition, employees are processing entries into Caselle that should be
performed by other employees. For example, several employees have access to and make
data entries into the system. This control weakness is exacerbated by the fact that the
system does not have the capability of identifying the originator of the transaction.
Recommendation:
If employees are not using specific applications, they should be removed from having
access to the application. In addition, the number of employees who have the capability
to make entries into the system should be limited.
FINDING 10: Deletin Receipts
In our testing, we discovered several instances where the Payment Receipt report
indicated that receipts may have been deleted. Currently there are three employees who
have access to delete receipts in Caselle. Procedures have recently been implemented to
review for deleted receipts. However, documentation or data is not maintained
identifying the receipts that have been deleted. This is primarily due to the fact that the
data is erased from the system on a regular basis.
Recommendation:
There is a Checkout report in Caselle that will provide the detail of the deleted receipts.
This report should be generated and reviewed daily for deleted cash receipts. This report
should also be retained as an audit trail.
9
FINDING 11: Title Company Receipts
When a house is sold or refinanced, the title company will request a current invoice from
MUBS for the utility billings. MUBS will generate an invoice and submit it to the title
company for reimbursement. MUBS maintains a file by customer with the request from
the title company and the invoice. Currently, each file is manually reviewed on a
monthly basis to determine if the title company payment is still outstanding. In our
discussions with representatives of Caselle, they indicated that a report may be able to be
generated which would list the outstanding receipts and make this process more efficient.
Recommendation:
The City of Meridian should contact Caselle to assist in the City in generating reports that
would identify outstanding receipts due from title companies. This report should be
reviewed at least weekly for follow up with the title companies.
FINDING 12: Seere~ation of Duties - Receiving Payments
Most customers mail their utility payments into the City. Since all customers are
invoiced at the same time during the month (See Finding 5), most of the payments are
received within a few days in the month. To accommodate the high volume of mail
payments, all MUBS employees participate in opening the mail. This results in a lack of
segregation of duties since numerous people have access to the checks received.
Recommendation:
We recommend that only one person open the mail. That person should stamp each check
with a "for deposit only" stamp.
FINDING 13: Audits
We commend the City for taking a proactive position to improve the MUBS process,
system and controls. We noted that the primary areas of risk for this operation are the
utility billing system, system security and procurement. Our review addressed the
process, systems and controls for the utility billing system and the security around that
system. However, other areas of risk may exist, such as procurement and the security
around other systems.
Recommendation:
The City should conduct regular control assessments to determine the areas of control
risk. Corresponding audits should be performed to ensure the risks are adequately
addressed. If the City has adequate resources to independently and objectively perform
10
these reviews, we recommend that the City use these resources. Otherwise, the City
should consider engaging the assistance of outside professionals.
11
SECTION C -SYSTEM SECURITY
SCOPE
The scope of the IT review was limited to the security, control and administration of the
computer server running the Caselle application and the security within the actual Caselle
software. Controls over the full network and computer systems supporting the City were
not included in the scope of this review.
OBJECTIVES
The audit objectives were as follows:
• To determine the adequacy of the controls related to security and administration
of the computer server running the Caselle application and the security within the
actual Caselle software.
• To identify control weaknesses based upon the results of the review performed.
• To make suggestions for control improvements based upon the results of the
review performed.
PROCEDURES
Our review included interviews with key personnel and a review of available
documentation and procedures. We observed, inspected and tested various manual and
automated controls and made inquiries concerning these controls associated with the
computer systems and network operations. Specific areas reviewed in this review
included:
• IS security policies
• Computer administration, security and operatipn practices
• Operating system configuration settings and files
• Application programs, database and file permissions and shares
• Application program and network password controls
• Computer user rights and privileges
• Remote computer access controls
• Software change authorization and control procedures
• Back-up procedures
• Physical security and environmental controls over the computer server
12
RESULTS OF REVIEW
FINDING 1: System Vulnerabilities Identified In the Security Scans
The system security scan results showed that good controls are in place in most areas,
however, the results did identify vulnerabilities on the system. The detailed scan result
reports and a summary listing of the vulnerabilities have been delivered to the IT
Manager. The IT Manager has developed corrective action responses and statuses to the
vulnerabilities identified on the production system. Some vulnerabilities identified in the
scans cannot be corrected because the item causing the vulnerability is needed in order to
conduct business. In these cases, for business reasons, the associated risk has been
accepted.
The identified vulnerabilities are classified as high, medium and low. High-risk
vulnerabilities are those that provide unauthorized access to the host. Medium-risk
vulnerabilities are those that provide access to sensitive system or network data that may
lead to the exploitation of higher risk vulnerabilities. Low-risk vulnerabilities are those
that provide access to data that may be sensitive.
Recommendation:
We recommend that all high-risk vulnerabilities be addressed as soon as possible.
Medium-risk and low-risk vulnerabilities should be addressed in a timely manner.
Management should track the status of other identified vulnerabilities on the scan
summary listing and address them in a timely manner.
FINDING 2: Caselle A___ nplicatioa Data Security
The security protecting the Caselle application's database is very weak. The weaknesses
include:
• Users must have "full control" over t~ database.
• The database is not protected with a password. Authorized users who do not use
terminal services to access the application, could access and change or delete any
data in the database outside of the application and its controls through
unauthorized programs (e.g. MS Access or through other applications using an
ODBC connection).
To limit this exposure the IT Manager has removed MS Access from the workstations of
the users authorized to use the Caselle application. Additionally, the IT Manager is
investigating means ~o prevent authorized users from utilizing an ODBC connection to
the database.
13
Recommendation:
The Caselle application vendor is developing a new version of the application, which
uses an SQL database. An SQL database can be secured better than the database system
currently used. The vendor should be requested to put a password on the database used
to store the application's data. The vendor should also be asked to design access
permissions so that the application program, and not the users, has full control over the
data. If the vendor cannot satisfactorily meet these requirements in a timely fashion, then
all users should access the application via a terminal services connection, which would
prevent access via unauthorized means.
FINDING 3: Caselle Application Password Controls
The Caselle application's password controls are very weak. The weaknesses include:
• No length requirement and blank passwords are allowed. Currently three IDs
have blank passwords. These IDs have limited authority.
• No change frequency.
• No content requirements.
• Users cannot change their own application user ID passwords. The Billings
Supervisor needs to initiate the process to change each user's password.
Recommendation:
Employees should be instructed to make their passwords six or more positions long and
that they use a mix of alpha and non-alpha characters. The Billings Supervisor, on a
periodic basis (sixty days), should initiate the process with each user to change the user's
password.
FINDING 4: Shared User Application IDs
Currently, two Caselle user IDs are not assigned to individuals. One ID, Front, is used as
a general purpose ID. The other ID, Temp, is setup for a temporary employee to use.
Neither requires the use of a password.
Recommendation:
All IDs should be assigned to an in~~vidual who exclus}v~ly controls the password.
Generic IDs; which are shared, should not be used. Individuals should use their own
computer IDs.
14
FINDING 5: Network Password Controls
Current network logon passwords are not adequately secure. In many cases employees
are using easily guessable passwords or passwords that are found in a dictionary. In
general, of the 44 active service and individual user account passwords used, 20 (45%)
were easily guessable or found in a dictionary. Easily guessable passwords are equal to
the sign-on ID or other account information.
Currently the system is set to require passwords to be five characters long and there is no
requirement for a mix of alpha and non-alpha characters to be used.
Recommendation:
Network password controls should be strengthened. The password policy should require
a minimum length of six characters and should have a complexity requirement, such as
the use of alpha and numeric characters in passwords.
FINDING 6: Computer Usage Policy
The City currently has a draft Computer Usage Policy. The policy adequately covers
many areas; however, it does not address the following areas:
• All passwords, including application passwords, should be changed every 60 days
(current network password change interval).
• All passwords, including application passwords, should be six characters in length
(current network length requirement is five characters).
• All passwords, including application passwords, should have a complexity
requirement. Passwords should contain a mix of alpha and numeric characters.
Passwords should not be words that occur in the dictionary or be equal to the
user's ID or their name. The use of special characters should be encouraged.
Recommendation:
The draft policy should be expanded to cover the above areas.
FINDING 7: Server Room Security
The computer server room door is not locked. The office the server room is located in is
locked after hours; however the custodial staff has access to the office.
15
Recommendation:
The computer server room door should be locked. Only the IT Manager, the Finance
Director and the Building Security Officer should have access to the server room.
FINDING 8: Backup Tapes
Monthly and year-end backup tapes are not stored offsite. Monthly and year-end tapes
are stored in the server room. Daily tapes, which are full backup tapes, are taken offsite
nightly and are brought back the next day. Therefore, there are no backup tapes stored
offsite during the workday.
Recommendation:
Monthly and year-end tapes should be stored offsite in a secure location. The daily
backup tapes should be kept offsite for one more day.
FINDING 9: Workstation Timeout Controls
Workstations are not automatically locked after a certain period of inactivity.
Recommendation:
Workstations should be configured to automatically lock after afive-minute period of
inactivity.
16
SECTION D -MISAPPROPRIATION OF ASSETS
OBJECTIVE
The audit objectives were as follows:
• To review and analyze available records and determine the cost effectiveness of
embarking on an in depth (forensic) investigation to fully quantify the suspected
loss.
• If control weaknesses are identified, perform limited specific tests that may
identify any accounting irregularities.
PROCEDURES
We performed the following tests to identify questionable accounting practices:
• Reviewed disbursements made to employee addresses.
• Reviewed disbursements made to relatives of employees.
• Analyzed cash activity and trends.
• Reviewed account write offs for trends.
• Reviewed transaction activity noting trends while employees on vacation.
• Reviewed billing adjustments.
• Interviewed customers who had account write offs.
Our review of disbursements to employee addresses and relatives was for the calendar
years 1999. through 2002. For the other aforementioned audit procedures, we primarily
tested transactions dated June 18, 2001
RE_
We did not discover any unknown instances of asset misappropriation. However, this
does not mean that misappropriation of assets did not occur. We identified several "red
flags" or indicators of potential asset misappropriation. There are as follows:
• In February 2002, approximately $1,600 was written off to misapplied accounts.
• Our review indicated that the accounts written off pertained to terminated
accounts.
• The accounts were terminated during specific months in 2001.
• Many of the accounts were terminated while the Billing Supervisor was out on
compensated personal absence.
• Title companies made payments to these accounts; however, only partial
payments were posted to the accounts.
• The Payment Register indicates several receipts were deleted from the system pr.
I~
• All MLTBS employees had access to the system and to make entries into the
system.
• Some entries were made using the Billing Supervisor's password while the
Billing Supervisor was out on compensated personal absence.
• The percent of cash receipts for this time period was less than normal.
• There were instances where there was no documentation supporting refunds
submitted to customers.
Due to the control weaknesses in the system and the processes, it is possible that the
City could have been exposed to embezzlement. We believe that a more detailed
review to identify whether there were any misappropriations could cost between
$15,000 and $20,000. Because it is likely that any misappropriation of assets could
involve cash, a detailed review still may not be able to identify the amount or type of
asset misappropriation.
18