I think I know how to set this problem up for any one moment in time, but am having trouble trying to think of a way to set it up which accounts for time from t=0 to t=15 in a manner that could be solved by excel all at once. Any pointers. Also in reading the problem definition, I have a question about adding capacity. When it states that capacity can be added in "increments" does this mean that you can add either 150,000 or 300,000 to any of the 4 plant locations at any time period, and do so more than once or does it mean simply that at some point in time you will open a plant at Delhi and/or Kolkata which will have a capacity of 150k or 300k and can not add capacity to the same location more than once. Need help setting up years 0 to 15.
Company A, a manufacturer boxes in India, has 2 plants in Mumbai and Chennai. Each plant has a capacity of 300,000 units. The two plants serve the entire country, which is divided into 4 regional markets: the North (demand is 100,000 units), the West (demand is 150,000 units), the South (demand is 100,000 units), and the East (demand is 50,000 units). Two other potential sites include Delhi and Kolkata. The variable production and transport costs (in thousands of rupees; 1 US dollar is worth about 45 rupees) per box from each potential production site to each market are as shown in the attached excel file.
Company A is anticipating a compounded growth in demand of 20 percent per year for the next 5 years and must plan its network investment decisions. Demand is anticipated to stabilize after 5 years of growth. Capacity can be added in increments of either 150,000 or 300,000 units. Adding 150,000 units of capacity incurs a one time cost of 2 Billion rupees, whereas adding 300,000 units of capacity incurs a one time cost of 3.4 Billion rupees. Assume that Company A plans to meet all demand (prices are sufficiently high) and that capacity for each year must be in place by the beginning of the year. Also assume that cost for the 5th year will continue for the next 10 years, that is years 6 to 15. The problem can now be solved for different discount factors. To begin with, assume a discount factor of 0.2, that is 1 rupee spent next year is worth 1-.2=.8 rupee this year.
a)How should the production network for the company evolve over the next 5 years?
b)How does the answer change if the anticipated growth is 15 percent? 25 percent?
c)How does the decision change for a discount factor of .25? .15?
Please see the attached file.
Red cells are the decision variables. Constraints and objective functions are defined.
User input cells are in green ...