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.

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

No related posts.