In Sage X3, we have the functionality of Financial Data Extraction using which user can create various reports such as Balance Sheet, P&L statements.
Steps of Financial Data Extraction functionality in Sage X3
In this feature, user can add the range of GL/groups/subgroups providing arithmetic operations on the output values with the given range of dates.
For better outcome and reduce the hectic of manual addition of accounts, there is feature to add groups & sub-groups in the screen. Accordingly, system will perform the summation for the accounts added into the particular group, sub-group and will show final sum amount.
Below is one of the examples of balance sheet created using groups & subgroups:
(navigation path: Setup ⇒Financials ⇒Accounting forms ⇒Financial Data Extraction)
In above example, columns are represented as the periods for the amount to be shown such as monthly & yearly.
Under lines section, Title column is free text field where we can add the headers rows wise to understand the respective values calculated column wise.
Once the headers are defined, we can add the groups by clicking on “Actions” button besides the title column as highlighted in below image:
Below screen will popup where you can define the company/group from which you want to extract the data.
Then provide the formula type such as “Analytical Accounting” (this analytical ledger will be common in between multiple companies due to which all transactions of mentioned companies will get recorded in this ledger.)
Under “analytical acct” section, system will provide provision to select ledger type as “Analytical”. Select the pyramid created for balance sheet as highlighted in below image:
Then select the group as highlighted in below image:
Once done, click on OK button and system will generate the formula as per selection and will show in field as highlighted below:
Once done, click on save button and navigate to path:
Financials ⇒ Reporting ⇒ Financial Data Extraction ⇒ Inquiry and open the created report.
Click on “Calculation” button and provide the input parameters as requested by system in the columns which we added at the time of setup. Please note, selection of currency will filter out the transactions from the ledger which is provided in the formula at the type of creation of report. Print Currency will show the value in selected currency under the column with the given rate type such as daily, monthly, etc. from the currency master.
Accordingly, system will show the balances on the screens which can be extracted in the excel as highlighted below:
