Financial Reporting in Adempiere


Problem Statement

While Adempiere comes up with powerful way of managing various financial reports, understanding and modifying these reports become tricky at times. Since business may need to add chart of accounts at run time, it becomes imperative for them to be able to include such accounts in financial reports. The purpose of this article is to enable business to be able to manage their financial reports more effectively.

Quick Overview on Financial Statements

Financial reports are formal records of a business’ financial activities. Often business need following financial reports

  • Balance Sheets
  • Profit and Loss Statements
  • Cash Flow Statement

These reports have two dimension and they are heavily dependent on the Chart of Accounts, which often differs for different company (unless companies always run with default account settings!).

Balance Sheet

When it comes to balance sheet, a standard company balance sheet has three parts and often business expect to see their subtotals

  • Assets
    • Current Assets
    • Fixed Assets
    • Liabilities
    • Ownership equity.

Also, the balance sheet is expected to respect the rule “Total Asset = Owner’s Equity + Total Liability”.

Two formats of balance sheet are very popular

  1. The columnar report showing balance of previous year as well as current year
  2. Side-by-side report where assets are shown on the left side and liability & owner’s equity are shown on the right side. The totals of assets and total of liability and equity are shown in the bottom.

By default Adempiere supports the first one.

Profit & Loss Statement

This is a company’s financial statement that indicates how the revenue is transformed into the net income. It displays the revenues recognized for a specific period, and the cost and expenses charged against these revenues, including write-offs and taxes. There are two types of P&L statements

  1. Single Step P & L – totaling revenues and subtracting expenses to find the bottom line
  2. Multiple step P & L – totaling and subtotaling at various level and finally deriving the bottom line

Adempiere allows you to be able to configure both these format.

Cash Flow Statement

The cash flow statement is concerned with the flow of cash in (receipt) and cash out (payment) of the business. You often record the offset accounts against which the payments / receipt has happened.

Financial Reporting Using Adempiere

Adempiere provides you following interfaces to create financial reports

  1. Report Line Set – determines which lines are printed in a Financial Report
  2. Report Column Set – identifies the columns used in a Financial Report
  3. Reporting Hierarchy – Reporting Hierarchy allows you to select different Hierarchies/Trees for the report. Accounting Segments like Organization, Account, Product may have several hierarchies to accommodate different views on the business
  4. Report Cube – allows you to define user customizable “materialized view” of the accounting fact table. Too much data into accounting fact table slows down the reporting process significantly. The pre-calculated & summarized (at period level) values in this cube facilitates fast processing for reports. This interface allows you to define multiple cubes depending on the desired dimensions and calendar.
  5. Recalculate Cube – This interface is used to delete the old summary and create updated summary in the cube.
  6. Financial Report – The interface to build the actual financial report using concepts like Report Line, Report Column, Report Cube and Jasper Reports

Deep Dive

Financial Report

You use this window to create and run financial reports (the reports based on chart of accounts). Using this window you can link a report with the “Report Line Set” and “Report Column Set”. Depending on the role settings the user can update the values of Report Line Set & Report Column Set to see different view of the same report.

Financial Report

While I will discuss report line set and column set later, it is important to know about following fields

  • The Report Cube is an optional parameter which allows you to configure the Report Cube to be used. If selected, pre-summarized data is obtained from the cube. If empty, the report will be generated running directly based on accounting fact table.
  • List Sources check box allows you to indicate if the sources of the summary data shall be listed or not
  • List Transactions checkbox allows you to indicate if the transactions associated with the source line shall be listed or not

Report Line Set

The report line set allows you to define the lines that will be displayed in the report. The Copy Lines button allows you to select an existing report line and copy their lines in the current report line. This is specifically useful when you may need to add / delete few fields.

On the report line you will be able to specify the line item that will appear on the report.

Important information on this tab:

  1. Line Type – allows you to define if the line item is a Segment Value or Calculation.
  • Segment Value
    • Posting Type – indicates the type of amount involved in the transaction (for example if the transaction is a commitment for payment or receipt or was it an actual payment or receipt). Often following posting types are involved:
      • Actual (e.g. Payment Received / Made, Down Payment) – In case the transaction is document controlled then you can only use the Actual posting type to post the transaction.
      • Commitment (e.g. Purchase Order, Down Payment Request, Invoice Receipt/Issue)
      • Budget – If you want to define budget for certain department or activity so that you can track and compare with actual at later stage then you use posting type as “Budget”
      • Reservation – Often applicable when you want to reserve certain inventory items for sales or production purpose
      • Statistical – Often used during asset transfer.
      • Amount Type – This value is often left blank. Following amount types are supported:
        • Quantity (accounted sign) – The “accounted sign” always returns DR-CR
        •  Quantity (expected sign) – “expected sign” adjusts the sign of the result based on the Account Type and Expected Sign of each Account Element
        • Credit Only
        • Debit Only
        • Balance (expected sign) –  “expected sign” adjusts the sign of the result based on the Account Type and Expected Sign of each Account Element
        • Balance (accounted sign) – The “accounted sign” always returns DR-CR.
        • Period Type – This value is often left blank. Following period types are supported
          • Period
          • Year
          • Total
          • Natural – In case you select Natural then Year for P & L accounts and Total for Balance Sheet accounts will be considered.
          • Calculation
            • Operations
              • By default system comes up with following operations
  1. Add (Op1 + Op2)
  2. Add Range (Op1 to Op2)
  3. Percentage (Op1 of Op2)
  4. Subtract (Op1 – Op2)
  • In case you have a need to make use of more than two operands then you can combine the lines created using above operands to fulfill your need. For example if there is a need for Op1 + Op2 + Op3 then you can define one line as trl1 = Op1 + Op2 and the final line as frl = trl1 + Op3. One thing you may need to remember is that – in case you don’t wish to see the temporary report line on the actual financial report then the “Printed” checkbox must be unticked.
  • Operands – the operands are the report lines already added for the Report Line Set.

For every report line of line type “Segment” you can optionally associate one or more source information where you specify the actual chart of account.

By default Adempiere comes up with following source types

  • Account (most commonly used)
  • Activity
  • Business Partner
  • Campaign
  • Combination
  • Location From
  • Location To
  • Organization
  • Org Trx
  • Product
  • Project
  • Sales Region
  • Sub Account

(Note: I have tested for Accounts only. But depending on how you intend to do the reporting, others will be definitely useful.)

Report Column Set

The Report Column Set defines what data is printed in the columns of a report. The Report Column tab allows you to define a column in a Financial Report.

The fields like Posting Type, Period Type and Amount Type has similar meaning as we have seen in Report Line Set configuration. However, it does have following important fields which are worth talking about:

  • Column Type
    • Calculations
    • Segment Value
    • Relative Period
      • While Calculation and Segment Value has same meaning as it was in case of Report line set, the relative period in column set allows you to specify the period relative to currently active period.
        • A value of -1 means one period prior to the active period. This is very useful when you want to compare current year balance with previous year balance.
        • A value of 0 means the currently active period – often used to see the transactions for the current period.
        • A value of 1 means one period after the active period
        • Factor – Thousand or Million
        • Format Pattern – A string complying with either Java SimpleDateFormat or DecimalFormat pattern syntax used to override the default presentation format of a date or number type field.

Reference

  1. http://en.wikipedia.org/wiki/Balance_sheet
  2. http://www.adempiere.com/Report_Lines
  3. http://help.sap.com/saphelp_45b/helpdata/en/4c/22756546e611d189470000e829fbbd/content.htm
  4. http://en.wikipedia.org/wiki/Income_statement
  5. http://en.wikipedia.org/wiki/Cash_flow

Summary

We have seen that financial reports are based on the accounting facts. You can make use of report line, report columns and report cube to be able to generate these reports. In case you add new chart of accounts of course it expect you to add such accounts in associated report line set as well (and that is it). I hope this article has increased your overall comfort in using Adempiere’s financial reports. In case you need any professional help, look at our contact us page for contact details.

Tagged with: , , , , , ,
Posted in ADempiere, EagleRP
4 comments on “Financial Reporting in Adempiere
  1. Ansajke says:

    But I am already selected the Amount type,but still I got debit and credit column as same.

  2. Walking Tree says:

    Hi Ansaj,
    At the accounting facts level, the transactions are posted as CR / DB as per the transaction rules. So, we do have source data to be able to get credit and debit amount. By selecting “Credit Only” or “Debit Only” as Amount Type, you should be able to fulfill your business need.

    Regards
    Alok

  3. Ansaj says:

    The debit and credit amount is same in the financial report.How can split the credit and debit amount seperately

  4. Pradeep Lavania says:

    To be explicit about the statement “the balance sheet is expected to respect the rule “Total Asset = Owner’s Equity + Total Liability”.
    this is only correct if you define Owners Equity to mean the Equity accounts PLUS any balance in the sum of all P&L accounts.

    Thanks to Steven for pointing this out.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

We Have Moved Our Blog!

We have moved our blog to our company site. Check out https://walkingtree.tech/index.php/blog for all latest blogs.

Sencha Select Partner Sencha Training Partner
Xamarin Authorized Partner
Recent Publication
%d bloggers like this: