Optimization Algorithm (1 Viewer)

[ions]

Hello Access Expert.

I would like to know if the below is possible in Access / VBA and if so can I leverage something on the internet to get me started?

Thank you,

Suppose you have three buckets A, B, C

Bucket Capacity % Water $
A 70L 10% $1
B 80L 30% $2
C 100L 40% $3


You must make an optimal blend from these buckets (optimized for Profit) where the Blend must be 100L but <= 25% water.

For Example:

Scenario 1

I can take 40L of A (4L of Water), 30L of B (9L of Water) and 30L of C (12L of Water) which is 100L with 25L of Water for a profit of

40($1) + 30($2)+ 30($3) = $190

Scenario 2

I can take 50L of A (5L of Water), 0L of B (0L of Water) and 50L of C (20L of Water) which is 100L with 25L of Water for a profit of

$60 + $150 = $210


Bucket Capacity, % Water, $ values are all variables and I would need to determine the most profitable Scenario out of all the Scenarios. There is actually more than 3 buckets and there is another Variable but I want to keep the base case simple.

Thank you

 

[Uncle Gizmo]

I realise you think you are being helpful in keeping some of the information back so that the question you ask is simplified.

However in my experience you need all of the information right at the beginning otherwise you can spend a lot of time working something out only to find that the question completely changes when new information is added later and it feels like you are playing a game and suddenly someone moves the goal to a completely different place and that is frustrating.

So I would suggest you explain properly what's involved and then offer the simplification because to my mind both the complete explanation and seeing how the simplification is derived from that are just as important.

 

[ions]

Hi Gizmo,

Thanks for your response. You are correct the problem outlined is the base case. The actual problem involves 5-8 buckets, and additional restrictions on the final blend such as > Y %, < Z %, on top of the 100 L volume and <= 25 % water restrictions.

Also please note that

Dollar is actually the incoming revenue from the material that resides in the bucket. The cost is the actual disposal of the final blend which meets the water and volume constraints etc... So the revenue and cost is actually reversed from the traditional manufacturing model in this case.

 

[Uncle Gizmo]

Q1)

Do you know how many buckets there will be in advance?

 

[Uncle Gizmo]

Q2)

Do the buckets represent actual fixed size containers or do they represent some sort of delivery system where the volume can be changed?

 

[Uncle Gizmo]

Q3)

BucketCapacity% Water$
A 70L10% $1

Please could you explain the capacity mentioned here and the percentage how does this relate?

 

[Uncle Gizmo]

Q4)

I originally thought that the buckets were a unit of measurement that you added to the mix. However I am now thinking that the buckets are actually a container you draw the chemical from?

Please clarify...

 

[CJ_London]

just to be clear - is this homework/coursework or a real world application? If the latter, please use a real world example.

In your initial example - why can't just take 100 litres from bucket C? that would give you $300 profit - or if it has to be a blend 98L from C and 1L each from A and B

 

[spikepl]

For two variables this is a linear programming problem that can be solved using the Solver add-in in Excel.

For multivariables you first need to find an algorithm to solve this thing

 

[Uncle Gizmo]

I'm not sure I think Chris is on the right track and it's some sort of exam question and my guess it is something to do with percentages.

 

[jdraw]

I agree with the others -

-we don't do homework ...we can assist, advise etc if you give an honest effort
-better to give a clear and complete description of the business problem in business terms than to "invent" an analogy or restrict the question so as to lose context
-it is a linear programming problem ( I don't know enough Excel to know its applicability so I defer to spikepl)

Good luck.

 

[plog]

You must make an optimal blend from these buckets (optimized for Profit) where the Blend must be 100L but <= 25% water.

Hopefully you oversimplified this, because its really easy. The algorithim is to start with the cheapest and take as much as you can from it. If you need more, move to the second cheapest and take as much as you can from it. If you need more, move to the third....

 

[ions]

Thanks for everyone's responses.

I was able to solve the problem with Excel's Solver Add-in and using Automation in MS Access. Excel's Solver is extremely powerful. Excel's solver can solve the optimization with 10 buckets and 7 constraints extremely fast and efficiently.