Optimization Tutorial: Step-by-Step Guide for Solver Tools

Welcome to our tutorial about Solvers for Excel and Visual Basic -- the easiest way to solve optimization problems -- from Frontline Systems, developers of the Solver in Microsoft Excel.

This tutorial addresses the following questions:

After completing this tutorial, you can learn even more about topics such as linearity versus nonlinearity and sparsity in optimization models by completing our Advanced Tutorial.

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 might 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 deciding how much cash to allocate to different purposes (accounts receivable, inventory, etc.) across multiple time periods, to maximize interest earnings.

Capital budgeting involves deciding how much money to invest in projects that initially consume cash but later generate cash, to maximize a firm's return on capital.

Portfolio optimization -- creating "efficient portfolios" -- involves deciding how much money to invest in 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 deciding how to assign work orders to different types of production equipment, to minimize delivery time or maximize equipment utilization.

Blending (of petroleum products, ores, animal feed, etc.) involves deciding how to combine raw materials of different types and grades, to meet demand while minimizing costs.

Cutting stock (for lumber, paper, etc.) involves deciding how to cut large sheets or timbers into smaller pieces, to meet demand while minimizing waste.

Distribution and Networks

Routing (of goods, natural gas, electricity, digital data, etc.) involves deciding which paths items should move through to arrive at various destinations, to minimize costs or maximize throughput.

Loading (of trucks, rail cars, etc.) involves deciding how items of different sizes should be placed in vehicles so as to minimize wasted or unused space.

Scheduling of everything from workers to vehicles and meeting rooms involves deciding how resources should be allocate to various tasks in order to meet demand while minimizing overall costs.