BCM Database Audit Final Report

Tomk

Description

BUFFALO CITY MUNICIPALITY IMPROVED PAYMENT STRATEGY: UNDERTAKE AN AUDIT OF THE ACCOUNTS DATABASE FINAL REPORT MARCH 2004 AFRICON Buffalo City: Audit Accounts Database Final Report TABLE OFCONTENTS 1 Introduction 1 1.1 Background1.2 Comments on the original terms of reference2 Our approach towards municipal service delivery 1.1General.2The service delivery cycle.3The balanced score card3Audit Methodology.1 Interviews.2 Analysis of the database 1 .3 Desk top study, reports and documents4 Results.1Analysis of the database.2Billing Process.3Debtors Management.4Credit control and debt recovery management 1 .5Indigence management.6Customer communication and education.7Systems, Information and data management4.8 Interrelationship with the community survey5 Conclusions and recommendations.1Recommendations to consider for immediate action 1 .2Recommendations to consider over the short to medium term.3Recommendations to consider over the longer termAppendix A: LIST OF STAFF/PERSONS INTERVIEWEDAppendix B: ANALYSIS OF THE DATABASE(FROM REPORTS BU 49,50,51)Appendix C: LIST OF PROPERTIES WITH MORE THAN 100 ACCOUNTS LINKED 1 Appendix D: DEBTOR CATEGORIES FOR ANALYSIS PURPOSES 1 BCM Database Audit Final Report.doc i 18/03/2004 Buffalo City: Audit Accounts Database Final Report 1 Introduction 1.1 Background The Buffalo City Municipality currently faces profound financial and service delivery challenges. The current financial ...

Informations

• Publié par: Nachung
• Nombre de lectures: 71
• Langue: English

Extrait

 

 

BUFFALO CITY MUNICIPALITY  

 

    IMPROVED PAYMENT STRATEGY: UNDERTAKE AN AUDIT OF THE ACCOUNTS DATABASE         FINAL REPORT      

  AFRICON  

MARCH 2004

Final Report 1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1  

Buffalo City: Audit Accounts Database TABLE OF CONTENTS   1   Itcudortnion  1.1    uodnckgrBa 1.2   on the original terms of referenceComments 2   Our approach towards municipal service delivery 2.1   General 2.2   The service delivery cycle 2.3   The balanced score card 3   Audit Methodology 3.1   Interviews  3.2   Analysis of the database 3.3   Desk top study, reports and documents 4   Results 4.1   Analysis of the database 4.2   Billing Process 4.3   Debtors Management 4.4   Credit control and debt recovery management 4.5   Indigence management 4.6   Customer communication and education 4.7   Systems, Information and data management 4.8   Interrelationship with the community survey 5   Conclusions and recommendations 5.1   Recommendations to consider for immediate action 5.2   Recommendations to consider over the short to medium term 5.3   Recommendations to consider over the longer term Appendix A: LIST OF STAFF/PERSONS INTERVIEWED Appendix B: ANALYSIS OF THE DATABASE(FROM REPORTS BU 49,50,51) Appendix C: LIST OF PROPERTIES WITH MORE THAN 100 ACCOUNTS LINKED Appendix D: DEBTOR CATEGORIES FOR ANALYSIS PURPOSES  

BCM Database Audit Final Report.doc

i

18/03/2004

Buffalo City: Audit Accounts Database

Final Report

1 Introduction 1.1 Background The Buffalo City Municipality currently faces profound financial and service delivery challenges. The current financial position of the municipality underlines the urgency of developing a coherent, realistic and integrated approach in managing its activities and more specifically actions to improve revenue collection. In order to start addressing the latter, the Buffalo City Municipality, during August 2003, called for three proposals, being: ·a customer satisfaction and willingness to pay survey; undertake  To · To undertake an audit of the consumer accounts database; and · To undertake an improved payment strategy debtors and business analysis.   Africon, to date, was awarded the second proposal, being the undertaking of an audit of the consumer accounts database. 1.2 Comments on the original terms of reference The initial statedobjectivesof the audit included the following: · determine the accuracy of the Customer Accounts Database; To · To determine the coverage of the Customer Accounts database; · A realistic estimation of the recoverable debt; · identify areas of risk that may influence the correctness of the database; To ·compare cost recovery for services of various built-up areas with each  To other; and · database with the customer satisfaction and willingness to pay survey Link results.   During discussions, towards the end of 2003, with senior officials from the Buffalo City Municipality it was indicated that, in terms of various initiatives currently being implemented by the Municipality, the project should focus more on processes and procedures impacting on revenue collection and a little less on the actual database. In addition to the original stated objectives and the mentioned request, the project will also investigate processes and procedures relating to: · Billing Process; · Management; Debtors · control and debt recovery management; Credit · Indigence management; · communication and education; and Customer · Systems, information and data management.   It should be stated the purpose of the study is not to investigate all the objectives in detail, as time and budget would not allow for this, but rather to highlight trends and issues that could be identified and prioritised, to assist the Municipality to guide future actions and policies towards improving the revenue collection, thus their financial position.

BCM Database Audit Final Report.doc 1

18/03/2004

Buffalo City: Audit Accounts Database

Final Report

2 Our approach towards municipal service delivery 2.1 General To be sustainable the municipal services rendered to customers in a municipality must at least adhere to the following critical aspects: · Be within the municipality's financial and administrative capacity; · Be provided in a manner that is fair and equitable to all its residents and communities;  · the highest quality service at the lowest cost and the most Ensure economical use and allocation of available resources; · financially and environmentally sustainable; and Be ·with a view to upgrading, extension and improvement. reviewed  Regularly 2.2 The service delivery cycle Our approach to service delivery takes into account the delicate balance of the above aspects. It is presented in the framework model presented below. Although we have not included all the aspects shown in the diagrams, it formed the basis and point of departure during the execution of our investigation. Certain aspects of the items encircled were included in this study.  

   

Figure 1: The AFRICON service delivery framework

BCM Database Audit Final Report.doc 2

18/03/2004

 

Buffalo City: Audit Accounts Database

Final Report

Service delivery is the single most important aspect at any local authority and needs to be nurtured and evaluated from time to time. All the aspects relating to the services offered by the Municipality, including: operations, upgrading, maintenance, skilled staff and all other aspects relating to sustainable service delivery do however depend on sufficient funding. The financial well being of a Municipality, in turn, is in direct relation to its ability and the progress made in terms of efficient service delivery. Clients will only pay when they are satisfied with the service they receive; therefore the service delivery framework and balanced scorecard strategy map are clear and comprehensive models for describing and communicating strategies to employees, stakeholders and the community in a consistent and insightful way. They are also valuable tools in change management and can help align the entire organisation with the strategy and focus on the key value drivers. These value drivers or lead indicators of future performance are mostly found in the non-financial areas – in employees, innovation, technology, processes, suppliers and customers. This framework and scorecard(shown below) replaces the budget as the centre for management processes and places strategy in the centre of management and change. By defining the strategy clearly, communicating it consistently, and linking it to the drivers of change, a performance-based culture can emerge that link everyone and every unit to the unique features of the Municipality’s strategy. The cause-and-effect linkages of the balanced scorecard describe the path by which improvements in the capabilities of intangible assets get translated into tangible customer and financial outcomes. 2.3 The balanced score card The Balanced Scorecard Strategy Map is a valuable tool for looking at both financial and non-financial measures, tangibles and intangibles, translating strategy into operational terms and concentrating on drivers that create economic value. The Balanced score card depicts the integrated nature and critical role of each of the local authority’s activities in ensuring successful service delivery to customers, and is based on the 2001 publication from Kaplan & Norton, “The Strategy-Focused Organization - How Balanced Scorecard companies thrive in the new business environment”.  The model (illustrated below) must be read from bottom up as indicated by the arrows. It consists of various boxes or value drivers and is grouped into the four perspectives namely theLearning and Growthperspective, where capacity is built through staff and skills and directed through clear strategies. This lays the foundation for the development of efficientInternal Processes deliver good to technical services. Only then, together withCommunity education and customer care, can the customer be satisfied, resulting inImproved payment. In summarythe mandate and mission of each local authority to serve the: It is community in exchange for certain fees. Payment and revenue growth will follow good service delivery and customer care. This growing stream of own income will then further enhance capacity, efficient processes and service delivery.   The four Balanced Scorecard perspectives in more detail: · Learning and growth perspective (L), shown in green, indicates the capacity and direction of a local authority. It is built on its mission, vision, culture and leadership and forms the foundation on which we build our strategy map. It provides the energy and direction towards its goal of increased revenues.

 

BCM Database Audit Final Report.doc 3

18/03/2004

Buffalo City: Audit Accounts Database

 

 

Final Report

· Internal processes perspective (P), shown in blue, is built on the Learning and growth and is divided into (i) organisational management and (ii) secondly technical management. Based on its capacity, the Municipality needs to build these internal processes for efficient service delivery. · perspective (C), shown in yellow, follows service delivery. Customer is This how customers perceive the Municipality and its services. Satisfaction can only be achieved through proper community education and involvement, good relationships with all stakeholders, efficient complaints handling and a fair and reliable system of meter readings and billing. · Financial perspective (F), shown in brown, follows customer satisfaction. Increased revenue is a natural outflow when the municipality has ensured that the above processes or value drivers are taken into account in the strategy and internalised within the municipality’s day to day activities.

  The balanced score card is shown below.  

BCM Database Audit Final Report.doc

4

18/03/2004

Buffalo City: Audit Accounts Database

Final Report

3 Audit Methodology The methodology followed was a combination of interviews, analysis of the data extracted from the Venus Billing System and a desk top study of information i.e. policies, procedures, reports, project plans and other relevant documents. The study was done over the time period November 2003 to February 2004. 3.1 Interviews A number of interviews were held with personnel at operational and managerial levels from relevant departments. This included feedback sessions. The results form these discussions and feedback sessions are included in the findings below. A list of the municipal staff and Councillors involved in these discussions is attached in Appendix A. 3.2 Analysis of the database Specific information was obtained from the billing system and analysed for certain indicative trends. The Venus data obtained included the following standard reports: · 049, 050, 051 electronic text files (Audit Report October 2003) BU · BP081 (Hard copy list of the codes used in the system) · BP136 electronic text file (Trail Balance per Service – Age analysis January 2004) · 2004) January electronic text file (Debtor Analysis Report BS906 - It should be noted that time and the availability of raw data from the system did not permit detailed analyses of the data, nor was it the intention of the team. The level of focus of this study is aimed at strategic and tactical issues and prioritisation of these to facilitate improved revenue collection. Spatial data were also obtained from the GIS department which included addresses, cadastre, owners, settlements, wards and zonings. 3.3 Desk top study, reports and documents The following reports/information were obtained: · credit control and debt collection policy Draft ·Finance Standing Committee – Nov 2003 Report to the · Data cleaning project plan · Rates and Tariff Policies Review · Venus user manuals · Application form and agreement for a new service · form for termination of service Application · for erf identification, creation and maintenance Procedure · of a printed municipal bill Example · done by the billing section Analysis · Frequently asked questions obtained from the Buffalo City Municipality website · Organogram of the Directorate Financial Services

BCM Database Audit Final Report.doc 5

18/03/2004

Buffalo City: Audit Accounts Database

Final Report

4 Results 4.1 Analysis of the database This section serves to link results of the study to the original objectives of the study, namely a database audit. It should be noted that the database, as the “heart” of the billing to customers, affect and is an integral part of a number of other areas. Some of the aspects mentioned here will appear again later on in the text, but then showing the relationship and importance with that specific section/aspect investigated.  4.1.1 Accuracy and coverage of the Customer Accounts Database 4.1.1.1 General findings and discussion With the amalgamation of different areas Buffalo City Municipality inherited areas with incomplete erf data and is in the process to address this (Data from the valuation system, the billing system and the Deeds office is being compared). An audit on the database had been done by Buffalo City Municipality team involved in data cleaning and it is estimated that about 40 000 properties exists within the Buffalo City Municipality boundaries that are currently not on the valuation system. It is further estimated that about 40% of the data between the systems mentioned above cannot be linked. It is also estimated that 30% of the erven in Mdantsane are not registered at the deeds office. Although there had been extensive effort to tackle this daunting task of data cleaning, the resources available to perform this task simply do not match the urgency to get this done. 4.1.1.2 Specific database findings As already mentioned earlier the raw data on customer accounts from the Venus billing system couldn’t be accessed and only the outcomes of the standard reports from the system could be used for the analysis. It was requested that a consultant on the Venus system should assist with the extraction of data however that did not materialise because the Venus consultant was not available at the time. It must be noted that BCM requested a report writing tool that will improve this situation in the near future. The exact implementation date of this is unknown. Using the BU 049, 050, 051 (Audit Reports October 2003) the following issues were identified that can have an effect on the customer accounts database: · The Unit Nr that represents an erf seems to be the unique identifier in the Venus system that links the different entities like the properties, meter readings and debtors. The following were found regarding the Unit Nr field: o 398 (3,2%) records contained no Unit number data. 8 was It mentioned that certain sundry accounts do not need unit numbers however this needs to checked and verified. Preliminary checks were done on this finding by the municipality and it was reported that most of these accounts seems to be inactive / closed or it reflects a sundry debtor. oare 116 159 unique Unit numbers which means that 116 159 There  properties are being billed. However 18 262 properties had no charges under the current amount for October 2003 and this needs to be investigated further oreceived bills of less than R50 are 10 098 properties that  There under the current charges for October 2003 this can represent the

BCM Database Audit Final Report.doc 6

18/03/2004

Buffalo City: Audit Accounts Database

Final Report

balance on the indigent accounts after the subsidy has been deducted however this needs to checked and verified. oour understanding that a Unit or property can have multiple is  It accounts or “debtor lines” linked to it however there are 54 properties with more than 100 accounts linked to it which seems to be out of the ordinary. A list is attached as Appendix C. · It was discovered that 7 731 (3%) of the records doesn’t have the correct billing cycle data. This can cause certain meters not being read and subsequently bills will not produced for these properties. This issue should be further investigated. (It is estimated that about a 1000 properties are affected by this) ·for the debtors seem to be complete with only 24 The postal addresses records having no postal address data. However on the 3 rd  line address (that contains mostly place names) there are 42 000 records with no address information. It also seems as if there is no uniform approach to fill in addresses on the system. This can have and affect on the effective distribution of the bills. · The debtor type data is complete and it could be a required field on the database however it was discovered that many of the debtor types actually belongs to different categories for example certain business accounts are being categorised as domestic debtors. The implication of this could be large and the full extent of this can only be determined through a line by line comparison which is not part of this project. Note: The findings above is based on standard reports from the system and depending on the internal parameters (not known) set on the report extraction the findings might not correlate hundred percent with the raw data however it still needs to be checked and verified.   4.1.1.3 Recommendation: · Data cleaning process should be sped up as the database is the basis of billing and therefore revenue for the Council. · Specificon validating the Venus data further emphasis should be placed and the cleaning thereof should be a priority. 4.1.2 A realistic estimation of the recoverable debt 4.1.2.1 Findings and discussion During the investigation to estimate the recoverable portion of the debt, it became clear that this might not be possible due to a number of reasons. The most important aspects were listed that could impact on the accuracy of such an analysis. These are:   · extensive database cleaning initiative is currently being undertaken and An we recommend that the estimation be done after the data cleaning project has been completed. · records of the effect that the credit control actions have on Accurate customer payment culture don’t exist. ·the billing system – it is understood that all data to raw data from  Access analysis are done from standard reports produced by Venus. It would be

BCM Database Audit Final Report.doc 7

18/03/2004

Buffalo City: Audit Accounts Database

Final Report

preferable to do customised analysis on the raw data where there are shortfalls in the standard reporting currently available on the system. ·is normally very difficult to collect. age – debt older than 90 days  Debt There are currently actions to collect debt through the pre payment meters which seem to be acceptable to a number of respondents included in the field survey – the extent of this is unknown. Data on the cost effectiveness of this and the period needed for eradication of outstanding amounts prior to the pre payment system implementation was not available. In addition the estimation based on the age analysis is not accurate due to the way in which arrangement to customers are being handled on the system. · Proportion of unemployment and the economic conditions – It is mentioned that, on overall, almost one third of people living in Buffalo City are unemployed but it should be more specific to an area to be able to link this to the database. Specific indigent data was also not available and would have been a valuable source to bring into account in the analysis. ·specific and direct link between the conditions – There is a very  Health health status of the household and the amount available to pay for services. This is an unknown factor at this stage. · levels and spendable income – It was reported in the community Income survey that only 18% of people living in Buffalo City are able to save anything after paying for basic expenses and more than 70% of residents earn less than the basic salary of R1 500 per month. Once again more specific data should be available to be able to link this to the billing database. · of payment/non payment in an area. Culture is made in the field Mention survey of the culture of non payment and it is linked to the period prior to 1994. To what extent it is still present and the effect of customer education and awareness campaigns would have on this culture is not certain. ·municipality and the need to pay for services – Awareness of activities of the It is mentioned form the results obtained in the field survey that some customers are aware of the services of the municipality renders while other are unaware. Ideally the analysis should include knowledge on the expected effect the awareness will have on the payment levels. · Extent to which the customers are satisfied with service delivery – The field survey states that the customers interviewed are fairly satisfied with the services rendered by the municipality. The question that was not answered however was how much the customers would be willing to pay for services. · control actions and effectiveness thereof – the credit control actions Credit are currently being recorded outside the Venus system and time does not permit a detailed analysis on this, mainly due to the fact that the current credit control section seems to be understaffed. Ideally the link between credit control actions and how it influences payment levels should be incorporated in the study to determine recoverable debt. · of customers – It is normally easier and more cost effective to apply Types credit control measures to businesses than to individual households 4.1.2.2 Recommendation: ·no attempt was made to determine the recoverable debt – to the above  Due It is recommended that this analysis should be done at a later stage when a realistic estimation to the above aspects can be given with a fair amount of certainty. 

BCM Database Audit Final Report.doc 8

18/03/2004

Buffalo City: Audit Accounts Database

Final Report

4.1.3 To identify areas of risk that may influence the correctness of the database The following areas of risk, amongst others mentioned in this report, were identified: · It is absolutely important that a uniform set of processes and procedures be agreed between all Departments regarding the creation and management of properties and associated data on the system. · base data (erf data) and ownership as well as address data is The absolutely crucial and cleaning of this should be sped up · of systems – the mismatch in data between the valuation system, Integration the billing system and the Deeds office · Frequent database audits need to be done to ensure data integrity. Database anomalies could easily be spotted with a routine spot checks · A culture of feedback(quick) to the system to constantly improve the quality of data in the database – this applies mainly to cross departmental · integrated system gives rise to the creation of several database islands Non to fulfil the need of that specific department/section and causes misalignment of data in the end. · point mentioned above also contributes to a risk that certain key skills The lies with specific personnel within the deferent departments. If someone in this position falls away it can have a huge affect on the processes and procedures and subsequently the database. · The customer base has grown for the municipality however the billing system that was originally only used for the East London area is now being utilised for the whole municipality. In affect the system is overloaded and actions like the billing run now takes up to 2 days to complete. Over these periods the system is “off-line” and certain transactions i.e. opening of new accounts are written on ‘n piece of paper and captured when the system is back online. 4.1.4 To compare cost recovery for services of various built-up areas with each other 4.1.4.1 Introduction The available data did not allow us to do this. This is done at length in the report to the Finance Standing Committee – graphs at the end of the report outlines the trends, albeit this might not be entirely correct mainly due to the way in which arrangements are being handled in the Venus system. The following analysis has been done on the data: · Analysis of the debt age – overall perspective ·  Analysisof type of debtor and payment levels   4.1.4.2 Assumptions made and analysis methodology The BU 049, 050, 051 audit reports were the only reports that were in an acceptable format that could be imported into a database file for analysis purposes. It was also mentioned that these reports are the most comprehensive and were specially developed to give an extract of the debtor data to the council auditors when it was requested. Most of the analysis was based on the Debtor file that was part of this extract. There are certain shortcomings when using these files for analysis which include:

BCM Database Audit Final Report.doc 9

18/03/2004