I have attached a word document named OSCM471571_Homework2_Sp23 x which is the hw along with its excel template named hw2 template. I also attached solved practice problems along with their excel spreadsheets solved as well to be used as a sample. The hw needs to be submitted as a word doc.
OSCM 471/571 Optimization and Decision Support Modeling for Business
Homework 2, Spring 2023
Notice for Homework 2
Instructor: Seokjun Youn (
syoun@email.arizona.edu
)
·
Due date: Thursday 2/16, 11:59 pm
·
Please submit your files to D2L > Assignments > Homework 2
1.
A Word file (or PDF) with your answers combined into a single document.
2.
An Excel spreadsheet template with your answers (for some subquestions).
·
This homework is made up of FIVE questions (20 pts):
·
Q1: 3 subquestions (2 pts)
·
Q2: 3 subquestions (5 pts)
·
Q3: 2 subquestions (5 pts)
·
Q4: 1 question
(4 pts)
·
Q5: 1 question (4 pts)
·
Students may choose either handwriting or word processing (or both).
· Handwriting: please properly scan or take photos and organize them into one file before uploading in D2L.
·
Please write down your solutions stepbystep for partial credit.
·
You may use:
· Your textbook and notes from the class.
· Notes or sources from a related class or internet source.
· Discussion with the instructor.
· Voluntary, mutual, and cooperative discussion with other students currently taking the class.
·
You may not use:
· Solution manuals (printed or electronic).
· Copying from other students in this class, including expecting them to reveal their solutions in “discussion.”
· It is fine if your answer is not 100% correct. However, if you do not put enough effort to the assignment, your score for this homework will be lower than your expectation. So, please try to convince your logic to instructor.
Your Name:
1. The shaded area in the following graph represents the feasible region of a linear programming problem whose objective function is to be maximized, where x1 and x2 represent the level of the two activities.
Label each of the following statements as True or False, and then justify your answer based on the graphical method. In each case, give an example of an objective function that illustrates your answer.
a. If (3,3) produces a larger value of the objective function than (0, 2) and (6, 3), then (3, 3) must be an optimal solution.
Answer:
b. If (3, 3) is an optimal solution and multiple optimal solutions exist, then either (0, 2) or (6, 3) must also be an optimal solution.
Answer:
c. The point (0, 0) cannot be an optimal solution.
Answer:
2. Larry Edison is the director of the Computer Center for Buckly College. He now needs to schedule the staffing of the center. It is open from 8 am until midnight. Larry has monitored the usage of the center at various times of the day and determined that the following number of computer consultants are required.
Two types of computer consultants can be hired: fulltime and parttime. The fulltime consultants work for eight consecutive hours in any of the following shifts: morning (8 am –4 pm), afternoon (noon–8 pm), and evening (4 pm –midnight). Fulltime consultants are paid $14 per hour.
Parttime consultants can be hired to work any of the four shifts listed in the table. Parttime consultants are paid $12 per hour.
An additional requirement is that during every time period, there must be at least two fulltime consultants on duty for every parttime consultant on duty.
Larry would like to determine how many fulltime and parttime consultants should work each shift to meet the above requirements at the minimum possible cost.
a. Which category of linear programming problem does this problem fit? Why?
Answer:
b. Formulate and solve a linear programming model for this problem on a spreadsheet.
·
Please include the screenshot of your final spreadsheet model here.
Answer:
c. Summarize the model in algebraic form.
Answer:
3. The Weigelt Corporation has three branch plants with excess production capacity. Fortunately, the corporation has a new product ready to begin production, and all three plants have this capability, so some of the excess capacity can be used in this way. This product can be made in three sizes—large, medium, and small—that yield a net unit profit of $420, $360, and $300, respectively. Plants 1, 2, and 3 have the excess capacity to produce 750, 900, and 450 units per day of this product, respectively, regardless of the size or combination of sizes involved.
The amount of available inprocess storage space also imposes a limitation on the production rates of the new product. Plants 1, 2, and 3 have 13,000, 12,000, and 5,000 square feet, respectively, of inprocess storage space available for a day’s production of this product. Each unit of the large, medium, and small sizes produced per day requires 20, 15, and 12 square feet, respectively.
Sales forecasts indicate that if available, 900, 1,200, and 750 units of the large, medium, and small sizes, respectively, would be sold per day.
At each plant, some employees will need to be laid off unless most of the plant’s excess production capacity can be used to produce the new product. To avoid layoffs if possible, management has decided that the plants should use the same percentage of their excess capacity to produce the new product.
Management wishes to know how much of each of the sizes should be produced by each of the plants to maximize profit.
a. Formulate and solve a linear programming model for this mixed problem on a spreadsheet.
·
Please include the screenshot of your final spreadsheet model here.
Answer:
b. Express the model in algebraic form.
Answer:
4. The CostLess Corp. supplies its four retail outlets from its four plants. The shipping cost per shipment from each plant to each retail outlet is given below.
Plants 1, 2, 3, and 4 make 10, 20, 20, and 10 shipments per month, respectively. Retail outlets 1, 2, 3, and 4 need to receive 20, 10, 10, and 20 shipments per month, respectively.
The distribution manager, Randy Smith, now wants to determine the best plan for how many shipments to send from each plant to the respective retail outlets each month. Randy’s objective is to minimize the total shipping cost.
Formulate this problem as a transportation problem on a spreadsheet and then use Solver to obtain an optimal solution.
a.
Please include the screenshot of your final spreadsheet model here.
Answer:
5. Four cargo ships will be used for shipping goods from one port to four other ports (labeled 1, 2, 3, 4). Any ship can be used for making any one of these four trips. However, because of differences in the ships and cargoes, the total cost of loading, transporting, and unloading the goods for the different ship–port combinations varies considerably, as shown in the following table.
The objective is to assign the four ships to four different ports in such a way as to minimize the total cost for all four shipments. Formulate and solve this problem on a spreadsheet.
a.
Please include the screenshot of your final spreadsheet model here.
Answer:
2/5
image2
image3
image4
image1
>Q2b
Full Time
Full Time
Part Time
Part Time
Part Time
pm
per Shift
Total
? (
yes, 0=no)
8amnoon noon4pm >=
>=
>=
Total
Total
Full Time
Part Time
Cost
>=
>=
>=
>=
Full Time  Part Time  
8am  4  noon8pm  4pmmidnight  8amnoon  noon4pm  4pm8pm  8pmmidnight  
Cost  
Total  
Shift Covers  Time of Day  1  =  Working  Needed  
>=  
Workers per Shift  
Times Total  
Q
3a
Total
Space
Space
Large
Medium
Small
Required
1
<=
<= <=
<= <=
<= <= <=
=
Percentage of Plant 1 Capacity=
Large  Medium  Small  
Unit Profit  
Space  Required  
(sq.ft. per unit)  
Production  Produced  Capacity  Available  
Plant  <=  
Plant 2  
Plant 3  
Total Produced  
Total Profit  
Sales Forecast  
Percentage of Plant 1 Capacity  Percentage of Plant 2 Capacity  
Percentage of Plant 3 Capacity 
Q4
1
2
3
4
1
2
3
4
ments
Retail Outlet
1
2
3
4
1
=
2
=
3
=
4
=
=
=
=
=
Unit Cost  Retail Outlet  
Ship  
Total Shipped  Supply  
Total Received  
Total Cost  
Demand 
Q5
Unit Cost 1
2
3
4
1
Ship2
3
4
Port
Total
1
2
3
4
Assignments
Supply
1
=
Ship2
=
3
=
4
=
=
=
=
=
Total Cost
Demand
Port  
Assignments  
Total Assigned 
Module 31 Practice Problems
OSCM 471/
5
71 Optimization and Decision Support Modeling for Business
Study Materials:
Lecture 31: LP Formulation and Applications
1.
(ResourceAllocation Problem) A cargo plane has three compartments for storing cargo: front, center, and back. These compartments have capacity limits on both weight and space, as summarized below.
Furthermore, the weight of the cargo in the respective compartments must be the same proportion of that compartment’s weight capacity to maintain the balance of the airplane.
The following four cargoes have been offered for shipment on an upcoming flight as space is available.
Any portion of these cargoes can be accepted. The objective is to determine how much (if any) of each cargo should be accepted and how to distribute each among the compartments to maximize the total profit for the flight.
a. Formulate and solve a linear programming model for this mixed problem on a spreadsheet.
Answer:
b. Express the model in algebraic form.
Answer:
Let
xij = tons of cargo
i stowed in compartment
j (
i = 1,2,3,4; j = F, C, B)
Maximize Profit = $3
2
0
(
x1F +
x1C +
x1B) + $
40
0(
x2F +
x2C +
x2B)
+ $3
60
(
x3F +
x3C +
x3B) + $290(
x4F +
x4C +
x4B)
subject to
x1F +
x2F +
x3F +
x4F ≤ 12 tons
x1C +
x2C +
x3C +
x4C ≤ 18 tons
x1B +
x2B +
x3B +
x4B ≤
10
tons
x1F +
x1C +
x1B ≤
20
tons
x2F +
x2C +
x2B ≤ 16 tons
x3F +
x3C +
x3B ≤
25
tons
x4F +
x4C +
x4B ≤ 13 tons
50
0
x1F +
70
0
x2F + 600
x3F + 400
x4F ≤ 7,000 cubic feet
500
x1C + 700
x2C + 600
x3C + 400
x4C ≤ 9,000 cubic feet
500
x1B + 700
x2B + 600
x3B + 400
x4B ≤ 5,000 cubic feet
(
x1F +
x2F +
x3F +
x4F) / 12 = (
x1C +
x2C +
x3C +
x4C) / 18
(
x1F +
x2F +
x3F +
x4F) / 12 = (
x1B +
x2B +
x3B +
x4B) / 10
and
x1F ≥ 0,
x1C ≥ 0,
x1B ≥ 0,
x2F≥ 0,
x2C ≥ 0,
x2B ≥ 0,
x3F ≥ 0,
x3C ≥ 0,
x3B ≥ 0,
x4F ≥ 0,
x4C ≥ 0,
x4B ≥ 0.
2.
(CostBenefitTradeoffProblem) Web Mercantile sells many household products through an online catalog. The company needs substantial warehouse space for storing its goods. Plans now are being made for leasing warehouse storage space over the next five months. Just how much space will be required in each of these months is known. However, since these space requirements are quite different, it may be most economical to lease only the amount needed each month on a monthbymonth basis. On the other hand, the additional cost for leasing space for additional months is much less than for the first month, so it may be less expensive to lease the maximum amount needed for the entire five months. Another option is the intermediate approach of changing the total amount of space leased (by adding a new lease and/or having an old lease expire) at least once but not every month.
The space requirement and the leasing costs for the various leasing periods are as follows.
The objective is to minimize the total leasing cost for meeting the space requirements.
a. Indicate why this is a cost–benefit–tradeoff problem by identifying both the activities and the benefits being sought from these activities.
Answer:
The activities are leasing space in each month for a number of months. The benefit is meeting the space requirements for each month.
b. Identify verbally the decisions to be made, the constraints on these decisions, and the overall measure of performance for the decisions.
Answer:
The decisions to be made are how much space to lease and for how many months. The constraints on these decisions are the minimum required space. The overall measure of performance is cost which is to be minimized.
c. Formulate a spreadsheet model for this problem. Identify the data cells, the changing cells, the objective cell, and the other output cells. Also show the Excel equation for each output cell expressed as a SUMPRODUCT function. Then use Solver to solve the model.
Answer:
Data cells: B4:P8, B10:P10, and S4:S8
Changing cells: B13:P13
Objective cell: S13
Output cells: Q4:Q8
d. Summarize the model in algebraic form.
Answer:
Let
xij = square feet of space leased in month
i for a period of
j months.
for
i = 1, … , 5 and
j = 1, … , 6
i.
Minimize
C = $650(
x11 +
x21 +
x31 +
x41 +
x51) + $1,000(
x12 +
x22 +
x32 +
x42)
+$1,
35
0(
x13 +
x23 +
x33) + $1,600(
x14 +
x24) + $1,900
x
15
subject to
x11 +
x12 +
x13 +
x14 +
x15 ≥
30
,000 square feet
x12 +
x13 +
x14 +
x15 +
x21 +
x22 +
x23 +
x24 ≥ 20,000 square feet
x13 +
x14 +
x15 +
x22 +
x23 +
x24 +
x31 +
x32 +
x33 ≥ 40,000 sq. feet
x14 +
x15 +
x23 +
x24 +
x32 +
x33 +
x41 +
x42 ≥ 10,000 square feet
x15 +
x24 +
x33 +
x42 +
x51 ≥ 50,000 square feet
and
xij ≥ 0, for
i = 1, … , 5 and
j = 1 , … , 6
i.
3.
(Mixed Problem) Comfortable Hands is a company that features a product line of winter gloves for the entire family—men, women, and children. They are trying to decide what mix of these three types of gloves to produce.
Comfortable Hands’s manufacturing labor force is unionized. Each fulltime employee works a 40hour week. In addition, by union contract, the number of fulltime employees can never drop below 20. Nonunion, parttime workers also can be hired with the following unionimposed restrictions: (1) each parttime worker works 20 hours per week and (2) there must be at least two fulltime employees for each parttime employee.
All three types of gloves are made out of the same 100 percent genuine cowhide leather. Comfortable Hands has a longterm contract with a supplier of the leather and receives a 5,000squarefoot shipment of the material each week. The material requirements and labor requirements, along with the gross profit per glove sold (not considering labor costs), are given in the following table.
Each fulltime employee earns $13 per hour, while each parttime employee earns $10 per hour.
Management
wishes to know what mix of each of the three types of gloves to produce per week, as well as how many fulltime and parttime workers to employ. They would like to maximize their net profit —their gross profit from sales minus their labor costs.
a. Formulate and solve a linear programming model for this problem on a spreadsheet.
Answer:
b. Summarize this formulation in algebraic form.
Answer:
Let
M =number of men’s gloves to produce per week,
W = number of women’s gloves to produce per week,
C = number of children’s gloves to produce per week,
F = number of fulltime workers to employ,
PT = number of parttime workers to employ.
Maximize Profit = $8M + $10
W + $6
C – $13(40)
F – $10(20)
PT
subject to 2
M + 1.5
W +
C ≤ 5,000 square feet
30
M +
45
W + 40
C ≤ 40(60)
F + 20(60)
PT hours
F ≥ 20
F ≥ 2
PT
and
M ≥ 0,
W ≥ 0,
C ≥ 0,
F ≥ 0 (and integer),
PT ≥ 0 (and integer).
4.
(Transportation Problem: Producing and Distributing AEDs) Heart Start produces automated external defibrillators in each of two different plants (A and B). The unit production costs and monthly production capacity of the two plants are indicated in the table below. The automated external defibrillators are sold through three wholesalers. The shipping cost from each plant to the warehouse of each wholesaler along with the monthly demand from each wholesaler are also indicated in the table. The management of Heart Start now has asked their top management scientist (you) to address the following two questions. How many automated external defibrillators should be produced in each plant, and how should they be distributed to each of the three wholesaler warehouses so as to minimize the combined cost of production and shipping? Formulate and solve a linear programming model in a spreadsheet.
Unit Shipping Cost 
Unit 
Monthly 

Warehouse 1 
Warehouse 2 
Warehouse 3 
Production 
Production 

Plant A 
$22 
$14 
$30 
$600 
100 

Plant B 
$16 
$20 
$24 
$625 
120 

Monthly Demand 
80 
60 
70 
Answer:
This is a transportation problem as described in Section 3.5 of the text.
The Solver information and solved spreadsheet are shown below.
Thus, from Plant A they should ship 40 to Warehouse 1 and 60 units to Warehouse 2, from Plant B they should ship 40 units to Warehouse 1 and 70 units to Warehouse 3, giving an overall total cost of $132,790.
5.
(Transportation Problem) The Childfair Company has three plants producing child push chairs that are to be shipped to four distribution centers. Plants 1, 2, and 3 produce 12, 17, and 11 shipments per month, respectively. Each distribution center needs to receive 10 shipments per month. The distance from each plant to the respective distribution centers is given below.
The freight cost for each shipment is $100 plus 50 cents/mile. How much should be shipped from each plant to each of the distribution centers to minimize the total shipping cost? Formulate this problem as a transportation problem on a spreadsheet and then use Solver to obtain an optimal solution.
Answer:
6.
(Transportation Problem) The Onenote Co. produces a single product at three plants for four customers. The three plants will produce 60, 80, and 40 units, respectively, during the next week. The firm has made a commitment to sell 40 units to customer 1, 60 units to customer 2, and at least 20 units to customer 3. Both customers 3 and 4 also want to buy as many of the remaining units as possible. The net profit associated with shipping a unit from plant i for sale to customer j is given by the following table.
Management wishes to know how many units to sell to customers 3 and 4 and how many units to ship from each of the plants to each of the customers to maximize profit. Formulate and solve a spreadsheet model for this problem.
Answer:
7.
(Assignment Problem) Consider the assignment problem having the following cost table.
The optimal solution is A3, B1, C2, with a total cost of $10. Formulate this problem on a spreadsheet and then use Solver to obtain the optimal solution identified above.
Answer:
8.
(Assignment Problem: Bidding for Classes) In the MBA program at a prestigious university in the Pacific Northwest, students bid for electives in the second year of their program. Each student has 100 points to bid (total) and must take two electives. There are four electives available: Management
Science
,
Finance
,
Operations
Management, and
Marketing
. Each class is limited to 5 students. The bids submitted for each of the 10 students are shown in the table below.
10
20
20
20
45
5
20
5
30
30
10
50
50
0
20
10
0
25
25
35
15
35
15
60
10
10
20
Student 
Management Science 
Finance 
Operations Management 
Marketing 

George 
60  10  20  
Fred 
40  
Ann 
45  5  
Eric 
50  25  
Susan 
30  
Liz 
0  
Ed 
70  
David 
35  15  
Tony 

Jennifer 
a. Formulate and solve a spreadsheet model to determine an assignment of students to classes so as to maximize the total bid points of the assignments.
Answer:
The Solver information and solved spreadsheet are shown below.
Thus, the 1’s in Assignment (C18:F27) show the assignments that should be made, achieving a total of 705 points.
b. Does the resulting solution seem like a fair assignment?
Answer:
No. For example, Eric did not get into Management Science despite bidding 50 points, while Ann got in with only 45 points. Also, Eric got into classes worth only 45 total bid points to him while Liz got classes worth 100 bid points to her.
c. Which alternative objectives might lead to a fairer assignment?
Answer:
Perhaps maximizing the minimum total number of bid points achieved by each student.
2 / 10
image4
image5
image6.emf
image7.emf
image8.emf
image9
image10
image11.emf
image12
image13.emf
image14
image15.emf
image16
image17.emf
image18.emf
image1
image2
image3.emf
>
Ch
3_Prob
11
(cf/ton)
0
0
0
0
0
CargoTotal
Volume
Cargo 2
Cargo 3
Cargo 4
Weight
Volume
Capacity
0
0
12
7,000
0
.9999999999
<=
<= 9,000
ack
0
0
<=
<= 5,000
Total12.9999999994
5.0000000001
<= <= <= <=
vailable
(tons)
20
13
,3
%
=
100%
100%
=
100%
Cargo  Cargo 2  Cargo 3  Cargo  4  
Volume  5  0  70  60  40  
Profit (per ton)  $3  20  $  400  $360  $2  9  
Total  Weight  
Placement (tons)  Cargo 1  Capacity  
Front  7.333  4.667  12  <  >  >  =  7,000  <=  
Center  12.9999999994  1.667  3.333  17  18  9,000  
B  5.0000000001  4.9999999998  9.9999999999  10  5,000  
9.0000000003  13  
Total Profit  
A  16  25  $13  30  
Percentage of Front Capacity  100  Percentage of Middle Capacity  
Percentage of Back Capacity 
Ch3_Prob2
Total
1
1
1
1
1
2
2
2
2
3
3
3
4
4
5
1
2
3
4
5
1
2
3
4
1
2
3
1
2
1
(sq. ft.)
1
1
1
1
1
>=
30,000
1
1
1
1
1
1
1
1
30,000
>=
1
1
1
1
1
1
1
1
1
>=
40,000
1
1
1
1
1
1
1
1
30,000
>=
1
1
1
1
1
,000
>=
50,000
of Lease
5
0
0
$65
$135
$160
$65
$100
$135
$65
$100
$65
0
0
0
0
30,000
0
0
0
0
10,000
0
0
0
0
20,000
,650,000
Month Covered by Lease?  Space  
Month of Lease:  Leased  Required  
Length of Lease:  (sq. ft.)  
Month 1  30,000  
Month 2  20,000  
Month 3  40,000  
Month 4  10,000  
Month 5  50  
Cost  $6  $10  $1  35  $16  $1  90  $100  
(per sq. ft.)  
Total Cost  
Lease (sq. ft.)  $7 
Ch3_Prob3
$10
$6
(per hour)
$13
$10
40
20
20
0
Usage
Resource
Resource
of Activity
Available
2
1
<=
30
40
<=
Men’s
Women’s
Children’s
(per week)
0
0
Gross Profit
Full Time
Part Time
Labor Cost
,400
25
12
,440
>=
20
2
25
>=
24
Men’s  Women’s  Children’s  
Gross Profit  $8  
(per glove)  
Full Time  Part Time  
Labor Cost  
Hours worked per week  
Labor Cost (per week)  $5  $20  
Resource  
per  Unit  Used  
Material (sq. ft.)  1.5  4960  5000  
Labor (minutes)  45  74400  74,400  
Production  24  80  
$19,840  
$  15  
Employees  Net Profit  $4  
Minimum Full Time  
Times PartTime 
Ch3_Prob4
Unit
Production
Cost
Capacity
:H12
A
$16
$20
Total
Monthly
Shipped
Warehouse 1
Warehouse 2
Warehouse 3
Out
Capacity
Plant A
40
60
0
100
<=
100
Plant B
40
0
70
<=
80
60
70
=
=
=
80
60
70
AED Production and Distribution  
Shipping  Range Name  Cells  
Cost (per unit)  Warehouse 1  Warehouse 2  Warehouse 3  Monthly  H  11  
Plant  $22  $14  $30  $  600  Monthly  Demand  C15:E15  
Plant B  $24  $625  OverallCost  H18  
UnitShippingCost  C5:E6  
TotalProductionCost  H16  
Units  Shipped  TotalShipped  Out  F11:F12  
TotalShippingCost  H17  
TotalToWarehouse  C13:E13  
110  120  UnitProductionCost  H5:H6  
Total to Warehouse  UnitsShipped  C11:E12  
Monthly Demand  
Total Production Cost  $128,750  
Total Shipping Cost  $4,040  
Overall Cost  $132,790 
Ch3_Prob5
1
2
3
4
1
400
Plant2
600
3
600
800
$100
Distribution Center
1
2
3
4
1
Plant2
3
$400
$500
Distribution Center
1
2
3
4
1
0
0
2
10
12
=
12
2
0
9
0
=
17
3
10
1
0
0
11
=
11
10
10
10
=
=
=
=
Total Cost
Demand
10
10
10
10
Distance (miles)  Distribution Center  
800  1,300  700  
1,100  1,400  1,000  
1,200  900  
Fixed Cost  
Cost per Mile  $0.50  
Unit Cost  
$500  $750  $300  $450  
$650  $800  $400  $600  
$700  $550  
Shipments  
Total Shipped  Supply  
7.9999999997  16.9999999997  
Total Received  9.9999999997  
$20,200 
Ch3_Prob6
1
2
3
4
1
$800
$700
$500
2
$500
$200
$100
$300
3
$600
$400
$300
$500
Customer
1
2
3
4
Total Shipped
Supply
1
0
0
0
60.0000000001
=
60
2
40
0
0
=
80
3
0
0
20
=
40
Total Received
40
60.0000000001
20
=
=
>=
Total Cost
40
60
20
Unit Profit  Customer  
$200  
60.0000000001  
40.0000000001  80.0000000001  
20.0000000008  40.0000000008  
60.0000000009  
Commitment  $90,000 
Ch3_Prob7
Unit Cost 1
2
3
A
$5
$7
$4
B
$3
$6
$5
C
$2
$3
$4
s
Job
Total
1
2
3
Assignments
Supply
A
0
0
1
1
=
1
B
1
0
0
1
=
1
C
0
1
0
1
=
1
1
1
1
=
=
=
Total Cost
Demand
1
1
1
$10
Job  
Person  
Assignment  
Total Assigned  
Ch3_Prob8
Range Name
Cells
Management
Assignment
60
10
10
20
Capacity
20
20
40
20
45
45
5
5
Points
50
20
5
25
Points
30
30
30
10
50
50
0
0
70
20
10
0
25
25
35
15
35
15
35
15
60
10
10
20
Management
Operations
Total
Classes
Student
Assignment
Science
Finance
Management
Marketing
Classes
Points
George
1
0
0
1
2
=
2
80
Fred
0
0
1
1
2
=
2
60
Ann
1
1
0
0
2
=
2
90
Eric
0
1
0
1
2
=
2
45
Susan
0
1
1
0
2
=
2
60
Liz
1
1
0
0
2
=
2
100
Ed
1
0
1
0
2
=
2
80
David
0
1
1
0
2
=
2
60
Tony
0
0
1
1
2
=
2
50
Jennifer
1
0
0
1
2
=
2
80
5
5
5
5
<= <= <= <=
Capacity
5
5
5
5
Bidding for  Classes  
Management  Operations  
Points  Science  Finance  Marketing  C18:F27  
George  C30:F30  
Fred  ClassesToTake  I18:I27  
Ann  C5:F14  
Eric  Student  K18:K27  
Susan  TotalClasses  G18:G27  
Liz  TotalInClass  C28:F28  
Ed  TotalPoints  I29  
David  
Tony  
Jennifer  
to Take  
Total in Class  
Total Points  705  
Module 32 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
1 / 7
Study Materials:
Lecture 32: LP Whatif Analysis
1. Consider the Big M Co. problem presented in Section 3.5, including the spreadsheet in Figure
3.10 showing its formulation and optimal solution.
There is some uncertainty about what the unit costs will be for shipping through the
various shipping lanes. Therefore, before adopting the optimal solution in Figure 3.10,
management wants additional information about the effect of inaccuracies in estimating
these unit costs.
Use Solver to generate the sensitivity report preparatory to addressing the following
questions.
a. Which of the unit shipping costs given in Table 3.9 has the smallest margin for error
without invalidating the optimal solution given in Figure 3.10? Where should the
greatest effort be placed in estimating the unit shipping costs?
Answer:
Module 32 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
2 / 7
All of the unit costs have a margin of error of 100 in at least one direction (increase or
decrease). Factory 1 to Customer 2 and Factory 2 to Customer 2 have the smallest margins
for error since it is 100 in both directions.
b. What is the allowable range for each of the unit shipping costs?
Answer:
The allowable range for Factory 1 to Customer 1 is Unit Cost≤ $800.
The allowable range for Factory 1 to Customer 2 is $800 ≤ Unit Cost ≤ $1,000.
The allowable range for Factory 1 to Customer 3 is Unit Cost ≥ $700.
The allowable range for Factory 2 to Customer 1 is Unit Cost ≥ $700
The allowable range for Factory 2 to Customer 2 is $800 ≤ Unit Cost ≤ $900.
The allowable range for Factory 2 to Customer 3 is Unit Cost ≤ $800.
c. How should the allowable range be interpreted to management?
Answer:
The allowable range for each unit shipping cost indicates how much that shipping cost can
change before you would want to change the shipping quantities used in the optimal solution.
d. If the estimates change for more than one of the unit shipping costs, how can you use
the sensitivity report to determine whether the optimal solution might change?
Answer:
Use the 100% rule for simultaneous changes in objective function coefficients. If the sum of
the percentage changes does not exceed 100%, the optimal solution definitely will still be
optimal. If the sum does exceed 100%, then we cannot be sure.
2. University Ceramics manufactures plates, mugs, and steins that include the campus name
and logo for sale in campus bookstores. The time required for each item to go through the
two stages of production (molding and finishing), the material required (clay), and the
corresponding unit profits are given in the following table, along with the amount of each
resource available in the upcoming production period.
Module 32 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
3 / 7
A linear programming model has been formulated in a spreadsheet to determine the
production levels that would maximize profit. The solved spreadsheet model and
corresponding sensitivity report are shown below.
For each of the following parts, answer the question as specifically and completely as is
possible without resolving the problem with Solver. Note: Each part is independent (i.e., any
change made in one part does not apply to any other parts).
a. Suppose the profit per plate decreases from $3.10 to $2.80. Will this change the
optimal production quantities? What can be said about the change in total profit?
Answer:
The decrease is within the allowable decrease, so the optimal production quantities stay the
same. Total profit will decrease by ($0.30)(300) = $90 to $2440.
b. Suppose the profit per stein increases by $0.30 and the profit per plate decreases by
$0.25. Will this change the optimal production quantities? What can be said about the
change in total profit?
Answer:
$0.30 is 0.30/0.65 = 46.2% of the allowable increase for steins.
$0.25 is 0.25/0.37 = 67.5% of the allowable decrease for plates.
46.2% + 67.5% > 100%, so the optimal production quantities may or may not change.
Module 32 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
4 / 7
The change in total profit can not be definitively determined since it is not certain whether
or not the production quantities change.
c. Suppose a worker in the molding department calls in sick. Now eight fewer hours are
available that day in the molding department. How much would this affect total profit?
Would it change the optimal production quantities?
Answer:
8 hours, or 480 minutes, is within the allowable decrease for molding, so the shadow price is
valid. The change in total profit is therefore ∆Profit = ($0.22)(–480) = –$105.60. The optimal
production quantities will change.
d. Suppose one of the workers in the molding department is also trained to do finishing.
Would it be a good idea to have this worker shift some of her time from the molding
department to the finishing department? Indicate the rate at which this would
increase or decrease total profit per minute shifted. How many minutes can be shifted
before this rate might change?
Answer:
The shadow price for finishing ($0.28) is higher than the shadow price for molding ($0.22), so
shifting minutes from molding to finishing would be beneficial, and would add $0.06 to total
profit per minute shifted. This rate will remain valid at least until the 100% rule is violated. If
x is the number of minutes shifted, the 100% rule will be violated when x/600 + x/2400 >
100%, or when x > 480 minutes.
e. The allowable decrease for the mugs’ objective coefficient and for the available clay
constraint are both missing from the sensitivity report. What numbers should be there?
Explain how you were able to deduce each number.
Answer:
300. The shadow price is 0 because there is slack in this constraint. The shadow price will
remain 0 so long as there is slack. There will remain slack so long as the righthand side
decreases no more than 300 minutes.
3. Consider the Super Grain Corp. case study as presented in Section 3.4, including the
spreadsheet in Figure 3.7 showing its formulation and optimal solution. Use Solver to
generate the sensitivity report. Then use this report to independently address each of the
following questions.
a. How much could the total expected number of exposures be increased for each
additional $1,000 added to the advertising budget?
Answer:
Module 32 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
5 / 7
The total number of expected exposures can not be increased by adding an additional $1,000
to the advertising budget.
b. Your answer in part a would remain valid for how large of an increase in the
advertising budget?
Answer:
This remains valid for any increases.
c. How much could the total expected number of exposures be increased for each
additional $1,000 added to the planning budget?
Answer:
The total number of expected exposures can be increased by 35,000 by adding an additional
$1,000 to the advertising budget.
d. Your answer in part c would remain valid for how large of an increase in the planning
budget?
Answer:
This remains valid for increases of up to $22,500.
Module 32 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
6 / 7
e. Would your answers in parts a and c definitely remain valid if both the advertising
budget and planning budget were increased by $100,000 each?
Answer:
Percentage of allowable increase for ad budget = (4,100 – 4,000) / ∞ = 0%
Percentage of allowable increase for planning budget = (1,100 – 1,000) / 22.5 = 444%
The sum is 444% > 100%, so the shadow prices may or may not be valid.
f. If only $100,000 can be added to either the advertising budget or the planning budget,
where should it be added to do the most good?
Answer:
$100,000 is beyond the allowable increase for the planning budget. Therefore, the total
impact of adding $100,000 to the planning budget can not be determined without resolving.
However, it would certainly be more worthwhile adding to the planning budget (35,000
additional exposures for each $1,000 spent up to $22,500) than adding to the advertising
budget which would not increase the expected number of exposures at all.
g. If $100,000 must be removed from either the advertising budget or the planning
budget, from which budget should it be removed to do the least harm?
Answer:
The $100,000 should be removed from the advertising budget. Since the shadow price is zero
for the advertising budget (and the allowable decrease is $225,000), this will have no impact
on the total number of exposures.
4. Reconsider the example illustrating the use of robust optimization that was presented in the
lecture note. Wyndor management now feels that the analysis described in the example was
overly conservative for three reasons: (1) it is unlikely that the true values of the parameters
𝐻𝐻𝐷𝐷3 and 𝐻𝐻𝑊𝑊3, will be as far as half an hour off of the original estimate, (2) it is even more
unlikely that both estimates will turn out to simultaneously lean toward the undesirable end
of their range of uncertainty, and (3) there is a bit of latitude in the constraint to compensate
for violating it by a tiny bit. Therefore, Wyndor management has asked its staff (you) to slove
the model again while using ranges of uncertainty that are half as wide as those used in the
lecture note below:
Module 32 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
7 / 7
a. Apply the procedure for robust optimization with independent parameters. What is
the resulting optimal solution and how much would this increase the total profit per
week?
Answer:
They should produce 1.385 doors and 6 windows per week. This increases the total profit by
$158 as compared to the more conservative solution.
b. If Wyndor would need to pay a penalty of $150 per week to the distributor if the
production rates fall below these new guaranteed minimum amounts, should Wyndor
use these new guarantees?
Answer:
Probably yes. The increase in profit that would be expected every week ($158) exceeds the
penalty of $150 that would need to be paid on occasion.
>Prob
1s
0
00
00
:E15
$800
$
0
$700
put
Customer 1
Customer 2
Customer 3
Out
Factory 1
2
0
12
12
Factory 2
0
6
9
15
=
15
10
8
9
=
=
=
10
8
9
Big M Company Distribution Problem  
Shipping  Cost  
(per Lathe)  Customer 1  Customer 2  Customer  3  Range  Name  Cell  
Factory 1  $7  0  $  9  $  8  OrderSize  C  1  5  
Factory 2  90  Out  H11:H  12  
ShippingCost  C5:E  6  
Total  TotalCost  H15  
Shipped  TotalShippedOut  F11:F12  
Units Shipped  Output  TotalToCustomer  C13:E13  
10  >  =  UnitsShipped  C11:E12  
Total To Customer  
Total Cost  
Order Size  $20,  500 
Prob1 Sens. Report
Allowable
Cell
Name
Cost
10
0
.0000000208
2
0
0
100.000000015
100.000000015
0
100.0000000206
800.0000000175
1E+30
100.0000000206
6
0
900.0000000015
9
0
1E+30
s
Final
Constraint
Allowable
Allowable
Cell
Name
Value
Increase
Decrease
12
0
12
0
1E+30
15
0
15
2
0
10
700.0000000011
10
0
10
8
8
0
2
9
9
0
2
Variable  Cells  
Final  Reduced  Objective  Allowable  
Value  Coefficient  Increase  Decrease  
$C$11  Factory 1 Customer 1  700.0000000007  100  1E+  30  
$D$11  Factory 1 Customer 2  900.0000000015  100.000000015  100.0000000206  
$E$11  Factory 1 Customer 3  800.0000000175  1E+30  
$C$12  Factory 2 Customer 1  
$D$12  Factory 2 Customer 2  100.0000000209  100.0000000153  
$E$12  Factory 2 Customer 3  700.0000000011  100.0000000152  
Constraint  
Shadow  
Price  R.H. Side  
$F$11  Factory 1 Out  
$F$12  Factory 2 Out  
$C$13  Total To Customer Customer 1  
$D$13  Total To Customer Customer 2  900.0000000036  
$E$13  Total To Customer Customer 3  700.00000000  4 
Prob3
Cells
500
300
100
90
30
<= 1,000
:E4
0
5
>=
5
:E19
0.2
>=
5
C11:E12
H15
TV Spots
Magazine Ads
SS Ads
0
40
=
1,490
F11:F12
($thousands)
TVSpots
C19
TV Spots
Magazine Ads
SS Ads
(thousands)
3
<=
5
Super Grain Corp. AdvertisingMix Problem  
TV Spots  Magazine Ads  SS Ads  Range Name  
Exposures per Ad  1,  300  600  Budget  Available  H7:H8  
(thousands)  BudgetSpent  F7:F8  
Cost per Ad  ($thousands)  Budget Spent  Budget Available  CostPerAd  C7:E8  
Ad Budget  150  3,775  <=  4,000  CouponRedemptionPerAd  C15:E15  
Planning Budget  40  1,000  ExposuresPerAd  C4  
Max  TVSpots  C21  
Number Reached per Ad (millions)  Total Reached  Minimum Acceptable  MinimumAcceptable  H11:H12  
Young Children  1.2  0.1  NumberOfAds  C19  
Parents of Young Children  0.5  0.2  5.85  NumberReachedPerAd  
RequiredAmount  
Total Redeemed  Required Amount  TotalExposures  H19  
Coupon Redemption per Ad  120  1,490  TotalReached  
TotalRedeemed  F15  
Total Exposures  
Number of Ads  14  7.75  16,175  
Maximum TV Spots 
Prob3 Sens. Report
Final
Reduced
Objective
Allowable
Allowable
Cell
Name
Value
Cost
Coefficient
Increase
Decrease
TVSpots
3
0
1E+30
14
0
1E+30
7.75
0
1E+30
Final
Shadow
Constraint
Allowable
Allowable
Cell
Name
Value
Price
R.H. Side
Increase
Decrease
3,775
0
1E+30
1,000
TotalRedeemed
1,490
$F$11
5
5
$F$12
5.85
0
5
1E+30
Variable Cells  
$C$19  1299.9999999981  1040.0000000008  
$D$19  Number of Ads Magazine Ads  600.0000000001  192.59  
$E$19  Number of Ads SS Ads  500.0000000009  577.78  
Constraints  
$F$7  Ad Budget Budget Spent  4000  225  
$F$8  Planning Budget Budget Spent  35  1000  22.5  85.0000000002 
$F$15  8  1490  385.0000000004  90.0000000001  
Young Children Total Reached  1575.76  1.32  0.45  
Parents of Young Children Total Reached  0.85 
Prob4
Range Name
Cells
Hours
Per Unit Produced
Used
Available
1
0
<= 4
0
2
12
<=
12
<= 18
C4
Doors
Windows
D4
6
D12
Wyndor Glass Co. ProductMix Problem  
Doors  Windows  
Unit Profit  $300  $500  DoorsProduced  C12  
Hours  HoursAvailable  G7:G9  
Hours  Used  HoursUsed  E7:E9  
Plant 1  1.3846153846  HoursUsedPerUnitProduced  C7:D9  
Plant 2  TotalProfit  G12  
Plant 3  3.25  2.25  18  UnitProfit  C4:  D4  
UnitProfitPerDoor  
Total Profit  UnitProfitPerWindow  
Units Produced  1.385  $3,415  UnitsProduced  C12:  D12  
WindowsProduced 