A Guide to Implementing the Theory of
Constraints (TOC) |
|||||

Solver is Excel’s built-in, or rather, add-in linear
programming module. You will find it
under “Tools” “Solver.” If it is not
there you may have to install it.
Check “Tools” “Add-Ins” first, otherwise Excel Help will explain how
to install the module. This is the format we are going to use
Let’s give it Excel row and column labels.
We need to
first set up some relationships between the cells (1) Cell E13 is the sum of the weekly throughput less the weekly operating
expense. (2) Cell B11 is the product of B5 and B6, and repeat for cells C11 – E11. (3) Cell B5 is sum of selling price less materials, B3-B4, and repeat for
cells C5 – E5. (4) Cell F10 is the sum of B10*B6 + C10*C6 + D10*D6 + E10*E6, and repeat
for F9 – F7. Now we can
start solver (Tools -> Solver) and set the cells in the solver window. (1) Set target cell as E13 (2) Equal to max (3) By Changing cells B6:E6 (4) Subject to: Now we can
solve the problem. It’s not as
difficult as it first looks. What we
are asking solver to do is to maximize the objective function – our weekly
net profit, by adjusting the units supplied (in whole numbers) but not
exceeding any of the constraints (market and machine time). In this
example the information for resource B, C, and D, is pretty much redundant
because we already know where the constraint is. However learning to use solver to work
through these types of problems is useful for rapidly developing multiple
what-ifs. And that after all is what
decision analysis is all about. To return
to the previous page press Alt key + left arrow. This Webpage Copyright © 2003-2009 by Dr K. J. Youngman |