Question

I need a screenshot of the proper excel model to solve this:

A Supply Company maintains 3 plants (production facilities P1, P2, & P3) which supply three fuel distributors (A, B, & C) in the city.

Daily plant capacities:

P1 = 4500 gallons; P2 = 3000 gallons; P3 = 5000 gallons

Daily distributor requirements:

A = 5500; B = 2500; C = 4200

Per-gallon transportation costs (in dollars) for each plant-distributor combination are:

              A            B            C

P1          0.80       0.50       1.00

P2          0.70       0.65       0.80

P3          0.50       0.45       0.70

Because of a failure of expected supply earlier, the distributors have decided to charge a penalty per gallon if there are shortages in the future. Each distributor charges a different rate as follows:

A = $0.45; B = $0.55, C = $0.50

Your objective is to build a linear model that will determine the optimum supply of fuel to the distributors in order to minimize the total transportation cost as well as the charges payable as penalty

Let:

X_1A = number of gallons of gasoline supplied from Plant 1 to Distributor A

X_1B = number of gallons of gasoline supplied from Plant 1 to Distributor B

X_1C = number of gallons of gasoline supplied from Plant 1 to Distributor C

X_2A = number of gallons of gasoline supplied from Plant 2 to Distributor A

X_2B = number of gallons of gasoline supplied from Plant 2 to Distributor B

X_2C = number of gallons of gasoline supplied from Plant 2 to Distributor C

X_3A = number of gallons of gasoline supplied from Plant 3 to Distributor A

X_3B = number of gallons of gasoline supplied from Plant 3 to Distributor B

X_3C = number of gallons of gasoline supplied from Plant 3 to Distributor C

MIN 0.80X_1A +0.50X_1B +1.00X_1C +0.70X_2A +0.65X_2B +0.80X_2C +0.50X_3A +0.45X_3B +0.70X_3C

s.t.

              X_1A + X_1B + X_1C <= 4500

              X_2A + X_2B + X_2C <= 3000

              X_3A + X_3B + X_3C <= 5000

              X_1A + X_2A + X_3A = 5500

              X_1B + X_2B + X_3B = 2500

              X_1C + X_2C + X_3C = 4200

              X_1A, X_1B, X_1C, X_2A, X_2B, X_2C, X_3A, X_3B, X_3C >= 0

Answer 1

Formulas

	A	B	C
P1	0.8	0.5	1
P2	0.7	0.65	0.8
P3	0.5	0.45	0.7
	A	B	C
P1	1700	2500	0	=SUM(B8:D8)	4500
P2	0	0	3000	=SUM(B9:D9)	3000
P3	3800	0	1200	=SUM(B10:D10)	5000
	=SUM(B8:B10)	=SUM(C8:C10)	=SUM(D8:D10)
	5500	2500	4200
Cost	=SUMPRODUCT(B2:D4,B8:D10)

data -> solver

Solution

	A	B	C
P1	0.8	0.5	1
P2	0.7	0.65	0.8
P3	0.5	0.45	0.7
	A	B	C
P1	1700	2500	0	4200	4500
P2	0	0	3000	3000	3000
P3	3800	0	1200	5000	5000
	5500	2500	4200
	5500	2500	4200
Cost	7750