Aberdeen Group recently reported that 46% of SMBs report a severe lack of data accessibility, keeping them from making better, faster decisions about their most critical operations. Data has become an increasingly hot topic in the business software world and not for the lack of a good reason- data, especially financial data, is crucial for businesses operating in today’s highly competitive world where every second and every penny counts. Every company has important data hiding somewhere in their business systems; for most companies though, as the research above points out, getting to it is the hard part. Sage X3 comes equipped with all of the financial reporting tools you need to quickly and easily access, control, and analyze your data. In this blog you will learn the three steps to successfully setting up the Financial Data Extraction (FDE) functionality from an experienced Sage ERP X3 Consultant.
The steps we will cover include:
- Setup, which will define the report characteristics, columns, rows, etc.
- Calculation, which generates the real-time data for the report.
- Inquiry, which will display the report data.
First you must find the FDE set up screen. From the Sage ERP X3 menu, go to setup> financials> accounting forms> financial data extraction (GESTXS). Then you will set up the definitions tab which displays the columns for the report. A sample is picture below:
In this screen, we show an Income Statement with two columns- Actual MTD and Actual YTD.
The cell that says “Total” allows for the definition of differences between columns. For instance, if we were to have a third column that shows a variance between the first and second column, the formula “1-2” can be entered in the Total field to give the result of column 1 minus column 2. This example is outlined in the image below.
Lines Tab: The Lines tab shows the row titles and formulas for the report. The Title column is free text entry and the columns, as you can see, are based on the entries in the definition tab we just covered. Formulas are entered to define the values that will appear in each cell when the report is run.
- The formula “5000..5699” indicates that all accounts between 500000 and 569999 will be added together (assuming the use of a six digit GL account number).
- The minus sign in front of the formula indicates an inverse sign for the result. We also see a formula that looks like “L10-L12”. This indicates that rows 10-12 in the lines grid will be added together.
Keep in mind that this is a relatively simple example and there are other formulas available for more advanced formula’s. Go to “Help” for the FDE screen for a list of available formula functions to help you build your reports.
Sage ERP X3 offers a wizard to walk you through selections that provide a resulting formula. This can help simplify the process significantly, especially for those who are new to the system or the FDE function. Accessing the wizard is easy, just right click inside one of the formula cells and select Add/Modify. As you make choices in the wizard, fields will be enabled for the necessary entries to build the formula. Here are a few more notes about the additional tabs you will use during setup:
- Selections: Not only can GL Balance values be displayed, but dimensions and budgets may also be displayed. Also, instead of account root selection, you may instead choose to select control accounts, reporting codes or pyramids.
- Variables Tab: In the Variables tab, variables can be defined, which may include values from another report or report version. These variables can replace any argument in the formulas.
- Reports Tab: The Reports tab provides header and footer text for the report. For instance, you can enter in free text between quotes, such as “Income Statement”. Or, you can enter a formula to pull table values, such as the Version, Run Date or Company Name.
Step #2: Financial Data Extraction Calculation
To find the calculation screen go to the menu and then select Financials >Reporting >Financial Data Extraction >Calculation. Select the report group/worksheet you need.
From there you can either allow the system to generate a version number for the calculation, or you can name the version yourself in the “version” cell. Either way, the version name will help you identify the calculation later when you look at the results.
In the “Parameter Definition” section, you can enter in the Company, the Start and End Dates for both columns and the variables (if you defined them) would be defined in the “Variables” section.
Step #3: Financial Data Extraction Inquiry.
Now it’s time to see the results of the calculation with the inquiry screen which is used to select the Extraction Code and version.
Go to menu>financials>reporting>financial data extraction> Inquiry
It will look like this:
The Parameter Definition tab is view-only. This tab is here to provide you with a reference to the selections made during data calculation. The Setup tab is also view-only and is there to give you a reference of how the FDE was originally set up.
You may choose to print the results or export them using the “Export” button.
Depending on how you set up the FDE you may be able to overwrite the numbers by typing new ones into the cells. If you do change them manually, they will be red.
There is a “calculation” button in the inquiry screen where you can recalculate the values in case something changes after your initial calculations. For example. Addition postings or changed postings to the GL.
The RTZ Values Entered checkbox gives you control over whether you want the manually entered values to be recalculated by the system. The checkbox for Including the Values Entered the calculation will include any value that has been deleted from a table.
The version history with the calculated values will remain in the list on the left, until deleted.
And there you have it, the three basic steps of setting up and using the Financial Data Extraction Tool in Sage ERP X3. If you have questions or need help getting set up in your system, contact us and one of our Sage ERP X3 Consultants would be happy to help. You can learn more about Sage ERP X3 in our resource library, click below to enter.