BUSI4489 Tutorial 1 Problems 21 22

Tutorial 1: Solving LPs with excel solver/graphical approach
1. Steelco has received an order for 100 tonnes of steel. The order must
contain at least 3.5 tonnes of nickel, at most 3 tonnes of carbon, and
exactly 4 tonnes of manganese. Steelco receives £200/tonne for the order.
To fill the order, Steelco can combine four alloys, whose chemical
composition is given in the table below. Steelco wants to maximise profit
(revenues – costs). Formulate Steelco’s problem as an LP and solve it with
Excel Solver.
Alloy 1 Alloy 2 Alloy 3 Alloy 4

Nickel 6% 3% 2% 1%
Carbon 3% 2% 5% 6%
Manganes 8% 3% 2% 1%
e
Cost/tonne £120 £100 £80 £60
2. AEROBEST is planning to source a large volume product for

distribution to four global regions from three manufacturing plants in
different parts of the worlds. The following tables give the estimated
annual volumes required by each of the four regions, the capacities of
each of the plants and the projected transportation cost to supply each
region from each plants.
Region Region 1 Region 2 Region 3 Region 4

Projected
annual 125,000 100,000 50,000 75,000
demand
Plant Plant 1 Plant 2 Plant 3

Estimated
125,000 75,000 200,000
capacity
Transportatio Region 1 Region 2 Region 3 Region 4

n costs per
unit
Plant 1 5 4 3 2
Plant 2 4 2 1 4
Plant 3 9 7 5 3
Develop an optimisation model to determine the appropriate quantities to

source from each of the plants for each of the regions. What will be the
decision variables, constraints and the objective function? Formulate the
model and solve with Excel Solver.
3. Company ABC produces products P and Q. The maximum market

demand for P is 100 units per week at a selling price of $90 per unit. The
maximum market demand for Q is 50 units per week at a selling price of
$100 per unit. P is made by assembling one purchased component, one
processed part from RM 1 and one processed part from RM2. Q is made
by assembling one processed part of RM 2 and one processed part of RM
3. There are four workers (A, B, C, D) in the plant (see diagram below).
They work an 8 hour day and a five day week - without stopping. The
fixed operating expense for the plant per week is $6,000. This does not
include any raw material purchased. What is the maximum net profit
(minimum loss) this company is capable of earning per week? How much P
and how much Q should be produced? Formulate this problem as a linear
program. Find the optimal solution graphically. Enter the LP in Excel and
solve it with Solver.
P Q
$90/Unit $ 100/Unit
100 50 Units/Week
Units/Week
D D
15 minutes/unit 5 minutes/Unit
Purchase C C B
Part 10 5 15
$ 5/Unit minutes/Unit minutes/unit minutes/unit
A B A
15 15 10
minutes/unit minutes/unit minutes/unit
Raw Material Raw Material Raw Material

1 2 3
$ 20/unit $ 20/unit $ 20/unit
4. U.S. Labs manufactures mechanical heart valves from the heart valves
of pigs. Different heart operations require valves of different sizes. U.S.
labs purchases pig valves from three different suppliers. The cost and size
mix of the valves purchased from each supplier are given in the table
below. Each month, U.S. Labs places one order with each supplier. At
least 500 large, 300 medium and 300 small valves must be purchased
each month. Because of limited availability of pig valves, at most 700
valves per month can be purchased from each supplier. Formulate an LP
that can be used to minimize the cost of acquiring the needed valves.
Enter the LP in Excel and find the optimal solution with Solver.
Supplier Cost per Percent Percent Percent

valve ($) Large Medium Small
1 5 40 40 20
2 4 30 35 35
3 3 20 20 60
5. The seasonal yield of olives in a Piraeus, Greece, vineyard is greatly

influenced by a process of branch pruning. If olive trees are pruned every
two weeks, output is increased. The pruning process requires considerably
more labour than permitting the olives to grow on their own, and it results
in smaller-sized olives. It also permits olive trees to be spaced closer
together. The yield of 1 barrel of olives by pruning requires 5 hours of
labour and 1 acre of land. The production of a barrel of olives by the
normal process requires only 2 labour hours but takes 2 acres of land. An
olive grower has 250 hours of labour available and a total of 150 acres for
growing. Because of the olive size difference, a barrel of olives produced on
pruned trees has a profit of £20, whereas a barrel of regular olives has a
profit of £30. The grower has determined that, because of market
preferences, the production of regular olives should be more than 1.5 times
the production of pruned olives.
a) Ignoring any other considerations, develop a linear programming model

to find the optimal combination of barrels of pruned and regular olives that
will yield the maximum profit.
b) Show all the constraints and the feasible region graphically and find the
optimum solution.
c) Classify each of the constraints as redundant, non-binding or binding and

explain how the olive grower should interpret this information.

BUSI4489 Tutorial 1 Problems 21 22

Uploaded by

Copyright:

Available Formats

BUSI4489 Tutorial 1 Problems 21 22

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

BUSI4489 Tutorial 1 Problems 21 22

Uploaded by

Copyright:

Available Formats

Tutorial 1: Solving LPs with excel solver/graphical approach

Alloy 1 Alloy 2 Alloy 3 Alloy 4

2. AEROBEST is planning to source a large volume product for

Region Region 1 Region 2 Region 3 Region 4

Plant Plant 1 Plant 2 Plant 3

Transportatio Region 1 Region 2 Region 3 Region 4

Develop an optimisation model to determine the appropriate quantities to

3. Company ABC produces products P and Q. The maximum market

Raw Material Raw Material Raw Material

Supplier Cost per Percent Percent Percent

5. The seasonal yield of olives in a Piraeus, Greece, vineyard is greatly

a) Ignoring any other considerations, develop a linear programming model

c) Classify each of the constraints as redundant, non-binding or binding and

You might also like