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

Labels * excel-formulas * exact-online * Labels bewerken Bekijk Eigenschappen Bestand bewerken |
Microsoft Excel-spreadsheet Exact Online - Sample - i_eol_bal_pder balance cumulative profit loss.xlsx | 27-11-2019 by Guido Leenders |

- Sleep hier je bestanden naartoe of bladeren

Upload bestand

Bestand omschrijving