Yes, You Can Add Optimization to Your Large Complex Excel Spreadsheets

Sunday, April 14, 2019

Analytics Conference Workshop

Presented by Linus Schrage

Ease-of-use and a powerful library of functions have helped make Excel the most widely used tool for building planning models. Learn how to get globally optimal solutions to your planning models using convenient Excel functions such as IF, VLOOKUP, SUMIF, MAX, ABS, MATCH, INDEX; How to handle IFERROR, and more. Using examples from refineries, supply chains, sourcing, routing, and more, we show you how to turn your large, complex What-If spreadsheet models into optimization models, using What'sBest! and LINGO from LINDO Systems.

We cover the various model types such as blending, financial portfolios, multi-period planning, cutting stock, etc. and suggest approaches that have worked well for our customers over the years. For modeling financial portfolios, there are special functions for computing the variance of a portfolio, as well as for estimating the covariance matrix from raw data. For modeling oil refineries, there are special functions for modeling heat exchangers and , for modeling the behavior of gases, e.g., “steam tables”, and modeling the nonlinear behavior of blends of hydrocarbons. For modeling combinatorial problems, support for cardinality constraints, and AllDiff functions, popular in constraint programming, is provided.