Upgrade or not to Upgrade?
Should you or should you not upgrade to Version 8? That is the question many of you will ask yourself. The answer is not straightforward. There are many issues to consider.
The best experiences we have, is with organizations that have taken well care of their data in 7.10, that have dedicated IT staff and who invest in training. Let’s start with the existing data in 7.10. You already know that we oblige you to send your 7.10 database to us for conversion to version 8 if you want to upgrade. That is not for no reason. We have seen many irregularities with especially FoxPro databases. We receive databases that have redundant tables, that should not be there, databases that have tables with the contents of another table, tables that have corrupted records etc. When we upgrade these tables to version 8 and SQL Server, these issues become visible and we correct them. Also data-entry errors come to the surface during the conversion. SQL Server does not recognize dates before 1900 so any birthday, application date or whatever data that was erroneously entered before 1900 will get an incorrect value in SQL Server and generate errors.
After the conversion, we always do the following checks:
- Is the number of clients the same in both versions, 7.10 and 8?
- Are the number of savings accounts and total savings balances the same?
- Are the number of outstanding loans and the total loan balances the same? Do the Arrears reports match?
- Do the Trial Balances show the same account balances? Do the TBs balance?
If there are any differences found, we resolve them. Often it is because of referential integrity issues. For instance a client category has been introduced once and some clients were give this category code – say 03 – but later this category was removed from the menu Support Files, but there are still clients in the database with this code. This can be the cause of differences between the client reports in 7.10 and 8. We have seen that often version 8 gives more correct figures than version 7.10!
Better results are also obtained if the organization has dedicated IT staff. In particular if this person has decent knowledge and experience with SQL Server and Windows Networking. If a dedicated person is not available in-house, we recommend finding an external person to assist, say once a week, with backups, connectivity issues and other IT related problems. Crystal Clear Software also provides a 2-day training on SQL Server issues related to Loan Performer.
Training is the 3rd element that defines a successful version 8 implementation. You may know your way in version 7.10 but version 8 is a different story. Without training you will not get the most out of the product and you may lose essential information that later may haunt you. Crystal Clear Software provides a 6-day training for users who already know Loan Performer 7.10.
Finally, how do 7.10 users find Loan Performer 8? There is one common issue that many have a problem with: the speed. As the architecture is different, this has an effect on the speed. There are some precautions that you can take: for instance upgrade all workstations to 4 GB of RAM and put in place a good server. As lookup tables take a long time to load, it is better to key in the client code, savings or loan account number than to retrieve and search for it. Especially for large database this is a must. However most agree that Loan Performer 8 is a superior product with many more features and possibilities.
Multi-Currency in Version 8
The Multi-Currency feature was implemented in Loan Performer 8 to allow microfinance Institutions to operate in a variety of foreign currencies in addition to the local currency or ‘Base Currency’. In general, Management Information Systems (MIS) help MFIs and other financial entities to improve on data quality, efficiency and reliability of data as well as help institutions to communicate their successes, identify challenges, manage risks and develop their relationships with donors and other stakeholders. The flexibility for end-users to report to their stakeholders in multiple currencies is what makes this feature one of the best innovations to Loan Performer version 8. It’s now possible for financial institutions to report to the Central Bank in a local currency while reporting to the donors in a foreign currency while using the same MIS.
How to setup a Multi-Currency Database in LPF:
In order to setup a Multi- Currency database, the following are important to note:
- One currency has to be defined as the Base Currency. This is the yard stick against which other currencies are measured.
- Exchange rates have to be entered as "1 foreign currency equals … local currency" (and not the other way round).
- Each savings/loan product is defined by it’s currency.
- The GL accounts of each savings/loan product must be of the same currency as the product itself. You cannot save a USD Savings product with a GL Savings account in Ghanian Cedis!
- You need cash and bank accounts in foreign currency as well.
- If you have loans in a foreign currency you must have the loan revolving fund account in the same currency.
- A Currency Revaluation GL Account must be defined at 'System/Configuration/Forex Transactions'. This has to be in local currency. This is the account where currency gains or losses are booked because of changes in the exchange rate.
- User has the flexibility to have the GL Account revalued or not as defined at menu "Chart of Accounts".
- Exchange rates must be entered at menu "Support Files" BEFORE a foreign currency transaction is entered. You cannot enter a transaction in a foreign currency and then, afterwards change the exchange rate that should have been applicable.
- Sub-ledger reports can be filtered according to the product currency; financial reports can be produced in any currency for which you have defined exchange rates.
Photo: Extract of the Breakdown report, each account having it's own currency.
One of the most difficult things in multi-currency is the concept of ‘currency revalution’. Suppose you have a database in local currency and you buy an asset with foreign currency. Say a vehicle at 10 FC (foreign currency). At the time of buying the exchange rate was 1 FC = 2,000 LC (local currency). So you book: GL Account Vehicles debit with 10 FC and GL Account Bank credit with the same amount. Behind the scenes LPF books this as 20,000 LC (10 FC x 2,000 LC/FC).
Suppose next month the exchange rate changes and we get 1 FC = 2,500 LC. The value of our vehicle does not change in FC - it is still worth 10 FC - but in local currency it now is worth 25,000 LC (10 FC x 2,500 LC). LPF does this for you when you enter the new rate at "Support Files/Exchange Rates". When saving the new rate, LPF checks which are the GL accounts held in this currency, what are their balances and multiplies the FC balances with new divided by the old exchange rate (20,000 x 2,500/2,000). It then books: GL Account Vehicles debit 5,000 LC and GL Account Currency Gains and Losses (the Currency Revaluation account) credit 5,000 LC. This is an income, though no money has been received!
If you now run the Trial Balance, you will see the vehicles account with a balance of 10 FC if run in foreign currency or 25,000 if run in local currency.
Approve Manual Transactions
One of our clients - Crédit Populaire de Centrafrique – asked us to make it possible to approve manually keyed in transactions. Transactions that are not approved should not be visible in the financial reports. We have added this feature in Loan Performer 8.14. How does it work?
Before you start, make sure you have access to the accounting module and specifically to the ‘Approve Manual Transactions’ menu in the Accounts module and at ‘Accounting’ at System\Configuration. If you cannot see them please check with your System Administrator. Also enter some manual transactions at menu 'Accounts/GL Transactions/Add Transaction'.
Now, go to System/Configuration/Accounting and check the option ‘Exclude manual Transactions that are not approved’ .
Now run the Breakdown report or any other financial report and you will not see the manually entered transactions.
The next step is to approve the manual transaction(s). Go to menu 'Accounts/Approve Manual Transactions' and make sure to check the transactions you want to show up in your final reports. Then click ‘Save’.
Now all approved manual transactions will be also be part of your Financial Accounts reports.
In Loan Performer, a Loan Revolving Loan Fund represents the source of money from which loans are given to clients. These sources can be Donors, Borrowed Amounts or Internal funds. Your organization may use client's shares and/or savings as loan funds. If this is the case, you have to set the percentage of shares or savings balances that are available for lending.
Before you can start issuing loans to clients in Loan Performer you have to set up the loan fund. This is done at System\Configuration\Funds. Here you will be able to define the parameters for each individual fund and these include, Name of fund, Donors' Name, Starting date, Closing date, GL account, checks on the availability of funds at loan application or disbursement etc. If no checks on availability of funds are needed, you can just enter the Fund without adding any entries to it. The fund is there but has a zero balance.
If you want Loan Performer to check the availability of funds, you have to key in the fund deposits. This is done by ticking the “Book to GL” check box on the first page and entering the deposits on the second page (the ‘Contributions’ tab). The extent of the loan fund usage can then be monitored with a special report in Loan Performer called the Fund Utilization report at menu ‘Loans\Portfolio Reports\Analysis Reports\Fund Utilization Report’.
Next you have to define at what point Loan Performer will check for the availability of funds. This can be done at the time of “Loan Application”, “Loan Disbursement” or “Never”. By default, the option "Never" is chosen. This means that Loan Performer will process all loan applications and loan disbursements without checking whether there are adequate funds. Choosing any of the other options will mean that Loan Performer will check the respective fund at the entry of application or disbursement or both (depending on what option is marked) and will allow you to proceed or will reject the transaction if the funds are not enough. The calculation that is done is as follows: funds available at application/disbursement minus outstanding principal should be more than the loan amount. If it is not, you will not be able to apply for or disburse the loan. Critical is the date of application/disbursement and of the balance of the fund at that date. Many people make mistakes with that.
In Loan Performer, by default, you will not be able to carry out loan transactions unless you have opened up a loan revolving fund. However it is also possible to use Loan Performer without making contributions to the loan revolving fund. This is useful in case of credit unions, where the source of funds is of no importance. In this case to allow loan entries, you should key in a fictitious fund name, uncheck the option “Book to GL" and choose the option "Never" under "Always check on availability of funds at". And you should not make any fund contributions.
After saving the settings, the fund will be assigned a unique Fund code which will be used in setting targets for Monitoring reports and during Loan Importation.
Please note that loan disbursements from the fund and loan repayments do not affect the fund account. What you enter here is only the fund contributions from the donors, the borrowed amounts for on-lending, your internal funds which you use for giving out loans and the withdrawals of the mentioned funds if any.
From the Loan Performer Users Community
|We welcomed the following Loan Performer Users:
Lifua Financial Services, Tanzania
Maroon Capital Microfinance, Ghana
Jopeg Investments, Uganda
Universal Capital, Ghana
Youth Initiatives, Kenya
|We had the following trainings:
This month, one person from Mairye Estates Employee Sacco in Uganda was trained at our office in Kampala.
|We had the following implementations:
Burkina Faso: training and version 8 implementation for Microstart (finished).
Ghana: on-site support was provided for Adom Sika, GIF and Tema AG.
Mexico: training and implementation of version 8 for Fomdesur, a project of the state of Guerrero (finished).
Uganda: Version 8 implementation for Mairye Estates Employee Sacco.
1. Fourteen of our staff members have started a SQL Server 2012 course, in particular 10774A: Querying Microsoft SQL Server 2012. We hired a trainer from BCI Wrox who will take us through this course in 5 Saturdays.
Photo: CCS Staff Training in SQL Server 2012
Next Training Opportunities
We have every first Monday of the month a training session of 12 days (2 weeks, Monday to Saturday from 9:00 to 17:00 hrs) in Loan Performer version 8. Next training starts Monday 4 November 2013. This takes place at our office in Kampala. Costs are 750$ per participant. At the end of the training the participants have to pass a test and a certificate will be issued. Use this link to download the training schedule.
If Kampala is too far, we can do an e-training via the internet. The full training takes 12 sessions of 4 hours at a cost of USD 150 per session. We can also tune these trainings to your needs and make them more efficient for you.
Need help with Loan Performer? Try the Online Help or Chat with our staff.