Tally Accounting Integration FS

Current Owners:

Kay Chau, Gigi Gatti

Status (Draft, In Review, Stable, Approved)

Approved

Last Updated

9/7/2010


Functional Specifications: MIFOS Interface to Tally

Configuration required in Tally for the interface to work:

  1. Set up branches as cost centers in Tally: Branch name in Tally should be the same in MIFOS
  2. Synchronize ledger/GL accounts of MIFOS and Tally: Alias of ledger account in Tally should be the same as GL account code in MIFOS
  3. Set up automatic numbering of vouchers in Tally

MIFOS/Pentaho Functional Requirements:

  1. Provide an option to extract date or range of dates to export. This date is the date that the GL account in MIFOS was generated, not necessarily the same as transaction date. This date should be a system-generated, un-editable date.
  2. (P2) Ability to warn user if the same export activity was already done. User should be allowed to re-export upon confirmation to override the warning message.
  3. Assign a filename to the XML file following a naming convention that will allow the users to identify the export files by name.
    1. Mifos Accounting Export DD-MM-YYYY to DD-MM-YYYY.xml
  4. Generate the XML file (format provided by Tally) with the mapping of fields between MIFOS and Tally below
    1. Each branch as a cost center, will have multiple vouchers per day.  These are aggregated amounts for each GL account for each type of voucher.
    2. For example, all disbursements for a particular date for a branch will be summed up into 2 GL accounts.  One voucher for this will be written in the XML.
    3. Same for all payments, and then for everything else that's not a payment or a disbursement.
  5. Write the XML file to a location that is accessible by the user.
  6. Generate the GL Interface Report (see format in the GL Reports worksheet).

Mifos-Tally Mapping

MIFOS Field

Tally Voucher Field

XML Tag

 

Voucher number (generated by Tally - requires this option to be activated at setup)

 

 

Voucher type - either "RECEIPT" or "PAYMENT" or "JOURNAL"

VOUCHERTYPENAME

Import date

None?

 

Transaction date

Voucher date (Effective date)

DATE

Branch name

Cost center name

NAME under COSTCENTREALLOCATIONS.LIST

GL Account code

Ledger alias

LEDGERNAME

Credit amount (aggregate amount)

Credit amount

AMOUNT (will be positive), ISDEEMEDPOSITIVE = NO

Debit amount (aggregate amount)

Debit amount

AMOUNT (will be negative), ISDEEMEDPOSITIVE = YES

 

Narration field  - MIFOS interface file name(see 3a above)

NARRATION

Note: Voucher type - RECEIPT (collections), PAYMENT (disbursements), JOURNAL (all others).  Only one file needs to be generated.

Tally XML Notes and Rules

  1. For each day of transactions, there should be a RECEIPT, PAYMENT, and JOURNAL voucher for each branch.
    1. So for example, if you were to import one day's worth of transactions today for 1 branch, there might be 3 total vouchers (1 RECEIPT, 1 PAYMENT, 1 JOURNAL).  If there were 2 branches, then there'd be 6.  If it was for 2 days of transactions for 2 branches, there could be max of 12 vouchers.  Only have a voucher if transactions for that voucher type exist.
  2. All Credits will have a positive amount, and should have an XML tag of ISDEEMEDPOSITIVE of NO.  Debits will be negative, and ISDEEMEDPOSITIVE of YES. 
  3. Only one date field will be captured = TRANSACTION date
  4. Associate cost center (branch) with every entry in the voucher in case the MFI has set up cost center as applicable for that ledger.
  5. PAYMENT vouchers have extra rules## DEBIT account (most likely Loan Account for disbursals) must be listed FIRST in ALLLEDGERENTRIES list

Process Flow

See attached - MIFOS-Tally Process Flow.docx 

Notes on the process flow

  1. If the user wants to re-do the interface, the imported voucher should be CANCELED In Tally. Using the CANCEL VOUCHER option will nullify entries previously posted but will not renumber the vouchers. If DELETE VOUCHER option is used, the voucher numbers will be renumbered (this is not recommended).
  2. It is recommended that the user maintain a manual log of dates that were successfully imported.

Other Processes / Requirements

Error correction process:

  1. In MIFOS: User specifies date/range of date to export. If these date/s have been exported, MIFOS will warn the user and ask for an override before it regenerates the XML file or user should check this manually against a manually maintained interface checklist (I can design this).
  2. In Tally: Use the CANCEL VOUCHER option to nullify entries previously posted in the previous export made. Voucher numbering will not move/regenerate if this option is used. If DELETE VOUCHER option is used, the voucher numbers will be regenerated (this is not recommended).
  3. Redo the MIFOS interface process.

Bank Recon process:

  1. COLLECTIONS/DEPOSITS: In MIFOS, all collections are done against a generic cash account. The branch should send the deposit slips to HO (if centralized) or make their own appropriate entries after the collection has been deposited. Deposits should be recorded in Tally using a CONTRA voucher. CONTRA vouchers are used to record transfer of cash to bank accounts and vice versa. With this method, bank reconciliation can be carried out in Tally, using the normal process flow of bank reconciliation.

When bank deposit is made, the entry in the CONTRA voucher in Tally will be:

Dr. Bank (whichever bank is appropriate)
Cr. Cash on Hand

  1. DISBURSEMENTS: When a check is written, the entry in the CONTRA voucher in Tally will be:

Dr. Cash on Hand
Cr. Bank (whichever bank is appropriate) - put in narration field the check number

When the bank statement is received, user will match the bank statement with the entries in the Bank account and perform regular bank recon procedures.

Report Specifications

1 new report needs to be created and 1 existing report needs to be updated.

Report Name

Description

Format

Template

GL Ledger Summary

Existing report that summarizes each account head and what's been entered for that transaction date

BIRT

GL Ledger Summary Report.xlsx 

GL Interface Report

Report to help match what's been exported from Mifos for import to Tally

Pentaho

GL Interface Report.xlsx 

Estimates

https://docs.google.com/document/edit?id=13_bad5nh5hf1IqR4jKrOQizVYmZKwUpo4-a4vbcoO6g&pli=1&authkey=CPqMmvcE#