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%
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.
Microsoft Excel 16.89 Sensitivity Report
Worksheet: [Week 2 Practice [Link]]2.30 Storage
Report Created: 10/7/25 7:31:48 AM

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

Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$C$17 weekly ad budget LHS (Cell reference$100.00 4 100 60 20
$C$18 facility size LHS (Cell reference) 8000 0.3 8000 1000 3000
$C$19 Max large sheds LHS (Cell reference) 30 0 40 1E+030 10
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 2-30 A warehouse

Facility size (Sq Feet) 8000 of each size—larg
Max large sheds 40 rental income. Ea
advertising, and r
requires $1 per w
Decisions number of sheds built weekly advertisin
sheds in any given
Large 30
Small 70
Objective Run a sensitivity r
all questions with
Maximize revenue - ad costs $2,800
1. If the rental r
Constraints LHS (Cell reference) RHS of large and sma
weekly ad budget $100 <= $100 change? If so by
facility size 8000 <= 8000 2. How much do
Max large sheds 30 <= 40 solution to chang

3. If the rental re
large and small sh
1 No, large shed decreasing by $10 won't change the optimal mix. by how much?
Objective function decreases by 30*10 = $300
4. How much doe
solution to chang
2 Revenue need to drop more than 30 to change the optimal,
5. If the weekly a
3 No because allowable increase is up to 30 for small sheds.
Objective function would increase by 70*20 = 1400

4 The small shed needs to increase by more than 30 to change the optimal.

5 If the weekly ad budget increases by 50 which is allowable, then profit increases by shadow price $4 * 50 = $200
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?

price $4 * 50 = $200
 3.21 (note: I have transposed rows and columns - the problem can be worked either way.)
Inputs
Cargo Type A B C D E
Value of freight per pound $8.00 $6.00 $3.50 $5.75 $9.50
Volume per pound ( cu. Ft) 3.0 2.7 6.3 8.4 5.5
Pounds available 210 150 90 120 130

Maximum volume (cu. Ft) 2500

Maximum lbs. 900

Decisions number of sheds built

Cargo Pound 210 150 0 0 125.4545

Objective
Maximize revenue - ad costs $5,922

Constraints LHS (Cell reference) RHS

Max weight 735.454545454545 <= 900
Space 2500 <= 2500
Pounds available 210 <= 210
150 <= 150
0 <= 90
0 <= 120
125.454545454545 <= 130
0 <= 340
250 <= 250
 F G
$5.25 $8.60
4.9 3.1 3-21 A small trucking company is determining the composition of
340 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?

0 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
Total Fuel cells prodced per day

Cells needed 2100

Max ratio Non-Union to Union 0.8
Max ratio Temp to Union 0.2
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
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.
Decisions number of sheds built The demand for October is 800 cases and order quantities are expected to increase by 20% each month
Large 30 --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
Small 70 inventory at the end of each month.
Objective
Maximize revenue - ad costs $0 Determine the minimum cost production schedule for the next 4 months (October through January) if
the company must satisfy demand each month.

Constraints LHS (Cell reference) RHS

Input 5.14
Distance, Demand and Supply
TO:
FROM: Columbia Albany Springfield Pleasatnburg Supply of cars
Parris 50 30 60 70 35
Butler 20 80 10 90 60
Century 100 40 80 30 25
Demand for cars 30 45 25 20
5-14 The Southern Rail Company ships coal by rail 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 cars.
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
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?
Problem 2-26
Inputs
Mission Rustic Available
Material cost $340 $430 $30,000
Labor hours 15 20 1200 2-26 A furniture cabinet maker produ
Profit $910 $1,200 televisions. The Mission-style cabinet
produce, and it yields a profit of $910
materials and 20 hours to produce, a
Decisions Number produced Mission Rustic $30,000 to spend on materials. To en
0 60 maximize its profit but at the same ti
available labor hours available must b
cabinets to be made?
Objective
Maximize Profit $72,000 Run a sensitivity report and answer t
questions without changing inputs an

Constraints used (Cell reference) available (RHS) 1. Explain why the upper limit for the
Material costs $25,800 <= $30,000 2. If the profit per unit for rustic cabi
Labor hours 1200 <= 1200 producing them?

3. If the budget for materials increas

the optimal product mix change? Wo
4. If labor hours available decreased
Would the optimal product mix chan

5. If labor hours available increased b

optimal product mix change? Would
 ture cabinet maker produces two types of cabinets that house and hide plasma
The Mission-style cabinet requires $340 in materials and 15 labor hours to
nd it yields a profit of $910 per cabinet. The Rustic-style cabinet requires $430 in
nd 20 hours to produce, and it yields a profit of $1,200. The firm has a budget of
spend on materials. To ensure full employment, the firm wishes to plan to
s profit but at the same time to keep all 30 workers fully employed, so all 1,200
bor hours available must be used. What is the best combination of furniture
be made?

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

why the upper limit for the coefficient of Mission cabinets is 1E+30 (infinity)

ofit per unit for rustic cabinets increased, at what profit would we consider
hem?

dget for materials increased by $1000 would the shadow price change? Would
product mix change? Would the profit for the optimal solution change?
hours available decreased by 100 would the shadow price for labor hours change?
optimal product mix change? Would the profit for the optimal solution change?

hours available increased by 200 would the shadow price change? Would the
duct mix change? Would the profit for the optimal solution change?
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

Objective Maximize profit

Constraints used (Cell reference) available (RHS)

Wiring time
Drilling time
Assemble time
Inspection time

Minimum needed EC221 EC496 NC455 NC791

Produced (Cell reference)

Minimum (RHS
Capasity (Hours
15000 2.41 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 to the left Formulate this problem and solve it by using Excel.
Your solution should honor all constraints and maximize the profit.

Refer to problem 2.41 completed in Week 1 problem set. 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?
our highly technical
h of the products
fore being
n. The time
he available time
uction levels for
re summarized in
e it by using Excel.
aximize the profit.

blem set. Using

and answer the
all questions

s long as the unit

0 would the
ct mix change?