Monday, February 6, 2017
Wednesday, April 10, 2013
Supply-chain-management by sunil chopra,,,,
Excel Problems
1. Mr. Smith, an avid reader, must decide on
which books to take with him during a long flight. Mr. Smith will use his carryon bag which can
hold a maximum of 20 pounds. Mr. Smith has
attached a satisfaction index for each book, based on a 5-point scale (1 = low
satisfaction, 5 =high satisfaction), as shown below.
Book Satisfaction Index Weight (lbs.)
Marketing 4 4
History 5 3
Statistics 1 3
Management 3 5
Finance 3 4
Economics 2 3
Excel 5 4
Access 4 5
Which books should Mr. Smith
take with him on the flight to maximize his satisfaction index?
2. Six products are currently waiting processing
on a single machine. The machine is
available today for a maximum of 25 hours.
The processing time per hour for each product along with the profit
contribution per product is show below.
Product Profit
Contribution Processing Time
(Hours)
A $55 5
B $70 4
C $45 6
D $60 5
E $50 3
F $65 6
Which products should be processed
to maximize the total profit contribution?
3. Refer to question 2. Assume that the following conditions must be
met:
a) We want to ensure that exactly
one of these products: A, B, or C must be processed.
b) We want to ensure that if
product E is processed then product F must be processed?
Which products should be
processed to maximize the total profit contribution?
4. A decision maker is provided with 5
different potential projects and must determine which projects to choose. The projects require different amounts of
capital and different expected net present values (NPV) over the next three
years. Determine which set of projects
should be selected in order to achieve the maximum net present value if the
decision maker has $150,000 available for investment each year.
NVP Capital Required (in $000s)
Project (in $000s) Year 1 Year 2 Year
3
1 140 70 20 25
2 180 85 40 15
3 120 60 20 20
4 80 30 30 15
5 200 50 15 10
5. Refer to problem 4. Determine which set of projects should be
selected in order to achieve the maximum net present value if the following two
conditions must also be met:
a) If project 1 is selected,
then project 2 must be selected, and vice versa.
b) Since projects 4 and 5
require outsourcing various operations, the decision maker wants at most one of
these projects to be included in the solution and not both.
6. A company currently has two factories:
F1 and F2, and three retail outlets: R1, R2, and R3. The shipping costs per unit along with the
monthly capacity and demand requirements are summarized below:
Shipping
Cost Per Unit
R1 R2 R3 Supply
F1 $3 $2 $4 100
F2 $1 $3 $5 200
Demand 100 100 200
The firm has decided to
build a new factory to expand its productive capacity. The two sites being considered are Philadelphia Pittsburgh
R1 R2 R3 Supply Fixed
Cost
Which of the new locations
will yield the lowest cost in combination with the existing factories and
retail outlets?
7. A company produces four products each
of which must undergo assembly, inspection, and packaging. The information below summarizes the hours
required for each operation by each product, the total hourly capacity for each
operation, and the fixed cost incurred by each product if it is produced.
Hours Required
Available
Operation Product 1 Product 2 Product
3 Product 4 Hours
Assembly 3 4 5 4 500
Inspection 2 1 3 2 300
Packaging 1 1 2 2 200
Fixed Cost $1100 $1000 $900 $850
Each unit of product 1 sold will contribute $60 to
profit, and each unit of products 2, 3, and 4 contributes $65, $70, and $55,
respectively. What is the optimal product
mix?
8. A
city is reviewing the location of its fire stations. The city is made up of a
number of districts, as illustrated below.
A fire station can be placed in any district and is able to handle the
fires for both its neighborhood and any adjacent neighborhood. The objective is
to minimize the number of fire stations used.
9. An investor is considering 7 different
stocks: A, B, C, D, E, F, and G. The expected annual return for each stock is
provided as follows:
Annual
Stock Return
A 9.5%
B 8.0%
C 7.0%
D 10.0%
E 8.0%
F 9.0%
G 10%
The investor has imposed the
following restrictions regarding the composition of the portfolio:
·
the portfolio must include exactly one of the
following stocks: either A or B.
·
if stock B is selected, then stock F must also be
selected.
·
if stock D is selected, then stock E must be
excluded.
Which stocks should be
included in the portfolio to maximize annual return?
10. A company is considering producing 8
different products for the upcoming holiday season. The profit contribution per product is
illustrated below:
Product Profit
($)
1 10
2 12
3 7
4 15
5 8
6 9
7 13
8 11
The marketing manager has imposed
the following restrictions on the production mix:
·
since products 1, 2, and 3 are electronic gadgets,
at least two of these products must be in the production mix..
·
since products 4 and 5 are kids oriented, the
production mix must include no more than one of these products.
·
if product 7 is included in the mix, then product 8
must be included, and vice versa.
Which products should be
included in the mix to maximize profit?
11. A developer wishes to expand an office
complex and needs to determine how many small, medium, and large offices to
include in the expansion. Each small
office requires 400 square feet, each medium office requires 700 square feet,
and each large office requires 1200 square feet. The current square footage available for
expansion is 35,000 square feet. The
developer expects to pay $10,000 for each small office, $20,000 for each medium
office, and $35,000 for each large office.
The developer wishes to limit his expenditures to $500,000. How many of each type of office should be
built if the following goals, stated in no particular order, must be met?
Goal 1: The expansion should include
approximately 6 small offices.
Goal 2: The expansion should
include approximately 11 medium offices.
Goal 3: The expansion should
include approximately 15 large offices.
Goal 4: The expansion should
consist of approximately 35,000 square feet.
Goal 5: The expansion should
cost approximately $500,000.
12. Consider the following
linear programming g problem:
Max: 500A + 700B
Subject to:
2A + 3B ≤ 35 (assembly
hours)
3A + 5B ≤ 40 (Machine
hours)
Reformulate and solve this
problem as a goal programming problem if the following goals, stated in no
particular order, must be met?
Goal 1: Produce at
least 15 units of each product.
Goal 2: Avoid overtime
in the assembly and machine departments.
Goal 3: Achieve at
least $100,000 in profit.
13. Consider the following
linear programming problem:
Max: 500A + 700B
Subject to:
2A + 3B ≤ 35 (assembly
hours)
3A + 5B ≤ 40 (Machine
hours)
Reformulate and solve this
problem as a goal programming problem if the following goals, with their
associated weights, must be met?
Goal 1: Produce at
least 15 units of product A; Weight 15
Goal 2: Produce at
least 15 units of product B; Weight 15
Goal 3: Minimize
overtime in assembly department; Weight 25
Goal 4: Minimize
overtime in machine department; Weight 25
Goal 5: Achieve at
least $100,000 in profit; Weight 20
14. Consider the following
linear programming g problem:
Max: 500A + 700B
Subject to:
2A + 3B ≤ 35 (assembly
hours)
3A + 5B ≤ 40 (Machine
hours)
Reformulate and solve this
problem as a goal programming problem if the following prioritized goals must
be met?
Goal Priority
Product at least 15 units of each product P1
Minimize overtime in
assembly and machine departments P2
Achieve at least
$100,000 in profit P3
15. A marketing manager is
considering the following advertising media to promote a new
product.
Type Audience Reached/Ad Cost/Ad Maximum No. of Ads
TV 50,000 $3000 10
Radio 25,000 $1000 15
Newspaper 10,000 $500 30
The marketing manager has
established the following goals, stated in no particular order, for the
advertising campaign:
Goal 1: Reach at least
500,000 individuals
Goal 2: Limit the
total spending to $150,000
Formulate and solve
this goal programming problem.
16. A marketing manager is
considering the following advertising media to promote a new
product.
Type Audience Reached/Ad Cost/Ad Maximum No. of Ads
TV 50,000 $3000 10
Radio 25,000 $1000 15
Newspaper 10,000 $500 30
The marketing manager has
established the following goals and weights:
Goal 1: Reach at least
500,000 individuals; Weight 40
Goal 2: Limit the
total spending to $150,000; Weight 60
Formulate and solve
this goal programming problem.
17. Set up and solve the
following nonlinear programming problem using Excel:
Max: 10X1 + 15X2 + 5X3 + 2X33
Subject to
X1 + X2 + X3
≤ 50
2X1 + 3X2 ≤
150
X1, X2, X3≥
0
18. Set up and solve the following nonlinear
programming problem using Excel:
Max: X1 + 2X2 + 3X1X2 + 5X33
Subject to
X1 + X2 + X3
≤ 75
X1 + X2 ≥ 15
2X1 + 2X3 ≤ 40
X1, X2, X3
≥ 0
19. Set up and solve the following nonlinear
programming problem using Excel:
Max: (X1 – 5)2 + (X2 – 7)2
Subject to
X1 ≤ 15
X2 ≤ 15
X1 + X2 ≥ 10
X1, X2 ≥ 0
20. Consider
the following profit expected from each dollar (Xi) spent in the
following advertising media:
Medium Profit
TV 0.0005Xi3
Radio 0.0025Xi2
Newspaper 0.01Xi
How should the advertising budget be allocated if the
company wants to spend at least $1000 on each medium without exceeding its $10,000
budget?
Subscribe to:
Posts (Atom)