purchasing examples

8/8/2019 Purchasing Examples

1/11

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 asallocating 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 possiblecost 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.


2/11

49192054.xls

Page 2

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 Building 2 Building 3 Building 4

Manufacturer 1 $50 $45 $48 $52



Contracts awarded per 1000 diskettes

Building 1 Building 2 Building 3 Building 4 Total Available

Manufacturer 1 5 5 5 5 20 25



Total 15 15 15 15

Required 20 25 15 15

Total Cost $3,010

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 notexceed the number of diskettes available from each manufacturer. This gives

Contracts_given >= Contracts_required

Total_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.


3/11

49192054.xls

Page 3

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 Building 2 Building 3 Building 4




Contracts awarded per 1000 diskettes

Building 1 Building 2 Building 3 Building 4 Total Available




Total 15 15 15 15

Required 20 25 15 15

Manufacturer 1 is only interested in contracts of 15000 diskettes or more.

Decisions 0 0 0 00 0 0 0

0 0 0 0

Total Cost $3,010

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_requiredTotal_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 = Minimum_diskettes


4/11

49192054.xls

Page 4

3) The objective is still to minimize total cost, defined on this worksheet as Total_Cost.

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.


5/11

49192054.xls

Page 5

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).

Product 1 $25 440 200 $50 50000

Product 2 $20 850 325 $50

Product 3 $30 1260 400 $50

Product 4 $15 950 150 $50

Quantity to order each month

EOQ Cost Space used (m3)

Product 1 25 28.28 $713 5500

Product 2 25 40.31 $900 10625

Product 3 25 36.51 $1,175 15750

Product 4 25 31.62 $488 11875

Total $3,275 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 theproblem. 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


6/11

49192054.xls

Page 6

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

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.


7/11

49192054.xls

Page 7

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.

Product 1 $25 440 200 $50 $200Product 2 $20 850 325 $50 $300

Product 3 $30 1260 400 $50 $275

Product 4 $15 950 150 $50 $400

Storage Capacity 50000 Budget $30,000

Quantity to order each month Cost of holding Space

EOQ and ordering used (m3)

Product 1 25 28.28 $713 5500

Product 2 25 40.31 $900 10625

Product 3 25 36.51 $1,175 15750

Product 4 25 31.62 $488 11875

Cost of products $29,375 Total $3,275 43750

Problem

This model continues to build on the first inventory policy model. We expand the model by giving the warehouse

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 order

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

Cost_of_products


8/11

49192054.xls

Page 8

ing

as:

, it

h


9/11

49192054.xls

Page 9

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 Radio Mail NewspaperAudience Size 50,000 25,000 20,000 15,000

Cost / Impression $500 $200 $250 $125

Max Impressions 20 15 10 15

Investments

TV Radio Mail Newspaper Total

Amount $0 $0 $0 $0 $0

Impressions 0 0 0 0

Audience 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 = 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.


10/11

49192054.xls

Page 10

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 Company 2 Company 3 Company 4 Company 5

Sugar prices (per ton) $40 $49 $47 $45 $44

Cost of shipping from companies to plants (per ton)

Company 1 Company 2 Company 3 Company 4 Company 5

Plant 1 $8 $4 $5 $4 $3

Plant 2 $7 $6 $3 $2 $4

Plant 3 $7 $3 $7 $5 $2

Plant 4 $8 $2 $5 $6 $7

Amounts of sugar to buy (tons)

Company 1 Company 2 Company 3 Company 4 Company 5 Total

Plant 1 0 0 0 0 0 0

Plant 2 0 0 0 0 0 0

Plant 3 0 0 0 0 0 0Plant 4 0 0 0 0 0 0

Total 0 0 0 0 0

Available supply 350 250 200 300 500

Cost of sugar $0 $0 $0 $0 $0 $0

Cost of shipping $0 $0 $0 $0 $0 $0

Total cost $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 thecompany 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


11/11

49192054.xls

Page 11

Demand

420

360

400375

purchasing examples

Documents