During my freshman year in college, many decades ago, students were required to take a seminar course where one must write an essay every week (still are). This requirement was added after professors found many initial writing efforts unreadable. (I later participated in a conference on teaching writing to college students where I concluded that good writing depends upon good thinking, and college is probably too late to teach that.) These seminars were taught by graduate students, so there were twenty or fewer students per class. My teacher was not from the US and had learned English as a second language in a British-influenced system. She spoke about “full stops” in one of the first seminars. It took me several minutes to realize that a full stop is a period. I’m going to write about periods today: periods in the XLstatements PTDBAL function.
The period argument of PTDBAL can be a number, a list, or a range (we saw no need to enable wildcard masks for this). As with the other comprehensive function arguments, we encourage using a single cell named range for this, and, since this argument is named “Pd” in the function definition, that’s what I typically name it.
One request we have had is to enable drill-down for the YTDBAL function. We deliberately did not do this for two reasons: 1) it may well return an unwieldy number of rows at the first drill-down level (expand masks), and 2) the beginning balance included in the function’s result may be unintuitive (period zero?) or even unwanted at that level.
So, in financial statements where you are showing year to date balances but want drill-down, we usually recommend enabling drill-down for just the current month:
=YTDBAL(CpnyID,LedgerID,$A9,SubAcct,FiscYr,Pd-1) +PTDBAL(CpnyID,LedgerID,$A9,SubAcct,FiscYr,Pd)
That’s all one formula, of course, but I broke it into two lines for readability here. This shows the year to date balances but the drill-down is only into the selected period.
If you do insist on drill-down into all periods, however, there’s an easy answer: use PTDBAL instead of YTBAL. Here’s how to do this by using a concatenated string as the Pd argument:
=PTDBAL(CpnyID,LedgerID,$A9,SubAcct,FiscYr,"1:"&Pd)
The formula used for the Pd argument, “1:”&Pd, evaluates to the range from period 1 though the current period. E.g., if the value in the named range (cell) named Pd is 9, the formula evaluates to the range 1:9.
Note that you cannot get beginning balances (which is “period 0” in YTDBAL) included. We decided that beginning balances should not be exposed as a period balance because there are no drilldown details and things get complicated if the optional arguments are used. If you need to get actual YTD balances, including beginning balances, then add a YTDBAL function for period 0:
=YTDBAL(CpnyID,LedgerID,$A9,SubAcct,FiscYr,0) +PTDBAL(CpnyID,LedgerID,$A9,SubAcct,FiscYr,"1:"&Pd)
Another common request is to use quarterly reporting periods. Here we just use the Excel CHOOSE function to return a list of periods for each quarter:
=CHOOSE($A$5,"1;2;3","4;5;6","7;8;9","10;11;12")
The first argument references the cell where the user enters the quarter number, and doesn’t really warrant a name. Name the cell containing the CHOOSE function, though, as that’s what you will use as the Pd argument in your PTDBAL functions, e.g., naming this cell “Quarter”would lead to a function like this:
=PTDBAL(CpnyID,LedgerID,$A9,SubAcct,FiscYr,Quarter)
You could also just name it Pd if it’s clear that this is the reporting period rather than the accounting period and you’re not using that name for the accounting period elsewhere.
To have a year to date balance with drill down to the current quarter, as in the first formula above where we used the current month:
=YTDBAL(CpnyID,LedgerID,$A9,SubAcct,FiscYr,Quarter*3-3) +PTDBAL(CpnyID,LedgerID,$A9,SubAcct,FiscYr,Quarter)
If you want to show a rolling quarter, that’s more difficult. Here is one way:
=PTDBAL(CpnyID,LedgerID,$A7,SubAcct,FiscYr,"1:"&Pd) +IF(Pd<3,PTDBAL(CpnyID,LedgerID,$A7,SubAcct,FiscYr-1,13-Pd&":12"),0)
If the quarter reaches back to the prior fiscal year (Pd<3), we must add another PTDBAL function because PTDBAL can only use one discrete fiscal year. We’re using the Excel IF function for this. Unlike the first formula above for using PTDBAL as YTDBAL, we also must leave out the beginning balance because that is not relevant with a rolling period. You can edit this if your company uses a nominal 13th period for adjustments:
=PTDBAL(CpnyID,LedgerID,$A7,SubAcct,FiscYr,"1:"&Pd) +IF(Pd<3,PTDBAL(CpnyID,LedgerID,$A7,SubAcct,FiscYr-1,13-Pd&":13"),0)
Please note that any use of ranges in the Pd argument requires at least Version 1.0 of XLstatements. If you are still using a pre-release (beta) version, please contact us for your free upgrade.
So full stop on this subject for now. If you have any special Pd needs not covered here, please leave a comment.
xlstatements.wordpress.com 