Hi,
I need to build a report to get a KF of inventory turnover, the formular used is:
<i>turnover =total consumption / average stock</i>
while we use the formular below to get the
<b>average stcok =(begin of stock + n stock at month's end)/(n+1)</b>
what I need is to based on user's date input(let's say 09/2005 to 11/2005 for example),
1,get the "total consumption" for Oct and Nov,
2,and also get
"average stock" = (Stock at Sep + Stock at Oct + Stock at Nov)/(2+1)
3, get the turnover = "total consumption" /"average stock"
let's say KF "Total cnsumption" for 10/2005 is 10, for 11/2005 is 20. so <b>"Total cnsumption" for Oct and NOv</b> will be 10+20=30;
and if KF "Stock at Sep" = 12, "Stock at Oct" is 24,
"Stock at Nov" = 30
then <b>"average stck"= (122430)/(2+1) = 22</b>so the "turnover" will be
<b>"Total cnsumption"/"average stck"= 30/22= 1.36</b>
I am not sure how the report lay out will be, but as long as i can get the turnover based on teh user input's month during, that would be good enough. so probably the report will look like:
"Month" "Totalcnsump" "Stck" "averageStck" "TurnOver"
09/2005 12
10/2005 10 24
11/2005 20 30
<b>result: 30 66 22 1.36</b>
Does any one have any idea how to make it done???
Your input is very much apprecieated,
ping
dose any one knwo how to write a macro at teh workbbok level, so that i can get the "turnover" by use the result value for "total consumption", divided by the the result value of "average stck".
in my example,
"Month" "Totalcnsump" "Stck" "averageStck" "TurnOver"
09/2005 12
10/2005 10 24
11/2005 20 30
result: 30 66 22 1.36
it should be
"turnover" = 30/22 =1.36
Add a comment