production examples (1).xls

8/10/2019 Production Examples (1).xls

1/8

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

One of the areas where linear and non-linear programming is applied most frequently is

in manufacturing and production. There are many different ways in which the Solver canbe used to increase productivity, lower cost, reduce waste, etc. We will limit ourselves to

5 different types of models.

First, in the ProductMix worksheet we examine a production mix model. Here we see how

to use parts to build different products and maximize profit.

Second, we look at a machine allocation problem, in two different versions (Alloc1 and

Alloc2). This model determines which machines to use to produce products, in order to

meet a certain demand and minimize cost.

Third, in worksheets Blend1 and Blend2, we look at a 'continuous' rather than 'discrete'

production problem, where the end product requires certain qualities and is a mixtureof previously produced products. This kind of blending problem is very common in

the oil industry and in agriculture, for example.

Fourth, worksheet Process is a process selection problem, where we have several

different ways of producing something (planed wood in this example) and we want to

pick the process that minimizes cost (or maximizes profit).

Finally, in worksheet Cutstock we look at a classic example of a cutting stock problem.In this model we determine how to cut steel sheets so as to minimize the waste of steel.


2/8

254879305.xls.ms_office

Product mix problem.

Your company manufactures TVs, stereos and speakers, using a common parts inventory

of power supplies, speaker cones, etc. Parts are in limited supply and you must determine

the most profitable mix of products to build.

TV set Stereo Speaker

Number to Build-> 0 0 0

Part Name Inventory No. Used

Chassis 450 0 1 1 0

Picture Tube 250 0 1 0 0

Speaker Cone 800 0 2 2 1

Power Supply 450 0 1 1 0

Electronics 600 0 2 1 1

Prof i ts :

By Product $0 $0 $0

Total $0

Problem

Your company builds TVs, stereos and speakers, using a common parts inventory of power supplies,

speaker cones, etc. Parts are in limited supply. What is the best combination of products to build

that maximizes profit?

Solution

1) The variables are clearly the number of TVs, stereos and speakers to build. In this worksheet, they

are given the name Number_to_build.

2) The constraints specify that the number of parts used cannot exceed the supply. This leads to

Number_used = 0 via the Assume Non-Negative option

3) The objective is to maximize profit. In the ProductMix worksheet this is defined as Total_profit.

Remarks

Although this is a good example of a product mix problem, bear in mind the l imitations of the

model. For example, market demand and price elasticity is not included in the model -- we assume

that it doesn't matter how many TVs we build, we will always be able to sell them. Nor are there any

pre-specified minimum or maximum of products that need to be made. The effect of introducing

these restrictions can be studied by examining a Sensitivity Report, which you can select from the

dialog appears with the message 'Solver found a solution.'

Page 2


3/8


Allocation Problem 1 (Single-Period)

Minimize the cost of operating 3 different types of machines while meeting product demand.

Each machine has a different cost and capacity. There are a certain number of m achines

available for each type.

In fo rmat ion on mach ines

Initial cost per

day

Additionalcost per

product

Productsper day

(Max)

Number of

machines

Alpha-1000 $200 $1.50 40 8

Alpha-2000 $275 $1.80 60 5Alpha-3000 $325 $1.90 85 3

Numbe r o f mach ines to use

Alpha-1000 1

Alpha-2000 1Alpha-3000 1

Num ber of produc ts to m ake per day

Alpha-1000 300Alpha-2000 300Alpha-3000 300

Total 900

Demand 750

Maximum num ber o f produc ts tha t can be made per day

Alpha-1000 40

Alpha-2000 60Alpha-3000 85

Cos t $2,360.00

Problem

A company has three di fferent types of machines that all make the same product. Each

machine has a different capacity, start-up cost and cost per product. How should the company

produce its product with the available machines to meet the daily demand?

Solution

1) The variables are the number of machines to use and the num ber of products to make on

each machine. In worksheet Alloc1, these are given the names Products_made and

Machines_used.

2) First, there are the logical constraints. These are

Products_made >= 0 via the Assume Non-Negative option

Machines_used >= 0 via the Assume Non-Negative option

Machines_used = integer.

Second, there are the demand and capacity constraints. These are:

Machines_used


4/8


Allocation Problem 2 (Multi-period)

Minimize the cost of operating 3 different types of machines while meeting product demand over a

week's time. Each machine has a different cost and capacity. There are a certain number of machines

available for each type.

In fo rmat ion on mach ines

Initial cost per

day

cost per

product

Products per

day (Max)

Number of

machines

Alpha-1000 $200 $1.00 40 8

Alpha-2000 $275 $1.80 60 5

Alpha-3000 $325 $1.90 85 3

Number of m ach ines to use

Monday Tuesday Wednesday Thursday Friday

Alpha-1000 0 0 0 0 0

Alpha-2000 0 0 0 0 0

Alpha-3000 0 0 0 0 0

Number of prod uc ts to m ake per day


Alpha-1000 0 0 0 0 0

Alpha-2000 0 0 0 0 0

Alpha-3000 0 0 0 0 0

Made 0 0 0 0 0Carry-over 0 -600 -1400 -2400 -3125

Total 0 -600 -1400 -2400 -3125

Demand 600 800 1000 725 750

Maximum n umb er o f produc ts tha t can be made


Alpha-1000 0 0 0 0 0

Alpha-2000 0 0 0 0 0

Alpha-3000 0 0 0 0 0

Total

Cost $0.00 $0.00 $0.00 $0.00 $0.00 $0.00

Problem

A company has three different types of machines that all make the same product. Each machine has

a different capacity, start-up cost and cost per product. How should the company produce its

product with the available machines to meet the demand over a week's time?

Solution

The solution is very similar in structure to the one found on worksheet Alloc1.

1) The variables are the number of machines to use and the number of products to make on each

machine. In worksheet Alloc2, these given the names Products_made and Machines_used.


Products_made >= 0 via the Assume Non-Negative option

Machines_used >= 0 via the Assume Non-Negative option

Machines_used = integer.

Second, there are the demand and capacity constraints. These are:

Alpha1000s_used


5/8


Blending Problem 2 (Multi-period)

What rock quarries should be used and how much should they produce to meet a certain

quality of limestone (calcium and magnesium content) and minimize cost? There are 4 quarries with

different qualities, capacity and cost to operate. A different output and quality is required each year.

Due to environmental restrictions, only 3 quarries are allowed to be open each year.

In format ion on rock qu arr ies

Calcium

contents

(relative to

required

quality)

Magnesium

contents

(relative to

required

quality)

Maximum

production

per year

(tons)

Cost to

keep quarry

open per

year

($Million)

Quarry 1 1 2.3 2000 3.5

Quarry 2 0.7 1.6 2500 4

Quarry 3 1.5 1.2 1300 4Quarry 4 0.7 4.1 3000 2

Quarries to be us ed (1=yes, 0=no)

Year 1 Year 2 Year 3 Year 4 Year 5

Quarry 1 0 0 0 0 0

Quarry 2 0 0 0 0 0Quarry 3 0 0 0 0 0Quarry 4 0 0 0 0 0

Total 0 0 0 0 0

Am ounts to produce ( tons) .


Quarry 1 0 0 0 0 0

Quarry 2 0 0 0 0 0

Quarry 3 0 0 0 0 0

Quarry 4 0 0 0 0 0

Total 0 0 0 0 0

Required4500 3100 3500 3700 4000

Am ounts that can be produc ed (tons)


Quarry 1 0 0 0 0 0

Quarry 2 0 0 0 0 0

Quarry 3 0 0 0 0 0

Quarry 4 0 0 0 0 0

Calc ium restr i c t ions


Total Amount of

Calcium 0 0 0 0 0Total Amount

Required 0 0 0 0 0

Calcium Required

per Ton (Minimum) 0.9 1.2 1 1.1 0.8

Magnes ium restr i c t ions


Total Amount of

Magnesium 0 0 0 0 0

Page 5


6/8


Total Amount

Required 0 0 0 0 0

Magnesium Required

per Ton (Minimum) 1.9 1.7 2.8 1.9 2.1

Cost Year 1 Year 2 Year 3 Year 4 Year 5 Total

$0.00 $0.00 $0.00 $0.00 $0.00 $0.00

Problem

A com pany owns four rock quarries from which it can extract lim estone with di fferent qualities. Two

qualities are important, the relative amount of calcium and magnesium in the stone. The company

must produce a certain total amount of limestone, with certain qualities, each year. There is a large

fixed cost to keep a quarry operating for extraction purposes each year. Which quarries should be

used each year, and how much limestone should each one produce each year?

Solution

The solution is very similar in structure to the one found in worksheet Blend1.

1) The variables are 0-1 or binary integer variables which determine whether each quarry is open,

and amounts of limestone to be extracted from each quarry. These variables occur in each year.

In worksheet Blend2, these variables the names Quarry_decisions and Amounts_produced.


Amounts_produced >= 0 via the Assume Non-Negative option

Quarry_decisions = binary

Second, there are contraints on the total production and the amount that can be produced at each

quarry. These constraints are:

Total_produced >= Total_required

Amounts_produced = Calcium_requirement

Magnesium_production >= Magnesium_requirement

Both the left-hand and right-hand sides of these constraints depend on the Am ounts_produced

decision variables.

Fourth, there is a constraint that lim its the number of quarries that can be open each year:

Number_of_open_quarries


7/8


Process Selection

A planing mill uses 3 different types of planers. What planers should the com pany use

to minimize cost? The total job has to be finished in 3 hours.

Character is t ics o f p laners

Speed

(ft/min)

Cost

($/hour)

Maximum

wood

thickness

(inches)

Planer 1 5 $150 6

Planer 2 7 $190 4Planer 3 8 $225 2

Wood to be p laned ( f t )

1" 2" 3" 5" Hours Cost

Planer 1 0 0 0 0 0.00 $0.00

Planer 2 0 0 0 0.00 $0.00Planer 3 0 0 0.00 $0.00

Total 0 0 0 0 $0.00

Demand 500 800 600 300

Problem

A planing mill has three different planers. Each planer has a dif ferent speed, cost to operate and

maximum thickness of wood it can handle. What planers should the mill use to m inimize cost, given

an amount of wood and no more than 3 hours to do the job?

Solution

The solution is structurally very similar to the one found on worksheet Alloc1.

1) The variables are the amounts of wood that go through the different planers. In worksheet

Process, these are given the names Wood_through_planer1, Wood_through_planer2 and

Wood_through_planer3.

2) The logical constraints are all defined via the Assume Non-Negative option:

Wood_through_planer1 >= 0



The time and demand constraints give

Total_hours = Demand

3) The objective is to minimize cost and this is defined on the worksheet as Total_cost.

Remarks

This is only a small example of a process selection. An example where process selection is very

important is the oil industry. A process selection model is often used to decide what method to useto create a product.

Page 7


8/8


Cutting Stock

A stee l mill produces sheets of steel in 3 sizes. These sizes are 100, 80 and 55 inches. Unfortunately,

demand is in 3 other sizes; 45,30 and 18 inches.How should the mill cut the sheets to minimize waste?

Poss ib le com bina t ions

45" sheet 30" sheet 18" sheet

Waste

(inches)

Number of

sheets

Total

Waste

1 100" sheet 2 0 0 10 1 10

2 1 1 1 7 1 7

3 1 0 3 1 1 1

4 0 3 0 10 1 10

5 0 2 2 4 1 4

6 0 1 3 16 1 16

7 0 0 5 10 1 10

8 80" sheet 1 1 0 5 1 5

9 1 0 1 17 1 17

10 0 2 1 2 1 2

11 0 1 2 14 1 14

12 0 0 4 8 1 813 55" sheet 1 0 0 10 1 10

14 0 1 1 7 1 7

15 0 0 3 1 1 1

Totals 7 12 26 Total 122

Demand 150 200 175

Problem

A stee l mill produces sheets of steel in three different sizes. Dem and, however, is in 3 other, smal ler, sizes.

How should the company cut the sheets of steel in order to minimize waste?

Solution

1) There are only a limited number of ways to cut the sheets. The variables are the number of times we have

to cut a sheet in a certain way. In worksheet Cutstock these are defined as Sheets_used.

2) The constraints are simple and straightforward.

Sheets_made = Demand

Sheets_used >= 0 via the Assume Non-Negative option

Sheets_used = integer

3) The objective is to minimize waste. This is defined on the worksheet as Total_waste.

Remarks

In some situations it may seem rather difficult to write out all the possibilities for cutting stock as is done in

this model. There is a technique that lets the computer do this, called column generation. It is beyond the

scope of this example to fully discuss this technique.

Page 8

production examples (1).xls

Documents