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 ©©