Problem 2-30

Inputs Sq Feet Weekly ad expense rental revenue revenue - ad expense

Large 150 1 $50 $49
Small 50 1 $20 $19

Weekly ad budget $100

Facility size (Sq Feet) 8000
Max large sheds 40

Decisions number of sheds built

Large 30
Small 70
Objective
Maximize revenue - ad costs $2,800

Constraints LHS (Cell reference) RHS

weekly ad budget $100 <= $100
facility size 8000 <= 8000
Max large sheds 30 <= 40
revenue - ad expense

2-30 A warehouse storage building company must determine how many storage sheds
of each size—large or small—to build in its new 8,000-square-foot facility to maximize
rental income. Each large shed is 150 square feet in size, requires $1 per week in
advertising, and rents for $50 per week. Each small shed is 50 square feet in size,
requires $1 per week in advertising, and rents for $20 per week. The company has a
weekly advertising budget of $100 and estimates that it can rent no more than 40 large
sheds in any given week.

Run a sensitivity report and answer the following questions. (Note: You should answer
all questions without changing inputs and rerunning solver.)

1. If the rental revenue for large sheds decreased by $10 would the optimal mix
of large and small sheds change? Would the value of the objective function
change? If so by how much?
2. How much does the rental revenue for large sheds need to drop for the optimal
solution to change?

3. If the rental revenue for small sheds increased by $20 would the optimal mix of
large and small sheds change? Would the value of the objective function change? If so
by how much?

4. How much does the rental revenue for small sheds need to increase for the optimal
solution to change?

5. If the weekly ad budget is increased by $50 what is the impact on profit?

Microsoft Excel 16.0 Sensitivity Report

Variable Cells
Final Reduced Objective Allowable
Cell Name Value Cost Coefficient Increase
$C$11 Large Sq Feet 30 0 49 8
$C$12 Small Sq Feet 70 0 19 30

Constraints
Final Shadow Constraint Allowable
Cell Name Value Price R.H. Side Increase
$C$17 weekly ad budget LHS (Cell reference) $100.00 4 100 60
$C$18 facility size LHS (Cell reference) 8000 0.3 8000 1000
$C$19 Max large sheds LHS (Cell reference) 30 0 40 1E+030
 Sample answers

1 A decrease in rental revenue for large sheds of $10 is within the

allowable decrease, therefore the optimal solution of 30 large and 70
small sheds will not change. Profit will decrease by 30*10 or 300
Allowable dollars ( 10 fewer dollars for each of the 30 large sheds.)
Decrease
30
2.6666666667 2 The allowable decrease for the coefficient of large sheds is 30. Therefore if
the weekly rentals fee for large sheds dropped below 50 - 30 or 20, then
we would change our optimal mix of large and small sheds. We would
build fewer large sheds.
Allowable
Decrease
An increase in rental revenue for small sheds of $20 is within the
20 3 allowable increase, therefore the optimal solution of 30 large and 70
3000 small sheds will not change. Profit will increase by 70*20 or 1400
10 dollars ( 20 more dollars for each of the 70 small sheds.)

4 The allowable increase for the coefficient of small sheds is 30. Therefore if
the weekly rentals fee for large sheds increased above 20+30 = 50, then we
would change our optimal mix of large and small sheds. We would build
more small sheds.

5 If the weekly ad budget is increased by $50 then profit will increase by 50*4
= $200.
the
e and 70
300

0. Therefore if
or 20, then
We would

n the
e and 70
1400

. Therefore if
= 50, then we
would build

crease by 50*4
Inputs Problem 3-9
Dem Rep Ind Minimum number
Cost per interview (Males) 10 9 13.5 2000
Cost per interview (Females) 12 11 13.5 1750

Min number total interviews 4500

Min number Ind 1000
Max % Dem 40%
Max % Rep 40%
Max % Male Rep 25%
Min % each category 10%

Decisions
Number polled Dem Rep Ind totasl
Males 1250 1050 450 2750
Females 450 750 550 1750
total 1700 1800 1000 4500
Performance
Min Cost $ 49,100

Constraints actual required

Min number males 2750 >= 2000
Min number males 1750 >= 1750
Min number polled 4500 >= 4500
Min number Ind 1000 >= 1000
Max % Dem -100 <= 0
Max % Rep 0 <= 0
Max % Male Rep -75 <= 0

Min % each category All values below must be >=0

Dem Rep Ind
Males 800 600 0
Females 0 300 100
3-9 A political polling organization is to conduct a poll of likely voters
prior to an upcoming election. Each voter is to be interviewed in
person. It is known that the costs of interviewing different types of
voters vary due to the differences in proportion within the popu-
lation. The costs to interview males, for example, are $10 per
Democrat, $9 per Republican, and $13.50 per Independent voter. The
costs to interview females are $12, $11 and $13.50 for Democrat,
Republican, and Independent voters, respectively. The polling service
has been given certain criteria to which it must adhere: There must
be at least 4,500 total interviews. At least 1,000 independent voters
must be polled. At least 2,000 males must be polled. At least 1,750
females must be polled. No more than 40% of those polled may be
Democrats. No more than 40% of those polled may be Republicans.
No more than one-quarter of those polled may be Republican males.
Each of the six categories of voters must be rep-resented in the poll
by at least 10% of the total interviews. Determine the least expensive
sampling plan and the total cost to carry out the plan.

Note: there are alternative solutions for this problem. If you get a
solution with a cost of 49,100 and all constraints are satsified then
you have a correct formulation.
 3.21 (note: I have transposed rows and columns - the problem can be worked either way.)
Inputs
Cargo Type A B C D E F
Value of freight per pound $8.00 $6.00 $3.50 $5.75 $9.50 $5.25
Volume per pound ( cu. Ft) 3.0 2.7 6.3 8.4 5.5 4.9
Pounds available 210 150 90 120 130 340

Maximum volume (cu. Ft) 2500

Maximum lbs. 900

Decisions Cargo Type A B C D E F

weight (Lbs.) 210 150 0 0 125.4545 0

Performance
Dollar value of freight $ 5,921.82

Constraints Used Available

Max volume 2500 <= 2500
Max weight 735.454545 <= 900

Amount available
Cargo Type A B C D E F
weight shipped 210 150 0 0 125.4545 0
<= <= <= <= <= <=
Weight avaibable 210 150 90 120 130 340
 G
$8.60
3.1 3-21 A small trucking company is determining the com-position of
250 its next trucking job. The load master has his choice of seven
different types of cargo, which may be loaded in full or in part. The
specifications of the cargo types are shown in the following table.
The goal is to maximize the amount of freight, in terms of dollars,
for the trip. The truck can hold up to 900 pounds of cargo in a
2,500-cubic-foot space. What cargo should be loaded, and what
will be the total freight charged?
G
250

G
250
<=
250
5.5 4.9 3.1 POUNDS AVAILABLE 210 150 90 120 130
340 250
Inputs 3.29

Union Non-union Temp

Wages per hour $ 15 $ 15 $ 10
Benefits per hour $ 7 $ 3 $ -
Hours worked per day 7 8 6
Fuel cells produced per hour 10 10 5

Calculated
Total cost per day $ 154 $ 144 $ 60
Total Fuel cells prodced per day 70 80 30

Cells needed 2100

Max ratio Non-Union to Union 0.8
Max ratio Temp to Union 0.2

Decisions Union Non-union Temp

Number hired 15 12 3

Objective
Minimize cost = $ 4,218.00

Constraints Actual Required

Fuel cell production 2100 >= 2100
Max ratio Non-Union to Union 0.00 <= 0
Max ratio Temp to Union 0 <= 0
3-29 A fuel cell manufacturer can hire union, non-union permanent, or temporary help. She has a contract to produce at
the rate of 2,100 fuel cells per day and would like to achieve this at minimum cost. Union workers work 7 hours per day and
can make up to 10 fuel cells per hour. Their wages and benefits cost the company $15.00 and $7.00 per hour, respectively.
Union workers are assured that there will be no more than 80% of their number working in non-union per-manent
positions and that there will be no more than 20% of their number working in temporary positions. Non-union permanent
workers work 8 hours per day and can also make up to 10 fuel cells per hour. Their wages are the same as the union
employees, but their benefits are worth only $3.00 per hour. Temporary workers work 6 hours per day, can make up to 5
fuel cells per hour, and earn only $10 per hour. They do not receive any benefits. How many union, non-union, and
temporary workers should be hired to minimize the cost to the manufacturer? What is the average cost of produc-ing a fuel
cell?
Note: Require integer values for decision variables
 a contract to produce at
rs work 7 hours per day and
7.00 per hour, respectively.
-union per-manent
ons. Non-union permanent
e same as the union
per day, can make up to 5
on, non-union, and
age cost of produc-ing a fuel
 3.38
Inputs
A B Protein
Cost per ml 0.00325 0.0045 0.028

Protein (g) 0.006 0.009 0.23

Fat (g) 0.008 0.012 0.002
Carbohydrate (g) 0.147 0.096 0.024
Sodium (mg) 1.77 0.72 0.32

Can capasity (mL) 325

Min Protein (g) 15
Max Fat (g) 3
Max Carbohydrates (g) 38
Max Sodium (mg) 310

Decision A B Protein
Number of ml used in MRE 93.68 175.41 55.91

Objectice
Min cost MRE 2.6592776695

Constraints
Can capasity (mL) 325.00 = 325
Min Protein (g) 15 >= 15
Max Fat (g) 2.966183669 <= 3
Max Carbohydrates (g) 31.952176738 <= 38
Max Sodium (mg) 310 <= 310
3-38 The military has requested a new ready-to-eat meal
(MRE) that will provide to troops in the field a very high-
protein, low-carbohydrate instant canned breakfast. The
can will contain 11 fluid ounces, or 325 mL, of the product.
The design specifications are as follows: The drink should
have at least 15 grams of protein, no more than 3 grams of
fat, no more than 38 grams of carbohydrates, and no more
than 310 mg of sodium. To make the drink, a food
contractor plans to mix two ingredients it already makes,
liq-uid A and liquid B, together with a new ingredient, liquid
protein. The table at the bottom of this page describes the
costs and the nutritional makeup of the three ingredients.
Determine the least-cost mixture for the new MRE.
Inputs Dallas Houston
Unit Production Cost $3.00 $3.50
Monthly Capacity 500 650
Unit Holding Cost $0.10

Estimated Demand October 800

A startup company sells LED iPhone cases online. They have been successful in marketing and have
Monthly percentage demand increase 20% orders for cases coming in monthly. They produce at two manufacturing sites - one in Dallas and one in
Current inventory 50 Houston. The costs to produce a one case in Dallas and Houston are $3.00 and $3.50 respectively.
Dallas can produce up to 500 cases per month and Houston can produce up to 650 per month. They
have shipped all September orders and have 50 cases remaining inventory at the beginning of October.
The demand for October is 800 cases and order quantities are expected to increase by 20% each month
Decisions Production Dallas Houston Demand units remaining at end of month --For example, November’s demand will be 800*(1.20) = 960. (Note it is OK to have demand for a
fraction of a case and to produce a fraction of a case.) Inventory holding costs are $0.10 per case in
September NA NA NA 50.0 inventory at the end of each month.
October 500 294.4 800 44.4
November 500 650 960 234.4 Determine the minimum cost production schedule for the next 4 months (October through January) if
the company must satisfy demand each month.
December 500 650 1152 232.4
January 500 650 1382.4 0.0

Performance
Note: The production schedule will not change if
Production Cost $ 13,855 the cost is included or not included as the cost of
Inventory Cost $ 51 50 units will be the same regardless of the
Total cost $ 13,907 production schedule decision.

Constraints
Production <= capacity
Dallas Produced Capacity
October 500 <= 500
November 500 <= 500
December 500 <= 500
January 500 <= 500
Houston
October 294.4 <= 650
November 650 <= 650
December 650 <= 650
January 650 <= 650

Inventory >0
44.4 >= 0
234.4 >= 0
232.4 >= 0
0.0 >= 0
Input 5.14
Distance, Demand and Supply
TO:
FROM: Columbia Albany Springfield Pleasatnburgupply of rail cars
Parris 50 30 60 70 35
Butler 20 80 10 90 60
Century 100 40 80 30 25
Demand for rail cars 30 45 25 20

Decision Shipment plan (Cars along each route)

TO:
FROM: Columbia Albany Springfield Pleasatnburg Total
Parris 0 35 0 0 35
Butler 30 5 25 0 60
Century 0 5 0 20 25
Total 30 45 25 20

Objective
Minimize distance 3,100

Constraints
Supply:
Parris 35 <= 35 Note: all constraints can also be eq
Butler 60 <= 60
Century 25 <= 25

Demand Columbia Albany Springfield Pleasatnburg

30 45 25 20
>= >= >= >=
30 45 25 20
 5-14 The Southern Rail Company ships coal by rail cars from
three coal mines to meet the demand requirements of four
coal depots. The following table shows the distances from the
mines to the various depots and the availabilities and
requirements for coal. Deter-mine the best shipment of coal
cars to minimize the total miles traveled by the rail cars.

: all constraints can also be equality constraints

Input 5.15
Distance, Demand and Supply
Interest rates: To
From Hill St. Banks St. Park Ave. Drury Ln. Max credit line
First Homestead 0.08 0.08 0.1 0.11 $80,000
Commonwealth 0.09 0.09 0.12 0.1 $100,000
Wash Federal 0.09 0.11 0.1 0.09 $120,000
Loan required (dollars) $60,000.00 $40,000.00 $130,000.00 $70,000.00

Decision
Loan amounts: To
From Hill St. Banks St. Park Ave. Drury Ln. Total
First Homestead $60,000 $0 $20,000 $0 $80,000
Commonwealth $0 $40,000 $0 $60,000 $100,000
Wash Federal $0 $0 $110,000 $10,000 $120,000
Total $60,000 $40,000 $130,000 $70,000

Objective
Minimize interest $28,300

Constraints
Max credit
First Homestead $80,000 <= $80,000 Note: all constraints can also be equa
Commonwealth $100,000 <= $100,000
Wash Federal $120,000 <= $120,000

Loan required Hill St. Banks St. Park Ave. Drury Ln.
$60,000 $40,000 $130,000 $70,000
>= >= >= >=
$60,000 $40,000 $130,000 $70,000
 5-15 The Piedmont Investment Corporation has identified
four small apartment buildings in which it would like to
invest. The four banks generally used by Piedmont have
provided quotes on the interest rates they would charge
to finance each purchase. The banks have also advised
Piedmont of the maximum amount of money they are
willing to lend at this time. Piedmont would like to
purchase as many buildings as possible while paying the
lowest possible amount in total interest. More than one
bank can be used to finance the same property. What
should Piedmont do?

: all constraints can also be equality constraints

Problem 2-26
Inputs
Mission Rustic Available
Material cost $340 $430 $30,000
Labor hours 15 20 1200 A furniture cabinet maker produces t
Profit $910 $1,200 and hide plasma televisions. The Mis
materials and 15 labor hours to prod
per cabinet. The Rustic-style cabinet
Decisions Number produced Mission Rustic hours to produce, and it yields a profi
0 60 of $30,000 to spend on materials. To
wishes to plan to maximize its profit
workers fully employed, so all 1,200 a
Objective must be used. What is the best comb
Maximize Profit $72,000 made?

Run a sensitivity report and answer t

Constraints used (Cell reference) available (RHS) You should answer all questions with
solver.)
Material costs $25,800 <= $30,000
Labor hours 1200 <= 1200 1. Explain why the upper limit for the
1E+30 (infinity)

2. If the profit per unit for rustic cabi

would we consider producing them?

3. If the budget for materials increas

price change? Would the optimal pro
profit for the optimal solution change

4. If labor hours available decreased

for labor hours change? Would the o
Would the profit for the optimal solu

5. If labor hours available increased b

change? Would the optimal product
the optimal solution change?
cabinet maker produces two types of cabinets that house
asma televisions. The Mission-style cabinet requires $340 in
nd 15 labor hours to produce, and it yields a profit of $910
. The Rustic-style cabinet requires $430 in materials and 20
oduce, and it yields a profit of $1,200. The firm has a budget
to spend on materials. To ensure full employment, the firm
an to maximize its profit but at the same time to keep all 30
y employed, so all 1,200 available labor hours available
ed. What is the best combination of furniture cabinets to be

tivity report and answer the following questions. (Note:

answer all questions without changing inputs and rerunning

why the upper limit for the coefficient of Mission cabinets is

nity)

ofit per unit for rustic cabinets increased, at what profit

onsider producing them?

dget for materials increased by $1000 would the shadow

e? Would the optimal product mix change? Would the
e optimal solution change?

hours available decreased by 100 would the shadow price

urs change? Would the optimal product mix change?
profit for the optimal solution change?

hours available increased by 200 would the shadow price

ould the optimal product mix change? Would the profit for
solution change?
Microsoft Excel 16.0 Sensitivity Report

Variable Cells
Final Reduced Objective Allowable
Cell Name Value Cost Coefficient Increase
$C$9 Mission 80 0 910 1E+030
$D$9 Rustic 0 -13.333333333 1200 13.333333333

Constraints
Final Shadow Constraint Allowable
Cell Name Value Price R.H. Side Increase
$C$15 Material costs used (Cell reference) 27200.00 0.0000 30000.00 1E+030
$C$16 Labor hours used (Cell reference) 1200.00 60.6667 1200.00 123.52941176
 Sample answers

1 The upper limit for coefficient of mission cabinets in this model is infinity
because we are producing as many as we can given the model constraints.
Allowable Regardless of how high the profit is for a mission cabinet, we are
Decrease constrained to 80 cabinets because of limited resources (specifically labor
10 hours).
1E+030
2 We would consider producing rustic cabinets if the profit per unit increased
by $13.34 to 1213.34. (Note: I rounded up here but you could also state a
$13.33 increase. Remember at exactly the allowable increase (or decrease)
Allowable of 13.333333... there will be alternative solutions. Anything above the
allowable increase will result in a new solution.)
Decrease
2800
1200

3 We are under the budget for materials by $30,000-$27,200 = $2,800.

Therefore, increasing the budget by $1000 will not increase our profit nor
will our optimal product mix change. The shadow price will remain at 0.

4 Labor is the binding constraint. At the current solution we are using all
labor hours. If available labor hours decreased by 100 the shadow price
will not change as 100 is less than the allowable decrease. The profit will
decrease by 100* 60.6667 or $60.67 (rounded up). The optimal product
mix will change but without re-running the model we do not know what
that mix will be.

5 Labor is the binding constraint. At the current solution we are using all
labor hours. If an additional 200 labor hours are made available then the
shadow price for labor hours will change as it is above the allowable
increase. Profit will increase but we are uncertain how much without re-
running the model because 200 is greater than the allowable increase.
The optimal product mix will change but without rerunning the model we
do not know what that mix will be.
 is infinity
constraints.
e
fically labor

unit increased
d also state a
(or decrease)
bove the

$2,800.
ur profit nor
emain at 0.

e using all
adow price
e profit will
mal product
know what

e using all
ble then the
owable
h without re-
increase.
he model we
Inputs Product
EC221 EC496 NC455 NC791
Wiring time per unit 0.5 1.5 1.5 1
Drilling time per unit 0.3 1 2 3
Assembly time per unit 0.2 4 1 2
Inspection time per unit 0.5 1 0.5 0.5
Minimum needed 150 100 300 400
Profit per unit $9 $12 $15 $11

Decision variables EC221 EC496 NC455 NC791

Units produced 20650 100 2750 400

Objective Maximize profit $232,700

Constraints used (Cell reference) available (RHS)

Wiring time 15000 <= 15000
Drilling time 12995 <= 12995
Assemble time 8080 <= 8080
Inspection time 12000 <= 12000

Minimum needed EC221 EC496 NC455 NC791

Produced (Cell reference) 20650 100 2750 400
>= >= >= >=
Minimum (RHS 150 100 300 400
Capasity (Hours
15000 An electronics corporation manufactures four highly technical
12995 products that it supplies to aero-space firms. Each of the products
must pass through the following departments before being
8080 shipped: wiring, drilling, assembly, and inspection. The time
12000 requirement (in hours) for each unit produced, the available time
in each department each month, minimum production levels for
each product, and unit profits for each product are summarized in
the table in the left. Formulate this problem as an LP model and
solve it by using Excel. Your solution should honor all constraints
and maximize the profit.

Using this version of the model, run a sensitivity report and answer
the following questions. (Note: You should answer all questions
without changing inputs and rerunning solver.)

1. The optimal mix of products will not change as long as the unit
profit for EC221 is between what two values?

2. If wiring time available decreased by 2000 would the

shadow price change? Would optimal product mix change?
What would be the change in profit?
ghly technical
h of the products
fore being
n. The time
he available time
uction levels for
re summarized in
n LP model and
or all constraints

report and answer

wer all questions

s long as the unit

0 would the
ct mix change?
Microsoft Excel 16.0 Sensitivity Report

Variable Cells
Final Reduced Objective Allowable
Cell Name Value Cost Coefficient Increase
$C$12 Units produced EC221 20650 0 9 6
$D$12 Units produced EC496 100 0 12 9
$E$12 Units produced NC455 2750 0 15 12
$F$12 Units produced NC791 400 0 11 1

Constraints
Final Shadow Constraint Allowable
Cell Name Value Price R.H. Side Increase
$C$17 Wiring time used (Cell reference) 15000 6 15000 9.094947E-13
$C$18 Drilling time used (Cell reference) 12995 0 12995 1E+030
$C$19 Assemble time used (Cell reference) 8080 0 8080 1E+030
$C$20 Inspection time used (Cell reference) 12000 12 12000 2450
$C$23 Produced (Cell reference) EC221 20650 0 150 20500
$D$23 Produced (Cell reference) EC496 100 -9 100 2.273737E-13
$E$23 Produced (Cell reference) NC455 2750 0 300 2450
$F$23 Produced (Cell reference) NC791 400 -1 400 5.197113E-13
 1 The optimal mix of products will not change as long as the profit for
EC221 is between 9 + 6 = 15 and 9 - 2 = 7
Allowable
Decrease
2 2 If wiring time available decreased by 2000 the shadow price
would not change, the product mix would change and profit
1E+030 would decrease by 2000*6 = 12,000.
2
1E+030

Allowable
Decrease
2450
3.36513E-12
7.275958E-13
1.818989E-12
1E+030
7.478068E-12
1E+030
400