How to solve such an optimisation problem?

Hi guys,

So I was planning on referring to the solver function, but i'm not sure that it is suitable for the problem I have at hands. Let me try to explain as clearly and concisely as possible the situation:

• I am going to market to buy (say 100 as in the spreadsheet) fruitbowls.
• I have 3 bags that I want to split them into according to a specific breakdown 75% in bag 1, 20% into bag 2, and the remaining 5% into bag 3

The key question I have is to do with the allocation into these 3 bags, which should meet the following 3 conditions:

• 1) The average price of the bowls in each bag should be exactly the same. I.e. as in the example spreadsheet the overall price is 104.55 paid. So the average price of the bowls in each of the 3 buckets should be as close to this number, and the same across all 3
• 2) The number of bowls in each bucket meets my target split (i.e. 75/20/5 split)
• 3) Only whole numbers of bowls exist in each bucket, and any 0.5 get rounded up to 1

I've attached an example spreadsheet as described here. Any help would be greatly appreciated.

I'm not sure I'm clear on what an "answer" to your inquiry would look like. Are you looking for an optimal distribution to this particular scenario? A strategy that can be applied to other scenarios like this? Given that the prices seem specific to this particular product, my guess was that you're looking for an approach to help you find the best distribution in cases like this. I came up with the breakdown in the attachment. I set up a mini-workspace in C9:E16 with lots of automated info surrounding it to aid the allocation process. I then started with the smallest bag (5) and worked my way toward the biggest (75). With all of the peripheral info updating, it wasn't too tricky. I am NOT very experienced with solver, though, so there definitely might a better approach using solver, but even then the additional info in my setup might prove useful in defining your solver constraints. Take a look at the attachment, hopefully it helps?

Hi Cantosh, thanks - this looks great.

The only problem is I can't seem to be able to view any of this automated info you talk of? I'd like to see how the solution works so I can better understand it.

Thanks

Originally Posted by andre199017

The only problem is I can't seem to be able to view any of this automated info you talk of?

Glad I could help! The "automated" bit I was referring to is D2:D4, F9:F17, and C17:E19 from my sample (F2:F4, H9:H17, and E17:G18 in Alf's version). Using those as guides, I solved the problem by manually toying with C9:E16 to get the numbers to work. Starting with the 5 bowl bag (least flexibility) and targeting outliers first, it wasn't hard to fill the 5 and 20 bowl bags to hit the target prices in D2:D4. Once that's done, all of the remaining values in F9:F16 simply become your values in C9:C16 for the 75 bowl bag. I still had to solve the problem manually, but all of the automatically adjusting formulas surrounding C9:E16 made it very quick and easy to do so.

If you have to do this process frequently and with a wide array of prices, it would probably wise to follow Alf's lead and learn to master solver. Unfortunately, I'm too much of a solver novice to prove very useful in that regard.

Hi Alf - yes thanks I had discovered the exact some points as you.

The problem with the solver approach I am finding is that if you add more rows than we currently have (i.e. more prices of bowls), it doesn't seem to like that very much and throws out a solution cannot be found error - especially when the integer constraint is added.

The problem with the solver approach I am finding is that if you add more rows than we currently have

I would assume this is because the excel solver is limited (200 x 200) rows and constraints. I would recommend you to test the OpenSolver a freebie developed by the University of Auckland NZ.

It integrates nicely with excel and has no limitation as long as you problem is a linear one. I usually build my models using the excel solver and then let OpenSolver crunch the numbers

Link https://opensolver.org/

Alf

Unfortunately doesn't seem to find a solution either! "OpenSolver could not find an optimal solution and reported infeasible. The model contains a constraint....for which instance 9 does not depend on the decision variables and is not satisfied"

for which instance 9 does not depend on the decision variables and is not satisfied"

Something strange with your model perhaps? Can you upload the file with the model after removing sensitive information and see if some forum member may be able to solve this problem.

Alf