Dashboards, Applications & Business Intelligence

Let us explore the chapter on how the qtd, ytd and ptd are calculated. You will find
references to these balances in FSGs, DBI for Financials and other financial reports.

I will try to explain with whatever knowledge that I have gathered so I may not be
100% accurate in the terminology or details. If you find any needed corrections please
feel free to comment or send me the details and we can keep this article current.

Often dealing with General ledger or Oracle Apps, I always had to scratch my head
in understanding what is stored in the following columns and what purpose do they serve

Period_net_cr, period_net_dr, quarter_to_date_dr, quarter_to_date_cr,
begin_balance_dr, beging_balance_cr

Let us consider the below table as an example. Following table is a list of entries
from gl_balances table for one particular code_combination_id named ‘1001′ and each entry corresponds
to each month. The first column is the code_combination_id which is the reference to the
actual Account Code combinations, the second column is the period_name, the remaining columns
are described below.

Period Activity
Period_net_cr and Period_net_dr : Store the monthly activity that is happening in the particular code_combination_id
for that particular period.

For e.g the first row: Period = Aug-05 , the period_net_dr = 38780088.1 and period_net_cr= 45809461.04

The above balance indicates the overall debit and credit for this particular account. This activity could be
coming from any subledgers like AR, AP, INV or from manual Journal entries created in GL.

Its like the Credit Card activity summary for a particular billing period.

Begin Balance
Now consider the Begin_balance_cr and Begin_balance_dr columns. There are 5 different account types in GL
‘Expense’, ‘Revenue’, ‘Liability’, ‘Assets’, ‘Equity’

‘Expense’ and ‘Revenue’ represent your profit and loss accounts whereas the remaining are the ‘Balance sheet’ accounts

So at the beginning of each year, ‘Expense’ and ‘Revenue’ accounts are reset to zero whereas the remaining account
types carry forward their balance through their life. Its like if a person has credit card debt (liability), it
will be carried forward through out until it is paid for.

Begin_balance_cr and _dr columns is the summary of beginning balance at each month. At every month beginning,
the beginning balance from previous period is added to the period activity balance to get the new beginning balance
for the current period.

For e.g. for period SEP-05, the Begin_balance_dr = 938288927.5 which is the total of Begin_balance_dr + period_net_dr for period AUG-05 => 899508839.4 (AUG-05) + 38780088.1 (AUG-05) = 938288927.5 (SEP-05)

Quarterly Balance
If you notice the above table, the Quarter_to_date_dr and quarter_to_Date_cr is reset to zero every 4th month.

In the above case, AUG-05 is the beginning of the Quarter and hence it is reset to zero.

Quarterly columns store the running total for the period activity of the previous months in that Quarter

SEP-05 Quarter_to_date_dr is the total of Period_net_dr in AUG-05 => 38780088.1

OCT-05 Quarter_to_date_dr is the total of period_net_dr in AUG-05 and SEP-05 => 38780088.1 + 23624086.3 (= 62404174.4)

Isn’t it interesting?

Now how is PTD, QTD and YTD calculated?

period-to-date is the period activity for the concerned period ( i.e Period_net_dr - period_net_cr)

QTD is (Quarter_to_date_dr - Quarter_to_date_cr) + (period_net_dr- period_net_cr)

and

YTD = (Begin-balance_dr- begin_balance_cr) + (period_net_dr- period_net_cr)

Hope you enjoyed this article! Share the knowledge.