Instructions & Standards Outline

• Objectives
• Master File Standard Format and Rules
• Components of the Master File
• Completing the Master File - Conclusion

Objectives

At the end of this lecture, the participants should:

• Identify Master File
• Know all the Schedules in the Master File - Write file name for each monthly returns - Complete the Master File - State the rules guiding the completion of the Master File

Master File Format

• MASTER FILE STANDARD FORMAT AND RULES
• Master File is a predefined Excel based returns template for rendering returns
• Sheet Names are defined as Form Numbers eg Sheet for Balance Sheet MMFBR 300 is defined as 300

Insert MFBXXXXXmMMYYYY-v2010.06.22(1) [Compatibility Mode] - Microsoft Excel View

Format	Delete	Forma
Painter	7 Clear
Formatting	as Table	Styles	Filte
Alignment	Cells	Editing
Styles

B . Bank Code ) Bank Name e: nent Name nent Code ABC Microfinance Bank Limited Form MMFBR 300 Monthly Statement of Assets and Liabilities 31/12/2009

	Item Description	Amount N'000	Amount N'000	Amount N'000
	ASSETS	e and the support of the support of the support of the support of the support of the support of the support of the support of the status and				e and the support of the support of the support of the states
ASH:	e and the	e and the	e and the	e and the support of	e and the property
	e and the
Notes	e and and	e and and
Coins
otal Cash
)UE FROM:		e and the support of the	and the property
Banks in Nigeria:
	Balances with Banks (Specify, MMFBR 221)
Placements
	Secured with Treasury Bills (Specify, MMFBR 311)
	Unsecured (Specify, MMFBR 321)
Total Placement with Banks/Discount Houses
otal Due From
SHORT TERM INVESTMENTS:
Treasury Bills
.ONG-TERM INVESTMENTS:
1000 /001 /221 /311 /321	641	7 62	703 .	764	811 141 201	202 / 312 / 322

G 451 / 501

Mmfbr 300 Balance Sheet Master File Format Contd.

• Each sheet has three sections, the Header, the Body and the Footer
• Header contains MFB Code, MFB Name, Returns Code, Returns Name, Reporting Date, State Name, State Code, Local Government Name and Local Government Code
• Body contains the Schedule for the Return - Footer contains the name, signature and phone number of the officers in charge.

Formatting

• The File format is MICROSOFT EXCEL 97 - 2003
• The File Name is in the format MFBXXXXXmMMYYYY.xls - Where
• MFB - Microfinance Banks
• XXXXX - FI (Financial Institution) code - (Numeric) eg. 50001
• m - frequency of Returns ie Monthly - MM - Number of Month eg 02 for February
• YYYY - Year (Numeric) eg 2010
• Example: The file name for ABC MFB with code 50001 that is sending returns
• in February 2010 is MFB50001m022010 - in March 2010 is MFB50001m032010 - in April 2010 is MFB50001m042010

Formatting Cont"D

• All yellow colored cells are filled with formulae (cannot be modified)

• All white cells are input cells (modifiable)

• All grey cells are labels (cannot be modified)

• Font for cells and numbers is Arial - size 10.

• Numbers are "ALIGNED RIGHT" and formatted as 123,456,123.

• All figures must be in thousands ("000) - Date format is; dd/mm/yyyy, e.g. 27/07/2009.

Protection

• The YELLOW AND GREY CELLS are protected and cannot be modified
• All the sheets are protected and users cannot insert, delete or rename
• Users cannot insert or delete rows in any of the sheets except form 771, Schedule of Classified Loans and Advances
• White cells accept only positive whole numbers except some cells under Reserve in the Balance Sheet

Master File Components

The MASTER FILE has the following schedules: MMFBR 763: Loan Structure and Maturity Profile MMFBR 764: Interest Rate MMFBR 811: Other Assets MMFBR 141: Other Deposits MMFBR 201: Deposit Structure and Maturity Profile MMFBR 202: Insured Deposits MMFBR 212: Takings from Banks in Nigeria MMFBR 322: Takings from Other Institutions MMFBR 451: Re-Financing Facilities MMFBR 501: Other Liabilities MMFBR 642: Borrowings from Foreign Agencies MMFBR 651: Borrowings from Other Agencies MMFBR 933: Deferred Grants and Donations MMFBR 951: Other Reserves MMFBR 996: Off Balance Sheet Engagements MMFBR 980: Gap Analysis I Anti Money Laundering and KYCMMFBR 300: Balance Sheet MMFBR 1000: Statement of Profit and Loss MMFBR 001: Memorandum Items MMFBR 221: Balances Due From Other Banks in Nigeria MMFBR 311: Secured Placements with Banks/Discount Houses MMFBR 321: Unsecured Placements with Banks/Discount Houses MMFBR 641 : Other Long Term Investments MMFBR 711 : Micro Loans by Lending Models MMFBR 746 : Other Loans MMFBR 761: Summary of Loans Classification MMFBR 771: Summary of Non-Performing Loans MMFBR 762: Sectoral Analysis of Loans & Advances

10

STEPS FOR COMPLETING THE MASTER FILE

• Fill the following information in the Header of Form MMFBR 300 - Balance Sheet
• The bank code, bank name, reporting date, state name, state code, local government name and code. Once this is done, all these information will appear on all the sheets.

Home Insert

& Cut	as Table
E43

Delete Format 2 Clear Cellt Micro-Finance Ba 2 Micro-Finance Bank Name 3 Return Code 4 Return Name 5 Reporting Date: 6 State Name 7 State Code 8 Local Government Name 9 Local Government Code 10 ABC Microfinance Bank Limited Form MMFBR 300 Monthly Statement of Assets and Liabilities

11
12	Code	Item Description	Amount N'000	Amount N'000	Amount N'000
13	10000	ASSETS
14	10100	CASH:
15	10110	Notes
16	10120	Coins
17	10130	Total Cash
18	10200	DUE FROM:
19	10210	Banks in Nigeria:
20	10220	Balances with Banks (Specify, MMFBR 221)
21	10300	Placements
22	10310	Secured with Treasury Bills (Specify, MMFBR 311)
10320		Unsecured (Specify, MMFBR 321)
10330	Total Placement with Banks/Discount Houses
10400	Total Due From
	SHORT TERM INVESTMEN
	Treasury Bills
	LONG-TERM INVESTMENTS	762 / 763 / 764 / 811 / 141 / 201 / 312 / 322 /	451

31/12/2009 MMFBR 300: Balance Sheet

Nb: Cbn Code Book Can Be Downloaded From Project Website. Loan Portfolio Review

• Prepare a Loan Schedule of all loan customers, Micro, Staff, Small & Medium Enterprise, Overdrafts, etc (i.e. all items described under Loans And Advances in Schedule MMFBR 300) segregating their loan balances into PRINCIPAL AND ACCRUED INTEREST (INTEREST IN SUSPENSE)
• Complete Schedule MMFBR 771, Summary of Non Performing Loans (if any) to derive loan classification into Pass & Watch, Sub-Standard, Doubtful and Lost as well as Accrued Interest Unpaid (Interest in Suspense). These figures automatically appear in Schedule MMFBR 761, Summary of Loan Classification. 13 13

Loan Portfolio Review Contd.

• Where there are more than 50 customers, INSERT additional rows and copy the fomulae to all the new rows.

• Where there are excess rows, DELETE the unwanted rows

• NB: If this schedule is not completed, it means all the loans are PERFORMING

Calculation Of Loan Loss Provision: Schedule Mmfbr761

• Derive Total Performing Loans figure by deducting the addition of Pass & Watch, Sub-Standard, Doubtful, Lost and Interest in Suspense, in Schedule MMFBR 761, from Total Loans figure. i.e. Total Loans - Classified Loans (i.e. Pass & Watch + Sub-Standard + Doubtful + Lost + Interest in Suspense).

• Update the Performing loans figure in Schedule MMFBR 761 Summary of Loan Classification. Once this is done, the total of this schedule should be the same with the total loans figure in the General Ledger and Trial balance.

15 15

Calculation Of Loan Loss Provision: Schedule Mmfbr761 Cont'D

• Compare the new Total Loan Loss Provision generated in Schedule MMFBR 300 with the previous Total Loan Loss Provision in the previous month and raise journal entries to adjust it as follows:
• Where the new PROVISION is higher than the previous PROVISION
• DR The Profit and Loss Account with the difference - CR The Loan Loss Provision Account

Calculation Of Loan Loss Provision: Schedule Mmfbr761 Cont'D

• Where the new PROVISION is lower than the previous PROVISION
• DR The Loan Loss Provision Account with the difference
• CR The Profit and Loss Account - Close all Accounts for the month and generate a trial balance whose TOTAL LOAN LOSS PROVISION will be the same with the figure in Schedule MMFBR 300.

17

Schedule Mmfbr 1000: Income Statement

• Update the income statement to derive PROFIT OR LOSS for the PERIOD. If it is "PROFIT", it automatically goes to "Unaudited Profit to Date" in Schedule MMFBR 501 Other Liabilities. If it is a "LOSS", it automatically goes under "RESERVES" in MMFBR 300.

• NB: The Income Statement should be cumulative of all relevant months and NOT for the CURRENT MONTH.

18

Schedule MMFBR 762 - Sectoral Analysis of Total Loans and Advances

• Breakdown all your Loans and Advances (i.e. all item description under LOANS AND ADVANCES in Schedule MMFBR 300) into Sectors.

• NB: This includes Staff Loans

SCHEDULE MMFBR 300 – BALANCE SHEET

• Identify all items in MMFBR 300 that requires breakdown (i.e. Specify MMFBR ….) should be updated from the Schedule. Example, Balances with Banks (Specify MMFBR 221) should be populated from SHEET/FORM 221 AND THE TOTAL WILL AUTOMATICALLY APPEAR IN MMFBR 300.

• NB: Same applies to all Descriptions with (Specify MMFBR …….)

SCHEDULE MMFBR 300 – BALANCE SHEET

• After completing all the "Specify items" in MMFBR 300 as stated above, then updated all other relevant WHITE Cells as applicable.

• Update all other relevant Schedules that are not linked to MMFBR 300 as applicable.

Conclusion

Users are reminded to:

• Adhere to the method for completing the Master File
• Avoid "doctoring" the information - Submit within the stipulated period as enshrined in the MFB Guidelines.

Thank You