Reference no: EM131215581

Your company is seeking to realize its growth opportunities over the next three years by expanding production capacity for your digital shoehorn product line. You currently have a production facility in Shenzen that serves Southeast Asian customers, and you anticipate growth of 15% per year over the next three years for these customers' demand. The marketing department tells you that you have a market potential in several other regions; namely Australia, Russia, Eastern Europe and North Africa. You have 2 billion Chinese Yuan in cash available to finance the expansion project, and the expected demands are as listed below:

SE Asia: 60 million units in year 1, 15% growth thereafter

Australia: 10 million units in year 1, 25% growth thereafter

Russia: 15 million units in year 1, 15% growth thereafter

Eastern Europe: 50 million units in year 1, 20% growth thereafter

North Africa: 8 million units in year 1, 10% growth thereafter

You must decide now, in year zero, how (and whether) to spend your available cash on expansion, assuming a 15% discount rate.

Building new production facilities will consume your cash reserves. You are a zero-debt company, and intend to stay that way, so all facility construction costs must be paid from cash reserves up front. The costs to build each facility, along with its annual upkeep (staff, utilities, etc. starting in year 1) are as follows:

Shenzhen, China: ¥0 to build (already built); ¥40 million upkeep

Melbourne, Australia: ¥650 million to build; ¥60 million upkeep

Novosibirsk, Russia: ¥425 million to build; ¥35 million upkeep

Debrecen, Hungary: ¥580 million to build; ¥50 million upkeep

Alexandria, Egypt: ¥325 million to build; ¥38 million upkeep

Each facility has a capacity of 45 million units per year at these prices. However, for an initial, one-time charge of ¥125 million for equipment and training, you can raise capacity by 20 million units. This is true for Shenzhen, as well. You may choose to build facilities or not in each of the scouted locations, but the Shenzhen facility must remain in operation.

The variable costs to build and transport one unit of your products are listed in the table below:

	SE Asia	Australia	Russia	E. Europe	North Africa
Shenzhen	45	51	47	46	51
Melbourne	65	50	60	57	57
Novosibirsk	45	54	38	43	52
Debrecen	51	55	47	44	49
Alexandria	83	85	65	50	33

Use Excel's Solver utility to construct a model that answers the following questions.

1. Assume that all demand must be met. Model the lowest cost supply chain network for meeting JUST YEAR ONE demand. Remember that all construction costs are incurred in year 0, so you must find the present value of the year 1 costs and add them to the year 0 costs.

2. Assume the average revenue from each unit is ¥55.

a. Build a model to determine the highest profit supply chain network, assuming all demand must be met for all three years of the problem's time horizon.

How confident do you feel that this is the lowest cost?

b. Russia has decided that it will not allow imports of your product into its markets. You can still supply Russian demand, but it will have to be from a plant in Russia. Determine the most profitable supply chain network under these new conditions over the three year time horizon. Your model must force all Russian supply to originate from Novobirsk.

c. How does the network from part b change if you instead make "lowest cost" your goal, rather than "highest profit"? Which method gives a better result, in your opinion? Explain.

d. You have decided that it is not good business to chase every possible customer, because it could leave you in dire straits if the market should fall later on. Therefore, you have decided that you will supply only 80% of the available demand, overall. However, you have determined that it would cause bad feeling if you drastically under-supplied a region in which you are present; it would be better to not supply that region at all than to supply below 50% of its demand. Build a model that satisfies part b's Russia requirement, but meet only 80% of total demand, and supply no less than 50% of a regions demand.

Turn in a SINGLE Excel worksheet, complete with separate models for each of 1, 2a, 2b, 2c, and 2d. Use Text Boxes (under the Insert ribbon) to write answers on each sheet. Almost all points for the assignment will derive from the model itself, not any particular answer given.