VIEWS: 244 PAGES: 9 CATEGORY: Financing POSTED ON: 2/2/2008 Public Domain
Purchasing Examples On each example worksheet, read the comments at the bottom of the sheet, then click Tools Solver... to examine the decision variables, constraints, and objective. To find the optimal solution, click the Solve button. In this series of models we will look at how the Solver can help make decisions about purchasing goods, awarding contracts, etc. You can view these purchasing models as allocating a scarce resource -- namely money -- to various uses in an optimal way. In the contract awards worksheets Award1 and Award2, we have bids from 3 different suppliers to supply diskettes at different prices to our facilities in 4 different states. We want to choose from the suppliers' bids in a way that will minimize our total cost. In Award2, one supplier has specified a minimum size bid for each state. In the inventory policy worksheets Invent1 and Invent2 we compare the EOQ (Economic Order Quantity) with the optimal amounts the Solver suggests we should order. These inventory policy models can also be found in the Finance Examples workbook. In the Media worksheet, a company wishes to buy advertising at the lowest possible cost while still reaching a certain target number of prospects. This type of media buying decision is a common Solver application. In the Purchase worksheet, we examine a purchasing/transportation problem where a company can buy goods at several different places and it needs those goods delivered to several different locations. 20ec43c6-b19e-488d-8631-517c5955f38e.xls Contract Awards 1 A large software company with 4 separate buildings in different states, has offers from 3 different floppy disk manufacturers to supply their monthly need of new diskettes. To whom should the contracts be awarded to minimize cost? Bids per 1000 diskettes Building 1 Manufacturer 1 Manufacturer 2 Manufacturer 3 Building 2 Building 3 Building 4 $50 $52 $49 Building 1 $45 $48 $51 Building 2 $48 $51 $50 Building 3 $52 $54 $52 Building 4 Total Available Contracts awarded per 1000 diskettes Manufacturer 1 Manufacturer 2 Manufacturer 3 Total Required Total Cost 5 5 5 15 20 $3,010 5 5 5 15 25 5 5 5 15 15 5 5 5 15 15 20 20 20 25 30 25 Problem A large software company with 4 different buildings in different states, needs a large supply of diskettes on a monthly basis in each of those buildings. The company has 3 different offers from several floppy disk manufacturers. Which offer or combination of offers should the company accept in order to minimize cost? Solution 1) The variables are the number of diskettes to buy from each manufacturer. On worksheet Award1 these are given the name Contracts. 2) The contracts awarded need to meet the demand of the software company and should not exceed the number of diskettes available from each manufacturer. This gives Contracts_given >= Contracts_required Total_contracts <= Contracts_available Besides these constraints, we also have the logical constraint Contracts >= 0 via the Assume Non-Negative option 3) The objective is to minimize cost. In Award1 this cell is given the name Total_Cost. Remarks Models like the one discussed here are often used by the government. A common example is the contracts that are awarded to companies to supply fuel for airbases. Normally, we have further constraints on the bids from each supplier, such as a minimum number of diskettes in this case. In the Award2 worksheet we will see how to handle such a constraint. Page 2 20ec43c6-b19e-488d-8631-517c5955f38e.xls Contract Awards 2 A large software company with 4 separate buildings in different states, has offers from 3 different floppy disk manufacturers to supply their monthly need of new diskettes. To whom should the contracts be awarded to minimize cost? Bids per 1000 diskettes Building 1 Manufacturer 1 Manufacturer 2 Manufacturer 3 Building 2 Building 3 Building 4 $50 $52 $49 Building 1 $45 $48 $51 Building 2 $48 $51 $50 Building 3 $52 $54 $52 Building 4 Total Available Contracts awarded per 1000 diskettes Manufacturer 1 Manufacturer 2 Manufacturer 3 Total Required 5 5 5 15 20 0 0 0 $3,010 5 5 5 15 25 0 0 0 5 5 5 15 15 0 0 0 5 5 5 15 15 0 0 0 20 20 20 25 30 25 Manufacturer 1 is only interested in contracts of 15000 diskettes or more. Decisions Total Cost Problem A large software company with 4 different buildings in different states, needs a large supply of diskettes on a monthly basis in each of those buildings. The company has 3 different offers from several floppy disk manufacturers. However, Manufacturer 1 is only interested in contracts of 15,000 diskettes or more. Which offer or combination of offers should the company accept to minimize cost? Solution On the surface this problem seems to be no different from the one in Award1. However, we have the problem that the number of diskettes bought from Manufacturer 1 should either be 0 or greater than 15000. This is a frequently occurring constraint and Award2 shows us how to handle this type of condition. The key is to introduce 4 new binary integer variables that tell us whether a contract is bought from manufacturer 1 or not, for each building. 1) The variables are the contracts to be awarded, and the binary integer variables as discussed above. In worksheet Award2 these are given the names Contracts and Contract_decisions. 2) First, we still have the constraints used in Award1: Contracts_given >= Contracts_required Total_contracts <= Contracts_available Contracts >= 0 via the Assume Non-Negative option Second, we have the logical constraints for the binary integer variables: Contract_decisions = binary The 15000 diskettes constraint is now handled by: Awarded_to_1 <= Maximum_diskettes Awarded_to_1 >= Minimum_diskettes 3) The objective is still to minimize total cost, defined on this worksheet as Total_Cost. Page 3 20ec43c6-b19e-488d-8631-517c5955f38e.xls Remarks The introduction of binary integer variables often allows us to express the effect of more complex conditions as seen in this model. It would also be possible to handle other types of constraints. For example, if Manufacturer 2 only distributes diskettes in multiples of 5000, we could model this constraint with binary integer variables. Page 4 20ec43c6-b19e-488d-8631-517c5955f38e.xls Inventory Policy 1 What is the best ordering policy for a warehouse to minimize cost, while meeting demands? The warehouse has a limited storage capacity of 50000 cubic meters (m3). Storage Space per unit (m3) Ordering cost per order Storage space available (m3) Holding Cost Product 1 Product 2 Product 3 Product 4 Demand per month $25 $20 $30 $15 440 850 1260 950 EOQ 200 325 400 150 $50 $50 $50 $50 Cost 50000 Quantity to order each month Space used (m3) Product 1 Product 2 Product 3 Product 4 25 25 25 25 28.28427 40.31129 36.51484 31.62278 Total $713 $900 $1,175 $488 $3,275 5500 10625 15750 11875 43750 Problem A warehouse sells 4 products with a different demand for each product. Each product has a different holding cost and requires a certain amount of space. What should the ordering policy for the warehouse be, given its limited storage capacity? Solution There is an analytical solution for this problem, which is known as the Economic Order Quantity (EOQ) and is given by the following formula: q = SQRT(2 k d/h), where q is the quantity to order, k is the cost to place an order, d is the demand and h is the holding cost of the product. Unfortunately, this formula doesn't always work in the real world. Demand usually fluctuates, ordering time is variable, and other factors arise to further complicate the problem. In this model we have one such factor, a limited storage space. 1) The variables are the amounts to order each month for each product. These are defined as Quantities in this worksheet. By changing these variables we change the total cost. 2) The constraints are very simple. We have a logical constraint and the storage capacity constraint. This gives Quantities >= 0 via the Assume Non-Negative option Space_used <= Available_space If the latter constraint wasn’t present, the solution to the problem could be calculated by the formula given above. 3) The objective is to minimize the total cost, which is defined as Total_cost. It is calculated by adding the individual costs for each product. Those costs are calculated by using the formula: Cost = h q /2 + k d /q, where h, q, k and d are as above. This formula is easy to understand if we realize that the average inventory level is q/2 and the average number of orders is d/q. Remarks In this worksheet we have also calculated the EOQ with the formula given above. Check to see that when you increase the storage capacity and thus relax that constraint, the answers found by the Solver will approach the analytic solution. This model is an example of a non-linear problem, as can easily be seen by looking at the cost formula. Whereas in linear problems it does not matter what are starting values for the variables are, it can be very important to have Page 5 20ec43c6-b19e-488d-8631-517c5955f38e.xls reasonable starting values in non-linear problems. In this model it is not possible to start with a quantity of 0, since this would cause an error in the calculation of the cost. Please see for yourself that the Solver will still find the correct answer, even when the starting values are close (but not equal to) zero. Page 6 20ec43c6-b19e-488d-8631-517c5955f38e.xls Inventory Policy 2 What is the best ordering policy for a warehouse to minimize cost, while meeting demands? The warehouse has a limited storage capacity of 50000 cubic meters (m3) and a budget of $30,000. Holding Cost Product 1 Product 2 Product 3 Product 4 Storage Capacity Storage Space per unit (m3) Demand per Ordering cost month per order Price per unit $25 $20 $30 $15 50000 440 850 1260 950 Budget 200 325 400 150 $30,000 $50 $50 $50 $50 $200 $300 $275 $400 Quantity to order each month EOQ Product 1 Product 2 Product 3 Product 4 Cost of products Cost of holding and ordering Space used (m3) 25 25 25 25 $29,375 28.28427 40.31129 36.51484 31.62278 Total $713 $900 $1,175 $488 $3,275 5500 10625 15750 11875 43750 Problem This model continues to build on the first inventory policy model. We expand the model by giving the warehouse a budget for buying new products. In other words: A warehouse sells 4 products with a different demand for each product. Each product has a different holding cost and requires a certain amount of space. What should the ordering policy for the warehouse be, given its limited storage capacity and limited budget? Solution The variables are exactly the same as in the first model. So is the objective, and the way it is calculated. The difference is that we have an extra constraint which keeps us within the budget. This new constraint is expressed as: Cost_of_products <= Available_money and we also have Space_used <= Available_space as before We still have Quantities >= 0 via the Assume Non-Negative option. This time, we also require integer quantities: Quantities = integer Remarks Once again, we have calculated the EOQs as discussed in the first inventory policy model. If we would give a unlimited budget and unlimited storage space, the Solver would find exactly those values. There is one more change we made in this model compared to the one on worksheet Invent1. This time we required the variables to be integers. Whether this is a valid assumption would depend completely on the type of product that is dealt with. If a model is trying to determine how many cars, airplanes or other such articles to buy, it could be very important to use integer variables. If the model, on the other hand, is giving an indication how much sugar to buy, for example, it would not be appropriate to use integer variables. Page 7 20ec43c6-b19e-488d-8631-517c5955f38e.xls Media Buying A company wants its advertisements to reach at least 1.5 million people through different media. There is a maximum number of ad impressions considered effective in each medium. How should the company advertise to minimize total cost while satisfying the limits on reach and frequency? Media Requirements TV Audience Size Cost / Impression Max Impressions Investments TV Amount Impressions Audience Radio Mail Newspaper Total Radio Mail Newspaper 50,000 $500 20 25,000 $200 15 20,000 $250 10 15,000 $125 15 $0 0 0 $0 0 0 $0 0 0 $0 0 0 $0 0 Problem A company wants its advertisements to reach at least 1.5 million people. It is considering advertising through TV, radio, direct mail, and newspapers. Each medium has a certain cost per run of an ad, a certain audience that will see the ad, and a maximum number of ad impressions before response to the ad falls off too much. How should the company advertise in order to reach its target audience at the lowest possible cost? Solution 1) The variables are the amounts of money to spend on each medium. In worksheet Media these are given the name Investments. 2) The constraints are very simple. Investments >= 0 via the Assume Non-Negative option Impressions <= Max_Impressions for each medium Total_Audience >= 1500000 3) The objective is to minimize total cost. In worksheet Media this is defined as Total_investment. Remarks Often, there are discounts for placing ads with greater frequency in different media. This could be expressed in a model with a 'piecewise-linear' constraint, using binary integer variables. Page 8 20ec43c6-b19e-488d-8631-517c5955f38e.xls Purchasing/Transportation Model A cake mix manufacturer has 4 different plants that all require a certain amount of sugar. There are 5 different companies where the sugar can be bought. Where should the company buy the sugar and how much should it buy, to minimize cost of sugar and shipping? Company 1 Sugar prices (per ton) Company 2 Company 3 Company 4 Company 5 $40 $49 $47 $45 $44 Cost of shipping from companies to plants (per ton) Company 1 Plant 1 Plant 2 Plant 3 Plant 4 Amounts of sugar to buy (tons) Company 1 Plant 1 Plant 2 Plant 3 Plant 4 Total Available supply Cost of sugar Cost of shipping Company 2 Company 3 Company 4 Company 5 Total Demand Company 2 Company 3 Company 4 Company 5 $8 $7 $7 $8 $4 $6 $3 $2 $5 $3 $7 $5 $4 $2 $5 $6 $3 $4 $2 $7 0 0 0 0 0 350 $0 $0 0 0 0 0 0 250 $0 $0 0 0 0 0 0 200 $0 $0 0 0 0 0 0 300 $0 $0 0 0 0 0 0 500 $0 $0 Total cost 0 0 0 0 420 360 400 375 $0 $0 $0 Problem A cake-mix manufacturer has 4 different plants throughout the country. It can buy sugar from 5 different companies. The cost of the sugar and the transportation costs from each company to each plant are known. Where should the company buy sugar and how much should it buy, to meet the demand and minimize cost? Solution 1) The variables are the amounts of sugar to be bought from each company for each plant. On worksheet Purchase these are given the name Amounts_to_buy. 2) The constraints are simple and straightforward: Amounts_to_buy >= 0 via the Assume Non-Negative option Total_amounts_to_buy >= Demand Total_sold <= Supply 3) The objective is to minimize cost. This is defined as Total_cost on the worksheet. Remarks Even though this model is very simple, it is one of the most used models in the industry. It routinely saves many companies thousands or even millions of dollars a year. Page 9