Transcript Optimization using LP models Repco Pharmaceuticals (Ex 4.6)

Optimization using LP models
Repco Pharmaceuticals (Ex 4.6)
Ravi Krishna Ravula
Dsc 8240
Situation and Objective
 Repco
is a Drug Mfg company
 It produces 3 drugs (A,B,C) and are
sold @ $8, $70 and $100
respectively.
 2 units of A are required to produceB
 1 unit of B is required to produce C
 Objective: Maximize sales revenue
Variables and Measures
Input Variables:
Required labor hrs to produce a
unit
Unit of a product used to
produce another product
Unit selling price of each drug.
Output variables:
Revenue from Sales.
Amount of products sold
Constraints:
Amount produced = Amount
used to produce other
products + Amount sold
Total labor hours available
Goods used in producing another
product cannot be sold.
Decision Variables:
Amount of goods produced
(these are the trial values)
Mathematical formulation
Labor hrs reqd to produce A = 1 hr. B = 2 + 2 = 4 hrs and C
= 3 + 4 = 7hrs
Unit of product used to produce another product.
A B C
A 0 2 0
B 0 0 1
C 0 0 0
Amount of products sold = Units produced – Units used as
inputs to produce other units
Revenue from Sales = Sum of each Product unit selling price *
amt of product sold
Spreadsheet model






The inputs are in the green
box on the left side.
The grey shaded area has
the ranges
The production plan is in
orange colored cells.
The production plan
includes the changing cells
(units produced) and
output cells (units sold)
The yellow cells show the
Balance constraints
The red and blue cells
show the Revenue (Target
cell)
Using Solver
After invoking the solver,
and filling in the
target cell (Revenue),
changing cells (Units
produced, sold) and
the constraints, click
on options and choose
Non-negativity and
linear model
assumption.
Optimum Solution from Solver
 Optimal
Production A = 20 units, B
=10 units.
 Optimal revenue is $700
 All units of A are used to produce B
 Only product B is being sold.
 Though C has the highest selling
price it is not being formed because
it involves larger labor requirements.
(View in excel)
Sensitivity Analysis
 To
see how the optimal solution
changes as we change one or more
of the inputs
 Solver table is invoked to perform
sensitivity analysis.
One way table



Sensitivity of revenue,
units produced and
units used as inputs to
product C selling
price.
with the price of C at
$130, there is a
production of 5.714
units of C, resulting in
revenue of $743.
Only product C is
being sold.
Using a 2-way table
Sensitivity of C produced to Selling price of C and
labor hour abailability
15
Number of C
produced
10
5
100
Selling Price of C
180
40
200
160
140
0
100

Sensitivity of amount
of C produced to
selling price of C and
labor hour availability
Though there is an
increase in labor hours
there is no production
in C till the price
reaches $130
120

Labout hour
Availability
It was seen that though there is an
increase in labor hours there is no
production in C till the price reaches $130
as it was in the previous analysis.
 B requires 4 labor hours and C needs 7
hours. Revenue of B for one unit is $70.
when divided by Labor hours needed it
shows that $17.50 is revenue per labor
hour.
 To earn more C should sell at a minimum
of 7 * 17.50 = $122.50.
