Making Excel calculate different options and give me the closest one

[rokko12]

Hi guys,

I have recently started a new business and I am trying to optimize my ordering procedure, so that I can lower my costs. I am really new to excel, so I am not exactly sure how and if it is even possible to do the following.

I have 3 columns. In the first one are my products names, in the 2nd one are my orders (number of units) for the next week, and in my 3th column I have categorized my products as either fast movers or slow movers depending on the sales.

The problem comes that I have limitation in the number of products I can put in one order. So what I want to do is to make the process of arranging orders automatic.

Example: I usually can put about 30 items in one order. For next week I need order 10 units from Product 1, 17 units from Product 2, 5 units from Product 3, 3 Units from Product 4. What I want to do is to tell excel to combine Product 1 + Product 2 + Product 4 in one order, so that I fill 100% of the available capacity.

Also, do you think it is possible to tell excel that if there is no existing combination, then add X units from random products with value "fast mover".

Do you have any suggestions how this can be implemented?

Thanks in advance.

[hoyasaxa215]

Can you upload a sample file to give us a better idea of what you're looking for?

[rokko12]

Basically since I have hundreds of products I will need Excel to give me the name of the product and the quantity. Do you reckon that will be possible?

test.xlsx

[rokko12]

Any suggestions?

[hoyasaxa215]

I'm not sure if this is possible, unless you use solver. You may have better luck posting this in the commercial section.

[Crooza]

Rokko12


if you only order 4 products at a time then even then you're going to have 96 differnet combinations (4 x 4 x 3 x2) being four products to choose from then 3 plus the option of nothing then 2 plus the option of nothing then 1 plus the option of nothing. Is it only ever 4 products you order at any one time from a possible 100.? If so then the 96 combinations can easily be optimised but if you potentially order up to 100 products (noting that there is a limit of 30 per package) then this gets significantly more complex.