Case Study on $$A Capacity Planning and Facility Location Problem$$

Due Date: Tuesday, November $21,2023$

An electrical components company produces capacitors at three locations: Los Angeles, Chicago, and New York. Capacitors are shipped from these locations to public utilities in five regions of the country: Northeast $($NE$),$ Northwest $($NW$),$ Midwest $($MW$),$ Southeast $($SE$),$ and Southwest $($SW$).$ The cost of producing and shipping a capacitor from each plant to each region of the country is given in the file ElectricalComponentsData.xlsx$.$ Each plant has an annual production capacity of $100,000$ capacitors. Each year, each region of the country must receive the following number of capacitors: NE$,55,000$; NW$,50,000$; MW$,60,000$; SE$,60,000$; SW$,45,000.$ The company believes that shipping costs are too high, and it is therefore considering building one or two more production plants. Possible sites are Atlanta and Houston. The costs of producing a capacitor and shipping it to each region of the country are given in the same file. It costs $$3$ million $($in current dollars$)$ to build a new plant, and operating each plant incurs a fixed cost $($in addition to variable shipping and production costs$)$ of $$50,000$ per year. A plant at Atlanta or Houston will have the capacity to produce $100,000$ capacitors per year. Assume that future demand patterns and production costs will remain unchanged. If costs are discounted at a rate of $12\%$ per year, how can the company minimize the net present value $($NPV$)$ of all costs associated with meeting current and future demands?

Hints:

$1.$ It costs $$3$ million $($in current dollars$)$ to build a new plant

$2.$ Operating each new plant incurs a fixed cost $($in addition to variable shipping and production costs$)$ of $$50,000$ per year. $($You could include this for existing plants as well or simply ignore it for the existing ones, since being a fixed cost, it will not impact your decision variables.$)$

$3.$ To compute the NPV of annually recurring costs for an unknown extended future you can use the following approach:

Interest rate$=$ r

Discount factor $=(1/1+$r$)$

Factor for calculating NPV $=(1/1-$Discount Factor$)$

So$,$ you can multiply annually recurring costs in the future by this factor to compute their NPV$.$

$4.$ Finally, we are simply comparing $4$ different scenarios: No new plants, open a new plant in Atlanta, open a new plant in Houston, open two new plants $($i$.$e$.$ one in Atlanta and one in Houston$).$ You could solve four different optimization models $($all in a separate excel worksheet$)$ or find a creative way to develop a single model that can include all these four options in one optimization model.

Requirements and Rubric:

$$ Setup your model in Excel. $(20$ pt$)$

$$ Clearly identify what is your objective function, decision variables and constraints. $(20$ pt$)$

$$ Identify what type of an optimization problem this is $($i$.$e$.$ linear, non$-$linear$)$ and why. $(10$ pt$)$

$$ Select an optimization algorithm from Solver and discuss why you think your selected algorithm is appropriate in this scenario. $(10$ pt$)$

$$ Setup your Solver model and solve the problem. $(10$ pt$)$

$$ Discuss whether you solved the problem optimally or just find a feasible solution. $(10$ pt$)$

$$ Report your solution and discuss why the solution you provided is an appropriate one. $(20$ pt$)$