Cost Averaging for Inventory

[jasonwood]

Here is a method I came up with for applying the weighted average method of inventory valuation in a FileMaker database. Please share your alternate methods, especially if anyone has figured out a reliable way to use FIFO!

When product is received on a PO:

Set productUnitCost = ((productInStock x productUnitCost) + (qtyReceive x poUnitCost)) / (productInStock + qtyReceive)

here's the same thing only closer to English:

Set the new product cost to:

value of current inventory PLUS value of received inventory, ALL DIVIDED BY the new total inventory.

At the point of sale, this cost would be recorded on the invoice and the quantity would be appropriately decreased.

I'm pretty sure this follows the weighted average method of inventory valuation to the letter... does anyone see any problems?

[BobWeaver]

What it comes down to is: total amount paid divided by total quantity purchased. I think that's what you will get in your calculation assuming your resulting productCost is intended to replace existing productUnitCost.

This isn't weighted average though. It's adjusted cost base.

[jasonwood]

Hey Bob,

Sorry I meant to say productUnitCost... I fixed my original post.

This might be a little off-topic but do you know why it is adjusted cost base and not weighted average? I had understood "adjusted cost base" as a type of "weighted average" applied to the amount you pay to buy units of a mutual fund.

Or is it because it is calculatd perpetually rather than at the end of the month only?

[BobWeaver]

On second thought, I guess you could call it a weighted average, although I think this can be a misleading term when you are dealing with identical items. Maybe it's a matter of semantics.

Adjusted cost base is indeed a term used in valueing mutual funds and equities (or anything for that matter) in order to determine net gain/loss when some or all are sold. It is calculated as the total amount paid for the entire lot, divided by the quantity. When you buy several lots at different prices, you can calculate it as:

((Lot1Qty * Lot1Price)+(Lot2Qty * Lot2Price)+(Lot3Qty * Lot3Price)+ ... +(LotNQty * LotNPrice) ) / (Lot1Qty+Lot2Qty+Lot3Qty+...+LotNQty))

or you can calculate it as the total money spent on everything divided by how many you bought. The number should be the same. The method you use depends on how your data is stored.

[Ugo DI LUCA]

I do not use FIFO, but her's my method. I am sure lots of inprovements can be made with this file, but that's also why I post to this forum... So

{Just a second to explain the complexicity I deal with:

1- I sell ceramic tiles coming from Italy to France

2- I have different suppliers in Italy

3- My transporter bills me depending on the weights for each "Depot" I carried out

4- I created a CarrytoDepot Order DB as :

- Some suppliers have their own depot

- Some have 2 or 3 differents depots depending on the category of tiles in my order

One unique Purchase Order could be carried in 3 different depots

(ex : Walls Tiles in A, Floor Tiles in B, Decoration&Accessories in C)

- Some use a Local Transit Depot - 3 different Local Depot existant for today.

When I receive the invoice of my transporter, I fill the record "costdepot" with the

amount detailled per depot (ex. Depot A - 500 Kg x 120,00 euros/Ton = 60,00 euros.

5. I run the total amounts for all depots I carried for the same Transport Date in a portal of Transport DB.}

OK, here's my actual solution :

A. My inventory is in my Products DB.

B. I don't consider received customer orders as stock entry as they cannot be sold to other customers.

C. All the datas are registered in a Main file I call Central and related to a portal in Products DB, in a specific layout for stock/inventory.

The Portal shows all datas related to ProductN

[jasonwood]

The reason I ask is just because the government of Canada will only accept "Weighted Average" or FIFO for inventory reporting (for tax purposes any ways).

When I learned the Weighted Average method, I learned it as a calculation performed at the end of the month based on qty/price of items in stock and purchased----in order to come up with a "weighted average" unit cost to apply to all items sold.

The difference is that in a Perpetual Inventory system you record a cost in your line items at the moment you invoice a sale (in which case your weighted average may change throughout the month). As far as I can tell, the end result will be close (but the affect is slightly delayed), but it certainly evens out in the long run.

[BobWeaver]

I think your method should work just fine.