Wednesday, February 16, 2011

How to Use Excel's Solver


1. Make sure that the Microsoft Excel solver add-in is available. If not select 'Options' from the main Menu and choose 'Solver Add-in' from the options menu. Restart Microsoft Excel and the Solver option should be available under the data menu.
2. Construct a model that contains and specifies variables, constraints and an objective.
3. Determine the variables on the solver construct model. These numbers can change given a specified constraint or constraints. For example, the variable cells or changing cells could be the amount of money spent by each department of a company. These cells do not need to be defined, just labeled and designated as changing cells.
4. Establish the constraints for the Excel solver model. These are the specific constraints placed on cells within the Solver model. These constraints can apply to the variable cells or the objective cells. For instance, in budget model it would be the overall budget limit or specific limits that are placed on each department.
5. Ascertain the objective of the solver model. The objective is what the model is intended to 'Solve.' Again using the budget example, it would be the overall budget limit. Essentially, it is the answer to the original question posed when using Solver.
6. Input the information into the Excel Solver model. Select 'Data' from the main menu and then choose the 'Solver' add-in. The Solver dialogue box should appear. First, input the information into the target cell or objective cell. Then set limits of that cell; in the budget case it would be the maximum amount of money to be spent. Next, select the cell ranges that are the changing cells. Finally, enter any constraints that will limit the changing cells.
7. Solve the model with Microsoft Excel Solver. After all the data is correctly entered click the 'Solve' button on the Solver dialogue box.

Blogger news