The ALPHA export (.xls or .xlsx) of the credit union.
No file at hand? or download the demo trial balance (fictional data).
The credit union's own old → new account list, or a mapping file saved from this tool (Excel, or CSV from older versions) to continue a review.
The file
Does the trial balance hold together?
3. Split loans by duration (optional)
The ALPHA Delinquent loans report of the same branch and date. ALPHA keeps loans of every duration in one product; the report classes them loan by loan: up to 1 year (32), 1 to 2 years (31), over 2 years (30), and doubtful when more than 45 days late, 90 for housing loans (3352). The exports then use this split, and the provisions booked for doubtful loans are checked.
| Loan purpose | Loans | Up to 1 year | 1 to 2 years | Over 2 years | Doubtful | Report total | Trial balance |
|---|
Interest on loans
ALPHA keeps each product's interest in its own account. Each one is split between 713 (up to 1 year), 712 (1 to 2 years) and 711 (over 2 years) in the proportions of its product's loans, doubtful loans included at their own duration; interest with no product in its name follows all the loans. This is an estimate: interest is earned over the whole period, the report gives the balances on one day.
| Interest account | Split like | Up to 1 year | 1 to 2 years | Over 2 years |
|---|
Provisions on doubtful loans
A provision is money put aside for loans that may not come back; too little makes the profit look bigger than it is. COBAC (regulation EMF R-2017/07, art. 17) asks for 100% of a doubtful loan within a year, less only when it is covered by savings formally pledged to the credit union or by a registered mortgage. The report shows neither the guarantees nor since when a loan is doubtful, so "required" is the most COBAC can ask: a shortfall is a point to check with the credit union, not a proven error. Nothing is changed in the figures.
| Loan purpose | Doubtful loans | Doubtful balance | Required provision (at most) | Booked provision | Shortfall |
|---|
4. Review each account
| Legacy account | Name | Closing balance | Network account | Network name / PCEMF | Status |
|---|
5. Export
The mapping file also saves your review: load it in step 2 (after the same trial balance) to continue later, on another computer, or to hand the review over.
What this tool does
Each credit union numbers its accounts its own way in ALPHA. COBAC requires reports in one official format (the PCEMF chart and the FM1000 statement), and the network needs everyone on the same accounts. This tool translates a credit union's accounts into the network accounts and checks every one of them.
Steps
- Load the credit union's trial balance exported from ALPHA. If they already made an old → new list, load it too (or keep it as a second sheet in the same file).
- Check the top panel: the trial balance must balance and each account must appear once. If not, ask for a fresh export before going further.
- Optionally, load the credit union's Delinquent loans report from ALPHA (same branch, same date) to split each loan product by duration.
- Go through Errors, then To check. Type or pick the right network account in the box; your choices are remembered in this browser.
- Export the mapping, the harmonised trial balance and the FM1000.
Splitting loans with the loan report
COBAC wants loans by initial duration, but ALPHA keeps short and long loans together in one product (MAIN LOAN, EXPRESS LOAN…). The trial balance alone cannot tell them apart; the ALPHA Delinquent loans report can, because it lists every loan. For each loan the tool reads only the member's account number, its balance, its days late and its number of instalments (never the member's name):
- More than 45 days late (90 for a housing loan): doubtful (member product 335200 Doubtful loans to members), whatever its duration (COBAC regulation EMF R-2017/07, art. 8). Once one loan of a member is doubtful, all that member's loans are too (art. 9, "contagion"), found by the member's account number.
- Otherwise by its number of instalments, which are monthly, so it is the duration in months: up to 12 → up to 1 year (32, 328100 for other loans to members); 13 to 24 → 1 to 2 years (31, 318000); more than 24 → over 2 years (30, 308000).
Each report product is matched to the League's loan purpose by its name, as in the network table (SCHOOL FEES → consumer loans, STAFF → loans to staff and elected officials; MAIN, EXPRESS, PROJECT or GOLDEN LOAN → other loans to members). Overdrafts and salary advances are not loans and are not split.
The table shows, per product, the report's total next to the trial-balance accounts of that product. They should agree. If not, the report is probably for another branch or date, or some loans sit in other accounts: the split still uses the report's proportions, and the accounts are flagged. The report shows the loans on its date only, so the opening balance and the movements of the period are split in the same proportions as the closing balance.
Interest on loans. ALPHA keeps each product's interest in its own account (INTEREST ON MAIN LOAN, INTEREST ON TAXABLE EXPRESS LOAN…). The network keeps interest by duration only (711 over 2 years, 712 1 to 2 years, 713 up to 1 year), not by product, so each interest account is split between the three in the proportions of its own product's loans. A doubtful loan counts at its own duration here. Interest with no product in its name (LOANS INTEREST) follows all the loans of the report; interest on overdrafts is not split. This is an estimate, because interest is earned over the whole period and the report gives the balances on one day. Fees, accrued interest and provisions are not split: ALPHA keeps them for all products together, so they stay in the duration proposed for them.
Provisions on doubtful loans. A provision is money put aside, as an expense, for loans that may never be repaid. If too little is booked, the profit looks bigger than it really is, a frequent problem in credit unions. COBAC (regulation EMF R-2017/07, art. 17) asks, on each doubtful loan's balance: nothing when it is fully covered by savings or deposits formally pledged to the credit union (or State securities); at least 25%, 50%, 75% then 100% after one, two, three and four years doubtful when it is covered by a registered mortgage or a bank or public guarantee; otherwise 100% within one year. The loan report shows neither the guarantees nor since when a loan is doubtful, so the tool takes every doubtful loan as not covered: 100% of its balance, the most COBAC can ask. It shows this per product and compares the total with the provisions in the trial balance (the accounts mapped to 391, 392 and 393: ALPHA keeps them for all products together). A shortfall is a warning on those accounts, never a change to the figures: check the guarantees and the provision entries with the credit union. The report's Savings column is not taken as a guarantee, because a guarantee counts only when it is written, registered and pledged to the loan (art. 19). COBAC allows the provision to be booked at the latest at the next annual closing, so a shortfall in the middle of the year may still be corrected.
Two kinds of errors
- Mapping errors: the balance is right but points to the wrong (or no) network account. Fix them here, in the tool.
- Errors in the figures: the trial balance does not balance, an account appears twice, a till is below zero, the previous year is not closed, shortages or suspense accounts remain. The tool only reports these: the credit union corrects them in ALPHA, with entries approved by management. Never hide them by choosing another account.
The review loop
- Load the trial balance (the opening balance or any period) and fix the mapping errors here.
- Send the Issues list to the credit union for the errors in the figures.
- They correct them in ALPHA and send a fresh export.
- Load the new export. Your choices are still there in this browser; on another computer, load the saved Mapping (Excel) in step 2.
- Repeat until there are 0 errors, then export the final, complete files.
The rules checked on every account
- Network accounts have 6 digits (member products) or 12 digits (general ledger), and one kind never becomes the other.
- The first 6 digits are the official PCEMF account; sub-accounts change only from the 10th digit (digits 7–9 stay 000).
- The account must exist in the COBAC PCEMF and be a detail account, not a heading.
- The balance must be on the side the FM1000 allows (for example savings are always credit, cash always debit).
- Items with the League or other network members should use network accounts (5th digit 9), which cancel out when the network is combined.
- Loans are classed by their initial duration, as COBAC requires: up to 1 year in 32, 1 to 2 years in 31, over 2 years in 30. The League's standard chart has one member account per purpose, borrower and duration (other loans to members: 328100 / 318000 / 308000; consumer loans 326000 / 316000 / 306000; staff and elected officials 328300 / 318200 / 308200…), and doubtful loans in member product 335200; interest and fees are kept by duration only (interest 711 / 712 / 713, fees 7151 / 7152 / 7153). Loans found in general-ledger accounts are refused: they must be moved to member accounts in ALPHA. ALPHA products mix durations, so a product's balance is split between the three (and doubtful loans) with the credit union's loan report, in step 3.
Where the proposals come from
In order: your own choice, the network table, the credit union's own mapping, then the same or a similar name. In the network table, standard accounts follow the network's standard decisions for a category 1 EMF; confirm means a standard decision the network still has to approve (for example a loan product's duration); league follows the League table; agreed means several credit unions chose the same account; single means only one credit union used it so far; review means a problem still to settle.
Privacy
Files are read in this browser only; nothing is uploaded.
The site counts visits anonymously with Vercel Web Analytics (country, device and browser, no cookies), and counts when a trial balance or a loan report is loaded or a file exported (the kind of trial balance or file, or the number of loan products, only). The loan report stays in memory and is forgotten when the page closes. It never sees your files, account names, amounts or the credit union's name.