Number of items into a box>onto a pallet>into a container calculator

[rotamasta7]

Hi All!

First post, I have done some forum hunting but couldn't find exactly what I was after, I found many container calculators (How many boxes fit onto a pallet or into a container)

However, I am trying to build a calculator for my team where they can enter a type of product, and how many they want, and it presents how many boxes it will likely use, how many pallets it will take up, and how many of these pallets will fit into a container, taking into consideration the boxes being used, and the pallets being used.

I have attached what I have so far, however, I am quite stuck on where to even start formula wise.

The end result I am seeking:

How many of a particular unit (T-shirt, sock, etc) will fit into a container. I am trying to find the thresholds, to assist a sales team (meaning, if a client is seeking 18,000 socks, I would like to know if there is room in a container for 19,000 socks so we can push this to the client, explaining there wouldn't be a huge increase in logistics cost if they went from 18,000 units to 19,000 units.

I also have a table of country to country freight costs I want to fill so it eventually also calculates an estimate of cost per unit and total freight cost.

I apologise for my explanation, I hope you will understand what I am trying to achieve.

[FDibbins]

It seems there is a problem with your file, can you try uploading it again?

[rotamasta7]

Hi FDibbins!

Please try this instead, not sure what was wrong with the last one.

Thanks for looking into this for me.

[MrShorty]

This really feels like a "packing problem". Most Excel based solutions will probably start with something using Solver to optimize the desired parameter (shipping cost or wasted space or whatever). Here are some examples put together by the programmers of Excel's Solver: https://www.solver.com/optimization-...stics-examples I would spend some time with those to see how they set up the spreadsheet and Solver model to solve these packing/logistics problems. Then look at adapting them to your scenario. Once you have the packing problem part solved, then you can calculate unused space and use that to explore what you might be able to do with the unused space.