Optimization with Excel: Operations Research without Coding

Learn the basics of Excel, from creating a blank workbook and navigating cells and sheets to applying number formats, colors, font styles, alignment, and zoom to support operations research workflows.

Learn to use Excel to perform optimization tasks with sum, if, sumif and sumifs on company profit data, including building conditional sums, multi-criteria filters, and basic logical tests.

Explore how the sumproduct function in Excel enables optimization by summing the products of two ranges, such as month and profit, and verify results by comparing with manual multiplication.

Discover how to create a sumproduct with filters in Excel to support optimization problems, applying filters by company and using range and true range references to compute a filtered total.

Discover how to use Vlookup to fetch a company name by matching a key and returning a value from the selected column, choosing exact or approximate matches.

Learn to replicate and lock formulas in Excel by using dollar signs to fix ranges while dragging, understanding how to lock rows and columns with the F key.

Understand the limitations of Excel's standard solver, which supports at most 200 variables, and learn to use the free open silver framework as an alternative solver for optimization problems.

Explore the basics of mathematical modeling for optimization, translating real world problems into variables, parameters, constraints, and an objective function to minimize costs or maximize outcomes.

Understand the problem, translate it into a mathematical model, and verify results; choose a framework with a solver to tackle optimization problems without coding.

Define optimization variables and parameters, with indices and sets, and explain variable types—continuous, integer, and binary—in an Excel context for production cost minimization with known demand.

Define the objective function and the constraints to set your goal and rules. Maximize the objective within the feasible set or solution domain shown by the blue region.

Learn how to model optimization problems by translating real problems into mathematical form, using examples to identify objectives and constraints, and compare your solutions with the provided resolutions.

Optimizes Mike's investment allocation across funds A, B, and C to maximize total return while respecting risk constraints and investing all 100000.

Model a nonlinear investment problem with returns for funds A, B, C, and D, including a quadratic fund term and constraints on total capital and fund shares to maximize return.

Model a three-machine production problem to minimize total cost by assigning integer products P_A, P_B, P_C to meet a demand of 10000, using a balance constraint and machine-on costs.

Develop a generic routing formulation using binary arc variables to minimize distance from start to end, with omega sets and flow constraints ensuring start and end arcs, conservation at nodes.

Maximize revenue by selecting a subset of five construction projects under a five-team limit using binary decision variables. The example shows choosing projects B and C under this constraint.

Form a simple linear model for conditional selections: make construction c feasible only if selected, and d feasible only if a, b, c are selected; start with basic constraints.

Formulate a three-day job scheduling problem with binary variables x_jd to decide whether to perform a job on day d, maximizing total profit under daily six-hour capacity and one-day-per-job constraints.

Apply a constraint that each day has at most one job and each job is assigned to exactly one day.

Begin with basics to master simple models, then address complex problems. Study two recommended books and select articles, and understand every variable, constraint, and index before solving with a computer.

Explore linear programming basics with two variables, defining a linear objective and linear constraints within a feasible domain. Solve these problems with a linear solver or Excel, without coding.

Learn to install OpenSolver for Excel, download linear and nonlinear versions, enable macros, and set up solvers like CBC and Coin to tackle linear and nonlinear optimization problems.

Learn to troubleshoot open solver issues in Excel with an alternative approach: open Excel, use the data menu and add-ins, then browse to the open solver file from the zip.

Explore a base case of linear programming in Excel using OpenSolver. Define variables X and Y, build the objective function, constraints, and non-negativity, then solve and interpret results.

Formulate a power generation LP in Excel to minimize total cost by setting generation levels for five generators under demand balance and capacity constraints.

Expanding to a two-hour, multiperiod power-generation problem, the lecture demonstrates how to minimize total generation cost across hours while meeting demand and respecting bounds.

Install and configure Gurobi for optimization workflows, including obtaining an academic license, downloading and installing the software, restarting, and setting the GRB_LICENSE_FILE environment variable.

Learn to choose the right solver for your optimization problem, from CBC for linear models to nonlinear options. Explore open solvers and cloud solving with the new server.

Learn how to model equity optimization in Excel with formulas like sum, if, max, and min, and apply binary variables to avoid nonlinear if constructs.

Understand how milp handles integer and binary variables within linear programming and how solving by splitting into subproblems, like x ≤ 4 or x ≥ 5, reveals feasible, cost-effective solutions.

Apply the base case MILP by converting a linear program to mixed integer with X as integer, defining constraints, and solving using open solver and CBC.

Solve a milp job scheduling problem using binary indexing across three days to maximize profit under daily work hour limits, with six jobs and duration constraints.

Learn to model and solve a routing problem in Excel with a mixed-integer linear program, using binary edge variables and distance parameters to create a path from A to B.

Learn to solve a milp routing problem in excel by defining decision variables, distances, and indices; use sumifs and the cbc solver to find the shortest path.

Learn to model a large routing milp in excel using open solver, converting distance data into arcs with x variables, and minimize the path from point a to point b.

Learn to tune solver parameters in Excel to balance speed and solution quality. Set time limits, tolerances, and iterations, and apply CBC options with a two-column parameter setup.

Speed up constructing large models with open solver by using quick solve, selecting changeable parameters, initializing quick solve, and reusing the problem structure for rapid testing.

Learn to view the solver progress and verify feasibility by enabling progress display, comparing CBC and Gurobi outputs, and adjusting time limits and relative gap.

Use a prepared template in Excel to build optimization models, adding variables, indices, parameters, and constraints. Download the template and learn how OpenSolver connects to Excel formulas for large problems.

Manipulate template variables in Excel by adding, deleting, or renaming indices and creating new variables with copy-paste and right-click insertion.

Clarify how parameters, as known inputs, differ from variables in optimization, and show how to create, name, and manage a parameter view in Excel.

Learn to build a multi-term objective function by placing terms in separate cells and summing them in the last cell.

Learn to work with constraints in an optimization template: define right-hand side constraints, manage indices, copy constraints across columns, and model equations like x times y.

optimize job scheduling with excel by formulating a one day assignment problem for 100 jobs over 10 days, maximizing revenue while respecting eight-hour daily limits and travel time.

Solve a 100-job, 10-day scheduling problem by building variables X and day indices in an Excel template, with parameters, durations, and input data.

Create an Excel-based objective function for job scheduling with 100 jobs over 10 days, using X and revenue R, and impose day and total X constraints.

Create a binary integer programming model in Excel to maximize revenue by selecting 100 jobs across 10 days, verify constraints, and obtain the optimal solution with the CBC solver.

Explore how nonlinear terms like x times y yield nonlinear problems, how solvers differ, and how nonlinear functions create local and global optima versus linear problem edges.

Build a nonlinear programming base case in Excel by defining x and y, maximizing x plus x y under three constraints, and solving with nonlinear solvers like ipopt or bonmin.

Solve a mathematical model for a horse-trade investment in Excel, maximizing total return across four funds: low, medium, high risk, and special fund, with a 100,000 cap using the solver.

Explore mixed-integer nonlinear programming (MINLP), its complexity, and iterative solver strategies that alternately solve integer and nonlinear parts, with a preview of genetic algorithms.

Transform a nonlinear problem by adding an integer constraint on X, solve with bonmin or couenne, and compare continuous versus integer solutions to obtain the optimal X.

Explore a mixed-integer linear programming model to minimize total production cost across three machines, using binary on/off variables and integer production quantities to meet a 10000 unit demand.

Solve a MINLP problem to minimize production cost across three machines, using big M constraints, integer variables, and a nonlinear-cost objective.

Explore genetic algorithms and evolutionary methods for optimization in Excel without coding. Understand fitness functions, chromosomes, genes, initial population, mutation, crossover, and generations that drive search for the best solution.

Solve the base case with a genetic algorithm in Excel using a VBA template to model variables, objective, and inequality and quality constraints, GA parameters like population size and mutation.

Demonstrate a production cost optimization using a genetic algorithm, with three machines, binary beta variables, and a 10,000 unit demand, highlighting moves to improve feasibility and cost.