When the SQL is executed in the Head Office, the budget data is displayed in Excel as shown below. This spreadsheet is saved and sent to the Finance teams in overseas branches (Sydney, Prague, Noumea), who will then finalize it and submit it back to Head Office. For example, the net amounts can be replaced with $700.

HOW TO CREATE ADVANCED APPLICATIONS WITH D4E

SUMMARY CONCLUSION:


This demonstration illustrates how you can build practical applications in Finance using various features of D4E.

In this demonstration, we will show how to create advanced applications using D4E's features by developing a Financial Information System (FIS).

In the FIS application, we aim to create and manage budgets for branch offices under the supervision of the Head Office. We will achieve this by developing the following functional requirements or user stories, using the Standard or the Advanced Edition, depending on business priority.

1. Extracting Budget Data for Remote Branches
As a remote Finance User, I want to extract my Budget Data from the HO database so that I can update it for the next budget period.
This functionality allows for extracting budget data from the Head Office database and sending it to remote branch offices via email for monthly updates.
PRIORITY: This is a must-have feature and can be delivered in the Standard Edition.

2. Sending Revised Budget Information from Remote Branches
As a Head Office Finance Officer, I want to receive final budgets from remote offices and update the HO database so that financial data can be maintained with integrity and security.
This enables remote branches to send finalized budget information, which is then seamlessly updated into the Head Office database with minimal effort and without re-processing or re-entering financial data.

PRIORITY: This is a must-have feature and can be delivered in the Standard Edition.  However an alternative approach is available in the Advanced Edition that will further empower the remote users.

3. Consolidating Final Budget Data Using Excel Reporting
As a Finance Analyst, I want to be able to create consolidated budget reports for all branches using the HO database so that financial data can be made available to senior management.
This feature automatically consolidates budget data in the Head Office database and generates Excel reports (such as pivot tables) every month for efficient reporting.

PRIORITY: This is a must-have feature and can be delivered in the Standard Edition.  Advanced Edition can provide optional automation.

4. Remote Work on the HO Database via Mobile Devices
As a Chief Finance Officer, I want to receive consolidated reports on my mobile devices on demand so that I can work remotely while traveling internationally in a self-service fashion.
This allows finance officers to work remotely on the HO database by receiving data on their mobile devices via email, with the assistance of D4E background processing, without direct access to the database.

PRIORITY: This is a nice-to-have feature (because the objective can still be achieved from HO) and can be delivered only in the Advanced Edition. 



Story 1 Implementation:  Extracting Budget Data for Remote Branches

The following SQL can be used to extract data from a database (e.g., .accdb).  It uses parameters to determine which country branch is being updated.

WHAT IS A MULTI-NATIONAL FINANCIAL INFORMATION SYSTEM:

Each country operation uses the same extract format and the same D4E app. Using parameters, the D4E app can be customised for different local databases, but the look and feel remain the same for the end-user. Each country then signs off on the spreadsheet and emails it to Head Office with changes to the content but not the format of the spreadsheet. Head Office accepts the signed-off spreadsheets and, without any re-keying, simply clicks the D4E upload button (Update Table) —automatically uploading each country’s data to the central HO servers. Head Office uses the same D4E application add-in and can upload the common data easily. Once all country data is accepted, the Head Office end-user can immediately generate a combined report for all operations.

In summary, country operations use the D4E app to download their individual trial balances. Since it is a fully tested application, there is no need for manual verification or re-keying. Users are confident that the data sent to Head Office comes directly from their General Ledger, and they know that HO will use the same tool to upload it—ensuring that the signed-off financial information is incorporated into group reporting exactly as intended, without errors or alterations. This is highly empowering.

Meanwhile, Head Office also uses the same Excel-based D4E application to upload the received data. They can be confident that the data just uploaded to the central server originated directly from the country operations’ local systems—thanks to the seamless integration provided by the shared D4E platform. This saves significant time and greatly increases confidence in the accuracy of group reports and board-level decisions.

The illustration below summarises the Data Mart for Excel downloader/uploader application.


Note that the spreadsheet was generated with an Action Code column in addition to the database fields. This Action Code will be used to update the database record with the new values. The HO support team will simply open the file above and click on the Update Table button, and the records will be updated in the database.

After updating the HO database, the HO team can extract the full database once budgets from all remote branches are finalized, submitted, and updated in the HO DB.



ALTERNATIVE PROCESS:


(This requires the Advanced Edition)

Although Head Office may require that they update the HO database for governance of the financial process, it is possible to empower the remote branches to update the HO database themselves for full self-service and end-to-end integrity and security. This can be implemented through a back-end process where remote branches send the email command with the submission files as attachments.

The back-end process (Scheduler) will repeatedly execute the following commands:
getinbound aelinon@caratrel.com
sendoutbound

The remote Finance User can send an email to the server with the same attachment (updatefile.xlsx with Action Codes) as below. This email will contain a title ($D4E$) to indicate that there is a macro command in the body (UpdateDBFile) specifically for the server to execute:

Story 2 Implementation:  Sending Revised Budget Information from Remote Branches


The same spreadsheet is returned to Head Office with updated values, e.g., $600 replaced with $700. Below is the final budget spreadsheet as submitted by the local teams. The remote branch will send the following file (updatefile.xlsx) as an attachment to Head Office:

Story 3 Implementation:  Consolidating Final Budget Data Using Excel Reporting


After all branches have updated the HO database with their individual budgets, HO team can extract and review the full database as follows:

Excel reports can be generated from this data, such as the pivot table below. Pivot tables can be refreshed directly in Excel by the end-user with new data updated from remote branches.

Below are 3 example command steps that can automate this process:  (This automation portion requires the Advanced Edition)

1. runsql GL H   - execute the SQL to extract the full budgets.

2. copyto "docs.BudgetData"   - copy the results to a sheet linked to by the pivot table below (named docs.Budget Rep).

3. refreshpivots   - refresh all pivot tables.

This way the pivot tables (in docs.Budget Rep) will always be linked to the latest budget data.

After the server executes the sendoutbound command as a scheduled task, the Finance user will receive the output of his requests. This will be the email response from the server:

Story 4 Implementation:  Remote Work on the HO Database via Mobile Devices


(This requires the Advanced Edition.)

With all data updates completed and reports available and refreshed, senior managers can extract their reports even while they are not directly connected to Head Office. To implement this, the D4E app in HO can be set up as a server to provide data to Finance users via mobile devices.


The D4E server can be started by clicking on Start Scheduler, and this will repeatedly execute tasks. The following tasks will be executed by the Scheduler every 5 minutes:

1. getinbound aelinon@caratrel.com
2. sendoutbound


GetInbound means that the server will be expecting commands via email from the Finance user. These commands will typically extract and prepare data for sending back to the requestor.

SendOutbound is the command to send the prepared data to the requestor’s email address.

With the server waiting in the background for commands via email, the Finance user can send an email containing commands as follows:

   CreateSendSheet   aelinon@caratrel.com   "My Report"   "docs.Budget Rep"


This command will instruct the server to send the report contained in the sheet docs.Budget Rep, which was previously refreshed after the HO database was updated with the latest data from branches.