Class Notes (838,381)
Canada (510,868)
York University (35,470)
Marketing (184)
MKTG 2030 (72)
Ben Kelly (12)

Lecture 2-2.pdf

23 Pages
Unlock Document

MKTG 2030
Ben Kelly

OMIS2000 1 Jessica Gahtan Lecture 2- January 14, 2014: Modeling with Excel & Sensitivity Analysis Introduction - Solving LP problems graphically is only possible when there are two decision variables - Few real-world LP have only two decision variables - Fortunately, we can now use sprea dsheets to solve LP problems Computer Solutions - Computer programs designed to solve LP problems are now widely available - Most large LP problems can be solved with just a few minutes of computer time - Small LP problems usually require only a few seconds - Linear programming solvers are now part of many spreadsheet packages, such as: - Microsoft Excel (SOLVER) Excel 2007: Data → Solver The Steps in Implementing an LP Model in a Spreadsheet 1. Organize the data for the model on the spreadsheet 2. Reserve separate cells in the spreadsheet for each decision variable in the model 3. Create a formula in a cell in the spreadsheet that corresponds to the objective function 4. For each constraint, create a formula in a separate cell in the spreadsheet that corresponds to the left - hand side (LHS) of the constraint Spreadsheet Modeling - There is no exact one way to develop an LP spreadsheet model. We’ll work through many examples so you can develop your own way - It can be helpful to use formatting to identify and distinguish the diffe rent parts of your model - The common elements in all LP spreadsheet models are: Inputs/Data* All numerical inputs - that is, the data needed to form the objective and the constraints – must appear somewhere in the spreadsheet. We normally place all inputs in the upper left corner. However, we sometimes violate this convention when certain inputs fir more naturally somewhere else. Changing*cells* Instead of using variable names, such as x’s, there is a set of designed cells that play the roles of the decision variables. The values in these cells can be changed to optimize the objective. In Excel, these cells are called the changing cells. Target*(objective)*cell* One cell, called the target or objective cell, contains the value of the objective. The Solver systema tically varies the values in the changing cells to optimize the value in the target cell. Constraints* Excel doesn’t show the constraints directly on the spreadsheet. Instead, we specify constraints in a Solver dialog box Non;negativity* Normally, we want the decision variables – that is, the values in the changing cells – to be nonnegative. Except for in earlier versions of Excel, these constraints don’t need to be written explicitly; we simply check a box that we want nonnegative changing cells. OMIS2000 2 Jessica Gahtan Excel*Spreadsheet*Solution* In general, the complete solution of the problem involves 3 stages: 1. The first stage is to enter all the inputs, trial values for the changing cells, and formulas relating these in a spreadsheet. We call this formulating the model 2. The second stage is invoking the Solver. At this point, we formally designate the objective cell, the changing cells, the constraints, and selected options, and we tell the Solver to find the optimal solution 3. The third stage is sensitivity analysis. In most model formulation of real problems, we make ‘best guesses’ for the numerical inputs to the problem. There’s typically some uncertainty about quantities such as unit prices, forecasted demands, and resource availabilities. When we use Solver to solve the problem, we use our best estimates of these quantities to obtain the optimal solution. However, it’s important to see how the optimal solution changes as we vary inputs. Also, a sensitivity analysis often gives us important insights about how th e model works Goals for Spreadsheet design Communication* A spreadsheet’s primary business purpose is communicating information to managers Reliability* The output a spreadsheet generates should be correct and consistent Auditability* A manager should be able to retrace the steps followed to generate the different outputs from the model in order to understand and verify results Modifiability* A well-designed spreadsheet should be easy to change or enhance in order to meet dynamic user requirements Spreadsheet Design Guidelines 1. Organize the data, then build a model around the data 2. Don’t embed numeric constants in formulas 3. Things which are logically related should be physically related 4. Use formulas that can be copied OMIS2000 3 Jessica Gahtan 5. Columns/rows totals should be close to the columns/rows being totaled 6. The English-reading eye scans left to right, top to bottom 7. Use color, shading, borders and protection to distinguish changeable parameters from other model elements 8. Use text boxes and cell notes to documents various elements of the m odel Example – LP Formulation – Excel Solver LP*in*Final*Form* Max Z=34x +41x 2 Subject*to! 1) 4x1+6x 2 48 (Extrusion constraint) 2) 2x1+2x 2 18 (Packaging Constraint) 3) 2x1+x 2 16 (Additive mix constraint) 4) x , x ≥ 0 (non-negativity constraints) 1 2 Spreadsheet*Solution* Partial Spreadsheet Showing Problem Data OMIS2000 4 Jessica Gahtan Spreadsheet*Solution*–*Solver* OMIS2000 5 Jessica Gahtan Partial*Spreadsheet*Showing*Solution* OMIS2000 6 Jessica Gahtan Interpretation*of*Computer*Output* Objective Function Value = 342 Decision Variable #1 (x1) = 3 Decision Variable #2 (x2) = 6 Manual Solution Z=34x 140x 2 Z=34*3+40*6 = $ 342 Interpretation of Computer Output – SOLVER Make vs. Buy Decisions: The Electro- Poly Corporation Electro-Poly is a leading maker of slip rings A $750,000 order has just been received The company has 10,000 hours of wiring capacity and 5,000 hours of harnessing capacity. OMIS2000 7 Jessica Gahtan Defining*the*Decision*variables* M 1 Number of model 1 slip rings to make in -house M 2 Number of model 2 slip rings to make in -house M 3 Number of model 3 slip rings to make in -house B 1 Number of model 1 slip rings to buy from competitor B 2 Number of model 2 slip rings to buy from competitor B 3 Number of model 3 slip rings to buy from competitor Defining*the*Objective*Function * Minimize the total cost of filling the order MIN: 50M + 13M + 132M + 61B 3 97B + 145B 2 3 Defining*the*Constraints * Demand Constraints M + B = 3,000} model 1 1 1 M 2 B = 2,000} model 2 M + B = 900} model 3 3 3 Resource Constraints 2M 11.5M +32 ≤ 103000} wiring 1M +2M +1M ≤ 5000} harnessing 1 2 3 Non-negativity Conditions M 1 M 2 M ,3B B1, B2≥ 03 Implementing the Model ELOCTRO-POLY Using Excel Solver What does your intuition say about how the model should solve? _____________________________________ Is the answer consistent with your expectations? __________________________________________________ Why? ______________________________________ ______________________________________________ Sensitivity Analysis (Or post-optimality analysis) It’s used to determine how the optimal solution is affected by changes, within specified ranges, in: • The objective function coefficients • The right-hand side (RHS) values - Sensitivity analysis is important to the manager who must operate in a dynamic environment with imprecise estimates of coefficients - Sensitivity analysis allows him to ask certainwhat-if questions about the problem What*to*look*for* - Changes to different parts of the formulation could potentially affect • Which point is the optimal one • The size/ shape of the feasible region • The value of the objective function at the optimal point - Each type of change typically has different effects on the model Impact*of*Possible*Changes* Change'objective' - Changes slope - May change optimal solution OMIS2000 8 Jessica Gahtan Change'existing'constraint' - May change size of feasible region - May change optimal solution (if binding) Add'new'constraint' - May decrease feasible region - May change optimal solu tion (if binding) Remove'constraint' - May increase feasible region - May change optimal solution (if binding) Changing Objective Function Coefficients Objective*Function*Coefficients * - Let us consider how changes in the objective function coefficients might affe ct the optimal solution - Question: What if the profit for one of the products changes – will we still have made the right decision? (I.e., How much can objective coefficient change without changing optimal corner point?) - This change is usually referred to a s Range of Optimality Range of Optimality - A range of optimality of an objective function coefficient (OFC) is found by determining an interval for the coefficient in which the original optimal solution remains optimal while keeping all other data of the problem constant - Graphically, the limits of a range of optimality are found by changing the slope of the objective function line within the limits of the slopes of the binding constraint lines - If the OFC changes beyond that range, a new corner point becomes optimal Example:*Flair*Furniture*Co.* Two products: Chairs and Tables Decision: How many of each to make this month? Objective: To maximize profit Other limitations: Make no more than 450 chairs Make at least 100 tables Flair*Furniture*Problem* Max: 7T+5C (Profit) ** T=Table C=Chair Subject to the constraints: 1. 3T + 4C ≤ 2,400 (carpentry hours) 2. 2T + 1T ≤ 1,000 (painting hours) 3. C ≤ 450 (max # chairs) 4. T ≥ 100 (min # tables) 5. T, C ≥ 0 (non-negativity Graphical*Solution* Objective Function Line 7T+5C = Prof it OMIS2000 9 Jessica Gahtan SOLVER*Formulation*&*Solution * LHS Coefficients Constraints X1 X2 RHS Values #1 3 4 2400 #2 2 1 1000 #3 0 1 450 #4 1 0 100 Obj.Func.Coeff. 7 5 Decision Variables Decision Variable1 Decision Variable2 Max/Min Objective Function 0.000 Constraints Amount Used Amount Avail. Constraints #1 0 <= 2400 Constraints #2 0 <= 1000 Constraints #3 0 <= 450 Constraints #4 0 >= 100 Objective*Function*Coefficient*(OFC)*Changes * What if the profit contribution for tables changed from $7 to $8 per table? MAX: 78T+5C (Profit) → Clearly, profit goes up, but would we want to make more tables and fewer chairs? (i.e. Does the optimal solution change?) Characteristics*of*OFC*Changes * - When we change the objective function coefficient there is no effect on the feasible region - The slope of the level profit line changes the slope of an objective function line, Max c 1 1 c x 2 2s -c /c1 2. - If the slope changes enough, a different corner point will become optimal Original Objective function 7T + 5C = $4040 Revised objective function 8T + 5C = $4360 OMIS2000 10 Jessica Gahtan What*if*the*OFC*became*hig her?*Or*lower?* Range of Optimality OMIS2000 11 Jessica Gahtan Solver Solution Practice Example Revisited: Alexmatt Inc. Alexmatt Inc. makes tractors and lawn mowers. The firm makes a profit of $30 on each tand $30 on each lawn mower, and they sell all they can produce. The time requirements in the machine shop, fabrication, and tractor assembly are given in the table Machine shop Fabrication Assembly Tractor 2 hours 2 hours 1 hour Lawn mower 1 hour 3 hours 0 hour Hrs. available 60 hours 120 hours 45hours 1) Formulate the LP model 2) Solve this problem graphically. 3) Is any constraint redundant? Explain 4) Determine the sensitivity range for the profit for tractors OMIS2000 12 Jessica Gahtan Changing Resource Availability– Changes to the RHS of constraints RHS Coefficient Changes - When a right-hand-side (RHS) value changes, the constraint moves parallel to itself - Question: how is the solution affected, if at all? - Two cases: • Constraint is binding or active • Constraint is nonbinding or inactive - Common terms • Shadow price, dual price • Range of feasibility Shadow Prices Shadow price: Amount objective function will change per unit increase in RHS value of constraint - Excel gives Shadow Price Dual prices reflect ‘improvement’ rather than ‘change’; hence the difference for minimization problems Shadow*Price:*Non;Binding* OMIS2000 13 Jessica Gahtan Shadow*Price:*Binding* When the RHS of painting hours constraint is increased by one unit, the feasible region changes a nd optimal solution changes as well Shadow Price (RHS coefficient) - A shadow price for a right hand side value (or resource limit) is the amount the objective function will change per unit increase in the right hand side value of a cons traint, assuming all other coefficient values remain constant - Graphically, a shadow price is determined by adding +1 to one of the right hand side values and then resolving for the optimal solution in terms of the same two binding constraints - The shadow price is equal to the difference in the values of the objective functions between new and original problems - Shadow prices hold only within RHS changes falling within the values in “Allowable Increase” and “Allowable Decrease” columns - The shadow price for a n onbinding constraint is 0 OMIS2000 14 Jessica Gahtan Solver Range of Feasibility - The range of feasibility for a change in the right hand side value is the range of values for this coefficient in which the original shadow price remains constant - Graphically, the range of feasibility is determined by finding the values of a right hand side coefficient such that the same two constraint lines that determined the original optimal solution continue to determine the optimal solution for the problem. Range*of*Feasibility:*Non ;binding* ↓ ↑ * * Solver* OMIS2000 15 Jessica Gahtan Range*of*Feasibility:*Binding* * As long as the change in RHS of the binding constraints is within the allowable limits, the current binding constraints will remain binding. The new OFV will change by an amount equal to shadow price multiplied by the change in RHS. The new optimal solution (point) can be determined only by re-solving. OMIS2000 16 Jessica Gahtan Tightening or Relaxing Constraints Tightening a constraint means to make it more restrictive; i.e. decreas ing the RHS of a ≤ constraint, or increasing the RHS of a ≥ constraint - Compresses feasible region - May make solution worse Relaxing a constraint means to make it less restrictive - Expands feasible region - May make solution better Adjustable Cells Minimization*Example * Final Reduced Objective Allowable Allowable
More Less

Related notes for MKTG 2030

Log In


Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.