Optimizing transportation cost using a max. 2 supplier constraint

Question

Hello community,

We are currently conducting a tender for airfreight services. We have received over 1500 bids from 10 suppliers, resulting in approximately 15,000 rates for  > 50 locations.

However, we are encountering an issue as we aim to restrict the number of suppliers to a maximum of 2 per origin location.

Could you please suggest a suitable tool and provide guidance on how to utilize it to determine the most cost-efficient pair of suppliers per location of origin?

I considered using a linear regression model, but I'm struggling to incorporate the constraint of limiting suppliers to a maximum of 2.

Thank you in advance!

TijsBooij · Answer

Thank you for the quick reply, I have included a .csv with sample data. 10 suppliers, 10 locations with each 30 lanes and 30 prices. I have been playing with the Optimization tool, but can't get the max. 2 supplier limit to work properly.

Thanks again

dummydata airfreight tender.csv

sparksun · Answer

I think optimization tool may be the best one for your scenario.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/Tool-Mastery-Optimization/ta-p/306117?utm_content=zh&utm_source=designer_2023.1.1.123_trial-False&utm_medium=global_search

philipmannering · Answer

Hi @TijsBooij,

This sounds like a job for the Optimization Tool - but this can be a bit tricky to set up. If you send some sample and/or anonymized data I can see if I can help set this up.

Thanks,

Philip