I am working on building a tool to filter through and optimize data quickly and simultaneously. At a very high level, my data has vendors that all serve different stores and I am trying to choose which vendors should serve which stores at a lowest cost level, while minimizing the total number of vendors. The data is divided into city, small region, province, and region and the tool needs to be able to find the mix of vendors that can serve each of these geographic areas at the lowest cost, while having a maximum number of vendors that you can use - so it's an optimization problem minimizing the total cost.
The tool needs to have 3 separate functionalities as follows - the end goal is to find the mix of vendors in a certain geographic area that will be able to serve all of the stores in the selected geographic region at a minimum cost or score. This could be 1 vendor, but it also could be more. A simple example is also provided to illustrate the function desired at the bottom of this.
1) The ability to select the region, the criteria for optimization (minimizing either total cost or total score which I have built out already), and the constraints (i.e. 1 vendor per store, minimizing total score/cost, and minimizing the number of vendors).
2) The tool will then find the lowest cost/score for the geographic region selected according to the constraints.
3) Exclude the no offer vendors from the analysis (there are some stores that are associated with vendors in the raw data that have "no offer" tags)
For example, in a region that has 10 stores the tool could spit out that these 10 stores are best served by 2 vendors due to the fact that these 2 vendors serve all 10 stores with a lower cost than all other alternatives (i.e. any combination of other 1 stores, 2 stores, 3 stores, etc.). The constraints for this region would just be that there can only be 1 vendor per store, there is an arbitrary maximum number of vendors allowed, and that we are minimizing cost.
The goal is to have the model generate a list of stores and the specific vendors serving each particular store at the point of optimization.
------------
High-Level Example
Provided that we want to minimize the total cost for stores in the following scenario with the constraint of a maximum of 2 vendors in total (this is just an example, we should have the freedom to control the maximum number of vendors to be selected), we would be picking Vendor X and Y in the end by having them serve the corresponding stores which are lowest cost, which gives us a minimum total cost of $225. Note that the vendor mix will change if we increase the maximum vendor constraint to 3 which would make Vendor Z the best choice for Store C with a minimum total cost of $215.
Store A Store B Store C Store D Store E
Vendor X $30 $70 $50 $10 $90
Vendor Y $20 $60 $70 $5 $100
Vendor Z $35 $80 $40 $15 $120
Hoping for help in 2 areas:
1) Can solver do something like this in optimization (I've heard it can only take up to 200 lines and this data has upwards of 5000 lines of data)
2) Does anyone have any VBA code that would help me in the analysis
Thanks!
The tool needs to have 3 separate functionalities as follows - the end goal is to find the mix of vendors in a certain geographic area that will be able to serve all of the stores in the selected geographic region at a minimum cost or score. This could be 1 vendor, but it also could be more. A simple example is also provided to illustrate the function desired at the bottom of this.
1) The ability to select the region, the criteria for optimization (minimizing either total cost or total score which I have built out already), and the constraints (i.e. 1 vendor per store, minimizing total score/cost, and minimizing the number of vendors).
2) The tool will then find the lowest cost/score for the geographic region selected according to the constraints.
3) Exclude the no offer vendors from the analysis (there are some stores that are associated with vendors in the raw data that have "no offer" tags)
For example, in a region that has 10 stores the tool could spit out that these 10 stores are best served by 2 vendors due to the fact that these 2 vendors serve all 10 stores with a lower cost than all other alternatives (i.e. any combination of other 1 stores, 2 stores, 3 stores, etc.). The constraints for this region would just be that there can only be 1 vendor per store, there is an arbitrary maximum number of vendors allowed, and that we are minimizing cost.
The goal is to have the model generate a list of stores and the specific vendors serving each particular store at the point of optimization.
------------
High-Level Example
Provided that we want to minimize the total cost for stores in the following scenario with the constraint of a maximum of 2 vendors in total (this is just an example, we should have the freedom to control the maximum number of vendors to be selected), we would be picking Vendor X and Y in the end by having them serve the corresponding stores which are lowest cost, which gives us a minimum total cost of $225. Note that the vendor mix will change if we increase the maximum vendor constraint to 3 which would make Vendor Z the best choice for Store C with a minimum total cost of $215.
Store A Store B Store C Store D Store E
Vendor X $30 $70 $50 $10 $90
Vendor Y $20 $60 $70 $5 $100
Vendor Z $35 $80 $40 $15 $120
Hoping for help in 2 areas:
1) Can solver do something like this in optimization (I've heard it can only take up to 200 lines and this data has upwards of 5000 lines of data)
2) Does anyone have any VBA code that would help me in the analysis
Thanks!
