Enable add-in capability of "Solver", which permits solving LP-type problems (Linear Programming with constraints).
(1) In Excel, go to Menu item "Tools/Add-Ins..." and select (see below):
(2) Click on "Solver
Add-in" in
the "Add-Ins available" list and click "OK": (see below)
[If
you're running an older version of Excel, Solver may not be available]
(3) Solver comes with its own tutorial and Help support if you need it, or you can just jump in and use the "Kristin's Cookies" examples I have already set up and used in class. Download the Kristin's Cookies example, select cell C3 (below Total margin), go to "Tools/Solver" in the Menu and it will bring up the Solver dialog box (see below):
Total margin cell is set equal to 2.5*A3+3.1*B3 ($2.50 per xcc and $3.10 per xor)
Usage: In the Solver box,
note that we have already:
-- Set Target Cell to
C3 (the Total margin, or the "thing" I want to optimize)
-- Equal to: Max
(that is, I want to Maximize the Total margin)
-- By Changing Cells A3 and B3 (the number of dozen of chocolate
chip (xcc) and oatmeal rasin (xor) cookies; that is, I want Excel to vary
xcc and xor in such a way as to Maximize the Total margin
-- Subject to the Constraints listed (e.g. A3
or xcc less than or equal to100 but greater than or equal to
zero, etc.)
You can remove, change, or add to
the constraints if you wish to explore. By then clicking on Solve,
it will then give you the optimal solution, subject to the constraints you
have specified. The solution will give you:
-- the optimal values of xcc and xor(that lead to the maiximization of...
-- the Total margin (that has
been
maximized)
If you want to see another set of
simpler constraints, check out Kristin's
Cookies with Workload Sharing, in which the only constraints are that:
-- xcc (A3) and xor (B3) must be greater than or equal to zero and
-- total work time of Kristin and her roomate are less than or equal to
2400 minutes (2 x 20 hours), with the assumption that their skills are interchangeable
and hence the labor can be load-shared. Clear as mud, eh?