Please Show excel formulas

Energy Soft Drinks Bottler $($ESDB$)$ Inc. has recently developed and started marketing a new soft drink. The sales of the soft drink have sky$-$rocketed and ESDB is evaluating the decision to set up a new plant to which some of the existing production can be moved.

The annual fixed cost, the cost of production and the plant capacity for each of the ten plant locations $($A through J$)$ is shown in Table $1.$

The distance in miles between the ten potential plants and the six markets $(1$ through $6)$ is given in Table $2.$ Table $2$ also shows the demand for soft drinks at the six markets.

ESDB uses trucks for transporting soft drinks from the bottling plant to the different markets. These trucks have a capacity of $150$ hectoliters and incur an operating cost of $$0\mathrm{.}92$ per mile. After delivery to the market, the trucks return empty to the plant.

$1)$ Formulate an integer linear programming model to minimize the total cost for the supply chain network and to determine the number of plants to be opened and the locations of these plants. Make sure you clearly define the decision variables, the objective function and the constraints of your model.

$2)$ Use Solver functionality in Excel to solve the model. What are the locations for the open plants and what is the optimal total cost of the network? Are all open plants being used to full capacity? Which, if any plant has excess capacity and what is the excess capacity available at this plant?

$3)$ Suppose that the maximum distance between a plant and a market cannot exceed $150$ miles. What additional decision variables and$/$or constraints do you need to add to your model in question $1)$ to accommodate this condition?

$4)$ Resolve the model developed in $3)$ and identify the new plant locations and the minimum cost.

$5)$ Identify an additional scenario and develop a model for the scenario. Analyze the model and discuss your model results.

Table $1.$ ESDB$$s costs and capacity

Plant Fixed cost $($in $$)$ Capacity

$($in hectoliters$)$ Production cost $($$$/$hectoliter$)$

A $81400220000\mathrm{.}5$

B $83800240000\mathrm{.}45$

C $88600280000\mathrm{.}42$

D $91000300000\mathrm{.}44$

E $79000200000\mathrm{.}56$

F $86200260000\mathrm{.}5$

G $88600280000\mathrm{.}5$

H $91000300000\mathrm{.}46$

I $79000200000\mathrm{.}43$

J $80200210000\mathrm{.}41$

a hectoliter is $100$ liters

Table $2.$ Distance in miles between potential plants and markets

Markets

$123456$

A $076\mathrm{.}130\mathrm{.}4139\mathrm{.}472\mathrm{.}611\mathrm{.}7$

B $76\mathrm{.}107177\mathrm{.}2144\mathrm{.}583\mathrm{.}7$

C $20\mathrm{.}892\mathrm{.}947\mathrm{.}2156\mathrm{.}147\mathrm{.}511\mathrm{.}7$

D $54\mathrm{.}7113\mathrm{.}352\mathrm{.}9187\mathrm{.}29345\mathrm{.}2$

Plants E $13\mathrm{.}585\mathrm{.}528148\mathrm{.}767\mathrm{.}39\mathrm{.}3$

F $30\mathrm{.}4710138\mathrm{.}294\mathrm{.}538\mathrm{.}1$

G $139\mathrm{.}477\mathrm{.}2138\mathrm{.}20207\mathrm{.}9146\mathrm{.}9$

H $47\mathrm{.}8106\mathrm{.}546\mathrm{.}2180\mathrm{.}286\mathrm{.}738\mathrm{.}9$

I $72\mathrm{.}6144\mathrm{.}594\mathrm{.}5207\mathrm{.}9063\mathrm{.}4$

J $11\mathrm{.}783\mathrm{.}738\mathrm{.}1146\mathrm{.}963\mathrm{.}40$

Demand $1400010000800012000100009000$