Optimization Solutions by Functional Area

To see example Excel optimization models in each functional area, choose Resources - Excel Examples - Functional Area in the top menu bar.

 

Corporate Finance

 

  • 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 inventory 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

 

  • 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

 

  • 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

 

  • 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

 

  • 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

 

  • 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