Cumulative Profit/Loss Excel formulas per Financial Period

Invantive Control has a popular Excel formula I_EOL_BAL_PDER to retrieve the cumulative balance of a General Ledger account for a specific period. This article explains how to use it to retrieve the cumulative profit/loss actuals across all General Ledger accounts, without specifying each of them individually.

Instructions

Perform the following steps:

  1. Log on to Exact Online.
  2. Select one company.
  3. Enter the formula: I_EOL_BAL_PDER(, 2017, 1, 7,,, "W")
  4. Press enter.
  5. The cumulative profit/loss for period 1 upto 7 for fiscal year 2017 is displayed in the cell.

Besides specifying “W” for profit/loss, you can also specify “B” to limit the outcome to the balance sheet. However, in that case note that you need to include the opening balance too. Instead of summing the opening balance and cumulative balance you might want to use the more powerful formula I_EOL_BAL_PDE_CLOSE.

Other filters are available as parameters, such as General Ledger account type (“solely include bank transactions”). Press the Excel function wizard button [f(x)] to get an overview when you have the cursor positioned on the formula.

Sample

The following picture shows the figures on a demo company in 2017:

Using the following formula you can retrieve the cumulative profit and loss actuals total per period:

Combine actuals with Exact Online Budget

The Excel formulas I_EOL_BGT_PDE_LINE_AMOUNT and I_EOL_BGT_PDER_LINE_AMOUNT can be used to retrieve the allocated budget on a General Ledger account for, respectively, a specific financial period and a range of financial periods.

The Excel formulas I_EOL_BGT_PDE_LINE_AMOUNT_CLASSIFICATION and I_EOL_BGT_PDER_LINE_AMOUNT_CLASSIFICATION can be used for the total budget across all general ledger accounts within a classification.

Bestand Gewijzigd
Microsoft Excel-spreadsheet Exact Online - Sample - i_eol_bal_pder balance cumulative profit loss.xlsx 27-11-2019 by Guido Leenders