View the step-by-step solution to:

I NEED THE EXCEL SHEET SOLVER FOR THE TWO EXERCISE 4 (a) Starting from the basic models in (a), we will build more advanced models in the subsequent...

I NEED THE EXCEL SHEET SOLVER FOR THE TWO EXERCISE

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.


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
fi Annual fixed cost of facility i
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:



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


(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.


(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. yi = 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.




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.




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.


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.



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.

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question