How do you use Excel Solver for linear optimization in different fields and applications?

Constraints can be tricky as there might be constraints that wouldn't come naturally to mind, because of the common sense about the world that we have, but the models haven't. For this reason sometimes the results will only start making sense once we have introduced the constraints that capture that "common sense". Another challenge that may be faced, is finding that after adding the constraints, we may get from Solver, that there are no feasible solutions. It's important to learn that there exists "goal programming" which is a technique based on "relaxing" constraints, making some constraints allow transgressions over the thresholds originally determined, and working on each relaxed constraints to minimize those transgressions.

Sometimes it's tricky to determine whether something is a constraints or the objective function. For example, we want to have a minimum reach to clients, and want to minimize costs. We could either fixate a maximum budget as a constraint and maximize reach, or fixate a minimum reach and minimize budget. It's important to think really thoroughly which ones really require a hard limit.

In my experience, decision variables can be difficult to define, depending on the type of problem that is being solved. Sometimes what we think expresses what we want to decide, or values we want to determine a good value for, are defined in a way that doesn't allow an easy way to model the constraints or the objective function. Therefore it's important to be willing to go back to re-define them if we are having trouble defining either constraints or the objective function. Another thing to take into account is that there will be times where we will need to use "auxiliary" or "dummy' variables to make the model work. This means, not we may need to define and use variables that aren't necessarily things we were aiming to decide.

Leverage Excel Solver for linear optimization across various fields and applications by defining objective functions, constraints, and decision variables to efficiently solve complex optimization problems with ease

La optimización lineal siempre comienza con la identificación de la función objetivo (qué queremos maximizar o minimizar) y las restricciones. Mi experiencia en proyectos logísticos y en la ense?anza de metodologías ágiles me ha permitido aplicar este enfoque para encontrar soluciones eficientes a problemas complejos, como la distribución de productos o el control de inventarios.

First, I define the objective function—what needs to be maximized or minimized, such as profit, cost, or efficiency. Then, I list the decision variables, which are the inputs we can adjust. Lastly, I set the constraints, which are the limitations or rules the solution must follow. By structuring the data in Excel, setting up Solver, and running it, we can quickly generate actionable insights tailored to different fields. It’s a versatile tool that bridges analytics with real-world decision-making!

Solver procede a calcular la mejor solución posible dentro de los límites establecidos. En mi experiencia con la gestión de operaciones y la docencia en la cadena de suministros, este es el momento donde se obtienen soluciones prácticas que tienen un impacto directo en la eficiencia de la operación.

The sensitivity report is key output and can be used for further analysis. The reduced costs and shadow prices for LP can be useful for understanding sensitivity of each variable and how binding the constraints are.

Excel has limitations on the number of variables and constraints. Today there are many open solvers in Python, like PuLP, which are better than Excel and can handle much more variables and constraints. With chatGPT and Github Pilot, coding will become more accessible to lot more people and the learning curve will be steeper.