1 / 18

Modeling and Solving LPs by Excel Solver

Modeling and Solving LPs by Excel Solver. Product Mix Example Static Workforce Planning models Blending Problem Aggregate Production Planning Reference: Winston and Albright, Chapters 2 & 3. Product Mix Problem. Monet company makes four types of frames.

prattm
Download Presentation

Modeling and Solving LPs by Excel Solver

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. Modeling and Solving LPs by Excel Solver • Product Mix Example • Static Workforce Planning models • Blending Problem • Aggregate Production Planning • Reference: • Winston and Albright, Chapters 2 & 3

  2. Product Mix Problem • Monet company makes four types of frames. • The following table gives the manufacturing data: • How many frames of each type should be made per week to maximize the profit?

  3. Product Mix Problem (contd.) Linear Programming Formulation: Max z = 6x1 + 2x2+ 4x3 + 3x4 subject to 6x1 + 2x2+ 4x3 + 3x4 £ 4000 (labor) 4x1 + 2x2+ x3 + 2x4 £ 6000 (metal) 6x1 + 2x2+ x3 + 2x4 £ 10000 (glass) x1£ 1000 x2£ 2000 x3£ 500 x4£ 1000 x1, x2, x3 , x4³ 0

  4. Product Mix Problem (contd.)

  5. Answer & Sensitivity Reports

  6. Answer & Sensitivity Reports

  7. Static Work Scheduling Problem • Number of full time employees on different days of the week • Each employee must work five consecutive days and then receive two days off • Meet the requirements by minimizing the total number of full time employees

  8. Static Workforce Scheduling LP Formulation: Min. z = x1+ x2 + x3 + x4 + x5 + x6 + x7 subject to x1 + x4 + x5 + x6 + x7³17 x1+ x2 + x5 + x6 + x7³13 x1+ x2 + x3 + x6 + x7 ³15 x1+ x2 + x3 + x4 + x7³19 x1+ x2 + x3 + x4 + x5³14 x2 + x3 + x4 + x5 + x6³16 x3 + x4 + x5 + x6 + x7³11 x1, x2, x3, x4, x5, x6, x7³ 0

  9. Static Workforce Scheduling (contd.)

  10. Static Workforce Scheduling (contd.)

  11. Static Workforce Scheduling (contd.) • Generally, such problems have multiple optimal solution. We may be interested in an optimal solution which maximizes the number of days with weekends off. How to obtain such a solution? • How to create a fair schedule for employees so that all employees get weekends off? • We assumed demands are static with time. What if demands are functions of time? Such problems are called dynamic workforce scheduling problems. • How to allocate overtimes, and allow part-time employees?

  12. Blending Problems • Situations where various inputs must be blended in some desirable proportion to produce goods for sale are called blending problems. • Examples of blending problems: Blending various crude oils to produce different types of gasoline Blending various types of metal alloys to various types of steels Blending various livestock feeds to produce minimum-cost feed mixture for cattle Mixing various types of paper to produce recycled paper of varying quality

  13. Blending Problems (contd.) • Chandler Oil manufactures gasoline and heating oil • These products are produced by blending two types of crude oil (crude 1, and crude 2) • The following table gives the data for quality points and sales and purchase prices: • Determine the production quantities of gasolines and heating oils to maximize the profit

  14. Blending Problem (contd.) Decision Variables: x11 : Number of barrels of crude 1 used in the manufacturing of gasoline x12 : Number of barrels of crude 1 used in the manufacturing of heating oil x21 : Number of barrels of crude 2 used in the manufacturing of gasoline x22 : Number of barrels of crude 2 used in the manufacturing of heating oil crude 2

  15. Blending Problem (contd.) Max. (25 - .02) (x11 + x21) + (20 - .01) (x12 + x22) s. t. x11 + x12 £ 5000 x21 + x22 £ 10000 10x11 + 5x21 ³ 8(x11 + x21) 10x12 + 5x22 ³ 6(x12 + x22) x11, x12 , x21, x22 ³0

  16. Blending Problem (contd.)

  17. Blending Problem (contd.)

  18. Blending Problem (contd.) • MODELING ISSUES: • Blending problems in practice have many more inputs and outputs • Quality level of gasoline and heating oil may not a linear function of the fractions of the inputs used • Blending problems are periodically solved on the basis of current inventories and demand forecasts

More Related