Using Goal Seek and Solver in MS Excel - Using Solver
(Page 3 of 4 )
Assuming you have successfully installed Solver in your MS Excel by following the procedure from the Microsoft website, we will illustrate how to solve a typical problem using Solver.
Let us use our previous example, summed up below:
Total monthly given budget: $1000
Constraints:
Variable X (food expenses) = $400
Variable X (food expenses) = $100
Variable Y (travel expenses) = $600
Variable Y (travel expenses) = $200
Problem: Solve the optimal food and travel expenses that meet the required monthly budget and given constraints.
Solution: Now this is a more interesting problem. You have two unknowns here (food and travel expenses), as opposed to just one in the goal seek example.
Step 1: Set up the conditions above in an Excel spreadsheet.
Ideally, use the same worksheet/cell setups as you used with goal seek on the previous page. To avoid confusion, summarize the variables with their respective cell locations. see below:
$A$1 = The “Overall monthly budget required”
$A$2 = Food expenses
$A$3 = Travel expenses
Step 2: Click Tools, and then “Solver.” Set the following data carefully in the “Solver Dialog” Box:
Set Target Cell: $A$1
Equal to: (please check “value of:”) 1000
By changing cells (select cell A2 and A3): $A$2:$A$3
Subject to the constraints:
$A$2 <= 400
$A$2 >= 100
$A$3 <= 600
$A$3 >= 200
To add these specific constraints, click “Add.” Next, click on the cell reference (whether it is A2 or A3 in this example), set <= or >= , and then enter the value as the constraint. Finally, click OK to add the constraint. Do the same for the other conditions; you can only add one constraint at a time. If you have correctly set up the constraints and the other stuff, it should look like this:
Next: Solving with Solver >>
More BrainDump Articles
More By Codex-M
