1 / 40

Evaluating Constrained Resources w/ Linear Programming

Evaluating Constrained Resources w/ Linear Programming. ISQA 511 Dr. Mellie Pullman. Overview. Game problem Terms Algebraic & Graphical Illustration LP with Excel. Tinker Toys. We need to allocate scarce resources among several alternatives resources= ? alternatives=?

rhoda
Download Presentation

Evaluating Constrained Resources w/ Linear Programming

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. Evaluating Constrained Resources w/ Linear Programming ISQA 511 Dr. Mellie Pullman

  2. Overview • Game problem • Terms • Algebraic & Graphical Illustration • LP with Excel

  3. Tinker Toys • We need to allocate scarce resources among several alternatives • resources= ? • alternatives=? • Need to get into teams • Your job is to produce Tinkertoys with three products (Turnstiles, Robots, & Front Wheel Assemblies)

  4. Parts Required and Availability

  5. Objectives • 1) Make as many of the three finished products as possible to maximize the total number of toys produced, • how many of each type of toy should be made? • 2) Make the number of finished products that make the most revenue. • Robots @ $30, Turnstiles @ $10, Front Wheel Assemblies @ $20.

  6. Maximize number of toys

  7. Maximize Overall Profit

  8. Value of constrained resources • A toy-trader has offered to sell your group two specific toy parts: • Orange rods $5/each • Wood caps $10/each • Are you interested in either of these parts? How many do you want to buy?

  9. Answers • Maximizing number of toys: 11 Toys2 Robots, 3 Turnstiles, & 6 Front Wheels • Maximizing revenue: $2203 Robots, 3 Turnstiles, & 5 Front Wheels

  10. Determining the Optimal Strategy in a constrained resource world • Try multiple attempts with different scenarios OR • Use Linear Programming (LP) • You will need to install Solver on your laptop • In Excel: • Click Tools • Click Add-ins • Click Solver Add-in

  11. Where to find it in Excel 2007

  12. 1 2 3

  13. What is Linear Programming? • A sequence of steps that will lead to an optimal solution. • Used to • allocate scarce resources (energy, food, land) • assign labor (shifts, Reg vs. OT, productivity) • determine lowest cost and emission transportation schemes • solve blending problems (food, chemicals or portfolios) • solve many other types of constrained resources problems

  14. Four essential conditions: • Explicit Objective: What are we maximizing or minimizing? Usually profit, units, costs, emissions, labor hours, etc. • Limiting resources create constraints:workers, equipment, parts, budgets, etc. • Linearity (2 is twice as good as 1, if it takes 3 hours to make 1 part then it takes 6 hours to make 2 parts) • Homogeneity (each worker has an average productivity)

  15. Bank Loan Processing • A credit checking company requires different processing times for consumer loans. • Housing loans (H) require 1 hour of credit review and 4 hours of appraising. Car loans (C) require 1 hour of credit review and 1 hour of appraising. • The credit reviewers have 200 hours available; the appraisers have 400 hours available. • Evaluating Housing loans yields $10 profit while evaluating Cars yields $5 profit. How many of each loan type should the company take?

  16. Graphical Approach (2 variables) • Formulate the problem in mathematical equations • Plot all the Equations • Determine the area of feasibility • Maximizing problem: feasible area is on or below the lines • Minimization: feasible area is on or above the lines • Plot a few Profit line (Iso-profit) by setting profit equation = different values. • Answer point will be one of the corner points (most extreme)

  17. Equations • Maximize Profit : $10 H + $5 C • Constrained Resources • 1H + 1C < 200 (credit reviewing hours) • 4H + 1C < 400 (appraising hours) • H>0; C>0 (non-negative) • H= ? • C=?

  18. Graphical Display C 400 4H + C < 400 300 200 10 H + 5 C 100 H + C < 200 100 200 300 400 H

  19. Farmer Gail (land and resource limits) • Farmer Gail in Pendleton owns 45 acres of land. Gail is going to plant each acre with wheat or corn. Each acre planted with wheat yields $200 profit while corn yields $300. The labor and fertilizer needed for each acre given below. 100 workers and 120 tons of fertilizer are available.

  20. Farmer’s Wheat and Corn Problem • Variables: • Acres planted in wheat = W • Acres planted in corn = C • Objective Function: • : Maximize profit $200 W + $300 C • Constraints: • Labor: 3 W + 2 C < 100 • Fertilizer: 2 W + 4 C < 120 • Land: 1W + 1 C < 45 • Non-Negativity: P1 & P2 > 0

  21. Wheat & Corn Corn Wheat

  22. Solver Set-up on Excel These 2 cells will change to find the solution. They represent W & C (our unknowns)

  23. Note: The inequality signs are NOT typed in, they are an option

  24. Answer Report What does slack mean here ?

  25. Sensitivity Report Reduced cost: how much more profitable would W or C have to be to be included in the answer? Profit of Wheat could increase by $250 or decrease by $50 and we would still use plant 20 acres. If we could get another worker, each worker contributes $25 (shadow price) to profit for the range (100+20 =120) to (100 - 40=60) or between 60 and 120 workers. So, how much are we willing to pay for an extra worker? How much are we willing to pay for an extra ton of fertilizer? How much for an extra acre of land ?

  26. Types of Problems • Transportation Networks/Models • Space Allocation • Financial Portfolios

  27. Transportation Networks Transportation model optimizes shipments between coming from m origins to n destinations. Mexico Warehouse Plant Tennessee Warehouse Plant Warehouse Toronto Plant Warehouse

  28. Equations • Objective:minimize cost of moving cars • $9AD +$9BD +$5CD+$8AE+$8BE+$3CE+$6AF+$8BF+$3CF+$5AG+$10CG • Constraints: • Have to at least meet demand @ D,E,F,GAD+BD+CD>50; AE+BE+CE>60; AF+BF+CF>25; AG+BG+CG>30 • Can’t exceed supply from A,B,CAD+AE+AF+FG<50; BD+BE+BF+BG<40; CD+CE+CF+CG<75.

  29. Other Sustainability Issues that might benefit from using this LP network solution?

  30. Space Allocation • Planes: how much space to allocate to people or cargo (profit maximizing) • Retail Space: which products to put on display (profit maximizing) • Warehouse Space: how much product to store

  31. Stereo Warehouse • The retail outlet of Stereo Warehouse is planning a special clearance sale. The showroom has 400 square feet of floor space available for displaying the week’s specials, model X receiver and series Y speakers. Each receiver has a wholesale cost of $100, requires 2 square feet of display space, and will sell for $150. The wholesale cost for a pair of speakers is $50, the pair requires 4 square feet of space and will sell for $70. The budget for stocking stereo items is $8000. The sales potential for the receiver is considered to be no more than 60 units. However, the budget-priced speakers appear to have unlimited appeal. The store manager, desiring to maximize gross profit, must decide how many receivers and speakers to stock.

  32. Space Solution • Variables x = # of receivers to stock; y = # of speaker pairs to stock • Objective? • Maximize profit: (Sale Price -cost)X + (Sale Price -cost)Y • Constraints? • Floor space: 2X+4Y < 400 • Budget: 100X+50Y < 8000 • Sales Limit X < 60

  33. Financial Portfolio Selection • Welte Mutual funds has just obtained $100,000 and is now looking for investment opportunities. The firm’s top financial analyst recommends these 5 options. The projected rates of return are shown below:Atlantic Oil 7.3%Pacific Oil 10.3%Midwestern Steel 6.4%Huber Steel 7.5%Government Bonds 4.5% • neither oil or steel should receive more than $50,000 of the total investment. • Government bonds should be at least 25% of the steel industry. • The investment in Pacific Oil is risky thus cannot be more than 60% of the total oil industry investment • What is the best investment plan for Welte?

  34. Financial Solution • Variables: • A,P,M,H,and G are the dollars allocated to each investment. • Objective? • Maximize return: .073A+.103P+.064M+.075H + .045G • Constraints? • Oil/steel: A+P < 50000; M+H < 50000 • Gov Bonds: G > .25 (M+H) or G - .25 M - .25 H > 0 • Risky oil: P < .60(A+P) or .40 P-.60A < 0

  35. Socially Responsible Investments? Constraints?

  36. Knapsack Problems (Binary) • You are running away from home and want to take all your favorite things (Ipod, knife, sweater, etc.) but only have so much room in your knapsack. You assign different values to each item and try to maximize the value of what you fit into the knapsack. • You take the item (1) or you don’t (0). • Note: This is a constraint called “Binary”under SOLVER.

  37. Cork’s Wine Tasting • Cork is doing a wine tasting of Oregon Pinot Noirs for a select group. As the wine manager, you must decide which wines to select. But, there are of course some limitations. • You have a budget (B) of $1000 and do not want to serve more than 30 bottles and only one of each brand. All the bottles will be pulled from the same year vintage and you have identified 64 (n) bottles each with a Wine Spectator rating rj and price pj (they range between $18 and $140)

  38. Equation Set-up • Many Different Possible Objectives • Maximize rating: • Subject to Constraints: • Budget • Number Bottles • Either in or out

  39. Other Possible Objectives? • Cheap tasting Objective? • Given you want the rating over some overall average • Must have best wines from 3 different parts of Oregon equally represented • Add a constraint on picking at least 10 from each

More Related