Spreadsheet modelling for solving combinatorial problems: The vendor selection problem

Spreadsheet modelling for solving combinatorial problems: vendor selection Pandelis G. Ipsilandis Proce edings of EuSpRIG 2 008 Confere nce "In P ursuit of Sprea dsheet Excellence" ISBN : 9 78-90561 7-69-2 Copyright © 20 08 E uropean Spread sheet Risks Interest Group ( www.eusprig.org ) a nd Author 95 Spreadsheet modelli ng for solving combinatorial p roblems: The ve ndor selectio n problem Pandelis G. Ipsilandis Project Managem ent Department, Technological Edu cation I nstitute of Larissa Larissa Greece, 41110, E-m ail: ipsil@teilar.gr ABSTRACT Spreadsheets have g rown up and beca me ver y po werful and eas y to use too ls in app lying analytical technique s for solving business problems. Opera tions managers, pr oduction managers, planners and schedulers can work w ith them in developi ng solid and prac tical Do-It -Yoursel f Decision Suppor t S ystems. S mall a nd Med ium size organiz ations, ca n ap ply OR methodo logies without the presence of specialized software and tra ined personnel, which in many ca ses cannot afford anyway. T his paper exa mines an efficient appro ach in solving combinato rial progra mming proble ms with the use of spread sheets. A practical app lication, wh ich demon strates the app roach, concerns t he de velopment of a spre adsheet-based DSS for the Mul ti Ite m Pro curement Problem with Fixed Vend or Cost. T he DSS has be en build using e xclusively standard spreadsheet feature and can so lve real prob lems of substantial size. The benefits and limitation s of t he approach are also discussed. 1 INTRODUCTION Since their introduction i n t he early eighties electronic spreadsheet programs such as Excel, Lotus 1-2-3 and Quattro Pro have grown to be the most comm on tool managers use to m odel and analyze quantitat ive problem s. The latest versions of spreadshe ets contain powerful analytical tool s accessible through a user -friendly interface t hat p rovide end users with such com puting power, we could only dream several years ago. In today' s business world an unp recedented num ber of m anagers are fam iliar with quantitative modelling tools available at their fingertips t hrough the spreadsheet software in their desktop computers. Furthermore, the great majority of them understand numbers, systems and relationships [Ashley, (1995)]. T herefore although they m ay not be very well v ersed in the techn iques of oper ations r esearch, t hey possess the required fundam ental sk ills for developing end-user decision support tools that mak e use of OR /MS principles and methodologies. Historically, optimization problems were typicall y solved using special purpose optimization software packages such as LINDO, MAT LAB, SAS etc. [Novak, et al ., (2003)] which is widely available i n the academic world, b ut is not commonly found in businesses since it is not considered part of the every day business tools that managers use. On the other hand, the spreadsheet analysis tools i ncluding the optimiz er known as "solver" whi ch is currently the most r eadily available general- purpose optimiz ation modelling system are available to approxim ately 35 million users of office product ivity software worldwide. Their widespread availability has spawned many optimization applications i n both t he private and public sectors [Fylstra et al (1998)]. M any users without a mathematical backg round became capable of developing use ful and substantive decision support tools ut ilizing OR methodolog ies [Roy et al (1989)]. Spreadsheet modelling for solving combinatorial problems: vendor selection Pandelis G. Ipsilandis Proce edings of EuSpRIG 2 008 Confere nce "In P ursuit of Sprea dsheet Excellence" ISBN : 9 78-90561 7-69-2 Copyright © 20 08 E uropean Spread sheet Risks Interest Group ( www.eusprig.org ) a nd Author 96 The relevant research literature addressi ng spreadshee t optimization and end- user spreadsheet modelling i s quite extensive i n the last years. Both academics and busi ness practitioners r eport o n suc cessful applications of end user decision modelling wi th t he help of spreadsheets. Several authors [ Barlow ( 1999), Thiriez (2001), Hesse (1997)] propose modelling schemes for almost all the familiar OR problems and methodologies such as linear programming , transportation problems, decision theory, dy namic programming, simulation etc. and t heir applications, which utilize moder n spreadsheet features such as mathematical and reference functions, r andom number generators, and the "solver" facility. Although, t hey present s preadsheet templates for OR pr oblem as an alternative to OR/MS software, yet the propos ed models are intended m ostly for educational use in a classroom environment, si nce they refer to very well structured problem s, of limited size in many cases; therefore no t too suitable for real life applications. More i nteresting spreadsheet models are the ones that refer to less structured problems such as solving discrete state and discrete stage dynam ic programs [Parlar (1989)] and stochastic programm ing with multiple decision criteria applications [Novak (2003 )] as well as spreadsheet m odels that have been used to sol ve real life pr oblems in a wide diversity of application ar eas such as: using m ultivariate constrained op timization in production scheduling [Osman (1992)] , university library staff scheduling using int eger programming techniques [Ashley (1995)], optimization of bank customer redistribution through a multi stage decision m aking model [Chu (1995)],.queuing decomposition models in produ ction work flow [Enns (1999) ], marketing decision making m odels [Albers (2000 )], integrated business p lanning including sales forecasting, production planning and cash budgeting [Chien (2000)], design of the workload of paced or linked assembly l ines [J ohnson ( 2002)], facility location pro blems [ Pearson (2003) ], t o m ention some of the most im portant during the last y ears. This paper presents an end-user spreadsheet-based model for solving a combinatorial integer-prog ramming problem referring to the optimum se lection of vendors in a m ulti- item procurement prob lem. Since the e xcel solv er works very poorly for problems wit h many bi nary variables, the spreadsheet model is base d on other spreadshee t features such as extensive use o f mathematical and reference functions, and data t ables for "smart" enumeration of alternative solutions. In t his sense it does not follow a formal OR model structure; instead, t he model uses typical business-like layout of the problems par am eters, in a way that facilitates the manag erial presentation and the analysis of the r esults. The remainder of t his paper is organized as follows: Section 2 describes the real case o f a multi item pr ocurement problem exploring the problem' s parameters, the difficulty i n estimating cer tain cost parameters and some of t he m anagerial concerns in volv ed in t he decision making process. Section 3 provides a formal def inition of the multi- item procurement problem, and its formulation as an integer programming optimization problem. I t a lso p rovides some i nsight t o the nature of the solutions of the problem and references to heuristic algorithm s and other approximate methods, which can be used to produce n ear-optim um sol utions. Section 4 describes the spreadsheet model used t o solve the problem. Finally, Sectio n 6 contains a summ ary of the conclu sions derived. 2 THE MULTI ITEM VE NDOR SELECT ION (MIVS) PROBLEM Vendor selection and procurement policies are very important since they can affect dramatically the c ost, the quality and the promised delivery time of deliverables of a specific project or operation. T he quantity and variety of vendor selection models published in the operations research, production mana gement and other related literature [Degraeve et al. (2000)] reflect no t on ly the complex ity but also the financial im portance of these decisions. A special t ype of procurement problem i s the Multi Item Ve ndor Spreadsheet modelling for solving combinatorial problems: vendor selection Pandelis G. Ipsilandis Proce edings of EuSpRIG 2 008 Confere nce "In P ursuit of Sprea dsheet Excellence" ISBN : 9 78-90561 7-69-2 Copyright © 20 08 E uropean Spread sheet Risks Interest Group ( www.eusprig.org ) a nd Author 97 Selection (MIVS) problem which re fers to procurement decisions i nvolving the acquisition of a specific set of discrete items, all of which ca n be supplied by many different vendor s, with no interdependen cies ( i.e. func tional, p rice, de mand etc.) or othe r variations (e.g. quality) be tween them, wher e the procurement decision is solely cost- based. A t ypical application of this pr oblem is an open public call of tenders for the procurement of a l ist of items where i nterested vendors s ubmit their bids. T he problem is not trivial because the c ost t o the procurer i ncludes a certain fixed handling cost for each selected vendor besides the direct a cquisition cost. T his cost c omprises components s uch as: l egal review, contract adm inistration, communications, invoicing, receipt of materials, bill of delivery processing, etc. The procurer bea rs this fixed cost regardle ss of the quantity of items t he specific supplier delivers. It i s obv ious that if the vendor selection process results in choosing m any vendors for the supply of the given set of items, the total supplier handling cost increases, leading consequently to increases in the tot al cost. Hence, procurement managers have a tendency to identify and select a limited number of suppliers, i n order to keep t he procurem ent adm inistrative cost low. However as markets become more open and firm s m ove fr om single sourcing to competitiv e 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 procurem ent policies are more open, transparent, doubtless b ut at t he same time effi cient [Hoekman (1998); Ferguson et al. (19 95)], the issue of select ing the optim um num ber of vendors in a multi-item procurement becomes very i mportant in order to maintain cost-efficie ncy. The following is a real life case of the MIVS problem . 2.1 A Case of The MIVS Problem: Procure ment of Library Books The expans ion of regional school libraries was set a s 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 ini tiated at different reg ions in the country , the m anagement of which was a ssigned to local universities because of t heir expertise in library operat ions. Each project involved a number of activities such as: preparing the library space, organizing the l ibrary functions, hiring and t raining staff, installing I T services e tc. The Technological Education Institute of Larissa managed one of thes e projects in t he region of Central Greece, which involved, am ong other tasks, t he acquisition of approximately 4.000 titles of books corresponding to a budget over 200. 000 euros. The project manager was obliged to f ollow all financial regulations and poli cies concerning public procurem ent 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 t o the nature of the products to be purchased, no q uality or other differences existed among the different vendors, therefore, the only selection criterion that could be used is the price offered by each vendor. Initially, t he project manager considered two d ifferent supplier selection alternatives: Alternative I Select a single vendor base d on the least total pur chasing cost . Alternative II Buy each book from the ven dor who offered the lowe st price for it. The project m anager followed alternat ive II beca use it seem ed reasonable, t ransparent, non-questionable, and easy to arrive at a justified decision. I n addition, prevents a result where one firm gets the entire order based o n a m arginal cost difference, while other vendors c ould have offered l ower prices for books in specific domains, which would lead to lower procurement cost. Figure 1 summarizes the results of the vendor selection policy followed. Nine vendors we re selected with a m inimum purchasing cost of 220.736 euros. Number of vendors submi tted bids: 10 Number of vendors selected: 9 Vendor Number of Books supplied Purchasing cost (€) Spreadsheet modelling for solving combinatorial problems: vendor selection Pandelis G. Ipsilandis Proce edings of EuSpRIG 2 008 Confere nce "In P ursuit of Sprea dsheet Excellence" ISBN : 9 78-90561 7-69-2 Copyright © 20 08 E uropean Spread sheet Risks Interest Group ( www.eusprig.org ) a nd Author 98 # 1 260 10.118 # 2 140 4.008 # 3 50 2.647 # 4 160 8.075 # 5 940 48.072 # 7 50 2.469 # 8 20 1.094 # 9 1.610 66.847 # 10 840 77.406 Total 4.070 titles 220.736 € Figure 1 – Results of procu rement policy "Buy each book at the lowe st price" Although, alternative II results i n the minimum total pr ocurement cost, the total cos t is not minimiz ed. As it was discovered during the course of the project, a relatively high vendor handling cost existed, which wa s initially hidden. Although it i s difficult to ar rive at an objective estimation of this cost, a rough calculation, put it in the range of 3.600 € per vendor (figure 2), This vendor handling cost is paid for every vendor selected, even in the case where on ly a small number of items a re assigned to the specific vendor, m aking this problem a typical MI VS problem. Personnel Cost based on p erson-days Proj. Mnger Libra- rian Acctg Admin. Legal Conslt. Proj. Admin. Proposal examination – Data verification and correction 2 0,2 0,5 Contract preparation – Financial/L egal Review 0,3 1 0,5 1 Receiving – 2,5 1 Payment preparation 0,3 2 2 Total person days per vend or 0,6 4,5 3 0,7 4,5 Total personnel cost: 13,3 days x 200 € per day = 2.660 € Other cost (mailing etc) = 1.000 € Fixed Vendor Handling Cost (approximately) = 3.600 € Figure 2 – Analysis of the fixed vendor hand ling cost Taking into consideration the fi xed vendor handling cost , the t otal cost to the project is as follows: Total book acquisition cost = Procurement cost + Fixed vendor handling cost = 220.736 + 9 x 3.600 = 253.136 € Obviously the policy that was f ollowed is not optimal. Selecting fewer suppliers could probably result in a lower cost. As it will b e explained in the ne xt section, where a formal definition of the M IVS problem is given, obtaining an optimal solution to the problem is not a trivial task. A spreadsheet model can be used to as sist managers in i dentify ing the optimal solution to the problem . 3 AN INTEGE R PROGRAMMING FORMUL ATION OF THE MIVS PROBLEM 3.1 Formal Problem De finition 3.1.1 Assumptions The multi-item vendor selection (MIVS) pr oblem under consideration is defined as the problem of minimizing the total c ost of procuring m different items from n given vendors. The following operational a ssumptions are m ade: Spreadsheet modelling for solving combinatorial problems: vendor selection Pandelis G. Ipsilandis Proce edings of EuSpRIG 2 008 Confere nce "In P ursuit of Sprea dsheet Excellence" ISBN : 9 78-90561 7-69-2 Copyright © 20 08 E uropean Spread sheet Risks Interest Group ( www.eusprig.org ) a nd Author 99 i Each item is bought from a si ngle vendor (no splitting of t he total demanded quantity of a given item among different vendo rs is allowed). Therefore w ithout loss of generality we can assume that only one unit (i.e. the total demanded quantity) of each item is to be obtained and v endors bid for it if they have the capaci ty to supply it. ii No significant differences exist betwe en vendors in terms of qualitativ e factors such as: product quality, v endor reliability, delivery t ime etc. iii There are no price, demand or functional dependenc ies of any kind between any of the m items. Without loss of generality we can assume that all n vendor s offer all m items at a cost of p ij ( i=1,…,m, j=1,…,n ), since the case of v endor j not offering item i can be handled by setting t he corresponding cos t p ij very high. iv The selection of an y vendor j, im poses a f ixed c ost c j to the procurer. T his cost is incurred regardless of the n umber of items that are bou ght from the spec ific vendor. These as sum ptions are compatible with many situations in publ ic pr ocurem ent dea lings. In these cases a minimum set of requirements is set regarding product feat ures an d quality, vendor reliability, expected d elivery times etc., a public call of tenders is announced and the buying decision are mainly cos t based. Mathematical Model Based on the formal definition given above, the MIVS problem can be formulated as an integer linear programm ing model as follows: Let i= 1,,2,…,m de note the items and j=1,2,…,n the vendors . Define x ij and y j to be indicator variables where ij 1, if item i is bought from ve ndor j x 0, otherwise  =   and m ij j i=1 = Minimum { x , 1} , i = 1 ,...,m, j = 1,.. .,n y ∑ . In other words, y j = 1 , if at least one item i s bought from the j th vendor, otherwise y j = 0 . Let p ij be the price of the i th item charged by the j th vendor and c j the fixed cost to the procurer if at least one item is bo ught from the j th vendor. Then the problem of optimum procurement can be wr itten in linear form as follows: Minimize j n j j ij m i n j ij y c x p C ∗ + ∗ = ∑ ∑ ∑ = = = 1 1 1 (1) subject to n ij j=1 x = 1, i = 1,.., m ∀ ∑ (2) ij j x y i = 1, ..., m, j = 1, ..., n ≤ ∀ (3) x ij , y j = 0,1 ∀ i=1,..., m, j=1,..,n (4) Note that the set of const raints (3) and (4) replaces the definition of the ind icator variable y j given earlier. I f for a given vendor j, at least one ite m is selected then one of the x ij 's will be equal to one, thus fo rcing y j to get a value of at least 1. In addition b ecause of the minimization of the object ive function the y j 's can never assum e values greater than 0 if no item is supplied by v e ndor j . 3.2 Exploring the Solutio n Space of the MIVS P roblem Spreadsheet modelling for solving combinatorial problems: vendor selection Pandelis G. Ipsilandis Proce edings of EuSpRIG 2 008 Confere nce "In P ursuit of Sprea dsheet Excellence" ISBN : 9 78-90561 7-69-2 Copyright © 20 08 E uropean Spread sheet Risks Interest Group ( www.eusprig.org ) a nd Author 100 The mathematical model for the MIVS problem is similar to the facility location problem, a wel l-know n NP -complete combinatorial problem [Akinc (1993)], and therefore it is rather unlikely to find an efficient algorithm for solving it [Cornuejols, et. al. (199 7) ]. The solution space of the MIVS problem is combinatorial in nature; t herefore the number of feasible variable com b inations to be searched by an (expli cit or im plicit) enumeration algorithm (suc h as branch and bound) in order to loc ate the optimum solution is enormous. Based on the mathematical model defined in 3.1 and especially considering constraints (2), it is easily concl uded that the maximum number of feasible v ariable combinations (and so candidate solutions) is n m . Even for moderate values of n (number of vendors) and m (num ber of items to be purchased) t he solution space m ay grow i nto very large num bers. Heuristic algorithms for this sort of problem s have been developed based on the observation that for any k given vendors i t is easy to identify the minimum cost solution by assigning each item to the vendor (out of the set of the k given vendors) who offers the lowest price for this spec i fic item. T he spreadsheet m odel, wh i ch is described in the next section, is based on this observ ation. Consider a vector Y r = (y r l , y r 2 ,…,y r j ,...,y r n ), the elements of which take bi nary values. We can call Y r a vendor se lection vector, since each vector Y r corresponds to a spe cific selection of k vendors (thos e wi th corresponding y r j = 1). Then for a given Y r , an associated minim um cost solutions is def ined as follows: S r = { x ij : x ij =1 if c ij = min i (x ij ) and y r j =1; x ij =0 otherwise ∀ I=1,…,m an d ∀ j = 1,…,n } The number of the solutions S r f or any MIVS problem is equal to 2 n -1 and is independent of the number of items m . This can be shown easily by considering all possible combinations of n variables each t aking only t he val ue of 0 or 1 and excluding the one combination where all variables take the value of 0 (i.e. at l east one vendor must be selected). Each solution S r is a candidate for optimiz ing t he total cost. The optimum solution for the problem ca n be found by com paring the cost of all the S r solutions. Figure 3 illustrates the above fo r a problem with 5 ve ndors and 9 prod ucts. T he table on the left includes the prices of fered by each vendor plu s the vendor f ixed cost. The table on the r ight presents a s olution where vendors 2, 3 and 5 are selected. The total cost is the acquisition cost (minimum price for each i tem among the specific vendors) plus t he fi xed cost of all selected vendors. Y j = 0 1 1 0 1 S 1 S 2 S 3 S 4 S 5 S 1 S 2 S 3 S 4 S 5 M i n P i P 1 1 9 1 3 1 1 1 2 1 2 P 1 1 3 1 1 1 2 1 1 P 2 1 9 1 7 1 6 1 3 1 0 P 2 1 7 1 6 1 0 1 0 P 3 1 5 1 4 21 1 8 1 1 P 3 1 4 2 1 1 1 1 1 P 4 1 6 2 3 2 4 2 3 1 4 P 4 2 3 2 4 1 4 1 4 P 5 2 3 1 1 1 6 1 1 2 4 P 5 1 1 1 6 2 4 1 1 P 6 1 8 1 6 2 0 1 8 1 1 P 6 1 6 2 0 1 1 1 1 P 7 2 2 1 8 2 2 2 0 1 1 P 7 1 8 2 2 1 1 1 1 P 8 2 3 2 4 1 6 1 4 2 2 P 8 2 4 1 6 2 2 1 6 P 9 1 2 1 0 1 0 1 4 1 6 P 9 1 0 1 0 1 6 1 0 1 0 5 A c ui s i t io n C o st F i x e d C o st C j 1 0 1 3 15 8 1 1 C j 1 3 1 5 1 1 3 9 V e nd o r F i xe d C o st 1 4 4 3 V e nd o r s S e le c t e d Products P r i c e M a t r i x ( P i j ) S u pp li e r s T o ta l c o s t o f s o lu t i o n N u m b er o f v e n do rs i n s o lu tio n Figure 3 – An exam p le of a MIVS problem 4 A SPREADSHEET M ODEL FOR THE MIVS PROBLEM Spreadsheet modelling for solving combinatorial problems: vendor selection Pandelis G. Ipsilandis Proce edings of EuSpRIG 2 008 Confere nce "In P ursuit of Sprea dsheet Excellence" ISBN : 9 78-90561 7-69-2 Copyright © 20 08 E uropean Spread sheet Risks Interest Group ( www.eusprig.org ) a nd Author 101 One of the advantages of spreadsheet modelling is that the problem data and parameters can be organized in a business-like format, avoiding the use of mathematical notation and special input formatting. The spreadsheet model used for solving the MIVS problem is explained analytically t hrough a smaller size demonstration example shown in f igure 3 with five vendors and nine i tems. For any given v endor selection shown by setting corresponding cells equal to 1 (i.e. vendors 2, 3, and 5 are selected in figure 3) the resulting solution is trivial (shaded cells correspond to minim um price items). 4.1 Organization of the P roblem's Data The parameters for t he MIVS problem consist of an m x n matrix of the prices p ij (i.e. price of item i offere d by vendor j ), and the 1 x n vector of the fixed costs for each o f the n vectors. A spreadsheet called "problem data " holds the values of the problem's parameters as shown in figure 4. Figure 4 – The MI VS problem's parameters 4.2 Determining a Proble m Solution As explained i n 3.2, for any given vendor selection vector Y r = (y r l , y r 2 ,…,y r j ,...,y r n ), corresponding to a c hoice of k specific vendors, the solution to the problem is trivial. It is found by obt aining e ach item fr om the v endors offer ing the lowest price for it. For a problem i nvolving n vendors the num be r of a ll possible Y r vectors i s 2 n -1. Since each o f these vectors corresponds to a binary sequence of n binary digits it is easy to map t he sequence {1, 2, 3, …, 2 n -1} to vectors matching the binary representation of each number in the list as follows: Vector ( element 1, 2, 3, n-2 , n-1, n) Y 1  {0, 0, 0, , 0, 0, 1} Y 2  {0, 0, 0, , 0, 1, 0} Y 3  {0, 0, 0, , 0, 1, 1} etc. Therefore one can produce a list of 2 n -1 solutions, each correspo nding to an integ er r between 1 and 2 n -1. Fig ure 5 shows the spreadsheet m odel, which accepts as input a number r and produces the r th solution corresponding t o a given com bination of vendors determined by the binary representation of r. Spreadsheet modelling for solving combinatorial problems: vendor selection Pandelis G. Ipsilandis Proce edings of EuSpRIG 2 008 Confere nce "In P ursuit of Sprea dsheet Excellence" ISBN : 9 78-90561 7-69-2 Copyright © 20 08 E uropean Spread sheet Risks Interest Group ( www.eusprig.org ) a nd Author 102 The formul a in cell E7 is copied to cells B7 to D7 The formula in c ell B 10 is copied to t he range o f cells B10 to F18 and B20 to F20 The formul a in cell G10 is copied to the c ells of the same col umn G11 to G18 The arrows show the flow of calculations (pre cedence relations hips in Excel) Figure 5 – Producing the r th solution to the MI VS problem The solution sequence num ber r is entered in cell C4 . The formulas in cells B7 through F7 convert this num ber in a binary r epresentation of 5 digits. For the specific example the binary notation of number 10, in a 5-bit f orm is 01010, corresponding to se lection of vendor s 2 and 4. The cell formulas in the range B10 to F18 bring f rom the or iginal p rice matrix i n t he spreadsheet problem data (see figure 4) only the values for those columns that correspo nd to the specific choice of ve ndors associated wi th the r th solution. Similar formulas bring the associated vendor fixed cost in cells B20 to F20 . The minimum price for each item is computed in colum n G and the r esults for the r th solution is summ arized in cells G21 , G22 and G23 . The computation of the number of items in the r th solution is necessary in the general case where some v endors do not offer all the m items. 4.3 Finding the Optimu m Solution If one tries all different values of the param eter r in the model described in the previous section, t he optimum solution can be found by com parison of the corresponding c ost o f each solution. Howev er the number of the solutions to be checked is very l arge ev en for moderate valu es of n . T he Excel data- table feature automatically tabulates t he results of complicated calculations, which cannot be expressed analytically, for different values of one or two input param eters. For the MI V S problem one can consider the solution sequence number r a s the data-table input parameters and ask Excel to tabulate the values of the results shown i n fi gure 5 (Cost, number of vendors, and number of items) for all possible values of r . Figure 6 de monstrates the organization of the data-table f or the tabulation of the results. Spreadsheet modelling for solving combinatorial problems: vendor selection Pandelis G. Ipsilandis Proce edings of EuSpRIG 2 008 Confere nce "In P ursuit of Sprea dsheet Excellence" ISBN : 9 78-90561 7-69-2 Copyright © 20 08 E uropean Spread sheet Risks Interest Group ( www.eusprig.org ) a nd Author 103 Figure 6 – MIVS problem : Fi nding the optimum solution For the problem with five vendors there are 31 (2 5 -1) poss ible choices for selecting 1, 2, 3, 4 or 5 vendors. The l ist of v alues 1 to 31 is enter ed in cells M8 to M38. Form ulas pointing to t he cells holding the c alculations of the results to be tabulated are entered in the top raw c ells N7 to P7. Using the Table com mand in the Data drop down m enu, and setting cell C4 as the column i nput cell, Excel automatically calculates and tabulates the results of the form ulas entered in cells N7 to P7 when cell C4 takes every value in column M. Following the tabulation of the re sults, the formulas in cells M3 to P3 calcu l ate th e optimum cost and determ i ne the optimum solution to the pro blem. 4.4 Results for the MIVS P roblem: Procurement o f Library Books The model described in t he previous section was u sed in the case described in section2.1 for the optimization of the number of vendors for the procurement of library b ooks. Figure 7 shows t he organization of the problem 's data and figure 8 the spreadshee t optimization. ………………… Spreadsheet modelling for solving combinatorial problems: vendor selection Pandelis G. Ipsilandis Proce edings of EuSpRIG 2 008 Confere nce "In P ursuit of Sprea dsheet Excellence" ISBN : 9 78-90561 7-69-2 Copyright © 20 08 E uropean Spread sheet Risks Interest Group ( www.eusprig.org ) a nd Author 104 Figure 7 – Price and Fixed Cost Data for the MI VS proble m The dimensions of the price matrix were 4070 rows (e ach row representing a di fferent book title) by 10 columns (each column corresponds t o a vendor). For certain books in the list multiple c opies were asked in the tender. Without any loss of generality, the definition of the item in the library book procu rement MI V S problem each item is defined as the total quantity of eac h different book title, and the as sociated price of the item represented the cost of buying the asked quantity at the offered price. For example, item 1 in figure 7 corresponds to 1 00 copies of the specif ic book title. The optimization results (figure 8) show that minimum cost is achieved if 3 vendors are selected (vendors 5, 9 and 10). The total cost of the optim um solution is 240.816 euro's, which i s broken down to 230.016 € cost of the books and 10.800€ fixed vendo r cost. I f the sug gested procurem ent policy were followed the corresponding cost would exc eed 250.000€. The spreadsheet model informs the user about which books are to be bought from each vendor and at what price, and allows " what-if" questions to be as ked in order to further analyze the results. For example, the user can easily find suboptimum solutions corresponding to a given number of vendors in order to s ee what is the additional cost involved by i ncreasing or dec reasing t he number o f vendors. Also in cases where some books are offered by very few vendors, or not offere d at all, t he model allow s the user t o select a suboptimum solution by putting restrictions to the number of items to be purchased. Figure 8 – Spreadsheet opt im ization of the MIVS problem Spreadsheet modelling for solving combinatorial problems: vendor selection Pandelis G. Ipsilandis Proce edings of EuSpRIG 2 008 Confere nce "In P ursuit of Sprea dsheet Excellence" ISBN : 9 78-90561 7-69-2 Copyright © 20 08 E uropean Spread sheet Risks Interest Group ( www.eusprig.org ) a nd Author 105 4.5 Spreadsheet Modelling L imitations for Combin atorial Problems Size li mitations The spreadsh eet model described in the previous sections performs v ery wel l as long as the size of the problem is such that t he tabulation of th e results pr oduced by direct enumeration f it within the size of a n excel spreadsheet. Given that the data table which holds the results m ust h ave 2 n -1 rows to accommodate all poss ible v endor selection combinations, the latest version of an excel sheet with 65536 rows can acco mm odate problems wit h a maximum of 16 vendors. Although t his may not present a pra ctical problem as in many cases the number of vendors is well below this upper limit, it is still a limitation. A way to over come it is by decom posing the problem into smaller sub- problems. However in combinatorial problems the num b er of solutions is growing explosively; for e ach additional vendor considered the number of solutions t o be examined doubles. Computi ng time Combinatorial problems require excessive computing power a nd CPU time e ven with today's co mputers. Table 1 shows the CPU time for solving M IVS pr oblems of different sizes on a personal com put er with Intel Pentium 800Mhz processor. Computation time as a fu nct ion of the MIVS prob lem size Number of items: m Number of Vendors: n 100 200 400 10 5" 10" 22" 12 27" 45" 1', 45" 14 2', 05" 3', 30" 7', 45" Table 1 – CPU time for obt aining an optimal solution to the MI V S problem The required computation time is considerable for problems of larger size. Nonetheless np-complete combinatorial problems are problem s, whic h are considered hard to s olve, and no one could dream produci ng an optimum so lution to this kind of problems using a personal computer several years ago. Empirical evidence drawn by the results in table 1 suggest that the computation time is almost linear in term s of the number of the parameter m ( number of i tems in the problem), while it shows an exponential growth i n terms of the parameter n (number of ven d ors). Why not us ing the Excel Solver Excel's built-in Solver is v ery powerful optimization to ol but it perform s very well only in linear problems. It presents problems in ca ses of nonlinear problems and it fails most of the time in problem s wit h non-continuous variables. Even t hough the solver allows t he modeler to specify that some variables are binary, o r to "solve" nonlinear problems the results are not necessary reliable [Thiriez (2001)]. For t he MIVS problem t he solver failed to produce an optim um s olution even for sm a ll demonstration problems l ike the one described in figure 3. 5 CONCLUSIONS Spreadsheets have progr e ssed well beyond the number crunching and g raphics presentation into the problem solving and decision tool arena. In this paper we analyzed Spreadsheet modelling for solving combinatorial problems: vendor selection Pandelis G. Ipsilandis Proce edings of EuSpRIG 2 008 Confere nce "In P ursuit of Sprea dsheet Excellence" ISBN : 9 78-90561 7-69-2 Copyright © 20 08 E uropean Spread sheet Risks Interest Group ( www.eusprig.org ) a nd Author 106 the us e of Excel in solvin g the MIVS pr oblem, which is a typical example of hard to solve combinatorial programm ing problems. Alt hough the Solver, the Excel add- on, cannot be used to optimize problems of this t ype, end-users could construct optim i zation models by utili zing other advanced features of Excel. T he spreadsheet approach is m or e attractive to an e nd-user for a number of reasons. The analytical tools are comm onl y available a nd more familiar, the data are e ntered in a clear a nd direct way, they are more portable, an d their layout is more business like, the res u lts a re immediately i nterpretable, explicit m athematical formulations are not requi red, and finally the user has access to other office tools such as g r aphs, and databases to supp ort the problem' s analysis. Another important aspect is the possibility of incorpo rating new calculations in a simple and transparent way that can be easily explained in order to convince clients and managers about the appropr iateness of the approach. There are t echnical limitations described in section 4.5 but with computing power of personal computers doubling every fe w years, restrictions o n problem si ze and computation time w ill be factors with less signi ficance. 6 REFERENCES 1. Akinc, U., Selecting a set of vendors in a manufact ur ing environment, Journal of Operatio ns Manageme nt , 11, (1993), 107- 122 2. Albers S., Im pact of types of functional relations hips, decisions, an d solutions on the applicability of m arketing models, International Journal of Resear ch in Marketing , Vol.17, (2000), pp. 169- 175 3. Ashley, D. A Spreadsheet O ptimization System for Library Staff Scheduling , Compute rs and Operati ons Research , Vol. 22, No. 6, ( 1995), pp.615-624. 4. Barlow J.F., Excel models f o r business and operations management, John Wiley & Sons, West Sussex, (1999 ), UK 5. Chien Y.I., Cunningham H. J., Incorporating produc tion planning in busin ess planning: a linked spreadsh eet approach, Production Planning & Control , Vol. 11, (2000), pp. 299- 307 6. Chu S.C., Tang B.K.P., An optimization m odelling for customer redistribut ion and its spreadsheet implem entation, Computers and Operations Resea rch , Vol. 22, (1995), pp. 335-343 7. Conway, D.G., R agsdale C.T, Modelling O ptimization Problem s in t he Unstructur ed World of Spreadsheets, Omega Internation al Journal of M anagement Scienc e , Vol. 25, No. 3, (1997), pp. 313- 322. 8. Cornford T. and Doukidis G. I., An investigation of t he use of com puters within OR . Europe an Journal of Informat ion Systems , 1, (1991), pp. 131- 140 9. Cornuejols, G., Fisher, M.L., Nemhauser, G.L., "Location of bank accounts t o optimize fl oat: An analytic study of exact and approxim ate algorithms", Management Science , Vol. 23, No. 8, (1 997), 789- 810 10. Degraeve, Z., Labro, E., Roodhooft, F., "An evaluation of vendor selection models from a total cost of ownership prospec tive", European Journal of Operationa l Research , 125 (2000) 34- 5 8 11. Enns S.T., A simple spreadsheet app roach to understanding work flow in produc tion facilities, Total Quality Management , Vol. 10, (1999), pp 107-119 12. Ferguson, N., Langford, D., Chan, W., "Em pir ical study of tende ring practice of Dutch municipalities fo r the procurement of civil-eng i neering contracts", Internati onal Journal of Project Mana gement , Vol. 13, No. 3, ( 1995), pp.157- 161 13. Fylstra D., Lasdon L., Wats on J., Design and use o f the Microsoft Exce l solver, Interf aces , Sept.-O ct., (1998), pp. 29-55 14. Hesse R., Managerial Sp readsheet Modelling and Ana lysis, Irwin, (1997), USA Spreadsheet modelling for solving combinatorial problems: vendor selection Pandelis G. Ipsilandis Proce edings of EuSpRIG 2 008 Confere nce "In P ursuit of Sprea dsheet Excellence" ISBN : 9 78-90561 7-69-2 Copyright © 20 08 E uropean Spread sheet Risks Interest Group ( www.eusprig.org ) a nd Author 107 15. Hoekman, B., "Using international i nstitutions to improv e public procurem en t", World Bank Research Obs erver , Vol. 13, No. 2, ( 1998) 249- 269 16. Hoekman, B., "Using international i nstitutions to improv e public procurem en t", World Bank Research Obs erver , Vol. 13, No. 2, ( 1998) 249- 269 17. Johnson D.J., A spreadshee t method for calculating work completion time probability distributions of paced or l inked assembly lines , International Journal of Pr oduction Research , Vol. 40, (2002), pp . 1131-1153 18. Novak, D.C., Ragsdale, C.T., A d ecision support m et hodology for stochastic m u lti- criteria linear programm ing us ing spreadsheets, Decision Support Systems , Vol. 36, (2003), pp. 99- 116 19. Osman R.B., Multivariate c onstra ined optim i zation using PC- ba sed spreadsheet, Advanc es in Engineerin g Software , Vol. 14, (1992), p p . 137-144 20. Parlar M., Solving dy namic optimization problem s on a personal computer using an electronic spreadsheet, Automatica , Vol. 25, (1989), p p . 97-101 21. Pearson M.M., Mundell l., Spreadshe et modelling of s pa tial problems for the classroom, Decisi on Sciences J ournal of Inn ovative Education , Vol. 1, (2003), pp. 133-139 22. Roy A., Lasdon L., Plane D., E nd-user optimiz ation with spreadsheet models Europe an Journal of Op erational Researc h , Vol. 39, (1 989), pp. 131-137 23. Thiriez H., Im proved OR education through the use of spreadsheet m odels, European Journal of Operational Re search , Vol. 135, (2 001), pp. 461- 476