OM 300 1nd Edition Lecture 16 Outline of Last Lecture I Supply Risks II Managing the Integrate Supply Chain III Building the Supply Base IV Shipping Systems V Ethics Outline of Current Lecture I Elements of Optimization II Feasibility III Excel Solver Current Lecture Optimization A constrained optimization problem is a mathematical model that maximizes or minimizes some quantity while satisfying a set of constraints Major elements of an optimization problem o Decision variables o Objective function o Constraints Elements Decision Variables o Values that must be chosen in order to define a solution to the problem o Influence the objective function in some way o Consume or supply some resource Objective Function o Value of a solution o Measures relative quality of different solutions o Benchmark Constraints o Inequalities or equalities o Associated with some limited resource o Determine whether a specific solution is feasible o Right Hand side defines the amount of resources available or needed to satisfy the constraint These notes represent a detailed interpretation of the professor s lecture GradeBuddy is best used as a supplement to your own notes not as a substitute Example Sidneyville Desk Mfg Product Mix Problem Produces two types of desk Using three types of wood in every desk measured in board feet b f Decision problem o How many of each type of desk should Sidneyville make in order to maximize profit using only the wood that is currently available Decision Variables o How many desks Objective function o Profit Constraints o Inventory Decision variables o X1 number of rolltop desks to produce o X2 number of regular desks to produce Objective function o Total profit 115x1 90x Example Constraints Limited wood available or equal to o Amount used is less than pr equal to the amount available o Pine 10x1 20x2 200 o Cedar 4x1 16x2 128 o Maple 15x1 10x2 220 Nonnegative production o X1 0 x2 0 Linear Program Refer to powerpoint slide 10 Feasibility A feasible solution is a set of decision variable values that satisfy all of the constraints The feasible region is the set of all feasible solutions Extreme points are the corners of the feasible region Optimality An optimal solution is a feasible solution that achieves the best possible objective function value within the feasible region o No other feasible solution has a better objective value o There may be multiple optimal solutions In an LP at least one of the extreme points is an optimal solution o Graphical method o Simplex method Excel Solver Computerized Method Simplex Method invented by George Dantzig in 1947 We will use o Excel spreadsheet with Solver add in Other more robust computer software is available to solve large LPs o Spreadsheet add ins o Stand alone optimization modeling and solution programs OPL and CPLEX ILOG Gray Boxes C6 C5 D6 D5 C10 C5 D10 D5 Using Excel Solver If you are using 2007or 2010 Go to Data and then Solver if not in your system Click on Office Button Excel Options Add ins Go check Solver click on OK and Solver will be added to your Data Tool Bar If a MAC you will need to download Solver as an outside program Set Objective objective function Changing Cells decision variables Constraints o Left hand side o Right Hand side Solver Options o Assume Linear o Assume Nonnegative Special Problems Multiple optimal solutions Infeasible problem o Empty feasible region no feasible solution o May have a missing or incorrect constraint Unbounded solution o There is no finite optimal solution o May have incorrect objective function o May have a missing or incorrect constraint Tips for Spreadsheet Optimization Keep it organized and structured Use descriptive labels Use notes where appropriate Pay attention to the Solver options For example Assume Non Negative Design the spreadsheet so that the decision variables are used directly in the objective function target cell Print Screen Formatting Some Useful Excel Functions SUM A1 C1 A1 B1 C1 SUM A1 A3 A1 A2 A3 SUMPRODUCT A1 B2 C3 D4 A1 C3 B1 D3 A2 C4 B2 D4 Transportation Problem Statement Objective minimize total cost of shipping product from each of the sources to each of the destinations Subject to the following requirements o Total amount shipped out of a source outbound cannot exceed its supply o Total amount shipped to a destination inbound must equal its demand requirement o Negative shipments returns are not allowed Refer to Powerpoint for graphs and explanations for this problem