1 / 47

An Introduction to Optimization DSS

arion
Télécharger la présentation

An Introduction to Optimization DSS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. An Introduction to Optimization DSS Optimize? 1. to make as effective, perfect, or useful as possible. 2. to make the best of. 3. Math. to determine the maximum or minimum values of (a specified function that is subject to a set of constraints)

    2. DSS Classification based on Alters Taxonomy

    3. Mathematical Programming Optimization A family of optimization tools Linear Programming Goal Programming Transportation model Network model Inventory model Integer programming Dynamic programming

    4. LP is the best known technique among the Optimization Tool Family All other optimization tools are variation of LP. Limited Resource Allocation Tool

    6. The company expects to have 200 pumps, 1,566 hours of labor, and 2,800 feet of tubing available during the next production cycle. The problem is to determine the optimal number of Aqua-Spas and Hydro-Luxes to produce in order to maximize profits.

    8. Implementing an LP Model in a Spreadsheet 1. Organize the data for the model on the spreadsheet. First, the goal is to organize the data so their purpose and meaning are as clear as possible. Descriptive labels should be placed in the spreadsheet to clearly identify the various data elements. 2. Reserve separate cells in the spreadsheet to represent each decision variable in the algebraic model. This is often helpful in setting up formulas for the objective function and constraints. When possible, it is also a good idea to keep the cells representing decision variables in the same area of the spreadsheet.

    9. 3. Create a formula in a cell in the spreadsheet that corresponds to the objective function in the algebraic model. The spreadsheet formula corresponding to the objective function is created by referring to the data cells where the objective function coefficients have been entered (or calculated) and to the corresponding cells representing the decision variables. 4. For each constraint in the algebraic model, create a formula in a cell in the spreadsheet that corresponds to the left-hand-side (LHS) of the constraint. The formula corresponding to the LHS of each constraint is created by referring to the data cells where the coefficients for these constraints have been entered (or calculated) and to the appropriate decision variable cells.

    11. LHS formula for the pump constraint: 1 X1 + 1 X2 Formula in cell D8: =B8*B4+C8*C4 LHS formula for the labor constraint: 9 X1 + 6 X2 Formula in cell D9: =B9*B4+C9*C4 LHS formula for the tubing constraint: 12 X1 + 16 X2 Formula in cell D10: =B10*B4+C10*C4

    14. The previous slide shows the initial spreadsheet model for Blue-ridge hot tub case. The model in the previous slide consists of three different data types. Label Column A, Rows 1,2, and 3 Number-- b8, b9, b10, etc. Formula Cells with #VALUE The most important part of spreadsheet modeling is to learn how to enter the formula.

    17. Representing the Bounds on the Decision Variables How Solver Views the Model 1. Target cell--the cell in the spreadsheet that represents the objective function in the model (and whether its value should be maximized or minimized) 2. Changing cells--the cells in the spreadsheet that represent the decision variables in the model 3. Constraint cells--the cells in the spreadsheet that represent the LHS formulas of the constraints in the model (and any upper and lower bounds that apply to these formulas)

    18. Summary of Solver terminology

    19. Using Solver After implementing an LP model in a spreadsheet, we still need to solve the model. To do this we must first indicate to Solver which cells in the spreadsheet represent the objective function (or target cell), the decision variables (or changing cells), and the constraints (or constraint cells).

    20. Defining the Target Cell In the Solver Parameters dialog box, specify the location of the cell that represents the objective function by entering it in the Set Target Cell box

    21. Defining the Changing Cells If the decision variables were not in a contiguous range, we would have to list the individual decision variable cells separated by commas in the By Changing Cells box. Whenever possible, it is best to use contiguous cells to represent the decision variables.

    22. Defining the Constraint Cells To define the constraint cells, click the Add button in the solver parameters box, then complete the Add Constraint dialog box. In the Add Constraint dialog box, click the Add button again to define additional constraints. Click the OK button when you have finished defining constraints. It is a good idea to keep constraints of a given type grouped in contiguous cells so you can select them at the same time.

    23. Defining the Nonnegativity Conditions To do this, we simply add another set of constraints to the model

    24. Solving the Model Click the Solve button in the Solver Parameters dialog box to solve the problem. When Solver finds the optimal solution, it displays the Solver Results dialog box.

    26. In-class problem: Using Solver to determine optimal product mix You work for a drug company that produces six products. Production of each product requires labor and raw material. Row 4 --- the hours of labor needed to produce a pound of each product. Row 5 --- the pounds of raw material needed to produce a pound of each product. Row 6 --- the price per pound Row 7--- unit cost per pound Row 8 this month demand for each drug

    27. Product Mix Problem

    28. The right formulation is the key! Formulate LP for this problem Objective Function: Max Z=? Constraints Subject to: Non-negativity constraints all variables >=0

    29. Finding solutions Use of SUMPRODUCT formula =D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 is a tedious procedure. =sumproduct(d2:i2, d4:i4) is a time-saving short cut. Find an optimal solution. Create two spreadsheets with/without SUMPRODUCT formula.

    30. Optimization model-based DSS Assignments Given the following problems, Formulate Linear Programming Models and solve them using Excel Solver. Problem #1: A computer manufacturing plant produces mice, keyboards, and video game joysticks. The per-unit profit, the per-unit labor usage, monthly demand, and per-unit machine-time usage are given in the following table. Each month, a total of 13,000 labor hours and 3000 hours of machine time are available. How can the manufacturer maximize its monthly profit contribution from the plant?

    32. Problem #2: Jason the jeweler makes diamond bracelets, necklaces, and earrings. He want to work at most 160 hours per month. He has 800 ounces of diamonds. The profit, labor time, and ounces of diamonds required to produce each product are given below. If demand for each product is unlimited, how can Jason maximize his profit?

    34. OUTPUTS For both problems, you need to turn in a word document of LP formulation and EXCEL file with LP formulation and Output. Save your files as yourlastnameDSSA.xls and yourlastnameDSSA.doc.

    35. Using solver for Transportation Prob. Transportation problems are a special case of LP applications.

    36. The Navy has 9,000 pounds of material in Albany, Georgia which it wishes to ship to three installations: San Diego, Norfolk, and Pensacola. They require 4,000, 2,500, and 2,500 pounds, respectively. Government regulations require equal distribution of shipping among the three carriers. The shipping costs per pound for truck, railroad, and airplane transit are shown on the next slide. Formulate and solve a linear program to determine the shipping arrangements (mode, destination, and quantity) that will minimize the total shipping cost. Problem: U.S. Navy

    37. Data Destination Mode San Diego Norfolk Pensacola Truck $12 $ 6 $ 5 Railroad 20 11 9 Airplane 30 26 28 Problem: U.S. Navy

    38. Define the Decision Variables We want to determine the pounds of material, xij, to be shipped by mode i to destination j. The following table summarizes the decision variables: San Diego Norfolk Pensacola Truck x11 x12 x13 Railroad x21 x22 x23 Airplane x31 x32 x33 Problem: U.S. Navy

    39. Define the Objective Function Minimize the total shipping cost. Min: (shipping cost per pound for each mode per destination pairing) x (number of pounds shipped by mode per destination pairing). Min: 12x11 + 6x12 + 5x13 + 20x21 + 11x22 + 9x23 + 30x31 + 26x32 + 28x33 Problem: U.S. Navy

    40. Define the Constraints Equal use of transportation modes: (1) x11 + x12 + x13 = 3000 (2) x21 + x22 + x23 = 3000 (3) x31 + x32 + x33 = 3000 Destination material requirements: (4) x11 + x21 + x31 = 4000 (5) x12 + x22 + x32 = 2500 (6) x13 + x23 + x33 = 2500 Nonnegativity of variables: xij > 0, i = 1,2,3 and j = 1,2,3 Problem: U.S. Navy

    41. The Management Scientist Output OBJECTIVE FUNCTION VALUE = 142000.000 Variable Value Reduced Cost x11 1000.000 0.000 x12 2000.000 0.000 x13 0.000 1.000 x21 0.000 3.000 x22 500.000 0.000 x23 2500.000 0.000 x31 3000.000 0.000 x32 0.000 2.000 x33 0.000 6.000 Problem: U.S. Navy

    42. Problem: U.S. Navy Solution Summary San Diego will receive 1000 lbs. by truck and 3000 lbs. by airplane. Norfolk will receive 2000 lbs. by truck and 500 lbs. by railroad. Pensacola will receive 2500 lbs. by railroad. The total shipping cost will be $142,000.

    43. In-Class and Homework Assignments Find the solution for the US Navy using the Solver. Homework assignment (Turn in LP formulation and Excel spreadsheet) The following slide shows a data file. A drug company produces a drug in Los Angeles, Atlanta, and New York. The Los Angeles, Atlanta, and NYC can produce up to 10000, 12000, and 14000 pounds of the drug per month respectively. Each month, the company must ship to the four regions of the US-East, Midwest, South, and West- 9000, 6000, 6000, and 13000 respectively. The cost per pound of producing a drug at each plant and shipping cost are also included in the next slide What is the cheapest way to get each region the quantity of the drug they need?

    44. In-class Assignment

    45. Transportation Problem Homework#1 The distance between Boston, Chicago, Dallas, Los Angeles, and Miami are given in the following table. Each city needs 40,000 kilowatt hours (kwh) of powers, and Chicago, Dallas, and Miami are capable of producing 70,000 kwh. Assume that shipping 1000 kwh over 100 miles costs $4.00. From where should power be sent to minimize the cost of meeting each citys demand?

    46. Transportation Problem Homework#2 Suppose that each day, northern, central, and southern CA each use 100 billion gallons of water. Also assume that northern CA and central CA have available 120 billion gallons of water available. The cost of shipping one billion gallons of water between the three regions is as follows:

    47. We will not be able to meet all demand for water, so we assume that each billion gallons of unmet demand incurs the following shortage costs. How should CAs water be distributed to minimize the sum of shipping and shortage costs?

More Related