Leveling Up in OR: Excel Solver vs. Python

Bala Madhusoodhanan - Sep 11 '23 - - Dev Community

Intro:
Operations Research, often referred to as OR, is a dynamic field at the intersection of mathematics, science, and decision-making. It was born out of the necessity to solve real-world problems during World War II, where military strategists needed better ways to allocate resources, plan logistics, and optimize their operations for maximum impact. Optimization is branch which is to succeed with decision making in real-time, navigating through a maze of choices and constraints. Optimization in OR is like having a superpower that helps organizations make decisions that minimize costs, maximize profits, reduce waste, and streamline operations

Real - world Concepts:
The series Optimization Simplified we explored some of the real world examples. These real-world examples illustrate how optimization techniques play a crucial role in improving efficiency, reducing costs, and making informed decisions across various industries and sectors. They demonstrate the versatility and applicability of optimization in solving complex problems efficiently.

Considerations for Excel Solver:
Excel Solver is a powerful tool for solving a wide range of optimization problems, particularly those that are relatively small to medium in size and don't require highly specialized algorithms. Solver proves most effective when you can decompose the larger problem into smaller components, allowing you to utilize Solver to prototype and establish the value proposition for a systemic solution.
Constraints with Solver:
1) The standard Microsoft Excel Solver has a limit of 200 decision variables
2) Problems that involve both discrete (integer) and continuous decision variables, combined with nonlinear constraints, can be challenging
3) Not suited for extremely complex optimization problems, especially those with a large number of decision variables and constraints. Would have performance impact due to memory and processing constraints
Excel is set by default to use all cores it can. But it also depends on how your code and functions are written. But ifnot then enable in advance options
Image description

Showdown - Excel Solver vs. Python:

Criterion Excel Solver Python
Skillset needed Low Code Pro Code
Community and Support Medium High (lots of literature but every implementation need a design approach)
Documentation Medium Mid
Security N/A Need to Engineer the solution
Scalability Low Medium to High depending on the type of solver you choose
Solution Quality Low High
Licensing Free Depends on type of solver. Have free solvers as well
Performance Low Depends on type of solver. Medium performance If open version solvers used

Inference from the series:
Ultimately, the choice between Excel Solver and Python programming should align with the specific requirements and characteristics of your optimization project. Evaluating factors like complexity, problem size, customization needs, and integration capabilities will help you make an informed decision.

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .