Optimise using VBA (without solver)

[djibbo]

Hi guys, I am wanting to find the optimum product mix and profit for a purchase decision using VBA but without solver. The product mix can vary each week and lets just say for ease of use the are 5 weeks. There are two products (CD's and DVD's) that have different costs (storage costs, purchase costs etc). I have built a model that already takes all of this into account and so the only things I need to vary to affect profit are the amount purchased each week. Lets say we have a limit of 6 CDs and 6 DVDs a week. I am wanting to use VBA code that will test all possible outcomes and will then show the optimal outcome.

If its easier for you guys let;

A1 = CD's purchased in Week 1
A2 = DVD's purchased in Week 1

B1 = CD's purchased in Week 2
B2 = DVD's purchased in Week 2

etc.

Any help would be greatly appreciated, thank you!

[Richard Buttrey]

What's the aim? Presumably to maximise profit.
Then are you trying to maximise the profit each week or for all five weeks.

In any case isn't the best possible 'mix' simply 6 of each if that's your maximum production capacity. I'm certainly not clear how you are defining permutation 'outcomes'.

Perhaps you'd better upload the workbook and manually add the results you expect for two or three different scenarios

[MrShorty]

You say that you have a model. With a model in place, the overall process I would expect for this:

1) Define what "optimize" means in the context of my model. Is it, as Richard guessed, maximum profit? Or maybe minimum cost? or something else? Once I know what I mean by "optimize", make sure I am calculating the appropriate "objective function" that represents that optimum somewhere in the spreadsheet.
2) This might be more math than programming, but then I would expect to spend some time exploring the behavior of the objective function. Does the OF have multiple local optima that can confuse some algorithms or is there only one optimum? Does the OF continuously increase or decrease?
3) Once I have some feel for the behavior of the OF, then I can look at possible algorithms for finding the optimum. Perhaps my efforts in 2 allow me to use what I learned in calculus about finding optima and I can take a few partial derivatives and come up with a closed form solution. Or I see, as Richard guessed in a max profit scenario, that max profit will occur at max sales/max capacity, so you can simply use that. Or some other strategy. Or maybe, after all other options have been discarded, I decide, like you, that a brute force "try every possible input combination" is the only way to find the optimum.
4) Then I start programming my chosen algorithm.

Without knowing anything about your model or your desired OF, it is difficult to do step 2 and, therefore, make any solid recommendations. If you are committed to the brute force try every possible input approach, I have this thread that talks a little about generating permutations in Excel and VBA: https://www.excelforum.com/tips-and-...thout-vba.html Assuming that your main question is about how to generate permutations, that should help with that part of the programming. Of course, one of the goals of this kind of programming is to find more efficient algorithms than brute force algorithms, so it is probably worth some effort to identify better algorithms.

[djibbo]

What's the aim? Presumably to maximise profit.
Then are you trying to maximise the profit each week or for all five weeks.

In any case isn't the best possible 'mix' simply 6 of each if that's your maximum production capacity. I'm certainly not clear how you are defining permutation 'outcomes'.

Perhaps you'd better upload the workbook and manually add the results you expect for two or three different scenarios

As there are many factors such as delivery time and storage costs the maximum product mix isn’t the best each week as further on in the weeks I go, the less product mix would be more efficient for profitability.

[Richard Buttrey]

..as I said perhaps you'd better upload the workbook with some examples.
From what you're saying their are constraints on some variables, or some which may be a key variable since they're limited.

[djibbo]

Screen Shot 2018-12-15 at 19.37.16.png

How's this? As you can see many factors affect profit. Including: demand, the cost of not meeting demand (and thus them going to another supplier) etc. The whole point of the VBA code is to not include these factors though - as I want it to be dynamic. The only cells I need included are the product mix cells B3:F4 and the total profit cell F16. As many factors will eventually be added or taken away I need it to be dynamic. I have managed to do this myself with a constant product mix and so I know it is possible. However, I am struggling at finding a way to use a varied product mix.

[djibbo]

..as I said perhaps you'd better upload the workbook with some examples.
From what you're saying their are constraints on some variables, or some which may be a key variable since they're limited.

I have uploaded a picture, hope this helps

[JeremyTran]

Support for newer VBA, revised file for working
Hi everyone
Please help me revised some code in VBA for extend Combobox Contents or change it to other sheet .

Thanks so much
JeremyTran/Mr

[MrShorty]

Pictures are not always helpful, because we cannot see the formulas behind the cells. However, doing my best to infer what is going on and doing a little algebra (tedious but not difficult), I make the following conclusions/observations:

1) Because of the way storage costs accumulate, storage costs have a big impact on total profit. Storage costs are greater for units purchased in earlier weeks than units purchased in later weeks. It appears to me that storage costs are going to be the main driver of max profit. In other words, max profit will occur when storage costs are at a minimum.
2) Unmet demand does not seem to figure into the current profit model, so there is no penalty for not purchasing.
3) The only week where the net profit per unit is positive is the final week. I see no difference in the profit per unit for CD or DVD.
4) I conclude that the max profit will occur by letting the shop sit empty for the first 4 weeks, then exactly matching demand in the final week. Because profit per unit appears the same, the ratio of CD to DVD in this last week does not matter.

As suggested, those conclusions all come out of the algebra of the problem, no programming needed. I'll spare you the details of the algebra (it is tedious, but not difficult). I recommend that you spend some time with the algebra so you can see how profit per unit changes with week bought and such. Getting a good handle on the algebra will also allow you to see how things change in different scenarios and models (for example, how does it change if you incorporate the unmet demand cost in the total profit?).

With those observations and conclusions, the programming is to simply identify the demand for week 5 and purchase that many units. No need to come up with a DIY solver type algorithm.