Forecast model for load/delivery system

Good day!

My skills are not capable of creating the following but hope someone can help me out!!

Each month I must nominate trains to leave from one of 6 locations and arrive at a plant just outside DENVER at GRAND JUNCTION. Each train will carry one of the five types of material.

The tricks are, there is only one off loading point at GRAND JUNCTION, so the trains need to arrive in a ratable manner throughout the month, while also trying to keep the material arriving ratable. i.e. I can’t have all batches of POTATO arrive at the beginning, or all the WOOD at the end (There must be consistency on what and when material is delivered).

So, for example, I need 20 batches to arrive at GRAND JUNCTION next month.

WOOD - 5
COOPER - 3
TIN - 2
CORN - 6
POTATO - 4

Considering the month has 30 days and we have 20 batches, there should be 0.667 batches daily loaded.
The furthest distance is FARGO to GRAND JUNCTION, 11 days. Shortest is from DENVER at 2.5 days.

FARGO – SIOUX FALLS = 2 days
SIOUX FALLS – SIOUX CITY = 1 day
SIOUX CITY – OMAHA = 1day
OMAHA – LINCOLN = 0.5 day
LINCOLN – DENVER = 4 days
DENVER – GRAND JUNCTION = 2.5 days

For the life of me, I cannot create anything in excel that can help me out. I really need something that can compute a forecast schedule based on the following inputs that control what and when things show up. Ideally, the output would look like this, but with load and delivery dates generated:


LOAD SPOT MATERIAL LOAD DATE DELIVERY DATE
FARGO WOOD
OMAHA CORN
Etc...

If someone can assist me, it would make my life 10000X better!!

Attached is a file with what should be needed to start a formula!

Thanks!

Related but not the same thing.

This current post is about developing a forecast model for the trains and their products.

The question related to the link you have copied is about developing a location specific tool.