Case Analysis: Drug Production at Repco
Repco is a drug company that
produces three drugs (A,B,C). Repco can produce Drug A in 1 hour, Drug B in
2 hours and Drug C in 3 hours. Drug B requires 2 unit of Drug A to produce and
Drug C requires 1 unit of Drug B to produce. Any drug that is used to produce
another drug cannot be sold. With a
total availability of 40 labor hours Repco wants to use linear programming to
maximize it’s sales revenue.
Variables/Inputs
The
following variables/inputs are
- Labor hours - Revenue
- Units produced - Units used in production of other
units
- Unit selling price - Leftover units
Spreadsheet Model
This model must have a
balanced equation for each product since everything produced must be used in
some way. Therefore, it must be used as either an input to production or
sold. In order to apply this reasoning, both
amounts produced and amounts sold must be designated as changing cells.
|
Inputs |
|
|
|
|
|
|
|
|
Labor
Hours |
|
|
Production
and Sales |
|
|
|
|
|
Product A |
|
|
Units Produced |
Units Used |
Units Sold |
|
|
|
Product B |
2 |
|
Product A |
20 |
|
||
|
Product C |
3 |
|
ProductB |
10 |
0 |
10 |
|
|
Products |
|
|
Product C |
0 |
0 |
0 |
|
|
Product A |
2 |
|
|
|
|
|
|
|
Product B |
1 |
|
Units
of products used to make products |
|
|
||
|
Product C |
0 |
|
|
Product A |
Product B |
Product C |
Total used |
|
|
|
|
Product A |
0 |
20 |
0 |
|
|
Unit Selling Price |
|
|
Product B |
0 |
0 |
0 |
0 |
|
Product A |
|
Product C |
0 |
0 |
0 |
0 |
|
|
Product B |
$70 |
|
|
|
|
|
|
|
Product C |
$100 |
|
|
|
|
|
|
|
|
|
|
Constraints |
|
|
|
|
|
|
|
|
|
Produced |
|
Used |
|
|
|
|
|
Product A |
20 |
|
|
|
|
Revenue from Sales |
Prodcut B |
10 |
|
10 |
|
||
|
|
|
|
Product C |
0 |
|
0 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Labor
Hour Constraints |
Used |
<= |
Available |
|
|
|
|
|
|
<= |
|
||
Once all the numbers are
inputted into the applicable cells, we use Solver to maximize Repco’s revenue.
In order to do a sensitivity
analysis we use SolverTable, using product C selling price as the input
variable, letting it vary from 100 to 200 in increments of 10.
|
Sensitivity
of revenue, units produced, and units used as inputs to product C selling
price |
|
|
|
||||
|
Product C selling price |
A produced |
B produced |
C produced |
A used |
B used |
C used |
Revenue |
|
|
$E$6 |
$E$7 |
$E$8 |
$H$6 |
$H$7 |
$H$8 |
$C$20 |
|
100 |
20 |
10 |
0 |
20 |
0 |
0 |
$700 |
|
110 |
20 |
10 |
0 |
20 |
0 |
0 |
$700 |
|
120 |
20 |
10 |
0 |
20 |
0 |
0 |
$700 |
|
130 |
11.429 |
5.714 |
5.714 |
11.429 |
5.714 |
0 |
$743 |
|
140 |
11.429 |
5.714 |
5.714 |
11.429 |
5.714 |
0 |
$800 |
|
150 |
11.429 |
5.714 |
5.714 |
11.429 |
5.714 |
0 |
$857 |
|
160 |
11.429 |
5.714 |
5.714 |
11.429 |
5.714 |
0 |
$914 |
|
170 |
11.429 |
5.714 |
5.714 |
11.429 |
5.714 |
0 |
$971 |
|
180 |
11.429 |
5.714 |
5.714 |
11.429 |
5.714 |
0 |
$1,029 |
|
190 |
11.429 |
5.714 |
5.714 |
11.429 |
5.714 |
0 |
$1,086 |
|
200 |
11.429 |
5.714 |
5.714 |
11.429 |
5.714 |
0 |
$1,143 |
We then run another
sensitivity analysis using the two-way SolverTable option using Product C
selling price and labor hour availability as the input variables and selecting
the amount of Product C produced as the output variable.
|
Sensitivity
of amount of C produced to selling price of C (along side) and labor hour
availability (along top) |
|
||||||
|
$B$16 |
40 |
50 |
60 |
70 |
80 |
90 |
100 |
|
100 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
110 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
120 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
130 |
5.714 |
7.143 |
8.571 |
10.000 |
11.429 |
12.857 |
14.286 |
|
140 |
5.714 |
7.143 |
8.571 |
10.000 |
11.429 |
12.857 |
14.286 |
|
150 |
5.714 |
7.143 |
8.571 |
10.000 |
11.429 |
12.857 |
14.286 |
|
160 |
5.714 |
7.143 |
8.571 |
10.000 |
11.429 |
12.857 |
14.286 |
|
170 |
5.714 |
7.143 |
8.571 |
10.000 |
11.429 |
12.857 |
14.286 |
|
180 |
5.714 |
7.143 |
8.571 |
10.000 |
11.429 |
12.857 |
14.286 |
|
190 |
5.714 |
7.143 |
8.571 |
10.000 |
11.429 |
12.857 |
14.286 |
|
200 |
5.714 |
7.143 |
8.571 |
10.000 |
11.429 |
12.857 |
14.286 |
Analysis:
We discover that Repco’s
maximum revenue is $700, which they attain by producing 20 units of product A, which are used to produce 10 units of Product B. All
units of Product B are sold for the maximum revenue. Product C is not sold
because it has such a large labor requirement.
After analyzing the data, we
run a sensitivity analysis to see what the selling price would have to be to
cause Repco to produce Product C. After
running the analysis, it shows that the selling price of the drug would have to
be $130 for Repco to produce Product C.
When it goes beyond $130, but the available labor hours
constraint imposes a limit on the amount of Product C that can be produced.
Because of this another
sensitivity analysis is run, which include labor hour availability as a input. The results
are the same, Product C is not produced until the
selling price is $130, despite of the increase in labor hour availability.