# Supply Chain Management - 4th edition

Document Sample

```					Chapter 5: Network Design in the Supply Chain

Exercise Solutions

1.
(a)
The objective of this model is to decide optimal locations of home offices, and number of trips
from each home office, so as to minimize the overall network cost. The overall network cost is a
combination of fixed costs of setting up home offices and the total trip costs.

There are two constraint sets in the model. The first constraint set requires that a specified
number of trips be completed to each state j and the second constraint set prevents trips from a
home office i unless it is open. Also, note that there is no capacity restriction at each of the home
offices. While a feasible solution can be achieved by locating a single home office for all trips to
all states, it is easy to see that this might not save on trip costs, since trip rates vary between home
offices and states. We need to identify better ways to plan trips from different home offices to
different states so that the trip costs are at a minimum. Thus, we need an optimization model to
handle this.

Optimization model:
n     = 4: possible home office locations.
m     = 16: number of states.
Dj    = Annual trips needed to state j
Ki    = number of trips that can be handled from a home office
As explained, in this model there is no restriction
fi    = Annualized fixed cost of setting up a home office
cij   = Cost of a trip from home office i to state j
yi    = 1 if home office i is open, 0 otherwise
xij   = Number of trips from home office i to state j.
It should be integral and non-negative
n            n   m
Min          i 1
fi yi   cij xij
i 1 j 1

Subject to
n

x
i 1
ij    D j for j  1,...,m        (5.1)
m

x
j1
ij    Ki yi for i  1,...,n (5.2)

yi  {0,1} for i  1,...n                (5.3)

Please note that (5.2) is not active in this model since K is as large as needed. However, it will be used in answering
(b).

1
SYMBOL     INPUT                                             CELL
Dj         Annual trips needed to state j                    E7:E22
G7:G22,I7:I22,
cij        Transportation cost from office i to state j
K7:K22,M7:M22
fixed cost of setting up office i
fi                                                           G26,I26,K26,M26
F7:F22,H7:H22,
xij        number of consultants from office i to state j.
J7:J22,L7:L22
obj.       objective function                                M31
5.1        demand constraints                                N7:N22
(Sheet SC consulting in workbook exercise5.1.xls)

2
With this we solve the model to obtain the following results:

Total   Trips             Trips        Cost    Trips    Cost      Trips     Cost
# of   from      Cost    from        from     from     From      from      from
State         trips    LA     from LA   Tulsa       Tulsa   Denver   Denver    Seattle   Seattle

Washington            40      -       150       -       250         -        200       40        25

Oregon                35      -       150       -       250         -        200       35        75

California            100     100     75        -       200         -        150       -         125

Idaho                 25      -       150       -       200         -        125       25        125

Nevada                40      40      100       -       200         -        125       -         150

Montana               25      -       175       -       175         -        125       25        125

Wyoming               50      -       150       -       175         50       100       -         150

Utah                  30      -       150       -       150         30       100       -         200

Arizona               50      50      75        -       200         -        100       -         250

Colorado              65      -       150       -       125         65       25        -         250

New Mexico            40      -       125       -       125         40       75        -         300

North Dakota          30      -       300       -       200         30       150       -         200

South Dakota          20      0       300       -       175         20       125       -         200

Nebraska              30      -       250       30      100         -        125       -         250

Kansas                40      -       250       25      75          15       75        -         300

Oklahoma              55      -       250       55      25          -        125       -         300

# of trips            675             190       -       110         -        250       -         125

# of Consultants                      8                 5                    10                  5
Fixed Cost of
office                                165,428           131,230              140,000             145,000

Cost of Trips                         15,250            6,250                20,750              9,875

Total Office Cost                     180,678           137,480              160,750             154,875

The number of consultants is calculated based on the constraint of 25 trips per consultant. As
trips to Kansas cost the same from Tulsa or Denver there are many other solutions possible by
distributing the trips to Kansas between these two offices.

3
(b)
If at most 10 consultants are allowed at each home office, then we need to add one more
constraint i.e. the total number of trips from an office may not exceed 250. Or in terms of the
optimization model, Ki, for all i, should have a value of 250. We can revise constraint (5.2) with
this Ki value and resolve the model. The new model will answer (b).

However in this specific case, it is clear that only the Denver office violates this new condition.
As trips to Kansas can be offloaded from Denver to Tulsa without any incremental cost, that is a
good solution and still optimal.

Hence we just allocate 5 of the Denver-Kansas trips to Tulsa. This reduces the number of
consultants at Denver to 10 while maintaining 5 consultants at Tulsa.

(c)
Just like the situation in (b), though in general we need a new constraint to model the new
requirement, it is not necessary in this specific case. We note that in the optimal solution of (b),
each state is uniquely served by an office except for Kansas where the load is divided between
Denver and Tulsa. The cost to serve Kansas is the same from either office. Hence we can meet
the new constraint by making Tulsa fully responsible for Kansas. This brings the trips out of
Tulsa to 125 and those out of Denver to 235. Again the number of consultants remains at 5 and 10
in Tulsa and Denver, respectively.

2.
DryIce Inc. faces the tradeoff between fixed cost (that is lower per item in a larger plant) versus
the cost of shipping and manufacturing. The typical scenarios that need to be considered are
either having regional manufacturing if the shipping costs are significant or have a centralized
facility if the fixed costs show significant economies to scale.

We keep the units shipped from each plant to every region as variable and choose the fixed cost
based on the emerging production quantities in each plant location. The total system cost is then
minimized with the following constraints:

a. All shipment numbers need to be positive integers.
b. The maximum production capacity is 400,000
c. All shipments to a region should add up to the requirement for 2006 .

Optimization model:
n     = 4: potential sites.
m     = 4: number of regional markets.
Dj    = Annual units needed of regional market j
Ki    = maximum possible capacity of potential sites.
Each Ki is assigned value 400000. If actually needed
capacity is less than or equal to 200000, we choose fixed cost accordingly.
fi    = Annualized fixed cost of setting up a potential site.
cij   = Cost of producing and shipping an air conditioner from site i to regional market j
yi    = 1 if site i is open, 0 otherwise
xij   = Number of air conditioners from site i to regional market j.

4
It should be integral and non-negative
n              n   m
Min             f y   c x
i 1
i   i
i 1 j 1
ij ij

Subject to
n

x
i 1
ij    D j for j  1,...,m               (5.1)
m

x
j1
ij    K i yi for i  1,...,n (5.2)

SYMBOL                 INPUT                                                         CELL
Dj                     requirement at market j                                       K10:K13
C10:C13,E10:E13
cij                    Variable cost from plant i to market j
G10:G13,I10:I13
fixed cost of setting up plant i                              C7:C8,E7:E8
fi
G7:G8,I7:I8
D10:D13,F10:F13
xij                    number of consultants from office i to state j.
H10:H13,J10:J13
obj.                   objective function                                            K21
5.1                    demand constraints                                            L10:L13
(Sheet DryIce in workbook exercise5.2.xls)

We get the following results:

The optimal solution suggests setting up 4 regional plants with each serving the needs of its own
region. New York, Atlanta, Chicago and San Diego should each have a 200,000 capacity plant
with production levels of 110000, 180000, 120000, 100000, respectively.

3
(a)
Sunchem can use the projections to build an optimization model as shown below. In this case, the
shipments from each plant to every market are assumed to be variable and solved to find the
minimum total cost. This is done by utilizing the following constraints:

        Each plant runs at least at half capacity.
        Sum of all shipments from the plant needs to be less than or equal to the capacity in that
plant.
        All production volumes are non-negative.
        All calculations are performed at the exchange rates provided.

5
Optimization model:
n   = 5: five manufacturing plants
m   = 5: number of regional markets.
Dj  = Annual tons of ink needed for regional market j
Ki  = Maximum possible capacity of manufacturing plants.
Especially for (a) lower limit for capacity is 50%*Ki .
cij = Cost of shipping one ton of printing ink from plant i to regional market j
pi = Cost of producing one ton of printing ink at plant i
xij = Tons of printing ink shipped from site i to regional market j.
It should be integral and non-negative
n    m
Min           (c
i 1 j 1
ij    pi ) xij

Subject to
n

x
i 1
ij    D j for j  1,...,m                  (5.1)
m

x
j1
ij    K i for i  1,...,n                  (5.2)

m

x
j1
ij    0.5 K i for i  1,...,n (5.3)

SYMBOL              INPUT                                                                                       CELL
Dj                  Annual demand at market j                                                                   N4:N8
D4:D8,F4:F8,H4:H8,
cij                 shipping cost from plant i to regional market j
J4:J8, L4:L8
production cost of at plant i
pi                                                                                                              D12,F12,H12,J12,L12
E4:E8,G4:G8,I4:I8,
xij                 printing ink shipped from site i to regional market j
K4:K8, M4:M8
obj.                objective function                                                                          N18
5.1                 demand constraints                                                                          O4:O8
5.2                 capacity constraints                                                                        E10,G10,I10,K10,M10
5.3                 50% capacity constraints                                                                    E10,G10,I10,K10,M10
(Sheet capacity_constraints in workbook exercise5.3.xls)

The optimal result is summarized in the following table:

US                      G
Shipment ermany          Japan
Shipment         Shipment Brazil       Shipment India     Shipment Demand(ton/yr)
N. America                          600       100      1,300   160      2,000      -          1,200        10    2,200      -        270         -
S. America                        1,200        -       1,400   -        2,100      -            800      190     2,300      -        190             0
Europe                            1,300        -         600   200      1,400      -          1,400      -       1,300      -        200         -
Japan                             2,000        -       1,400     95       300        25       2,100      -       1,000      -        120             0
Asia                              1,700        -       1,300     20       900      -          2,100      -         800        80     100         -
Capacity (ton/yr)                   185       100        475    475         50       25        200       200       80       80
Minimum Run Rate                     93                  238                25                 100                 40
\$                 Mark               Yen                Real                Rs.
Production Cost per Ton           10,000               15,000         1,800,000              13,000            400,000
Exch Rate                          1.000                0.502             0.009               0.562              0.023
Prod Cost per Ton(US\$)            10,000                7,530            16,740               7,306              9,200
Production Cost In US\$         1,000,000            3,576,750           418,500           1,461,200            736,000
Tpt Cost in US\$                   60,000              487,000             7,500             164,000             64,000

Total                          1,060,000            4,063,750          426,000            1,625,200            800,000           7,974,950

6
This is clearly influenced by the production cost per ton and the local market demand. Low cost
structure plants need to operate at capacity.

(b)

If there are no limits on production we can perform the same exercise as in (a) but without the
capacity constraints (5.2) and (5.3). This gives us the following results:
US                      G
Shipment ermany         Japan
Shipment        Shipment Brazil       Shipment India     Shipment Demand(ton/yr)
N. America                        600          -       1,300   -       2,000      -          1,200      270     2,200      -        270          -
S. America                      1,200          -       1,400   -       2,100      -            800      190     2,300      -        190              0
Europe                          1,300          -         600   200     1,400      -          1,400      -       1,300      -        200          -
Japan                           2,000          -       1,400   120       300      -          2,100      -       1,000      -        120              0
Asia                            1,700          -       1,300   100       900      -          2,100      -         800      -        100          -
Capacity (ton/yr)                185          -          475    420             50   -         200       460           80   -
Minimum Run Rate                      93                 238                25                 100                 40
\$                Mark               Yen                Real                Rs.
Production Cost per Ton        10,000                  15,000         1,800,000              13,000            400,000
Exch Rate                       1.000                   0.502             0.009               0.562              0.023
Prod Cost per Ton(US\$)         10,000                   7,530            16,740               7,306              9,200
Production Cost In US\$            -                 3,162,600               -             3,360,760                -
Tpt Cost in US\$                   -                   418,000               -               476,000                -

Total                             -                 3,580,600               -             3,836,760                -                 7,417,360

Clearly by having no restrictions on capacity SunChem can reduce costs by \$557,590. The
analysis shows that there are gains from shifting a significant portion of production to Brazil and
having no production in Japan, US and India.

(c)
From the scenario in (a) we see that two of the plants are producing at full capacity. And in (b),
we see that it is more economical to produce higher volumes in Brazil. Once we add 10 tons/year
to Brazil, the cost reduces to \$7,795,510.
US                      G
Shipment ermany          Japan
Shipment         Shipment Brazil       Shipment India     Shipment Demand(ton/yr)
N. America                        600         115      1,300   135      2,000      -          1,200        20    2,200         0     270             (0)
S. America                      1,200          -       1,400   -        2,100      -            800      190     2,300      -        190         -
Europe                          1,300          -         600   200      1,400      -          1,400      -       1,300      -        200         -
Japan                           2,000          -       1,400   120        300      -          2,100      -       1,000      -        120         -
Asia                            1,700          -       1,300     20       900      -          2,100      -         800        80     100         -
Capacity (ton/yr)                185          115        475    475             50   -         210       210           80       80
Minimum Run Rate                      93                 238                25                 105                 40
\$                Mark               Yen                Real                Rs.
Production Cost per Ton       10,000                   15,000         1,800,000              13,000            400,000
Exch Rate                      1.000                    0.502             0.009               0.562              0.023
Prod Cost per Ton(US\$)        10,000                    7,530            16,740               7,306              9,200
Production Cost In US\$     1,150,000                3,576,750               -             1,534,260            736,000
Tpt Cost in US\$               69,000                  489,500               -               176,000             64,000

Total                      1,219,000                4,066,250               -             1,710,260            800,000               7,795,510

(d)
It is clear that fluctuations in exchange rates will change the cost structure of each plant. If the
cost at a plant becomes too high, there is merit in shifting some of the production to another plant.
Similarly if a plant’s cost structure becomes more favorable, there is merit in shifting some of the
production from other plants to this plant. Either of these scenarios requires that the plants have
built in excess capacity. Sunchem should plan on making excess capacity available at its plants.

7
4
(a)
Starting from the basic models in (a), we will build more advanced models in the subsequent
parts of this question. Prior to merger, Sleekfon and Sturdyfon operate independently, and so we
need to build separate models for each of them.

Optimization model for Sleekfon:
n        = 3: Sleekfon production facilities.
m        = 7: number of regional markets.
Dj       = Annual market size of regional market j
Ki       = maximum possible capacity of production facility i
cij      = Variable cost of producing, transporting and duty from facility i to market j
fi       = Annual fixed cost of facility i
xij      = Number of units from facility i to regional market j.
It should be integral and non-negative.
n          n   m
Min             i 1
f i   cij xij
i 1 j 1

Subject to
n

x
i 1
ij    D j for j  1,...,m       (5.1)
m

x
j1
ij    K i for i  1,...,n       (5.2)

Please note that we need to calculate the variable cost cij before we plug it into the optimization
model. Variable cost cij is calculated as follows:
cij = production cost per unit at facility i + transportation cost per unit from facility i to market j
+ duty*( production cost per unit at facility i + transportation cost per unit from facility i to
market j + fixed cost per unit of capacity)

SYMBOL                  INPUT                                                                           CELL
Dj                      Annual market size of regional market j                                         B4:H4
Ki                      maximum possible capacity of production facility i                              C12:C14
cij                     Variable cost of producing, transporting and duty from facility i to market j   B22:H28
Annual fixed cost of facility i
fi                                                                                                      D12:D17
xij                     Number of units from facility i to regional market j.                           C43:I45
obj.       objective function                                                                           D48
5.1        demand constraints                                                                           J43:J45
5.2        capacity constraints                                                                         C46:I46
(Sheet sleekfon in workbook problem5.4)

The above model gives optimal result as in following table:

8
N.            S.          Europe      Europe   Japan          Rest of           Africa      Capacity
Quantity Shipped      America       America     (EU)        (Non EU)                Asia/Australia

Europe
(EU)               0.00        0.00       20.00        0.00        0.00              0.00        0.00        0.00
Sleekfon   N.
America           10.00        0.00        0.00        3.00        2.00              2.00        0.00        3.00
S.
America            0.00        4.00        0.00        0.00        0.00              0.00        1.00        5.00
Demand             0.00        0.00        0.00        0.00        0.00              0.00        0.00

Total Cost for Sleekfon =            \$ 564.39

And we use the same model but with data from Sturdyfon to get following optimal production
and distribution plan for Sturdyfon:

N.            S.          Europe      Europe   Japan          Rest of           Africa      Capacity
Quantity Shipped      America       America     (EU)        (Non EU)                Asia/Australia

Europe
(EU)                0.00        0.00        4.00        8.00        0.00              0.00        1.00        7.00
Sturdyfon N.
America            12.00        1.00        0.00        0.00        0.00              0.00        0.00        7.00
Rest of
Asia                0.00        0.00        0.00        0.00        7.00              3.00        0.00        0.00
Demand                0           0           0           0           0                 0           0

Total cost for Sturdyfon =              512.68

(b)

Under conditions of no plant shutdowns, the previous model is still applicable. However, we need
to increase the number of facilities to 6, i.e., 3 from Sleekfon and 3 from Sturdyfon. And the
market demand at a region needs revised by combining the demands from the two companies.
Decision maker has more facilities and greater market share in each region, and hence has more
choices for production and distribution plans. The optimal result is summarized in the following
table.

9
N.           S.          Europe      Europe   Japan          Rest of           Africa      Capacity
America      America     (EU)        (Non EU)                Asia/Australia

Europe
(EU)             0.00        0.00        4.00       11.00        0.00              0.00        2.00        3.00
Sleekfon N.
America         16.00        0.00        0.00        0.00        4.00              0.00        0.00        0.00
S.
America          0.00        5.00        0.00        0.00        0.00              0.00        0.00        5.00
Europe
(EU)             0.00        0.00       20.00        0.00        0.00              0.00        0.00        0.00
Sturdyfon N.
America          6.00        0.00        0.00        0.00        0.00              0.00        0.00       14.00
Rest of
Asia             0.00        0.00        0.00        0.00        5.00              5.00        0.00        0.00
Demand           0.00        0.00        0.00        0.00        0.00              0.00        0.00

Total Cost for Merged Network =    1066.82

(c)

This model is more advanced since it allows facilities to be scaled down or shutdown.
Accordingly we need more variables to reflect this new complexity.

Optimization model for Sleekfon:
n = 6: Sleekfon and Sturdyfon production facilities.
m = 7: number of regional markets.
Dj = Annual market size of regional market j, sum of the Sleekfon and Sturdyfon market share.
Ki =capacity of production facility i
Li =capacity of production facility if it is scaled back
cij = Variable cost of producing, transporting and duty from facility i to market j
fi = Annual fixed cost of facility i
gi = Annual fixed cost of facility i if it is scaled back
hi = Shutdown cost of facility i
xij = Number of units from facility i to regional market j.
It should be integral and non-negative.
yi = Binary variable indicating whether to scale back facility i. y i = 1 means to scale it back, 0 otherwise.
Since two facilities, Sleekfon S America and Sturdyfon Rest of Asia, can not be scaled back, the index i
doesn’t include these two facilities.
zi = Binary variable indicating whether to shutdown facility i. zi =1 means to shutdown it, 0 otherwise.
(1-yi –zi) would be the binary variable indicating whether the facility is unaffected.

10
n                                                                 n   m
Min           ( f (1  y  z )  g y  h z )   c x
i 1
i         i       i          i   i           i i
i 1 j 1
ij ij

Subject to
n

x
i 1
ij    D j for j  1,..., m                                                      (5.1)
m

x
j 1
ij    K i (1  yi  zi )  Li yi for i  1,..., n                               (5.2)

1  yi  zi  0              for i  1,..., n                                            (5.3)
yi , zi are binary for i  1,..., n                                                      (5.4)

Please note that we need to calculate the variable cost cij before we plug it into the optimization
model. Variable cost cij is calculated as following:
cij = production cost per unit at facility i + transportation cost per unit from facility i to market j
+ duty*( production cost per unit at facility i + transportation cost per unit from facility i to
market j + fixed cost per unit of capacity)

And we also need to prepare fixed cost data for the two new scenarios: shutdown and scale back.
As explained in the problem description, fixed cost for a scaled back facility is 70% of the
original one; and it costs 20% of the original annual fixed cost to shutdown it.

Above model gives optimal solution as summarized in the following table. The lowest cost
possible in this model is \$988.93, much lower than the result we got in (b) \$1066.82. As shown in
the result, the Sleekfon N.America facility is shutdown, and the market is mainly served by
Sturdyfon N.America facility. The N.America market share is 22, and there are 40 in terms of
production capacity, hence it is wise to shutdown one facility whichever is more expensive.

N.          S.           Europe          Europe   Japan           Rest of           Africa      Scale back Shut down Plant      Capacity
Quantity Shipped         America     America      (EU)            (Non EU)                 Asia/Australia                                     unaffected

Europe
(EU)                 0.00         0.00            5.00         11.00       0.00              0.00        2.00        0.00      0.00        1.00      2.00
Sleekfon N.
America             20.00         0.00            0.00          0.00       0.00              0.00        0.00        0.00      0.00        1.00      0.00
S.
America              2.00         5.00            0.00          0.00       3.00              0.00        0.00           0      0.00        1.00      0.00
Europe
(EU)                 0.00         0.00           19.00          0.00       1.00              0.00        0.00        0.00      0.00        1.00      0.00
Sturdyfon N.
America              0.00         0.00            0.00          0.00       0.00              0.00        0.00        0.00      1.00        0.00      0.00
Rest of
Asia                 0.00         0.00            0.00          0.00       5.00              5.00        0.00        0.00      0.00        1.00      0.00
Demand            0.00         0.00            0.00          0.00       0.00              0.00        0.00

Total Cost for Merged Network =          988.93

For questions (d) and (e), we need to change the duty to zero and run the optimization model
again to get the result. We can achieve this by resetting B7:H7 to zeros in sheet merger
(shutdown) in workbook problem5.4.xls.

11
5
(a)
The model we developed in 4.d is applicable to this question. We only need to update the demand
data accordingly. And the new demand structure yields a quite different optimal configuration of
the network.
N.           S.          Europe      Europe   Japan          Rest of           Africa      Scale back Shut down Plant      Small         Large        Capacity

Europe
(EU)             0.00        0.00       20.00        0.00        0.00              0.00        0.00        0.00      0.00        1.00                                 0.00
Sleekfon N.
America         15.60        0.00        0.00        0.00        0.00              0.00        0.00        0.00      0.00        1.00                                 4.40
S.
America          0.00        6.00        0.00        0.00        0.00              0.00        0.00           0      0.00        1.00                                 4.00
Europe
(EU)             0.00        0.00        0.00        0.00        0.00              0.00        0.00        0.00      1.00        0.00                                 0.00
Sturdyfon N.
America          6.40        0.00        4.00        9.60        0.00              0.00        0.00        0.00      0.00        1.00                                 0.00
Rest of
Asia             0.00        0.00        0.00        3.60        9.00             15.00        2.40        0.00      0.00        1.00        0.00         1.00        0.00
Demand           0.00        0.00        0.00        0.00        0.00              0.00        0.00                                                       1.00

Total Cost for Merged Network      1141.77

As shown in the table, Sturdyfon N.America is not shutdown in this optimal result. Instead,
Sturdyfon EU facility is shutdown.

For questions (b), (c) and (d), we need to update Excel sheet data accordingly and rerun the
optimization model.

6
(a)

StayFresh faces a multi-period decision problem. If we treated each period separately, only two
constraints are relevant, i.e., the demand and capacity constraints. Considering the multi-period
nature of this problem, it must be noted that as the demand increases steadily, we need to add
capacities eventually. However due to the discount factor, we want to increase capacities as late
as possible. On the other hand, even when the total capacity at a certain period is greater than or
equal to the total demand, we might want to increase capacity anyway. This is because a regional
market might run short while the total supply is surplus, and it may be more expensive to ship
from other regions than to increase local capacity. This complexity calls for an optimization
model to find an optimal solution which can serve all demands, satisfy capacity constraints, adjust
the regional imbalance, and take benefit of discount effect over periods.

12
I: set of plants and potential plants
J: set of regional markets
T: set of periods under consideration. 6~10 year is treated separately. And T  5 in this model.
K: set of capacity incremental options
d jt : demand of regional market j at period t
M i : capacity of plant i at beginning
cij: production and transportation cost from plant i to reginal market j
e k : capacity increment amount of option k
f k : capacity increment cost of option k
r : discount factor
Yikt : binary variable. 1 means to increase capacity of plant i using option k at time t; 0 otherwise.
X ijt : decision variable, shipment amount from plant i to market j at time t

                                           10                           
Min      x
t  i
ijt cij   f k yikt  /( 1  r)t     xij 5cij   f k yik 5  /( 1  r)t
j            i k                       t 5  i j        i k          
 xijt M i   yikt ek for each plant i at each period t
j                    t
5.1

x
i
ijt    dj         for each regional market j                    5.2

xijt  0 , binary yikt      for all plant, market, period, and capacity incremental options

13
SYMBOL      INPUT                                                                                            CELL
d jt        demand of regional market j at period t                                                          B9:H9
Mi          capacity of plant i at beginning                                                                 C12:C14
c ij        Production and transportation cost from plant i to regional market j                             B5:F8
ek          capacity increment amount of option k                                                            D12:D17
fk          capacity increment cost of option k                                                              C43:I45
r           discount factor                                                                                  D48
Yikt        binary variable. 1 means to increase capacity of plant i using option k at time t; 0 otherwise   I5:Q8
B22:E25
H22:K25
X ijt       decision variable, shipment amount from plant i to market j at time t                            N22:Q25
T22:W25
Z22:AC25
obj         objective function                                                                               C31
G22:G25
M22:M25
5.1         capacity constraint                                                                              S22:S25
Y22:Y25
Ae22:Ae25
B26:E26
H26:K26
5.2         demand constraint                                                                                N26:Q26
T26:W26
Z26:AC26
(Sheet StayFresh in workbook problem5.6.xls)

In the first year, original total capacity was 600,000 units, which was 60,000 units more than the
total demand. However, a new plant in Kolkata is built in the optimal solution anyway, since it is
cheaper to server the local market from Kolkata than to ship from other regions.

In the second year, no new capacity is added, since the plant location is reasonable and the total
capacity still exceeds the demand.

In the third and fourth years, new capacity is added consecutively, which has lead to high surplus
capacity. Note that this additional capacity is needed for the fifth year. While there is no reason to
add capacity earlier than necessary, especially under the consideration of the discount factor, the
solution is optimal in this particular model. Since the cost of fifth year will be added into the total
cost six times, it is strategically correct to spend as little as possible in the fifth year. This
explains why extra capacity is built into the network earlier than necessary.

For questions (b) and (c), we need to change data in the Excel sheet accordingly.

14
7
(a)

Blue Computers has two plants in Kentucky and Pennsylvania, however both have high variable
costs to serve the West regional market. On the other hand, West regional market has 2 nd highest
demand. Hence it is not hard to see that Blue Computers needs a new plant, which can serve the
West regional market at a lower cost. From this point of view, California is a better choice than
N.Carolina since California has a lower variable cost serving West regional market. However,
N.Carolina has extra tax benefit. Even if a network of Kentucky, Pennsylvania, and California
might yield higher before-tax profit than a network of Kentucky, Pennsylvania, and N.Carolina,
the after-tax profit might be worse.

n        = 2 potential sites.
m        = 4: number of regional markets.
Dj       = Annual units needed of regional market j
Ki       = maximum possible capacity of potential sites.
fi       = Annualized fixed cost of setting up a potential site.
cij      = Cost of producing and shipping a computer r from site i to regional market j
yi       = 1 if site i is open, 0 otherwise
xij      = Number of products from site i to regional market j.
It should be integral and non-negative
n             n    m
Min              f y   c x
i 1
i   i
i 1 j 1
ij ij

Subject to
n

x
i 1
ij    D j for j  1,..., m              (5.1)
m

x
j 1
ij    Ki yi for i  1,..., n            (5.2)

y3  y4  1 add at most one site (5.3)
y3 , y4 are binary                                  (5.4)

SYMBOL                  INPUT                                                                           CELL
Dj                      Annual market size of regional market j                                         B9:F9
Ki                      maximum possible capacity of production facility i                              H5:H8
cij                     Variable cost of producing, transporting and duty from facility i to market j   B5:F8
Annual fixed cost of facility i
fi                                                                                                      G5:G8
xij                     Number of units from facility i to regional market j.                           B17:F20
obj.                    objective function                                                              I21
5.1                     demand constraints                                                              B21:F21
5.2                     capacity constraints                                                            H17:H20
5.4                     see explanation in next paragraph
(Sheet Blue in workbook problem5.7.xls)

15
Even though constraint (5.4) is simple in its mathematical notation, we can do better in practice.
Since at most one site can be open, we can run the optimization three times for three scenarios
respectively: none open, only California, or only N.Carolina. And we compare the three results
and choose the best one. It is much faster to solve these three scenarios separately given that
EXCEL solver cannot achieve a converging result with constraint (5.4). The result below shows
the optimal solution when California is picked up.

Shipment             Northeast          Southeast Midwest              South          West        Open (1) / Capacity                       Cost
Shut (0) Constraint
Kentucky                       0                 0      600                      0              0          1       400.00                    \$ 255,000
Pennsylvania                   0               150 2.43E-09                    450            900          1          0.00                   \$ 516,500
N. Carolina                    0                 0        0                      0              0       1.00      1500.00                    \$ 200,000
California                  1050               450        0                      0              0          1          0.00                   \$ 530,000
Demand                 -2.65E-06          -1.5E-06 5.01E-10               -1.1E-06       -2.3E-06
constraint                                                                                                             Total Cost =                1501500

(b)
We only need to change the objective function from minimize cost to maximize profit. On the
Excel sheet, all we need to do is to set the target cell from I21 to L21, and change the direction of
optimization from minimizing to maximizing. The following table shows the result. It is easy to
see that lowest cost doesn’t mean maximum after tax profit.

Shipment       Northeast    Southeast Midwest       South       West      Open (1) / Capacity        Cost          Revenue       Profit           After tax
Shut (0) Constraint                                                     profit
Kentucky                0         600           0         400           0          1          0.00   \$ 328,000     \$ 1,000,000   \$    672,000     \$    490,560
Pennsylvania         1050           0         450           0           0          1          0.00   \$ 459,500     \$ 1,500,000   \$ 1,040,500      \$    759,565
N. Carolina             0           0           0           0           0       0.00          0.00   \$        0    \$         -   \$          (0) \$             (0)
California              0           0         150          50         900          1       400.00    \$ 396,500     \$ 1,100,000   \$    703,500     \$    513,555
Demand          -2.65E-06    -1.5E-06    -1.5E-06    -1.1E-06    -2.3E-06
constraint                                                                          Total Cost =         1184000                 Total Profit =   \$ 1,763,680

8
(a)
Starting from the basic models in (a), we will build more advanced models in the subsequent
parts of this question. Prior to merger, Hot&Cold and CaldoFreddo operate independently, and
we need to build separate models for each of them.

Optimization model for Hot&Cold:
n = 3: Hot&Cold production facilities.
m = 4: number of regional markets.
Dj = Annual market size of regional market j
Ki = maximum possible capacity of production facility i
cij = Variable cost of producing, transporting and duty from facility i to market j
fi = Annual fixed cost of facility i
ti =Tax rate at facility i
xij = Number of units from facility i to regional market j.
It should be integral and non-negative.

16
n          n    m
Min            f   c x
i 1
i
i 1 j 1
ij ij

Subject to
n

x
i 1
ij    D j for j  1,...,m                (5.1)
m

x
j1
ij    K i for i  1,...,n                (5.2)

And replace above objective function to the following one to maximize after tax profit:
n             m               n           n    m
Max             (1  t ) px   f   c x
i 1
i
j 1
ij
i 1
i
i 1 j 1
ij ij

SYMBOL                INPUT                                                                                                CELL
Dj                    Annual market size of regional market j                                                              C8:F8
Ki                    maximum possible capacity of production facility i                                                   G5:G7
cij                   Variable cost of producing, transporting and duty from facility i to market j                        C5:F7
Annual fixed cost of facility i
fi                                                                                                                         H5:H7
xij                   of units from facility i to regional market j.                                                       C20:F22
obj.      objective function                                                                                               H24
5.1       demand constraints                                                                                               C23:F23
5.2       capacity constraints                                                                                             G20:G22
(Sheet Hot&Cold in workbook problem5.8.xls)

The above model gives optimal result as in following table:

Shipment                      North              East               South          West          Capacity Annual
Cost
Hot&Cold                 France                     0.0                    0.0             15.0          35.0            0        6150
Germany                       10.0                0.0              5.0           0.0            35       2475
Finland                       20.0               20.0              0.0           0.0            0        4650
Demand                                0                  0                0             0
Total Cost       13275

And we use the same model but with data from CaldoFreddo to get following optimal production
and distribution plan for CaldoFreddo:

Capacity       Annual Cost
Shipment
Quantity Shipped (million units)
CaldoFreddo             U.K.                              15                  15             0           20              0 \$      6,175
Italy                              0                   5            30            0           25 \$        4,225
Demand                                  0                   0             0            0
Total Cost \$       10,400

17
(b)

If none of the plants is shut down, the previous model is still applicable. However, we need to
update the number of facilities to 5, with 3 from Hot&cold and 2 from CaldoFreddo. And we
need to update the market demand Dj, which should be the sum of market shares. Decision maker
has more facilities and greater market share in each region, and hence has more choices for
production and distribution plans. The optimal result is summarized in the following table.

Open (1) /       Capacity     Annual
Shipment
Quantity Shipped (million units)           Shut (0)                      Cost
Merged                  France                  0           0           0           5                 1              45 \$    1,500
Company                 Germany                30           0           0           0                 1              20 \$    3,850
Finland                15          25           0           0                 1    7.9492E-09 \$      4,700
U.K.                    0           0           0          50                 1              0 \$     5,500
Italy                   0          10          50           0                 1              0 \$     6,450
Demand                                  1.3E-10     -9.8E-09      0.0E+00     -1.9E-09
\$   22,000

(c)

This model is more advanced since it allows facilities to be shutdown. Accordingly we need more
variables to reflect this new complexity.

Optimization model for Sleekfon:
n   = 5: Hot&Cold and caldoFreddo production facilities.
m   = 4: number of regional markets.
Dj  = Annual market size of regional market j, sum of the : Hot&Cold and caldoFreddo market share.
Ki  =capacity of production facility i
cij = Variable cost of producing, transporting and duty from facility i to market j
fi  = Annual fixed cost of facility i
xij = Number of units from facility i to regional market j.
It should be integral and non-negative.
zi = Binary variable indicating whether to shutdown facility i. zi =1 means to shutdown it, 0 otherwise.

n                   n   m
Min          i 1
fi (1  zi )   cij xij
i 1 j 1

Subject to
n

x
i 1
ij    D j for j  1,..., m                                  (5.1)
m

x
j 1
ij    K i (1  zi )                   for i  1,..., n      (5.2)

zi are binary for i  1,..., n                                        (5.3)

18
SYMBOL      INPUT                                                                                     CELL
Dj          Annual market size of regional market j                                                   C8:F8
Ki          maximum possible capacity of production facility i                                        C12:C14
cij         Variable cost of producing, transporting and duty from facility i to market j             C5:F7
Annual fixed cost of facility i
fi                                                                                                    H5:H7
xij         Number of units from facility i to regional market j.                                     C19:F23
Zi          open or shutdown facility i                                                               G19:G23
obj.      objective function                                                                          I25
5.1       demand constraints                                                                          C24:F24
5.2       capacity constraints                                                                        H19:H23
(Sheet Merged in workbook problem5.8.xls)

It turned out that all sites are open so as to achieve best objective value. Following table shows
the optimal configuration.

Open (1) /       Capacity       Annual
Shipment
Quantity Shipped (million units)         Shut (0)                        Cost
Merged         France              0           0          0          5                 1                45 \$    1,500
Company        Germany            40           0          0          0                 1                10 \$    4,800
Finland             5          35          0          0                 1                0 \$     4,800
U.K.                0           0          0         50                 1                0 \$     5,500
Italy               0           0         50          0                 1                10 \$    5,400
Demand                           0.0         0.0         0.0        0.0
Total Cost      \$   22,000

19

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 147 posted: 9/29/2012 language: English pages: 19
How are you planning on using Docstoc?