8Average Balance Processing

This chapter contains the following:

You can use average balance processing to track average and end-of-day balances, report average balance sheets and income statements, and create reports using both standard and average balances. Average balance processing is particularly important for financial institutions since many regulatory agencies require average balance sheets in addition to standard balance sheets. Many organizations use average balances for internal management reporting and profitability analysis for both balance sheet and income statement accounts.

The difference between an average and standard account balance is that balances are expressed as average amounts rather than actual period-end amounts. An average balance is calculated as the sum of the actual daily ending balance for an account, divided by the number of calendar days in the reporting period.

You can maintain and report on average balances daily, quarterly, and yearly. The application tracks average balances using effective dates, which you enter for each transaction. You can display information about average balances for specific effective dates using:

  • Financial reports created using Financial Reporting Studio

  • Smart View queries

  • The Inquire and Analyze Average Balances page

  • Standard average balance reports

The application provides the features to satisfy the following basic average balance needs:

  • Use average balance processing only in those ledgers that require it.

  • Maintain average balances for all balance sheet and income statement accounts automatically.

  • Create and maintain a transaction calendar to ensure that all postings have effective dates that are valid business days.

  • Ensure that entries balance by effective date, as well as by period.

  • Calculate average balances based on a transaction's effective date, not the posting or accounting date.

  • Calculate period, quarter, and year averages-to-date based on the balances for each day within the period, quarter, or year.

  • Calculate the impact of net income on the average balance for retained earnings when income statement accounts aren't enabled for average balance processing.

  • Report or inquire on average and ending balances for any effective date.

Here are some examples that illustrate the concepts of average balance processing. The first example presents the scenario. You have three balance sheet accounts: Account A, Account B, and Account C. Each account has a period opening balance of 0.00 (USD). The rest of the examples move on to show period, quarter, and average-to-date balances.

Day One

Here's the only transaction that takes place on the first day of the accounting period.

Account Debit Credit

Account A

1,000

Account B

1,000

The transaction results in these balances.

Account Activity End-of-Day Balance Aggregate Balance Average Balance

Account A

1,000

1,000

1,000

1,000

Account B

(1,000)

(1,000)

(1,000)

(1,000)

The aggregate balance on day one for each account is the same as the end-of-day balance. The average balance is equal to the aggregate balance divided by 1, the number of days in the period to date.

Day Two

Here's the transaction that takes place on the second day.

Account Debit Credit

Account A

100

Account C

100

The transaction results in the following balances.

Account Activity End-of-Day Balance Aggregate Balance Average Balance

Account A

100

1,100

2,100

1,050

Account B

0

(1,000)

(2,000)

(1,000)

Account C

(100)

(100)

(100)

(50)

The aggregate balance for each account is equal to the end-of-day balance for day one plus the end-of-day balance for day two. Another way to state this is that the aggregate balance is equal to the previous aggregate balance plus the current day's end-of-day balance. The average balance for each account is equal to the aggregate balance divided by 2, the number of days in the period to date.

Day Three

Here's the transaction that takes place on the third day.

Account Debit Credit

Account B

200

Account C

200

Here are the resulting balances for all three accounts.

Account Activity End-of-Day Balance Aggregate Balance Average Balance

Account A

0

1,100

3,200

1,066.67

Account B

200

(800)

(2,800)

(933.33)

Account C

(200)

(300)

(400)

(133.33)

The aggregate balance for each account is equal to the sum of the end-of-day balances for days one through three. And the average balance is equal to the aggregate balance divided by 3, the number of days in the accounting period to date.

Amount Types for Average Balance Processing

If you have enabled average balance processing for your ledger, every time you post a transaction, the application updates the standard period-end balances, and all the average balances including the aggregate balances, End-of-Day balance, Daily Activity balance, Period Average-to-Date balance, Quarter Average-to-Date balance, and Year Average-to-Date balance.

The following amount types are available in the General Ledger average balances cube for inquiry or reporting using the Inquire and Analyze Average Balances page, Smart View, or reports created in Financial Reporting Studio:

  • End-of-Day (EOD)

  • Daily Activity

  • Period Average-to-Date (PATD)

  • Quarter Average-to-Date (QATD)

  • Year Average-to-Date (YATD)

Daily activity balances in the General Ledger average balances cube are derived by subtracting the prior day's EOD balance from today's EOD balance. PATD, QATD, and YATD balances are derived by summing the EOD balances for each day into an aggregate balance that's divided by the correct number of days in a period, quarter, and year.

Types of Average Balances

To satisfy different reporting and analysis requirements, the application tracks these types of average balances:

  • Period average-to-date

  • Quarter average-to-date

  • Year average-to-date

Note: Only actual balances are tracked. You can't track budget or encumbrance balances.

Examples of Average-to-Date Balances

These examples show how the application calculates period, quarter, and year average-to-date balances.

Period Average-to-Date Balances

In this example, the ending balance in an account on May 31 is 100,000 (USD). Here are the daily activity, end-of-day (EOD), period-to-date (PTD) aggregate, and period average-to-date (PATD) balances for the first three days in June.

Date Daily Activity EOD PTD Aggregate PATD Days in Period to Date

June 1

5,000

105,000

105,000

105,000

1

June 2

8,000

113,000

218,000

109,000

2

June 3

4,000

117,000

335,000

111,667.67

3

The PATD balance for June 3 is calculated by taking the PTD aggregate balance for June 3 and dividing it by the number of days in the period to date, or 335,000 divided by 3, which is 111,667.67.

Note: PTD aggregate balances reset to zero at the beginning of each period.

Quarter Average-to-Date Balances

Expanding on the previous example, in this scenario, the ending balance in an account on March 31 is 70,000 (USD). Here are the daily activity, EOD, PTD aggregate, quarter-to-date (QTD) aggregate, and quarter average-to-date (QATD) balances for the first three days in April and the first three days in June, which is the last period in the quarter.

Day Daily Activity EOD PTD Aggregate QTD Aggregate QTD Average Days in Quarter to Date

April 1

2,000

72,000

72,000

72,000

72,000

1

April 2

3,000

75,000

147,000

147,000

73,500

2

April 3

(1,000)

74,000

221,000

221,000

73,666,67

3

.

.

.

.

.

.

.

June 1

5,000

105,000

105,000

5,145,000

82,983.87

62

June 2

8,000

113,000

218,000

5,258,000

83,460.32

63

June 3

4,000

117,000

335,000

5,375,000

83,984.38

64

The QATD balance for June 3 is calculated by taking the QTD aggregate balance as of June 3 and dividing it by the number of days in the quarter to date, or 5,375,000 divided by 64, which is 83,984.38.

Note: The QTD aggregate balance is reset to zero at the beginning of each quarter. Accordingly, throughout the first period of a quarter, the PTD and QTD aggregate balances for any day are the same.

Year Average-to-Date Balances

Expanding on the previous two examples, in this example, the ending balance for December 31 of the previous year is 50,000 (USD) and the account under consideration is a balance sheet account. Income statement accounts don't have opening balances. Here are the daily activity, EOD, PTD aggregate, QTD aggregate, year-to-date (YTD) aggregate, and year average-to-date (YATD) balances for the first three days in January, the first three days in April, and the first three days in June.

Day Daily Activity EOD PTD Aggregate QTD Aggregate YTD Aggregate YATD Days in Year to Date

January 1

4,000

54,000

54,000

54,000

54,000

54,000

1

January 2

2,000

56,000

110,000

110,000

110,000

55,000

2

January 3

0

56,000

166,000

166,000

166,000

55,333.33

3

.

.

.

.

.

.

.

April 1

2,000

72,000

72,000

72,000

5,711,000

62,758.24

91

April 2

3,000

75,000

147,000

147,000

5,786,000

62,891.30

92

April 3

(1,000)

74,000

221,000

221,000

5,860,000

63,010.75

93

.

.

.

.

.

.

.

.

June 1

5,000

105,000

105,000

5,145,000

10,784,000

70,947.37

152

June 2

8,000

113,000

218,000

5,258,000

10,897,000

71,222.22

153

June 3

4,000

117,000

335,000

5,375,000

11,014,000

71,519.48

154

The YATD balance for June 3 is calculated by taking the YTD aggregate balance as of June 3 and dividing it by the number of days in the year to date, or 11,014,000 divided by 154, which is 71,519.48.

Note: The YTD aggregate balance is reset to zero at the beginning of each year. Accordingly, for every day in the first period of a year, the PTD, QTD, and YTD aggregate balances are the same.
Note: All three aggregate balances reset to zero at the beginning of a new year for all accounts.

To use average balance processing, you must enable it for a specific ledger. With this feature, you can enable average balance processing only for those ledgers that require it. This ensures you incur no additional overhead unless you need average balance processing.

Here are some other key aspects of average balance processing.

Balances Capture

The application calculates the necessary aggregate balance information needed to calculate average balance amounts as of any day in the year.

Effective-Date Transaction Processing

A transaction's effective date determines which end-of-day and aggregate balances are updated. These balances, in turn, determine the calculated values of your average balances.

Transaction Calendar Control

Certain organizations that need average balance processing, such as financial institutions, are required to post transactions only on business days. Posting on weekends or holidays isn't allowed, although some organizations do post period-end accruals on nonbusiness days. You can control transaction posting with a transaction calendar. When you define a transaction calendar, you choose which days of the week are business days and you also specify the holidays.

Each ledger that's enabled for average balance processing is assigned a transaction calendar. When transactions are posted, the application checks the effective dates against the transaction calendar. If the dates are valid, the transaction is posted. For invalid dates, you can tell the application how you want the transaction handled.

Here are some other points on transaction calendar control.

  • Multiple ledgers can share a transaction calendar.

  • You can combine multiple ledgers in a ledger set to take advantage of processing efficiencies, such as opening and closing periods across multiple ledgers. All ledgers in a ledger set must share the same chart of accounts and accounting calendar, but can use different transaction calendars.

  • You can allow certain users to post transactions on nonbusiness days.

  • Controls are applied to imported journals, as well as manual journals.

Transaction Balancing by Effective Date Control

Usually, the application requires that total transactions balance for an entire period. When you enable average balance processing, the application checks total transactions for each effective date to ensure that debits and credits balance. When they don't, the transactions are rejected, or, if you have enabled suspense posting, the application creates a balancing entry to the suspense account.

Manual journals are balanced directly, since the effective date is entered at the journal level, not for individual journal lines. Imported journals are sorted and must be in balance by effective date within each source.

Backdated Transactions

A backdated transaction isn't limited to the current period. It can be in a prior period, or even in a period from a prior year, as long as the effective date is posted in an open period. Here's what happens when you post a backdated transaction:

  • The change to average balances is calculated using the effective date, rather than the current accounting date.

  • The application adjusts the ending and aggregate balances as of the effective date and all subsequent dates.

  • And the application adjusts both standard and average balances based on the same effective date.

Reporting

You can use the Inquire and Analyze Average Balances page, Smart View, and Financial Reports to review online information about the average or end-of-day balance of any balance sheet account for your ledgers or ledger sets. You can view summary or detail balances.

You can also run these predefined reports:

  • Average Balances Trial Balance Report: Displays standard and average balances for selected ledgers and accounts, as well as period, quarter, and year average-to-date balances for any accounting date you specify.

  • Average Balance Audit Account Analysis Report: Displays the detail account activity used to create the aggregate and related average balances.

Multicurrency Accounting

The application maintains average and end-of-day balances for all transaction currencies, in addition to the ledger currency. Converted amounts and revalued balances, as well as unrealized gains or losses, are factored into average balance calculations.

Effective Date Handling

The effective date on which transactions are posted has a direct impact on average balance calculations. Effective dates are equally important when selecting inquiry or reporting criteria, since your report displays average balance amounts as of your specified effective date.

Backdated Transactions and Average Balances

When you post a backdated transaction, the application adjusts the end-of-day and aggregate balances of the affected accounts, as of the effective date and all subsequent dates. The following example continues the general example and illustrates what happens when you post a backdated transaction.

Here are the end-of-day and aggregate balances from the example previously described in the Example of Average Balance Processing topic.

Day Account A End-of-Day Account A Aggregate Account B End-of-Day Account B Aggregate Account C End-of-Day Account C Aggregate

Day 1

1,000

1,000

(1,000)

(1,000)

0

0

Day 2

1,100

2,100

(1,000)

(2,000)

(100)

(100)

Day 3

1,100

3,200

(800)

(,2,800)

(300)

(400)

Here's the average balance for each account on Day 3.

Account Calculation Average Balance

Account A

3,200 / 3

1,066.66

Account B

(2,800) / 3

(933.33)

Account C

(400) / 3

(133.33)

Now assume that the backdated transaction shown in the following table occurs on Day 3, with an effective date of Day 1.

Account Debit Credit

Account A

500

Account B

500

The effects of the backdated transaction are shown in the following table.

Day Account A End-of-Day Account A Aggregate Account B End-of-Day Account B Aggregate Account C End-of-Day Account C Aggregate

Day 1

1,500

1,500

(1,500)

(1,500)

0

0

Day 2

1,600

3,100

(1,500)

(3,000)

(100)

(100)

Day 3

1,600

4,700

(1,300)

(4,300)

(300)

(400)

Here's the average balance now for each account on Day 3.

Account Calculation Average Balance

Account A

4,700 / 3

1,566.66

Account B

(4,300) / 3

(1,433.33)

Account C

(400) / 3

(133.33)

You can enable average balance processing by selecting the Enable Average Balances option when defining a ledger in Functional Setup Manager. In the Setup and Maintenance work area use the Specify Ledger Options task:

  • Offering: Financials

  • Functional Area: General Ledger

  • Task: Specify Ledger Options, with the ledger scope set

When you enable average balance processing, by default it's enabled only for balance sheet accounts. If you want to also include income statement accounts, you must explicitly enable the Include Income Statement Accounts option.

Note: You can only enable income statements for newly created ledgers. You can't enable income statement accounts on average balance processing ledgers that are already created.

Once your accounting setup is complete, the application automatically begins to store the balances that are used to calculate average and end-of-day balances for your ledger.

Note: During implementation, you can convert a standard ledger to an average balance ledger before the first period is opened, or you can convert an average balance ledger to a standard ledger before submitting the accounting configuration.

Transaction Calendars

Use a transactional calendar to indicate which days in an accounting calendar are business days or nonbusiness days. In the Setup and Maintenance work area, use the Manage Transaction Calendars task:

  • Offering: Financials

  • Functional Area: Financial Reporting Structures

  • Task: Manage Transaction Calendars

When you first define the calendar, you specify a name and optionally, a description. The application uses this information to create the calendar, which includes an entry for every calendar day in the range of dates that exist in your application. Each entry includes these items:

  • Date: The actual calendar date.

  • Day of Week: The day of the week.

  • Business Day: An indicator that shows whether the entry is defined as a business day. The business day defaults to Yes for Monday through Friday and No for Saturday and Sunday. You can change the initial default values to suit your own needs.

After the transaction calendar is created, you should specify your holidays by changing the Business Day indicator to a nonbusiness day.

Here are some points to note for nonbusiness days:

  • Nonbusiness days can't be used for posting, unless you explicitly indicate that posting is allowed.

  • Nonbusiness days are included in determining the number of days in a period range.

  • Even though transactions aren't generally posted to accounts on nonbusiness days, the application still maintains aggregate and average balances for nonbusiness days, as well as business days.

Transaction calendars and accounting calendars are completely independent of each other. For example, you might have one accounting calendar, shared by your parent company and all its subsidiaries. However, each subsidiary might use a separate transaction calendar to accommodate their different holiday schedules.

Ledgers

You define the attributes of a ledger, such as accounting calendar, ledger currency, chart of accounts and subledger accounting method in Functional Setup Manager. You can define a ledger with average balance processing enabled.

In a typical ledger where average balance processing is enabled, the standard and average balances are linked, since the average balances are derived from the standard balances. To enforce this linkage, the application prevents you from creating journal entries that directly manipulate average balances.

If you choose to enable average balance processing, you must specify additional information when defining the ledger, such as:

  • Transaction Calendar: A calendar used to ensure that transactions are posted only to valid business days.

  • Net Income Account: An account the application uses to capture the net activity of all revenue and expense accounts when calculating the average balance for retained earnings. If you decide to include income statement accounts for average balance processing, then you don't need to specify the Net Income Account.

    Note: If you enable the option to include income statement accounts, the Net Income Account field won't appear.

Net Income Account

If you decide not to include income statement accounts for average balance processing, then you must specify the net income account when you enable the ledger for average balance processing.

Retained earnings contains two components for any interim accounting period:

  • Current account balance, which is equal to the final ending balance from the previous year.

  • Net income, which is the net of all revenue and expense accounts.

The application calculates the average balance for retained earnings the same way that it calculates average balances for any other account. However, since the application doesn't maintain average balances for revenue and expense accounts, some special processing takes place to handle this particular component of retained earnings.

The application uses a special nonpostable net income account to capture the net activity of all revenue and expense accounts. The account is treated as a balance sheet account, with an account type of Owners' Equity. It's used to calculate the net income impact on the average balances for any given period, quarter or year.

Note: You can also use the nonpostable net income account in your reports and online inquiries.
Note: The primary difference between the nonpostable net income account and other balance sheet accounts, is that the nonpostable balance doesn't roll forward when you open a new year. Instead, the application resets the account to zero when revenues and expenses are closed out to retained earnings at the end of the year.

You can use two methods to control transaction processing when effective dates fall on nonbusiness days.

  • By User: The Enter Journal for Non-Business Day Transactions privilege is assigned by default to both of the predefined General Accounting Manager and General Accountant roles. It controls whether the application allows transactions to be posted on nonbusiness days for manually entered journals.

  • By Source: You can specify an Accounting Date Rule for each journal source when average balance processing is enabled. You can select one of the following three options, which tell the application how to handle transactions whose effective dates are nonbusiness days:

    • Leave Alone: Accept transaction dates and complete posting.

    • Fail: Reject transactions and don't post.

    • Roll Date: Roll transactions to the previous valid business day within the same period, and complete posting.

      Note: The roll date can't cross periods to find a valid business day. This example illustrates what happens when the effective date is close to a period boundary. If April third is a Monday and a transaction has an effective date of April second (Sunday), then the effective date is rolled to April third (Monday), not to March thirty-first (Friday).

      Use this method to control nonbusiness day processing of automated journals from your subledger applications, such as Oracle Fusion Receivables and Oracle Fusion Payables.

Opening a Period

When you open an accounting period, the application prepares the new period for journal entry.

If you open a period when average balance processing is enabled, the application initializes the aggregate balances. Period-to-date aggregates are set to zero. Quarter-to-date and year-to-date aggregates are only initialized if the new period is at the beginning of a quarter or a year, respectively. Otherwise, the ending quarter-to-date and year-to-date aggregates from the previous period are carried forward as the beginning balance of the new period.

If you enabled income statement accounts for average balance processing, when you open the first period of a new fiscal year, the application resets the income and expenses accounts to zero. Aggregates are set back to zero at the beginning of each new year for the period average-to-date, quarter average-to-date, and year average-to-date balances. The beginning balance of retained earnings is set to the sum of the prior year's ending balance, plus the net of the ending balances of the income and expenses accounts.

If you haven't enabled income statement accounts for average balance processing, when you open the first period of a new fiscal year, the application sets the nonpostable net income account back to zero. Aggregates are set back to zero at the beginning of each new year for period average-to-date, quarter average-to-date, and year average-to-date balances. The beginning balance of retained earnings is set to the sum of the prior year's ending balance, plus the ending balance of the nonpostable net income account.