Import of Actuals via Excel
Actuals can be uploaded via Excel per General Ledger.
If Actuals are imported, the Actuals will overwrite the forecast up to and including the import date.
This creates a multi-year Financial Plan, composed of a combination of Actuals (past) and forecast (future).
This means:
1. If General Ledgers are linked in the General Structure of the Financial Statement, differences between Forecast and Actuals will be reversed in the next period after the import date.
This ensures that the total forecast (consisting of Actuals + rest forecast) of Reaforce projects and RE Assets projects remains the same throughout the entire project duration.
2. If General Ledgers are linked in the Excel Structure of the Financial Statement, then differences between Forecast and Actuals will not be reversed in the next period after the import date.
Calculation example for situation under 1:
The calculation following example shows that the total imported Cash Flow Forecast is 200.
In periods 1 to 6 this is a total amount of -/- 430
In periods 7 to 12 this is a total amount of 630
Actuals are then imported in period 1 to 6 for a total amount of -/- 445
The difference between the Forecast and Actuals in periods 1 to 6 for a total amount of 15 is corrected in period 7.
As a result, the Forecast amount in period 7 is adjusted from 40 up to 25, so that the total Forecast of 200 is maintained.
WARNING:
Before being able to import Actuals, the Structure of the Financial Statement needs to be defined by linking the General Ledgers (see Linking General Ledgers in the Financial Statement).
For importing Actuals, the Financial Controller selects in the navigation panel Source Data – Excel actuals (see the following screenshot) after which the following steps are followed:
Step 1. Creating an Excel template
Click the button Download Excel template. The resulting Excel file can be stored locally for entering data later.
Step 2. Modifying the Excel file
The downloaded Excel file is ready for filling with Actuals on Entity level (see the following screenshot).
Actuals for multiple Entities can be imported via 1 Excel template.
The following information needs to be entered:
• The name of the Financial Plan. This is automatically added in when generating the Excel template.
• Name: fill in a name (e.g., Actuals). This name is shown in the Financial Statements
• Assume when not provided: Yes or No.
◦ If Yes, when a General Ledger in the importing Entity has no mutations at all for the importing periods, then 0 is imported.
◦ If No, when a General Ledger in the importing Entity has no mutations at all for the importing periods, then the forecasted amounts will be maintained for these periods.
• The date until which Actuals should be imported in the Financial Plan
• Then per line:
◦ Entity code
◦ General Ledger code
◦ Date
◦ Amount
Example - of An Excel actuals import file
Step 3. Importing the Excel file
By clicking Import Excel file a pop-up will show up. Click Browse to search your local storage for the correct modified file, click Import to execute the import action. A validity check is executed on the background regarding the data in the Excel file. In case of validation errors, these errors will be visualized to the extent possible. This error list can be exported to Excel if desired.
In case of errors, the file is not imported. Modify the Excel data and attempt to reload the data into RE Finance . A notification is generated when the Excel import is completed successfully (see the following screenshot).
After loading and validating the Excel data, the data is processed in RE Finance . Existing Actuals are deleted and overwritten with new Actuals when these Entities are presented again in a new Excel import. An overview is shown of successfully imported Actuals.
The imported Excel cash flow data, profit and loss data and balance sheet data is also visible in the Financial Statements under the Excel Actuals header (see Financial Statements).
Lastly, it is possible to remove an imported Excel Actuals file within the Entity. Select the relevant Actuals file and click the Delete button.