Difference between revisions of "How to initialize financial balances in Openbravo"

From InfiniteERP Wiki
Jump to: navigation, search
(Created page with "= Introduction = The financial balances initialization is an step of the data migration process. By financial balances we are referring to : * the "ledger account's" initial...")
 
(Master Data import)
Line 92: Line 92:
 
[[File:Initial_Data_Load.png|600px]]
 
[[File:Initial_Data_Load.png|600px]]
  
There is an [http://wiki.openbravo.com/wiki/Modules:IDL_User_Manual IDL User Manual] which explains in deep how to configure and how to use this tool, very briefly:
+
There is an [[IDL User Manual | IDL User Manual] which explains in deep how to configure and how to use this tool, very briefly:
 
* Initial Data Load tool supports the '''import of the following entities data''':
 
* Initial Data Load tool supports the '''import of the following entities data''':
 
** Assets
 
** Assets

Revision as of 14:52, 29 November 2021

Introduction

The financial balances initialization is an step of the data migration process.

By financial balances we are referring to :

  • the "ledger account's" initial balance
  • and the "bank/cash account's" initial balance

Migration Process

To start managing the business of an organization into Openbravo it is usually require a data migration process.

The data store in the old system needs to be migrated to Openbravo.

It is key to define an efficient migration strategy and process as this part of an implementation is key for the success of the implementation. Besides the migration process has a cost.
The relevance of that cost depends on the type and volume of data the organization wants to migrate.

Having said that and given the migration tools available the recommended option to take is not to migrate transactions such as orders, goods receipts/shipments and invoices but just accounting data
until a given date when the organization is able to enter and manage both transactional and accounting data into Openbravo.

Overall the migration process is detailed below:

  • Define and Import the Chart of Accounts of the organization
  • Define and Import Master Data such as:
    • Business Partners
    • Products
    • Price Lists
    • Warehouses
    • Stock
    • Cost
    • and Assets
      by using the "Initial Data Load" tool
  • Initialize financial balances by:
    • Defining, entering and posting in Openbravo a G/L Journal that includes the Opening Accounting entry of the General Ledger dated on the very first day of the accounting period.
      This step would require to close the accounting year in the old system, if any and enter the corresponding "Opening" accounting entry in Openbravo manually.
      The opening accounting entry needs to reflect each "Asset", "Liability" an "Owner's Equity" account type balance at a given date (i.e. 1st January, 2013).
      • Another way of doing would be to enter and post in Openbravo all the accounting entries of the accounting year and close the accounting year in Openbravo to get that the "Opening" accounting entry is automatically generated by Openbravo.
    • Defining, entering and posting in Openbravo a G/L Journal/s that includes all the accounting entries dated from the "Opening Accounting" entry date (i.e. 1st January, 2013)
      to the date when the organization wants to star managing its business in Openbravo. (i.e. 28th February, 2013, if the organization is able to manage its business in Openbravo dated on 1st March, 2013)
    • Entering the pending "Payments" by using the "Initial Data Load" tool or the "Invoices" not paid by using an extension of the Initial Data Load" tool:
      • Pending payments or Invoices not paid at a given date that in our example should also be 1st March, 2013.
    • Entering the initial balance of the organization's financial accounts in the Financial Account window.
      That initial balance needs to be dated on a given data that in our example should be the 1st March, 2013.

Finally, it is important to remark that it will not be possible to launch reports which do require transactional data such as fiscal reports,
until the date when the organization is able to manage transactional and accounting data in Openbravo.

Chart of Accounts definition

First thing is to have the Chart of Accounts of the organization imported in the Account Tree window.

Localization Packs include a Chart of Accounts csv file that is available for selection during the Initial Client Setup or the Initial Organization Setup process.
Those process are the processes which create a "Client" or an "Organization" in Openbravo, respectively.

Therefore, it is possible to:

  • modify that accounting csv file to include "customized" ledger accounts of the customer
  • and then create the Openbravo "Client" or "Organization" by using the processes mentioned above.


On the other hand, there is no way to import "customized" ledger accounts in existing "Clients" or "Organizations" as accounting csv files can not be imported/applied
by using the Enterprise Module Management system. The options available to workaround that constraint are:

  • Openbravo plans to work on an "Initial Data Load" tool for ledger accounts, by the moment it is possible to use the Import Data module to import accounts. See next section "Import Account".
  • To manually create those accounts in the Account Tree window as described in this article.

Import Accounts

Once the "Import Data" module is installed there is no need to apply any reference data. This module creates below folders in the Openbravo menu:

Import Data.png

As shown in the image below the "Import Loader Format" window details the accounting csv file format structure required

Import Loader Format.png

for the csv accounting file to be imported into Openbravo. For additional information about the accounting csv file structure, review this article

Import Account csv file.png

It is important to remark that this tool imports the accounts in the "Organization" selected in the "Profile" window.

Profile.png

That Organization needs to have already defined an Account Tree that do not necessary have elements or accounts, as those are the ones to be imported.

The "Import File Loader" window allows to select the accounting csv file and the "Format File" that needs to be "Accounting - Accounts".
The checkbox "Header first line" needs to be selected to get that the first line which contains the column names is not imported.

Once imported, the "Process" button moves the accounts imported into the window "Import Account".
Once in that window, the imported accounts can be modified or even new ones can be created.

The account's import process ends once the process button "Import Accounts" of the "Import Account" window is used.

Import Account.png

This process creates all the accounts in the "Element Value" tab of the Account Tree of the Organization.

Account Tree Element.png

Master Data import

Master data such as "Products" and "Business Partners" can be imported in Openbravo by using the "Initial Data Load" (IDL) tool.
The initial data load tool is a "commercial" module that can be installed in Openbravo 3 from the "Module Management" window as it is registered and published in the Forge.
An Openbravo Commercial License (OCL) is required to install the initial data load module.

Once installed the corresponding "Reference Data" needs to be applied to the organization. See image below:

Initial Data Load.png

There is an [[IDL User Manual | IDL User Manual] which explains in deep how to configure and how to use this tool, very briefly:

  • Initial Data Load tool supports the import of the following entities data:
    • Assets
    • Business Partners
    • Costing (Standard Cost)
    • Financial Accounts
    • Journal Entries
    • Payables (Payments)
    • Price Lists
    • Products
    • Projects
    • and Stock
      all of them can be found and configured if required in the "Entity Default Values" window available in the application path: Master Data Management // Initial Data Load // Setup
      once the module is installed.
  • Openbravo provides some templates in Microsoft Excel format that contains sheets for each of the entities. These templates once filled in with the data need to be save in a CSV file format for them to be imported into Openbravo.
  • Prior to the import process there is some Openbravo data that needs to be configured, data such as:
    • a Client and an Organization where the data is going to be imported
    • a Fiscal Calendar as well as the corresponding periods of a given year of the calendar. Those periods need to be opened.
  • The import sequence of the entities' data is important same way as the Openbravo data which needs to be configured prior to any import of an entity data.
    For instance before importing business partners payment terms needs to be configured in advanced.

Financial Balances Initialization

Financial balances can be split into:

  • Ledger account's initial balance.
    By ledger accounts we mean the Openbravo Account Tree "Subaccounts".
    Subaccounts is the lowest account tree level, that allows to post transactions to the ledger.
  • and Bank/Cash account's initial balance.
    By bank accounts we mean the initial balance of the Openbravo financial accounts which can either by "Bank" or "Cash" type.

This part of the migration process is key to get into Openbravo the correct financial situation of the organization which is being migrated.

This step can be split into below sub-steps:

Opening Accounting entry

First sub-step is about Defining and posting to the ledger a G/L Journal that includes an Opening Accounting entry which reflects each "Asset", "Liability" an "Owner's Equity" account type balance
at a given date (i.e. 1st January, 2013).

This "opening" G/L Journal can be imported into Openbravo by using the "Initial Data Load" tool, Journal Entries entity.

Openbravo recommends not to migrate transactional data such as "Orders", "Goods Receipt/Shipments" and do not paid "Invoices" but just the corresponding accounting information, therefore:

  • The "Opening" G/L Journal needs to be marked as "Opening"
  • The Accounting date of the "Opening" G/L Journal needs to be the first day of the year, i.e. 1st January, 2013
  • The "Opening" G/L Journal needs to contain the balance of :
    • "Assets" such as "Account Receivable", "Bank", "Cash", "Inventory", etc, and "Long-Term Assets" such as "Fixed Assets"
    • "Liabilities" such as "Account Payable", "Tax Payable", etc, and "Long-term Liabilities" such as loans and other long-term doubts.
    • and "Owner's Equities" such as "Capital" and "Retained Earnings".
In other words, the opening G/L Journal entry reflects among others do not paid customer/vendor invoices that is the "Outstanding debt" at a given date that is the "Opening" accounting date.

Rest of ledger accounting entries

It is necessary to import into Openbravo all the accounting entries that need to be posted to the ledger within a period that goes from the "Opening" accounting entry date till the date when
the organization it is able to manage transactional and accounting information by using Openbravo (i.e. from the 1st January, 2013 to the 28th February, 2013).

By rest of ledger accounting entries it is meant the accounting entries related to invoice's accounting entries or payments' accounting entries among others
that need to be posted to the ledger within that period of time.

The way to do that in Openbravo is by defining and posting to the ledger a G/L Journal that includes all the accounting entries with an "accounting date" between:

  • the "Opening" accounting date
  • to the date when the organization wants to star managing its business in Openbravo.

This G/L Journal/s can be imported into Openbravo by using the "Initial Data Load" tool, Journal Entries entity.

It is important to remark that Openbravo "G/L Journals" allows to enter not just accounting information but also Accounting Dimension such as "Business Partner", "Product" and "Project".
That information is very useful while entering the G/L Journal lines or accounting entries of sales/purchase invoices that needs to be posted to the ledger within that period of time.

Accounting dimensional information can be reviewed in the Accounting Transaction Details report.

Pending Payments or Not Paid Invoices

Third sub-step is about entering the payments pending to be received/paid at a given date, that is the date when the organization is able to manage the payment of those pending payments in Openbravo
(i.e. 1st March,, 2013). Otherwise, those payments would have been posted to the ledger as paid in the previous step.

By payments pending to be received/paid, it is meant:

  • Payments In pending to be received by the organization from their customers at a given date.
  • and Payments Out pending to be paid by the organization to their vendors at a given date.

These "Payments" can be imported into Openbravo by using the "Initial Data Load" tool, Payments entity.

Once imported the "status" of those payments is "Awaiting Execution".
Next step is to simply execute them manually by using the process button "Execute Payment" whenever the payment is either received, therefore its status changes to "Payment Received" or made therefore its status changes to "Payment Made".
Please review this article to learn more about payment status.

It is important to remark that this way of doing does not allow to manage partial payments if required. In other words:

  • Let's take for instance a customer invoice pending to be paid by a total amount of 1.000 €. That invoice has an Invoice Payment Plan that includes two partial payments.
  • Under this scenario the "Accounts Receivables" account "opening" balance includes among others the 1.000,00 € pending to be paid.
  • As the invoice issued has not been imported into Openbravo, there is the need of importing the pending payments related, therefore their payment can be managed in Openbravo.
  • It is not possible to import two partial payments but just one payment of 1.000,00 € related to the issued invoice.

To workaround above scenario, this step can be skip if the invoices pending to be paid are imported into Openbravo by using the Initial Data Load Extension for Invoices.
The invoices imported by that tool are disable for accounting, as otherwise that would mean a double posting, same way will never show up in the Not Posted Transactions Report because their "Post" column is 'D' (disable for accounting).

Once imported, the invoice related payments can be managed as normally.

Bank/Cash accounts initial balance

Finally, it is also required to enter the initial balance of the organization's financial accounts in the Financial Account window.

That initial balance needs to be dated on a given data, that date is the date when the Organization is able to manage in Openbravo transactional and accounting data (i.e. 1st March, 2013), in other words, it is able to pay the pending payment by using Openbravo.

The way to do that in Openbravo is by entering the bank/cash balance as of a given date (i.e 1st March 2013) in the field "Initial Balance" while creating the corresponding financial accounts
in the Financial Account window.

Later on the value of the "Initial Balance" field is used as the Bank/Cash "Starting Balance" within the first reconciliation of the bank/cash account into Openbravo.