Loading in 2 Seconds...
Loading in 2 Seconds...
Optimization – Part I Applications of Optimization To Operations Management. For this session, the learning objectives are: Learn what a Linear Program is. Learn how to formulate a Linear Program and solve it using Excel’s Solver . Using Solver to solve a Make-or-Buy Problem .
Applications of Optimization
To Operations Management
The next page summarizes the use of Excel’s Solver.
DuPunt, Inc. manufactures three types of chemicals. For the upcoming month, DuPunt has contracted to supply its customers with the following amounts of the three chemicals:
DuPunt’s production is limited by the availability of processing time in two chemical reactors. Each chemical must be processed first in Reactor 1 and then in Reactor 2. The following table provides the hours of processing time available next month for each reactor and the processing time required in each reactor by each chemical:
Because of the limited availability of reactor processing time, DuPunt has insufficient capacity to meet its demand with in-house production. Consequently, DuPunt must purchase some chemicals from vendors having excess capacity and resell them to its own customers. The following table provides each chemical’s in-house production cost and outside purchase cost:
DuPunt’s objective is to fill its customers’ orders with the cheapest combination of in-house production and outside purchases. In short, DuPunt must decide how much of each chemical to produce in-house (i.e., “make”) and how much of each chemical to purchase outside (i.e.,”buy”).
Define the following 6 decision variables:
Minimize Total Costs
72 = Total Supply
Total Demand = 70
Let AZ denote the amount shipped from location A to location Z. As examples, TL denotes the amount shipped from Tokyo to Los Angeles, and SN denotes the amount shipped from Seattle to New York.
Min Total Shipping Costs
The Spreadsheet Before Optimization
The Spreadsheet After Optimization
In many businesses and industrial environments, the goal is to find the optimal “recipe” for blending a variety of “ingredients” to obtain a product that meets lower and/or upper limits on a variety of characteristics. The table below summarizes several applications.
The exercise below is designed to review the “basics” of formulating a linear program and solving it using Solver.