Please help me with this transportation problem

Gregory Richards, Vice president of logistics at Pasture, a dairy company in Montana foothills, USA, is planning to adopt sustainability practices in his company. As a Part of this initiative, Greg believes that his company could significantly improve inbound supply costs from transportation by investigating the possibility of using various modes of transportation. At its disposal, Pasture has a variety of intermodal combinations. Monthly demand is about 3,500 tons of raw materials. Hang is a transportation costs analyst. He was assigned to analyze the different intermodal combinations and to come up with best solution.

Hang figured out the following information:

From the supplier base, raw materials can be transported by rail to the train station/inland harbor in transit area 1 (320 km) or the train station/port in transit area 2 (1160 km). By road, it is possible to drive to Pasture directly (1197 km). An inland ship can travel from the inland harbor of transit area 1 to the inland harbor of transit area 4 ([=RANDBETWEEN(900,1000)] km). The inland harbor of transit area 4 can also be reached by inland ship from the port of transit area 2 ([=RANDBETWEEN(70,90)] km). Finally, Pasture can only be reached by truck, it takes 26 km to drive from the inland harbor of transit area 4 to Pasture.

Distribution costs are $1 per km/ton for a truck with a capacity of 40 tons. An inland ship from the harbor of transit area 1 to the harbor of transit area 4 costs $13.75/ton per trip with a capacity of 1,000 tons. An inland ship from transit area 2 to transit area 4 costs $9.75/ton per trip with a capacity of 1,000 tons. Rail tariffs for a capacity of 800 tons from supplier base to transit areas 1 or 2 is $25.4/ton. Price of the raw materials is $48 per ton (Ex works). Inventory holding cost is 3% per ton per month. In-transit inventory holding cost is 1% per ton per month. Operating speed of the train is 80 km/h and operating speed of the ship carrier is approximately 28 km/h. The operating speed of the truck is 100 km/h. Safety inventory is kept at twice the consumption during the lead time of supply. What mode or intermodal of transport do you recommend and why? One month = 30 days and one day = 24 hours.

NOTE: Copy Randbetween formula to an excel sheet to get actual numbers and then substitute with the actual numbers.