Question

2. Find the optimal allocation of demand to production facilities for the data given in the following table. (Hint: Use the model for allocating demand to existing facilities. First, formulate the problem and then use Excel Solver to find the optimal results.)

Supply Location	Demand Location			Monthly Capacity (Ki)
	Production and Transportation Cost ($per unit)
	1	2	3
1	2.0	1.0	1.7	17,000
2	0.9	2.0	1.3	20,000
3	1.8	2.4	1.6	29,000
Monthly Demand (Dj)	11,000	8,500	15,00

Answer 1

Let the no. of units transported from Supply Location 1 to Demand location 1 be x11, Supply location 2 to Demand location 1 be x21 and so on.

Hence, we get total cost = 2.0*x11 + 1.0*x12 + 1.7*x13 + 0.9*x21 + 2.0*x22 + 1.3*x23 + 1.8*x31 + 2.4*x32 + 1.6*x33

We have to minimze this total cost

We get objective function as:

Minimize Total Cost = 2.0*x11 + 1.0*x12 + 1.7*x13 + 0.9*x21 + 2.0*x22 + 1.3*x23 + 1.8*x31 + 2.4*x32 + 1.6*x33

Total Supply = 17000 + 20000 + 29000 = 66000

Total Demand = 11000 + 8500 + 15000 = 34500

Since, total Demand < Total Supply, we will get "<=" constraint for Supply Constraint in Excel Solver

We get Supply Constraints as:

x11 + x12 + x13 <= 17000

x21 + x22 + x23 <= 20000

x31 + x32 + x33 <= 29000

We get Demand constraints as:

x11 + x21 + x31 = 11000

x12 + x22 + x32 = 8500

x13 + x23 + x33 = 15000

x11, x12, x13, x21, x22, x23, x31, x32, x33 >= 0............Non-negativity constraints as no. of units transported cannot be negative

We solve above problem in Excel using Excel solver as shown below:

The above solution in form of formulas along with Excel Solver extract is shown below for better understanding and reference:

The number of units transported is as per middle Variable Table shown above.

_______________________________________________________________________________________

In case of any doubt, please ask through the comment section before Upvote/downvote.