1 / 20

Using MS Excel for LP

This guide explores the application of Microsoft Excel's Solver tool to optimize production strategies for different businesses, including a brewery, a cake baker, and furniture maker, WoodCo. Each case demonstrates how to maximize revenue or profit while adhering to resource constraints. The brewery case examines the production of beer and ale, the baker's dilemma with cakes, and WoodCo's furniture manufacturing. Participants will engage in small group exercises and lectures that enhance understanding of linear programming principles using Excel.

scott-miles
Download Presentation

Using MS Excel for LP

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. Using MS Excel for LP • Collect homework • Roll Call • Review homework • Lecture: Using MS Excel’s Solver • Small Group Exercise • Lecture: More Excel • Assign homework

  2. #9: The Brewery The brewery produces beer and ale. Beer sells for $5 per barrel, and ale for $2/barrel. Producing a barrel of beer requires 5 pounds of corn and 2 pounds of hops. Producing a barrel of ale requires 2 pounds of corn and 1 pound of hops. The brewery has 60 pounds of corn and 25 pounds of hops.

  3. #9: The Brewery

  4. #9: The Brewery Max Z ( revenue ) = 5 x1 + 2 x2 S. T. 5 x1 + 2 x2 <= 60 2 x1 + 1 x2 <= 25 x1, x2 >= 0

  5. 30 #9: The Brewery 25 Max Z ( revenue ) = 5 x1 + 2 x2 S. T. 5 x1 + 2 x2 <= 60 2 x1 + 1 x2 <= 25 x1, x2 >= 0 0, 25 = $50 10, 5 = $60 3 12, 0 = $60 3 12 ,12.5

  6. #10: The Baker A gourmet cook bakes two types of cake, chocolate and vanilla, to supplement her income. Each chocolate cake can be sold for $12, and each vanilla cake can be sold for $9. Each chocolate cake requires 20 minutes of baking time and uses 4 eggs. Each vanilla cake requires 40 minutes of baking time and uses 1 egg. The baker has 8 hours of baking time and 30 eggs.

  7. #10: The Baker

  8. #10: The Baker Max Z ( profit ) = 12 x1 + 9 x2 S. T. 20 x1 + 40 x2 <= 480 4 x1 + 1 x2 <= 30 x1, x2 >= 0, integers

  9. 30 #10: The Baker To find a point’s coordinates that aren’t obvious, solve simultaneous equations. 0,12 ??, ?? 12 7.5,0 3 3 7.5 24

  10. 30 #10: The Baker 20 x1 + 40 x2 <= 480 -5(4) x1 + -5 (1) x2 <= -5 (30) 0,12 ??, ?? 12 7.5,0 3 3 7.5 24

  11. 30 #10: The Baker 20 x1 + 40 x2 = 480 -20 x1 - 5 x2 = -150 0 35 x2 = 330 x2 = 330/35 or = 66/7 0,12 ??, ?? 12 7.5,0 3 3 7.5 24

  12. 30 #10: The Baker Isoprofit lines: Solve the optimization equation for x2 to find the slope of the profit line. 0,12 36/7, 66/7 12 7.5,0 3 3 7.5 24

  13. #10: The Baker Z = 12 x1 + 9 x2 Z - 12 x1 = 9 x2 Z/9 - 12/9 x1 = x2 5, 9 = $141

  14. 11. Woodco ...tables and chairs. Each table and chair must be made entirely out of oak or entirely out of pine. A total of 150 board feet of oak and 210 board feet of pine are available. A table requires either 17 board feet of oak or 30 board feet of pine. A chair requires either 5 board feet of oak or 13 board feet of pine. Each table sells for $40, and each chair for $15.

  15. #11: Woodco

  16. #11: Woodco Max Z ( profit ) = 40 x1 + 15 x2 S. T. 17 x1 + 5 x2 <= 150 30x1 + 13 x2 <= 210 x1, x2 >= 0, integers

  17. #11: Woodco Max Z ( profit ) = 40 x1 + 15 x2 S. T. 17 x1 + 5 x2 <= 150 30x1 + 13 x2 <= 210 x1, x2 >= 0, integers WRONG - FOLLOW ME 0, 210/13 = ~$240 7, 0 = $280

  18. Using MS Excel’s Solver • EX: using the Woodco example. • Create the table of information. • Set up formulas to relate the information to be optimized. • Set up solver to list constraints. • Run solver.

  19. Go to MS Excel w/ 9-3cexblank

  20. EX: Chicken Feed A farmer feeds chickens, maintaining a particular level of nutrition. Minimize the cost, using this information:

More Related