Spreadsheet modelling for solving combinatorial problems: The vendor selection problem

Since their introduction in the early eighties electronic spreadsheet programs such as Excel, Lotus 1-2-3 and Quattro Pro have grown to be the most common tool managers use to model and analyze quantitative problems. The latest versions of spreadsheets contain powerful analytical tools accessible through a user-friendly interface that provide end users with such computing power, we could only dream several years ago. In today's business world an unprecedented number of managers are familiar with quantitative modelling tools available at their fingertips through the spreadsheet software in their desktop computers. Furthermore, the great majority of them understand numbers, systems and relationships [Ashley, (1995)]. Therefore although they may not be very well versed in the techniques of operations research, they possess the required fundamental skills for developing end-user decision support tools that make use of OR/MS principles and methodologies. Historically, optimization problems were typically solved using special purpose optimization software packages such as LINDO, MATLAB, SAS etc. [Novak, et al., (2003)] which is widely available in the academic world, but is not commonly found in businesses since it is not considered part of the everyday business tools that managers use. On the other hand, the spreadsheet analysis tools including the optimizer known as "solver" which is currently the most readily available general-purpose optimization modelling system are available to approximately 35 million users of office productivity software worldwide. Their widespread availability has spawned many optimization applications in both the private and public sectors [Fylstra et al (1998)]. Many users without a mathematical background became capable of developing useful and substantive decision support tools utilizing OR methodologies [Roy et al (1989)]. 97 Selection (MIVS) problem which refers to procurement decisions involving the acquisition of a specific set of discrete items, all of which can be supplied by many different vendors, with no interdependencies (i.e. functional, price, demand etc.) or other variations (e.g. quality) between them, where the procurement decision is solely costbased. A typical application of this problem is an open public call of tenders for the procurement of a list of items where interested vendors submit their bids. The problem is not trivial because the cost to the procurer includes a certain fixed handling cost for each selected vendor besides the direct acquisition cost. This cost comprises components such as: legal review, contract administration, communications, invoicing, receipt of materials, bill of delivery processing, etc. The procurer bears this fixed cost regardless of the quantity of items the specific supplier delivers. It is obvious that if the vendor selection process results in choosing many vendors for the supply of the given set of items, the total supplier handling cost increases, leading consequently to increases in the total cost. Hence, procurement managers have a tendency to identify and select a limited number of suppliers, in order to keep the procurement administrative cost low. However as markets become more open and firms move from single sourcing to competitive tendering [Reid (1989)], while at the same time organizations such as the World Trade Organization (WTO) and the European Union (EU) issue directives to ensure that public procurement policies are more open, transparent, doubtless but at the same time efficient [Hoekman (1998); Ferguson et al. (1995)], the issue of selecting the optimum number of vendors in a multi-item procurement becomes very important in order to maintain cost-efficiency. The following is a real life case of the MIVS problem.

The expansion of regional school libraries was set as a top priority goal of the Greek ministry of education in the late 90s. In the period 1998-2000, a number of such projects were initiated at different regions in the country, the management of which was assigned to local universities because of their expertise in library operations. Each project involved a number of activities such as: preparing the library space, organizing the library functions, hiring and training staff, installing IT services etc. The Technological Education Institute of Larissa managed one of these projects in the region of Central Greece, which involved, among other tasks, the acquisition of approximately 4.000 titles of books corresponding to a budget over 200.000 euros. The project manager was obliged to follow all financial regulations and policies concerning public procurement imposed by the EU and the national government both of which funded the project. Ten bids by major book suppliers were submitted, following an open call for tenders. Due to the nature of the products to be purchased, no quality or other differences existed among the different vendors, therefore, the only selection criterion that could be used is the p

…(Full text truncated)…

This content is AI-processed based on ArXiv data.