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
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
That picks up all the numbers in column B except the last one.
The above formula allocates the freight into the Freight column. Download FreightCalculator.xls.zip