Packaging material requirement for packaging line (Personal Care Shampoo bottle line)

[mahesh5277]

Hi all,

I am working on a Packaging material requirement calculation for a Bottle line which has a annual production volume planned as 9.5 tonnes divided in to 4 SKU's ( A1-100 ml - 66%, A2-100 ml -20%, B1-180 ml - 11% & B2-180 ml - 3%).

Each SKU would be filled on a 300 upm speed Bottle filler& Capper with a integrated End of line ( Shrink wrapper+Case Packer). Now, the throughput in tons/hour will vary depending upon the size of bottle (i.e. 100 ml or 180 ml).
The formula envisaged is - Speed x OEE x 60 x No. of machines x Volume of SKU/ 1000000
So if i have A1-100 ml SKU running then the throughput would be : 300 x 75% x 60 x 1 x 100/1000000 = 1.35 tons/hr.

Now, i have a situation where in i need to calculate the Packaging material requirement for Bottles, Caps & Cases for this line in terms of pallets/hours of empty bottles, empty caps, empty cases & so on...
I have figures for no. of empty bottles/ pallet, no. of caps/pallet, no. of cases/pallet.

My problem is - How do i apportion the Volume % to calculate the PM requirement for any particular SKU (in this A1-100 ml) in terms of Pallets/hour of empty bottle, empty caps & empty cases. Remember this SKU contributes to 66% of the annual volume.

What is the formula in excel to arrive at a correct method to calculate the Pkg material requirement per shift

I don't have a excel right now to help u people understand.

Can anybody help please? URGENT

Thanks...

[mahesh5277]

Excel file attached. Pl check & provide your comments or a better way to perform calculations