Formula Help: Vehicles On Railcars

I am trying to write a formula that will calculate the load efficiency of loading three model vehicles on two types of railcars. The loading capacity of each deck of railcars is dependant upon the model. A brief recap of the specifics follows:
1. Two types of railcars; tri-level, 3 decks and bi-levels, 2 decks
2. Three vehicle models, SS, VS and VH.
3. The cost to transport a tri-level is about the same as the cost to transport a bi-level. As a result, the objective is to load/transport as many vehicles as possible in the fewest number of railcars.
4. The number of models that will fit on each deck of the two types of railcars (tri and bi-level) and the monthly volume follows:
5. The attachment outlines the load factor per railcar deck and type as well as the volume for three periods. Any help you can provide in helping me write a formula to load as many vehicles on the fewest number of railcars possible and display the number of railcars and vehicles loaded and transported will be appreciated.

Thank you

In order to accomplish your needs, you need to run through different iterations of maximizing use of the railcars, so your attachment is a bit simplistic in that it infers that a single formula will suffice. I’ll try to explain the logic that I would use to determine the results.

The object is to use as few railcars as possible to ship your products. There are several considerations – the configuration of the railcar to maximize space (different products take up more/less space on the railcar, some products only fit in certain configurations) and the number of products to ship in the time period. These factors affect the number of iterations needed.

Where I started was to consider the max number the railcar can hold and ship as many items in that configuration as I could. The 3-deck railcar can hold a max of 15 items if you ship 6 of Model VH on the top deck & 9 of Model SS in the middle (5 item) & bottom (4 item) decks.

The next best configuration (there are 2) will hold 14 items:
1) 5 of Model VS on top & 9 of Model SS in the middle (5 item) & bottom (4 item) decks
2) 14 of Model SS in all 3 decks (5-5-4)

So, I would then determine the next configuration to use based on the lowest balance of the remaining products to fit into the next best configuration. For example, in the 15 unit config, I divided the number of units to ship by the max qty the car holds for that model (for Oct, model VH forecast is 6178/6 = 1029 with 4 remaining). Here’s my results:

Oct :
1029 three-tier, 15-unit cars (6VH + 9 SS)
662 three-tier, 14-unit cars (5 VS + 9 SS)
229 two-tier, 10-unit cars (10 VS)
1 two-tier, 10-unit car (1 SS + 4 VH on top, 5 VS on bottom)


Nov :
726 three-tier, 15-unit cars (6VH + 9 SS)
522 three-tier, 14-unit cars (5 VS + 9 SS)
60 two-tier, 10-unit cars (10 VS)
1 two-tier, 10-unit car (1 VS + 4 SS on top, 5 VS on bottom)

Dec:
335 three-tier, 15-unit cars (6VH + 9 SS)
385 three-tier, 14-unit cars (5 VS + 9 SS)
540 three-tier, 14-unit cars (14 SS)
1 three-tier, 14-unit cars (12 SS)
1 two-tier, 10-unit car (2 VS on top, 4 VH on bottom)


Based on the forecast shipments of each model, the results will vary. In December, the forecast ratio for SS model was roughly 7:1, but in the previous 2 months it was around 3:1 or 4:1.

Now that you have an understanding of my logic, you may want to investigate using some of the data analysis tools in Excel.


Hope this helps,
theDude

Thank you very much for your help. I see now that I was going down the wrong path and will approach differently.

Thanks again,