Steps on how to use SOLVER to solve an LP. ...

 

MS Excel Solver is an optimization tool that can be used to determine the optimal value of Model objective by changing the values of decision variables used in a model.

To begin with, first we need to organize the data of our problem in the spreadsheet in a logical manner.

 

Next, we need to select cells in the spreadsheet to present the value of each decision variable appearing in the model (green cells), value of objective function (blue cell) and amount of resources (pink cells) used in your model.

In the selected cells in the spreadsheet for each decision variable, initially we assign value of decision variables as 1.

 

Next, we need to create a spreadsheet formula in a cell that holds the value of objective function in our model.

 

Later, we need to create a spreadsheet formula in cells that represent the value of resources utilized corresponding to model constraints.

A simple formula without using SUMPRODUCT can also be used to create spreadsheet model for optimization.

 

Once the problem is presented in the spreadsheet, we can call SOLVER function available in the DATA tab. In the Solver Dialog Box, first provide details related to objective function like cell address of objective function in the spreadsheet and whether to maximize or minimize.

Also, provide allocated cell address in the spreadsheet for the decision variables.

Next, we need to add constraints appearing in the Solver Dialog Box. This we need to do step by step corresponding to each constraint appearing in our model.

Here, we first provide details related to amount of resources used for that constraints (LHS) with appropriate mathematical sign. Next, we provide cell address allocated in the spreadsheet for the available resources of that constraints(RHS).

 

 

 

Once all the constraints details have been added in the Solver Dialog Box, screen would appear as follows.

 

Next, we need to select non-negative condition by check in the box appearing below the constrains in the Solver Dialog Box.

 

At Last, we need to select simplex LP as a Solving Method in the SLOVER dialog box.

Finally, press solve to run the Solver to find the optimal solution.

  

 Ignore other options available for sensitivity analysis. Simply select OK to get the optimal solution of our problem.