modeling and solving lp problems in a spreadsheet chapter 3 © 2014 cengage learning. all rights...

Modeling and Solving LP Problems in a Spreadsheet

Chapter 3

© 2014 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly

accessible website, in whole or in part.

Agri-Pro has received an order for 8,000 pounds of chicken feed to be mixed from the following feeds.

Nutrient Feed 1 Feed 2 Feed 3 Feed 4

Corn 30% 5% 20% 10%

Grain 10% 30% 15% 10%

Minerals 20% 20% 20% 30%

Cost per pound$0.25$0.30 $0.32 $0.15

Percent of Nutrient in

The order must contain at least 20% corn, 15% grain, and 15% minerals.



X1 = pounds of feed 1 to use in the

mix


mix


mix


mix



MIN: 0.25X1 + 0.30X2 + 0.32X3 +

0.15X4

Defining the Constraints

8,000 pounds of feedX1 + X2 + X3 + X4 = 8,000

At least 20% corn (0.3X1 + 0.5X2 + 0.2X3 + 0.1X4)/8000 >= 0.2

At least 15% grain(0.1X1 + 0.3X2 + 0.15X3 + 0.1X4)/8000 >= 0.15

At least 15% minerals(0.2X1 + 0.2X2 + 0.2X3 + 0.3X4)/8000 >= 0.15

Nonnegativity conditionsX1, 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


the mix


the mix


the mix© 2014 Cengage Learning. All Rights Reserved. May not be

scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.



Nutrient F1 F2 F3 F4Corn 0.3 0.05 0.2 0.1 1.6 >= 1.60Grain 0.1 0.3 0.15 0.1 1.2 >= 1.20Minerals 0.2 0.2 0.2 0.3 1.75 >= 1.20Cost per pound 0.25 0.3 0.32 0.15 1.950

4.5 2 0 1.5 8 = 8

F1 = Thosand pounds of Feed 1 in the mixCost in $1000

Microsoft Excel Worksheet (code)

Fig3-30.xlsm



Feed 1 Feed 2 Feed 3 Feed 4 Total

Unit cost $250 $300 $320 $150 $1,950 Units Req'd

Units to mix 4.5 2.0 0.0 1.5 8 = 8(Note: 1 unit = 1,000 pounds)

Percent of Nutrient in Amount MinimumNutrient Feed 1 Feed 2 Feed 3 Feed 4 in Blend Req'd AmntCorn 0.30 0.05 0.20 0.10 1.6 >= 1.6Grain 0.10 0.30 0.15 0.10 1.2 >= 1.2Minerals 0.20 0.20 0.20 0.30 1.75 >= 1.2

Agri-Pro

Invest $750,000 in the following bonds. No more than 25% in any single company. At least 50% in long-term bonds (10+ year). No more than 35% in Good and less than Good

Years toCompany Return Maturity RatingAcme 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



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

MAX: .0865X1+ .095X2+ .10X3+ .0875X4+ .0925X5+ .09X6

Total amount is investedX1 + X2 + X3 + X4 + X5 + X6 = 750,000

No more than 25% in any one investmentXi <= 0.25(750,000) =187,500, for all i

50% long term investment restriction.X1 + X2 + X4 + X6 >= 0.5(750,000) = 375,00035% Restriction on DynaStar, Eagle Vision, and OptiPro.X2 + X3 + X5 <= 0.35(750,000) = 262,500Nonnegativity conditionsXi >= 0 for all i



Fig 3-22© 2014 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly


Primitive

ACME DySt EaVi MiMo OpPr SaSs1-Excellent 3-Good 4-Fair 1-Excellent 3-Good 2-Very Good

8.65% 9.50% 10.00% 8.75% 9.25% 9.00%11 10 6 10 7 13 68887.5

112500 75000 187500 187500 0 1875001 1 1 1 1 1 750000 = 7500001 112500 <= 187500

1 75000 <= 1875001 187500 <= 187500

1 187500 <= 1875001 0 <= 187500

1 187500 <= 1875001 1 1 1 562500 >= 375000

1 1 1 262500 <= 262500

Fig 3-22



Intermediate

ACME DySt EaVi MiMo OpPr SaSs1-Excellent 3-Good 4-Fair 1-Excellent 3-Good 2-Very Good

8.65% 9.50% 10.00% 8.75% 9.25% 9.00%11 10 6 10 7 13 68887.5

112500 75000 187500 187500 0 1875001 1 1 1 1 1 750000 = 7500001 112500 <= 187500

1 75000 <= 1875001 187500 <= 187500

1 187500 <= 1875001 0 <= 187500

1 187500 <= 1875001 1 1 1 562500 >= 375000

1 1 1 262500 <= 262500

Fig 3-22



BetterACME DySt EaVi MiMo OpPr SaSs

1-Excellent 3-Good 4-Fair 1-Excellent 3-Good 2-Very Good8.65% 9.50% 10.00% 8.75% 9.25% 9.00%

11 10 6 10 7 13 68887.5112500 75000 187500 187500 0 187500 750000 = 750000

1 1 1 1 562500 >= 3750001 1 1 262500 <= 262500

LongTerm 112500 75000 187500 187500 562500Not Long Term 187500 0 187500 750000Better Than Good 112500 187500 187500 487500Goog and Less 75000 187500 0 262500 750000

Fig 3-22



=IF(B5>=10,IF(B6>0,B6,""),"")

Even Better=IF(B2<>"",1)=IF(ROWS($B$8:E11)=COLUMNS($B$8:E11),1,"")=IF(B5>=10,1,"")=IF(VALUE(LEFT(B3,1))>=3,1,"")=LEFT(G22,2)=LEN(G22)=FIND(" ",G22)=RIGHT(G22,G29-G30)=LEFT(G31,2)=G28&G32=LEFT(G22,2)&LEFT(RIGHT(G22,LEN(G22)-FIND(" ",G22)),2)=IF(B5>=10,IF(B6>0,B6,""),"")=IF(B10<>"","",IF(B6>0,B6,""))

Microsoft Excel Worksheet (code)

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 MaturityA 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

Investment 1 2 3 4 5 6 7

A1 -1 1.018B1 -1 <_____> 1.035C1 -1 <_____> <_____> 1.058D1 -1 <_____> <_____> <_____> <_____> <_____> 1.11A2 -1 1.018A3 -1 1.018B3 -1 <_____> 1.035A4 -1 1.018C4 -1 <_____> <_____> 1.058A5 -1 1.018B5 -1 <_____> 1.035A6 -1 1.018

Req’d Payments $0 $0 $250 $0 $250 $0 $300(in $1,000s)

Cash Inflow/Outflow at the Beginning of Month




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© 2014 Cengage Learning. All Rights Reserved. May not be



Minimize the total cash invested in month

1.

MIN: A1 + B1 + C1 + D1



Defining the Constraints Functional Constraints

1.018A1 – 1A2 = 0 } Beg. month 2

1.035B1 + 1.018A2 – 1A3 – 1B3 = 250 } Beg. month 3

1.058C1 + 1.018A3 – 1A4 – 1C4 = 0 } Beg. month 4

1.035B3 + 1.018A4 – 1A5 – 1B5 = 250 } Beg. Beg. month 5

1.018A5 –1A6 = 0 } month 6

1.11D1 + 1.058C4 + 1.035B5 + 1.018A6 = 300 } Beg. month 7

Nonnegativity ConditionsAi, Bi, Ci, Di >= 0, for all i



Fig3-37© 2014 Cengage Learning. All Rights Reserved. May not be


Just Type it in

1 2 3 4 5 6A1 B1 C1 D1 A2 A3 B3 A4 C4 A5 B5 A6 741.3634241

241.24 0.00 500.13 0.00 245.58 0.00 0.00 245.58 283.55 0.00 0.00 0.001 -1.00 -1.00 -1.00 -1.002 1.018 -1.00 0 = 03 1.035 1.018 -1.00 -1.00 250 = 2504 1.058 1.018 -1.00 -1.00 5.68434E-14 = 05 1.035 1.018 -1.00 -1.00 250 = 2506 1.018 -1.00 0 = 07 1.11 1.058 1.035 1.018 300 = 300

Trial 1



Trial 2

A1 B1 C1 D1 A2 A3 B3 A4 C4 A5 B5 A6 741.3634241241.24 0.00 500.13 0.00 245.58 0.00 0.00 245.58 283.55 0.00 0.00 0.000.018 0.035 0.058 0.110 0.018 0.018 0.035 0.018 0.058 0.018 0.035 0.018

1 1 1 1 2 3 3 4 4 5 5 62 3 4 7 3 4 5 5 7 6 7 7

-1.00 -1.00 -1.00 -1.001.018 -1 0 = 0

1.035 1.018 -1 -1 250 = 2501.058 1.018 -1 -1 5.68434E-14 = 0

1.035 1.018 -1 -1 250 = 2501.018 -1 0 = 0

1.11 1.058 1.035 1.018 300 = 300

=IF($A8=B$5,-1,IF($A8=B$6,1+B$4,""))



Trial 3

A1 B1 C1 D1 A2 A3 B3 A4 C4 A5 B5 A6 741.3634241241.24 0.00 500.13 0.00 245.58 0.00 0.00 245.58 283.55 0.00 0.00 0.000.018 0.035 0.058 0.110 0.018 0.018 0.035 0.018 0.058 0.018 0.035 0.018

1 1 1 1 2 3 3 4 4 5 5 62 3 4 7 3 4 5 5 7 6 7 7

1 -1.00 -1.00 -1.00 -1.002 1.018 || || || -1 0 = 03 1.035 || || 1.018 -1 -1 250 = 2504 1.058 || 1.018 || -1 -1 5.68434E-14 = 05 || 1.035 1.018 || -1 -1 250 = 2506 || || 1.018 || -1 0 = 07 1.11 1.058 1.035 1.018 300 = 300

=IF($A8=B$5,-1,IF($A8=B$6,1+B$4,IF(AND($A8>B$5,$A8<B$6),"||","")))

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

A1 + B1 + C1 + D1} month 1

1A2 + 3B1 + 8C1 + 6D1< 5

A2 + B1 + C1 + D1} month 2

1A3 + 3B3 + 8C1 + 6D1< 5

A3 + B3 + C1 + D1} month 3

1A4 + 3B3 + 8C4 + 6D1< 5

A4 + B3 + C4 + D1} month 4

1A5 + 3B5 + 8C4 + 6D1< 5

A5 + B5 + C4 + D1

} month 5

1A6 + 3B5 + 8C4 + 6D1< 5

A6 + B5 + C4 + D1} month 6



An Alternate Version of the Risk Constraints

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

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

3C1 + 1D1 – 4A3 – 2B3 < 0 } month 3

1D1 – 2B3 – 4A4 + 3C4 < 0 } month 4

1D1 + 3C4 – 4A5 – 2B5 < 0 } month 5

1D1 + 3C4 – 2B5 – 4A6 < 0 } month 6



-4A1 -2B1 3C1 1D1-2B1 3C1 1D1 -4A2

3C1 1D1 -4A3 -2B31D1 -2B3 -4A4 3C41D1 3C4 -4A5 -2B51D1 3C4 -2B5 1A6

An Alternate Version of the Risk Constraints

-4A1 – 2B1 + 3C1 + 1D1

-2B1 + 3C1 + 1D1 – 4A2

3C1 + 1D1 – 4A3 – 2B3

1D1 – 2B3 – 4A4 + 3C4

1D1 + 3C4 – 4A5 – 2B5

1D1 + 3C4 – 2B5 – 4A6



A1 B1 C1 D1 A2 A3 B3 A4 C4 A5 B5 A6-4 -2 3 1

-2 3 1 -43 1 -4 -2

1 -2 -4 31 3 -4 -21 3 -2 -4

-4A1 -2B1 +3C1 +1D1-2B1 +3C1 +1D1 -4A2

+3C1 +1D1 -4A3 -2B3+1D1 -2B3 -4A4 +3C4+1D1 +3C4 -4A5 -2B5+1D1 +3C4 -2B5 -4A6

=IF(AND($A8>=B$5,$A8<B$6),B$7,"")

=IF(B16="","",IF(B16<0,B16&B$15,"+"&B16&B$15))

Fig3-40.xlsm



Month of Cash Cash Flow Summary For MonthInvestment Inflow Outflow Return Risk Amount 1 2 3 4 5 6 7

A 1 2 0.018 -4 451.607392 -1.000 1.018 B 1 3 0.035 -2 0 -1.000 ==== 1.035 C 1 4 0.058 3 290.682106 -1.000 ==== ==== 1.058 D 1 7 0.11 1 0 -1.000 ==== ==== ==== ==== ==== 1.110A 2 3 0.018 -4 459.736325 -1.000 1.018 A 3 4 0.018 -4 218.011579 -1.000 1.018 B 3 5 0.035 -2 0 -1.000 ==== 1.035 A 4 5 0.018 -4 366.011364 -1.000 1.018 C 4 7 0.058 3 163.466091 -1.000 ==== ==== 1.058A 5 6 0.018 -4 122.599569 -1.000 1.018 B 5 7 0.035 -2 0 -1.000 ==== 1.035A 6 7 0.018 -4 124.806361 -1.000 1.018

=Total Invested in Month 1 742.289498 Surplus Funds 2E-12 250 -1E-13 250 1E-12 300

Req'd Payments 0 250 0 250 0 300

A 1 0.018 -4 A -4B 2 0.035 -2 B -2 -2C 3 0.058 3 C 3 3 3D 6 0.11 1 D 1 1 1 1 1 1

A -4A -4B -2 -2A -4C 3 3 3A -4B -2 -2A -4

-934.4 -966.899 2E-13 -973.6 0 -8.827<= <= <= <= <= <=0 0 0 0 0 0

A Production Planning Problem:The Upton Corporation

Upton is planning the production of their heavy-duty air compressors for the next 6 months.

• 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

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

Month




Pi = number of units to produce in month i, i=1

to 6

Bi = beginning inventory month i, i=1 to 6




Minimize the total cost production & inventory costs.

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

P6

+ 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.© 2014 Cengage Learning. All Rights Reserved. May not be


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© 2014 Cengage Learning. All Rights Reserved. May not be


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

B2 = B1 + P1 - 1,000

B3 = B2 + P2 - 4,500

B4 = B3 + P3 - 6,000

B5 = B4 + P4 - 5,500

B6 = B5 + P5 - 3,500

B7 = B6 + P6 - 4,000

Notice that the Bi can be computed directly from the Pi. Therefore, only the Pi need to be identified as changing cells.



Fig3-33.xlsm



1 2 3 4 5 6Beginning Inventory 2,750 5,750 4,750 2,750 1,500 2,000Units Produced 4,000 3,500 4,000 4,250 4,000 3,500Units Demanded 1,000 4,500 6,000 5,500 3,500 4,000Ending Inventory 5,750 4,750 2,750 1,500 2,000 1,500

Minimum Production 2,000 1,750 2,000 2,250 2,000 1,750Maximum Production 4,000 3,500 4,000 4,500 4,000 3,500

Minimum Inventory 1,500 1,500 1,500 1,500 1,500 1,500Maximum Inventory 6,000 6,000 6,000 6,000 6,000 6,000

Unit Production Cost $240 $250 $265 $285 $280 $260Unit Carrying Cost 1.5% $3.60 $3.75 $3.98 $4.28 $4.20 $3.90

Monthly Production Cost $960,000 $875,000 $1,060,000 $1,211,250 $1,120,000 $910,000Monthly Carrying Cost $15,300 $19,688 $14,906 $9,084 $7,350 $6,825

Total Cost $6,209,403

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:

Weighted sum of unit i’s outputs

Weighted sum of unit i’s inputs=

I

O

n

jjij

n

jjij

vI

wO

1

1




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.




Maximize the weighted output for unit i :

On

jjijwO

1MAX:



Defining the Constraints Efficiency cannot exceed 100% for any unit

Sum of weighted inputs for unit i must equal 1

Nonnegativity Conditionswj, vj >= 0, for all j

units ofnumber the to1 ,1 1

kvIwOO In

j

n

jjkjjkj

11

In

jjijvI



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-43.xlsm



The Analytic Solver Platform software featured in this book is provided by Frontline Systems.

http://www.solver.com



modeling and solving lp problems in a spreadsheet chapter 3 © 2014 cengage learning. all rights...

Documents