External notes & SFTP interlinking
External notes functionality
This functionality makes provision for import of data from automated reports used directly in the primary and secondary disclosure data sets.
Input of Financial position data: manual vs automated
For the departmental Statement of Pos (SPos) sources are not only just the BAS TB. Other elements such as non-TB data such as accruals and commitments need to be prepared and / or obtained from other sources. Some of the information is coming from the notes which populate the SPos, to which the Notes are manual. The completion of the notes then pulls through to the BAS, to allow for SPos balancing.
Where the departments have comfort of the individual SPos notes, they then pull through to SPos itself and then have a balancing figure in Net Assets due to system overridden by manual inputs (majority of the time is at Recoverable Revenue). On the template for notes, cells marked as ‘Green’ are manual inputs on the AFS for external notes / secondary working papers.
Some of the Notes have elements where some parts come from the TB and some not from the TB.
Items not coming from the Trial Balance, have two options for its standard population:
· Standardised system report (via SFTP)
· Manual input
External notes
‘External notes’ functionality is for data which is not coming from BAS TB or Vulindlela, but comes from either via a standard system report (structured) or a ad-hoc manual element (e.g. related parties) (unstructured data). This will be linked to the ‘green’ cells in the AFS which pulls through once marked of as finalised to the AFS face.
The system structure for the functionality is set per the AFS structure to allow it for specific allocation. This is not necessary to top level (e.g. assets), but allow for ‘sub’-working papers via this functionalities for sub-levels (e.g. moveable assets, immovable assets, etc)
The underlying external notes file is a replication of the AFS notes, which allows for a carbon copy of the NT template note element (which is linked to the actual AFS: AFS Notes sheet), the input sheet where rough workings / calculations / preparatory notes are included (input sheet). Additional workspace(s) pages allows for additional data sets to be included which can then link to the Input note via standards spreadsheet functionality for shared data between colleagues.
Please note that multiple ‘INPUT’ sheets could be populating the locked ' AFS notes'. This may also for multiple sub-notes in the specific note, and as such split into separate sheets for each. Eg. sheet x (summary), sheet X.1 (prior year), sheet X.2 (reconciliation set), sheet X.3, etc.
Other colleagues can continuously update and amend the input / workspace data, to allow for final data elements to be run and once approved (marked as finalised on the system), pull through to the AFS face.
The SFTP import allows for files pushed by the Departmental SFTP setup to the shared server, to then push the standardised data to the similar pages as the ‘additional workspaces’ (once approved by the reviewers in terms of the workflow). It in essence acts as a middle man between rough data in a specific file (e.g. full alphabet) and then provide specific data sets which is required for external notes (export only lines ABC with specific conditions).
Please note that practical size limitation exist for external working paper sheets, as too large data sets may result in slow speeds and / or limited data. It is advised that sheets be optimised in terms of data required as well as to limit multiple data inputs. Where large datasets are required, it is also advised that the data be shaped and limited at the originating point (e.g. SAP export) rather than at import point (eg. SFTP).
Ad hoc support can be provided for shaping of new standardised files which wish to be automated to the ‘workspace’ sheets.
External notes and Secondary Working papers needs to be signed off as finalised before the shaped and structured data in the external notes are displayed on the AFS itself.
SFTP overview
SFTP stands for Secure File Transfer Protocol, to which we have setup a shared standardised data set which interlink between certain data of entity which can be included in the AFS module. Given the standardised nature, the processing of similar data sets (per specific or consolidated reports) allow for transversal application of such data to the individual AFS.
The functionality upload to the SFTP should be shaped (single header line with data setout headers) per XLS format (to allow for pickup of different sheet data), for system interaction and functionality.
Please note that running the SFTP importer service, it will override all data sets in the current output sheet, and as such care needs to be take, especially in the setup and linking of such data to the user edit sheets.
SFTP shaping for external notes: Import and Shaping
The shaping of the automated elements are via the SFTP Import system, which require:
Import of the SFTP file from the server
inputs: name & sheet details and
outputs: ID & sheet
The shaping of the data from the SFTP file into the external notes (via the column details set)
IMPORTING DATA INTO SFTP SHAPER
For the Import, the key elements are ‘IN:file_name', ‘IN:Sheet_name’, ‘OUT:Sheet_id’ and 'OUT:sheet_name’.
'In:File name’ From the SFTP setup, the shared documents are imported into Dynamic AFS. On the SFTP import ‘In:File name’ the applicable system output can be selected.
‘In: Sheet name’ allows user to select specific xls sheet to import the data into the ‘workspace’ in the external note.
‘Out: sheet id’ This is the Sheet id number of the externa working paper. This is visible in the URL for the external note.
‘Out: sheet name’. This is the location to where the data is pushed on the specific external note per ‘out:sheet id’. This will then push the SFTP file data per the set sheet name, to that specific document. Multiple SFTP file outputs can be pushed to a single external working paper. E.g. sheet X.1 and sheet X.2 are different SFTP file outputs.
Once completed, press ‘get data’ where the system checks the information to allow for the rows header.
Once ‘data was fetched’, the dropdown arrow (on the right side) can be clicked, which allows for the various columns to be displayed for shaping.
SHAPING OF INPUT DATA
Once the imported data set is accepted into the SFPT system, the individual data columns for the import can be shaped.
This allows for only the key information columns to be included.
‘Column name’: This is the data headers included in row 1 of the SFTP file, which allows for the only the relevant data to be imported for further assessment.
‘DataType’: This further formats the data to allow correct interaction on the ‘workspace’, given the computer understanding of such formats. (Options: Number, currency, text, data)
‘Filter’: This allows for dedicated filtering on specific outcomes in specific column (filter per a set value – see ‘value’ below) (options: null / greater / equals / less). Example for specific entity based on specific value, select ‘entity’ column’ under ‘filter’ and in ‘value’ set the condition (e.g. ABC). This can also be to allow for filterning values (i.e. all amounts in Column X which is zero)
‘Value’: This allows the value against the filtering condition is set.
If any adjustements on the column names, datatypes, filtertypes have been updated or deleted, this must be saved, by clicking the ‘update parameter’ button
PUSH SHAPED SFTP DATA INTO EXTERNAL WORKING PAPER
Once the columns are set, click ‘run’ (the ‘get data’ button changes to ‘run’ button) to allow the information to be processed and included in the specified external note working paper.
Manual inputs & SFTP overrides
Please note that the sheet location of the SFTP should be kept as an import location only (i.e. no further processing should occur on that sheet), as when the SFTP run function is activated, it will replace the current data set with the new data. No revert or recovery of the sheet data will be possible after such run. This will override all other data areas in that sheet. The ‘additional workingspace’ location should be used for the further manual inputs or processing of the data from the SFTTP output, be it for summarising, formulas for casting, grouping, etc.
Location sheet for SFTP file data should not be used for any other data manipulation or formulas, as this sheet content is replaced with each SFTP file run.
Number formatting of manual number inputs on external notes
Although data can be copied into an external note sheet on the system, some cell formatting may not be transferred. As such, please ensure to remove pre-formattings prior to copy of data into external notes sheet.
E.g. for copying of Excel based information onto the online platform, pre-formattings needs to be removed for Number / Accounting values. Such pre-formattings to be removed are:
Thousand separators, such (,) comma 1000’s separator.
Decimal separators should be a period (.)
Once formattings have been removed, the values can be copied and pasted. The system will then recognise the unformatted values.
Once unformatted values are in the system, the pasted figures can be formatted in the system.
Refreshing changes from the SFTP onto the AFS via the portal
Once the SFTP data has been run, go to the Portal. Under the ‘Financial Statements’ menu, please select ‘View external notes’.
This will bring up all the external notes which has been created.
Under the ‘Actions’ view, the will be two options:
Mark as ‘Complete’: This will shift the ‘Status’ from WIP, to complete. The data automated by the SFTP ‘run’ function, will then be possible to be included into the AFS.
Mark as ‘Finalised’: For the SFTP data to be shown in the AFS, the ‘Finalise’ button under ‘Actions’ needs to be selected by another users (management review level). Once this is selected, the ‘status’ will change to finalised and the SFPT data will be reflected in the AFS. If the AFS is already open, the applicable page can be refreshed.
Please note any change on the ‘external note’ sheet, will result the ‘status’ to revert back to ‘WIP’. This will result in the ‘complete’ and ‘finalise’ actions to be run, before the new values from the SFTP will be updated in the ‘green cells’ on the AFS.