University of Phoenix Linear Programming Model Worksheet

Problem Set 2—Linear Programming ModelsInstructions
1. Solve each of the following three problems in three tabs in a single Excel file. Label the tabs by problem
number (i.e., 1, 2, and 3).
2. Submit your individual solution to Canvas no later than the date and time shown above.
3. Meet with your study team (virtually if necessary). You need not wait until after 11:59pm on January 31
if your team members have all submitted their individual assignments. Compare and discuss your various
solutions. Help teammates who struggled, as needed. Create a single submission for the team. If you copy
models from one worksheet to another to create the team submission, right-click on the tab and use Move
or Copy Sheet, or control-click/option-click (PC/Mac) and drag the tab to copy the whole worksheet; don’t
just copy and paste the cells as this does not carry over the Solver information.
4. One member of your team should submit it to Canvas no later than the due date and time shown above.
1. Optimizing a Distribution System
A multi-echelon distribution system consists of two factories, three regional warehouses, and four
retailers. Production costs are $42 per unit at factory 1 and $45 per unit at factory 2. Costs of shipping
from the factories to the regional warehouses are given in Table 1, and costs of shipping from the
warehouses to the retailers are given in Table 2. Factory 1 can produce up to 5800 units per month and
factory 2 up to 6400 units per month. The four retailers require at least 3800, 3600, 2200, and 2300 units
per month, respectively. You must decide how much to produce and ship to each regional warehouse
from each factory, and how much to ship from each regional warehouse to each retailer to minimize total
monthly costs. Build a linear programming spreadsheet model, and solve it using Solver.
Warehouse 1
Warehouse 2
Warehouse 3
Factory 1
$5.10
$6.40
$4.30
Factory 2
$4.90
$5.40
$5.80
Table 1. Shipping Cost per Unit from Factories to Warehouses
Retailer 1
Retailer 2
Retailer 3
Retailer 4
Warehouse 1
$2.70
$1.45
$2.95
$3.40
Warehouse 2
$2.30
$2.55
$1.60
$1.95
Warehouse 3
$1.45
$2.45
$2.00
$1.60
Table 2. Shipping Cost per Unit from Warehouses to Retailers
2. Miller Construction
Miller Construction is considering four residential development projects: The Evergreen, Blairwood, Red
Acres and Wild Bluff. Each development project requires a significant investment over the next few years
and then would be sold upon completion. The projected cash flows (in millions of dollars) associated with
each project are shown in the table below.
Year
1
2
3
4
5
The Evergreen
-14
-10
-12
-8
52
Blairwood
-5
-5
-8
22
0
Red Acres
-10
-11
23
0
0
Wild Bluff
-10
-9
-8
-8
42
Miller Construction starts with $20 million cash on hand and also expects to receive $5 million in other
income at the start of each year (1 through 5). Thus, a total of $25 million will be available for investment
at the start of year 1. Assume that money not spent each year is available in future years, and also earns
1% interest. For example, if the ending balance in year 1 is $2 million, then $2.02 million will be available
for projects in year 2 (along with the $5 million in other income for year 2). Assume no interest earned for
year 1 as it is already included in the $20 million starting balance. Assume all cash flows occur
simultaneously at the start of each year (income each year can be used for investment in that same year).
The company may participate in each project either fully, fractionally (with other development
partners), or not at all. If Miller participates at less than 100%, then all the cash flows associated with that
project are reduced proportionally. For example, if Miller participates in The Evergreen at 50%, the cash
flows associated with that project would be –7, –5, –6, –4, and +$26 million at the start of years 1 through
5, respectively. Company policy requires ending each year with a cash balance of at least $2 million.
(Interest is earned on all remaining cash, including the $2 million minimum balance.) Which projects
should Miller Construction take part in and at what fraction of participation, so as to end year 5 with as
much cash as possible? Build a linear programming spreadsheet model, and solve it using Solver.
3. Auto Power Human Resources Scheduling
Auto Power has acquired a new manufacturing facility to produce car parts. They will need to hire and
train employees over the next nine months, and potentially lay off employees later in the season due to
the seasonality of their product sales. The monthly wage rates for trained employees and the
manufacturing labor requirements are given in the following table.
Monthly Wage Rate
Mfg. Hours Required
Jan
$6500
8800
Feb
$6500
9500
Mar
$6700
11,200
Apr
$6700
10,300
May
$7100
9700
June
$7100
11,600
July
$7200
10,700
Aug
$7200
7900
Sep
$7300
8700
Trainees can be hired at the beginning of each month. Workers must have one month of classroom
instruction before they can work in manufacturing. Therefore, a trainee must be hired a month before
the worker is actually needed. Each classroom student uses 70 hours of a trained manufacturing
employee’s time, so there are 70 fewer hours available for manufacturing. Each trained employee can
work up to 190 hours a month (total time, instructing plus in manufacturing). Management may lay off at
most 15% of the trained employees at the beginning of the month but must pay one-half-month’s wage
for severance pay. A trainee is paid 75% of regular wages for a trained employee during their training
month. There are 51 trained employees available at the beginning of January. Formulate this hiring-andtraining-and-layoffs problem as a linear programming spreadsheet model, and solve it using Solver.
3
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
FT/PT
FT
>=
>=
>=
>=
>=
>=
>=
>=
>=
>=
3
Times
Part
Time
Working
0
3
3
6
3
3
0
0
Q
R
S
T
Scheduling at Bank of Seattle
Part Time Cost per Hour
Paid Hours per Shift
$17
3
Full Time Cost per Hour
Paid Hours per Shift
Part
Part
Part
Part
Part
Part
Time Time Time Time Time Time
Shift 1 Shift 2 Shift 3 Shift 4 Shift 5 Shift 6
Cost per Shift $51
$51
$51
$51
$51
$51
Shift Covers Time of Day?
9-10am
10-11am
11-noon
noon-1pm
1-2pm
2-3pm
3-4pm
4-5pm
Workers per Shift
1
1
1
0
1
1
1
1
1
1
1
0
1
1
1
1
1
1
1
0
1
1
1
0
Part
Time
Working
0
1
1
2
1
1
0
0
Full
Time
Shift 1
$200
Full
Time
Shift 2
$200
$25
8
Full
Time
Shift 3
$200
Shift Covers Time of Day?
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
4
3
3
Full
Time
Working
10
10
6
7
7
10
10
10
Part Time Cost $102
Full Time Cost $2.000
Total Cost $2.102
(per day)
3 Nonlinear
3*PT Linear
Total
Working
10
11
7
9
8
11
10
10
Total
Needed
>=
3
>=
5
>=
7
>=
9
>=
8
>=
6
>=
8
>=
9