Excel Solver - Optimization problem

Hello everyone,

I have to perform an optimization tast by using excel solver. The case study has two parts and I already managed to complete the first one.

However, I couldn't solve the second one so far.

Both parts are connected.

This is the first assignment:

We illustrate the design of a regional network in the context of SunOil, a manufacturer of
petrochemical products with worldwide sales. The vice president of supply chain is considering
several options to meet demand. One possibility is to set up a facility in each region. The advantage
of such an approach is that it lowers transportation cost and also helps avoid duties that may be
imposed if product is imported from other regions. The disadvantage of this approach is that plants
are sized to meet local demand and may not fully exploit economies of scale. An alternative approach
is to consolidate plants in just a few regions. This improves economies of scale but increases
transportation cost and duties. When designing a regional configuration, the manager must consider
these quantifiable trade-offs along with non-quantifiable factors such as the competitive environment
and political risk.
The first step is to collect the data in a form that can be used for a quantitative model. For SunOil, the
vice president of supply chain decides to view the worldwide demand in terms of five regions North
America, South America, Europe, Africa, and Asia. The data collected are shown in Figure 5-3. Annual
demand for each of the five regions is shown in cells B9:F9. Cells B4:F8 contain the variable
production, inventory, and transportation cost (including tariffs and duties) of producing in one region
to meet demand in each individual region. All costs are in thousands of dollars. For example, as shown
in cell C4, it costs $92,000 (including duties) to produce 1 million units in North America and sell
them in South America. As shown in cell G4, it costs $6 million in annualized fixed cost to build a low-
capacity plant in North America. Observe that the data collected at this stage are at a fairly aggregate
level.
There are fixed as well as variable costs associated with facilities, transportation, and inventories at
each facility. Fixed costs are those that are incurred no matter how much is produced or shipped from
a facility. Variable costs are those that are incurred in proportion to the quantity produced or shipped
from a given facility. Facility, transportation, and inventory costs generally display economies of scale,
and the marginal cost decreases as the quantity produced at a facility increases. In the models we
consider, however, all variable costs grow linearly with the quantity produced or shipped. e
SunOil is considering two plant sizes in each location. Low-capacity plants can produce 10 million
units a year, whereas high-capacity plants can produce 20 million units a year, as shown in cells
H4:H8 and J4:J8, respectively. High-capacity plants exhibit some economies of scale and have fixed
costs that are less than twice the fixed costs of a low-capacity plant, as shown in cells I4:I8. All fixed
costs are annualized. The vice president wants to know what the lowest-cost network should look
like. To answer this question, we use the capacitated plant location model, which can be used in this
setting.

Please see my solution for this problem in the excel file attached. The result is correct.

The second task requires an extended planning of the supply chain.

Now it must be considered, that in future the demand worldwide will be doubled (however the proportions stay the same: N. America = 24, S. America = 16, Europe = 28, Asia = 32, Africa =14)

Thus, I must devide the planning in two periods.

The first period includes the original demand, the second one includes the doubled demand.

Both periods must be regarded within a simultaneos planning - not separated.

The facilities can be set up during the first or during the second period. However, once set up, they cannot be closed.

All the other assumptions (costs, capacities, regions, etc.) stay the same.

I hope, someone might be able to help me

Thanks in advance,
Frosty

Thanks for the answer

But what about the demand? For the first period it equals 12, 8, 14, 16 and 7. For the second one it's doubled - so 24, 16, 28, 32 and 14. Which one do I take?
It is relevant for the demand constraint at the bottom of the sheet.