false

Class Notes
(837,469)

Canada
(510,274)

York University
(35,409)

Marketing
(184)

MKTG 2030
(72)

Ben Kelly
(12)

Lecture 2

Unlock Document

Description

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) 4x 16x ≤248 (Extrusion constraint)
2) 2x 12x ≤218 (Packaging Constraint)
3) 2x 1x 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 +397B + 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 +1.5M +3M2≤ 10,300} wiring
1M +2M +1M ≤ 5000} harnessing
1 2 3
Non-negativity Conditions
M 1 M ,2M ,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 x1 1c 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
M

More
Less
Related notes for MKTG 2030

Join OneClass

Access over 10 million pages of study

documents for 1.3 million courses.

Sign up

Join to view

Continue

Continue
OR

By registering, I agree to the
Terms
and
Privacy Policies

Already have an account?
Log in

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.