Optimization modelling in Excel

Microsoft have announced that they're combining our two favourite tools: Python will be available within Excel:

Now you can do advanced data analysis in the familiar Excel environment by accessing Python directly from the Excel ribbon. No set up or installation is required. Using Excel's built-in connectors and Power Query, you can easily bring external data into Python in Excel workflows.

Microsoft. "Introducing Python in Excel: The Best of Both Worlds for Data Analysis and Visualization"

You'll be able to use hundreds of Python libraries from within Excel. That includes SciPy and Networkx. Hopefully, libraries like Pyomo and OR-Tools will become available too.

The feature is being rolled out in Microsoft 365 progressively, so look out for it becoming available in your version of Excel soon.

For more information: Introducing Python in Excel: The Best of Both Worlds for Data Analysis and Visualization

• Excel
• Python

Excel Solver is a free add-in that's available natively within Excel.

Microsoft provides an overview of defining and solving an optimization problem using the Solver add-in:

Solver is a Microsoft Excel add-in program you can use for what-if analysis. Use Solver to find an optimal (maximum or minimum) value for a formula in one cell – called the objective cell – subject to constraints, or limits, on the values of other formula cells on a worksheet.

Solver works with a group of cells, called decision variables or simply variable cells that are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.

Microsoft. "Define and solve a problem by using Solver"

For more information: Define and solve a problem by using Solver

• Excel
• Solver

Frontline Systems make Excel's Solver add-in, along with a variety of other data analysis tools.

Their website's learning section includes a comprehensive list of more than 100 example models that you can run with Excel Solver.

The example models are organized into the following categories:

• Corporate finance: Working capital management, capital budgeting, inventory management, cash management, and capacity planning.
• Investment: Portfolio optimization (Markowitz model), stock portfolio management, portfolio optimization (Sharpe CAPM), bond portfolio management, and bond portfolio exact matching.
• Production: Product mix, machine allocation, blending, process selection, and cutting stock.
• Distribution: Transportation, multi-level multi-commodity transportation, partial loading, facility location, and production/transportation.
• Purchasing: Contract awards, inventory stocking/reordering, media planning, and purchasing/transportation.
• Human resources: Crew scheduling, office assignment, employee scheduling, workforce composition, and workforce movement.

For more information: Examples of optimization problems

• Excel
• Solver

OpenSolver is a free add-in that's a powerful alternative to Excel's Solver add-in.

Overview of OpenSolver:

OpenSolver is an Excel VBA add-in that extends Excel's built-in Solver with more powerful solvers.

OpenSolver provides the following features:

• OpenSolver offers a range of solvers for use in Excel, including the excellent, Open Source, COIN-OR CBC optimization engine which can quickly solve large Linear and Integer problems.
• Compatible with your existing Solver models, so there is no need to change your spreadsheets.
• No artificial limits on the size of problem you can solve – have as many variables and constraints as your computer memory allows (but be aware that large problems can be slow to solve).
• OpenSolver is free, open source software.

OpenSolver.org. "About OpenSolver"

For more information: OpenSolver

• Excel
• OpenSolver