1 / 23

MBAD 6122 Decision Modeling and Analysis via Spreadsheets

MBAD 6122 Decision Modeling and Analysis via Spreadsheets. Dr. C em S aydam Main Campus: Friday Building 266B saydam@uncc.edu. Outline. Introductions Course objectives - syllabus Intro to Management Science - Chap. 1 Intro to Optimization and LP - Chap. 2

lilly
Download Presentation

MBAD 6122 Decision Modeling and Analysis via Spreadsheets

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. MBAD 6122 Decision Modeling and Analysis via Spreadsheets Dr. Cem Saydam Main Campus: Friday Building 266B saydam@uncc.edu

  2. Outline • Introductions • Course objectives - syllabus • Intro to Management Science - Chap. 1 • Intro to Optimization and LP - Chap. 2 • Modeling and solving LP problems via spreadsheets

  3. Management Science • Management Science (a.k.a. “analytics”) is the scientific approach to decision making. • Mgt. Sci. uses mathematical models to make sound decisions. • The focal point of analysis is the problem and mathematical (quantitative) models are the vehicles by which solutions are obtained. • Mgt. Science uses a systematic and logical approach to problem solving.

  4. Models • A model is an abstraction of reality. Models can be: • Prescriptive • functional relationships are well-known • solutions prescribe (advise, recommend) a set of values for the decision variables in order to maximize (or minimize) an objective • Predictive • explanatory variables affect the outcome(s) • functional relationship can be derived • Descriptive • describe the outcome or behavior of a system • functional relationships might be well-known but are either too complex to tackle analytically or are highly stochastic

  5. But there is no programming required Mathematical Programming (MP) • MP, a.k.a. optimization, is a field of management science that finds the optimal, or most efficient, way of using limited resources to achieve the objectives of an individual or a business. • Is this part of “analytics”? What is “analytics”? • http://www.sas.com/news/sascom/analytics_levels.pdf

  6. General form of an Math Prog. Model • Every optimization problem involves the following: • decisions that must be made • objective(s) (or goal(s)) • a set of restrictions (or constraints) MAX (or MIN): f0(X1, X2, …, Xn) Subject to: f1(X1, X2, …, Xn) <= b1 : fk(X1, X2, …, Xn) >= bk : fm(X1, X2, …, Xn) = bm Note: If all the functions in an optimization are linear(ized), the problem is a Linear Programming (LP) problem

  7. Aqua-Spa Hydro-Lux Pumps 1 1 Labor 9 hours 6 hours Tubing 12 feet 16 feet Unit Profit $350 $300 Intro to LP • An example: Blue Ridge Hot Tubs, Inc. • Blue Ridge Hot Tubs produces two types of hot tubs: Aqua-Spas & Hydro-Luxes. There are 200 pumps, 1566 hours of labor, and 2880 feet of tubing available.

  8. Formulating LP Models • Given a problem, first, determine the objective or goal. Maximize (or minimize) what? • Identify & define the decision variables (unknowns). • What should they represent and how many do we need? • State the objective as a linear function of the decision variables. Maximize profits X1=number of Aqua-Spas to produce X2=number of Hydro-Luxes to produce or abbreviate as follows Xi = no. of product i to make i=1,2 Max 350 X1 + 300 X2

  9. Formulating LP Models • Translate the requirements, restrictions, or wishes, that are in narrative form to linear functions. • Identify any lower or upper bounds on the decision variables (non-negativity constraints are v. common). 1X1 + 1X2 <= 200 } pumps 9X1 + 6X2 <= 1566 } labor 12X1 + 16X2 <= 2880 } tubing X1 >= 0 X2 >= 0 or Xi >= 0 i=1,2

  10. The Complete LP Model MAX: 350X1 + 300X2 S.T.: 1X1 + 1X2 <= 200 9X1 + 6X2 <= 1566 12X1 + 16X2 <= 2880 X1 , X2 >= 0 The general form of an LP model: MAX (or MIN): c1X1 + c2X2 + … + cnXn Subject to: a11X1 + a12X2 + … + a1nXn <= b1 : ak1X1 + ak2X2 + … + aknXn >= bk : am1X1 + am2X2 + … + amnXn = bm

  11. Feasible Region Graphical solution approach X2 261 boundary line of pump constraint 250 X1 + X2 = 200 200 boundary line of labor constraint 180 9X1 + 6X2 = 1566 150 boundary line of tubing constraint 12X1 + 16X2 = 2880 100 50 0 174 240 100 200 0 150 X1 250 50

  12. Final/optimal o.f.v. = $66,100 (122, 78) o.f.v. = $0 (0, 0) o.f.v. = $60,900 (174, 0) o.f.v. = $64,000 (80, 120) o.f.v. = $54,000 (0, 180) Enumerating the corner points X2 250 200 150 100 50 0 X1 100 0 150 200 250 50 o.f.v. = $15,000

  13. Another Graphical Solution Problem Min $10A + $20B ST 4A + 6B >= 120 B <= 30 A - 2B <= 5 A, B >= 0

  14. Excel 2007 Standard Solver “Add-In”

  15. Excel 2010 Standard Solver “Add-In”

  16. How Excel 2007 standard solver views the model • Target cell - the cell in the spreadsheet that represents the objective function • Changing cells - the cells in the spreadsheet representing the decision variables • Constraint cells - the cells in the spreadsheet representing the LHS formulason the constraints • Click on Options and check “Assume Linear Model” and “Assume Non-negative”, then, click OK, then click Solve!

  17. How Excel2010standard solverviews the model • Target cell - the cell in the spreadsheet that represents the objective function • Changing cells - the cells in the spreadsheet representing the decision variables • Constraints - the cells in the spreadsheet representing the LHS formulason the constraints and the RHS values (could be formulas also but should not include decision variables in them.) • For non-negativity constraints check and for Linear models select “Simplex LP” then, click OK, then click Solve!

  18. Expanded Blue Ridge Hot Tubs Problem • BRHT has added another product line: Deck-Spa • Each Deck-Spa uses 1 pump, 7 hours of labor and 14 ft. of tubing. • Profit margin is estimated as $315/unit. • Expand the formulation • Setup in Excel and solve via solver • Interpret the solution • What is the product mix? • What is the optimal profit (objective function)? • Other “observations” from investigating the solution provided by solver.

  19. Another Starter Formulation Exercise The Pyrotec Company produces three electrical products - clocks, radios, and toasters. The products have the following resource requirements: The manufacturer has a daily budget of $2,000 and a maximum of 660 hours of labor. Maximum daily demand for radios is 300. The marketing department requires that at least 15 percent of the total products must be toasters. Clocks sell for $15, radios for $20, and toasters for $12. Formulate as an LP. Solve using solver.

  20. Can we be so lucky, every time? • When we attempt to solve an LP, one of the following will occur: • Unique optimal solution • Multiple (alternate) optimal solutions • Unbounded solutions • Infeasible solution

  21. Unbounded solutions Always result of an error; a typo, misspecification etc. Example: Max 3x1 + 5x2 ST x1 + x2 >= 100 x1 <= 40 x1, x2 >= 0

  22. Infeasible solution • Suppose the Pyrotec Co. problem has the following additional requirement: • Management wants at least 350 Clocks. • Therefore we need to add one more constraint: • x1 >= 350

  23. Practice • Two practice problems from Chap 3: • #22 (#27 in the Int’l ed.)“A trust officer…” • #24 (#23 in the Int’l ed.) “A manufacturer of …” • Formulate • Setup and Solve using the standard solver in 2007 or 2010 • Study Chapters 2 and 3 • Install Risk Solver Platform for Education (RSPE) • Instructions to be emailed and posted in Moodle.

More Related