Calculating Average Balances

This chapter provides an overview of average balance calculation and discusses how to:

• Process average daily balances.

• Produce average daily balance reports.

Understanding Average Balance Calculation

This section lists prerequisites, common elements and discusses:

• Average daily balance setup.

• Summary of capabilities.

• Average balance calculations.

• How ADB determines calculation method.

• Incremental calculations.

• Management and regulatory ADB reporting.

Prerequisites

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.

Common Elements Used in This Chapter

 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).

Average Daily Balance Setup

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.

See Pages Used to Set Up Average Balances.

Summary of Capabilities

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.

• 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.

• 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.

Prerequisites

Average Balance Calculations

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

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.

 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: February 1 is the 32nd day of the period

Incremental Calculation for MTD

ADB for the month-to-date (MTD) should not include the ending balance for the prior period as the beginning balance for the current period. 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: February 1 is the first day of the second period

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 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. 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.

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.

Adjustment journals are those journals that have been marked as adjustments in the journal header record. General Ledger posts these journals to special adjustment periods, which are defined on the Calendar Periods page. They are not posted to the period of the journal date. This prevents period-to-period reporting from being distorted by the adjustments.

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.

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

Defining Accounting Calendars

When you enter journals, you identify an ADB date. The ADB date is the date that the system uses to determine in which accounting period the transaction is posted to the ADB ledger. Usually the ADB date and the journal date are the same, but in some cases they might differ.

General Ledger can maintain separate daily balances for the period, one that reflects the journal date and one that reflects the ADB date. You specify this requirement by selecting Maintain Regulatory Balances on the Ledgers For A Unit − Definition page.

When you select maintaining regulatory 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.

The following tables illustrate how ADB maintains regulatory balances. This first table identifies a journal with an ADB date different from the journal date on lines 3 and 4:

 Jrnl Line BU Ledger Acct Jrnl Date ADB Date Amt 1 US001 Corporate 100000 January 1, 2009 January 1, 2009 100.00 2 US001 Corporate 100000 January 2, 2009 January 2, 2009 200.00 3 US001 Corporate 100000 January 3, 2009 January 1, 2009 50.00 4 US001 Corporate 100000 January 4, 2009 January 2, 2009 50.00

This second table illustrates the daily balance stored only for the ADB date. This table shows the transactions as they are posted to the ADB ledger based on ADB date only:

 BU Ledger Account Acct Period Posted Tran Amount US001 Corporate 100000 1 150.00 US001 Corporate 100000 2 250.00

This last table illustrates the daily balances stored based on both dates:

 BU Ledger Acct Acct Per Per Seq* Posted Trans Amt Comments US001 Corporate 100000 1 0 150.00 Balance posted from ADB date US001 Corporate 100000 2 0 250.00 Balance posted from ADB date US001 Corporate 100000 1 1 -50.00 Reversal for balances posted from ADB date US001 Corporate 100000 2 1 -50.00 Reversal for balances posted from ADB date US001 Corporate 100000 3 1 50.00 Balance posted from journal date US001 Corporate 100000 4 1 50.00 Balance posted from journal date

* The Period Seq (period sequence) field distinguishes the balance types: 0–balances posted from ADB date, 1−balances posted from journal date, and reversals for balances posted from the ADB date.

Defining Ledgers for a Business Unit

Preparing Your System for Average Daily Balancing

This section discusses how to:

• Identify the ledgers.

• Identify the ChartFields.

Identifying the Ledgers

Access the ADB Definition - Definition page (General Ledger, Average Daily Balance, ADB Definition).

Prerequisites

Identifying the ChartFields

Access the ADB Definition - ChartFields page (General Ledger, Average Daily Balance, ADB Definition, ChartFields).

Processing Average Daily Balances

This section provides an overview of processing of ADB and discusses how to request the ADB process.

Once you establish the basis for average balance calculations, then initiate the background process that calculates the balances and updates the target ledger. If you want to run average balances for a ledger that has already been archived, you must restore the ledger and then run the ADB process.

ADB processing includes the following activities:

• Journal Post (GLPPPOST).

The Journal Post process posts the daily balances into a holding table.

The ADB post process (GL_ADB_POST) posts the daily balances from the holding table into the ADB ledger (the source ledger containing the daily balances).

Note. Although the ADB post process is run from the journal post request, the ADB post process criteria is different from the journal post process for the posting of interunit journals. When posting interunit journals, the journal post process posts all of the non-anchor business units when posting the requested anchor business unit. The ADB post process only posts the anchor business unit. The non-anchor business units must be posted in separate requests.

Note. Instead of running the Journal Post process and the Post Daily Balances process separately, you can run the PS/GL Journal and ADB Post multiprocess job (GLADBPST) to post the journals and update the ADB ledger with the daily balances.

Note. The system prevents double posting in the event that two ADB post processes (GL_ADB_POST) are running concurrently for the same business unit. The ADB_PROCESS_STATUS field on the ADB ledger holding table (LEDGER-ADB_HLD) locks the rows that are being posted to the ADB ledger.

If an abnormal termination or failure occurs on a step in the ADB post process, perform the following steps before rerunning the process:

• Run the delivered DMS script ADBSTATUS.DMS in Data Mover to unlock the rows in the ADB ledger holding table. You must modify the script to include the process instance of the process that failed.

• Delete the failed request from the process monitor by selecting the Delete Request radio button in the Update Process section of the Process Detail page. This step is necessary to clean up data in the application engine state records.

The ADB Calculation process calculates average balances using transactions from the ADB ledger and the adjustment holding table and places the results (average balances) in the ADB target ledger.

The ADB Calculation process creates an ADB log entry. The ADB log is used by the ADB process to determine when the average balances (for a given definition, period type, and requested period) were calculated.

Understanding Average Balance Calculation

Archiving for Ledgers and Journals

Page Used to Process Average Daily Balances

 Page Name Definition Name Navigation Usage ADB Process Request ADB_REQ General Ledger, Average Daily Balance, ADB Process, ADB Process Request Identify the business unit, ADB definition, and period type that you want to process. Also specify whether you want to recalculate existing average daily balances.

Enterprise PeopleTools PeopleBook: PeopleSoft Process Scheduler, "Understanding PeopleSoft Process Scheduler"

Producing Average Daily Balance Reports

This section provides an overview of ADB report processing and discusses how to:

• Run the ADB definition report.

• Run the ADB calculation report.

To run a report, select it from a menu and enter the necessary parameters. Once you enter the report parameters, you use PeopleSoft Process Scheduler to actually run the report. PeopleSoft Process Scheduler manages the processes, tracks the status, and generates the report behind the scenes while you continue to work on something else.

You can create your own reports or reformat report output. PeopleSoft offers a variety of reporting tools.

Note. Summary ledgers support summarization of ADB target ledgers. However, summarizing daily ledgers is not supported.

Pages Used to Produce ADB Reports

 Page Name Definition Name Navigation Usage ADB Definition Report (average daily balance definition report) RUN_GLS5500 General Ledger, Average Daily Balance, ADB Definition Report, ADB Definition Report Specify the run parameters for the ADB Definition Report. ADB Calculation Report (average daily balance calculation report) RUN_GLC5501 General Ledger, Average Daily Balance, ADB Calculation Report, ADB Calculation Report Specify the run parameters for the ADB Calculation Report.