# Spreadsheet Modeling & Decision Analysis: - Download Now PowerPoint

Document Sample

```					       Chapter 3

Modeling and Solving LP
Problems in a Spreadsheet
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
spreadsheets to solve LP problems
Spreadsheet Solvers

 The company that makes the Solver in
Excel, Lotus 1-2-3, and Quattro Pro is
Frontline Systems, Inc.
Check out their web site:
http://www.solver.com
 Other packages for solving MP problems:
AMPL                  LINDO
CPLEX                 MPSX
Preparation: Install Premium Solver
1.   You must have a PC, not a MacIntosh. If you have a
MacIntosh, plan to work in the computer lab Zone 1.
2.   Quit from Excel
3.   Execute PremSolv.exe

4
Preparation: the Model
 Make sure you have a model, written in
correct form.
 We’ll use the example from your textbook.

5
Preparation: the Model

MAX Z = 350X1 + 300X2      } profit
Subject to:
1X1 + 1X2 <= 200      } pumps
9X1 + 6X2 <= 1566     } labor
12X1 + 16X2 <= 2880   } tubing

Legend: X1 = Number of Aquaspas to make
X2 = Number of Hydroluxes to make
Entering the model in Excel
Step 1: Give your model a cool title

7
Step 2: Set up the columns
II. Add one column for each   Legend: X1 = Number of Aquaspas to make
unknown in the legend                 X2 = Number of Hydroluxes to make

III. Reserve a
column on the
I. Reserve the 1st column                                  right side for all
for row labels                                             the spreadsheet
formulas

IV. Add a label
for what you
are trying to
Maximize or to
Minimize

8
Step 3: Enter the objective function
Legend: X1 = Number of Aquaspas to make
X2 = Number of Hydroluxes to make
MAX Z = 350X1 + 300X2 } profit
I. Unknowns will go in the     III. In the row underneath the
first row. Enter 0 in these    unknowns, put the
cells, since we don’t know     coefficients.
what they’ll be.

II. Refer to the legend and add a          IV. Enter a nice explanatory
nice explanatory label for the row         label for the objective             9
of unknowns                                function coefficients
Step 3: Enter the objective function,
cont.
Legend: X1 = Number of Aquaspas to make
X2 = Number of Hydroluxes to make
MAX Z = 350X1 + 300X2 } profit

V. Enter a formula for the objective
function, multiplying each coefficient
cell by the unknown in the cell above
it. Add each pair together.

10
Step 4: Add pretty colors
I. Use the font color icon to add color,
II. Use the cell shading icon to add
improving readability
background shading, improving
readability

11
Step 6: Enter the constraints
Legend: X1 = Number of Aquaspas to make
X2 = Number of Hydroluxes to make
1X1 + 1X2 <= 200        } pumps
9X1 + 6X2 <= 1566       } labor
12X1 + 16X2 <= 2880     } tubing

II. List the
I. Insert a label   constraints
to show where
the constraints
are located

12
Step 6: Enter the constraints, cont.
Legend: X1 = Number of Aquaspas to make
III. Enter the constraint           X2 = Number of Hydroluxes to make
1X1 + 1X2 <= 200        } pumps
coefficients.
9X1 + 6X2 <= 1566       } labor
12X1 + 16X2 <= 2880     } tubing

IV. Skip a column
and enter the
right-hand side
values.

13
Step 6: Enter the constraints, cont.
Legend: X1 = Number of Aquaspas to make
X2 = Number of Hydroluxes to make
1X1 + 1X2 <= 200        } pumps
Enter formulas for the      9X1 + 6X2 <= 1566       } labor
constraints, multiplying    12X1 + 16X2 <= 2880     } tubing
each constraint
coefficient times the
unknown above it.

14
Running Solver
 With the spreadsheet now set up, it’s time
to run the Solver utility.

15
Running Solver Step 1
 Select Tools..Add-ins
and make sure the
Solver Add-in is
checked. (Click on the
check box if it isn’t.)
 Click OK
 If the Solver Add-In is
not showing at all, plan
on working in the lab
Zone 1.

16
Running solver: Step 1 cont.
 Select the Tools..Solver menu item
 If the Standard Solver window appears,
click the Premium button

17
Running Solver Step 1 cont.
I. Click the options button

II. When the Solver Options window appears, make sure Assume
Linear Model and Assume Non-Negative are both checked.
III. Click OK.                                             18
Running Solver: Step 2
 In the Premium Solver window, set the
solution method to Standard Simplex LP

19
How Solver Views the Model

Constraint cells - the cells in
Changing cells - the cells     the spreadsheet
in the spreadsheet             representing the
representing the decision      constraint formulas the
constraint right-hand sides
variables

Set Cell - the cell
in the spreadsheet
that represents the
objective function
How Solver Views the Model

21
Running Solver: Step 3
Click in the Set Cell textbox,
then click the cell in the
spreadsheet. The appropriate
cell reference will appear in
the textbox.

22
Running Solver: Step 4
Click in the Changing Variable
Cells textbox, then click and
drag the spreadsheet cursor
to highlight all the unknowns
in the objective functions. The
appropriate cell references will
appear in the textbox.

23
Running Solver: Step 5
I. Click the Add button.
II. The Add Constraint dialog
box appears.
III. Click in the Cell Reference
textbox, then click the cell with
the constraint formula.

IV. Click in the
Constraint textbox,
then click on righthand
side value for the
constraint.
V. Set inequality.
24
VI. Click the OK button.
Running Solver: Step 6
Add all remaining ineqalities

25
Running Solver: Step 7
I. Click solve
II. Check to see if solver found
a solution.
III. If solver did not find
a solution, click OK.
IV. Look for errors
in the solver
parameters and try
again.

26
Running Solver: Step 7 cont.
I. If solver found a solution
II. Highlight all three reports
by clicking on them.
III. Click OK to close
window.
IV. Click Close to
close window.

27
Running Solver: Step 7 cont.
V. Various linear programming
reports will have been added to the
spreadsheet.
V. Click on Answer Report to view the solution.

28
Instructor opinion
 You can set up the
spreadsheet
differently, but for
beginners it’s best to
use the same
spreadsheet design.
 Once you can get it to
work the way
described in this
handout/powerpoint,
feel free to try other
approaches.
 Let’s do another.

29
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.
Model 1   Model 2   Model 3
Number ordered              3,000    2,000      900
Hours of wiring/unit         2        1.5        3
Hours of harnessing/unit     1         2         1
Cost to Make                \$50       \$83       \$130
Cost to Buy                 \$61       \$97       \$145

 The company has 10,000 hours of wiring
capacity and 5,000 hours of harnessing capacity.
Convert to model
Minimize the total cost of filling the order.
MIN: 50M1+ 83M2+ 130M3+ 61B1+ 97B2+ 145B3
Legend:
Demand Constraints
M1 = Number of model 1 slip
rings to make in-house
M1 + B1 = 3,000 } model 1
M2 + B2 = 2,000 } model 2
M2 = Number of model 2 slip
rings to make in-house           M3 + B3 = 900 } model 3
M3 = Number of model 3 slip
Resource Constraints
rings to make in-house           2M1 + 1.5M2 + 3M3 <= 10,000 }
B1 = Number of model 1 slip      wiring
rings to buy from competitor     1M1 + 2.0M2 + 1M3 <= 5,000 }
B2 = Number of model 2 slip      harness
rings to buy from competitor
B3 = Number of model 3 slip
rings to buy from competitor
End of in-class materials

32
An Investment Problem:
Retirement Planning Services, Inc.
 A client wishes to invest \$750,000 in the
following bonds.
Years to
Company              Return   Maturity     Rating
Acme Chemical         8.65%     11       1-Excellent
DynaStar              9.50%     10         3-Good
Eagle Vision         10.00%      6         4-Fair
Micro Modeling        8.75%     10       1-Excellent
OptiPro               9.25%      7         3-Good
Sabre Systems         9.00%     13       2-Very Good
Investment Restrictions
 No more than 25% can be invested in any
single company.
 At least 50% should be invested in long-
term bonds (maturing in 10+ years).
 No more than 35% can be invested in
DynaStar, Eagle Vision, and OptiPro.
Defining the Decision Variables

X1 = amount of money to invest in Acme Chemical
X2 = amount of money to invest in DynaStar
X3 = amount of money to invest in Eagle Vision
X4 = amount of money to invest in MicroModeling
X5 = amount of money to invest in OptiPro
X6 = amount of money to invest in Sabre Systems
Defining the Objective Function
Maximize the total
annual investment return:

MAX: .0865X1+ .095X2+ .10X3+ .0875X4+ .0925X5+ .09X6
Defining the Constraints
 Total amount is invested
X1 + X2 + X3 + X4 + X5 + X6 = 750,000
 No more than 25% in any one investment
Xi <= 187,500, for all i
 50% long term investment restriction.
X1 + X2 + X4 + X6 >= 375,000
 35% Restriction on DynaStar, Eagle Vision, and
OptiPro.
X2 + X3 + X5 <= 262,500
 Nonnegativity conditions
Xi >= 0 for all i
Implementing the Model
See file Fig3-20.xls
A Transportation Problem:
Tropicsun
Processing
Groves           Distances (in miles)     Plants
Supply                                                         Capacity
Mt. Dora         21                        Ocala
275,000                                                         200,000
1              50                        4

40

35
Eustis          30                        Orlando
400,000                                                         600,000
2                                        5
22

55

Clermont         20                      Leesburg
300,000                                                          225,000
3       25                               6
Defining the Decision
Variables
Xij = # of bushels shipped from node i to node j
Specifically, the nine decision variables are:

X14 = # of bushels shipped from Mt. Dora (node 1) to Ocala (node 4)
X15 = # of bushels shipped from Mt. Dora (node 1) to Orlando (node 5)
X16 = # of bushels shipped from Mt. Dora (node 1) to Leesburg (node 6)
X24 = # of bushels shipped from Eustis (node 2) to Ocala (node 4)
X25 = # of bushels shipped from Eustis (node 2) to Orlando (node 5)
X26 = # of bushels shipped from Eustis (node 2) to Leesburg (node 6)
X34 = # of bushels shipped from Clermont (node 3) to Ocala (node 4)
X35 = # of bushels shipped from Clermont (node 3) to Orlando (node 5)
X36 = # of bushels shipped from Clermont (node 3) to Leesburg (node 6)
Defining the Objective Function

Minimize the total number of bushel-miles.
MIN: 21X14 + 50X15 + 40X16 +
35X24 + 30X25 + 22X26 +
55X34 + 20X35 + 25X36
Defining the Constraints
 Capacity constraints
X14 + X24 + X34 <= 200,000   } Ocala
X15 + X25 + X35 <= 600,000   } Orlando
X16 + X26 + X36 <= 225,000   } Leesburg
 Supply constraints
X14 + X15 + X16 = 275,000    } Mt. Dora
X24 + X25 + X26 = 400,000    } Eustis
X34 + X35 + X36 = 300,000    } Clermont
 Nonnegativity conditions
Xij >= 0 for all i and j
Implementing the Model
See file Fig3-24.xls
A Blending Problem:
The Agri-Pro Company
 Agri-Pro has received an order for 8,000 pounds of
chicken feed to be mixed from the following feeds.
Percent of Nutrient in
Nutrient         Feed 1      Feed 2      Feed 3    Feed 4
Corn              30%          5%         20%       10%
Grain             10%          3%         15%       10%
Minerals          20%         20%         20%       30%
Cost per pound   \$0.25        \$0.30      \$0.32     \$0.15

 The order must contain at least 20% corn, 15%
grain, and 15% minerals.
Defining the Decision Variables

X1 = pounds of feed 1 to use in the mix
X2 = pounds of feed 2 to use in the mix
X3 = pounds of feed 3 to use in the mix
X4 = pounds of feed 4 to use in the mix
Defining the Objective Function

Minimize the total cost of filling the order.

MIN:   0.25X1 + 0.30X2 + 0.32X3 + 0.15X4
Defining the Constraints
 Produce 8,000 pounds of feed
X1 + X2 + X3 + X4 = 8,000
 Mix consists of at least 20% corn
(0.3X1 + 0.5X2 + 0.2X3 + 0.1X4)/8000 >= 0.2
 Mix consists of at least 15% grain
(0.1X1 + 0.3X2 + 0.15X3 + 0.1X4)/8000 >= 0.15
 Mix consists of at least 15% minerals
(0.2X1 + 0.2X2 + 0.2X3 + 0.3X4)/8000 >= 0.15
 Nonnegativity conditions
X1, X2, X3, X4 >= 0
A Comment About Scaling
 Notice the coefficient for X2 in the ‘corn’
constraint is 0.05/8000 = 0.00000625
 As Solver runs, intermediate calculations are
made that make coefficients larger or smaller.
 Storage problems may force the computer to
use approximations of the actual numbers.
 Such ‘scaling’ problems sometimes prevents
Solver from being able to solve the problem
accurately.
 Most problems can be formulated in a way to
minimize scaling errors...
Re-Defining the Decision
Variables
X1 = thousands of pounds of feed 1 to use in the mix
X2 = thousands of pounds of feed 2 to use in the mix
X3 = thousands of pounds of feed 3 to use in the mix
X4 = thousands of pounds of feed 4 to use in the mix
Re-Defining the
Objective Function
Minimize the total cost of filling the order.

MIN:    250X1 + 300X2 + 320X3 + 150X4
Re-Defining the Constraints
 Produce 8,000 pounds of feed
X1 + X2 + X3 + X4 = 8
 Mix consists of at least 20% corn
(0.3X1 + 0.5X2 + 0.2X3 + 0.1X4)/8 >= 0.2
 Mix consists of at least 15% grain
(0.1X1 + 0.3X2 + 0.15X3 + 0.1X4)/8 >= 0.15
 Mix consists of at least 15% minerals
(0.2X1 + 0.2X2 + 0.2X3 + 0.3X4)/8 >= 0.15
 Nonnegativity conditions
X1, X2, X3, X4 >= 0
Scaling: Before and After
 Before:
– Largest constraint coefficient was 8,000
– Smallest constraint coefficient was
0.05/8 = 0.00000625.
 After:
– Largest constraint coefficient is 8
– Smallest constraint coefficient is
0.05/8 = 0.00625.
 The problem is now more evenly scaled!
The Assume Linear Model Option
 The Solver Options dialog box has an option
labeled “Assume Linear Model”.
 This option makes Solver perform some tests to
verify that your model is in fact linear.
 These test are not 100% accurate & may fail as a
result of a poorly scaled model.
 If Solver tells you a model isn’t linear when you
know it is, try solving it again. If that doesn’t
work, try re-scaling your model.
Implementing the Model
See file Fig3-28.xls
A Production Planning Problem:
The Upton Corporation
 Upton is planning the production of their heavy-duty air
compressors for the next 6 months.
Month
1       2        3           4    5      6
Unit Production Cost   \$240    \$250    \$265     \$285     \$280   \$260
Units Demanded         1,000   4,500   6,000    5,500 3,500     4,000
Maximum Production     4,000   3,500   4,000    4,500 4,000     3,500
Minimum Production     2,000   1,750   2,000    2,250 2,000     1,750

•   Beginning inventory = 2,750 units
•   Safety stock = 1,500 units
•   Unit carrying cost = 1.5% of unit production cost
•   Maximum warehouse capacity = 6,000 units
Defining the Decision Variables
Pi = number of units to produce in month i, i=1 to 6

Bi = beginning inventory month i, i=1 to 6
Defining the Objective Function
Minimize the total cost production
& inventory costs.

MIN: 240P1+250P2+265P3+285P4+280P5+260P6

+ 3.6(B1+B2)/2 + 3.75(B2+B3)/2 + 3.98(B3+B4)/2
+ 4.28(B4+B5)/2 + 4.20(B5+ B6)/2 + 3.9(B6+B7)/2

Note: The beginning inventory in any month is the
same as the ending inventory in the previous month.
Defining the Constraints - I
 Production levels
2,000 <= P1 <= 4,000 } month 1
1,750 <= P2 <= 3,500 } month 2
2,000 <= P3 <= 4,000 } month 3
2,250 <= P4 <= 4,500 } month 4
2,000 <= P5 <= 4,000 } month 5
1,750 <= P6 <= 3,500 } month 6
Defining the Constraints - II
 Ending Inventory (EI = BI + P - D)
1,500 < B1 + P1 - 1,000 < 6,000 } month 1
1,500 < B2 + P2 - 4,500 < 6,000 } month 2
1,500 < B3 + P3 - 6,000 < 6,000 } month 3
1,500 < B4 + P4 - 5,500 < 6,000 } month 4
1,500 < B5 + P5 - 3,500 < 6,000 } month 5
1,500 < B6 + P6 - 4,000 < 6,000 } month 6
Defining the Constraints - III
 Beginning Balances
B1 = 2750
Notice that the Bi
B2 = B1 + P1 - 1,000   can be computed
B3 = B2 + P2 - 4,500   directly from the
Pi. Therefore,
B4 = B3 + P3 - 6,000   only the Pi need
B5 = B4 + P4 - 5,500   to be identified
as changing
B6 = B5 + P5 - 3,500   cells.
B7 = B6 + P6 - 4,000
Implementing the Model
See file Fig3-31.xls
A Multi-Period Cash Flow
Problem:
The Taco-Viva Sinking Fund - I
 Taco-Viva needs a sinking fund to pay \$800,000 in
building costs for a new restaurant in the next 6 months.
 Payments of \$250,000 are due at the end of months 2
and 4, and a final payment of \$300,000 is due at the end
of month 6.
 The following investments may be used.
Investment   Available in Month Months to Maturity Yield at Maturity
A         1, 2, 3, 4, 5, 6         1                1.8%
B             1, 3, 5              2                3.5%
C               1, 4               3                5.8%
D                1                 6               11.0%
Summary of Possible Cash
Flows
Cash Inflow/Outflow at the Beginning of Month
Investment     1       2     3       4      5        6      7
A1        -1    1.018
B1        -1 <_____> 1.035
C1        -1 <_____> <_____> 1.058
D1        -1 <_____> <_____> <_____> <_____> <_____> 1.11
A2                -1  1.018
A3                      -1   1.018
B3                      -1 <_____> 1.035
A4                              -1   1.018
C4                              -1 <_____> <_____> 1.058
A5                                     -1    1.018
B5                                     -1 <_____> 1.035
A6                                             -1    1.018
Req’d Payments \$0      \$0   \$250     \$0    \$250      \$0    \$300
(in \$1,000s)
Defining the Decision Variables
Ai = amount (in \$1,000s) placed in investment A at
the beginning of month i=1, 2, 3, 4, 5, 6
Bi = amount (in \$1,000s) placed in investment B at
the beginning of month i=1, 3, 5
Ci = amount (in \$1,000s) placed in investment C at
the beginning of month i=1, 4
Di = amount (in \$1,000s) placed in investment D at
the beginning of month i=1
Defining the Objective Function

Minimize the total cash invested in month 1.

MIN: A1 + B1 + C1 + D1
Defining the Constraints
 Cash Flow Constraints
1.018A1 – 1A2 = 0                   } month 2
1.035B1 + 1.018A2 – 1A3 – 1B3 = 250 } month 3
1.058C1 + 1.018A3 – 1A4 – 1C4 = 0 } month 4
1.035B3 + 1.018A4 – 1A5 – 1B5 = 250 } month 5
1.018A5 –1A6 = 0                    } month 6
1.11D1 + 1.058C4 + 1.035B5 + 1.018A6 = 300 } month 7
 Nonnegativity Conditions
Ai, Bi, Ci, Di >= 0, for all i
Implementing the Model
See file Fig3-35.xls
Risk Management:
The Taco-Viva Sinking Fund - II
 Assume the CFO has assigned the following risk ratings to
each investment on a scale from 1 to 10 (10 = max risk)

Investment     Risk Rating
A              1
B              3
C              8
D              6

 The CFO wants the weighted average risk to not exceed 5.
Defining the Constraints
 Risk Constraints
1A1 + 3B1 + 8C1 + 6D1
<5   } month 1
A1 + B1 + C1 + D1
1A2 + 3B1 + 8C1 + 6D1
<5   } month 2
A2 + B1 + C1 + D1
1A3 + 3B3 + 8C1 + 6D1
<5   } month 3
A3 + B3 + C1 + D1
1A4 + 3B3 + 8C4 + 6D1
<5   } month 4
A4 + B3 + C4 + D1
1A5 + 3B5 + 8C4 + 6D1
<5   } month 5
A5 + B5 + C4 + D1
1A6 + 3B5 + 8C4 + 6D1
<5   } month 6
A6 + B5 + C4 + D1
An Alternate Version of the Risk
Constraints
 Equivalent Risk Constraints

-4A1 – 2B1 + 3C1 + 1D1 < 0 } month 1

-2B1 + 3C1 + 1D1 – 4A2 < 0 } month 2   Note that each
coefficient is equal
3C1 + 1D1 – 4A3 – 2B3 < 0 } month 3    to the risk factor
for the investment
1D1 – 2B3 – 4A4 + 3C4 < 0 } month 4    minus 5 (the max.
allowable
1D1 + 3C4 – 4A5 – 2B5 < 0 } month 5    weighted average
risk).
1D1 + 3C4 – 2B5 – 4A6 < 0 } month 6
Implementing the Model
See file Fig3-38.xls
Data Envelopment Analysis (DEA):
Steak & Burger
 Steak & Burger needs to evaluate the performance
(efficiency) of 12 units.
 Outputs for each unit (Oij) include measures of: Profit,
Customer Satisfaction, and Cleanliness
 Inputs for each unit (Iij) include: Labor Hours, and Operating
Costs
 The “Efficiency” of unit i is defined as follows:
nO

Weighted sum of unit i’s outputs        Oij w j
j 1
=
Weighted sum of unit i’s inputs         nI
 I ij v j
j 1
Defining the Decision Variables
wj = weight assigned to output j
vj = weight assigned to input j

A separate LP is solved for each unit, allowing each
unit to select the best possible weights for itself.
Defining the Objective Function

Maximize the weighted output for unit i :

nO
MAX:  Oij w j
j 1
Defining the Constraints
 Efficiency cannot exceed 100% for any unit
nO             nI
 Okj w j   I kj v j , k  1 to the number of units
j 1           j 1

 Sum of weighted inputs for unit i must equal 1
nI
 I ij v j  1
j 1

 Nonnegativity Conditions
wj, vj >= 0, for all j
Important Point

When using DEA, output variables should be
expressed on a scale where “more is better”
and input variables should be expressed on a
scale where “less is better”.
Implementing the Model
See file Fig3-41.xls
Analyzing The Solution
See file Fig3-48.xls
End of Chapter 3

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 10 posted: 2/7/2012 language: English pages: 79
How are you planning on using Docstoc?