Optimization Tutorial for Solver Users

In addition to this tutorial, you can visit our Resources To Help You Succeed page to access videos, user manuals, textbooks, and online courses you may find helpful.

Optimization Tutorial Overview

Welcome to our tutorial about Solvers for Excel and Visual Basic -- the easiest way to solve optimization problems -- from Frontline Systems, developers of the Solvers in Microsoft Excel. The product mix example we will be using is a linear programming example you can solve with the built-in Excel Solver alone -- but if you need to solve problems with more than 200 variables, consider our Excel Solver upgrades

• What are Solvers Good For?
• Introduction to Optimization Models
• Step by Step Examples
• What Kind of Solution Can I Expect?
• What Makes a Model Hard to Solve?

After completing this tutorial, you can learn even more about topics such as linearity versus nonlinearity and sparsity in optimization models by taking our Advanced Tutorial. If you'd like to learn more about simulation as well as optimization, consult our tutorials on risk analysis and Monte Carlo simulation.

What are Solvers Good For?

Solvers, or optimizers, are software tools that help users determine the best way to do something. The "something" might involve allocating money to investments, or locating new warehouse facilities, or scheduling hospital operating rooms. In each case, multiple decisions need to be made in the best possible way while simultaneously satisfying a number of logical conditions (or constraints). The "best" or optimal solution may mean maximizing profits, minimizing costs, or achieving the best possible quality.  Here are some representative examples of optimization problems:

Finance and Investment

• Working capital management involves allocating cash to different purposes (accounts receivable, inventory, etc.) across multiple time periods, to maximize interest earnings.
• Capital budgeting involves allocating funds to projects that initially consume cash but later generate cash, to maximize a firm's return on capital.
• Portfolio optimization -- creating "efficient portfolios" -- involves allocating funds to stocks or bonds to maximize return for a given level of risk, or to minimize risk for a target rate of return.

Manufacturing

• Job shop scheduling involves allocating time for work orders on different types of production equipment, to minimize delivery time or maximize equipment utilization.
• Blending (of petroleum products, ores, animal feed, etc.) involves allocating and combining raw materials of different types and grades, to meet demand while minimizing costs.
• Cutting stock (for lumber, paper, etc.) involves allocating space on large sheets or timbers to be cut into smaller pieces, to meet demand while minimizing waste.

Distribution and Networks

• Routing (of goods, natural gas, electricity, digital data, etc.) involves allocating something to different paths through which it can move to various destinations, to minimize costs or maximize throughput.
• Loading (of trucks, rail cars, etc.) involves allocating space in vehicles to items of different sizes so as to minimize wasted or unused space.
• Scheduling of everything from workers to vehicles and meeting rooms involves allocating capacity to various tasks in order to meet demand while minimizing overall costs.

Next: Introduction to Optimization Models >