Transformation of financial data from YTD into periodic in Budgeting & Planning
If the YTD Load property in the Entities business object is TRUE, then this logic is used for loading data:
- YTD data for periods 1-12 and 13-16 from the BudgetingMain integration table can be loaded into Budgeting & Planning.
- During the data load, periodic values for a specific period, entity, account, and other structures are calculated. The previous period's YTD value that is stored in a cube is deducted from the YTD value for the current period. For example, the BudgetingMain table contains the YTD amount of 50 USD for period 2.2022 and 20 USD for period 1.2022. The calculated periodic value that is loaded into Budgeting & Planning for period 2.2022 is 30 USD.
- To load data to a specific entity, account, period, and other structures,
the BudgetingMain integration table or the Budgeting and Planning Main cube must contain the
YTD value for the previous period. For example, to load data to period 3.2022, the
BudgetingMain integration table must contain a record with the YTD amount for period 2.2022
or the YTD amount for period 2.2022 must exist in the Budgeting and Planning Main cube from
the previous data load. Note: An exception to this rule is if you start with period 2 and there is no data in period 1 for all accounts. This exception case is handled.
This table shows an example with periodic values for each period:
| BudgetingMain | OLAP | ||||||
|---|---|---|---|---|---|---|---|
| Period | Year | Entity | Account | Intercompany | Value | Periodic | YTD |
| 1 | 2021 | A | 123 | B | 100 | 100 | 100 |
| 2 | 2021 | A | 123 | B | 150 | 50 | 150 |
| 3 | 2021 | A | 123 | B | 210 | 60 | 210 |
| 4 | 2021 | A | 123 | B | 300 | 90 | |
To calculate an OLAP periodic value for the current period, all the previous periodic values in a specific year in OLAP (100+50+60=210) are deducted from the current period's value in the BudgetingMain table (300).
300-(100+50+60)=90
This table shows an example with missing values at the entity level:
| BudgetingMain | OLAP | ||||||
|---|---|---|---|---|---|---|---|
| Period | Year | Entity | Account | Intercompany | Value | Periodic | YTD |
| 1 | 2021 | A | 123 | B | 100 | 100 | 100 |
| 1 | 2021 | A | 567 | B | 50 | 50 | 50 |
| 2 | 2021 | records for all accounts are missing | no amounts | ||||
| 3 | 2021 | A | 567 | B | 99 | error | error |
| 3 | 2021 | A | 123 | B | 300 | error | error |
If no previous period's periodic value for an entity exists in OLAP but there is data from older periods, an error is displayed.
If you store historical data in the Budgeting and Planning main cube and you load data for a period in which periodic data for the previous period is missing only for some accounts, then the YTD value from the Budgeting and Planning Main cube is used to calculate the periodic value.
This table shows an example with missing values at the account level, for example, at the entity-account or entity-intercompany level:
| BudgetingMain | OLAP | ||||||
|---|---|---|---|---|---|---|---|
| Period | Year | Entity | Account | Intercompany | Value | Periodic | YTD |
| 1 | 2021 | A | 123 | B | 100 | 100 | 100 |
| 2 | 2021 | record for account 123 is missing | missing | ||||
| 2 | 2021 | A | 567 | B | 50 | 50 | 50 |
| 3 | 2021 | record for account 123 is missing | missing | ||||
| 3 | 2021 | A | 567 | B | 99 | 49 | 99 |
| 4 | 2021 | A | 123 | B | 300 | 200 | |
With missing values for the previous periods for account 123, the current period's periodic value for this account in OLAP is calculated by deducting the YTD value (100) from the current period's value for this account in the BudgetingMain table (300).
300-100=200