Modeling and Solving LPs by Excel Solver

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