Multiple Constraints

[dimbasco]

Hi Folks -
Finally got he HTMLmaker to work .. so here's my question again.
There are 4 Carriers I have (A,B,C,D - I can select any/ all in any combination I desire).
The goal is to minimize $ spend and the constraint is the capacity offered by the carriers.

This is the data:
So for example I want to select the lowest cost carrier (out of A,B,C, and D) in CT (line 12). The Result will be Carrier B - $ 358,185. Capacity needed = 11 . Capacity available (Carrier B) = 80. (so this is a valid result).
But for next region capacity available from Carrier B = 80-11= 79. How do I incorporate this?

Mr Excel.xls
	A	B	C	D	E	F	G
10	Capacityofferedbycarriers		125	80	150	75	430
11	Origin	CapacityNeeded	A	B	C	D
12	CT	11	$365,820	$358,185	$385,070	$374,025
13	MA	17	$624,855	$678,440	$763,335	$664,105
14	ME	8	$364,536		$419,485	$392,370
15	NH	8	$317,050		$376,820	$313,320
16	NJ	31	$1,010,520	$844,810	$883,300	$793,980
17	NYE	25	$909,415	$915,290	$926,820	$830,775
18	RI	3	$114,000	$119,320	$135,280	$118,560
19	VT	2	$72,600		$85,675	$76,470
20	DC	1	$13,650	$13,545	$14,175	$12,180
21	DE	1	$12,350	$10,830	$10,450	$9,310
22	KY	1	$1,884		$2,550
23	MD	17	$535,305	$498,570	$486,225	$474,240
24	NJ	14	$428,400	$346,800	$380,800	$336,600
25	NYW	16	$481,025		$481,805	$537,805
26	OH	20	$653,555		$708,900
27	PAE	27	$690,440	$661,305	$672,910	$661,320
28	PAW	9	$274,075		$292,780	$300,635
29	TN	1	$16,269		$26,100
30	VA	16	$566,894		$569,050	$598,165
31	WV	2	$86,030		$82,575	$98,380
32		230	$7.539MN	$4.447MN	$7.704MN	$6.592MN
MAster

Thanks in advance guys.

Dimbasco

 

[dimbasco]

Also tried to use solver ... supposedly Solver cannot optimize this...

 

[PaddyD]

how do we know how much capacity the carrier has?

 

[dimbasco]

The capacity is given in row 10.

 

[just_jon]

Have attempted a try, using a combination of values vs next higher transport and RANK, but so far to no avail...

 

[Yogi Anand]

Hi dimbasco:

If I have understood you correctly, you should find the following approach to be of some interest ...

y031220h1.xls
	A	B	C	D	E	F	G	H	I	J	K	L
9	CapacityOfferedByCarriers		125	80	150	75	430	0	80	125	75	150
10									TRUE	TRUE	TRUE	FALSE
11	Origin	CapacityNeeded	A	B	C	D			CapacityNeeded	CapacityNeeded	CapacityNeeded	CapacityNeeded
12	CT	11	$365,820	$358,185	$385,070	$374,025			11	11	11
13	MA	17	$624,855	$678,440	$763,335	$664,105			17	17	17
14	ME	8	$364,536		$419,485	$392,370			8	8	8
15	NH	8	$317,050		$376,820	$313,320			8	8	8
16	NJ	31	$1,010,520	$844,810	$883,300	$793,980			31	31	31
17	NY_E	25	$909,415	$915,290	$926,820	$830,775				25	25
18	RI	3	$114,000	$119,320	$135,280	$118,560				3	3
19	VT	2	$72,600		$85,675	$76,470				2	2
20	DC	1	$13,650	$13,545	$14,175	$12,180				1	1
21	DE	1	$12,350	$10,830	$10,450	$9,310				1	1
22	KY	1	$1,884		$2,550					1	1
23	MD	17	$535,305	$498,570	$486,225	$474,240				17	17
Sheet8

(sorry, because of post-size limits, I could not show the whole table)

I have first ranked the carriers by lowest cost and their respective carrying capacity in cells I9:L9

Then for each of the carriers, I conduct an Advanced Filter -- the yellow, aqua, light green, and brown colored cells form my criteria ...

the formula in cell I9 is ...

=INDEX($C$9:$F$9,MATCH(SMALL($C$12:$F$12,COLUMNS($I$9:I$9)),$C$12:$F$12,0))

and the formula in cell I 10 is ...

=AND(SUM($B$12:$B$31)>SUM($H$9:H$9),SUM($B$12:$B12)<=SUM($H$9:I$9))

these formulas are then copied over to cells J9:L9 and J10:L10 respectively.

The process of performing the 4 Advanced Filters can be automated by assigning the following code to a Command Button ...

Sub yDo4Filters()
'
' 12/21/2003 by Yogi Anand
'
    Range("A11:F32").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "I9:I10"), CopyToRange:=Range("I11"), Unique:=False
    Range("A11:F32").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "J9:J10"), CopyToRange:=Range("J11"), Unique:=False
    Range("A11:F32").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "K9:K10"), CopyToRange:=Range("K11"), Unique:=False
    Range("A11:F32").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "L9:L10"), CopyToRange:=Range("L11"), Unique:=False
End Sub

I hope this helps. If I have misunderstood your question -- my apologies!

 

[dimbasco]

Hi Yogi Anand,

Thanks for your response. What I see is you have selected the lowest cost option in The columns I-L. (am I understanding this right?)

What I am a little confused about is that lets say line 12 (CT). Carrier A,B, and D all three show capacity of 11. But only carrier B should have been selected. (The other two should not refelct a usage/ allocation of 11 per week).

Note: It is not necessary that we use all the capacity offered by all carriers. We can use only 10% of 1 and 80 of another. The capacity sets the upper limit.

Thanks,
Dimbasco.

 

[Yogi Anand]

Hi Diambasco:

My formulation uses the lowest cost carriers in sequence -- using the full capacity of the first lowest cost carrier (if needed) then on to the next lowest cost carrier utilizing its full capacity if needed, and then on to the next lowest cost carrier and so on.

Because of the size limit on what I can post on the Board, I am showing a part of the table, so please read this in conjunction with my earlier post and your full table.

y031220h1.xls
	F	G	H	I	J	K	L
8				CarrierB	CarrierA	CarrierD	CarrierC
9	75	430	0	80	125	75	150
10				TRUE	TRUE	TRUE	FALSE
11	D			CapacityNeeded	CapacityNeeded	CapacityNeeded	CapacityNeeded
12	$374,025			11	11	11
13	$664,105			17	17	17
14	$392,370			8	8	8
15	$313,320			8	8	8
16	$793,980			31	31	31
17	$830,775				25	25
18	$118,560				3	3
19	$76,470				2	2
20	$12,180				1	1
21	$9,310				1	1
22					1	1
23	$474,240				17	17
24	$336,600				14	14
25	$537,805				16	16
26					20	20
27	$661,320				27	27
28	$300,635					9
29						1
30	$598,165					16
31	$98,380					2
32	6.592Million
Sheet8

To display only the capacity numbers to be used from the respective carrier, I have used Conditional Formating to show those numbers in Bold and the others numbers are shown grayed out. I hope this is clear -- and if we need to discuss this further, please post back and then let us take it from there.

 

[dimbasco]

Hi Yogi Anand,

Sorry for the delay in replying to your post. I am actually trying to figure out the "CODE" you have given. To be honest I am a little perplexed - alll it did for me was copy the cells from A:F to I:L.
But I did get the formulas in the columns I9:L10. I was unable to generate the capacities in I:L. (Guess I'm a little slow). (I Put the code into a Macro).
Thanks,

Dimbasco

 

[Yogi Anand]

Hi Yogi Anand,

Sorry for the delay in replying to your post. I am actually trying to figure out the "CODE" you have given. To be honest I am a little perplexed - alll it did for me was copy the cells from A:F to I:L.
But I did get the formulas in the columns I9:L10. I was unable to generate the capacities in I:L. (Guess I'm a little slow). (I Put the code into a Macro).
Thanks,

Dimbasco

Hi Diambasco:

I am not clear on what you did, what results you got and where the problem lies. As I mentioned in my posts -- here is the concept I have used ...

I have started with the lowest cost carrier to begin with, and if my needs were still not met, I went to utilize the next lowest cost carrier and utilzed theit carrying capablities, and if my needs were still not met, I went to the next lowest cost supplier, and so on -- but I do not think you have a problem with the concept.

So, if you are having a problem with implementing the concept, please post ...

1. what data you have used
2. what are you trying to accomplish
3. what formulation have you used
4. what result did you get
5. what you think is the right answer

and then let us take it from there.