1 / 27

Decision Support System for Linear Programming and Applications

This review explores basic LP models and their applications in various business sectors, including product mix production, transportation, marketing, assignment, and labor planning. The use of spreadsheets and Excel's Solver to solve linear programs is also discussed.

inaquin
Download Presentation

Decision Support System for Linear Programming and Applications

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. MIS 463: Decision Support Systems for Business Review of Linear Programming and Applications Aslı Sencer

  2. Basic LP Models: Product Mix Production System for tables and chairs. BIS 517-Aslı Sencer

  3. Formulating a Linear Problem • Define variables: : number of tables produced in a period : number of chairs produced in a period • Define constraints: • Define Objective Function BIS 517-Aslı Sencer

  4. Basic LP Models: Feed Mix • Two types of seeds are mixed to formulate the wheat of wild birdseed. BIS 517-Aslı Sencer

  5. LP Formulation BIS 517-Aslı Sencer

  6. Applications of LP:Transportation Models • Sporting goods company Warehouses Demand Capacity Plants Frankfurt 100 300 200 Juarez 150 100 200 150 NY Seoul Phoenx Tel Aviv Yokohama BIS 517-Aslı Sencer

  7. LP:Transportation Models (cont’d.) • What are the optimal shipping quantities from the • plants to the warehouses, if the demand has to be met • by limited capacities while the shipping cost is minimized? Shipping Costs per pair of skis BIS 517-Aslı Sencer

  8. LP:Transportation Models (cont’d.) Xij: Number of units shipped from plant i to warehouse j. i=1,2,3 and j=1,2,3,4. Minimize shipping costs=19X11+7X12+3X13+21X14 +19X21+7X22+3X23+21X24 +11X31+14X32+15X33+22X34 BIS 517-Aslı Sencer

  9. LP:Transportation Models (cont’d.) subject to #shipped from a plant can not exceed the capacity: X11+X12+X13+X14≤100 (Juarez Plant) X21+X22+X23+X24≤300 (Seoul Plant) X31+X32+X33+X34≤200 (Tel Aviv Plant) #shipped to a warehouse can not be less than the demand: X11+X21+X31+X41≥150 (Frankfurt) X12+X22+X32+X42≥100 (NY) X13+X23+X33+X43≥200 (Phoenix) X14+X24+X34+X44 ≥150 (Yokohama) Nonnegativity Xij ≥0 for all i,j. BIS 517-Aslı Sencer

  10. LP:Transportation Models (cont’d.) Optimal Solution: Optimal cost=$6,250 Warehouses Demand Capacity Plants Frankfurt 100 300 200 Juarez 150 100 200 150 50 100 NY Seoul 100 100 Phoenx 100 Tel Aviv Yokohama 150 BIS 517-Aslı Sencer

  11. LP: Marketing Applications • How to allocate advertising budget between mediums such as TV, radio, billboard or magazines? Ex: Real Reels Co. Allocated ad. Budget=$100,000 • No more than 5 ads in True and at least two ads in Playboy and Esquire BIS 517-Aslı Sencer

  12. LP: Marketing Applications (cont’d.) BIS 517-Aslı Sencer

  13. LP: Assignment Models • Assignment of a set of workers to a set of jobs BIS 517-Aslı Sencer

  14. LP: Assignment Models (cont’d.) BIS 517-Aslı Sencer

  15. LP:Labor Planning • Addresses staffing needs over a specific time period. Hong Kong Bank of Commerce: • 12 Full time workers available, but may fire some. • Use part time workers who has to work for 4 consequtive hours in a day. • Luch time is one hour between 11a.m. and 1p.m. shared by full time workers. • Total part time hours is less than 50% of the day’s total requirement. • Part-timers earn $4/hr (=$16/day) and full timers earn $50/day. BIS 517-Aslı Sencer

  16. LP:Labor Planning (Cont’d.) BIS 517-Aslı Sencer

  17. LP:Labor Planning (cont’d.) Alternative Optimal Solution F=10, P2=2, P3=7, P4=5 F=10, P1=6, P2=1, P3=2, P4=5 at a cost of $724/day BIS 517-Aslı Sencer

  18. Solving Linear Programs with a Spreadsheet • Write out the formulation table • Put the formulation table into a spreadsheet • Use Excel’s Solver to obtain a solution BIS 517-Aslı Sencer

  19. Step 1: The Formulation Table The formulation table arranges the problem in a tabular format, as shown below for the Microcircuit Production Plan.

  20. Step 2: The Excel Spreadsheet The numbers in the Excel spreadsheet come from the formulation table.

  21. Step 3: Expanded Spreadsheet The expanded spreadsheet contains the formulas necessary to use Solver. Put =SUMPRODUCT(B4:F4,$B$15:$F$15) in cell J4 and copy it down to cell J12. Cell J4 gives the value of the objective function. The solution is found here (the values of the decision variables). BIS 517-Aslı Sencer

  22. Using Excel’s Solver to Solve Linear Programs Click on Tools on the menu bar, select the Solver option, and the Solver Parameters dialog box shown next appears. BIS 517-Aslı Sencer

  23. 1. Enter the value of the objective function, J4, in the Target Cell line, either with or without the $ sign. Solver Parameters Dialog Box NOTE: Normally all these entries appear in the Solver Parameter dialog box so you only need to click on the Solve button. However, you should always check to make sure the entries are correct for the problem you are solving. 2. The Target Cell is to be maximized so click on Max in the Equal To line. 3. Enter the decision variables in the By Changing Cells line, B15:F15. 4. The constraints are entered in the Subject to Constraints box by using the Add Constraints dialog box shown next (obtained by clicking on the Add button). If a constraint needs to be changed, click on the Change button. The Change and Add Constraint dialog box function in the same manner. BIS 517-Aslı Sencer

  24. The Add Constraint Dialog Box To represent the constraints in rows 5 - 8: 1. Enter J5:J8 (or $J$5:$J$8) in the Cell Reference line. This is the total amount of these resources used. Normally, all these entries already appear. You will need to use this dialog box only if you need to add a constraint. 3. Enter the amounts of the resources available H5:H8 in the Constraint line (or =$H$5:$H$8). 4. Click Add and repeat Steps 1 - 3 if another constraint is to be added. If this is the last constraint, click OK. 2. Enter <= as the sign because the resources used must be equal to or less than the amounts available, given next in Step 3. If another sign is needed, see the next slide. BIS 517-Aslı Sencer If you need to change a constraint, the Change Constraint dialog box functions just like this one.

  25. The Solver Options Dialog Box Click on the Options button in the Solver Parameters dialog box to check the Solver Options dialog box to ensure that the Assume Linear Model and Assume Non-Negative boxes are checked. BIS 517-Aslı Sencer

  26. Solver Results Dialog Box(Figure 9-9) Be sure to check the message in the Solver Results dialog box. In this case it indicates that a solution has been found. What happens when Solver does not find a solution will be discussed latter. Click OK and the spreadsheet with the solution, shown next, is obtained. BIS 517-Aslı Sencer

  27. Spreadsheet with Optimal Solution 1. To solve other problems: 2. Enter the data: the coefficients of the objective function in cells B4:F4, the right-hand sides in cells H5:H12, and the exchange coefficients in cells B5:F12. 3. To find the solution, click on Tools and Solver to obtain the Solver Parameters dialog box and then click the Solve button. 4. For bigger problems insert additional rows or columns. Insert them in the middle of the table and not at the beginning or the end. Copy the formulas in column J to any new cells created by inserting rows. Check to make sure the ranges of the formulas and signs in the Solver Parameters dialog box are correct. BIS 517-Aslı Sencer

More Related