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:
- Log on to Exact Online.
- Select one company.
- Enter the formula:
I_EOL_BAL_PDER(, 2017, 1, 7,,, "W") - Press enter.
- 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 |