Rolling Rolling Rolling

How can one create an XLstatements reports showing rolling periods? Here’s an example of a twelve-month rolling report (click to expand):

I’m using the good old “Solomon” demo data here, which has only a few months of data, so it looks a bit odd. The first thing I did was put a reference to my cell named Pd (A5) above my last column in cell N2. I also referenced my FiscYr cell below that in cell N3. Next I move to the column to the left and enter a formula in M2, referring to the value in N2:

This formula is:

=IF(N2=1,12,N2-1)

And I use another IF formula in N3, for the fiscal year:

=IF(M2<$N$2,FiscYr,FiscYr-1)

I then copy those two formulas to the left ten columns (by selecting the two cells M2 and N2 and then dragging the fill handle to the left).

To make the column headers, I used this formula in cell C6:

=LEFT(PDMONTH(CpnyID,C$2),3)&" "&C$3

PDMONTH is a XLstatements function that returns a month name from the period number, and I am using the Excel LEFT function to get just the first three letters in each month name. I then copy that formula to the eleven cells to its left.

For the PTDBAL functions of the report, I used the values in rows 2 and 3, making sure to make the row absolute and the column relative, e.g., cell C7 has this formula:

=PTDBAL(CpnyID,LedgerID,$A7,SubAcct,C$3,C$2)

I then copied that formula to all the rows and columns.

If you don’t want your report to include the rows with the period numbers and years (rows 2 and 3 in this example), you can hide the rows you inserted for them: