How are you trying to calculate the Inventory Turnover? By dividing the cost of goods sold (COGS) for the reporting period by average value of inventory on hand during the period?
One problem is that i cannot figure it out how can i calculate dynamically the average stock for a period selected by the user. If the user wants to have it for 3, 6 or 12 month this should be calculated every time he changes the period.
Shouldn't i calculate initial stock + IN - OUT for each month in the period ?
Indeed, your solution would be easier then : COGS for the period / inventory value / number of months in the period, right ?
Sorry about the late response. After Qonnections and a small vacation, I'll getting back to reviewing the forum.
Check out http://en.wikipedia.org/wiki/Inventory_turnover for the easiest way to calculate inventory turnover. Instead of creating a complicated formula to calculate the average inventory, just sum the inventory value at the end of the period and the inventory value at the beginning of the period and divide by 2.
To calculate similar period related ratios, I've either let the user select a range of dates or select just the max date and then select a variable with the values 30, 60, 90, 180, 365. In both cases, I use set analysis to define the sum that returns the beginning and ending inventory value. The sum of COGS is pretty straight forward.
If you want to go a step further to calculate Days of Inventory then you would divide the number of days in the period by the Inventory Turnover result.
