Description
Revenue sharing has multiple, related meanings depending on context. In business, revenue sharing refers to the sharing of profits and losses among different groups.
An Online Revenue Management System: Case Study-Kampala City Council(KCC).
By Babwona Ziria Naiwumbwe, BSTAT,MA(EPM),(Mak)
A Research Report Submitted for the study leading to a Project in partial ful?lment of the requirements for the award of a Post Graduate Diploma in Computer Science of Makerere University
Supervisor Ms. Rehema Baguma, Faculty of Computing and Information Technology Makerere University,
November, 2005
Declaration
I, Babwona Ziria Naiwumbwe, do declare that this report is my own work and that has never been previously presented anywhere for the award of any degree. Signed :——————————— Date :——————————— BABWONA ZIRIA NAIWUMBWE Candidate (2004/PGD18/175U)
Approval
This project has been under my supervision and has my approval for submission Signed :——————————— Date :——————————— MS. REHEMA BAGUMA Faculty of Computing and Information Technology
ii
Dedication
This work is dedicated to my mother Hajati Aisha Olwitta and in memory of my father, the late Haji Sulaimani Olwitta who never lived to reap the fruits of his sweat.
iii
Acknowledgement
I thank the almighty God for having seen me through this course up to the production of this report, with his mercy and blessings many people became of great help and assistance to enable me reach this stage. I am highly indebted to my supervisor Ms. Rehema Baguma lecturer, Faculty of Computing and Information Technology for her guidance, constructive critisms and interest she showed in my work. Special thanks go to the entire course mates and indeed my fellow group members for cooperation they showed throughout the entire course. I would like to thank the staff of K.C.C Revenue Department and special thanks go to the Principal Town Clerk for Kampala Central Mr. Stephen Higobelo who was co-operative and provided me with all the necessary information and also allowing me access to their revenue ?les. My appreciation is extended to my employer Bank of Uganda, for giving me chance and the facilitation to this course. Finally, failure to acknowledge the role my family played would be unfair. Without their understanding, encouragement and inspiration, my efforts would have been to no avail. Many other people are inevitably omitted from this acknowledgement, not as an act of ingratitude but because of limited space. Otherwise I am very grateful for all the assistance they rendered to me. Any error and/or omissions in this project are entirely my own responsibility.
iv
Contents
Declaration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Approval . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Dedication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Acknowledgement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . i ii iii iv
Abstract . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xii
1
INTRODUCTION 1.1 1.2 1.3 Background to the study . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Statement of the Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Objectives of this Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.3.1 1.3.2 1.4 1.5 Major Objective . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Speci?c Objectives of this Project . . . . . . . . . . . . . . . . . . . . . .
1 1 2 3 3 3 3 3
Scope of the Study . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Signi?cance of the Study . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2
LITERATURE REVIEW
5
v
2.1 2.2 2.3 2.4
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Revenue management systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . Computerized Revenue Management . . . . . . . . . . . . . . . . . . . . . . . . . Some Case Studies of Computerized Revenue Management . . . . . . . . . . . . . 2.4.1 Marriot(2005) [6]: Linking Two Data Streams With Revenue Management System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.4.2 The Uganda Revenue Authority (URA) . . . . . . . . . . . . . . . . . . .
5 6 7 8
8 9
2.5
The Bene?ts of Computerized Revenue Management . . . . . . . . . . . . . . . . 11 2.5.1 Problems of implementing Online Revenue Management Systems . . . . . 12
3
Methodology 3.1 3.2
13
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Data collection techniques/tools used in studying and investigating the existing system . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
3.3 3.4
Tools used in Analysis and Design of the System . . . . . . . . . . . . . . . . . . 14 Tools used in development and implementation of the system . . . . . . . . . . . . 14
4
STUDY AND INVESTIGATION OF THE EXISTING SYSTEM
15
5
SYSTEMS ANALYSIS AND DESIGN 5.1
18
Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 5.1.1 5.1.2 User Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Functional requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 vi
5.1.3 5.1.4 5.2
Non-functional Requirements. . . . . . . . . . . . . . . . . . . . . . . . . 19 System Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
System Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 5.2.1 5.2.2 5.2.3 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Process Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Data Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
6
SYSTEM IMPLEMENTATION 6.1 6.2 6.3
45
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Components of the Online Revenue Management System for KCC. . . . . . . 45 Physical Design Of The System . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 6.3.1 6.3.2 Data Services Layer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Function Logic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
6.4
Presentation Layer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 6.4.1 6.4.2 Web Application Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Local Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
6.5 6.6 6.7
Sample of the Program Flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Sample of the Forms or Screens of the system. . . . . . . . . . . . . . . . . . . . . 50 System Testing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
7
DISCUSSION OF ACHIEVEMENTS, LIMITATIONS AND CONCLUSION
59
vii
7.1 7.2 7.3 7.4
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Discussion of Achievements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Recommendations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
REFERENCES
61
APPENDIX A: Interview Guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 APPENDIX B: Sample Program Code . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
viii
List of Figures
5.1 5.2 5.3 5.4 5.5 5.6 5.7 5.8 5.9 Symbols used in the design of DFDs . . . . . . . . . . . . . . . . . . . . . . . . . 20 Context Diagram depicting the Online Revenue Management System for KCC . . . 21 Level 1 Data Flow Diagram For The Online Revenue Management System For KCC 22 Table for Processes in the DFD . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Table for Data Stores in the DFD . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Table for Data Flows in the DFD . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Table for External Entities in the DFD . . . . . . . . . . . . . . . . . . . . . . . . 25 Architectural Design for the Online Revenue Management System for KCC . . . . 25 The Market-Division Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . 35
5.10 The Street-Division Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 5.11 The Trading License-Division Relationship . . . . . . . . . . . . . . . . . . . . . 35 5.12 The Taixi/Bus Park-Division Relationship . . . . . . . . . . . . . . . . . . . . . . 36 5.13 The Property-Division Relationship . . . . . . . . . . . . . . . . . . . . . . . . . 36 5.14 The Graduated Tax-Division Relationship . . . . . . . . . . . . . . . . . . . . . . 36 5.15 The Donation-Division Relationship . . . . . . . . . . . . . . . . . . . . . . . . . 37
ix
5.16 The Market-Tendering Company Relationship . . . . . . . . . . . . . . . . . . . . 37 5.17 The Taxi/Bus Park-Tendering Company Relationship . . . . . . . . . . . . . . . . 37 5.18 The Street Parking-Tendering Company Relationship . . . . . . . . . . . . . . . . 38 5.19 The Entity Relationship Diagram for the System . . . . . . . . . . . . . . . . . . . 39 5.20 The Key to Entity Relationship Diagram for the System . . . . . . . . . . . . . . . 40 5.21 The Structure of Divisions Table . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 5.22 The Structure of Market Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 5.23 The Structure of Street Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 5.24 The Structure of Property Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 5.25 The Structure of Graduated Tax Table . . . . . . . . . . . . . . . . . . . . . . . . 42 5.26 The Structure of Donation Table . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 5.27 The Structure of Trading License Table . . . . . . . . . . . . . . . . . . . . . . . 43 5.28 The Structure of Taxi/Bus Park Table . . . . . . . . . . . . . . . . . . . . . . . . . 44 5.29 The Structure of Tendering Company Table . . . . . . . . . . . . . . . . . . . . . 44
6.1 6.2 6.3 6.4 6.5
The Sample of the program Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 The Appearance of the Login Page . . . . . . . . . . . . . . . . . . . . . . . . . . 50 The Appearance of the Register Revenue Collection Form . . . . . . . . . . . . . 51 The Appearance of the View Revenue Collections Page . . . . . . . . . . . . . . . 52 The Appearance of the Monthly General Divisions Performance Report . . . . . . 53
x
6.6 6.7 6.8 6.9
The Appearance of the Register Market Revenue Collections . . . . . . . . . . . . 54 The Appearance of the Report on Monthly Market Revenue Collections per Division 55 The Annual Revenue Collections Report For The Year 2005 Per Division Resource Part Of The Quarterly Revenue Report For July - September, 2005 Per Resource Per Division . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 56
6.10 The Bi-Annual Revenue Report For July - December, 2005 Per Resource Per Division 58
xi
Abstract
Today more and more people are relying on information technology to perform their operations more ef?ciently. Computers are put to more use as tools of commerce, governance and education and they can no longer be looked at as luxury items. Kampala City Council (KCC) is a local government body that provides services for the people of Kampala City. It is in the process of establishing information technology department in an effort to computerize its operations and service delivery to the public. It is better at this time for the revenue department to come up with a computerised system that can be incorporated in the main system, as part of improving operations and service delivery. The system study found that the existing system (manual) was very slow, prone to errors and hard to quickly generate integrated reports for fast decision-making. This project has developed an automated alternative to revenue management. The system records revenue collections made for KCC for different resources and divisions, generates periodical (monthly,quarterly and annual) revenue collection reports per resource and division and tracks the performance of different resources and divisions in terms of revenue collections to assist management in decision-making.
xii
Chapter 1 INTRODUCTION
1.1 Background to the study
Kampala City Council(KCC) is the Kampala City government administrative body located within the heart of Kampala, the capital City of Uganda. Administratively, Kampala has under gone transformations which can be seen as the true origins of the KCC. In 1906, Kampala was of?cially declared a township. On January,1st 1949 it was raised to municipality status. Kampala was declared a City Council and a Capital of Uganda on October 9th 1962 after Uganda had attained her independence. KCC is headed by the City Council Chairman(who is titled ”Mayor”) and the Mayor has a Vice Chairman who works under him. The hierachy is then followed down by the Councilors who are followed by the chairmen of committees under KCC. The Town Clerk then follows these committee chairmen and under the Town Clerk, there are Heads of departments. Originally the city was limited to a small area of about 50 square Kms but today it extends for about 195 square Kms, divided into seven political units. These are Central, Makindye (South), Nakawa (East), Lubaga (West) and Kawempe (North) divisions, each with a seat in parliament. The Universities comprise of Makerere and Kyambogo also comprise of political divisions but are not represented in parliament. The population of Kampala has been growing at an alarming 3 percent per annum since the 1920s. At the time it was declared a township (1906), Kampala had 30,000 people, by 1914 it had in-
1
creased to 35,390. When it became a municipal council (1949) it had roughly 58,000 persons. At independence the population had swollen to over 200,000. After the 1968 decree the neighborhoods were incorporated into Kampala, and the population stood at 331,000 persons. According to the local government Act of 1997 and as part of decentralization programme, Kampala has been divided into 5 divisions. Each division collects its own revenue of which 65% is retained and the rest goes to the City Council Headquarters. Globally, according to RMS(1997) [7],Revenue Management started in early 80’s in airline industry. In Uganda, revenue management module forms part of the Integrated Financial Management System (FMS)- a budget execution and expenditure management sub-system to monitor and account for revenues and public expenditures. The sources of revenue for KCC include land tax, property tax, graduated tax and income tax among others. KCC out sources different local ?rms to collect taxes on its behalf like Multiplex Limited(responsible for collecting parking fees from vehicle owners who park their vehicles on the streets within KCC boundaries), Uganda Tax Operators and Drivers Association (UTODA)( responsible for collecting parking fees from public service vehicles parked in the taxi parks). Apart from internally generated revenue, KCC also receives grants from development partners like World Bank and International Monetary Fund(IMF) to ?nance development projects of the city. All this revenue needs to be managed effectively for the smooth running of the City Council activities for example for infrastructure development, social economic projects on HIV/AIDS,environmental protection among others.
1.2 Statement of the Problem
Currently, KCC is still using a manual system for collecting and managing their revenue. The system is very slow,prone to errors and hard to quickly generate integrated reports for fast decision making. This formed the motivation for this study to provide an automated alternative to the current manual system used in revenue management.
2
1.3 Objectives of this Project
1.3.1 Major Objective
The major aim of this project was to develop and implement an Online Revenue Management System for Revenue Collection and management for KCC.
1.3.2 Speci?c Objectives of this Project
The speci?c objectives of this project were to;
1. Study and investigate the processes involved in revenue management at KCC. 2. Identify the weaknesses in the current processes used to manage revenue at KCC. 3. Design and develop a system that would; (a) Improve the ef?ciency and effectiveness of revenue collection by KCC with less time wasting and less errors. (b) Provide automated report generation on the revenue collections per resource,division, and the entire KCC for easy planning and decision making.
1.4 Scope of the Study
The project focused on the function of collection, processing and management of revenue by KCC and exploring how these processes can be automated.
1.5 Signi?cance of the Study
The project was able to contribute to increasing awareness about the importance of automating management of revenue. The awareness was backed by a software application developed based
3
on the KCC environment but customisable for use by other revenue management entities both government and non-government.
4
Chapter 2 LITERATURE REVIEW
2.1 Introduction
According to RMS (1997) [7], Revenue Managment is the application of disciplined tactics that predict consumer behavior at the micromarket level and optimize product availability and price to maximize revenue growth. Revenue Management is about maximizing revenue from existing business. It’s a hard management science that employs rocket-science mathematical concepts and high-powered computers to crunch gigabytes of marketing information to:
1. Accurately assess future consumer behavior under dynamically changing market conditions 2. Determine the most effective way to price and allocate inventory to reach and every future consumer, each and every day, making real-time adjustments as market conditions change, with the consumer in real-time 3. Communicate this information instantaneously to distribution and sale outlets which deal with the consumer in real-time 4. Serve as a decision-support resource for marketing and operational functions, including but not limited to: pricing, scheduling, product development, advertising, sales, distribution, human resource utilization and capacity planning.
When Revenue Management was ?rst applied in the airline industry in the early 80’s, it was applied manually. However, the enourmous scope of the problem (managing tens of thousands of 5
?ight departures departing over the next year) convinced the early adopters of this practice that only through computerization could the airline capture the revenue opportunity that Revenue Management provided.
2.2 Revenue management systems
According to Van(1987) [12], revenue management traditionally involves segmenting customers, setting prices and controlling capacity to maximize the revenue from a ?xed capacity . The traditional revenue management model is where the seller must determine the capacity to sell in a given period before observing demand in future periods as provided by Seqev et al(2001) [10]. According to Belobaba (2002) [2],a wide range of industries have joined airlines, hospitability and travel companies in embracing revenue management as an essential component of their business strategies. Large databases of consumer information developed to improve customer relationship management will provide inputs to consumer choice models, which will in turn call prices to be set based on estimates of demand elasticity. According to Andrew (2003) [1], revenue management systems operate by periodically running forecasting and optimization along on this in order to refresh control parameters in the control reservation system and concerns have been voiced over the best way to update bid prices in the interim period between refresh points. According to Belobaba (2002) [2],joint optimization of price and inventory, combined with realtime updates based on the most current demand forecasts and available inventory, will generate incremental revenue gains that exceed those realized to date. According to Kimes et al(2003) [5],demand based pricing is underused in many service industries, because customers are believed to perceive such pricing as fair. Fencing can be highly effective in improving the perceived fairness of demand based pricing. Andrew (2003) [1] quotes Sabre et al.,(1998) [8] that over booking, demand forecasting taking willingness to pay into account optimizing the mixture of fare products involved or dynamically to the point where rocket science” is now an opt descriptor collectively known as revenue management. This is from the ?rst revenue management areas of freight engineering and many freight 6
revenue management systems have been developed. INTEL has also developed an asset management program, which is a single bared web based source from which the physical and con?guration of data for each of the series in the e-business product and pre-production environments can be observed (Brad et al., 2000) [3]. This means that effective asset management and revenues generated can be effectively managed by one of better processes and tools with good discipline of the audience. According to Ianelal (2004) [4], the dif?culty for revenue management does not know when and where discounts are needed to stimulate demand. Revenue management and pricing is a practical subject that is more than a management science algorithm. This means that revenue management still needs a lot of study to accommodate better decision-making and right forecasts using right prices and other variables.
2.3 Computerized Revenue Management
According to RMS (1997) [7], today, most organization managers understand that they are not maximizing the revenue potential from each and every asset. They are under- utilizing their most valuable asset. In an attempt to maximize their revenue collections, most organizations implement some type of revenue management methodology. Some organizations set global authorization levels for certain resources in their areas of location. Others assign analysts to examine the status of future business. Still others have implemented simple spreadsheet approaches to analyze resource performance. Each of these solutions captures additional revenue that would otherwise be lost. But in fact none of these solutions allow the organization to capture the wasted revenue. This is because a number of factors limit their ability to deliver the optimal solution. First, the different number of revenue sources an organization has,require maximum attention. Second, the variations in the revenues generated from the organization’s resources(for example from markets as for the case of KCC ) make it impossible to assume that what took place last week will probably happen again this week(if it didn’t rain on the market day last week and many traders brought goods to the market thereby increasing revenue through taxes imposed on them may be a different story when it rains on the next market day).
7
2.4 Some Case Studies of Computerized Revenue Management
2.4.1 Marriot(2005) [6]: Linking Two Data Streams With Revenue Management System
The whole can sometimes be greater than the sum of its parts. Marriott International Inc. found that to be true after it combined two revenue management systems into one. Revenue data collected by Marriott has many uses, such as predicting customer needs. But two separate revenue management systems kept the hotel chain from easily analyzing revenue data for forecasting and marketing insights generated by 62 million reservations annually at 2,800 properties. It was also expensive to operate one system for its full-service hotels and another for select-service or extended-stay facilities. And system upgrades and training typically required IT visits to hotels around the globe. They wanted to lower their cost and increase their speed to market. The new system, known as One Yield, has accomplished that. By combining the two systems, the need for support staff is 33% lower because only one database is used and the system requires little local IT support. Its Web and thin-client-based architecture delivers training remotely and cuts costs. But Marriott Hotel has also improved its ability to forecast changing market conditions. The system’s inventory data consolidation improves planning. The upside: Marriott Hotel estimates that One Yield delivered incremental pro?ts of $6.7 million last year, its ?rst full year in operation. Marriott Hotel chose to build the system in-house because revenue management is specialized and tightly integrated with its reservation system. The IT staff used standard technologies, including a centralized J2EE(Java 2 Platform, Enterprise Edition) architecture, IBM’s WebSphere and Actuate Corp.’s reporting tool. Marriott’s decision to build a centralized system with thin-client technology at global locations puts it ahead of many others in the hospitality industry. That particular industry is not known for being aggressive on technology but that’s changing as more hotels automate systems such as lighting and even supply handhelds to maintenance personnel. A critical goal for the management of Marriot Hotel was to ensure that the project aligned with the business. By combining two systems, the IT team was also merging different methods for managing and analyzing revenue. The business side worked closely with the technical staff. The 8
team had to decide what features to add and discard. Everybody had to compromise a little along the way and the management reports that it was one of the toughest challenges, because the ideas that came up were great ideas. Marriot’s goal was to have scheduled releases of this into the future, get it running, prove success and then continue to build. One Yield has been rolled out to 1,700 properties since the end of 2003 and is gradually being installed at others. It has become a proving ground for technologies, such as single sign-on, that will likely be extended to other systems.
2.4.2 The Uganda Revenue Authority (URA)
Uganda Revenue Authority (URA) was set up on September 5, 1991(URA,1991) [11] as a central body for the assessment and collection of speci?ed tax revenue, to administer and enforce the laws relating to such revenue and to account for all the revenue to which those laws apply. The Uganda Revenue Authority is also required to advise the Government on matters of policy relating to all revenue. Uganda Revenue Authority is charged with the responsibility of providing the foundation for development through revenue mobilization and management to:
1. Finance current and capital development activities. 2. Increase the standard of living of all Ugandans and reduction of poverty; 3. Increase the ratio of revenue to GDP, to a level at which Government can - fund its own essential expenditure.
The above objectives can be achieved by identifying taxpayers and informing the tax payers of their tax rights and obligations by providing them with - necessary information; Assess the taxpayer fairly with regard to those taxes relevant to them; Collect the taxes in accordance with the law and related regulations and practice; Account for the taxes collected in accordance with the statute; and enforce collection of taxes where default has occurred. Departments in URA responsible for revenue management have District Revenue Of?ces in various towns. URA mission is maximizing central government tax revenue while optimizing resource utilization by ensuring a 9
fair and equitable tax administration with a highly motivated and professional staff. In order for URA to increase and properly manage the revenue, there is need to;
1. Provide the taxpayers and their authorized agents with clear, precise and timely information. 2. Ensuring that courtesy and considerate treatment are extended unconditionally to all taxpayers. 3. Responding expeditiously to every taxpayer’s enquiry, complaint or request. 4. Explaining the grounds for and derivation of every tax assessment. 5. Providing proper technical advice to the taxpayer on requests about tax implications. 6. Assisting new taxpayers to register. 7. Educating the taxpayers and the general community about tax obligations and rights, objections and appeals 8. We should normally give prior notice to any taxpayer whose premises are to be inspected or upon whom an audit is to be conducted. 9. Tax records are maintained up to date and Tax credits are processed promptly and properly accounted for. 10. Providing high quality public relations at all levels of the Authority. 11. Accepting criticism, advice at all times, receiving and acting promptly on all genuine complaints made in good faith against URA revenue management system.
It is the obligation of the taxpayer to ensure that he/she is registered and obtains a Tax Identi?cation Number (TIN), which is issued free to every applicant and is the unique identi?er for all tax purposes. Income Tax payers are required to register for Income Tax and obtain an Income Tax File number. Taxable persons for VAT purposes are also required to register for VAT and obtain a VAT registration number.
10
2.5 The Bene?ts of Computerized Revenue Management
According to RMS (1997) [7], the ultimate bene?t of computerized revenue management is increased revenue. Each resource for the organization is managed to ensure that it generates the maximum possible revenue . You can expect this system to generate anywhere from 3% to 8% additional revenue for your business, resulting in potential pro?t increases of 50%-100%. Of course, your level of improvement depends upon how revenue management was utilized at your business prior to the implementation of the computerized system. At the same time, management bene?ts from an effective revenue management system. Management gains a decision-support resource for marketing and operational functions. Having at their disposal a tool that can analyze historical and forecast future behavior is invaluable when planning marketing campaigns, and pricing and schedule changes. New analysts can become effective more rapidly when they have a reference tool that helps them understand the relationship between historical and future behavior. Without such a tool, the analyst only learns about the business’ behavior through extensive experience. The analysts gain a tool that helps them focus their attention where it is needed, when it is needed. A tool that can provide them the con?dence to make the right decision while enabling them to apply their own knowledge and abilities to the maximum bene?t. According to Saleemi, (1997: 13,14) [9], the timing aspect of information is very important, because late information is better than no information. Automatic processing as provided by the electronic system, quickens the operations on the input data to produce timely information. He states further that the degree of accuracy and preciseness of the information is increased by the use of mechanical or electronic data processing tools, which remove the use of hand written entries. Automatic processing, is the process of keeping the data in a safe place and retrieved when required. According to Wallace (1987) [13], Automatic processing is information that is recorded in some form, stored in a system and retrieved in a manner. A record is any piece of information put aside for further use or reference. As per this study, the records include various revenue sources and collections made from different sites. According to Harrison (1996), an important function of the of?ce is the ?ling and ?nding of infor11
mation, which may be held on paper, disk or ?lm and to ensure quick and reliable access to it. He states further that electronic data storage has revolutionarised the speed of retrieval and allowed greater and easier access to vast quantities of records without the need to store papers in bulky shelves. According to Connolly (1996:12), when data is isolated in separate ?les, it becomes more dif?cult to access it.
2.5.1 Problems of implementing Online Revenue Management Systems
Like any other management information system, implementing an online Revenue management system posses alot of challenges to the organisation and among these we have;
1. The need for alot of ?nancial support. Much as the organisation is certain of bene?ting from the results of implementing their revenue management processes online, it needs alot of funds to support such projects. Even when the system is operational, it will need ?nancial support to maintain it. 2. Political in?uence in the organisation. Much as the organisation may be in need of an Online Revenue Management system, the politics within the organisation might cause such a project be abandoned even before completion. 3. Security of the organisation’s information. Once the organisation implements on online revenue management system, then, the information concerning their revenue matters becomes prone to unauthorised access. This means that the organisation will neeed to set up enough security measures both physical and logical security to the implemented revenue management system. 4. Need for more technical expertise. Much as the staff members of the organisation may be used to executing the processes involved in revenue management in their organisation, there will be a need for technical support to run the revenue management system implemented for example the system and database administrators which may not be present in the organisation before the revenue management system is set up. 5. The need for the infrustructure. The organisation may need to create more space for example construction of server rooms, of?ces for the new technical staff and any other physical infrustructure for the system. 12
Chapter 3 Methodology
3.1 Introduction
This section explains the methods used in the study of the existing system, analysing and designing the proposed online revenue management system for KCC.
3.2 Data collection techniques/tools used in studying and investigating the existing system
1. Interviews. This involved interviewing Kampala City Council Revenue Collection Department of?cials and other relevant persons in different divisions on information concerning revenue collection processes and sources. Through the interview, the researcher gave chance to the respondent to ask all relevant questions concerning the topic of the study. This gave the researcher an advantage of getting ?rst hand information from the respondents who were already part of the system under study. 2. Review of existing documentation. Document review was used to understand the current system and get relevant literature on the revenue management techniques that were being used the time the study was done. A number of documents were reviewed ranging from invoices, payment receipts, order papers, and delivery notes in addition to reports generated on different divisions and head quarters.
13
This also acted as a basis on which to validate the information obtained from the interviews. 3. Observation technique. Observation technique helped the researcher to physically observe the processes involved in revenue collection, and the characteristics of these processes for example the speed of information ?ow and the number of personnel involved.
3.3 Tools used in Analysis and Design of the System
After collecting data about the existing system at KCC, the researcher made use of the following tools to analyse and design the new system;
1. Entity Relationship Diagram The E-R Diagram was used in data modeling to set up an entity relational model of the system. This involved determining the relationships between the various entities in the system and associating these entities with their attributes and attribute domains. 2. Data Flow Diagram The Data Flow Diagram (DFD) was used to model the processes involved in the system to show how data ?ows into and out of the system in a top-down manner by giving a graphical representation of a system’s components, processes and how they interface with each other.
3.4 Tools used in development and implementation of the system
1. A scripting language (PHP in particular) embedded in HTML was used to develop graphical user interfaces (GUIs) that the users would interact with while logically interacting with the server. 2. MySQL database management system was used in setting up a server based database for data storage and querying to generate reports like summaries on revenue collection by type, month and also pending payments.
14
Chapter 4 STUDY AND INVESTIGATION OF THE EXISTING SYSTEM
Using the data collection methods stated in the methodology in Chapter 3, the following information about revenue management in KCC was obtained. KCC is divided into ?ve divisions namely: Kawempe, Nakawa, Central, Makindye and Rubaga. With in each division there are a number of resources that generate revenue for the city council. Some KCC revenue resources are tendered by companies while others are managed by the KCC revenue collection of?cials from the various divisions. The tendering companies collect revenue from these resources on behalf of KCC and submit records on revenue collection to the KCC for a given period of time (either daily basis or monthly basis depending on the agreement between the ?rm and KCC). Generation of revenue collection reports at KCC is done by considering collection per resource per division. The revenue collection report displays the resource identi?cation, where it is located and how much was collected on which date. KCC’s Sources of Revenue. KCC has a number of source of revenue that include Graduated tax, Markets, Trading Licence, Tax Parks,Funding Agencies, Street Parking, Properties like ground rent and property rate value. The process involved in revenue collection varies from one resource to another.
1. Graduated Tax Collection This is in form of arrears, current, surcharge, fees from appeal and replacement of lost ticket. (a) Arrears - If the Graduated tax payer had some Graduated taxes not paid for the past 15
years. (b) Current - The graduated tax of the current year. (c) Surcharge - If the Graduated taxpayer has delayed to pay the G/tax of the current year, then he must pay extra 50% of the G/tax. (d) Fees from appeal - If the G/tax payer is not satis?ed with the assessment of his property and determination of the G/tax he is supposed to pay, he submits an appeal with a fee for appeal. (e) Replacement of ticket - If the G/tax payer has lost the ticket and needs a new G/tax ticket, then he must pay for the replacement. 2. Property Tax. This is charged on every building within the boundaries of the City Council.It involves property rates (value) and ground rent. The purpose of this tax is to help KCC in providing services to residents like road network, street lighting, garbage collection, health services, spray of mosquitoes among others. The valuers of the property use their evaluation techniques to come up with the taxable income from the property. Ground rent is payable by people who occupy City Council land by getting a lease premium (5-49 years). 3. Market Charges. These include charges from stalls and lockers speci?cally from people who trade in the markets of KCC. This activity is normally contracted out through tendering process and the tenders normally go up to 3 years. Some of the markets under KCC include among others Owino- St. Balikuddembe, Nakasero, Sawuliyako, Bukoto, Bugolobi, Kalerwe, Kasubi. 4. Taxi /Bus park fees. The collection of the fees from these two resources is tendered and currently UTODA (Uganda Taxi Operators and Drivers’ Association) is in charge of collecting fees from taxi parks and UBOA (Uganda Bus Owners Association) is in charge of collecting fees from the bus park. These two associations must submit revenue collected for a given period to the divisions revenue of?ce. 5. Street Parking. To park your vehicle on the street under KCC, you have to pay some fee depending on the time you spend parking. All these fees generate revenue for KCC. This collection of street parking fees is normally tendered out to companies and currently; Multiplex Limited 16
is doing this. The company that gets this tender must submit revenue collection records to the divisions’ revenue of?ces for a given period of time depending on their agreement with KCC. 6. Trading Licence. Everybody running a business within boundaries of KCC (whether a shop, private school) must operate the business under a KCC Trading Licence. The trading licence fee depends on the size of the business. KCC elects a team from their staff to handle this task of moving from business to business checking for possession of the trading licence. The business person may obtain the trading licence after paying the trading licence fee to division’s revenue of?ces. 7. Funding Agencies. KCC normally receives donations from funding agencies to support projects running in divisions. The funds donated to KCC are distributed to different divisions which act as a source of revenue to the divisions.
Weaknesses in the Existing System
1. The process of generating revenue reports consumes a lot of time as all this is done manually. 2. The process of recording the revenue collections takes a long time for example when a person comes for replacement of a graduated tax ticket, i.e. takes a signi?cant amount of time to check whether that person is a true owner of the lost ticket, whether that person really has no arrears he is supposed to pay or determination of the surcharge on the G/tax if need be. 3. The co-ordination between the KCC head of?ce and the divisions’ revenue collection department is poor. For a person at the headquarters to access information about revenue collections he must either send an of?ce messenger or call directly to the division. Similarly, submission of reports to the head of?ce by the divisions ’ revenue of?cers would need a person to travel there. This takes a signi?cant time in the organization process of KCC.
17
Chapter 5 SYSTEMS ANALYSIS AND DESIGN
5.1 Requirements
5.1.1 User Requirements.
A number of staff members from the revenue collection of?ces of some of the divisions (who are the users-to-be of the system) were approached by the researcher during the system study and were asked how and what they would need the system to be and the following were the ?ndings:-
1. A system that is easy to learn and adapt. 2. One that is fast in producing results thus reducing processing time and increasing user productivity. 3. A system that would improve on the ef?eciency of information storage and retrieval. 4. A system that would allow them take a few steps to get done with any task. 5. A system that has an element of consistence especially on the interface. For example, a label or icon to mean the same thing throughout the system. 6. A system that has an element of error validation. That is, a system that would prompt a user on entering unusual commands or data format incosistent with the database. 7. A system that would provide attractive interfaces with easy navigation throughout the system. 18
5.1.2 Functional requirements.
The system performs the following functions for the users;-
1. Recording of revenue collections made for KCC from a particular resource by the revenue collection of?cers in a given division. 2. Generation of reports about the revenue collected for KCC per division per resource per Month by the revenue collection of?cers in a given division or at the headquarters. 3. Tracking of the division’s resource that does better in revenue collection by speci?cally looking at the totals collected per division.
5.1.3 Non-functional Requirements.
1. The system authorizes users (who must be members of the revenue collection of?cers from each division under KCC)to access its sensitive parts. 2. The user interfaces were designed using the KCC colours (Green, Orange, Black) 3. The system has made the division’s operations independent from each other but the Headquarter’s revenue of?cers have access to all the division’s revenue collection system modules.
5.1.4 System Requirements
1. Software Requirements.
Software Component Operating system for the server Operating system for the client PCs Web server Web browser Data base management system
System Requirement Linux, windows NT,2000,2003 Linux, Windows NT,2000,XP Apache Web server version 3.1 MS Internet Explorer 6.0 or above Mysql Server Version 3:23.48- Max Log
19
2. Hardware Requirements.
Hardware Component Processor Processor speed Memory Disk Space Bandwidth
System Requirement Intel Pentium III or above 800 MHZ or above 128 MB RAM or above depending on the Operating system 10 GB or above 100 MBps
5.2 System Design
5.2.1 Introduction
The researcher approached the design of the system by modeling the processes and data involved in the system.
5.2.2 Process Modeling
The researcher identi?ed the processes with their inputs and outputs in the system, the external entities involved in the system and put them together to build a Data Flow Diagram (DFD) that helped to show the ?ow of data in the system. The following are the symbols used to design the DFDs.
Figure 5.1: Symbols used in the design of DFDs
1. The Context Diagram The Context Diagram for the Online Revenue Management System for KCC is shown below;
Figure 5.2: Context Diagram depicting the Online Revenue Management System for KCC
2. Modeling the Level 1 DFD for the Online Revenue Management System for KCC. The whole system depicted in the context diagram above is made up of different processes that work together to achieve the goals of this Online Revenue Management System for KCC. The following are the processes that make up the above system; (a) Register Revenue Resource (b) Register Revenue Collection (c) Determine General Revenue Performance. (d) Generate Revenue Report.
21
The model of the Level DFD is shown below;
Figure 5.3: Level 1 Data Flow Diagram For The Online Revenue Management System For KCC
22
3. Data Dictionary for the Level 1 DFD. The researcher provided a dictionary for the processes, the data stores and data ?ows involved in the system as shown in the tables below; (a) Proceses
Figure 5.4: Table for Processes in the DFD
23
(b) Data Stores
Figure 5.5: Table for Data Stores in the DFD
(c) Reports. Revenue Reports. Display of revenue collections made from the respective revenue resources, division and in a given period. (d) Data Flows
Figure 5.6: Table for Data Flows in the DFD
24
(e) External Entities
Figure 5.7: Table for External Entities in the DFD
4. Architectural Design of the Online Revenue Management System for KCC. In order to show a high level view of the designed system with its main components and the services provided by these components as well as how the components work together as a team, the researcher produced the architectural design for this system being designed as shown below;
Figure 5.8: Architectural Design for the Online Revenue Management System for KCC
25
5.2.3 Data Modeling
1. Data Requirements The researcher used the results of the existing system to come up with the data requirements that state the data to be modeled and the conditions that must hold over this data. The following are the data requirements for the data to be modeled;
(a) KCC has a number of Revenue Resources namely Graduated tax, Property tax, Market charges, Tax/Bus park fees, Street parking and Trading License. (b) Each division has at least one of the above categories of revenue resource and each revenue resource has different characteristics from others in KCC. Information kept about a division includes division ID, the division name and address. Each of the ?ve divisions of KCC is uniquely identi?ed by its ID. (c) Information about Graduated tax includes; Graduated tax ticket Identi?cation, Division ID, ticket number, collection date, ticket holder name and address and amount paid. The ticket expires with in the year it was issued. A combination of the Division ID, year and graduated tax ticket number forms the graduated tax ticket identi?cation which uniquely identi?es a collection made from the graduated tax revenue resource in KCC. (d) Information kept about property tax includes Property tax identi?cation, Property Rate Value, Ground Rent Value, ID of the division where the property is located, Property Number and Property Owner, location, and the collection date. The property tax expires with in the year when it is paid. The combination of Division ID, collection date, and Property Number forms the property tax identi?cation which uniquely identi?es a property tax collected in KCC. (e) Information kept about Market Charges includes market identi?cation, Division ID, Market Name, Identi?cation of Company tendering the market, amount collected, and date of collection. The combination of Division ID, Market Name and Date of collection forms the market identi?cation which uniquely identi?es a collection made from the market in KCC. (f) Information kept about Taxi/Bus Parks fees includes park identi?cation, Division ID, Identi?cation of Company tendering the Taxi/Bus Park, Name of the Taxi/Bus Park, Date of collection and amount collected. The combination of Division ID, Name of 26
Tax/Bus Park and Date of collection forms the park identi?cation which uniquely identi?es a collection made from a Taxi/Bus Park in KCC. (g) Information kept about Street Parking includes street identi?cation, Division ID, Identi?cation Company of tendering the Street Parking Revenue collection, Date of collection, and amount collected as well as Street Name. The combination of Division ID, Street Name and Date of collection forms the park identi?cation which uniquely identi?es a collection made from street parking in KCC. (h) Information kept about Trading License includes license identi?cation Division ID, License Number, collection date, Name of holder and address, business name, and amount paid. Trading License expires with in the year when it is paid. A combination of Division ID, License Number and year of issue forms the license identi?cation which uniquely identi?es a Trading License collection made in KCC. (i) Information kept about Donations includes Donation Identi?cation, Division ID, Funding Agency Name, Donation Date, Amount donated. The Donation Identi?cation (a combination of Division ID, Funding Agency and Donation Date) uniquely identi?es a donation received by KCC. (j) Information kept about a tendering company includes Company ID, Company Name, the company address.The Company Id uniquely identi?es a tendering company at KCC.
27
2. Conceptual Data Model. This was formed by identifying the entities involved in the system and their associated attributes. This model gave a basis on which the Entity Relational Model was produced by identifying the characteristics of the entities in the system. The tables below give the various entities in the system and their associated attributes. (a) Graduated Tax
28
(b) Property Taxes
29
(c) Market Collections
30
(d) Taxi/Bus Park Fees
31
(e) Street Parking Fees
32
(f) Trading Licence Fees
(g) Divisions in KCC
33
(h) Donations to KCC Divisions
(i) Tendering Companies
34
3. Modeling Relationships Between The Entities Participating In This System.
(a) The Market-Division Relationship
Figure 5.9: The Market-Division Relationship
Each division has one or more markets and each market belongs to a particular division. Thus the cardinality of this relationship is M: 1. (b) The Street-Division Relationship
Figure 5.10: The Street-Division Relationship
Each division has one or more streets and each street in located with in a particular division. Thus the cardinality of the relationship is M: 1. (c) The Trading License-Division Relationship
Figure 5.11: The Trading License-Division Relationship
Each business is conducted with in a particular division where it pays the trading license from and each division has one or more businesses being run with in it. Hence the cardinality of this relationship is M: 1. (d) The Taxi/Bus Park-Division Relationship 35
Figure 5.12: The Taixi/Bus Park-Division Relationship
Each Taxi/Bus Park belongs to a particular division and a division can have one or more Taxi/Bus Parks. Hence cardinality is M: 1. (e) The Property-Division Relationship
Figure 5.13: The Property-Division Relationship
Each property is located in a particular division and each division can have one or more properties located in it from where revenue collections are made. Thus the cardinality of this relationship is M: 1. (f) The Graduated Tax-Division Relationship
Figure 5.14: The Graduated Tax-Division Relationship
Each division can issue one or more graduated tax tickets to its residents and hence a revenue collection from the graduated tax ticket but each graduated tax ticket is issued with in a particular division. Hence cardinality of the relationship is M: 1.
36
(g) The Donation-Division Relationship
Figure 5.15: The Donation-Division Relationship
A division may receive one or more donations from funding agencies but each donation is given to a particular division. Hence cardinality of the relationship is M: 1. (h) The Market-Tendering Company Relationship
Figure 5.16: The Market-Tendering Company Relationship
A company may tender one or more markets but each market is tendered by one company. Hence cardinality of the relationship is M: 1 (i) The Taxi/Bus Park-Tendering Company Relationship
Figure 5.17: The Taxi/Bus Park-Tendering Company Relationship
A company may tender one or more parks but each park is tendered by one company. Hence cardinality of the relationship is M: 1.
37
(j) The Street Parking-Tendering Company Relationship
Figure 5.18: The Street Parking-Tendering Company Relationship
A company may tender one or more streets but each street is tendered by one company. Hence cardinality of the relationship is M: 1.
38
4. Integrating the Relationships Above To Model The Entity Relationship Diagram For The Online Revenue Management System For KCC.
Figure 5.19: The Entity Relationship Diagram for the System
39
Key to the Entity Relationship Diagram.
Figure 5.20: The Key to Entity Relationship Diagram for the System
5. Mapping the Entity Relational Model onto the Relational Schema After constructing the Entity Relational Model for the system, the researcher went further to map this model onto the relational schema so as to design the structures of the relations to be constructed in the database implementation. (a) Division Table
Figure 5.21: The Structure of Divisions Table
40
(b) Market Table
Figure 5.22: The Structure of Market Table
(c) Street Table
Figure 5.23: The Structure of Street Table
41
(d) Property Table
Figure 5.24: The Structure of Property Table
(e) Graduated Tax Table
Figure 5.25: The Structure of Graduated Tax Table
42
(f) Donation Table
Figure 5.26: The Structure of Donation Table
(g) Trading License Table
Figure 5.27: The Structure of Trading License Table
43
(h) Taxi/Bus Park Table
Figure 5.28: The Structure of Taxi/Bus Park Table
(i) Tendering Company Table
Figure 5.29: The Structure of Tendering Company Table
44
Chapter 6 SYSTEM IMPLEMENTATION
6.1 Introduction
This chapter presents the results of the design and implementation of the system. The system is made up various components each of which performs a speci?c role to achieve the objectives of this project as stated earlier in the project report.
6.2 Components of the Online Revenue Management System for KCC.
The system is made up of the following components; 1. The Graphical User Interface (GUI). This component mainly allows the user to interact with the system. It provides the following services; (a) Authentication of Users. The system allows the users to login into the system so as to gain access to the system services. (b) Registration and management of users. Through submission of user pro?les to the system, the users can be registered and managed. (c) Data entry forms. The system provides the user with data entry forms through which they enter revenue records into the backend of the system. 45
(d) Reports Display. The system provides the users with a display of generated reports about revenue collections per resource and division. 2. The database component. This component serves as the back-end of this system and provides the following services; (a) Insertion. Allows the user to commit data to the system’s database. (b) Deletion. System allows the user to delete unwanted data in its database. (c) Update. The system allows a user to make changes to existing records in its database. (d) Searching. The system allows the user to search information about a given record in its database. (e) Data retrieval. The system allows a user to retrieve data about a given record. 3. Reports Generator. This component helps to display results of the system to the user in form of reports. It provides the following services; (a) Display of reports about the revenue collected from all resource within a particular division on monthly, quarterly and annual basis. (b) Display of reports about performance of a given division per resource for a given month, quarter or year. The system records revenue collections made for KCC for different resources and divisions, generates periodical (monthly,quarterly and annual) revenue collection reports per resource and division and tracks the performance of different resources and divisions in terms of revenue collections to assist management in decision-making. 4. Security Component. This component handles the security issues of the system. (a) User Registration and management. This helps in registering users by giving them access rights to the services provided by the system. Also manages user accounts through updating users’ pro?les and deleting of users who are no longer authorized to access the system. (b) User Authentication. A user is requested to submit user login details created at the time of registration to gain access to the system functionalities. With out the right login information, a user can not access the system. 46
6.3 Physical Design Of The System
The physcal design was divided into
1. Data services layer 2. Function logic 3. Presentation layer
6.3.1 Data Services Layer
MySQL server, a relational database management system (RDBMS) was used to implement the data services layer. MySQL server was chosen because of its fair robustness, reliability and security for large scale online transactional (OLTP) requirements.
6.3.2 Function Logic
The function logic (middle tier component) was designed using PHP(Hypertext Pre-processor), a server-based scripting language and served by Apache web server.
6.4 Presentation Layer
This is also called the user interface. It comprises of the web applications and the local application authored in HTML(HyperText Markup Language) and PHP.
6.4.1 Web Application Functions
The web application was designed to meet web based operations with the following features;
1. A system interface presented to users through the application web interface. 47
2. Users interact with the application using the web browsers. 3. Main pplication functions can only be accessed by authorised users. 4. Authorised users have been assigned usernames and secret passwords,changed regularly (system policy)which they use to access the system. 5. At logon, each user is availed the speci?c functional settings and access rights applying to his/her category.
6.4.2 Local Applications
This is for inputting and retrieving data from the database server. It is installed on the client computers to be used by the revenue collection of?cers of KCC. The table below summerizes the technologies used and functions where they were applied.
Technology MySQL server DBMS PHP Apache web server
HTML
Functions To provide data storage and DBMS services To develop the application(function logic layer) To mediate the interaction between the data services layer with the user interface through the application layer To design the user interface and navigation
48
6.5 Sample of the Program Flow.
The system is designed into two layers in that the system administration part should not be accessed by any other user unless authorized to do so and also the layer for revenue management should only be accessed by those responsible for revenue management. The ?gure below shows the sample of the program ?ow. It shows how the user can navigate through the system.
Figure 6.1: The Sample of the program Flow
49
6.6 Sample of the Forms or Screens of the system.
The system provides a number of forms/screens (either for data capture or for reports displays). Some samples forms are provided in the ?gures below;
1. The Login Page
Figure 6.2: The Appearance of the Login Page
50
2. The Register Revenue Collections Page
Figure 6.3: The Appearance of the Register Revenue Collection Form
51
3. The View Revenue Collections Page
Figure 6.4: The Appearance of the View Revenue Collections Page
52
4. The Monthly General Divisions Performance Report
Figure 6.5: The Appearance of the Monthly General Divisions Performance Report
53
5. The Register Market Collections Page
Figure 6.6: The Appearance of the Register Market Revenue Collections
54
6. Report on Monthly Market Revenue Collections for all the Divisions
Figure 6.7: The Appearance of the Report on Monthly Market Revenue Collections per Division
55
7. Sample Annual Revenue Report Per Resource Per Division
Figure 6.8: The Annual Revenue Collections Report For The Year 2005 Per Division Resource
56
8. Part Of Sample Quarterly Revenue Report Per Resource Per Division
Figure 6.9: Part Of The Quarterly Revenue Report For July - September, 2005 Per Resource Per Division
57
9. Sample Bi-Annual Revenue Report Per Resource Per Division
Figure 6.10: The Bi-Annual Revenue Report For July - December, 2005 Per Resource Per Division
6.7 System Testing.
The researcher only applied alpha testing of this system which produced expected results but the beta testing will be done after installing the system in one of the divisions for KCC whose results will be a basis to install it in the other divisions.
58
Chapter 7 DISCUSSION OF ACHIEVEMENTS, LIMITATIONS AND CONCLUSION
7.1 Introduction.
This section discusses the achievements realised in relation to the objectives of the project, the limitations encountered during the project and is concluded by giving recommendations regarding this project.
7.2 Discussion of Achievements.
The researcher was able to achieve the project’s intended objectives as follows: The users of the system are able to;
1. Register any revenue collection made from any revenue resource for KCC 2. Retrieve stored information about revenue collections made from each revenue resource in form of reports per division per resource on monthly, quarterly, bi-annaully and annual basis. 3. Rank divisions with their respective revenue resources in revenue generation performance by looking at the totals generated per division on monthly, quarterly and annual basis. 4. The system is also customizable for other organisations’ revenue management needs.
59
7.3 Limitations
Much as the researcher was able to obtain the above achievements, a number of limitations were experienced during the project implementation as stated below;
1. During the system study, the KCC staff members (who were among the target groups) to be interviewed were reluctant in giving information regarding revenue management claiming that those are ’sensitive areas’ for KCC. This delayed the speedy progress of the project. 2. During the implementation of the system, there was too much competition for Personal Computers in the Computer Laboratory and a limited number of computers had the software needed which also affected fast progress of the project.
7.4 Recommendations
I recommend that in future if another researcher picks up interest in Revenue Management for KCC or any other institution, this project should be a starting point. Future work may consider all other sources of revenue at KCC although this study considered the major revenue resources only.
60
REFERENCES
1. Andrew B. E. ( 2003): Journal of Forecasting . 5., 559-584. 2. Belobaba. P. P (2002) Back to the future?. Direction for revenue management: Journal of revenue and pricing management. 1(1) 87-89. 3. Brad Coyne, Sonja K. Sadeen (2000): E-Business Asset Management and Capacity Planning. Intel Technology Journal. 94(1) .4. 4. Ianelal Y. and Una Mc. M. B. (2004): Case studies and Applications. Journal of Revenue management and pricing. 20(4) 2. 5. Kimes and Wife (2003): Has Revenue management become acceptable: Finding from an international study, Journal of service research 6(1): 125-135. 6. Marriot(2005). The Marriot Hotel: Linking Two Data Streams With Revenue Management System. http://www.computerworld.com/managementtopics/management/story/0,10801,99963,00.html. 7. RMS(1997).Revenue Management Systems. http://www.revenuemanagement.com/whatisrm.htm (Cited on 3rd June 2005) 8. Smith, E.L. 1994a. Class notes for Range Management RAM 456/556. Fast Copy, University of Arizona, Tucson. 9. Saleemi, N.A(1997) Electronic Data Processing Simpli?ed: 2nd ed. Nairobi Saleemi Publishers. 10. Seqev A. Bean C, Bean, C and Shanthikumar J. (2001): Optimal design of Internet based auctions Information Technology and Management 2(2) 121-163, 2001. 11. URA,1991. Uganda Revenue Authority Statute No. 6 of 1991 61
12. Van. R. G. and Gil. J. (1987): Optimal Dynamic Actions for Revenue Management: Journal of Economic Literature, 25 (2) 699-738 4. 13. Wallace, P (1983). Records Management Information Systems, 2nd ed. New York
62
APPENDIX A: Interview Guide
The interviewer gave an explanation to the respondents about the intention of the interview before proceeding to the questions below:
KCC Headquarters.
1. How many departments are involved in revenue assessment, collection, and storage. How many divisions does KCC have? 2. How many ?rms have been outsourced to collect revenue on behalf of the City Council. 3. Do these people submit reports, if yes may I see some of these reports. 4. How often do divisions submit revenue reports to the headquarters? 5. What are the major processes involved in generating general revenue reports by the City Council and who are players in the process. 6. What are the major sources of revenue to the City Council? 7. Does KCC have a computerized revenue management system, if yes; (a) What processes are involved? (b) Which people work on revenue report generation?
At the Division. Questions asked to the Assistant Town Clerk.
1. What are the major sources of revenue in your division? 2. What ways/methods are used in collection of these revenues? 3. What method is used in documentation and storage of data on revenue? 4. What problems do you face when collecting these revenues. 5. Which people are involved in; (a) Collection of revenue 63
(b) Storage of revenue collection (c) Preparation of revenue report (d) Co-ordination with the KCC Headquarters
APPENDIX B: Sample Program Code
As stated earlier in the methodology used to develop this system, the researcher used PHP, HTML and MySQL computer languages to design the system. The researcher opted to provide the Structured English that re?ects the actual program code in the languages stated above. Below are samples of Structured English used.
1. Structured English to Produce the Register Market Collections Form
Print Heading for the Form
Provide option for user to select a Division Name
Provide Text box to enter Market Name
Provide Text box to enter Amount Collected
Provide Text box to enter Collection Date (Date is pasted in this textbox by default the computer system’s date).
Provide Text box to enter Name of the Company Tendering the market.
Extract the data entered by user in System Variable to use in PHP code.
64
Validate this user input and give communication to the user for invalid data.
If data entered is valid, then proceed or else exit from here.
Connect to the local host with appropriate login details.
Select database called ’zbabwona’ using a MySQL statement.
Check user input against the existing data in the database for duplication.
Commit the transaction to the database if there are no duplicate problems.
Notify the user of success in registering a market collection.
Provide submission button and other navigation buttons.
65
2. Structured English to Produce the General Division Performance Report
Print Report Heading/Title.
Connect to the local host with appropriate login details.
Select database called ’zbabwona’ using a MySQL statement.
Write SQL statements to select totals per revenue resource per division.
Write PHP code to extract SQL query results to a data structure like an array.
Draw the table and populate it with the contents of the data structure created.
Provide Navigation Buttons.
66
doc_234371410.pdf
Revenue sharing has multiple, related meanings depending on context. In business, revenue sharing refers to the sharing of profits and losses among different groups.
An Online Revenue Management System: Case Study-Kampala City Council(KCC).
By Babwona Ziria Naiwumbwe, BSTAT,MA(EPM),(Mak)
A Research Report Submitted for the study leading to a Project in partial ful?lment of the requirements for the award of a Post Graduate Diploma in Computer Science of Makerere University
Supervisor Ms. Rehema Baguma, Faculty of Computing and Information Technology Makerere University,
November, 2005
Declaration
I, Babwona Ziria Naiwumbwe, do declare that this report is my own work and that has never been previously presented anywhere for the award of any degree. Signed :——————————— Date :——————————— BABWONA ZIRIA NAIWUMBWE Candidate (2004/PGD18/175U)
Approval
This project has been under my supervision and has my approval for submission Signed :——————————— Date :——————————— MS. REHEMA BAGUMA Faculty of Computing and Information Technology
ii
Dedication
This work is dedicated to my mother Hajati Aisha Olwitta and in memory of my father, the late Haji Sulaimani Olwitta who never lived to reap the fruits of his sweat.
iii
Acknowledgement
I thank the almighty God for having seen me through this course up to the production of this report, with his mercy and blessings many people became of great help and assistance to enable me reach this stage. I am highly indebted to my supervisor Ms. Rehema Baguma lecturer, Faculty of Computing and Information Technology for her guidance, constructive critisms and interest she showed in my work. Special thanks go to the entire course mates and indeed my fellow group members for cooperation they showed throughout the entire course. I would like to thank the staff of K.C.C Revenue Department and special thanks go to the Principal Town Clerk for Kampala Central Mr. Stephen Higobelo who was co-operative and provided me with all the necessary information and also allowing me access to their revenue ?les. My appreciation is extended to my employer Bank of Uganda, for giving me chance and the facilitation to this course. Finally, failure to acknowledge the role my family played would be unfair. Without their understanding, encouragement and inspiration, my efforts would have been to no avail. Many other people are inevitably omitted from this acknowledgement, not as an act of ingratitude but because of limited space. Otherwise I am very grateful for all the assistance they rendered to me. Any error and/or omissions in this project are entirely my own responsibility.
iv
Contents
Declaration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Approval . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Dedication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Acknowledgement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . i ii iii iv
Abstract . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xii
1
INTRODUCTION 1.1 1.2 1.3 Background to the study . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Statement of the Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Objectives of this Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.3.1 1.3.2 1.4 1.5 Major Objective . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Speci?c Objectives of this Project . . . . . . . . . . . . . . . . . . . . . .
1 1 2 3 3 3 3 3
Scope of the Study . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Signi?cance of the Study . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2
LITERATURE REVIEW
5
v
2.1 2.2 2.3 2.4
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Revenue management systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . Computerized Revenue Management . . . . . . . . . . . . . . . . . . . . . . . . . Some Case Studies of Computerized Revenue Management . . . . . . . . . . . . . 2.4.1 Marriot(2005) [6]: Linking Two Data Streams With Revenue Management System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.4.2 The Uganda Revenue Authority (URA) . . . . . . . . . . . . . . . . . . .
5 6 7 8
8 9
2.5
The Bene?ts of Computerized Revenue Management . . . . . . . . . . . . . . . . 11 2.5.1 Problems of implementing Online Revenue Management Systems . . . . . 12
3
Methodology 3.1 3.2
13
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Data collection techniques/tools used in studying and investigating the existing system . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
3.3 3.4
Tools used in Analysis and Design of the System . . . . . . . . . . . . . . . . . . 14 Tools used in development and implementation of the system . . . . . . . . . . . . 14
4
STUDY AND INVESTIGATION OF THE EXISTING SYSTEM
15
5
SYSTEMS ANALYSIS AND DESIGN 5.1
18
Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 5.1.1 5.1.2 User Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Functional requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 vi
5.1.3 5.1.4 5.2
Non-functional Requirements. . . . . . . . . . . . . . . . . . . . . . . . . 19 System Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
System Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 5.2.1 5.2.2 5.2.3 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Process Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Data Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
6
SYSTEM IMPLEMENTATION 6.1 6.2 6.3
45
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Components of the Online Revenue Management System for KCC. . . . . . . 45 Physical Design Of The System . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 6.3.1 6.3.2 Data Services Layer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Function Logic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
6.4
Presentation Layer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 6.4.1 6.4.2 Web Application Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Local Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
6.5 6.6 6.7
Sample of the Program Flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Sample of the Forms or Screens of the system. . . . . . . . . . . . . . . . . . . . . 50 System Testing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
7
DISCUSSION OF ACHIEVEMENTS, LIMITATIONS AND CONCLUSION
59
vii
7.1 7.2 7.3 7.4
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Discussion of Achievements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Recommendations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
REFERENCES
61
APPENDIX A: Interview Guide . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 APPENDIX B: Sample Program Code . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
viii
List of Figures
5.1 5.2 5.3 5.4 5.5 5.6 5.7 5.8 5.9 Symbols used in the design of DFDs . . . . . . . . . . . . . . . . . . . . . . . . . 20 Context Diagram depicting the Online Revenue Management System for KCC . . . 21 Level 1 Data Flow Diagram For The Online Revenue Management System For KCC 22 Table for Processes in the DFD . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Table for Data Stores in the DFD . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Table for Data Flows in the DFD . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Table for External Entities in the DFD . . . . . . . . . . . . . . . . . . . . . . . . 25 Architectural Design for the Online Revenue Management System for KCC . . . . 25 The Market-Division Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . 35
5.10 The Street-Division Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 5.11 The Trading License-Division Relationship . . . . . . . . . . . . . . . . . . . . . 35 5.12 The Taixi/Bus Park-Division Relationship . . . . . . . . . . . . . . . . . . . . . . 36 5.13 The Property-Division Relationship . . . . . . . . . . . . . . . . . . . . . . . . . 36 5.14 The Graduated Tax-Division Relationship . . . . . . . . . . . . . . . . . . . . . . 36 5.15 The Donation-Division Relationship . . . . . . . . . . . . . . . . . . . . . . . . . 37
ix
5.16 The Market-Tendering Company Relationship . . . . . . . . . . . . . . . . . . . . 37 5.17 The Taxi/Bus Park-Tendering Company Relationship . . . . . . . . . . . . . . . . 37 5.18 The Street Parking-Tendering Company Relationship . . . . . . . . . . . . . . . . 38 5.19 The Entity Relationship Diagram for the System . . . . . . . . . . . . . . . . . . . 39 5.20 The Key to Entity Relationship Diagram for the System . . . . . . . . . . . . . . . 40 5.21 The Structure of Divisions Table . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 5.22 The Structure of Market Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 5.23 The Structure of Street Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 5.24 The Structure of Property Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 5.25 The Structure of Graduated Tax Table . . . . . . . . . . . . . . . . . . . . . . . . 42 5.26 The Structure of Donation Table . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 5.27 The Structure of Trading License Table . . . . . . . . . . . . . . . . . . . . . . . 43 5.28 The Structure of Taxi/Bus Park Table . . . . . . . . . . . . . . . . . . . . . . . . . 44 5.29 The Structure of Tendering Company Table . . . . . . . . . . . . . . . . . . . . . 44
6.1 6.2 6.3 6.4 6.5
The Sample of the program Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 The Appearance of the Login Page . . . . . . . . . . . . . . . . . . . . . . . . . . 50 The Appearance of the Register Revenue Collection Form . . . . . . . . . . . . . 51 The Appearance of the View Revenue Collections Page . . . . . . . . . . . . . . . 52 The Appearance of the Monthly General Divisions Performance Report . . . . . . 53
x
6.6 6.7 6.8 6.9
The Appearance of the Register Market Revenue Collections . . . . . . . . . . . . 54 The Appearance of the Report on Monthly Market Revenue Collections per Division 55 The Annual Revenue Collections Report For The Year 2005 Per Division Resource Part Of The Quarterly Revenue Report For July - September, 2005 Per Resource Per Division . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 56
6.10 The Bi-Annual Revenue Report For July - December, 2005 Per Resource Per Division 58
xi
Abstract
Today more and more people are relying on information technology to perform their operations more ef?ciently. Computers are put to more use as tools of commerce, governance and education and they can no longer be looked at as luxury items. Kampala City Council (KCC) is a local government body that provides services for the people of Kampala City. It is in the process of establishing information technology department in an effort to computerize its operations and service delivery to the public. It is better at this time for the revenue department to come up with a computerised system that can be incorporated in the main system, as part of improving operations and service delivery. The system study found that the existing system (manual) was very slow, prone to errors and hard to quickly generate integrated reports for fast decision-making. This project has developed an automated alternative to revenue management. The system records revenue collections made for KCC for different resources and divisions, generates periodical (monthly,quarterly and annual) revenue collection reports per resource and division and tracks the performance of different resources and divisions in terms of revenue collections to assist management in decision-making.
xii
Chapter 1 INTRODUCTION
1.1 Background to the study
Kampala City Council(KCC) is the Kampala City government administrative body located within the heart of Kampala, the capital City of Uganda. Administratively, Kampala has under gone transformations which can be seen as the true origins of the KCC. In 1906, Kampala was of?cially declared a township. On January,1st 1949 it was raised to municipality status. Kampala was declared a City Council and a Capital of Uganda on October 9th 1962 after Uganda had attained her independence. KCC is headed by the City Council Chairman(who is titled ”Mayor”) and the Mayor has a Vice Chairman who works under him. The hierachy is then followed down by the Councilors who are followed by the chairmen of committees under KCC. The Town Clerk then follows these committee chairmen and under the Town Clerk, there are Heads of departments. Originally the city was limited to a small area of about 50 square Kms but today it extends for about 195 square Kms, divided into seven political units. These are Central, Makindye (South), Nakawa (East), Lubaga (West) and Kawempe (North) divisions, each with a seat in parliament. The Universities comprise of Makerere and Kyambogo also comprise of political divisions but are not represented in parliament. The population of Kampala has been growing at an alarming 3 percent per annum since the 1920s. At the time it was declared a township (1906), Kampala had 30,000 people, by 1914 it had in-
1
creased to 35,390. When it became a municipal council (1949) it had roughly 58,000 persons. At independence the population had swollen to over 200,000. After the 1968 decree the neighborhoods were incorporated into Kampala, and the population stood at 331,000 persons. According to the local government Act of 1997 and as part of decentralization programme, Kampala has been divided into 5 divisions. Each division collects its own revenue of which 65% is retained and the rest goes to the City Council Headquarters. Globally, according to RMS(1997) [7],Revenue Management started in early 80’s in airline industry. In Uganda, revenue management module forms part of the Integrated Financial Management System (FMS)- a budget execution and expenditure management sub-system to monitor and account for revenues and public expenditures. The sources of revenue for KCC include land tax, property tax, graduated tax and income tax among others. KCC out sources different local ?rms to collect taxes on its behalf like Multiplex Limited(responsible for collecting parking fees from vehicle owners who park their vehicles on the streets within KCC boundaries), Uganda Tax Operators and Drivers Association (UTODA)( responsible for collecting parking fees from public service vehicles parked in the taxi parks). Apart from internally generated revenue, KCC also receives grants from development partners like World Bank and International Monetary Fund(IMF) to ?nance development projects of the city. All this revenue needs to be managed effectively for the smooth running of the City Council activities for example for infrastructure development, social economic projects on HIV/AIDS,environmental protection among others.
1.2 Statement of the Problem
Currently, KCC is still using a manual system for collecting and managing their revenue. The system is very slow,prone to errors and hard to quickly generate integrated reports for fast decision making. This formed the motivation for this study to provide an automated alternative to the current manual system used in revenue management.
2
1.3 Objectives of this Project
1.3.1 Major Objective
The major aim of this project was to develop and implement an Online Revenue Management System for Revenue Collection and management for KCC.
1.3.2 Speci?c Objectives of this Project
The speci?c objectives of this project were to;
1. Study and investigate the processes involved in revenue management at KCC. 2. Identify the weaknesses in the current processes used to manage revenue at KCC. 3. Design and develop a system that would; (a) Improve the ef?ciency and effectiveness of revenue collection by KCC with less time wasting and less errors. (b) Provide automated report generation on the revenue collections per resource,division, and the entire KCC for easy planning and decision making.
1.4 Scope of the Study
The project focused on the function of collection, processing and management of revenue by KCC and exploring how these processes can be automated.
1.5 Signi?cance of the Study
The project was able to contribute to increasing awareness about the importance of automating management of revenue. The awareness was backed by a software application developed based
3
on the KCC environment but customisable for use by other revenue management entities both government and non-government.
4
Chapter 2 LITERATURE REVIEW
2.1 Introduction
According to RMS (1997) [7], Revenue Managment is the application of disciplined tactics that predict consumer behavior at the micromarket level and optimize product availability and price to maximize revenue growth. Revenue Management is about maximizing revenue from existing business. It’s a hard management science that employs rocket-science mathematical concepts and high-powered computers to crunch gigabytes of marketing information to:
1. Accurately assess future consumer behavior under dynamically changing market conditions 2. Determine the most effective way to price and allocate inventory to reach and every future consumer, each and every day, making real-time adjustments as market conditions change, with the consumer in real-time 3. Communicate this information instantaneously to distribution and sale outlets which deal with the consumer in real-time 4. Serve as a decision-support resource for marketing and operational functions, including but not limited to: pricing, scheduling, product development, advertising, sales, distribution, human resource utilization and capacity planning.
When Revenue Management was ?rst applied in the airline industry in the early 80’s, it was applied manually. However, the enourmous scope of the problem (managing tens of thousands of 5
?ight departures departing over the next year) convinced the early adopters of this practice that only through computerization could the airline capture the revenue opportunity that Revenue Management provided.
2.2 Revenue management systems
According to Van(1987) [12], revenue management traditionally involves segmenting customers, setting prices and controlling capacity to maximize the revenue from a ?xed capacity . The traditional revenue management model is where the seller must determine the capacity to sell in a given period before observing demand in future periods as provided by Seqev et al(2001) [10]. According to Belobaba (2002) [2],a wide range of industries have joined airlines, hospitability and travel companies in embracing revenue management as an essential component of their business strategies. Large databases of consumer information developed to improve customer relationship management will provide inputs to consumer choice models, which will in turn call prices to be set based on estimates of demand elasticity. According to Andrew (2003) [1], revenue management systems operate by periodically running forecasting and optimization along on this in order to refresh control parameters in the control reservation system and concerns have been voiced over the best way to update bid prices in the interim period between refresh points. According to Belobaba (2002) [2],joint optimization of price and inventory, combined with realtime updates based on the most current demand forecasts and available inventory, will generate incremental revenue gains that exceed those realized to date. According to Kimes et al(2003) [5],demand based pricing is underused in many service industries, because customers are believed to perceive such pricing as fair. Fencing can be highly effective in improving the perceived fairness of demand based pricing. Andrew (2003) [1] quotes Sabre et al.,(1998) [8] that over booking, demand forecasting taking willingness to pay into account optimizing the mixture of fare products involved or dynamically to the point where rocket science” is now an opt descriptor collectively known as revenue management. This is from the ?rst revenue management areas of freight engineering and many freight 6
revenue management systems have been developed. INTEL has also developed an asset management program, which is a single bared web based source from which the physical and con?guration of data for each of the series in the e-business product and pre-production environments can be observed (Brad et al., 2000) [3]. This means that effective asset management and revenues generated can be effectively managed by one of better processes and tools with good discipline of the audience. According to Ianelal (2004) [4], the dif?culty for revenue management does not know when and where discounts are needed to stimulate demand. Revenue management and pricing is a practical subject that is more than a management science algorithm. This means that revenue management still needs a lot of study to accommodate better decision-making and right forecasts using right prices and other variables.
2.3 Computerized Revenue Management
According to RMS (1997) [7], today, most organization managers understand that they are not maximizing the revenue potential from each and every asset. They are under- utilizing their most valuable asset. In an attempt to maximize their revenue collections, most organizations implement some type of revenue management methodology. Some organizations set global authorization levels for certain resources in their areas of location. Others assign analysts to examine the status of future business. Still others have implemented simple spreadsheet approaches to analyze resource performance. Each of these solutions captures additional revenue that would otherwise be lost. But in fact none of these solutions allow the organization to capture the wasted revenue. This is because a number of factors limit their ability to deliver the optimal solution. First, the different number of revenue sources an organization has,require maximum attention. Second, the variations in the revenues generated from the organization’s resources(for example from markets as for the case of KCC ) make it impossible to assume that what took place last week will probably happen again this week(if it didn’t rain on the market day last week and many traders brought goods to the market thereby increasing revenue through taxes imposed on them may be a different story when it rains on the next market day).
7
2.4 Some Case Studies of Computerized Revenue Management
2.4.1 Marriot(2005) [6]: Linking Two Data Streams With Revenue Management System
The whole can sometimes be greater than the sum of its parts. Marriott International Inc. found that to be true after it combined two revenue management systems into one. Revenue data collected by Marriott has many uses, such as predicting customer needs. But two separate revenue management systems kept the hotel chain from easily analyzing revenue data for forecasting and marketing insights generated by 62 million reservations annually at 2,800 properties. It was also expensive to operate one system for its full-service hotels and another for select-service or extended-stay facilities. And system upgrades and training typically required IT visits to hotels around the globe. They wanted to lower their cost and increase their speed to market. The new system, known as One Yield, has accomplished that. By combining the two systems, the need for support staff is 33% lower because only one database is used and the system requires little local IT support. Its Web and thin-client-based architecture delivers training remotely and cuts costs. But Marriott Hotel has also improved its ability to forecast changing market conditions. The system’s inventory data consolidation improves planning. The upside: Marriott Hotel estimates that One Yield delivered incremental pro?ts of $6.7 million last year, its ?rst full year in operation. Marriott Hotel chose to build the system in-house because revenue management is specialized and tightly integrated with its reservation system. The IT staff used standard technologies, including a centralized J2EE(Java 2 Platform, Enterprise Edition) architecture, IBM’s WebSphere and Actuate Corp.’s reporting tool. Marriott’s decision to build a centralized system with thin-client technology at global locations puts it ahead of many others in the hospitality industry. That particular industry is not known for being aggressive on technology but that’s changing as more hotels automate systems such as lighting and even supply handhelds to maintenance personnel. A critical goal for the management of Marriot Hotel was to ensure that the project aligned with the business. By combining two systems, the IT team was also merging different methods for managing and analyzing revenue. The business side worked closely with the technical staff. The 8
team had to decide what features to add and discard. Everybody had to compromise a little along the way and the management reports that it was one of the toughest challenges, because the ideas that came up were great ideas. Marriot’s goal was to have scheduled releases of this into the future, get it running, prove success and then continue to build. One Yield has been rolled out to 1,700 properties since the end of 2003 and is gradually being installed at others. It has become a proving ground for technologies, such as single sign-on, that will likely be extended to other systems.
2.4.2 The Uganda Revenue Authority (URA)
Uganda Revenue Authority (URA) was set up on September 5, 1991(URA,1991) [11] as a central body for the assessment and collection of speci?ed tax revenue, to administer and enforce the laws relating to such revenue and to account for all the revenue to which those laws apply. The Uganda Revenue Authority is also required to advise the Government on matters of policy relating to all revenue. Uganda Revenue Authority is charged with the responsibility of providing the foundation for development through revenue mobilization and management to:
1. Finance current and capital development activities. 2. Increase the standard of living of all Ugandans and reduction of poverty; 3. Increase the ratio of revenue to GDP, to a level at which Government can - fund its own essential expenditure.
The above objectives can be achieved by identifying taxpayers and informing the tax payers of their tax rights and obligations by providing them with - necessary information; Assess the taxpayer fairly with regard to those taxes relevant to them; Collect the taxes in accordance with the law and related regulations and practice; Account for the taxes collected in accordance with the statute; and enforce collection of taxes where default has occurred. Departments in URA responsible for revenue management have District Revenue Of?ces in various towns. URA mission is maximizing central government tax revenue while optimizing resource utilization by ensuring a 9
fair and equitable tax administration with a highly motivated and professional staff. In order for URA to increase and properly manage the revenue, there is need to;
1. Provide the taxpayers and their authorized agents with clear, precise and timely information. 2. Ensuring that courtesy and considerate treatment are extended unconditionally to all taxpayers. 3. Responding expeditiously to every taxpayer’s enquiry, complaint or request. 4. Explaining the grounds for and derivation of every tax assessment. 5. Providing proper technical advice to the taxpayer on requests about tax implications. 6. Assisting new taxpayers to register. 7. Educating the taxpayers and the general community about tax obligations and rights, objections and appeals 8. We should normally give prior notice to any taxpayer whose premises are to be inspected or upon whom an audit is to be conducted. 9. Tax records are maintained up to date and Tax credits are processed promptly and properly accounted for. 10. Providing high quality public relations at all levels of the Authority. 11. Accepting criticism, advice at all times, receiving and acting promptly on all genuine complaints made in good faith against URA revenue management system.
It is the obligation of the taxpayer to ensure that he/she is registered and obtains a Tax Identi?cation Number (TIN), which is issued free to every applicant and is the unique identi?er for all tax purposes. Income Tax payers are required to register for Income Tax and obtain an Income Tax File number. Taxable persons for VAT purposes are also required to register for VAT and obtain a VAT registration number.
10
2.5 The Bene?ts of Computerized Revenue Management
According to RMS (1997) [7], the ultimate bene?t of computerized revenue management is increased revenue. Each resource for the organization is managed to ensure that it generates the maximum possible revenue . You can expect this system to generate anywhere from 3% to 8% additional revenue for your business, resulting in potential pro?t increases of 50%-100%. Of course, your level of improvement depends upon how revenue management was utilized at your business prior to the implementation of the computerized system. At the same time, management bene?ts from an effective revenue management system. Management gains a decision-support resource for marketing and operational functions. Having at their disposal a tool that can analyze historical and forecast future behavior is invaluable when planning marketing campaigns, and pricing and schedule changes. New analysts can become effective more rapidly when they have a reference tool that helps them understand the relationship between historical and future behavior. Without such a tool, the analyst only learns about the business’ behavior through extensive experience. The analysts gain a tool that helps them focus their attention where it is needed, when it is needed. A tool that can provide them the con?dence to make the right decision while enabling them to apply their own knowledge and abilities to the maximum bene?t. According to Saleemi, (1997: 13,14) [9], the timing aspect of information is very important, because late information is better than no information. Automatic processing as provided by the electronic system, quickens the operations on the input data to produce timely information. He states further that the degree of accuracy and preciseness of the information is increased by the use of mechanical or electronic data processing tools, which remove the use of hand written entries. Automatic processing, is the process of keeping the data in a safe place and retrieved when required. According to Wallace (1987) [13], Automatic processing is information that is recorded in some form, stored in a system and retrieved in a manner. A record is any piece of information put aside for further use or reference. As per this study, the records include various revenue sources and collections made from different sites. According to Harrison (1996), an important function of the of?ce is the ?ling and ?nding of infor11
mation, which may be held on paper, disk or ?lm and to ensure quick and reliable access to it. He states further that electronic data storage has revolutionarised the speed of retrieval and allowed greater and easier access to vast quantities of records without the need to store papers in bulky shelves. According to Connolly (1996:12), when data is isolated in separate ?les, it becomes more dif?cult to access it.
2.5.1 Problems of implementing Online Revenue Management Systems
Like any other management information system, implementing an online Revenue management system posses alot of challenges to the organisation and among these we have;
1. The need for alot of ?nancial support. Much as the organisation is certain of bene?ting from the results of implementing their revenue management processes online, it needs alot of funds to support such projects. Even when the system is operational, it will need ?nancial support to maintain it. 2. Political in?uence in the organisation. Much as the organisation may be in need of an Online Revenue Management system, the politics within the organisation might cause such a project be abandoned even before completion. 3. Security of the organisation’s information. Once the organisation implements on online revenue management system, then, the information concerning their revenue matters becomes prone to unauthorised access. This means that the organisation will neeed to set up enough security measures both physical and logical security to the implemented revenue management system. 4. Need for more technical expertise. Much as the staff members of the organisation may be used to executing the processes involved in revenue management in their organisation, there will be a need for technical support to run the revenue management system implemented for example the system and database administrators which may not be present in the organisation before the revenue management system is set up. 5. The need for the infrustructure. The organisation may need to create more space for example construction of server rooms, of?ces for the new technical staff and any other physical infrustructure for the system. 12
Chapter 3 Methodology
3.1 Introduction
This section explains the methods used in the study of the existing system, analysing and designing the proposed online revenue management system for KCC.
3.2 Data collection techniques/tools used in studying and investigating the existing system
1. Interviews. This involved interviewing Kampala City Council Revenue Collection Department of?cials and other relevant persons in different divisions on information concerning revenue collection processes and sources. Through the interview, the researcher gave chance to the respondent to ask all relevant questions concerning the topic of the study. This gave the researcher an advantage of getting ?rst hand information from the respondents who were already part of the system under study. 2. Review of existing documentation. Document review was used to understand the current system and get relevant literature on the revenue management techniques that were being used the time the study was done. A number of documents were reviewed ranging from invoices, payment receipts, order papers, and delivery notes in addition to reports generated on different divisions and head quarters.
13
This also acted as a basis on which to validate the information obtained from the interviews. 3. Observation technique. Observation technique helped the researcher to physically observe the processes involved in revenue collection, and the characteristics of these processes for example the speed of information ?ow and the number of personnel involved.
3.3 Tools used in Analysis and Design of the System
After collecting data about the existing system at KCC, the researcher made use of the following tools to analyse and design the new system;
1. Entity Relationship Diagram The E-R Diagram was used in data modeling to set up an entity relational model of the system. This involved determining the relationships between the various entities in the system and associating these entities with their attributes and attribute domains. 2. Data Flow Diagram The Data Flow Diagram (DFD) was used to model the processes involved in the system to show how data ?ows into and out of the system in a top-down manner by giving a graphical representation of a system’s components, processes and how they interface with each other.
3.4 Tools used in development and implementation of the system
1. A scripting language (PHP in particular) embedded in HTML was used to develop graphical user interfaces (GUIs) that the users would interact with while logically interacting with the server. 2. MySQL database management system was used in setting up a server based database for data storage and querying to generate reports like summaries on revenue collection by type, month and also pending payments.
14
Chapter 4 STUDY AND INVESTIGATION OF THE EXISTING SYSTEM
Using the data collection methods stated in the methodology in Chapter 3, the following information about revenue management in KCC was obtained. KCC is divided into ?ve divisions namely: Kawempe, Nakawa, Central, Makindye and Rubaga. With in each division there are a number of resources that generate revenue for the city council. Some KCC revenue resources are tendered by companies while others are managed by the KCC revenue collection of?cials from the various divisions. The tendering companies collect revenue from these resources on behalf of KCC and submit records on revenue collection to the KCC for a given period of time (either daily basis or monthly basis depending on the agreement between the ?rm and KCC). Generation of revenue collection reports at KCC is done by considering collection per resource per division. The revenue collection report displays the resource identi?cation, where it is located and how much was collected on which date. KCC’s Sources of Revenue. KCC has a number of source of revenue that include Graduated tax, Markets, Trading Licence, Tax Parks,Funding Agencies, Street Parking, Properties like ground rent and property rate value. The process involved in revenue collection varies from one resource to another.
1. Graduated Tax Collection This is in form of arrears, current, surcharge, fees from appeal and replacement of lost ticket. (a) Arrears - If the Graduated tax payer had some Graduated taxes not paid for the past 15
years. (b) Current - The graduated tax of the current year. (c) Surcharge - If the Graduated taxpayer has delayed to pay the G/tax of the current year, then he must pay extra 50% of the G/tax. (d) Fees from appeal - If the G/tax payer is not satis?ed with the assessment of his property and determination of the G/tax he is supposed to pay, he submits an appeal with a fee for appeal. (e) Replacement of ticket - If the G/tax payer has lost the ticket and needs a new G/tax ticket, then he must pay for the replacement. 2. Property Tax. This is charged on every building within the boundaries of the City Council.It involves property rates (value) and ground rent. The purpose of this tax is to help KCC in providing services to residents like road network, street lighting, garbage collection, health services, spray of mosquitoes among others. The valuers of the property use their evaluation techniques to come up with the taxable income from the property. Ground rent is payable by people who occupy City Council land by getting a lease premium (5-49 years). 3. Market Charges. These include charges from stalls and lockers speci?cally from people who trade in the markets of KCC. This activity is normally contracted out through tendering process and the tenders normally go up to 3 years. Some of the markets under KCC include among others Owino- St. Balikuddembe, Nakasero, Sawuliyako, Bukoto, Bugolobi, Kalerwe, Kasubi. 4. Taxi /Bus park fees. The collection of the fees from these two resources is tendered and currently UTODA (Uganda Taxi Operators and Drivers’ Association) is in charge of collecting fees from taxi parks and UBOA (Uganda Bus Owners Association) is in charge of collecting fees from the bus park. These two associations must submit revenue collected for a given period to the divisions revenue of?ce. 5. Street Parking. To park your vehicle on the street under KCC, you have to pay some fee depending on the time you spend parking. All these fees generate revenue for KCC. This collection of street parking fees is normally tendered out to companies and currently; Multiplex Limited 16
is doing this. The company that gets this tender must submit revenue collection records to the divisions’ revenue of?ces for a given period of time depending on their agreement with KCC. 6. Trading Licence. Everybody running a business within boundaries of KCC (whether a shop, private school) must operate the business under a KCC Trading Licence. The trading licence fee depends on the size of the business. KCC elects a team from their staff to handle this task of moving from business to business checking for possession of the trading licence. The business person may obtain the trading licence after paying the trading licence fee to division’s revenue of?ces. 7. Funding Agencies. KCC normally receives donations from funding agencies to support projects running in divisions. The funds donated to KCC are distributed to different divisions which act as a source of revenue to the divisions.
Weaknesses in the Existing System
1. The process of generating revenue reports consumes a lot of time as all this is done manually. 2. The process of recording the revenue collections takes a long time for example when a person comes for replacement of a graduated tax ticket, i.e. takes a signi?cant amount of time to check whether that person is a true owner of the lost ticket, whether that person really has no arrears he is supposed to pay or determination of the surcharge on the G/tax if need be. 3. The co-ordination between the KCC head of?ce and the divisions’ revenue collection department is poor. For a person at the headquarters to access information about revenue collections he must either send an of?ce messenger or call directly to the division. Similarly, submission of reports to the head of?ce by the divisions ’ revenue of?cers would need a person to travel there. This takes a signi?cant time in the organization process of KCC.
17
Chapter 5 SYSTEMS ANALYSIS AND DESIGN
5.1 Requirements
5.1.1 User Requirements.
A number of staff members from the revenue collection of?ces of some of the divisions (who are the users-to-be of the system) were approached by the researcher during the system study and were asked how and what they would need the system to be and the following were the ?ndings:-
1. A system that is easy to learn and adapt. 2. One that is fast in producing results thus reducing processing time and increasing user productivity. 3. A system that would improve on the ef?eciency of information storage and retrieval. 4. A system that would allow them take a few steps to get done with any task. 5. A system that has an element of consistence especially on the interface. For example, a label or icon to mean the same thing throughout the system. 6. A system that has an element of error validation. That is, a system that would prompt a user on entering unusual commands or data format incosistent with the database. 7. A system that would provide attractive interfaces with easy navigation throughout the system. 18
5.1.2 Functional requirements.
The system performs the following functions for the users;-
1. Recording of revenue collections made for KCC from a particular resource by the revenue collection of?cers in a given division. 2. Generation of reports about the revenue collected for KCC per division per resource per Month by the revenue collection of?cers in a given division or at the headquarters. 3. Tracking of the division’s resource that does better in revenue collection by speci?cally looking at the totals collected per division.
5.1.3 Non-functional Requirements.
1. The system authorizes users (who must be members of the revenue collection of?cers from each division under KCC)to access its sensitive parts. 2. The user interfaces were designed using the KCC colours (Green, Orange, Black) 3. The system has made the division’s operations independent from each other but the Headquarter’s revenue of?cers have access to all the division’s revenue collection system modules.
5.1.4 System Requirements
1. Software Requirements.
Software Component Operating system for the server Operating system for the client PCs Web server Web browser Data base management system
System Requirement Linux, windows NT,2000,2003 Linux, Windows NT,2000,XP Apache Web server version 3.1 MS Internet Explorer 6.0 or above Mysql Server Version 3:23.48- Max Log
19
2. Hardware Requirements.
Hardware Component Processor Processor speed Memory Disk Space Bandwidth
System Requirement Intel Pentium III or above 800 MHZ or above 128 MB RAM or above depending on the Operating system 10 GB or above 100 MBps
5.2 System Design
5.2.1 Introduction
The researcher approached the design of the system by modeling the processes and data involved in the system.
5.2.2 Process Modeling
The researcher identi?ed the processes with their inputs and outputs in the system, the external entities involved in the system and put them together to build a Data Flow Diagram (DFD) that helped to show the ?ow of data in the system. The following are the symbols used to design the DFDs.
Figure 5.1: Symbols used in the design of DFDs
1. The Context Diagram The Context Diagram for the Online Revenue Management System for KCC is shown below;
Figure 5.2: Context Diagram depicting the Online Revenue Management System for KCC
2. Modeling the Level 1 DFD for the Online Revenue Management System for KCC. The whole system depicted in the context diagram above is made up of different processes that work together to achieve the goals of this Online Revenue Management System for KCC. The following are the processes that make up the above system; (a) Register Revenue Resource (b) Register Revenue Collection (c) Determine General Revenue Performance. (d) Generate Revenue Report.
21
The model of the Level DFD is shown below;
Figure 5.3: Level 1 Data Flow Diagram For The Online Revenue Management System For KCC
22
3. Data Dictionary for the Level 1 DFD. The researcher provided a dictionary for the processes, the data stores and data ?ows involved in the system as shown in the tables below; (a) Proceses
Figure 5.4: Table for Processes in the DFD
23
(b) Data Stores
Figure 5.5: Table for Data Stores in the DFD
(c) Reports. Revenue Reports. Display of revenue collections made from the respective revenue resources, division and in a given period. (d) Data Flows
Figure 5.6: Table for Data Flows in the DFD
24
(e) External Entities
Figure 5.7: Table for External Entities in the DFD
4. Architectural Design of the Online Revenue Management System for KCC. In order to show a high level view of the designed system with its main components and the services provided by these components as well as how the components work together as a team, the researcher produced the architectural design for this system being designed as shown below;
Figure 5.8: Architectural Design for the Online Revenue Management System for KCC
25
5.2.3 Data Modeling
1. Data Requirements The researcher used the results of the existing system to come up with the data requirements that state the data to be modeled and the conditions that must hold over this data. The following are the data requirements for the data to be modeled;
(a) KCC has a number of Revenue Resources namely Graduated tax, Property tax, Market charges, Tax/Bus park fees, Street parking and Trading License. (b) Each division has at least one of the above categories of revenue resource and each revenue resource has different characteristics from others in KCC. Information kept about a division includes division ID, the division name and address. Each of the ?ve divisions of KCC is uniquely identi?ed by its ID. (c) Information about Graduated tax includes; Graduated tax ticket Identi?cation, Division ID, ticket number, collection date, ticket holder name and address and amount paid. The ticket expires with in the year it was issued. A combination of the Division ID, year and graduated tax ticket number forms the graduated tax ticket identi?cation which uniquely identi?es a collection made from the graduated tax revenue resource in KCC. (d) Information kept about property tax includes Property tax identi?cation, Property Rate Value, Ground Rent Value, ID of the division where the property is located, Property Number and Property Owner, location, and the collection date. The property tax expires with in the year when it is paid. The combination of Division ID, collection date, and Property Number forms the property tax identi?cation which uniquely identi?es a property tax collected in KCC. (e) Information kept about Market Charges includes market identi?cation, Division ID, Market Name, Identi?cation of Company tendering the market, amount collected, and date of collection. The combination of Division ID, Market Name and Date of collection forms the market identi?cation which uniquely identi?es a collection made from the market in KCC. (f) Information kept about Taxi/Bus Parks fees includes park identi?cation, Division ID, Identi?cation of Company tendering the Taxi/Bus Park, Name of the Taxi/Bus Park, Date of collection and amount collected. The combination of Division ID, Name of 26
Tax/Bus Park and Date of collection forms the park identi?cation which uniquely identi?es a collection made from a Taxi/Bus Park in KCC. (g) Information kept about Street Parking includes street identi?cation, Division ID, Identi?cation Company of tendering the Street Parking Revenue collection, Date of collection, and amount collected as well as Street Name. The combination of Division ID, Street Name and Date of collection forms the park identi?cation which uniquely identi?es a collection made from street parking in KCC. (h) Information kept about Trading License includes license identi?cation Division ID, License Number, collection date, Name of holder and address, business name, and amount paid. Trading License expires with in the year when it is paid. A combination of Division ID, License Number and year of issue forms the license identi?cation which uniquely identi?es a Trading License collection made in KCC. (i) Information kept about Donations includes Donation Identi?cation, Division ID, Funding Agency Name, Donation Date, Amount donated. The Donation Identi?cation (a combination of Division ID, Funding Agency and Donation Date) uniquely identi?es a donation received by KCC. (j) Information kept about a tendering company includes Company ID, Company Name, the company address.The Company Id uniquely identi?es a tendering company at KCC.
27
2. Conceptual Data Model. This was formed by identifying the entities involved in the system and their associated attributes. This model gave a basis on which the Entity Relational Model was produced by identifying the characteristics of the entities in the system. The tables below give the various entities in the system and their associated attributes. (a) Graduated Tax
28
(b) Property Taxes
29
(c) Market Collections
30
(d) Taxi/Bus Park Fees
31
(e) Street Parking Fees
32
(f) Trading Licence Fees
(g) Divisions in KCC
33
(h) Donations to KCC Divisions
(i) Tendering Companies
34
3. Modeling Relationships Between The Entities Participating In This System.
(a) The Market-Division Relationship
Figure 5.9: The Market-Division Relationship
Each division has one or more markets and each market belongs to a particular division. Thus the cardinality of this relationship is M: 1. (b) The Street-Division Relationship
Figure 5.10: The Street-Division Relationship
Each division has one or more streets and each street in located with in a particular division. Thus the cardinality of the relationship is M: 1. (c) The Trading License-Division Relationship
Figure 5.11: The Trading License-Division Relationship
Each business is conducted with in a particular division where it pays the trading license from and each division has one or more businesses being run with in it. Hence the cardinality of this relationship is M: 1. (d) The Taxi/Bus Park-Division Relationship 35
Figure 5.12: The Taixi/Bus Park-Division Relationship
Each Taxi/Bus Park belongs to a particular division and a division can have one or more Taxi/Bus Parks. Hence cardinality is M: 1. (e) The Property-Division Relationship
Figure 5.13: The Property-Division Relationship
Each property is located in a particular division and each division can have one or more properties located in it from where revenue collections are made. Thus the cardinality of this relationship is M: 1. (f) The Graduated Tax-Division Relationship
Figure 5.14: The Graduated Tax-Division Relationship
Each division can issue one or more graduated tax tickets to its residents and hence a revenue collection from the graduated tax ticket but each graduated tax ticket is issued with in a particular division. Hence cardinality of the relationship is M: 1.
36
(g) The Donation-Division Relationship
Figure 5.15: The Donation-Division Relationship
A division may receive one or more donations from funding agencies but each donation is given to a particular division. Hence cardinality of the relationship is M: 1. (h) The Market-Tendering Company Relationship
Figure 5.16: The Market-Tendering Company Relationship
A company may tender one or more markets but each market is tendered by one company. Hence cardinality of the relationship is M: 1 (i) The Taxi/Bus Park-Tendering Company Relationship
Figure 5.17: The Taxi/Bus Park-Tendering Company Relationship
A company may tender one or more parks but each park is tendered by one company. Hence cardinality of the relationship is M: 1.
37
(j) The Street Parking-Tendering Company Relationship
Figure 5.18: The Street Parking-Tendering Company Relationship
A company may tender one or more streets but each street is tendered by one company. Hence cardinality of the relationship is M: 1.
38
4. Integrating the Relationships Above To Model The Entity Relationship Diagram For The Online Revenue Management System For KCC.
Figure 5.19: The Entity Relationship Diagram for the System
39
Key to the Entity Relationship Diagram.
Figure 5.20: The Key to Entity Relationship Diagram for the System
5. Mapping the Entity Relational Model onto the Relational Schema After constructing the Entity Relational Model for the system, the researcher went further to map this model onto the relational schema so as to design the structures of the relations to be constructed in the database implementation. (a) Division Table
Figure 5.21: The Structure of Divisions Table
40
(b) Market Table
Figure 5.22: The Structure of Market Table
(c) Street Table
Figure 5.23: The Structure of Street Table
41
(d) Property Table
Figure 5.24: The Structure of Property Table
(e) Graduated Tax Table
Figure 5.25: The Structure of Graduated Tax Table
42
(f) Donation Table
Figure 5.26: The Structure of Donation Table
(g) Trading License Table
Figure 5.27: The Structure of Trading License Table
43
(h) Taxi/Bus Park Table
Figure 5.28: The Structure of Taxi/Bus Park Table
(i) Tendering Company Table
Figure 5.29: The Structure of Tendering Company Table
44
Chapter 6 SYSTEM IMPLEMENTATION
6.1 Introduction
This chapter presents the results of the design and implementation of the system. The system is made up various components each of which performs a speci?c role to achieve the objectives of this project as stated earlier in the project report.
6.2 Components of the Online Revenue Management System for KCC.
The system is made up of the following components; 1. The Graphical User Interface (GUI). This component mainly allows the user to interact with the system. It provides the following services; (a) Authentication of Users. The system allows the users to login into the system so as to gain access to the system services. (b) Registration and management of users. Through submission of user pro?les to the system, the users can be registered and managed. (c) Data entry forms. The system provides the user with data entry forms through which they enter revenue records into the backend of the system. 45
(d) Reports Display. The system provides the users with a display of generated reports about revenue collections per resource and division. 2. The database component. This component serves as the back-end of this system and provides the following services; (a) Insertion. Allows the user to commit data to the system’s database. (b) Deletion. System allows the user to delete unwanted data in its database. (c) Update. The system allows a user to make changes to existing records in its database. (d) Searching. The system allows the user to search information about a given record in its database. (e) Data retrieval. The system allows a user to retrieve data about a given record. 3. Reports Generator. This component helps to display results of the system to the user in form of reports. It provides the following services; (a) Display of reports about the revenue collected from all resource within a particular division on monthly, quarterly and annual basis. (b) Display of reports about performance of a given division per resource for a given month, quarter or year. The system records revenue collections made for KCC for different resources and divisions, generates periodical (monthly,quarterly and annual) revenue collection reports per resource and division and tracks the performance of different resources and divisions in terms of revenue collections to assist management in decision-making. 4. Security Component. This component handles the security issues of the system. (a) User Registration and management. This helps in registering users by giving them access rights to the services provided by the system. Also manages user accounts through updating users’ pro?les and deleting of users who are no longer authorized to access the system. (b) User Authentication. A user is requested to submit user login details created at the time of registration to gain access to the system functionalities. With out the right login information, a user can not access the system. 46
6.3 Physical Design Of The System
The physcal design was divided into
1. Data services layer 2. Function logic 3. Presentation layer
6.3.1 Data Services Layer
MySQL server, a relational database management system (RDBMS) was used to implement the data services layer. MySQL server was chosen because of its fair robustness, reliability and security for large scale online transactional (OLTP) requirements.
6.3.2 Function Logic
The function logic (middle tier component) was designed using PHP(Hypertext Pre-processor), a server-based scripting language and served by Apache web server.
6.4 Presentation Layer
This is also called the user interface. It comprises of the web applications and the local application authored in HTML(HyperText Markup Language) and PHP.
6.4.1 Web Application Functions
The web application was designed to meet web based operations with the following features;
1. A system interface presented to users through the application web interface. 47
2. Users interact with the application using the web browsers. 3. Main pplication functions can only be accessed by authorised users. 4. Authorised users have been assigned usernames and secret passwords,changed regularly (system policy)which they use to access the system. 5. At logon, each user is availed the speci?c functional settings and access rights applying to his/her category.
6.4.2 Local Applications
This is for inputting and retrieving data from the database server. It is installed on the client computers to be used by the revenue collection of?cers of KCC. The table below summerizes the technologies used and functions where they were applied.
Technology MySQL server DBMS PHP Apache web server
HTML
Functions To provide data storage and DBMS services To develop the application(function logic layer) To mediate the interaction between the data services layer with the user interface through the application layer To design the user interface and navigation
48
6.5 Sample of the Program Flow.
The system is designed into two layers in that the system administration part should not be accessed by any other user unless authorized to do so and also the layer for revenue management should only be accessed by those responsible for revenue management. The ?gure below shows the sample of the program ?ow. It shows how the user can navigate through the system.
Figure 6.1: The Sample of the program Flow
49
6.6 Sample of the Forms or Screens of the system.
The system provides a number of forms/screens (either for data capture or for reports displays). Some samples forms are provided in the ?gures below;
1. The Login Page
Figure 6.2: The Appearance of the Login Page
50
2. The Register Revenue Collections Page
Figure 6.3: The Appearance of the Register Revenue Collection Form
51
3. The View Revenue Collections Page
Figure 6.4: The Appearance of the View Revenue Collections Page
52
4. The Monthly General Divisions Performance Report
Figure 6.5: The Appearance of the Monthly General Divisions Performance Report
53
5. The Register Market Collections Page
Figure 6.6: The Appearance of the Register Market Revenue Collections
54
6. Report on Monthly Market Revenue Collections for all the Divisions
Figure 6.7: The Appearance of the Report on Monthly Market Revenue Collections per Division
55
7. Sample Annual Revenue Report Per Resource Per Division
Figure 6.8: The Annual Revenue Collections Report For The Year 2005 Per Division Resource
56
8. Part Of Sample Quarterly Revenue Report Per Resource Per Division
Figure 6.9: Part Of The Quarterly Revenue Report For July - September, 2005 Per Resource Per Division
57
9. Sample Bi-Annual Revenue Report Per Resource Per Division
Figure 6.10: The Bi-Annual Revenue Report For July - December, 2005 Per Resource Per Division
6.7 System Testing.
The researcher only applied alpha testing of this system which produced expected results but the beta testing will be done after installing the system in one of the divisions for KCC whose results will be a basis to install it in the other divisions.
58
Chapter 7 DISCUSSION OF ACHIEVEMENTS, LIMITATIONS AND CONCLUSION
7.1 Introduction.
This section discusses the achievements realised in relation to the objectives of the project, the limitations encountered during the project and is concluded by giving recommendations regarding this project.
7.2 Discussion of Achievements.
The researcher was able to achieve the project’s intended objectives as follows: The users of the system are able to;
1. Register any revenue collection made from any revenue resource for KCC 2. Retrieve stored information about revenue collections made from each revenue resource in form of reports per division per resource on monthly, quarterly, bi-annaully and annual basis. 3. Rank divisions with their respective revenue resources in revenue generation performance by looking at the totals generated per division on monthly, quarterly and annual basis. 4. The system is also customizable for other organisations’ revenue management needs.
59
7.3 Limitations
Much as the researcher was able to obtain the above achievements, a number of limitations were experienced during the project implementation as stated below;
1. During the system study, the KCC staff members (who were among the target groups) to be interviewed were reluctant in giving information regarding revenue management claiming that those are ’sensitive areas’ for KCC. This delayed the speedy progress of the project. 2. During the implementation of the system, there was too much competition for Personal Computers in the Computer Laboratory and a limited number of computers had the software needed which also affected fast progress of the project.
7.4 Recommendations
I recommend that in future if another researcher picks up interest in Revenue Management for KCC or any other institution, this project should be a starting point. Future work may consider all other sources of revenue at KCC although this study considered the major revenue resources only.
60
REFERENCES
1. Andrew B. E. ( 2003): Journal of Forecasting . 5., 559-584. 2. Belobaba. P. P (2002) Back to the future?. Direction for revenue management: Journal of revenue and pricing management. 1(1) 87-89. 3. Brad Coyne, Sonja K. Sadeen (2000): E-Business Asset Management and Capacity Planning. Intel Technology Journal. 94(1) .4. 4. Ianelal Y. and Una Mc. M. B. (2004): Case studies and Applications. Journal of Revenue management and pricing. 20(4) 2. 5. Kimes and Wife (2003): Has Revenue management become acceptable: Finding from an international study, Journal of service research 6(1): 125-135. 6. Marriot(2005). The Marriot Hotel: Linking Two Data Streams With Revenue Management System. http://www.computerworld.com/managementtopics/management/story/0,10801,99963,00.html. 7. RMS(1997).Revenue Management Systems. http://www.revenuemanagement.com/whatisrm.htm (Cited on 3rd June 2005) 8. Smith, E.L. 1994a. Class notes for Range Management RAM 456/556. Fast Copy, University of Arizona, Tucson. 9. Saleemi, N.A(1997) Electronic Data Processing Simpli?ed: 2nd ed. Nairobi Saleemi Publishers. 10. Seqev A. Bean C, Bean, C and Shanthikumar J. (2001): Optimal design of Internet based auctions Information Technology and Management 2(2) 121-163, 2001. 11. URA,1991. Uganda Revenue Authority Statute No. 6 of 1991 61
12. Van. R. G. and Gil. J. (1987): Optimal Dynamic Actions for Revenue Management: Journal of Economic Literature, 25 (2) 699-738 4. 13. Wallace, P (1983). Records Management Information Systems, 2nd ed. New York
62
APPENDIX A: Interview Guide
The interviewer gave an explanation to the respondents about the intention of the interview before proceeding to the questions below:
KCC Headquarters.
1. How many departments are involved in revenue assessment, collection, and storage. How many divisions does KCC have? 2. How many ?rms have been outsourced to collect revenue on behalf of the City Council. 3. Do these people submit reports, if yes may I see some of these reports. 4. How often do divisions submit revenue reports to the headquarters? 5. What are the major processes involved in generating general revenue reports by the City Council and who are players in the process. 6. What are the major sources of revenue to the City Council? 7. Does KCC have a computerized revenue management system, if yes; (a) What processes are involved? (b) Which people work on revenue report generation?
At the Division. Questions asked to the Assistant Town Clerk.
1. What are the major sources of revenue in your division? 2. What ways/methods are used in collection of these revenues? 3. What method is used in documentation and storage of data on revenue? 4. What problems do you face when collecting these revenues. 5. Which people are involved in; (a) Collection of revenue 63
(b) Storage of revenue collection (c) Preparation of revenue report (d) Co-ordination with the KCC Headquarters
APPENDIX B: Sample Program Code
As stated earlier in the methodology used to develop this system, the researcher used PHP, HTML and MySQL computer languages to design the system. The researcher opted to provide the Structured English that re?ects the actual program code in the languages stated above. Below are samples of Structured English used.
1. Structured English to Produce the Register Market Collections Form
Print Heading for the Form
Provide option for user to select a Division Name
Provide Text box to enter Market Name
Provide Text box to enter Amount Collected
Provide Text box to enter Collection Date (Date is pasted in this textbox by default the computer system’s date).
Provide Text box to enter Name of the Company Tendering the market.
Extract the data entered by user in System Variable to use in PHP code.
64
Validate this user input and give communication to the user for invalid data.
If data entered is valid, then proceed or else exit from here.
Connect to the local host with appropriate login details.
Select database called ’zbabwona’ using a MySQL statement.
Check user input against the existing data in the database for duplication.
Commit the transaction to the database if there are no duplicate problems.
Notify the user of success in registering a market collection.
Provide submission button and other navigation buttons.
65
2. Structured English to Produce the General Division Performance Report
Print Report Heading/Title.
Connect to the local host with appropriate login details.
Select database called ’zbabwona’ using a MySQL statement.
Write SQL statements to select totals per revenue resource per division.
Write PHP code to extract SQL query results to a data structure like an array.
Draw the table and populate it with the contents of the data structure created.
Provide Navigation Buttons.
66
doc_234371410.pdf