Reverse running totals 1

[jdathey]

I've read a couple threads on this but I think my problem may be slightly different.

I'm trying to calculate the ending inventory value for the last 12 months based on the current inventory value and net inventory activity during the last 12 months.

For example, I have a current inventory value of $500,000. And net activity of

Jan12 $5,000
Dec11 $(3,000)
Nov11 $1,500
Oct11 $10,000
Sep11 $(2,750)
Aug11 $(5,000)
Jul11 $9,500
Jun11 $3,000
May11 $(8,500)
Apr11 $(6,000)
Mar11 $9,000
Feb11 $(5,000)

So I need the running total (inventory value) to be

Jan12 $500,000 - $5,000 = $495,000
Dec11 $495,000 + $3,000 = $498,000
Nov11 $498,000 - $1,500 = $496,500
Oct11 $496,500 - $10,000 = $486,500
Sep11 $486,500 + $2,750 = $489,250
Aug11 $489,250 + $5,000 = $494,250
Jul11 $494,250 - $9,500 = $484,750
Jun11 $484,750 - $3,000 = $481,750
May11 $481,750 + $8,500 = $490,250
Apr11 $490,250 + $6,000 = $496,250
Mar11 $496,250 - $9,000 = $487,250
Feb11 $487,250 + $5,000 = $492,250

And from this running inventory value I also want to calculate the inventory turn rate (Beg Inv Val + Purchases - End Inv Val) / (End Inv Val) for each month.

I've grouped my data by date with the section printed for each month giving me the net dollar activity for each month and I brought in a subreport for the current inventory value. Of course the value displays the same inventory value for each date group (month).

 

[lbass]

Place the subreport in the report header of the main report. Within the report footer of the subreport, place a formula:

whileprintingrecords;
shared currencyvar BegInvBal := <your current inventory calculation here>;

Then in the main report, create these formulas and add them to the month group header or footer:

//{@EndInvVal}:
whileprintingrecords;
shared currencyvar BegInvVal;
currencyvar Purch := sum({table.purch},{table.date},"monthly");
currencyvar Sales := sum(table.sales},{table.date},"monthly");
currencyvar accumPurch := accumPurch + Purch;
currencyvar accumSales := accumSales + Sales;
currencyvar EndInvVal := BegInvVal+accumPurch-accumSales;

//{@Turn Rate}:
evaluateafter ({@EndInvVal};
currencyvar Sales;
currencyvar EndInvVal;
Sales/EndInvVal;

-LB

 

[jdathey]

OK, looking pretty good. But let me throw this in. The monthly data is actually the second group. Group 1 is a location. We have 4 inventory locations. The "accum" formula is being passed through each location group and I need to reset the accum total for each group.

 

[lbass]

Can we assume the beginning balance is different for each location and that the sub that is passing this is in the Group #1 header? If so, then you can add a reset formula to the Group #1 footer:

whileprintingrecords;
shared currencyvar BegInvVal := 0;
currencyvar Purch := 0;
currencyvar Sales := 0;
currencyvar accumPurch := 0;
currencyvar accumSales := 0;
currencyvar EndInvVal := 0;

-LB

 

[jdathey]

Thank you very much. I know these things are very basic to you but I learn a lot from you and others in this forum.