...
This means each journal entry batch must have a batch Id (so all individual entries under it can be identified for easy reversal)
4 Accounting rules for Loans
4.1 Cash based accounting Scenario:
...
This is must for regulatory requirements. We should have the option to turning the same “on” and “off” from the user interface
*This section needs to be revisited in detail
...
. We should have the option to turning the same “on” and “off” from the user interface
*This section needs to be revisited in detail
5 Accounting rules for Loans
4.1 Cash based accounting Scenario:
4.1.1 COA Snippet
HEAD | Placeholder |
Assets | Cash |
| Loan Portfolio | |
Income
| Interest on loans |
Income from fees | |
Income from Penalties | |
Expenses | Losses Written Off |
4.1.2 Posting Rules
Event | Debit | Credit |
Disbursal | Loan Portfolio | Cash |
Principal repayment | Cash | Loan Portfolio |
Interest repayment | Cash | Interest on loans |
Principal Write off | Losses Written Off | Loan Portfolio |
Fees payment | Cash | Income from fees |
Penalty payment | Cash | Income from Penalties |
6 Closure Dates
Allow setting closure dates at branch level after which accounting transactions cannot be posted.
...
7 Reports
The three basic reports
- Balance sheet
- Profit and Loss Statement
- Trial balance
...
Debit | Credit | |
|---|---|---|
Asset | Increase | Decrease |
Liability | Decrease | Increase |
Equity | Decrease | Increase |
Income | Decrease | Increase |
Expenditure | Increase | Decrease |
...
7.1 Balance Sheet
A balance sheet summarizes an organization or individual's assets, equity and liabilities at a specific point in time.
Formula : Assets = Liabilities + Equity
...
7.1.1 Sample Query
The below query is for assets, the queries for liabilities and equity would be same but for the calculation of balance (would be credit - debit refer verbiage in section 6)
...
select credits.glcode as 'glcode', credits.name as 'name', (ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0)) as 'balance'
from
(select acc_coa.gl_code as 'glcode',name,sum(amount) as 'debitamount'
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type='DEBIT'
and acc_coa.category in ('ASSET')
group by name
order by glcode) debits
RIGHT OUTER JOIN
(select acc_coa.gl_code as 'glcode',name,sum(amount) as 'creditamount'
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type='CREDIT'
and acc_coa.category in ('ASSET')
group by name
order by glcode) credits
on debits.name=credits.name;
...
7.2 Income Statement (P & L statement)
Is a company's financial statement that indicates how the revenue (money received from the sale of products and services before expenses are taken out) is transformed into the net income (the result after all revenues and expenses have been accounted for, also known as Net Profit ).
It displays the revenues recognized for a specific period, and the cost and expenses charged against these revenues
...
Formula: Net Income = Total Income – Total Expenses
...
7.2.1 Sample Query
Below query if for Income, the query for expenses would be similar (save for calculation of balance, refer verbiage in section 6)
...
select credits.glcode as 'glcode', credits.name as 'name', (ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0)) as 'balance'
from
(select acc_coa.gl_code as 'glcode',name,sum(amount) as 'debitamount'
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type='DEBIT'
and acc_coa.category in ('INCOME')
and acc_journal_entry.entry_date between '2012-11-01' and '2012-11-31'
group by name
order by glcode) debits
RIGHT OUTER JOIN
(select acc_coa.gl_code,name,sum(amount) as 'creditamount'
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type='CREDIT'
and acc_coa.category in ('INCOME')
and acc_journal_entry.entry_date between '2012-11-01' and '2012-11-31'
group by name
order by glcode) credits
on debits.name=credits.name)as fullouterjoinresult
order by glcode;
...
7.3 Trial Balance
A trial balance is a list of all the General ledger accounts (both revenue and capital) contained in the ledger of a business.
...
The debit balance values will be listed in the debit column of the trial balance and the credit value balance will be listed in the credit column.
...
7.3.1 Sample Query
This is the complete query for the report
select *
from
(select debits.glcode as 'glcode', debits.name as 'name',IF(debits.type = 'ASSET' or debits.type = 'EXPENSES', ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0),'') as 'debit', IF(debits.type = 'INCOME' or debits.type = 'EQUITY' or debits.type = 'LIABILITIES', ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0),'') as 'credit'
from
(select acc_coa.gl_code as 'glcode',name,sum(amount) as 'debitamount',acc_coa.type as 'type'
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type='DEBIT'
and acc_journal_entry.entry_date between '2012-11-01' and '2012-11-31'
group by name
order by glcode) debits
LEFT OUTER JOIN
(select acc_coa.gl_code,name,sum(amount) as 'creditamount',acc_coa.type as 'type'
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type='CREDIT'
and acc_journal_entry.entry_date between '2012-11-01' and '2012-11-31'
group by name
order by glcode) credits
on debits.name=credits.name
union
select credits.glcode as 'glcode', credits.name as 'name',IF(credits.type = 'ASSET' or credits.type = 'EXPENSES', ifnull(debits.debitamount,0)-ifnull(credits.creditamount,0),'') as 'debit', IF(credits.type = 'INCOME' or credits.type = 'EQUITY' or credits.type = 'LIABILITIES', ifnull(credits.creditamount,0)-ifnull(debits.debitamount,0),'') as 'credit'
from
(select acc_coa.gl_code as 'glcode',name,sum(amount) as 'debitamount',acc_coa.type as 'type'
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type='DEBIT'
and acc_journal_entry.entry_date between '2012-11-01' and '2012-11-31'
group by name
order by glcode) debits
RIGHT OUTER JOIN
(select acc_coa.gl_code,name,sum(amount) as 'creditamount',acc_coa.type as 'type'
from acc_journal_entry,acc_coa
where acc_coa.id = acc_journal_entry.coa_id
and acc_journal_entry.type='CREDIT'
and acc_journal_entry.entry_date between '2012-11-01' and '2012-11-31'
group by name
order by glcode) credits
on debits.name=credits.name) as fullouterjoinresult
order by glcode
...
8 Database changes
...
8.1 Chart of Accounts Table (acc_coa)
Table stores the “Chart of Accounts” for an organization
| Column Name | Data Type | Description |
|---|---|---|
| id | int | |
| gl_code | varchar(45) | |
| category | varchar(45) | Whether the account falls under “asset”, “income”, “expense”, “liability” or “equity” |
| parent_Id | int | References “id” of table “m_acc_coa”. For enabling a hierarchical COA |
| name | varchar(45) | |
| type | varchar(45) | Either “header” or “detail” account. Header accounts will not have transactions logged against them |
| description | varchar(500) | |
| disabled | Boolean | A Flag indicating if the account has been disabled (not deleted). No more manual entries can be posted against a disabled Account |
| manual_entries_allowed | Boolean | Flag indicates if manual Journal entries can be passed against this account |
...
8.2 Modifications to Loan Product (“m_product_loan”)
Table stores the “Chart of Accounts” for an organization
| Column Name | Data Type | Description |
|---|---|---|
| accounting_rule | varchar(45) | The type of accounting required. Either “none”, “cash” or “accrual” *Selecting none would disable posting of all accounting transactions |
...
8.3 Mapping Loan product to Chart of accounts (acc_product_coa_mapping)
The table maps the GL codes used by a particular loan/saving product.
| Column Name | Data Type | Description |
|---|---|---|
id | int |
|
acc_id | int | Foreign Key to acc_coa |
product_id | int | reference to either Loan or Saving product |
product_type | varchar(45) | "loan" or "saving" |
financial_account_type | varchar(45) | Name of a financial Account Type Ex: " Cash", "Interest on Loan" |
...
8.4 Journal Entries (acc_journal_entry)
Table stores all journal entries generated in the system (either created automatically by the system and those created manually)
| Column Name | Data Type | Description |
|---|---|---|
id |
| Primary key for the entry |
| coa_id | int | Foreign key to the Chart of Accounts Table |
entry_date | DATE TIME | The entry date for the journal entry (can be backdated to any date etc) |
batch_id | varchar(45) | Batch key for the entry, could be a UUID generated by Java.Used to link together a set of related Journal entries |
type | varchar(45) | Whether the entry is a debit or a credit |
amount | Float |
|
description | varchar(500) | user notes for manual entries |
branch_id |
| Branch with which this entry is associated |
reversal_id |
| Foreign key to another journal entry which reversed this journal entry (id column of acc_journal_entry) |
is_reversed | Boolean | Flag indicating if the Journal entry is reversed (an entry can be reversed only once) |
entity_type | varchar(45) | “Loan” or “Savings” account for system generated entries. Null for manual entries |
entity_id | int | Nullable foreign key to a loan or savings account |
system_generated | Boolean | Flag indicating whether the entry was logged automatically by the system |
created_date | Date | Audit feild to indicated the actual date on which the entry was posted into the system |
created_by | varchar(45) | Foreign key to user who created the Entry (for manual entries) |
...
8.5 Branch accounts closure (acc_closure)
Table stores details of all branches and dates for which all Journal entries have been closed
...