Excel Template
  • 30 Oct 2020
  • 14 Minutes To Read
  • Print
  • Share
  • Dark
    Light

Excel Template

  • Print
  • Share
  • Dark
    Light

Overview

The Excel template that you download for the migration process has different fields corresponding to the information you are trying to import into Mambu. At the bottom of the file, you can see that there are ten different sheets — Clients, Groups, Accounts, Schedules, Transactions, Chart of Accounts, Centres, Branches, Credit Officers, Products — to structure the data you'll enter. This is a quick reference guide that will help you make sure your data is exactly where it should be.

All the custom fields for clients and groups that you've entered in Mambu before downloading this template will be displayed in the corresponding sheets so that you can also fill in that information.

Important
  • The format of the data must be kept intact, please paste special with "only data" and have no Formulas in the cells.)

  • (Date cells should always be in dd.MM.yyyy format. Other formats, such as 3.4.2001 or =DATE("2001,3,4") will not pass validation.)

  • Only .xlsx files are accepted for the validation process. Please make sure the file you upload is not in .xls format.)

  • Input fields have set character limits. Fields that contain EntityIDs have a max of 32 characters; other input fields that usually contain text have a max of 255 characters.)

  • Excel files need to be smaller than 5MB to be able to be imported successfully, if your file is larger please split it in different 5MB tranches.)


Clients

Here you'll find all the fields you need to fill in with your clients data, including any custom fields you've created before.

b0adcf0b-a4dc-4aef-a9fe-6bf479861cc9.jpg

Mandatory fields are in bold text below.

Field Description
Client ID is the Alphanumeric code corresponding to that client's existing ID.
Group ID is the Alphanumeric code that corresponds to the group the client is a member of.
Branch ID is the Alphanumeric code corresponding to the branch the client is assigned to.
Centre ID is the Alphanumeric code corresponding to the centre the client is assigned to.
Credit Officer username is the username of the credit officer who the client will be associated with in Mambu.
Date Joined (DD.MM.YYYY) The day, month and year the client joined your organization. Must follow the format: 2 digits for day, dot, 2 digits for month, dot and 4 digits for year.
First Name the given name.
Middle Name To be filled if the client has middle names.
Last Name the surname.
Date of Birth (DD.MM.YYYY) The day, month and year the client was born. Must follow the format: 2 digits for day, dot, 2 digits for month, dot and 4 digits for year.
Gender (M/F) M-male or F-female.
Address 1, Address 2, City, Zip, State/Province/Region, Country contact information used to locate the client.
Mobile/Cellphone mobile phone number that can be used to contact the client.
Phone The land line phone number that can be used to contact the client.
Notes any additional comment you want to add.
Individual Loan Cycle The number of individual loans the client has already completed.
Group Loan Cycle The number of group loans the client has already completed.
ID Type Examples could be passports, ID card, or any other official document.
ID Number the unique number of the ID document.
ID Authority the authority that issued the document (police, government).
ID Valid Until (DD.MM.YYYY) the expiry date of the document.
Custom Fields the last columns will contain the custom information fields you created before in Mambu. If it’s a Selection field you have to write the same values you chose in the Mambu Settings.

Groups

86589cbe-43ae-4fc9-aacd-f1beb09b2ded.jpg(https://s3.amazonaws.com/mambu-static/images/stories/group-tab.jpg)

Mandatory fields are in bold text below.

Field Description
Group ID Alphanumeric and unique code used to identify the group.
Branch ID Alphanumeric and unique code of the branch that group is assigned to.
Centre ID Alphanumeric code of the centre the group is assigned to.
Credit Officer Username Username of the credit officer the group is assigned to.
Group Name Name which identifies that group.
Date Joined (DD.MM.YYYY) The day, month and year the group joined your organization. Must follow the format: 2 digits for day, dot, 2 digits for month, dot and 4 digits for year.
Address 1, Address 2, City, Zip, State/Province/Region, Country contact information used to locate the group.
Notes any additional comment you want to add.
Group Loan Cycle The number of loans the group has already completed.
Please be Aware
Currently Mambu only supports the import of groups using the Pure Group method. If your groups will be using the Hybrid Group method, you should create the groups manually in Mambu after importing or creating the individual clients.

Loan Accounts

f53f083a-f08c-4d13-a3fa-1281c4116f74.jpg

Mandatory fields are in bold text below.

Field Description
Account ID Alphanumeric code which you can customize and that is unique for each loan account.
Client ID Alphanumeric code of the client the account is for.
Client Type C-Individual Client or G-Group. If you leave this empty, it will be Client by default.
Product ID The alphanumeric code of the loan product the account is associated with. The product ID is defined when you create the products and you can see it in the Loan Product sheet.
Date Applied Date when the client applied for the loan.
Date Approved Date when your organization approved the loan account.
Date Disbursed Date when the loan account was disbursed.
Repayment Start Date The day of the first loan repayment.
# Grace Installments: the number of grace installments for the loan account, if any.
Loan Length (# Installments) the number of repayments of the account. The time unit you chose in the Loan Product will be the criteria to be followed (i.e. months, days, weeks...).
Repayment Frequency: How often the installments will be. If, for instance the repayments occur once per month, you would enter "1". If every 2 weeks, you would enter 2 and so on.
Repayment Period (D/W/M/Y): The unit of time used for the Repayment Frequency. If repayments will be made every 10 days, for instance, then the Repayment Period would be D (D=days, W=weeks, M=months, Y=years).
Principal Interval: If the principal amount should be paid in every installment, you would enter 1. If, for instance, principal would only be paid every 6 installments, enter 6.
Interest Rate The fee in percentage that is charged by your institution to borrowers for the use of a loan amount. When entering this information, you should only include the number. So for an interest rate of 12%, for instance, you should only enter 12.
Loan Amount the original amount of the loan.
Interest and Principal Paid How much of interest and principal has been paid. If nothing has been paid yet, you should enter 0.
Account State the loan state: Active, Closed, Withdrawn, Rejected, Written Off, Pending Approval and Approved.

Loan Schedules

For organizations that are trying to recreate their loan schedule, it is possible to import for fixed loan accounts .

9c365fba-4bf9-42d1-88c7-0e1ea53f015f.png

Mandatory fields are in bold text below.

Field Description
Account ID Alphanumeric code which you can customize and that is unique for each loan account.
Due Date The installment due date;must be in an ascending order for the same loan.
Principal Expected Amount of principal expected for the installment. If nothing is expected yet, you should enter 0.
Interest Expected Amount of interest expected for the installment. If nothing is expected yet, you should enter 0 and the interest will be marked as Grace.
Fees Expected Amount of fees expected for the installment. If nothing is expected yet, you should consider it 0 and the principal will be marked as Grace.
Penalty Expected Amount of penalties expected for the installment. If nothing is expected yet, you should consider it 0.

Note : User cannot import  Dynamic Loan schedules  ; schedules of dynamic loans will always be recalculated based on Mambu logic.

Requirements / Implications

  1. The system will use the interest, fees and penalties specified in the import sheet, even if the product has other interest/fees/penalties settings.

  2. If an imported loan has a schedule specified in the import sheet, don't generate another schedule, but use the one specified by the user.

  3. Mambu updates the account dues/balances accordingly to the imported schedule and the repayments states/paid amounts after the loan is imported:

    • Payment due fees will not be applied on the imported schedules

    • Late fees will not be applied on the schedule, for the installments that are late at import moment


Loan Transactions

For organizations that are trying to recreate their original loan schedules and full transaction history, it is possible to import past transactions.

aa6fadf7-90c6-4511-8d4d-3b22af9894dd.png

Mandatory fields are in bold text below.

Field Description
Account ID Alphanumeric code which you can customize and that is unique for each loan account.
Transaction Type Must be on of: DISBURSEMENT, REPAYMENT, FEE, or PENALTY.
Date Date of the transaction.
Amount Depending on the transaction type, how much principal was paid / disbursed / fee applied / penalty applied.
Notes Any free text notes to be attached to the transaction.

Requirements / Implications

  1. If a disbursement transaction is provided, the disbursement date and loan amount (on the account sheet) are ignored. If there are transactions defined for an account, a Disbursement transaction is mandatory.

  2. If any transaction is provided, the Interest and Principal Paid amount from the loan account are ignored.

  3. If the account is fixed:

    • Fees will be applied on the first unpaid installment. If the whole schedule is paid, an error message will be shown.

    • Penalties will be applied on the first unpaid installment. If the whole schedule is paid, an error will be shown.

  4. Fees: Can be applied regardless of the products' arbitrary fees setup.

  5. Penalties: The product setup (rate and calculation method) are not considered when imported amounts are applied.

  6. If taxes are enabled for the product they should be included in the amounts (interest/penalty/fee).

  7. All transactions must specify a date. A date validation should be performed:

    • Disbursement goes before fee, penalty applied or repayment transactions

    • Transactions should be ordered by their date, in ascending order (older first) - the transactions will be posted on the account on the order from the import table.

    • The transactions must be posted in a sequence for an account (all transactions for account 1, then all transactions for account 2 etc.

    • Creation date will be the approval of import event date.

  8. The total number of imported transactions will be shown in the Import Events table.

  9. No accounting is logged for imported transactions.


Deposit Accounts

9ac930bb-9bd7-439d-b67f-a92f64f15f56.jpg

Mandatory fields are in bold text below.

Field Description
Account ID Alphanumeric code which you can customize and that is unique for each client.
Client ID Alphanumeric code of the client the deposit account is for.
Product ID The alphanumeric code of the deposit product the account is associated with. The product ID is defined when you create the products and you can see the ID in the Deposit Product sheet.
Date Applied When the account was created.
Date Approved When the account was approved and activated. If accounts are imported with current balances, these balances will be reflected as deposit transactions with entry date same as the "Date Approved".
Overdraft Interest Rate The interest rate charged on overdraft accounts. Can be zero.
Overdraft Limit The maximum amount a client can withdraw from the overdraft account.
Overdraft Amount Due How much is due at the date of the import.
Overdraft Interest Due The amount of interest due at the point of the import.
Overdraft Fees Due The amount of fees due at the moment of the import.
Current Balance Account balance* at the moment of the import. This balance will be imported as a Deposit transaction with Entry Date same as the "Date Approved" indicated in the import file.


*Please note, if you pay interest to deposit accounts and at the moment of the import there is an interest amount accrued and not applied yet, you should include this in the current balance. After the import, Mambu will start accruing interest (if applicable) on the balance that has been imported, based on the product settings)
Notes Any additional comment you want to add.
Please Note
The dates for last interest calculation, latest interest storage and lates account appraisal will be set as of the point of the migration event.

Chart of Accounts

35b135cd-a2ad-446b-a238-31b59cf1f847.jpg

Mandatory fields are in bold text below.

Field Description
GL Code The number used to identify the account in the General Ledger.
Account Name The name so that you can identify the account.
Date The date AS-OF the GL account's balance in the Balance Column. This is ideally the date of the client's data cut off, ensuring a clean accounting record in Mambu.
Type (A/L/I/E/Q) Select if the account is an Asset, Liability, Income, Expense or Equity.
Usage (D/H) The category of the account: Detail or Header.
Balance Balance of existing GL accounts at the moment of the import.

Balances must follow the correct sign according to the type of account, as follows:

- Asset: Debit (+) / Credit (-)

- Expense: Debit (+) / Credit (-)

- Liability: Debit (-) / Credit (+)

- Equity: Debit (-) / Credit (+)

- Income: Debit (-) / Credit (+)
Notes Any additional comment you want to add.
Please Note
If balances are added for the GL sheet, debit amount and credit amount must be equal.

Centres

adc0dc14-444f-427a-9580-0df3e641dd81.png

Mandatory fields are in bold text below.

Field Description
Centre ID Alphanumeric code which you can customize and that is unique for each centre.
Branch ID Alphanumeric code of the branch the centre is associated to.
Name The centre's name.
Meeting Day The day of the week when the clients who are associated to this centre meet with the credit officer. Use the initials of the appropriate day as they are shown. So for instance, M for Monday, T for Tuesday and so on.
Address 1, address 2, city, zip, state/province/region, country Contact information of the centre.
Notes Any comments you want to have associated to the centre.

Data (for reference only)

The following sheets contain information to be used only as a reference when you're filling the other sheets. So, for instance, when you're entering information in the Clients' or Groups' sheets, you will need to open the Branches' sheet to see which is the branch's ID that the clients are associated with.

Branches Data

This information corresponds to the data you've entered before about your organization's branches.

The branches IDs are automatically generated when you create a new branch in Mambu.

49a241d3-c51b-49db-bd9c-46e8c0f1f548.jpg

Credit Officers

The information in the Credit Officers sheet corresponds to the data you entered in the system about them.

The Credit Officers IDs are automatically generated when you download the template so that you can use it as a reference.

3394b308-0e59-4e2c-9c3c-cef79b47bc30.jpg

Loan Products

The information displayed in this sheet corresponds to the data you entered in the system when creating your products.

Loan products' IDs are not automatically generated, so you need to define the ID when creating your loan products.

940392d6-36ef-4a22-9cfe-823385050066.jpg

Deposit Products

Just like for the loan products, the information in this sheet corresponds to the one you entered before in the system.

Savings products' ID number is not generated automatically, so you also need to define it when creating your savings products.

e9c2336b-f013-44d9-b708-4b2991baa582.jpg

ID Associations

So that the data migration is completely successful, the information in the ten sheets - Clients, Groups, Loan and Savings Accounts, Chart of Accounts, Branches, Credit Officers, Centres and Loan and Savings Products - in the template has to match. You can make sure it does by using the ID numbers, which ensure that the clients and groups will be assigned to the appropriate branch and credit officers in the system.

In the Clients sheet:

  • The Group ID (for clients who are members of a group), the Branch ID, the Centre ID and the Credit Officer ID need to correspond to Group, Branch, Centre and Credit Officers IDs in the other sheets - Groups, Branches, Centres, Credit Officers.

In the Groups sheet:

  • The Branch, Centre, Credit Officer ID you enter here has to be the same as the one displayed in the Branches, Centres and Credit Officers sheets.

In the Loan Accounts sheet:

  • The Client ID and the Product ID must match with the fields in the Clients and Loan Products sheets.

In the Deposit Accounts sheet:

  • Just like for the loan accounts sheet, the Client ID and the Product ID need to match the fields in the Clients and Deposit Products sheets.

In the Centres Sheet:

  • The Branch ID needs to match the ID in the branches' sheet.
Please Note
Neither clients nor credit officers need to be assigned to the same branch their group is assigned to.

Custom Fields

If you added custom fields to clients, groups, users or accounts, you will see that there will be a column for each of them in the excel template.

For any of the Custom Field types Free Text, Number, or Selection you can just enter the information as it should be displayed in Mambu.

Exception
The only exception is for Checkbox custom fields in which case you will have to enter the word True if the box should be checked and False if it shouldn't.

Was This Article Helpful?