Tag Archives: linear programming

Classic Linear Programming in Microsoft Solver Foundation for Excel

The Supply Chain optimization is a classic sample in linear programming. The Microsoft Solver Foundation comes with an example on solving this problem in Excel.
solver-foundation2

The data are categorized and neatly listed out in one worksheet, while the Model pane provided interface for constraints settings. Unlike in the standard Excel Solver, the modelling is a bit more complex, but the interface helped by providing an organized and consistent user interface in Excel.

solver-foundation3

Advertisements

Exploring optimization problems in Excel

Excel is able to solve optimization problems. Two commonly available tools are the build-in Solver tool and the Excel plugin for Microsoft Solver Foundation (MSF). The former is not installed by default but can be easily enabled through the Excel Options menu. The latter is a separate download available from Microsoft.

For a simple comparison of the performance of the two, the non-linear data fitting example from the MSF is used as benchmark.
msf-1

MSF provided additional menu pane within Excel for complex optimization operations.
msf-2

Optimization results and log of this benchmark run of a non-linear data fitting sample from the MSF, based on an NIST sample.
msf-10
msf-3

Goals setting screen.
msf-5

Model Display.
msf-6

On the other hand, the built-in Solver offered a simpler interface but still provide detailed reports, including answer, sensitivity, and limits reports in separate spreadsheets.
msf-7

msf-8

The built-in Excel Solver offered easy to use interface, while the Microsoft Solver Foundation is more capable for complex problems and modelling.

The NelderMead solver is selected in this benchmark by the MSF. Check out this previous installment for details of running Nelder-Mead on TI Nspire. The same data set is performed on the Nspire using Nelder-Mead to obtain the following results.
msf-9
msf-11

Solving linear programming problem with Nelder-Mead method

For solving linear programming problem, the simplex method is often applied to search for solution. On the other hand, the Nelder-Mead method is mostly applied as a non-linear searching technique. It would be interesting to see how well it is applied to a linear programming problem previously solved using the Simple Method in TI-84.

The Nelder-Mead method is ran under the TI Nspire CX CAS with NM program written in the TI Basic program. The program accepts arguments including the name of the function to maximize as a string and a list of initial parameters to execute the Nelder-Mead algorithm. The function itself is declared using piece-wise function to bound the return value to the function to maximize while giving penalty to values that violate any constraints (as in the inequalities of the standard simplex method).

Previous program in the TI-84 using the simplex method obtained {200,400} as the solution. The Nelder-Mead returned a solution very close to it.

nmsimplex

Simplex Algorithm on the Casio 9860GII

With matrix capable calculator, simplex algorithm for common maximization problem can be solved easily like in the TI-84.

The Casio 9860GII is also equipped with equivalent matrix operations to solve the same problem.

casiosimplex1

casiosimplex2

casiosimplex4

casiosimplex3