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 USD (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 USD 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.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 here https://easyupload.io/sx4c3z. 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
