Optimization Use Case in Alteryx

[ShrikantAlteryx]

Hello Community members,

I hope someone can help me with this use case that I am trying to build.

Problem Statement:

I am building a logistics cost optimization model for a manufacturing company. The objective is to minimize the total per-unit cost of sourcing parts from multiple suppliers to multiple plants while considering complex shipment constraints.

Key Requirements:

1. Production Demand:

   • Each plant has specific production volume requirements (e.g., 50,000 units for Memphis, 65,000 units for Detroit).

2. Supplier Selection:

   • Parts can be sourced from multiple suppliers with varying capacities, distances, and transportation costs.

3. Shipment Capacity Constraints:

   • Each truck has fixed cargo volume and weight limits (e.g., 2,000 parts per shipment).
   • If demand exceeds truck capacity, additional shipments are required.

4. Cost Calculation:

   • Per shipment cost: (Distance×Cost per Mile)
   • Per-unit cost: (Total Shipment Cost+ Total Parts Cost)/Total Parts Delivered. 
   • Additional shipments increase the per-unit cost when demand exceeds the truck capacity and we will need additional truck.

Optimization Requirements:

• Minimize the total logistics cost by optimizing:
  • Supplier selection
  • Shipment allocation to meet plant demand at the lowest per-unit cost
• Constraints:
  1. Production demand must be fulfilled at each plant.
  2. Integer constraint for the number of shipments.
  3. Non-linear cost function due to additional shipments when capacity is exceeded.

Challenges Faced:

1. Calculating per-unit costs dynamically when the number of shipments changes requires iterative computation.

Request for Assistance:

• How can I configure the Alteryx Optimization Tool to handle dynamic cost calculations?
• Are there alternative methods/tools to achieve this optimization in Alteryx?

[KGT]

Take a look at this: https://community.alteryx.com/t5/Data-Science/Legolytics-Part-4-Optimizing-Cost/ba-p/565416

And then see how you go. If the truck cost for extra shipments is the greatest determiner, then you may not need the full optimisation model. In this type of process, if you really need to shoehorn something in, then you probably need to look at that separately and see if there's another constraint. If you know something will cost too much, why leave that in the model options...

For instance, a constraint might be on order size, and so you can only order in certain ranges (as outside of those ranges costs too much). I.e. 1500-2000, 3500-4000....

You can make some decisions on the constraints and get a model working, then start broadening out the options you want to consider.

[DawnDuong]

hi @ShrikantAlteryx 

I interpret your question as how to use the standard Optimization tool to solve this problem.

your optimisation requirements include 2 criteria (i) supplier selection (do you mean the least number or the max number of suppliers? or something else?) and (ii) minimum per unit costs.

As far as I know, the optimisation tool only allow 1 objective, so if you need to solve for optimisation that involves >1 objectives, firstly you need to define the interaction/trade-off between these 2 critieria.

As to the 2nd part of your question, no issue as long as the objectively correctly, i.e. minimizing the average per unit costs (in any case objective has to be dynamically calculated otherwise you do not need optimisation - unless I miss something here).

If you can upload a dummy data set, the community more likely can help you find some solutions faster. For inspiration, explore the Optimsation example that has been shared in an earlier post or simply go to the recorded live training to find it.

Dawn.

[ShrikantAlteryx]

Thank you @DawnDuong for your response. The objective is to reduce per unit cost. but the challenge is if we decide to choose one supplier, the per unit cost will vary based on number of parts we decide to purchase from that supplier. e.g. Lets say we decide to order 2000 parts from a supplier who considers 2000 parts as Full truck load. In this case unit cost will be actual part cost plus shipping cost divided by 2000 parts. but we decide to order 2050 parts then the per unit cost will go up as those additional 50 parts would require second truck and shipping cost per unit will go up as we are not fully utilizing the truck capacity. I have attached a sample workflow will the data set. I hope I was able to clarify the requirement here.

[ShrikantAlteryx]

@reyna789 I am not sure what you are talking about, probably you responded to wrong post.

[ShrikantAlteryx]

Hi @DawnDuong ,

I now think that it will be too much to consider the the dynamic per unit cost based on full load truck shipping cost vs additional partial load cost. May be for the sake of simplicity, I will just use constraint as number of shipments should be integer so even if the parts spill in to additional shipment, the per unit cost for shipping can be considered same as full load shipment.

[ntakeda]

@ShrikantAlteryx 

The contents in Formula Tool have been slightly changed.

When the number of parts required changes, the Formula tool can be used to calculate the number of tracks needed.
Please refer to the workflow attached.

I would be happy if the problem is solved.

[ShrikantAlteryx]

Really appreciate your response @ntakeda . I was happy to see that I was on the right track. I was able to figure out how to set up the objectives, constraints and use them in the tool. I am attaching my workflow here, please let me know if you see any issues with it.

[ntakeda]

@ShrikantAlteryx 

I created and sent the above workflow based on my understanding as follows:

Input: Required number of parts and cost tables for each part.
Output: The supplier with the lowest cost for each part based on the required number of parts.

If the above understanding is correct, then the result of my workflow matches the requirements.
The output is as follows:

In my understanding, an optimization tool is not required, and the requirements can be achieved with the workflow I have attached.

If the requirements are different, please let me know.