Divide whole numbers across multiple cells with variable

[Legacy 386498]

Hi all,

I need to solve a problem of dividing whole numbers in excel based on these variable:

Company AA can only sale to company A and the quantity bought and sold is always the same. In this example 44 000. Company A1 can receive 6000 from AA1 and 9000 from AA2 or 15000 from AA1, etc. That doesn't matter.
Company BB can only sale to company B, but the quantity bought and sold is always different, so company always need to buy externally to compensate. In this example, 13 000 sold and 18 000 bought, so 5000 bought externally.
Company CC to FF can sale to any company from C to F. The goal here is for CC to sell has much as possible to C, DD to D, EE to E and FF to F. When not possible, sell from one to another and keeping the quantity bought and sold externally to a minimum. In this example, CC can sell 8000 to C and 2000 to D.

What can I do to solve this problem with excel? I've read online about excel solver, is there anything else? Is it the best solution for me?

Thanks in advance.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company[/TD]
[TD]Capacity sold[/TD]
[TD]A1[/TD]
[TD]A2[/TD]
[TD]A3[/TD]
[TD]B1[/TD]
[TD]B2[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[TD]F1[/TD]
[TD]External[/TD]
[/TR]
[TR]
[TD]Capacity bought[/TD]
[TD][/TD]
[TD]15000[/TD]
[TD]10000[/TD]
[TD]19000[/TD]
[TD]10000[/TD]
[TD]8000[/TD]
[TD]8000[/TD]
[TD]7000[/TD]
[TD]20000[/TD]
[TD]7000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA1[/TD]
[TD]25000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA2[/TD]
[TD]9000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AA3[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BB1[/TD]
[TD]7000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BB2[/TD]
[TD]6000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CC1[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DD1[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EE1[/TD]
[TD]25000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FF1[/TD]
[TD]8000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]External[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[joshman108]

I'm not sure I understand the constraints.

You say the goal is for CC to sell as much as possible to C and when that's not possible, sell to someone else the minimum possible. But under what conditions is it not possible for CC to sell to C? And what do you mean "minimum"?
Also, company B bought 5,000 externally. Why 5,000? What are they trying to meet? What are the conditions? And does that action play into what companies CC:FF & C:F are doing?

Please tag me when you respond

 

[Legacy 386498]

I'm not sure I understand the constraints.

You say the goal is for CC to sell as much as possible to C and when that's not possible, sell to someone else the minimum possible. But under what conditions is it not possible for CC to sell to C? And what do you mean "minimum"?
Also, company B bought 5,000 externally. Why 5,000? What are they trying to meet? What are the conditions? And does that action play into what companies CC:FF & C:F are doing?

Please tag me when you respond

Company B bought 5 000 externally, because B can only buy from BB. BB has 13 000 to sell (6 000 + 7 000) and we need to full B with 18 000 (10 000 + 8 000), so B will buy/transfer 13 000 from BB and 5 000 externally to max out the number they can buy (18 000). So they're trying to meet the max capacity bought (same for every company).

Company CC can sell 10 000 to C, but C can only buy 8 000, they don't have the capacity to buy more, so CC can still sell 2 000 to D, E and/or F. The goal here is for company AA to FF to sell everything to/full company A to F internally. When that's not possible, they will sell externally. Or the contrary can happen where A to F can buy more than company AA to FF can sell internally, so they will buy the difference externally. That's what I mean by keeping the quantity bought and sold externally to a minimum.

I hope this makes more sense, don't hesitate if something isn't clear.

 

[joshman108]

I can't think of a way to solve this "problem" in excel. I feel like its as simple as saying "Has BB sold all of it's capacity to B?" if yes, then B buys the rest externally. I don't see there being anything complicated to solve. This should be built into the ordering software you use. That's my only thought.

 

[Legacy 386498]

I can't think of a way to solve this "problem" in excel. I feel like its as simple as saying "Has BB sold all of it's capacity to B?" if yes, then B buys the rest externally. I don't see there being anything complicated to solve. This should be built into the ordering software you use. That's my only thought.

The problem is I don't have an ordering software, only excel. Thanks for taking the time 'tho.