Optimization with Excel: Operations Research without Coding

Course Curriculum

Chapter 1: Introduction

Lecture 1: Introduction

Lecture 2: How to solve Optimization Problems and Limitations of using Excel

Lecture 3: Preview of the course

Chapter 2: Introduction to Excel

Lecture 1: Excel – the basics

Lecture 2: Sum, If, SumIf, SumIfs

Lecture 3: SumProduct

Lecture 4: SumProduct with Filters

Lecture 5: Vlookup

Lecture 6: Replicate and Lock Formulas

Lecture 7: Limitations of the standard solver from Excel (we will not use this solver!)

Chapter 3: Introduction to Mathematical Modeling

Lecture 1: What is mathematical modeling?

Lecture 2: How we solve optimization problems?

Lecture 3: Type of variables and what is parameters, indexes and sets

Lecture 4: Objective function and constraints

Lecture 5: How to model?

Lecture 6: Example 1 – Investment Problem

Lecture 7: Example 2 – Investment Problem, nonlinear

Lecture 8: Example 3 – Cost of production

Lecture 9: Example 4 – Routing problem

Lecture 10: Example 5 – Team assignment in a construction company

Lecture 11: Example 6 – Team assignment with condition

Lecture 12: Example 7 – Job scheduling

Lecture 13: Example 8 – Job scheduling with limit

Lecture 14: References for VRPTW, Jobshop, and TSP

Lecture 15: How to learn more

Chapter 4: Linear Programming (LP) and installation of what you need

Lecture 1: LP – Introduction

Lecture 2: Installing OpenSolver

Lecture 3: Issues with OpenSolver

Lecture 4: LP – Example 1 – Base Case

Lecture 5: LP – Example 2 – Power generation

Lecture 6: LP – Example 3 – Power generation multiperiod

Lecture 7: Installing Gurobi

Lecture 8: Academic License for Gurobi [Updates]

Lecture 9: Selecting different solvers

Lecture 10: Formulas and limits for Excel

Lecture 11: LP – Concepts

Chapter 5: Mixed-Integer Linear Programming (MILP)

Lecture 1: MILP – Introduction

Lecture 2: MILP – Example 1 – Base Case

Lecture 3: MILP – Example 2 – Job Scheduling

Lecture 4: MILP – Example 3 – Routing Problem

Lecture 5: MILP – Example 3 – Routing Problem – Solution

Lecture 6: MILP – Example 4 – Large Routing Problem

Lecture 7: MILP – Concepts

Chapter 6: Solver Parameters and Tips

Lecture 1: Defining parameters for the solver

Lecture 2: How to speed up the construction of problem?

Lecture 3: See the progress of the solver

Chapter 7: Template

Lecture 1: The template [download]

Lecture 2: Template – Working with variables

Lecture 3: Template – Working with parameters

Lecture 4: Template – Working with the objective function

Lecture 5: Template – Working with constraints

Lecture 6: Example – Job scheduling – 100 jobs in 10 days

Lecture 7: Example – Job scheduling – 100 jobs in 10 days – Variables and parameters

Lecture 8: Example – Job scheduling – 100 jobs in 10 days – Objective function

Lecture 9: Example – Job scheduling – 100 jobs in 10 days – Constraints

Lecture 10: Example – Job scheduling – 100 jobs in 10 days – Model and Solution

Chapter 8: NonLinear Programming (NLP)

Lecture 1: NLP – Introduction

Lecture 2: NLP – Example 1 – Base Case

Lecture 3: NLP – Example 2 – Cosines

Lecture 4: NLP – Example 3 – Investment

Lecture 5: NLP – Concepts

Chapter 9: Mixed-Integer NonLinear Programing (MINLP)

Lecture 1: MINLP – Introduction

Lecture 2: MINLP – Example 1 – Base Case

Lecture 3: MINLP – Example 2 – Production Cost

Lecture 4: MINLP – Example 2 – Production Cost – Solution

Chapter 10: Genetic Algorithm (GA)

Lecture 1: GA – Introduction

Lecture 2: GA – Example 1 – Base Case

Lecture 3: GA – Example 2 – Production Cost

Chapter 11: Vehicle Routing Problem with Time Window (VRPTW)

Lecture 1: VRPTW – Introduction

Lecture 2: VRPTW – Example

Lecture 3: VRPTW – Processing Time Issues

Chapter 12: Practical Problems

Lecture 1: Introduction

Lecture 2: A Revenue Problem – Modeling

Lecture 3: A Revenue Problem – Solution

Lecture 4: A Maintenance Planning Problem – Business Concept

Lecture 5: A Maintenance Planning Problem – Modeling

Lecture 6: A Maintenance Planning Problem – Solution

Lecture 7: Optimal Power Flow – Business Concept

Lecture 8: Optimal Power Flow – Modeling

Lecture 9: Optimal Power Flow – Solution

Chapter 13: Congratulations and Keep Learning

Lecture 1: If you want, where could you learn a programming language to solve optimization?

Lecture 2: Thank you!