I wanted to get some input from some of you guys about calculating "Average Inventory".

I am creating a report that will show Gross Profit, Average Inventory, Inventory Turns and GMROI.

Everything is clear to me except the best way to calculate Average Inventory.

I understand that Average Inventory is (beginning inventory + ending inventory) / 2.

Now when is the best time to calculate this?

I was trying to come up with a formula that I could use, that would update a field in a sku record, every time a sku is accessed(ie sold or received).

This would elimate the user having to run some routine at the end of every period. This would also simplify reports that need to act upon an average inventory figure.

Any thoughts, ideas or suggestions?

Thank you,
Paul Acton