A Guide to Implementing the Theory of
Using Excel Solver To Calculate The P & Q Answer
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