Case Study 28 Tender Evaluation Using a Decision Support System
Tender Evaluation Using a Decision Support System
Problem Description
Local governments usually organize tenders where local companies bid for large scale projects supported and financed by the government. Tenders involve large amounts of money. Since the government supports the projects, on one side the companies find it very prestigious to be part of it, and on the other side, the public is very sensitive about how well the money is used. A multi-disciplinary committee is constituted in order to evaluate the participants. The evaluation process consists of two phases: first is the pre-qualification phase where tenders are scrutinized based on their legal and technical system, and second is the final phase where tenders are evaluated based on a costs/performance analysis.
In the first phase, participants submit general information about the company, their legal and technical system, number of employees, etc. The companies are evaluated based on the following criteria: ISO 9000 certification, 6-sigma process control, etc. In the second phase, participants submit information on prices and product quality. The companies are then evaluated based on the following criteria: price, product quality, and technical competence.
The aim of this project is to develop a multi-level, multi-objective decision support system that will assist members of the committee in evaluating the participants and making a final recommendation about the company that will carry on the project.
Database Design
We present below the main entity types of this database. For each entity type, we provide some of the corresponding attributes. Use this information in order to: (a) Build an Enhanced E-R diagram; (b) Transform the Enhanced E-R diagram to a relational database. Identify the primary key(s) and the foreign key(s) for each relation. Draw the relational integrality constraints; (c) For each of the relations created, indicate its normal form. If the relation is not in the 3NF, decompose it into 3NF relations.
1. Participant: The main attributes are identification number, name, address, name and telephone number of the contact person, number of employees, compliance with ISO 9000, compliance with 6-sigma process control, etc.
2. Project: The main attributes are project identification number, name, description, requirements, etc.
3. Project Evaluation Committee: The main attributes are social security number, name, address, telephone number, degree, area of specialty, etc.
4. Evaluation Criteria: The main attributes are identification number, name, and weight.
Note that the evaluation criteria are used to identify the competency of a participant to undertake a project. The data collected from the two phases of the evaluation process is recorded in this database. When a participant wins the tender, the following information is recorded: estimated starting date, estimated finish date, actual starting date, actual finish date, estimated costs, actual cost, etc.
Access Application Development
The following are some of the queries, forms, and reports one can create in order to increase the functionality of the database:
Queries:
1. List the name, address, and telephone number of the companies that comply with ISO 9000 and 6-sigma process control.
2. A number of criteria are used to identify the companies that have adequate technical capabilities to carry on the project. Each criterion has its own weight. The figure below presents the decision tree considered during this phase. The numbers in brackets are the weights given to each criterion. The members of the project evaluation committee evaluate each company on a scale of 1-100 (consider only leaf criteria). Create a query that calculates the total score for each company. Sort this information by total score.
à References (17)1. Delivery record (67)
2. Length of relationship to the manufacturer (33)
à Services (42)
1. Network (25)
1. Commerce (27)
2. Maintenance (73)
2. Guarantee (24)
3. Information, training (14)
4. Maintenance (26)
1. Capital (46)
2. Provinces (54)
5. Consumer support (11)
à Manufacturing (24)
1. Delivery Capacity (42)
2. Quality assurance (58)
à Employee (17)
3. In the second phase, companies are evaluated based on a cost/performance analysis. Suppose that the members of the project evaluation committee give the following weights to the evaluation criteria of this phase: weight of price is 50, weight of product quality is 30, and weight of technical competence is 20. Then, the companies are evaluated on a scale of 1-100 points. As price is a quantitative measure but quality and technical competence are subjective measures, a price equivalent is calculated using the following procedure:
a. Calculate the fractional weighted sum of scores for product quality and technical competence. For example, for each tender calculate
p = score * percentage weight/100.
b. Calculate the harmonic mean and quadratic mean of the scores accumulated. Group together tenders with a score less than the harmonic mean; and group together tenders with a score more than the quadratic mean.
c. Calculate the geometric mean of the score (p1) and price offer (C1) for the tenders in the first group and similarly p2 and C2 for the tenders in the second group.
d. Calculate benchmark price:
N = (C2-C1)/(p2-p1).
e. Finally calculate the adjusted price for each tender as follows:
C* = C + (1-p)N,
if weight of criteria price is 50, or
C* = C + 2/3(1-p)N,
if the weight is 60.
Create queries that would enable the user to complete the second evaluation phase.
4. Create a crosstab query that presents for each company participating in the tender the points earned from each member of the project evaluation committee.
Forms:
1. Create a user sign-in form together with a registration form for new users.
2. Create the following data entry forms that are used for database administrative functions: companies, projects, project evaluation committee, etc. These forms allow the user to add, update, and delete information about companies, projects, the project evaluation committee, etc. Note that when creating data entry forms to be used by companies to submit tenders, you must assure that the proper validation rules are implied in order to minimize data entry errors. Create two such forms, one for the pre-qualification phase and another for the final (second) phase.
3. Create a form that allows the user to choose the name of a company from a combo box. Create a subform that presents the following information about the selected company: the points received from each member of the project evaluation committee on all the evaluation criteria during the first phase. Create another subform that presents the points received from each member of the project evaluation committee on all the evaluation criteria during the second evaluation phase. Insert three textboxes to display: (a) the total score from the first evaluation phase, (b) the total score from the second evaluation phase, and (c) the name of the company that wins the tender.
4. Create a form that allows the user to choose from a list box the name of a project. Create a subform that provides detailed information about the participants in the tender. Insert a textbox that displays the name of the winner.
Design a logo for this database. Insert this logo in the forms created above. Have the background color of the forms light green and the border color for the titles yellow. Include the following in the forms created: record navigation command buttons, record operations command buttons, and form operations command buttons as needed.
Reports
1. Create a report that presents a table that has the name of the criteria that will be used for evaluation as column headings and the names of the companies participating in the tender as row headings. These reports will be submitted to the members of the project evaluation committee to enter the scores during the evaluation process.
2. Create reports (letters) that will be sent to companies whose offer was rejected. The report should also present the reason(s) for rejection.
3. Prepare a summary report that contains the following information: number of tenders considered in the first and second phases of the evaluation process, a list of criteria that was considered, recommendations, etc.
4. Create a summary report that consists of the following information about the participants in the tender: (a) name, (b) total, average, and standard deviation of the scores received in each evaluation criterion, and (c) total, average, and standard deviation of the overall score (overall criteria).
5. Use the label wizard to create labels with the address of each participating company.
Visual Basic.NET Application Development
This database application can be used by companies, project evaluation committee members, etc. In the following figure we present a tentative layout of the system.
In the welcome screen, the user can choose one of the four options presented. We give details about the forms or set of forms to be included in each option; however, you are encouraged to add other forms you find important. We suggest that the queries, forms, and reports already created in the Access Application Development section be included in here.
Projects: Companies mainly visit this part of the database in order to (a) identify potential projects, (b) submit tenders, etc.
Companies: The project evaluation committee members mainly visit this part of the database in order to learn about the companies that are participating in a tender.
Statistics, Graphs & Data Analysis: Project evaluation committee members mainly visit this part of the database. Statistics and data analysis tools offered in here facilitate the decision-making process.
Update: The update form requires an administrator login name and password. This form allows the user to add/delete/update the information kept in this database about companies, evaluation criteria, etc.
Web Extension
A user may access this database from personal computers at home or in the office. The user could be a company, a member of the project evaluation committee, the database administrator, etc. The companies can access the database to check available projects and apply for tenders. Members of the project evaluation committee check the database in order to learn about the participants or to use the statistical analysis tools offered to process data, draw conclusions, and make recommendations. Only the database administrator can have access to the update forms.
Develop an ASP.NET web application that will enable the users to access the database and perform the activities described above. Your application will have forms similar to the ones described in the VB.NET Application section.
Reference
Rapcsák, T., Sági, Z., Tóth, T., Kétszeri, “Evaluation of tenders in information technology.”Decision Support Systems 30: 1-10, 2000.