Understanding Average Balance Calculation
These topics provide an overview of the average balance calculation and discuss how to:
Prepare your system for average daily balancing (ADB).
Process average daily balances.
Produce average daily balance reports.
This section lists prerequisites, common elements, and includes the following detail:
Average daily balance setup.
Summary of capabilities.
Ledgers used by ADB.
Average balance calculations.
How ADB determines calculation method.
Incremental calculations.
Ad hoc calculations.
Adjustments in ADB.
Journal adjustments (998, 901, 902...) in ADB.
Management and regulatory ADB reporting.
Note: Adding ADB for a BU in the middle of a Fiscal Year is not retroactive.
Perform activities to set up ADB for the detail ledger that will be used to maintain the standard balances and daily balances when calculating the averages.
Before you can use the ADB feature, you must set up your business units and ledgers for ADB processing:
|
Setup |
What |
|---|---|
|
Business Units using the General Ledger Definition - Definition Page |
Set up the business units for which you want to calculate average daily balances. Select the ADB Incremental Calculation Method link on the General Ledger Business Unit Definition page, and select the definitions that you run regularly (using the incremental calculation method) for this business unit. |
|
Detail Ledgers using the Detail Ledger Page |
Set up these ADB detail ledgers:
|
|
Ledger groups using the Ledger Groups - Definition Page |
Set up these ADB ledger groups:
Associate the ledger group with one of the ADB templates. |
|
ADB templates using the Templates - Record Definitions Page |
Select each of these PeopleSoft ADB target ledger templates:
The ADB process determines where to store the calculated averages using the table defined in Record (Table) Name field on this template. Create a unique template for each unique table name depending on where you want to store the averages. For example, if you want to store all the averages to one table, you need only one template. To set up the template, select ADB Reporting Ledger in the Default Ledger Type field and click the button to select the default table names. You can accept the default table or choose another ADB target ledger table. |
|
|
|
Add the ADB ledger group to the business unit. This is where the ADB calculation process looks to determine what ledger group and ledger to use when calculating the averages.
The calendar that you select determines how the average balances are stored. If you select a monthly calendar, for example, the current period reflects today's averages (or the day ADB was processed in the current month) and the prior period reflects the month-end average balances. Note: Do not select Report Average Balances or any other ADB-related fields for these ADB target detail ledgers that you attach to a business unit in the Ledgers For a Unit component. |
|
|
Post Adjustment Periods to ADB option using the Ledgers For A Unit - Journal Post Options Page |
Disable (or leave enabled) the Post Adjustment Periods to ADB option. Select whether to post adjustment journal entries to the ADB ledgers using an adjustment period (as defined for the ledger group) or using a regular accounting period that is derived from the ADB calendar. |
Field or Control |
Description |
|---|---|
Target ADB Ledger (target average daily balance ledger) |
Stores the average balances. The detail ledger must be a different ledger. |
Incremental Method |
Indicates the method of calculating ADB that uses prior period calculated averages and daily balances to calculate the requested periods' averages. This is a more efficient processing method than the ad hoc method. |
Ad Hoc Method |
Indicates the method of calculating ADB that uses the daily ledger balances to calculate the requested periods' averages. This method uses more system resources than the incremental method. |
ADB (average daily balance) |
Average Daily Balance. |
Period Type |
Defines the time period for the ADB calculation (month to date, year to date). |
After you complete the ADB prerequisites, you must perform the following activities before you can process average balances for a business unit:
Define the interrelationship of ledgers and ChartFields used in ADB on the ADB Definition page:
Define the ledgers and amount fields to be used in the ADB calculations and the interrelationship between the ADB ledger and target ADB ledger's amount fields (Definition page).
Define the ChartField and value that is used to store the ADB rounding adjustment (Rounding Adjustment page).
Define the association between the ADB and target ADB ledgers' ChartFields (ChartFields page).
Select the ADB Incremental Calculation Method link on the General Ledger Business Unit Definition page and select the definitions that you run regularly (using the incremental calculation method) for this business unit.
Access the ADB Process Request page to run the ADB Calculation process (GL_ADB_CALCX).
The Calculating Average Balances feature enables you to report your organization's financial position using average, rather than period-end, balances. You can:
Select which ChartField values are included in average balances.
Select the time periods for ADB calculations from month-to-date, quarter-to-date, and year-to-date options—or define your own time periods.
Report prior day and current average balances.
Summarize ADB target ledgers for summary ledgers.
There are two ledgers involved in ADB processing: the ADB ledger and the target ADB ledger.
The source ADB ledger (also known as the ADB ledger) stores the daily ledger activity that is used by the ADB process to calculate the average daily balances.
The target ADB ledger stores the calculated averages from the ADB process.
You can have as many target ADB ledgers as you need.
This design has several advantages:
Flexibility to maintain the average balances in different ledger tables:
Partition your averages to different target ledger tables, such as period type (MTD—month-to-date, YTD—year-to-date, and QTD—quarter-to-date), which can improve processing performance.
You can maintain all the averages in a single table.
Flexibility to define a calendar ID to maintain the calculated average daily balance history:
To maintain month-end balances, use only the monthly calendar ID, which represents the current day's averages.
To maintain daily balances, use the daily calendar ID.
Choose either the incremental or the ad hoc calculation method.
ADB uses either the incremental method or the ad hoc method to calculate average balances. This table summarizes the differences between the two methods:
|
Incremental |
Ad hoc |
|---|---|
|
Uses prior period stored aggregate balances and daily balances to calculate the requested periods' averages. |
Uses the daily ledger balances to calculate the requested periods' averages. |
|
Results in more efficient processing. |
Uses more system resources. |
By default, the ADB Calculation process uses the ad hoc method to calculate average balances.
In order to use the incremental calculation, you must define the ADB definitions and period type on the General Ledger Definition − Incremental Calculation Method page. Here's how period types relate to the ADB definition on that page:
|
ADB Definition |
Period Type |
|---|---|
|
Actuals MTD Averages |
MTD (month to date) |
|
Actuals QTD Averages |
QTD (quarter to date) |
|
Actuals YTD Averages |
YTD (year to date) |
In the sections that follow, there are examples of how these period types are used in calculations.
Incremental calculations are the most efficient way to calculate average balances. Each time that the system processes ADB calculations, it extracts only the daily balances that have been posted since the last time that it ran the ADB Calculation process. The process uses the prior period averages as the starting point to calculate the current period's average.
The ADB incremental calculation method determines average balance by dividing the aggregate amount by the number of days within the requested period type:
Average balance equals the aggregate amount divided by the number of accumulated days in the requested period type.
Term |
Definition |
|---|---|
Aggregate Amount |
The aggregate amount equals today's ending balance plus the previous aggregate amount (the aggregate amount for the previous day of this period). If it is the first day of a period, the aggregate amount is equal to the ending balance and the average balance. |
Ending Balance Amount |
The ending balance amount equals today's daily balance plus the previous ending balance (the ending balance of the previous day of this period). If it is the first day of a period, the ending balance is the same as the aggregate amount and the average balance. |
The following two tables illustrate incremental calculation by showing how February 1 is calculated using two different time periods (period types): year to date and month to date.
This table shows how the system calculates February 1 for a YTD (year to date) period.
Incremental Calculation for YTD
The following table shows how the system performs incremental year to date calculations:
Year-to-Date Example: Incremental ADB Calculation
Incremental Calculation for MTD
Month-to-date balances are maintained until the last day of the month. On the first day of the new month, the ending balance of the prior month is carried forward to the beginning of the new month and the aggregate balance starts over. MTD is for the month only and the calculation does not include any amounts from any other period.
The following table shows how the system calculates February 1 for a MTD (month to date) period:
Month-to-Date Example: Incremental ADB Calculation
Ad hoc calculations require more system resources. Each time that the system processes ADB calculations, it extracts data for all the days within the period type from the ADB ledger. Therefore, you usually want the system to use the incremental calculation method for regularly scheduled averages (MTD, YTD, and QTD). Otherwise, you can use the ad hoc method.
The ad hoc calculation for average balance is the same as the incremental calculation:
Average Balance = Aggregate / number of days with in the requested period type
The difference is that the ad hoc method does not use prior period balances. Instead, it requires all the daily balances needed to calculate the requested period type. (The incremental process requires only the daily balances from the previous period.)
This table shows how the system calculates average daily balances for a date-to-date period type (DTD), starting January 5 through the request date of February 1. The ADB ledger uses a monthly calendar.
Calculation Using a DTD Period Type
This table shows incremental day-to-day calculations:
Incremental Day-to-Day ADB Calculations
In this case, even though February 1 is in the next period, the system still uses the previous day's ending balance and aggregate amount in the calculation.
In the sections that follow, there are examples of how these period types are used in calculations.
ADB adjustments are any transactions for a specific period that have been posted to the ADB ledger after ADB calculations have been run, which includes that period in the average balances.
For example, suppose average balances are calculated at 8:30 a.m. on Monday as of period 1. Additional transactions are posted to the ADB ledger at 9:00 a.m. that same day. These new transactions are considered ADB adjustments.
If the ADB process is using the incremental calculation method, the process automatically adjusts prior period balances before it calculates the requested period averages because the incremental calculation method uses prior period balances to calculate the current period averages (thus the adjustments must first be applied to those prior balances).
Note: The ADB process does not require you to go back and rerun prior month balances for the same fiscal year in order to adjust prior period balances when using the incremental calculation method. For example, if you have adjustments in May and June and the averages are already calculated up to July 31, you do not have to rerun the ADB Calculation process for the month end of May and June. You can rerun the ADB calculation process as of July 31 or as of August 1. The ADB process automatically adjust the prior months balances, for May and June, as long as the adjustments are within the same fiscal year. If you were to rerun the balances as of May 31, the process would not properly adjust the balances in June and July.
If the ADB process is not using the incremental calculation method, then the process does not have ending balances.
When using the ad hoc method, you have to run the process for every open month for which you have transactions.
The ADB process adjusts all average balances starting from the minimum accounting period of the ADB adjustments, as long as that accounting period is within the same fiscal year as that of the requested period.
Keep in mind the distinction between ADB adjustments and adjusting journals (which are posted to special adjustment periods). They are different and are processed by ADB differently.
Adjustment journals are those journals that have been marked as adjustments in the journal header record.
You have the option to post adjustment journal entries to the ADB ledgers using:
Adjustment periods as defined for the ledger group (this prevents period-to-period reporting from being distorted by adjustments); or,
Regular accounting periods that are derived from the ADB calendar.
You select this option on the Ledgers For A Unit - Journal Post Options page. Deselect (or leave selected) the Post Adjustment Periods to ADB check box.
See Ledgers For A Unit - Journal Post Options Page.
Posting Adjustment Journals to ADB Ledgers Using Adjustment Periods (Default)
ADB calculations support all adjustment periods.
For the most part, you will probably not want to include adjustment periods in ADB calculations, with the possible exception of year-to-date daily averages.
If you do choose to include journal adjustments, you have two points in the period that you can include the adjustments. You can include them at the beginning of the period, in which case the adjustment period (period 998, 901, 902) is considered the first day of the period, or you can include them at the end of the period, in which case the adjustment period is considered the last day of the period. For ADB definitions using the incremental calculation method, the process calculates the journal adjustments as of the last day of the period, regardless of the option selected. However, you can choose to run the ADB process using the ad hoc method on the ADB request if you want to include the adjustments as of the first day of the period. It is recommended that you use the ad hoc calculation method when including adjustment journals in the average balances. The impact of the journal adjustment period on average balances is illustrated in the following table.
This example assumes that Account 100000 has a zero beginning balance. During the course of the month, only two transactions were posted: one on December 1 and one on December 31 :
|
Account |
Journal Date |
Period |
Day Within Period |
Transaction Amount |
|---|---|---|---|---|
|
100000 |
December 1, 2009 |
12 |
1 |
100 |
|
100000 |
December 31, 2009 |
12 |
31 |
150 |
|
100000 |
December 31, 2009 |
998 |
300 < − adjusting entry |
The following table shows the results of not including period 998, including it at the beginning of the period or including it at the end of the period:
|
Period 998 Option |
ADB Calculation |
Result |
|---|---|---|
|
No Adjustment Period |
ADB = ( [100 * 31] + [150 * 1] ) / 31 [Aggregate of the December 1 amount] plus [aggregate of December 31 amount] divided by the number of days. |
104.84 |
|
As First Day of Report Period |
ADB = ( [400 * 31] + [150 * 1] ) / 31 |
404.84 |
|
As Last Day of Report Period |
ADB = ( [100 * 31] + [450 * 1]) / 31 |
114.52 |
Posting Adjustment Journals to ADB Ledgers Using Regular Accounting Periods
If you have deselected the Post Adjustment Periods to ADB check box on the Ledgers For A Unit – Journal Post Options page, the adjusting journals are posted to regular accounting periods, and therefore, are included in the ADB calculations.
In order to comply with International Accounting Standards (IAS), PeopleSoft General Ledger can also maintain separate daily balances for the period:, one that reflects the journal date (trade date, for example), one that reflects the settlement date, and one that reflects the ADB date. To enable PeopleSoft General Ledger to maintain multiple balances, see Enabling Date Code in PeopleSoft General Ledger.
When you select to maintain multiple balances, for every journal that has an ADB date that differs from the journal date, the system creates two additional entries: an entry for the period based on the journal date and a reversal for the period based on the ADB date.