1 / 14

OPSM 301 Operations Management

Ko ç Un iversity. OPSM 301 Operations Management. Class 13&14: Linear Programming using Excel. Zeynep Aksin zaksin @ku.edu.tr. Example: Giapetto's Woodcarving. Two types of toys are manufactured: soldiers and trains Soldiers: Sells for $27 Uses raw materials worth $10

garren
Download Presentation

OPSM 301 Operations Management

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. Koç University OPSM 301 Operations Management Class 13&14: Linear Programming using Excel Zeynep Aksin zaksin@ku.edu.tr

  2. Example: Giapetto's Woodcarving • Two types of toys are manufactured: soldiers and trains • Soldiers: • Sells for $27 • Uses raw materials worth $10 • Each soldier increases variable labor and overhead costs by $14 • Trains: • Sells for $21 • Uses raw materials worth $9 • Each train increases variable labor and overhead costs by $10

  3. Giapetto's Woodcarving • Manufacture requires skilled labor of two types • Carpentry • Finishing • Resource requirements by product • Soldier: 1 hour of carpentry and 2 hours of finishing • Train: 1 hour of carpentry and 1 hour of finishing • Total resources available • Unlimited raw materials • 80 hours of carpentry • 100 hours of finishing labor

  4. Giapetto's Woodcarving • Demand • for trains is unlimited • At most 40 soldiers can be sold each week • Objective is to maximize weekly profit • Formulate as a linear program (LP)

  5. Towards the Mathematical Model: Define (decision variables) • x1 : number of soldiers produced each week • x2 : number of trains produced each week Objective function: • maximize weekly profit = weekly profit from soldiers + weekly profit from trains Constraints: • each week, no more than 100 hours of finishing time may be used • each week, no more than 80 hours of carpentry time may be used • each week, the number of soldiers produced should not exceed 40 because of limited demand

  6. The Linear Programming Model: max 3x1 + 2x2 subject to 2x1 + x2 100 (finishing hours) x1 + x2 80 (carpentry hours) x1 40 (demand for soldiers) x1 0 (nonnegativity constraint) x2  0 (nonnegativity constraint)

  7. Giapetto's Woodcarving: The LP Model max 3x1 + 2x2 subject to 2x1 + x2 100 (finishing hours) x1 + x2 80 (carpentry hours) x1 40 (demand for soldiers) x1 0 (nonnegativity constraint) x2  0 (nonnegativity constraint) • Where • x1 : number of soldiers produced each week • x2 : number of trains produced each week

  8. The Excel Model Filled in by Excel Solver

  9. Reading the variable information • The optimal solution for Giapetto is to produce 20 soldiers and 60 trains per week, resulting in an optimal profit of $180. (The maximum possible profit attainable is $180, which can be achieved by producing 20 soldiers and 60 trains)

  10. Example 1:Product Mix Problem P Q Sales price: 100 $/unit Max Demand:50 units/week Sales price:90 $/unit Max demand:100 units/week D 10 min/un D 5 min/un Products P and Q are produced using the given process routing. 4 machines are used:A,B,C,D. (available for 2400 min/week) The price and raw material costs are given. Problem: Formulate an LP to find the product mix that maximizes weekly profit. i.e. How many of each product should we produce given the capacity and demand constraints? What is the bottleneck of this process? Purchase Part 5$/un C 10 min/un C 5 min/un B 15 min/un A 15 min/un. B 15 min/un. A 10 min/un. RM1 20$/un RM2 20$/un RM3 20$/un Source: Paul Jensen

  11. LP Formulation • Decision variables: • P:Amount of product P to produce per week • Q:Amount of product Q to produce per week • Objective Function: Maximize Profit • Max 45P+60 Q • Constraints: • Machine hours used should be less than or equal to 2400 minutes: • A: 15 P + 10 Q <= 2400 • B: 15 P + 30 Q <= 2400 • C: 15 P + 5 Q <= 2400 • D: 10 P + 5 Q <= 2400 • Production should not exceed demand: • P<=100 • Q<=50 • Non-negativity • P>=0, • Q>=0

  12. Solver Solution

  13. Example Problem The Huntz Company purchases cucumbers and makes two kinds of pickles: sweetand dill. The company policy is that at least 30%, but no more than 60%, of thepickles be sweet. The demand for pickles is SWEET:5000 jars + additional 3 jars for each $1 spent on advertising DILL:4000 jars + additional 5 jars for each $1 spent on advertising • Sweet and dill pickles are advertised separately.  The production costs are:   SWEET:0.60 $/jarDILL:0.85 $/jar and the selling prices are:   SWEET:1.45 $/jarDILL:1.75 $/jar Huntz has $16,000 to spend on producing and advertising pickles. Formulateanappropriate Linear Program.

  14. Solution: LP Formulation Xs: Number of Sweet pickle jars produced. Xd: Number of Dill pickle jars produced. As: Amount of advertisement done for Sweet Pickles in dollars Ad: Amount of advertisement done for Dill Pickles in dollars • Objective; maximize profits: (Revenue - Cost) max[(1,45* Xs + 1,75* Xd)-( 0,6* Xs + 0,85* Xd + As + Ad)] Subject to: Demand Constraints: Xs ≤ 5,000 + 3As Xd ≤ 4,000 + 5Ad Budget Constraint: 0,6* Xs + 0,85* Xd + As + Ad ≤ 16,000 Ratio Constraint: Xs / (Xs + Xd) ≥ 0,3  0,7 Xs – 0,3 Xd ≥ 0 Xs / (Xs + Xd) ≤ 0,6  0,4 Xs – 0,6 Xd ≤ 0 Non-negativity Constraints: Xs, Xd, As, Ad≥0

More Related