I have attached a word document named OSCM-471571_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 sub-questions).
·
This homework is made up of FIVE questions (20 pts):
·
Q1: 3 sub-questions (2 pts)
·
Q2: 3 sub-questions (5 pts)
·
Q3: 2 sub-questions (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 step-by-step 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: full-time and part-time. The full-time 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). Full-time consultants are paid $14 per hour.
Part-time consultants can be hired to work any of the four shifts listed in the table. Part-time consultants are paid $12 per hour.
An additional requirement is that during every time period, there must be at least two full-time consultants on duty for every part-time consultant on duty.
Larry would like to determine how many full-time and part-time 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 in-process 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 in-process 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 Cost-Less 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
>Q2-b
Full Time Part Time pm
per Shift
Total ? ( yes, 0=no)
>= >= >= Total Total Part Time Cost >= >= >= >=
2
Full Time
Full Time
Part Time
Part Time
Part Time
8am-
4
noon-8pm
4pm-midnight
8am-noon
noon-4pm
4pm-8pm
8pm-midnight
Cost
Total
Shift Covers
Time of Day
1
=
Working
Needed
8am-noon
>=
noon-4pm
4pm-8pm
8pm-midnight
Workers per Shift
Times Total
Time of Day
Full Time
8am-noon
noon-4pm
4pm-8pm
8pm-midnight
Q
-a
| 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
| Unit Cost | Retail Outlet | ||
| Ship | |||
| Total Shipped | Supply | ||
| Total Received | |||
| Total Cost | |||
| Demand |
Q5
| Port | |
| Assignments | |
| Total Assigned |
Module 3-1 Practice Problems
OSCM 471/
5
71 Optimization and Decision Support Modeling for Business
Study Materials:
Lecture 3-1: LP Formulation and Applications
1.
(Resource-Allocation 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.
(Cost-Benefit-Tradeoff-Problem) 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 month-by-month 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–trade-off 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 full-time employee works a 40-hour week. In addition, by union contract, the number of full-time employees can never drop below 20. Nonunion, part-time workers also can be hired with the following union-imposed restrictions: (1) each part-time worker works 20 hours per week and (2) there must be at least two full-time employees for each part-time 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,000-square-foot 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 full-time employee earns $13 per hour, while each part-time 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 full-time and part-time 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 full-time workers to employ,
PT = number of part-time 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 A-3, B-1, C-2, 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.
|
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
> h _Prob 1
(cf/ton)
0
0
0
0
0
Total Volume Cargo 2 Volume Capacity 0 0 12 7,000 0 .9999999999
<=
<=
9,000
ack
0 <=
<=
5,000
5.0000000001 <=
<=
<=
<=
vailable
(tons)
20 13 ,3 %
= Total 1 1 (sq. ft.) 1 >= 1 30,000 1 1 >= 1 1 1 30,000 1 1 1 1 ,000
>= of Lease
5
0
0
$65 $135 0 ,650,000
$10 (per hour)
$13 40 20
0
Usage
Resource Resource of Activity
Available 2 1 <=
30 40 <=
Men’s (per week)
0 Gross Profit Full Time Labor Cost ,400
25 ,440
>= 20 2 Unit Production Cost Capacity
:H12
A
$16 Total Monthly Shipped Capacity Plant A Plant B <=
80 = 80 1 1 400 600 3 800 $100 Distribution Center 1 1 3 $500 Distribution Center 1 1 0 = 3 10 10 = Total Cost Demand 1 1 3 Customer 1 Supply 1 0 = 3 = Total Received = Total Cost 40 1 A B C s
Job Total 1 Supply A C 1 = Total Cost Demand $10 Range Name Management Assignment 60 Capacity 20 45 Points 50 Points
30 50 70 25 35 60 Management Operations Total Classes Student Assignment Points George 80 Fred 60 Ann 90 Eric 45 Susan 60 Liz 100 Ed 80 David 60 Tony 50 Jennifer 80 5 <=
<=
<=
<=
Capacity
2
C
3
1
Cargo
Cargo 2
Cargo 3
Cargo
4
Volume
5
0
70
60
40
Profit (per ton)
$3
20
$
400
$360
$2
9
Cargo
Total
Weight
Placement (tons)
Cargo 1
Cargo 3
Cargo 4
Weight
Capacity
Front
7.333
4.667
12
<
>
>
=
7,000
<=
Center
12.9999999994
1.667
3.333
17
18
9,000
B
0
5.0000000001
4.9999999998
9.9999999999
10
5,000
Total
12.9999999994
9.0000000003
13
Total Profit
A
16
25
$13
30
Percentage of Front Capacity
100
100% Percentage of Middle Capacity
Percentage of Front Capacity
100%
=
100%
Percentage of Back Capacity
Ch3_Prob2
Month Covered by Lease?
Space
Month of Lease:
1
1
1
1
2
2
2
2
3
3
3
4
4
5 Leased
Required
Length of Lease:
2
3
4
5
1
2
3
4
1
2
3
1
2
1
(sq. ft.)
Month 1
1
1
1
1
30,000
30,000
Month 2
1
1
1
1
1
1
1
>=
20,000
Month 3
1
1
1
1
1
1
1
40,000
40,000
Month 4
1
1
1
1
1
>=
10,000
Month 5
1
50
50,000
Cost
$6
$10
$1
35
$16
$1
90
$100
$160
$65
$100
$135
$65
$100
$65
(per sq. ft.)
Total Cost
Lease (sq. ft.)
0
0
0
30,000
0
0
0
0
10,000
0
0
0
0
20,000
$7
Ch3_Prob3
Men’s
Women’s
Children’s
Gross Profit
$8
$6
(per glove)
Full Time
Part Time
Labor Cost
$10
Hours worked per week
20
Labor Cost (per week)
$5
$20
Resource
per
Unit
Used
Material (sq. ft.)
1.5
4960
5000
Labor (minutes)
45
74400
74,400
Women’s
Children’s
Production
24
80
0
$19,840
Part Time $
15
Employees
12 Net Profit
$4
Minimum Full Time
Full Time
25
>=
24
Times Part-Time
Ch3_Prob4
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
$20 $24
$625
OverallCost
H18
UnitShippingCost
C5:E6
TotalProductionCost
H16
Units
Shipped
TotalShipped
Out
F11:F12
Warehouse 1
Warehouse 2
Warehouse 3
Out TotalShippingCost
H17
40
60
0
100
<=
100
TotalToWarehouse
C13:E13
40
0
70 110
120
UnitProductionCost
H5:H6
Total to Warehouse
60
70 UnitsShipped
C11:E12
=
=
Monthly Demand
60
70
Total Production Cost
$128,750
Total Shipping Cost
$4,040
Overall Cost
$132,790
Ch3_Prob5
Distance (miles)
Distribution Center
2
3
4
800
1,300
700
Plant
2
1,100
1,400
1,000
600 1,200
900
Fixed Cost
Cost per Mile
$0.50
Unit Cost
2
3
4
$500
$750
$300
$450
Plant
2
$650
$800
$400
$600
$400
$700
$550
Shipments
2
3
4
Total Shipped
Supply
0
0
2
10
12
=
12
Plant
2
0
9
7.9999999997
16.9999999997
17
10
1
0
0
11
=
11
Total Received
10 9.9999999997
=
=
=
10
10
10
10 $20,200
Ch3_Prob6
Unit Profit
Customer
2
3
4
$800
$700
$500
$200
Plant
2
$500
$200
$100
$300
$600
$400
$300
$500
Shipments
2
3
4
Total Shipped
0
60.0000000001
0
60.0000000001
=
60
Plant
2
40
0
0
40.0000000001
80.0000000001
80
0
0
20 20.0000000008
40.0000000008
40
40
60.0000000001
20 60.0000000009
=
>=
Commitment
60
20 $90,000
Ch3_Prob7
Unit Cost
Job
2
3
$5
$7
$4
Person
$3
$6
$5
$2
$3
$4
Assignment
2
3
Assignments
0
0
1
1
=
1
Person
B
1
0
0
1
=
1
0
1
0
1
=
1
Total Assigned
1
1
=
=
1
1
1
Ch3_Prob8
Bidding for
Classes
Management
Operations
Cells
Points
Science
Finance
Marketing
C18:F27
George
10
10
20 C30:F30
Fred
20
40
20 ClassesToTake
I18:I27
Ann
45
5
5 C5:F14
Eric
20
5
25
Student
K18:K27
Susan
30
30
10 TotalClasses
G18:G27
Liz
50
0
0 TotalInClass
C28:F28
Ed
20
10
0 TotalPoints
I29
David
25
35
15
Tony
15
35
15
Jennifer
10
10
20
Science
Finance
Management
Marketing
Classes to Take
1
0
0
1
2
=
2
0
0
1
1
2
=
2
1
1
0
0
2
=
2
0
1
0
1
2
=
2
0
1
1
0
2
=
2
1
1
0
0
2
=
2
1
0
1
0
2
=
2
0
1
1
0
2
=
2
0
0
1
1
2
=
2
1
0
0
1
2
=
2
Total in Class
5
5
5
Total Points
705
5
5
5
5
Module 3-2 Practice Problems
OSCM 471/571 Optimization and Decision Support Modeling for Business
1 / 7
Study Materials:
Lecture 3-2: LP What-if 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 3-2 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 3-2 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 re-solving 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 3-2 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 right-hand 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 3-2 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 3-2 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 re-solving.
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 3-2 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 s
0
00
00
:E15
$800 0 put
Customer 1 Factory 1 2 12 Factory 2 10 = 10 Allowable Cell Cost 10 .0000000208
2 0 100.000000015 0 6 9 1E+30 s
Final Constraint Cell Increase 12 15 10 8 8 9 9 Cells 500 300 100 90 <=
1,000
:E4
0 :E19
0.2 >= C11:E12 H15 TV Spots 0 = F11:F12 ($thousands) TVSpots TV Spots (thousands) 3 <=
5 Final Cell TVSpots 1E+30 14 1E+30 7.75 1E+30 Final Cell 3,775 1E+30 1,000 TotalRedeemed $F$11 5 5 $F$12 5.85 1E+30 Range Name Hours Per Unit Produced
Used Available 1 <=
4
0 <=
18
C4 Doors D4 6 D12
2
1
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
$700
Out
H11:H
12
ShippingCost
C5:E
6
Total
TotalCost
H15
Shipped
TotalShippedOut
F11:F12
Units Shipped
Customer 2
Customer 3
Out Output
TotalToCustomer
C13:E13
10
0
12
>
=
UnitsShipped
C11:E12
0
6
9
15
=
15
Total To Customer
8
9
=
= Total Cost
Order Size
8
9 $20,
500
Prob1 Sens. Report
Variable
Cells
Final
Reduced
Objective
Allowable
Name
Value
Coefficient
Increase
Decrease
$C$11
Factory 1 Customer 1
0 700.0000000007
100
1E+
30
$D$11
Factory 1 Customer 2
0
900.0000000015
100.000000015
100.0000000206
$E$11
Factory 1 Customer 3
100.000000015
800.0000000175
1E+30
$C$12
Factory 2 Customer 1
100.0000000206
800.0000000175
1E+30
100.0000000206
$D$12
Factory 2 Customer 2
0
900.0000000015 100.0000000209
100.0000000153
$E$12
Factory 2 Customer 3
0
700.0000000011
100.0000000152
Constraint
Shadow
Allowable
Allowable
Name
Value
Price
R.H. Side
Decrease
$F$11
Factory 1 Out
0
12
0
1E+30
$F$12
Factory 2 Out
0
15
2
0
$C$13
Total To Customer Customer 1
700.0000000011
10
0
10
$D$13
Total To Customer Customer 2
900.0000000036
0
2
$E$13
Total To Customer Customer 3
700.00000000
4
0
2
Prob3
Super Grain Corp. Advertising-Mix 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
30
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
5
>=
5 NumberOfAds
C19
Parents of Young Children
0.5
0.2
5.85
5 NumberReachedPerAd
RequiredAmount
Magazine Ads
SS Ads Total Redeemed
Required Amount
TotalExposures
H19
Coupon Redemption per Ad
40 120
1,490
1,490 TotalReached
TotalRedeemed
F15
Total Exposures
C19
Magazine Ads
SS Ads
Number of Ads
14
7.75
16,175
Maximum TV Spots
Prob3 Sens. Report
Variable Cells
Reduced
Objective
Allowable
Allowable
Name
Value
Cost
Coefficient
Increase
Decrease
$C$19
3
0 1299.9999999981
1040.0000000008
$D$19
Number of Ads Magazine Ads
0 600.0000000001
192.59
$E$19
Number of Ads SS Ads
0 500.0000000009
577.78
Constraints
Shadow
Constraint
Allowable
Allowable
Name
Value
Price
R.H. Side
Increase
Decrease
$F$7
Ad Budget Budget Spent
0 4000
225
$F$8
Planning Budget Budget Spent
35
1000
22.5
85.0000000002
$F$15
1,490 -8
1490
385.0000000004
90.0000000001
Young Children Total Reached
-1575.76
1.32
0.45
Parents of Young Children Total Reached
0
5 0.85
Prob4
Wyndor Glass Co. Product-Mix Problem
Doors
Windows
Cells
Unit Profit
$300
$500
DoorsProduced
C12
Hours
HoursAvailable
G7:G9
Hours
Used
HoursUsed
E7:E9
Plant 1
0 1.3846153846
HoursUsedPerUnitProduced
C7:D9
Plant 2
2
12
<=
12
TotalProfit
G12
Plant 3
3.25
2.25
18
UnitProfit
C4:
D4
UnitProfitPerDoor
Windows Total Profit
UnitProfitPerWindow
Units Produced
1.385
$3,415
UnitsProduced
C12:
D12
WindowsProduced
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
