Accounting Module Functionality and Expectations

1 What Accounting rules?

User must be able to select the applicable accounting rule as "none", "cash" or "accrual" at a product level.

The user is expected at the time of product definition to select the “type” of accounting for the product and select his appropriate General Ledger (GL) “Detail account” for the placeholders Mifos X accounting module provides

Page 10 of attached accounting summary document gives useful basic distinction between cash and accrual accounting.

2 The COA

Continue having the same design with five main account "Types":

Assets
Liabilities
Income
Expenditure
Equity

Allow adding new accounts of any one of these types from the UI.

New accounts can be either "header" or "detail" accounts to make things simpler for a user. "Header" accounts would be used purely for grouping together

related accounts etc. An organization would not log journal entries against the same (Journal entry screens would not list "Header" accounts).

Question: Would you be interested in hearing what COA nirantara and GK accountants use (or any other MFI in community)? On document pages 23, 24 is content about the 'classification of transactions' through chart of accounts and a sample chart of accounts for MFIs. How does nirantara and gk COA differ and why would be good to know.

2.1 Scope

For simplicity (for the first cut) have COA at organization level and run accounting reports at the branch level

So system should associate all journal entries to branch which owns the entity that caused the entry (Example: Loan account on which a disbursal occurred)
All user defined journal entries should also be associated with a branch

3 Journal Entries

The 'accounting cycle' image provided on page 22 of attached accounting summary document provides a good visualization of steps involved from 'economic activity step (transaction)' to closing books for a given cycle period (weekly/monthly/quarterly/yearly)

The Journal entries view would look similar to the below mock up

3.1 Automated Journal Entries

Mifos X to automatically log (log each transaction as a separate journal entry....no need of daily consolidation etc.)

Disbursals
Repayments
Rescheduling
Write-Off
Adjustments

These journal entries should not have permissions to be altered from the accounting UI (like manually created journal entries which can be reversed etc.)

They should link back to the entity that generated them (the loan transaction etc. so that a user can analyse the same).

They may only be altered with events generating from the system (Example: a user reverts a loan disbursal etc.)

3.2 Manual Journal Entries

For a manual entry, allow "m" debit and "n" credit entries for a "Journal Entry entered by a user" as long as the sums of all debits and credits match.

Allow journal entry reversal (system automatically logs reversal entries of corresponding debits and credits).

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:

4.1.1 COA Snippet

HEAD	Placeholder
Assets	Cash
	Loan Portfolio
	Transfers in Suspense
Income	Interest on loans
	Income from fees
	Income from Penalties
Expenses	Losses Written Off
Liability	Loan Over-payments

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
Initiate Transfer (From Branch A to Branch B)	Transfers in Suspense	Loan Portfolio
Accept Transfer (In Branch B)	Loan Portfolio	Transfers in Suspense
Over payment	Cash	Loan Over-payments
Refund (of Overpaid amount)	Loan Over-payments	Cash

4.2 Accrual Based accounting Scenario

Would only involve posting to three new accounts (Receivables "Income Type").

4.2.1 COA Snippet

HEAD	Placeholder
Assets	Cash
	Loan Portfolio
	Receivables Interest
	Receivables Fees
	Receivables Penalties
INCOME	Interest on loans
	Income from fees
	Income from Penalties
Expenses	Losses Written Off

4.2.2 Posting Rules

Event	Debit	Credit
Disbursal	Loan Portfolio	Cash
Interest Applied	Receivables Interest	Interest on loans
Fee Applied	Receivables fee	Income from fees
Penalty Applied	Receivables penalties	Income from Penalties
Principal repayment	Cash	Loan Portfolio
Interest repayment	Cash	Receivables Interest
Principal Write off	Losses Written Off	Loan Portfolio
Fees payment	Cash	Receivables fee
Penalty payment	Cash	Receivables penalties
Interest write off	Losses Written Off	Receivables Interest
Fee write off	Losses Written Off	Receivables fee
Penalty write off	Losses Written Off	Receivables penalties

4.2.3 Examples

4.2.3.1 Flat Interest Type

When Interest Type is flat, prepayment does not write off any interest. The entire interest is recognized and “receivable” as soon as the loan is disbursed.

The entire interest (for complete duration of the loan) is recognized immediately after disbursal.

There are quite a few conflicting views on if this special treatment is required. However, leaving it in here for now till we get feedback from a few accountants.

The sample loan shown here follows a monthly repayment cycle.

Event	Debit	Credit
Disbursal (7-10-2012)	Portfolio Control Account (1)	Fund (1)
Disbursal (7-10-2012)	Interest Receivable (2)	Income from interest (2)
Apply Fee (7-10-2012)	Fees Receivable(3)	Income from fees (3)
Principal, Interest and Fee Repayment(1) (7-11-2012)	Fund (4)	Interest Receivable (4)
		Portfolio Control Account (4)
		Fees Receivable(4)
Prepayment for next n-1 Installments (excluding part of last principal) (8-11-2012)	Fund (5)	Interest Receivable (5)
	Fund (5)	Portfolio Control Account(5)
Prepayment of remaining Principal (17-11-2012)	Fund (6)	Portfolio Control Account(6)

*Note: A Batch Id is shown in Brackets

4.2.3.1 Declining Interest Type

In the case of Declining Balance, we have loans involving interest recalculation. The interest is accrued at the date of each repayment.

The system needs to have a job for posting the same

Event	Debit	Credit
Disbursal (7-10-2012)	Portfolio Control Account (1)	Fund (1)
Automated System Event (when an Interest repayment becomes due) (7-11-2012)	Interest Receivable (2)	Income from interest (2)
Principal, Interest Repayment(1st Installment) (7-11-2012)	Fund (3)	Interest Receivable (3)
Principal, Interest Repayment(1st Installment) (7-11-2012)	Fund (3)	Portfolio Control Account (3)
Prepayment for remaining Installment’s (no interest accrued) (7-11-2012)	Fund (4)	Portfolio Control Account(4)

Note: If any interest is accrued till date while doing a prepayment, the application should automatically log a system generated journal entry

for recognizing the same while doing a prepayment.

4.2.4 Month end and year End Closing

We should also have an option for Month end closing and year end closing at a branch level (basically a bulk “apply accrued interest” till date job for all loans of declining Interest type)

This is must for regulatory requirements. We should have the option of turning the same “on” and “off” from the user interface

*This section needs to be revisited in detail

5 Accounting rules for Savings

5.1 COA Snippet

HEAD	Placeholder
Assets	Cash
Income	Income from fees
Income	Income from Penalties
Expenses	Interest for Savings
Liability	Savings Control
	Transfers in Suspense

5.2 Posting Rules

Event	Debit	Credit
Deposit	Cash	Savings Control
Withdrawal	Savings Control	Cash
Interest posting	Interest for Savings	Savings Control
Fees payment	Savings Control	Income from fees
Penalty payment	Savings Control	Income from Penalties
Initiate Transfer (From Branch A to Branch B)	Savings Control	Transfers in Suspense
Accept Transfer (In Branch B)	Transfers in Suspense	Savings Control

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

Some context for reporting

“Golden” rules of accounting:

Real account: Debit what comes in and credit what goes out.
Personal account: Debit who receives and Credit who gives.
Nominal account: Debit all expenses & losses and Credit all incomes and gains

	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 debits.glcode as 'glcode', debits.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
LEFT 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 ('ASSET')
group by name) credits
on debits.name=credits.name

union

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)

Income 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 is for Income, the query for expenses would be similar (save for calculation of balance, refer verbiage in section 6)

select *
from
(
select debits.glcode as 'glcode', debits.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
LEFT 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) credits
on debits.name=credits.name

union

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.

This list will contain the name of the nominal ledger account and the value of that nominal ledger account.The value of the nominal ledger will hold either a debit balance value or a credit balance value.

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 or 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.

Column Name	Data Type	Description
Id	Int
closing_date	DATE
office_id	Int	Id of the office for which the closure date is applicable
created_by	varchar(45)
created_date	DATE
last_updated_date	DATE

Appendix

Appendix 1 - Links to material on previous mifos accounting

Browser not supported