Inventory Freight Calculation

To account for freight on incoming inventory, I allocate the freight charges proportionately over the inventory items by total value. Quickbooks doesn’t have a way to handle that, so I use a simple Excel spreadsheet to do the calculation.

inventory freight calculator

The last entry is always the freight (or handling or whatever I want to distribute). I don’t have much information here because it’s just a throwaway calculator. I get the numbers I need for data entry from the Totals column and don’t save the workbook.

I start by making a couple of defined names. First, ‘Freight’ is defined as =OFFSET(Sheet1!$B$2,COUNT(Sheet1!$B:$B),0).

It picks the last used cell in the Line Items column. It starts at B2 and COUNTs down the number of filled cells. Note that I don’t use COUNTA because I don’t want to count B2, which is text.

Next I define ‘LineItems’ as =OFFSET(Sheet1!$B$2,1,0,COUNT(Sheet1!$B:$B)-1,1)

That picks up all the numbers in column B except the last one.

C3: =IF(ISBLANK(B4),0,ROUND(Freight*(B3/SUM(LineItems)),2))

The above formula allocates the freight into the Freight column. Download FreightCalculator.xls.zip

Posted in Uncategorized

4 thoughts on “Inventory Freight Calculation

  1. Alternatives
    a)
    C3 =(B4>0)*(LOOKUP(BigNum,$B:$B)/(SUM(B:B)-LOOKUP(BigNum,$B:$B))*B3)

    or with a Named Range “Freight” =LOOKUP(BigNum,Sheet1!$B:$B)

    C3 =Freight/(SUM(B:B)-Freight)*B3

    b)
    The Freight Column is not required:

    Without separating the freight the formula is

    =(B4>0)*(1+Freight/(SUM(B:B)-Freight))*B3

  2. I do this kind of allocation often enough and in enough varied situations that I’ve added an array function to my addins to do it. That lets me select the output range and type a single formula directly into whatever sheet I’m working on. In many cases I’ll immediately copy and paste values to eliminate the formula. In your application, you’d still need to add the distributed freight to the line items (or modify the function to do it).

    Public Function Dist(dTotal As Double, baseDist As Variant, Optional rDigits As Integer = 2) As Variant
    ‘ Array function, select destination range first, end with Ctrl-Shft-Enter
    ‘ Distributes 1st argument (scalar) like 2nd argument (column range) into
    ‘ selected column range, rounding to rDigits digits.

        Dim baseArray As Variant
        Dim dRatio As Double
        Dim i As Long

        baseArray = Application.Transpose(baseDist)
        dRatio = dTotal / Application.Sum(baseArray)
       
        For i = 1 To UBound(baseArray)
            baseArray(i) = round(baseArray(i) * dRatio, rDigits)
        Next i
       
        Dist = Application.Transpose(baseArray)
       
    End Function

  3. Unfortunately the formulae given can result in rounding errors. Consider 3 line items each for 100 and freight of 10. A formula of C3 =IF(ISBLANK(B4),0,ROUND(Freight*(SUM($B$3:B3)/SUM(LineItems)),2)-SUM($C$2:C2)) will return freight amounts of 3.33, 3.34 and 3.33. Arbitrary, but everything balances!


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *