1. ## Solver constraint problem

Hi all,
I am trying to figure out a way to optimize the sales of items at a number of markets. The number of items is arbitrary as is the number of markets. The best case is to sell each item at the market that has the highest price. However, if I want to limit the number of trips I take, I am having trouble optimizing the market selection. Here is a shot of the sample worksheet (numbers in the Mkt and Total cells are \$):

Microsoft Excel - solver test.jpg

A "1" in the Trip columns means the item is sold at the corresponding market and are adjusted by the solver. I have constraints that the green area be 1 or 0, the trip count column is the sum across the green row and must be "1" (can only sell the item at one market). I am optimizing for the blue cell which is the sum of the totals above it.

In this example, items are being sold at all markets and they are the highest price markets which makes sense (this is a result of running the solver as a test). The ones in the pink cells have a formula like =IF(SUM(E2:E9)>0,1,0) in them with the idea that the red cell could sum those cells and I could constrain it to "2". However adding that constraint causes the solver to fail (on Linearity), I'm guessing because of the IF statement. I can't think of any other way to make this constraint, help?

Thanks,
Eric

2. Could you attach an example file with the setup?

Steve

3. Hi Steve,
Added the file to the original post.

Thanks,
Eric

4. If you use GRG nonlinear you do not get that error, but I think it will have problems solving it

You can do it yourself. Fundamentally, if you want H10 to <=2 you only want 2 trips, not 3, so it is either only 1&2, 1&3, or 2&3. You can easily solve by taking the max of the 2 columns for each item to discover that I10 is max at 289 for 2&3 (1&3 = 282 and 1&2 = 264) so this is your best bet. The items in F&G will be 1s for the max of the market values. Mkt1 (col E) will all be zero. Mkt2 (Col F) will be 1 for items A,D,F,H and Mkt3 (Col g) will be 1 for items B,C,E,G

Steve

5. Hi Steve,
This was a test worksheet, I figured the answer using the solver would be scalable... The number of markets could be ~4X so it becomes harder to do manually. If the solver can't handle it, I would probably go with the Max function approach in VBA and let it run through all the cases. I've not used the solver before so this was also an attempt to become familiar with it.

Thanks,
Eric

6. It doesn't matter what the number of markets are if you only want to go to 2 of them. It seems to me it becomes too constrained for solver to handle to get the best solution (it will get a solution). I think you must work with THAT constraint first to create different problems to solve and then choose from those solutions

You may want to include something about the cost of each trip to really have an optimized solution. Are you constraining the number of markets for a valid economic reason?
Steve

7. ## The Following User Says Thank You to sdckapr For This Useful Post:

RunDeep (2013-08-16)

8. Hi Steve,
The number of markets is ultimately variable, and, as you noted, the number of trips should be based on some cost factor (still working on what that formula would be). As a start I was going estimate the number and plug it in; seems like it should be something the solver can work with if I can get rid of the "IF" statement. Found some starting info here: http://www.solver.com/premium-solver...ooth-functions and https://groups.google.com/forum/#!ms...I/OlVH8LJa-v4J.

Thanks,
Eric

9. There may be more direct ways to solve it rahter than solver. It depends on the cost of extra trips. For example, the max amount with 3 trips can be calc as 312. The Max amount with only 1 trip is 242 (Mkt3). The max Amt with 2 trips is 289 (Mkt2&3). The question is does the 47 more you get from just going to Mkt3 make it worthwhile to go to Mkt 2 as well. And if that is worthwhile, is it worthwhile to go to Mkt1 to get an additional 23.

Additional items do not really pose a calc problem, the problem is how many markets you may have to choose from. With the 3 you have only 7 choices (1,2,3, 123, 12, 13, 23) and they can be calc'd directly. 4 Mrkts start expanding with 15 possibilities, but they can also be calculated to see what is the best and how much you gain by adding a Market.

Steve

10. After some thought I think the best approach would be to not only have the cost of the item for each market, but the cost to go to each market. The number of choices are (2^n)-1 where n is the number of markets. You could use VB to loop through each of the numbers marking whether you go or not to that market and calculate what the total is and then get the max dollars. I don't think solver could do this. Solver does not always pick the BEST solution, just a solution that meets the criteria.

Steve

11. Hi Steve,
I'm quite comfortable with VBA so I'll give it a try.

Thanks,
Eric

12. If you need any additional help, post back. I think a good approach would be to include a row of 1s and 0s to indicate which markets you are attending and then calculate the max of the items you are attending, then you can sum those maxes. The VBA would only need to loop through all 2^n-1 combinations of 1s and 0s (the "-1" is the all zero option: going to no markets, you know that will not be the best) and have the VBA read the total.

You could eliminate some markets by looking to see if they have the max for any item. Any markets that do not have the max for any item are most likely not going to gain anything by going there [this may not be strictly true, however, you could have a market that everything is a close 2nd and just going to that one gets the best without having to go to a bunch of different markets for 1 or 2 points more.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•