Frontline Systems, Excel Solver, optimization software, Solver Excel, simulation software
Solver.com
From Frontline Systems, developers of the Excel Solver.

Solver tutorials

Learn to use optimization for resource allocation, and Monte Carlo simulation for risk analysis of your model.


 

Examples of Optimization Problems

Can You Show Me Examples Similar to My Problem?

Optimization is a tool with applications across many industries, and many functional areas.  Here are some examples:

Examples by Functional Area

Corporate Finance (Click to see models)

  • Working Capital Management:  Invest in 1-month, 3-month, and 6-month CDs to maximize interest while meeting cash requirements
  • Capital Budgeting:  Choose a combination of capital projects to maximize overall  NPV (Net Present Value)
  • Inventory Management:  Compare investory stocking and reordering policies with the EOQ (Economic Order Quantity) model
  • Cash Management:  Determine where to locate lockboxes to minimize the "float" or interest lost to due mailing delays
  • Capacity Planning:  Determine which plants should be opened or closed

Investments (Click to see models)

  • Portfolio Optimization - Markowitz Model:  Allocate funds to stocks to minimize risk for a target rate of return - with known or computed variances and covariances
  • Stock Portfolio Management:  Uses a VBA macro to optimize several scenarios for minimum risk at different target rates of return, then draws a graph of the efficient frontier
  • Portfolio Optimization - Sharpe Model (CAPM):  Uses Excel's regression functions to calculate alphas and betas for stocks relative to a market index, then uses these to find an efficient portfolio
  • Bond Portfolio Management:  Allocate funds to bonds to maximize return while ensuring that the portfolio duration equals the investment horizon for maturity - with known or computed durations
  • Bond Portfolio Exact Matching:  Allocate funds to bonds to maximize portfolio return while ensuring that periodic liabilities are met - with or without reinvestment

Production (Click to see models)

  • Product Mix:  Determine how many products of each type to assemble from certain parts to maximize profits while not exceeding available parts inventory
  • Machine Allocation:  Allocate production of a product to different machines, with different capacities, startup cost and operating cost, to meet production target at minimum cost
  • Blending:  Determine which raw materials from different sources to blend to produce a substance with certain desired qualities at minimum cost
  • Process Selection - Decide which of several processes (with different speeds, costs, etc.) should be used to make a desired quantity of product in a certain amount of time, at minimum cost
  • Cutting Stock:  Determine how to cut larger pieces of wood, steel, etc. into smaller pieces of desired sizes, each needed in certain quantities, to minimize waste

Distribution (Click to see models)

  • Transportation Model:  Determine how many products to ship from each factory to each warehouse, or from each factory to each warehouse and direct to each end customer, to minimize shipping cost while meeting warehouse demands and not exceeding factory supplies
  • Multi-Level, Multi-Commodity Transportation Model:  Determine how many products of several different types to ship from each factory to each warehouse and each customer, to minimize total shipping cost while meeting demands and not exceeding capacities and supplies
  • Partial Loading - Decide which sizes or types of products to load into a vehicle, given its size limits, to best meet demand or to minimize wasted space
  • Facility Location:  Determine which (if any) plants to close to minimize total costs, including fixed operating costs and shipping costs between facilities
  • Production / Transportation Model:  Determine how many products to produce in each factory and ship to warehouses and customers, to minimize overall costs while meeting demands, warehouse capacities and factory supplies

Purchasing (Click to see models)

  • Contract Awards - Award contracts to suppliers who have bid certain prices to supply products to facilities in several states - allow for bids specifying a minimum size for each state
  • Inventory Stocking/Reordering:  Compare inventory stocking and reordering policies with the EOQ (Economic Order Quantity) model
  • Media Planning - Decide how much advertising to purchase in different media to minimize total cost while achieving a target level of reach or frequency
  • Purchasing / Transportation Model:  Determine how much to purchase from different suppliers at specified prices, to be shipped from their locations to various plants, to minimize total costs including purchase and shipping costs

Human Resources (Click to see models)

  • Crew Scheduling:  Assign crews to different airline flight segments to minimize total cost while ensuring that a crew "rotation" begins and ends in the same city
  • Office Assignment:  Assign employees to available offices to maximize satisfaction of employee preferences
  • Employee Scheduling:  Schedule park employees for weekly "shifts" (five works days plus two consecutive days off) to minimize payroll costs while meeting varying demand for each day of the week, optionally taking into account employee seniority and preferences
  • Workforce Composition: Decide how many employees to retrain, hire and fire to meet changing workforce composition requirements while minimizing costs or employee turnover
  • Workforce Movement:  Decide how many troops to move from several camps to several other bases, to minimize movement time or total cost

Examples by Industry

Airlines and Trucking

  • Crew Scheduling:  Given a flight schedule, aircraft assignments, and restrictions on duty periods, allocate crews most effectively to flights
  • Fleet Routing and Assignment:  Determine which aircraft to fly on each route, and the sequence of segments flown by each aircraft
  • Revenue Management:  For different classes of tickets, determine how many seats to sell or hold back as flight date approaches

Oil and Gas

  • Gasoline Blending: From hydrocarbons with specific octane ratings, vapor pressure, volatility and cost, determine how much of each should be blended together to produce regular, midgrade, and premium gasoline
  • Gas Contract Purchase:  With forecasted but uncertain demand for gas, determine which contracts to buy, and how much gas to store at different times
  • Pipeline Capacity Auction:  Determine which bids, at different prices, should be awarded to maximize sales revenue while not exceeding daily pipeline capacity

Lumber, Paper and Steel

  • Cutting Stock Problems:  Given large wood / paper sheets or steel slabs / bars, and demand for units of smaller lengths/widths, determine the cutting pattern of large into small pieces that meets demand while minimizing waste

Agriculture

  • Crop Planning:  Given forecasted crop prices and growing conditions, determine how much of each crop to plant
  • Feed Blending:  Given the nutritional requirements for feed animals and the price of available feeds, find the blend of feed ingredients that will minimize total cost

Electric Power

  • Generator Commitment:  Given forecasted demand by period and operating cost for each generator, determine which generators should be run in each time interval
  • Electricity Trading:  Maximize the value of electricity sales in an ongoing auction environment

Financial Services

  • Efficient Portfolios:  Given forecasts of stock, bond or asset class returns, variances and covariances, allocate funds to investments to minimize portfolio risk for a given rate of return
  • Index Fund Management:  Solve a portfolio optimization problem that minimizes "tracking error" for a fund mirroring an index composed of thousands of securities
  • Asset/Liability Management:  Allocate funds to various investments to maximize portfolio return while ensuring that periodic liabilities are fully funded

Back to Reduce Costs and Control Risks with Excel and Our Solvers

Back to Optimization Software Overview