Example 4.

Drug Production at Repco

 Background Information
• Repco produces three drugs, A, B and C, and
can sell these drugs in unlimited quantities at unit
prices $8, $70, and $100, respectively.
• Producing a unit of A requires 1 hours of labor.
• Producing a unit of B requires 2 hours of labor and
2 units of A.
• Producing 1 unit of C requires 3 hours of labor
and 1 unit of B.

Albright/Winston Management Science Modeling South-Western/Cengage Learning ©

Thomson/South-Western 2007 ©
 Background Information --
continued
• Any product A that is used to produce B cannot
be sold separately, and any product B that is used
to produce C cannot be sold separately.
• A total of 4000 hours of labor are available.
• Repco wants to use LP to maximize its
sales revenue.

Albright/Winston Management Science Modeling South-Western/Cengage Learning ©

Thomson/South-Western 2007 ©
 Solution
• The variables and constraints required to
model this problem are shown below.
• The key to the model is understanding which
variables can be chosen - the decision variables -
and which variables are determined by this choice.

Albright/Winston Management Science Modeling South-Western/Cengage Learning ©

Thomson/South-Western 2007 ©
 Developing the Model
• The key to developing the spreadsheet model is that
everything that is produced must be used in some way.
• Either it must be used as an input to the production of
some other product, or it must be sold. Therefore, we have
the “balance” equation for each product:
Amount produced = Amount used to produce
other products + Amount sold
• We will implement this “balance” equation by:
1. Specifying the amounts produced in changing cells
2. Calculating the amounts used to produce other drugs based
on the way the production process works
3. Calculate the amounts sold from the balance equation by
subtraction, then imposing the constraint that the
balance equation must be satisfied
Albright/Winston Management Science Modeling South-Western/Cengage Learning ©
Thomson/South-Western 2007 ©
 Production [Link]
• This file shows the spreadsheet model for
this problem.

Albright/Winston Management Science Modeling South-Western/Cengage Learning ©

Thomson/South-Western 2007 ©
 Developing the Model
• To proceed, carry out the following steps.
– Inputs and range names. Enter the inputs in the shaded ranges.
– Units produced. Enter any trial values for the number of units
produced and sold in the Units_produced range.
– Units used to make other products. In the range G16:I18 calculate
the total number of units of each product that are used to produce
other products. Begin by calculating the amount of A used to
produce A in cell G16 with the formula =B7*B$16 and copy this
formula to the range G16:I18 for the other combinations of
products. Then calculate the row totals in column J with the SUM
function. It is convenient to “transfer” these sums in column J to the
B18:D18 range. Use Excel’s TRANSPOSE function, type the
formula =TRANSPOSE(J16:J18) and press Ctrl+Shift+Enter (three
keys at once).
Albright/Winston Management Science Modeling South-Western/Cengage Learning ©
Thomson/South-Western 2007 ©
 Developing the Model --
continued
– Units sold. Enter the formula =B16-B18 in cell B19 and
copy it to the range C19:D19.
– Labor hours used. Calculate the total number of labor
hours used in cell B23 with the formula
=SUMPRODUCT(B5:D5,Units_produced).
– Total revenue. Calculate Repco’s revenue from sales
in the cell B25 with the formula
=SUMPRODUCT(B12:D12,Units_sold).

Albright/Winston Management Science Modeling South-Western/Cengage Learning ©

Thomson/South-Western 2007 ©
 Using Solver
• To use Solver to maximize Repco’s revenue, fill
in the main Sovler dialog box a shown below.

Albright/Winston Management Science Modeling South-Western/Cengage Learning ©

Thomson/South-Western 2007 ©
 Solution
• We see that Repco obtains a revenue of $70,000
by producing 2000 units of product A, which are
then used to produce 1000 units of product B.
• All units of product B produced are sold.
• Even though product C has the highest selling
price, Repco produces none of product C.
• This is because of the large labor requirements
for product C.

Albright/Winston Management Science Modeling South-Western/Cengage Learning ©

Thomson/South-Western 2007 ©
 Sensitivity Analysis
• We saw that product C is not produced at all,
even though its selling price is by far the highest.
• How high would this selling price have to be
to induce Repco to produce any of product C?
• We use SolverTable to answer this, using product
C selling price as the input variable, letting it vary
from $100 to $200 in increments of $10, and
keeping track of the total revenue, the units
produced of each product, and the units used
(row 18) of each product. The results appear on
the next slide.
Albright/Winston Management Science Modeling South-Western/Cengage Learning ©
Thomson/South-Western 2007 ©
Albright/Winston Management Science Modeling South-Western/Cengage Learning ©
Thomson/South-Western 2007 ©
 Sensitivity Analysis --
continued
• As we see, until the product C selling price gets
to $130, Repco uses the same solution as above.
• However, when it increases to $130 and
beyond, 571.4 units of C are produced.
• This in turn requires 571.4 units of product B,
which requires 1142.9 units of product A, but
only product C is actually sold.
• Of course Repco would like to produce even
more of product C, but the labor hour constraint
does not allow it.

Albright/Winston Management Science Modeling South-Western/Cengage Learning ©

Thomson/South-Western 2007 ©
 Sensitivity Analysis --
continued
• Therefore, further increases in selling price of
product C have no effect on the solution –
other than increasing revenue.
• Because available labor imposes an upper limit on
the production of product C, even when it is very
profitable, it is interesting to see what happens
when the selling price of product C and labor hour
available both increase. Here we can use a two-
way SolverTable and select the amount produced
of product C and the labor hours as the two inputs.

• The results appear on the next slide.

Albright/Winston Management Science Modeling South-Western/Cengage Learning ©
Thomson/South-Western 2007 ©
Albright/Winston Management Science Modeling South-Western/Cengage Learning ©
Thomson/South-Western 2007 ©
 Sensitivity Analysis --
continued
• This table shows that no product C is
produced, regardless of labor hour availability,
until the selling price of C is $130.
• The effect of increases in labor hour availability
is to let Repco produce more of product C.
• Specifically, Repco will produce as much of C as
possible, given that 1 unit of B, and hence 2
units of A, are required for each unit of C.

Albright/Winston Management Science Modeling South-Western/Cengage Learning ©

Thomson/South-Western 2007 ©
 Sensitivity Analysis --
continued
• Before leaving this example, we provide some
further insight into the sensitivity behavior.
• Specifically, why should Repco start producing
product C when its unit selling price increases
to some value between $120 and $130?
• We can provide a straightforward answer to
this question because there is a single
resource constraint, the labor hour constraint.

Albright/Winston Management Science Modeling South-Western/Cengage Learning ©

Thomson/South-Western 2007 ©
 Sensitivity Analysis --
continued
• Consider the production of 1 unit of product B. It
requires 2 labor hours plus 2 units of A, each of
which requires 1 labor hour, for a total of 4 labor
hours, and it returns $70 in revenue.
• Therefore, revenue per labor hours
when producing product B is $17.50.
• To be eligible as a “winner” product C has to beat
this. To beat the $17.50 revenue per labor hour
of product B, product C’s unit selling price must
be at least $122.50.

Albright/Winston Management Science Modeling South-Western/Cengage Learning ©

Thomson/South-Western 2007 ©
 Sensitivity Analysis --
continued
• If its selling price is below this, such as $121,
Repco will sell all products B and no product C.
• If its selling price is above this, such as $127,
Repco will sell all product C and no product B.
• As this analysis illustrates, we can sometimes –
but not always – unravel the information
obtained by SolverTable.

Albright/Winston Management Science Modeling South-Western/Cengage Learning ©

Thomson/South-Western 2007 ©