Using Microfin 4

A Handbook for Operational Planning and Financial Modeling

Authors: Shirley A. Lunde, Tony Sheldon, Chuck Waterfield

Technical Tools Series No. 2

December 2006
Using Microfin 4: A Handbook for Operational Planning
and Financial Modeling

Copyright 1998-2007, CGAP/World Bank, The Consultative Group to Assist the Poorest

Printed in the United States of America

ISBN 1-888753-20-X

2 Using Microfin
Contents
Foreword 9
Acknowledgments 11
Introduction 15
About the Handbook 15
Organization 16
Conventions 17
Printed versus Electronic (Word or Acrobat) Versions 18
About the Audience 19
About Business Planning 19
About the Case Study: Freedonia Enterprise Development Association (FEDA) 20
Chapter 1 Before You Begin: A Best-practice Approach to Planning 23
A Framework for Business Planning 23
Strategic Planning 26

Contents
Operational Planning 26
Financial Modeling 27
Overview of Microfin’s Planning Framework 27
General Planning Recommendations 31
Budget Adequate Time and Resources 32
Encourage Participation by All Stakeholders 32
Assign Responsibility for Financial Data Gathering 33
Avoid Unrealistic Projections 33
Case Study: FEDA’s Strategic Plan 34
Chapter 2 Overview of Microfin 43
Structure of Microfin (Sheets and Tools) 43
The User Interface 53
Microfin Operating Mode 53
Microfin Toolbar 55
Pagebars 66
Flowcharts 67
Links Buttons 69
Wizards 69
Keyboard Shortcuts 70
Recalculation Options 71
Automatic Error Checking 71
Security (Protected Worksheets) 72
Data Entry, With and Without NAVIGATOR 72

Using Microfin 3
 Microfin Help 88
Online Help Facility 88
Auto Help Manual System 90
Pop-up Help Text 91
Self-Study Tutorials 91
Performance Issues 92
Limitations 92
Technical Support and Enhancement Requests 93
Chapter 3 Installing Microfin 95
User License 95
Recommended Hardware and Software 95
Installation Procedure 96
Transferring Microfin to Another Computer 102
Chapter 4 Creating a Microfin Model 105
Creating a New Model From the Microfin Template File 105
Customizing the Model’s Operations Using the SETUP WIZARD 107
Saving the New Microfin Model 116
Opening a Previously Saved Microfin Model 117
Upgrading a Model Developed in an Older Microfin Version 117
Using the MODEL SETUP Sheet to Establish a New Model 117
Microfin Operating Mode 122
Clearing the Model (Optional) 122
Choosing an Approach for the Projections (Consolidated, Branch or Region)
123
Entering Institutional Information 128
Entering Inflation and Indexing Data 129
Entering Data from Historical Financial Statements 131
Entering Portfolio Information 137
Generating Historical Ratio Analyses 138
Case Study: FEDA’s MODEL SETUP Sheet 141
Chapter 5 Defining Products and Services 145
Designing Successful Loan Products 146
Key Characteristics of Product Design 146
Choosing a Lending Methodology 148
Using the PRODUCTS Sheet to Define Loan and Savings Products 148
Enabling Term Loan Products 151
Reviewing the Term Loan Product Design Summary 154
Defining Loan Products on the Product Design Sheet (PDS) 156
Defining Line-of-Credit Products 172
Defining Savings Products 173
Case Study: FEDA’s Loan and Savings Products 179

4 Using Microfin
Chapter 6 Defining Marketing Channels to Project Client Activity 185
Using the BRANCH Sheet to Generate Loan and Savings Projections 186
Changing the Number of Branch or Region Sheets 189
Reviewing the Branch Summary Report Data 191
Generating Term Loan Portfolio Projections 193
Choosing Method to Project Credit Activity 198
Reviewing the Credit Graphs 212
Reviewing the Portfolio Projections 218
Generating Line-of-Credit Projections 220
Reviewing the Line-of-Credit Product Summary 223
Reviewing the Line-of-Credit Graphs 223
Generating Savings Projections 225
Reviewing the Voluntary Savings Mobilization Summary 229
Reviewing the Savings Graphs 229
Optional Exercise in Projecting the Portfolio 232
Case Study: FEDA’s Marketing Channels 236
Chapter 7 Planning Institutional Resources and Capacity 241
Developing an Overall Resources Plan 241
Planning Expenditures for Staffing, Other Operational Expenses, and Fixed

Contents
Assets 242
Automating the Expenditure Projections (Optional Feature) 244
Implementing the Resources Plan in Microfin 246
Using the INST CAP Sheet to Enter General Background Information for
Institutional Resources and Capacity 248
Choosing a Cost Allocation Method (Branch and Regional Models) 251
Establishing Policies for Loan Loss Provisioning 254
Defining Categories for Staffing 255
Defining Categories for Other Operational Expenses 258
Defining Categories for Fixed Assets, Buildings and Other Assets 259
Defining Categories for In-kind Subsidies 263
Entering Adjustments to the Cash Flow Analysis 264
Case Study: FEDA’s Institutional Resources and Capacity 268
Chapter 8 Projecting Branch-related Resources and Capacity 271
Using the BRANCH Sheet to Project Branch-related Resources and Capacity 271
Projecting Additional Sources of Income 273
Reviewing Income Graphs 275
Reviewing Financial Costs 278
Generating Loan Loss Provisions and Reserves 279
Projecting Branch-related Staffing Requirements 281
Reviewing Staffing Graphs 296
Projecting Branch-related Other Operational Expenses 298
Reviewing Expense Graphs 303

Using Microfin 5
 Projecting Branch-related Expenditures for Fixed Assets 304
Reviewing Asset Graphs 310
Case Study: FEDA’s Branch-related Resources and Capacity 312
Chapter 9 Projecting Administrative Resources and Capacity 319
Using the HEAD OFFICE Sheet to Project Administrative Resources and Capacity
319
Projecting Administrative Staffing Requirements 326
Projecting Administrative Other Operational Expenses 331
Projecting Administrative Expenditures for Fixed Assets 335
Projecting Expenditures for Land and Buildings 339
Projecting Administrative Other Assets 341
Calculating Taxes 343
Projecting In-kind Subsidies 344
Using the Aggregate Sheet to review Total Projections 344
Case Study: FEDA’s Administrative Resources and Capacity 345
Chapter 10 Developing a Financing Strategy 351
Classifying Financing Sources 351
Using the FINANCING SOURCES Sheet to Identify Debt and Equity Financing 353
Identifying Sources of Financing 356
Identifying Restrictions on Use of Initial Available Assets 359
Setting Liquidity Requirements 360
Establishing the Market Rate Cost of Funds 362
Reviewing Financial Costs Calculations 363
Using the FINANCING FLOWS Sheet to Project Cash Flow 364
Identifying Financing Flows by Source 368
Enabling Automated Default Financing Sources 370
Developing an Investment Strategy 373
Projecting Income on Investments 374
Analyzing Financing Flow and Liquidity 376
Reviewing the Financing Graphs 385
Case Study: FEDA’s Financing Strategy 388
Chapter 11 Analyzing Graphs, Projections and Indicators 393
Generating and Reviewing Graphs Using the GRAPHS and USER GRAPH Sheets
393
Storing, Printing or Copying Graphs to Other Windows-based Software Using
the GRAPH CLIPBOARD Sheet (Advanced Feature) 399
Creating Custom Line Graphs Using the USER GRAPHS Sheet 400
Reviewing the SUMMARY REPORT Sheet 402
Reviewing the FINANCIAL STATEMENTS Sheet 410
Analyzing the Balance Sheet 411
Analyzing the Income Statement 414
Reviewing the Cash Flow Projections 417

6 Using Microfin
 Analyzing Performance Indicators and Ratios 418
Reviewing the Efficiency and Profitability Graphs 427
Reviewing Branch or Regional Income Statements and Analyses on the BRANCH
Sheet 431
Reviewing Institution-wide Analyses on the AGGREGATE Sheet 433
Chapter 12 Financial Modeling as a Management Tool 439
Using the EXPORT Sheet to Export or Import the Data in a Microfin Workbook 439
Using the SCENARIOS Sheet (Scenario Manager) for Sensitivity Analysis 450
Using the VAR ANALYSIS Sheet to Analyze Monthly or Quarterly Performance
Variances 456
Incorporating Microfin into the Annual Planning Process 461
Chapter 13 Optional Facilities for Advanced Modeling 463
Using the USER-DEFINED Sheet to Add Features or Supplement Calculations 463
Using the CASELOAD Sheet to Optimize Caseload Methodology and Design 465
Using the CLIENT COST and REP SCHEDULE Sheets to Generate Effective Interest
Rates and Client Cost Analyses 471
Calculating Effective Interest Rates Using the CLIENT COST Sheet 471
Generating Loan Repayment and Cash Flow Schedules Using the REP SCHED
Sheet 477

Contents
Using the RETENTION Sheet to Evaluate Retention Rates 478
Defining an Additional Language 479
Using the TRANSLATIONS Sheet to Create a User-defined Language 480
Downloading or Importing a Language 481
Annex 1 Business Planning Framework 483
Strategic Planning Framework 484
Operational Planning Framework 494
Annex 2 Microfin’s Data Requirements 495
MODEL SETUP Sheet 495
PRODUCTS Sheet 496
BRANCH Sheet 496
INST CAP Sheet 498
HEAD OFFICE Sheet 498
FIN SOURCES and FIN FLOWS Sheets 499
SUMMARY REP Sheet 499
OTHER PROGRAMS Sheet (Optional) 499
CLIENT COST Sheet (Optional) 500
RETENTION Sheet (Optional) 500
CASELOAD Sheet (Optional) 500
Data Requirements and Methodology for Estimating Client Loan Data 501
Annex 3 Bibliography of Business Planning Materials 503
Clients and Markets 503

Using Microfin 7
 Environmental Analysis 503
Finances and Funding 503
Financial Management 504
Implementing a Planning Process 504
Institutional Assessment 504
Institutional Resources and Capacity 504
Marketing and Distribution Channels 505
Mission and Goals 505
Products and Services 505
Strategic Planning 506
Index 507

8 Using Microfin
Foreword
Over the past 20 years, a microfinance industry has emerged in response to the lack
of access to formal financial services for most of the world’s poor. Microfinance
institutions (MFIs) serve an ever-increasing number of poor clients, but the demand
for their financial services still far outstrips capacity.

To meet this demand, most MFIs plan to increase their outreach. But rapid growth
strains an institution’s systems and changes its financial dynamics. Without effective
business planning and projection tools, MFIs can—and often do—inadvertently
undermine their own efforts.

Many MFIs have business plans, but these plans are sometimes of poor quality. They
are often overly ambitious because the underlying projections are not sufficiently
detailed to reveal the hurdles the institutions must overcome. And, if prepared by
outsiders (as they often are, in response to requirements by potential funders), they
are usually stored away after the funding is secured rather than used as ongoing
management tools.

Even the best business plans and financial projections are certainly not secure
predictions of the future; under the most positive circumstances, they involve

Foreword
assumptions that must be adjusted for changing realities. But, while a business plan is
not a crystal ball, the exercise of preparing one with participation by staff at all levels
helps an institution in three ways:

It raises key strategic and operational issues that the institution’s stakeholders
must address and, ultimately, resolve by consensus.

The financial and operational planning process, if carefully done, often reveals
important, but not previously understood, dynamics of the business.

The resulting plan serves as a roadmap for reaching the institution’s goals.
While there will always be deviations, a good and frequently updated plan
helps management to identify when the institution is deviating and the direction
in which it needs to move in order to get back on track.

In 1997 and 1998, the Consultative Group to Assist the Poorest (CGAP)
commissioned and published the initial version of this handbook to help MFIs
perform their own business planning—including preparing strategic and operational
plans and, especially, financial projections. This previous version, entitled Business
Planning and Financial Modeling for Microfinance Institutions: A Handbook,
included an accompanying Excel-based financial model (Microfin) that MFIs could
use to prepare sophisticated five-year financial projections. CGAP financed the
development of this model and the related [Link] website.

The Microfin model, the handbook, and the website were developed under the
excellent stewardship of Charles (Chuck) Waterfield and Tony Sheldon. Microfin
also benefited from the significant financial and technical contributions made by
Women’s World Banking, as well as the field-testing performed by its affiliates.

Using Microfin 9
In 1999, CGAP commissioned a comprehensive technical evaluation of the handbook
and the Microfin model. This evaluation included a global user survey of how MFIs
rated both tools. Based on the findings of this evaluation, CGAP then commissioned
revisions to the handbook, Microfin, the [Link] website and a related training
module. In addition to financing and coordinating this initiative, CGAP invested
considerable staff time in designing, reviewing, field-testing and publishing the
updated handbook and the new 3.0 version of Microfin.

The current version of the handbook, Using Microfin 4, A Handbook for Operational
Planning and Financial Modeling, focuses more directly on using Microfin and is
structured as a software user’s guide. It explains planning and modeling concepts and
provides detailed procedures for using Microfin to develop operational and financial
projections. In so doing, it guides readers through Microfin, worksheet by worksheet
and section by section. The handbook provides both an overall framework and
general guidance for business planning, including strategic and operational planning.
It also includes a brief discussion on using the resulting projections and business plan
as ongoing management tools.

When piloting the updated handbook and model, CGAP realized that new users
may have questions and need technical support. Therefore, for technical support
options and answers to frequently asked questions about Microfin, you may refer
to the CGAP website ([Link] or the Microfin website
([Link] (New MFIs may prefer to use a less complex model, then
turn to Microfin when they have more experience and need a more powerful model.)

Likely, experience with Microfin—and the evolution of the microfinance industry

itself—will reveal opportunities for additional improvement to these business
planning tools. We always appreciate hearing from anyone who has put these tools to
practical use. Send comments or suggestions by email to cgap@[Link], or
contact us through the CGAP offices. Our mailing address is:

CGAP, Room Q4-400

c/o The World Bank
1818 H Street NW
Washington, DC 20433 USA
Telephone: +1.202.473.9594 / Fax: +1.202.522.3744

The Using Microfin 4 handbook and the Microfin model are part of CGAP’s
technical guide series of publications, intended to provide practical tools for
microfinance institutions. CGAP serves MFIs, donors and the microfinance industry
by providing technical assistance and strategic advice, developing and disseminating
technical guides, delivering training, and performing field research on innovations.
CGAP also has a small grant facility that provides funding for these activities as well
as for strategic investments in MFIs.

For information on CGAP’s activities, see the CGAP website at [Link]

Elizabeth Littlefield
June 2001
Chief Executive Officer
Consultative Group to Assist the Poorest

10 Using Microfin
Acknowledgments
The original version of this handbook was financed by the Consultative Group to
Assist the Poorest (CGAP), with the revised version for Microfin 4 financed by the
Swedish International Development Agency (Sida). The accompanying financial-
projections model, Microfin, was financed by Sida, Women’s World Banking
(WWB), and CGAP working in partnership.

WWB is a leading microfinance support organization, serving 45 affiliates globally.

The organization was visionary in recognizing the potential benefits of Microfin and
proactively tested the model among their affiliates. WWB’s contributions to
Microfin, and their extensive testing, led to important changes within their own
affiliate network and, ultimately, served to benefit the industry as a whole.

Jennifer Isern of CGAP coordinated the Microfin project and worked closely with all
partners to evaluate and enhance Microfin and this handbook. Since 1997, when
work began on the first model and handbook, she has been the key person at CGAP
to design and guide the development of this tool.

The Microfin model and the business planning framework were developed by Chuck

Acknowledgments
Waterfield and Tony Sheldon. Both have made key contributions to the current
handbook, based on their original publication. Chuck and Tony have taken an
entrepreneurial approach to developing and promoting Microfin, which has received
wide acclaim from the microfinance industry. From the beginning, they have
provided top quality training courses and presentations to WWB, CGAP and many
other participants. Their skill in conveying difficult concepts to an enthusiastic
audience has led to greater acceptance and use of the model.

Kim Craig conducted a thorough and rigorous systems review and usability test of
Microfin 4 using professional software standards. Her review and additional technical
assistance led to important revisions in the final model which have improved its
overall design, performance and ease of use.

Shirley Lunde adapted the original manual to create the “Using Microfin 3”
handbook, translating complex concepts into a practical step-by-step guide that leads
Microfin users through the modeling process. She was also one of two consultants
that CGAP commissioned to perform an independent evaluation of the original
handbook and the Microfin model. She based the new handbook on her
understanding of how microfinance institutions use Microfin, as well as her
background in software development and accounting/finance. In addition to writing
the handbook, Shirley also supervised its layout and publication, developing a new,
easier-to-revise format that is readily accessible in electronic form.

The new version of this handbook, “Using Microfin 4”, is a significant revision of the
previous handbook to incorporate the several hundred new features added in the new
version of the Microfin software. All updating was done by Chuck Waterfield, the
developer of the software.

Using Microfin 11
Sheryl Henry edited the handbook. Nathalie D’Ambrosio-Vitale and CGAP’s
publications manager, Tiphaine Crenn, worked intensively on the review, publication
and translations of the handbook and model. David Ferrand co-authored the
independent evaluation of the original handbook and Microfin model (along with
Shirley Lunde). Tom Goering of World Relief made valuable contributions to
Microfin’s import/export facility.

12 Using Microfin
 Introduction

14 Using Microfin
Introduction
Welcome to Microfin—a powerful business modeling tool for microfinance
institutions.

Microfin is designed to support your business planning processes by generating an

integrated, detailed set of five-year projections to help you assess the viability of your
plan. It is authored in Microsoft® Excel and operates within your Windows
environment.1

About the Handbook

This handbook explains how to use Microfin to model your institution’s operations
and to develop detailed financial projections. It also provides an overall framework
and general guidance for operational planning that involves:

defining financial products and services

specifying marketing channels and related activity

projecting institutional resources and expenditures—both program and

Introduction
administrative

developing a financing strategy

analyzing financial projections and indicators

It includes a brief discussion of how you can use your business plan and financial
projections as ongoing management tools, and concludes with an overview of
Microfin’s optional facilities for advanced modeling.

If you are new to Microfin, you may choose to begin with the first page of the
handbook and read through to the finish. As you do so, you can build a practice
model using the case study data in each chapter.

If you are familiar with Microfin, you can use the handbook as a reference guide to
refresh your memory on a specific topic or to understand a never-before-used facility
within the software. Use the table of contents or the index to locate the relevant
portions of the handbook.

1
Due to incompatibilities between Microsoft Excel for Windows and Microsoft Excel for Apple,
Microfin will not function on Apple computers.

Using Microfin 15
Organization
The Using Microfin 4 handbook is generally organized according to the individual
worksheets in the Microfin model (e.g., MODEL SETUP, PRODUCTS). It includes the
following chapters:

“Introduction” presents basic information about using the handbook and

planning in general, and introduces the handbook’s case study. [page 15]

“Chapter 1, Before you Begin: A Best-practice Approach to Planning” outlines
a best-practice framework for your planning process and presents the strategic
plan for the FEDA case study. [page 23]

“Chapter 2, Overview of Microfin” describes the features of your Microfin
software and explains the function of each of its component sheets. [page 43]

“Chapter 3, Installing Microfin” details the procedures for installing your
Microfin software and explains the terms of your user license. [page 95]

“Chapter 4, Creating a Microfin Model” describes the procedure for creating
and saving your new projections model and for using its NAVIGATOR and
MODEL SETUP sheets. [page 105]

“Chapter 5, Defining Products and Services” details the process for defining
your loan and savings products using Microfin’s PRODUCTS sheet. [page 145]

“Chapter 6, Defining Marketing Channels to Project Client Activity” describes
the procedure for generating projections of loan and savings activity using the
BRANCH sheet. [page 185]

“Chapter 7, Planning Institutional Resources and Capacity” introduces you to
capacity planning for your institution’s program and administrative activities
using the INST CAP sheet. [page 241]

“Chapter 8, Projecting Program-related Resources and Capacity” provides
guidance on projecting your program-related resource requirements using the
BRANCH sheet. [page 271]

“Chapter 9, Projecting Administrative Resources and Capacity” provides
guidance on projecting your administrative resource requirements using the
HEAD OFFICE sheet. [page 319]

“Chapter 10, Developing a Financing Strategy” describes Microfin’s process
for modeling financing sources and cash flow, including detailed instructions
for using the F IN SOURCES and FIN FLOWS sheets. [page 351]

“Chapter 11, Analyzing Graphs, Projections and Indicators” provides general
guidance on reviewing graphs, reports, projected financial statements and ratios
using Microfin’s GRAPHS, SUMMARY REPORT and FINANCIAL STATEMENTS
sheets. [page 393]

16 Using Microfin

“Chapter 12, Financial Modeling as a Management Tool” discusses
Microfin’s import/export facility, as well as the tools designed for
sensitivity analysis and variance analysis, using the EXPORT, SCENARIOS
and VAR ANALYSIS sheets. [page 439]

“Chapter 13, Optional Facilities for Advanced Modeling” describes each of
Microfin’s optional analysis tools (including those on the CASELOAD, CLIENT
COST, REP SCHEDULE, RETENTION and OTHER PROGS sheets), and details the
optional procedures for adding features using the USER-DEFINED sheet and for
creating additional language translations. [page 463]

The handbook also includes the following annexes:

“Annex 1, Business Planning Framework” provides a general framework for

strategic and operational planning. [page 483]

“Annex 2, Microfin’s Data Requirements” identifies the information that is
required to complete each of Microfin’s sheets. [page 495]

“Annex 3, Bibliography of Business Planning Materials” provides a detailed,
categorized listing of additional source materials on the subject of business
planning. 2 [page 503]

Finally, the handbook provides a detailed keyword Index.

Introduction
Conventions
The handbook incorporates the following conventions:

The names of Microfin’s component sheets (worksheets) and the sections of

those sheets display in small caps (e.g., PRODUCTS, PROGRAM, ADMIN). All
other references—to button titles and cell names, for example—also appear in
small caps (e.g., DATA VALIDATION subsection, CHANGE METHOD button).

Procedures with a prescribed sequence for you to follow are defined using
numbered lists.

Series of items or options without an inherent sequence are defined using
bulleted lists.

The word choose is used to instruct you to perform a specific action that causes
the software to execute a command.

The word select is used to instruct you to highlight information on a display or
a choice from a drop-down list.

2 In addition, throughout the text, footnotes refer to relevant introductory and advanced texts on specific
topics so that you can pursue areas of particular interest.

Using Microfin 17

References to line numbers refer to Microfin’s numbering scheme, displayed in
the leftmost column of each Microfin worksheet. The format of these line
numbers is #.##, where the number to the left of the decimal point represents a
sequence number for each major section on the sheet, and the digits to the right
of the decimal represent the sequence of the line within the designated section.
From time to time you may see references to line numbers that do not appear in
your model. Microfin hides or displays portions of various sheets based on
your entries. For example, if you only define one loan product, Microfin hides
sections of the model that project activity for loan products two, three and four.
References to row numbers refer to Excel’s numbers which, at your option,
display to the left of Microfin’s line numbers.

Wherever you see a reference to “Microfin generates” or “Microfin calculates”
in a procedural section of the manual, this action must be triggered by first
recalculating the model using the F9 key or the RECALC (F9) option on the
Microfin toolbar. To avoid excessive repetition in the various procedural
sections of the manual, it is understood that you will routinely recalculate
the model as you enter data or change options in each section of each
Microfin sheet.

Printed versus Electronic (Word or Acrobat) Versions

If you are working with a printed version of the handbook, use the Contents section
and the Index to locate specific material of interest.

Throughout the body of the handbook, you will also find page references to related
materials. For example:

For more detailed instructions on using the NAVIGATOR sheet, refer to

“Using the NAVIGATOR Sheet and NAVIGATOR WIZARD as a Framework for
Entering Data” on page 72.

Enter PORTFOLIO INFORMATION used to calculate historical ratios. [page 137]

If you have Microsoft Word (version 97 or later) or Adobe Acrobat installed on your
computer, you can refer to an electronic copy of the handbook file3 while working
with Microfin.

When using an electronic version, you can choose (i.e., click your mouse on) any
page number in the Contents section to automatically move to the referenced
material. To return to your original position in the Contents section, choose the BACK
button () on Word’s Web toolbar or the Acrobat toolbar.

3 Note that the file contains the text of this Microsoft Word document. It should not be confused with
Microfin’s Help file, which is not a Word document and may not contain exactly the same information
as is included in this document.

18 Using Microfin
 You can also choose the page number in an internal reference (such as the
NAVIGATOR reference in the example above) to move to the indicated material. To
return to your original position within the document, choose the BACK button () on
the toolbar.

About the Audience

Microfin is intended primarily for the senior managers of microfinance institutions,
particularly those who lead the planning process. The handbook can also serve as a
resource for other stakeholders, including staff, board members, advisers and funders.

Microfin is designed to be broadly inclusive and relevant for all microfinance

institutions regardless of their stage of development, institutional form, lending
methodology or range of services. While variations in these factors do impact the
content of a specific plan, the overall planning framework is much the same for all
microfinance institutions.

Every effort has been made to ensure that Microfin is easy to use; however, the
software and accompanying handbook are relatively sophisticated and assume you
have a basic understanding of credit methodologies and financial management, as
well as experience in using Microsoft Excel and Microsoft Windows software.

Introduction
About Business Planning
Business planning is an essential management function that includes two closely
related processes:

strategic planning, which establishes your institution’s mission and goals,

assesses your current situation, and develops an overall strategy for the future.
For additional information, refer to “Strategic Planning” on page 26 and
“Annex 1, Business Planning Framework” on page 483.

operational planning, which provides an implementation plan for your
proposed strategy, and typically includes development of detailed financial
projections. For additional information, refer to “Operational Planning” on
page 26 and “Financial Modeling” on page 27.

Microfin supports your business planning process by generating a five-year financial

model based on your strategic and operational plans.

Using Microfin 19
About the Case Study: Freedonia Enterprise
Development Association (FEDA)
Throughout the handbook, you will find figures and other examples and illustrations
based on the business plan of a fictitious microfinance institution, the Freedonia
Enterprise Development Association (FEDA).

You can review FEDA’s strategic plan beginning on page 34. Elements of FEDA’s
operational plan are presented in various case-study sections throughout the
handbook. These sections also provide detailed data that you can enter in Microfin to
create your own FEDA practice model. By developing such a practice model you can
experiment with Microfin and develop a more thorough understanding of its
capabilities and operations.

The various figures included throughout this handbook contain samples of Microfin’s
sheets and sections using the FEDA data, unless otherwise noted. Use them as a
reference when you create your own model. Because many of these figures show
year-one data only, you may find it helpful to refer to a completed FEDA data model,
available from the [Link] website.

20 Using Microfin
 Before
You
Begin

22 Using Microfin
Chapter 1
Before You Begin: A Best-practice
Approach to Planning
It is often true that, by failing to plan, an institution plans to fail.

1 — Before You Begin: A Best-practice Approach to Planning

It is equally true that the very process of planning is often more important than the
plan itself. The planning process forces you to clearly analyze what you wish to
accomplish, and how and when you can reasonably expect to accomplish it. The
process also helps you to understand the factors that are key to your institution’s
success.

Specifically, the overall process enables you to:

clearly define your institution’s vision, mission and goals

build consensus and motivate your staff

create an action plan with objective targets and detailed projections

focus and mobilize the resources required to implement the action plan

strengthen your negotiating position with donors, commercial banks and other
funding sources

communicate vital information to external audiences such as clients, regulatory
authorities and other stakeholders

monitor performance for variances and make midcourse corrections, when
necessary

A Framework for Business Planning

According to Chinese philosopher Sun Tzu, strategy without tactics is the slowest
route to victory, and tactics without strategy is the noise before defeat. The same can
be said of business planning; to do it well, you need an overall strategy and detailed
tactics in the form of an operational plan and financial model.

Using Microfin 23
Therefore, while you may be tempted to install Microfin and immediately begin
generating numbers, it is vital that you lay the proper foundation for your projections.
Without solid research into your institution’s mission, short- and long-term goals,
clients, markets, business and competitive environment, resources, and financial
position, your Microfin model is likely to be both unrealistic and misleading.

To provide this foundation, a best-practice framework for planning generally requires

that you complete each of the following steps:

Develop a detailed strategic plan.

Develop a preliminary operational plan.
Develop a financial model to support your operational plan.
Refine your strategy and operational plan, as necessary.
Based upon your financial projections, you may find it necessary to revise your
strategy or the elements of your operational plan, and then adjust your projections
accordingly. It is not uncommon to develop three, four or even five or more versions
before you are satisfied with the results.
Draft a formal business plan document.
The business plan formalizes the results of your strategic planning, operational
planning and financial modeling activities. It generally covers a period of between
one and five years.

Figure 1.1 outlines the key activities in strategic and operational planning. The
vertical flow of the figure reflects the sequence of the planning process, beginning
with strategic planning, which is then followed by operational planning and financial
modeling (pursued in tandem). The horizontal flow reflects links between key
topics.4

4 Refer to Annex 1, Business Planning Framework [page 483] for more detailed information on strategic
and operational planning.

24 Using Microfin
Figure 1.1 The Flow of Strategic and Operational Planning

Strategic Planning Operational Planning Financial Modeling

Articulate the mission and Set up the model and
goals enter initial balances
Define markets and clients Define products and Analyze credit and
services savings products
Analyze the environment Specify marketing Project credit and

1 — Before You Begin: A Best-practice Approach to Planning

channels savings activity
Competition
Collaborators
Regulatory factors
Other external elements
Perform an institutional Plan institutional Estimate loan loss
assessment resources and provision, reserve and
capacity write-offs

Credit and savings Estimate required

program caseload

Board and management Project program (or

issues branch) expenditures

Human resources
management
Administration Project administrative
(or head office)
expenditures

Financing Develop a financing Analyze financing by

strategy source

Cost of funds

Liquidity and
investment analysis

Financial management Analyze financial Analyze projected

projections financial statements
Develop a strategy Use business planning Perform variance
and financial analysis
projections as ongoing
management tools

Using Microfin 25
Strategic Planning
It is said that if you don't know where you are going, any road will get you there.
During strategic planning, you establish where you want your institution to go by
assessing its current situation and developing an overall plan for the future.

Strategic planning requires you to:

express your institution’s vision and mission

develop broad institutional goals

assess your institution’s past performance in achieving its goals

devise a strategy to achieve your goals and enhance your ability to expand your
outreach and achieve (or maintain) profitability

Although a detailed discussion of strategic planning is outside the scope of this

handbook, “Annex 1, Business Planning Framework” (beginning on page 483)
briefly describes the process, as follows:

Articulate your institution’s mission and goals.

Define your markets and clients.

Conduct an environmental analysis.

Perform an institutional assessment.

Based on the results of these analyses, choose an appropriate strategy—
generally one that focuses on maximizing outreach and profitability.

Define objectives and general activities.

Operational Planning
During operational planning, you develop a detailed action plan to support the
mission, goals and strategy of your strategic plan.

An analysis of your markets and clients indicates the products and services you
should offer, as well as the markets into which you should offer them. Analysis of
your environment provides guidance on where to provide services and identifies
external factors that will affect your choice of marketing channels. An institutional
assessment provides information on how best to provide your services, as reflected in
institutional resources and capacity, financing, and analysis of financial projections.

In the course of operational planning and financial modeling, you may decide to
make certain necessary changes to your strategy or implementation plan in order to
achieve your outreach and profitability goals. If any element of your strategy appears
unachievable, you can reevaluate the strategy or refine your operational plan. For
example, if you cannot achieve the targeted expansion in the timeframe you
anticipated with the funding you budgeted, you can change the strategy to expand
more slowly. Alternatively, you can alter your operational plan by seeking additional
funding sooner than you had originally anticipated.

26 Using Microfin
Financial Modeling
Financial modeling is an integral part of a comprehensive business planning process.
A well-crafted model:

provides a template to develop detailed projections. These projections facilitate

strategic and operational planning, performance/variance analysis, and decision
making—thereby enhancing an institution’s ability to set and achieve goals.

strengthens the financial planning and management skills of key staff. These
skills are vital to the successful implementation of an institution’s mission and
strategy.

1 — Before You Begin: A Best-practice Approach to Planning

Although the first benefit is generally regarded as more useful, the second is often
more important to an institution’s success.

To develop your financial projections:

Review Microfin’s procedural requirements, general approach and operations.

For information on Microfin’s procedural approach, refer to “Overview of
Microfin’s Planning Framework” on page 27.

Review Microfin’s data requirements to ensure that you have all necessary
information available.
For information on Microfin’s data requirements, refer to “Annex 2, Microfin’s
Data Requirements” on page 495.

Enter your data and build your projections using the worksheets in Microfin.

Overview of Microfin’s Planning Framework

Microfin’s step-by-step approach to planning closely follows a best-practice
framework and is appropriate for most microfinance institutions.

The approach concentrates on four key areas of your institution’s operations:

products and services

marketing channels

institutional resources and capacity

financing5

Using the sheets in Microfin, you identify and analyze the crucial factors for each of
these areas, based on your mission and strategic plan (figure 1.2).

5 A detailed analysis of the operations of microfinance institutions is beyond the scope of this handbook.
For a more thorough discussion, see Charles Waterfield and Ann Duval, CARE Savings and Credit
Sourcebook (New York: PACT Publications, 1996), SEEP Network, An Institutional Guide for
Enterprise Development Organizations (New York: PACT Publications, 1993), and Robert Peck
Christen, Banking Services for the Poor: Managing for Financial Success (Washington, D.C.: ACCION
International, 1997).

Using Microfin 27
Figure 1.2 Overview of Microfin’s Business Planning Framework

Strategic Planning Operational Planning Relevant Microfin Sheet

Mission and Goals (Not directly applicable)
However, Microfin does
include an introductory
MODEL SETUP sheet to
enter background
information and establish
modeling parameters
Markets and Clients Products and Services PRODUCTS sheet to
identify and describe loan
and savings products (also
the PRODUCT DESIGN
SHEETS to design Term
Loan Products)
Environmental Analysis Marketing Channels BRANCH/REGION sheet
(depending on modeling
approach) to project loan
and savings activities
(also the PRODUCT
ACTIVITY SHEETS to
project term loan product
activity)
Institutional Assessment Institutional Resources INSTITUTIONAL CAPACITY
and Capacity to identify loan loss
Program
provision policies and
Board/Management institution-level
Human Resources expenditures

Administration BRANCH/REGION sheet

(depending on modeling
approach) to identify
branch-level expenditures
HEAD OFFICE sheet to
identify institution-level
expenditures

28 Using Microfin
Strategic Planning Operational Planning Relevant Microfin Sheet
Financing Financing FINANCING SOURCES
sheet to identify funding
sources and associated
costs, and to establish
liquidity requirements
FINANCING FLOWS sheet
to project sources and uses
of funds, describe

1 — Before You Begin: A Best-practice Approach to Planning

investment strategies, and
identify possible liquidity
shortfalls
Financial Management Financial Management MODEL SETUP sheet to
enter historical financial
data
BRANCH sheet to review
branch-level financial
statements, if appropriate
GRAPHS sheet to review
Microfin’s projections in
graphical form, for easy
interpretation and trend
analysis
SUMMARY sheet to
review summarized
financial statements and
ratios, including data
presented in constant and
external currencies
FINANCIAL STATEMENTS
sheet for institution-wide
financial statements and
indicators
Strategy Ongoing Management SCENARIOS sheet for
sensitivity analysis and
multi-scenario
comparisons (available in
Advanced mode only)
VARIANCE ANALYSIS
sheet for comparing actual
and projected data

Using Microfin 29
Procedure to develop a financial model using Microfin:
1. Create and save your new Microfin model using the Microfin template file.
For additional information, refer to “Chapter 4, Creating a Microfin Model”
on page 105.

Using the NAVIGATOR sheet as a central location, or switchboard, from which

to access and enter data in sheets throughout the model, complete steps two
through ten:

Enter key parameters and historical financial data into the MODEL SETUP sheet.
One of these parameters establishes whether you intend to model the activities
of your entire institution on a consolidated basis, or to separately model the
activities of individual regions or branches. Microfin adapts its structure
somewhat based on your choice, as described below.
For additional information, refer to “Chapter 4, Creating a Microfin Model”
on page 105.
Design your credit and savings products. These products should reflect your clients’
needs, as established by the client and market analysis you performed during strategic
planning.
For additional information, refer to “Chapter 5, Defining Products and Services” on
page 145.
For those products and services that you intend to offer, project the estimated levels
of credit and savings activity. These activity levels represent the engine that drives
your institution’s operations and generates its anticipated interest and fee income. (If
you develop your model using a branch or regional approach, you will develop these
projections separately for each branch or region within your institution.)
For additional information, refer to “Chapter 6, Defining Marketing Channels to
Project Client Activity” on page 185.
Based on your projected activity levels, estimate the institutional resources that you
will require. Estimate your loan loss provision, loan loss reserve and loan write-offs.
Define categories of anticipated expenditures, such as personnel, other operational
expenses and fixed assets.

For additional information, refer to “Chapter 7, Planning Institutional Resources and

Capacity” on page 241.
Project your program-related (i.e., direct) expenditures, reflecting those costs
necessary to achieve your projected level of activity. (If you develop your model
using a branch or regional approach, you will develop these projections separately for
each branch or region within your institution.)
For additional information, refer to “Chapter 8, Projecting Program-related
Resources and Capacity” on page 271.

30 Using Microfin
 After projecting program-related expenditures, project your indirect, administrative
costs (or head office costs, for regional or branch models) by considering the
overhead necessary to support projected program activities. You should also project
institutional development costs.
For additional information, refer to “Chapter 9, Projecting Administrative Resources
and Capacity” on page 319.
Develop a financing strategy to ensure that you have sufficient funding, when
needed, to support your anticipated operations. You analyze your financing
requirements in terms of three distinct areas: operations, portfolio and other assets.
Financing derives from two sources: equity (e.g., earned income, grants and equity

1 — Before You Begin: A Best-practice Approach to Planning

investments) and debt (e.g., loans and savings deposits).
Based upon your estimate of financing requirements and probable sources of
financing, project your cost of funds. Determine whether you are likely to have
excess funds on hand (such as loan funds repaid and not yet disbursed) in order to
project income from investments.
For additional information on financing strategies, refer to “Chapter 10, Developing
a Financing Strategy” on page 351.
Once you have completed an initial set of financial projections, carefully review the
information on the completed Microfin sheets to identify any necessary changes in
your model.
Analyze Microfin’s graphs, summary report, and projected financial statements and
indicators to evaluate whether your operational plan and financial projections are
realistic. For example, you may need to increase or decrease the volume of lending or
savings activity, hire additional staff, accelerate or delay the purchase of assets, or
seek additional sources of funding.
For