1

I originally posted this in superuser, but was told it was more of a math problem than an Excel problem.

I'm working on a project, part of which involves figuring out the number and type of vessels required to transport x amount of crude oil over a given trade route. The table below shows the required number of ships assuming that all ships are of the same type (i.e., 0.83 of a VLCC as defined can cover Route 1), but obviously this becomes impractical considering the volumes vary dramatically per route and a different combination of ships may be needed.

What I have been struggling to do is to find a solution that allows me to optimise the number of ships for each route, and produce an output with the number of each shiptype needed per route.

I have tried various things based on the almighty Google and practically spent my day scratching my head, but none seem to apply to this problem. I also tried using Solver, but could not make it work... I'm at a loss.

Table

PS: Dwt = Deadweight tonnes = Vessels cargo-carrying capacity in tonnes.

youjustreadthis
  • 113
  • 1
    What are the units? What does the different labels represent? – Alice Ryhl Jun 12 '15 at 11:18
  • VLCC, panamax etc are size categories of ships. So 0.83 is a result of the round trips needed to carry x amount of cargo divided by the number of possible trips per year for that route at a certain speed. Hope that made sense. – youjustreadthis Jun 12 '15 at 11:35
  • I should be able to minimize the amount of ships, but surely having one VLCC is more expensive than having one handysize ship? Wouldn't you rather want to minimize costs? – Alice Ryhl Jun 12 '15 at 12:05
  • Also, can ships work on several routes? Like a Handysize ship has the capacity to take care of route 12, route 13, route 14 and route 15, but could it do that or do you require seperate ships for each route? – Alice Ryhl Jun 12 '15 at 12:17
  • it is generally cheaper to carry cargo in as large volumes as possible, due to economies of scale. but it's more about carrying the cargo in as few ships as possible. yes one ship can do several routes. – youjustreadthis Jun 12 '15 at 12:44
  • It seems like the best is 4 VLCC ships distributed over all the routes. Does something like that fit your needs? If so I'll write up an answer describing why. – Alice Ryhl Jun 12 '15 at 13:16

0 Answers0