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.

...

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 headers

...

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

Question: Would 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 (Ex: Loan account on which a disbursal occured)
• 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 visualisation 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 mockup

3.1        Automated Journal Entries

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

...

They may only be altered with events generating from the system (Ex: 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

...

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

4.1        Cash based accounting Scenario:

4.1.1        COA Snippet

4.1.2        Posting Rules

4.2        Accrual Based accounting Scenario

Would only involve posting to three new accounts (Receivables "incomeType") 

4.2.1        COA Snippet

4.2.2        Posting Rules

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 sample loan shown here follows a monthly repayment cycle

*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

...

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 section needs to be revisited in detail

5         Closure Dates

Allow setting closure dates at branch level after which accounting transactions cannot be posted.

6         Reports

Only The three basic reports (using stretchy reporting)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 & gains

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

Image Added

6.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;

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

Image Added

6.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 *
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;

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

Image Added

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

7        Database changes

...