Www.ruralfinanceandinvestment.org



Ag Loan Analyzer.xlsm Agricultural Loan Application AppraisalA Microsoft Excel-Based Tool to Assist Loan Officers—Draft User's Guide—1086928232710Ag. Loan AnalyzerVersion 3Ag. Loan AnalyzerVersion 3Developed by FAO TIME \@ "d MMMM yyyy" 18 August 2015Table of ContentsList of AbbreviationsivIntroduction1Origins of the Ag Analyzer Tool and this Manual1Organization of the Manual1About the Intended Audience2About Agricultural Loan Analysis3About the Extended Illustrative Example3Security3Before You Begin: A Preface to Ag Loan Analysis5Data Collection5Overview of the Ag Loan Analyzer’s Features5Time Requirements6Avoid Unrealistic Projections6Overview of the Ag Loan Analyzer Tool7Structure of the Ag Loan Analyzer7The User Interface10Special Pull-Down Menu10Help12Installing and Using the Ag Loan Analyzer Tool13Software Versions and Required Software13User License13Installation Procedure13Preferred Language14Sharing the results across a LAN16Take-On of a New Loan Application’s Data17Creating a New Loan File from Template17Inputting the Client’s Loan Application17Client Info18Past & Ref19Balance Sheet21Outflows21Inflows25Epilogue on Inflows and Outflows26The Base Case Loan 26Finalizing the Loan Projections27The Cash Flow and Loan Planner27Review of the tool’s Financial Analysis and Recommendations29The “Financial Indicators” Analytical Worksheet29CVP Analysis30Charts32Family Expenditures32Comparative BS33Printing33Exporting to Other Windows Software using Windows Clipboard34User Customization of the Ag Loan Analysis Tool37Priorities for Future Development38Annexes39Annex 1 Output Tables40Annex 2 Charts50List of AbbreviationsAbbreviationExplanation / Complete NameAg Loan Analyzer.xlsmThe sophisticated agricultural loan appraisal tool developed by FAO which is the subject of this manual. AGSMThe FAO office dealing with rural finance and agricultural marketingBSThe prospective borrower’s personal Balance SheetCDCompact DiskCVPCost-Volume-Profit Relationships.DOCNormal suffix of Microsoft Word document filesDVDDVD DiskFAOFood and Agriculture Organization of the United NationsGHzGigahertz, a measure of a computer’s processor (chip) speed Ha.Hectare(s)Kg.Kilogramme(s)LANLocal Area (Computer) NetworkMFIMicrofinance InstitutionMTMetric TonsPCPersonal Computer.PDFSuffix of Adobe Acrobat filesVBAVisual Basic for Applications, a programming language used in Microsoft Office applications to automate tasks with macros, menus, command buttons, etc.ToCTable of Contents.xlsTraditional suffix of Microsoft Excel spreadsheet files up through Excel 2003, and still usable, but no longer the default format, by Excel 2007, the latest version..xlsmNew suffix of Microsoft Excel VBA macro-enabled spreadsheet files in Excel 2007 and laterAgricultural Loan Application AppraisalA Microsoft Excel-Based Tool to Assist Loan Officers—Draft User's Guide—IntroductionOrigins of the Ag Loan Analyzer Tool and this ManualThis user guide was written to assist selected agricultural credit practitioners to review a third Beta version of a tool to help them quickly assess medium to large scale agricultural loan applications. This MS Excel spreadsheet is based on a rough draft spreadsheet originally prepared by Calvin Miller, Senior Officer, Rural Finance in FAO and Chet Aeschliman, former FAO Rural Finance Expert further expanded and integrated the tool. Version 3 of the Excel based tool was significantly updated to its current version by A’kos Szebeni, a consultant to the AGS Division in FAO. The tool developers have integrated and modified the original spreadsheet, but the basic structure is still the same. As was the case with the original Miller and Aeschliman versions, this latest version closely follows the (manual) paper-based format recommended by the Agricultural Lending Toolkit developed by Germany's Bankakademie International and FAO. Organization of the ManualFollowing this brief Introductory Chapter, the manual is organized in seven additional technical chapters:Chapter I: IntroductionChapter II: Before You Begin: A Preface to Ag Loan AnalysisThis chapter provides some practical advice to agricultural lenders from the tool’s developer.Chapter III: Overview of the Ag Loan Analyzer ToolThis chapter summarizes the objectives and structure of the spreadsheet tool, as well as briefly discusses what the tool is and is not, what it can do and what it cannot do.Chapter IV: Installing the Ag Loan Analyzer ToolThis chapter takes the user through the simple installation process.Chapter V:Take-On of a New Loan Application’s DataThis is the heart of the manual, where the actual day-to-day work is actually done: how to input data from the agricultural loan application through the automatic preparation of the Base Case loan structuring.Chapter VI:Finalizing the Loan ProjectionsThis next-to-final chapter guides users in moving forward from the Base Case loan proposal through as many as necessary iterations until a final loan package is ultimately produced or the loan is disapproved. It includes explanations and guidelines for using the tool’s multiple analytical tools (analytical tables and a series of seven charts). Chapter VII:User Customization of the Ag Loan Analysis ToolThis is, after all, a Beta version of the ag. loan analyzer tool, and despite all the efforts of all those involved, such a tool cannot be all things to all people. From the beginning, it has been conceived as “Freeware”. Accordingly, this chapter provides some hopefully useful advice to users on how they might modify the tool to meet their particular needs, e.g., adding extra ratios, extending the maximum loan period beyond 24 months, etc. Users are strongly discouraged from modifying this complex tool if they do not have top-notch Excel developer skills.Throughout the manual, extensive use is made of illustrations of actual screens, so that users can check their own computer screens against what the manual prescribes. The illustrations (screen dumps) used in this manual were generated using Excel 2013. Those users employing Excel 2010, 2007 or 2003 will see basically the same thing, although Excel’s main menu differs considerably. The main effect is that the special pull-down menu element inserted into Excel’s own main menu that the user will use to manage tool operations appears directly to the right of Excel’s main menu in Excel 2003, while in the 2013, 2010 and 2007 versions, it appears as a sub-menu within Excel’s “Add-In” main menu element. Despite the slightly different appearance, the special pull-down menu works exactly the same in all versions.Users probably know by now that the manual is currently available in both Word and Adobe Acrobat (.PDF) formats:Ag Loan Analyzer.doc (Word)Ag Loan Analyzer.pdf (Adobe)Printed versions of the manual are only available during courses sponsored by FAO. In keeping with the “freeware” status of the program, the general public is free to print and distribute the manual. However, the FAO strictly forbids the sale of the manual or the tool itself without the prior written accord by the organization. This restriction applies to this and all future versions, revisions, enhancements of the tool, as well as additions incorporated by third parties. The current and future versions of the software will be available on FAO’s website.About the Intended AudienceThe current agricultural loan appraisal tool is intended to be used by professional rural lenders in developing countries that can operate in one of three languages: English, French or Spanish. If the tool proves popular among lenders trying the Beta version, FAO will endeavor to incorporate a facility for users to add a fourth language chosen by the user him/herself to the three existing operating languages. About Agricultural Loan AnalysisAs all practitioners of rural and agricultural lending know, agricultural lending is expensive and the risks high. Furthermore, collecting the information required to use this tool effectively is equally expensive. Accordingly, most users will only use this tool for larger loans to medium to large farms. To go through all these procedures for a small village bank loan of $50 or $100 is probably overkill. Accordingly, its primary intended use will be by formal sector lenders, typically agricultural and development banks, commercial banks and larger credit unions and microfinance institutions (MFIs) granting medium and large loans to farmers and livestock raisers.About the Illustrated ExampleThe ag. loan analyzer software is distributed in two versions, one complete loan dossier showing the loan of a fictitious farming family, Akoni Ndong Mba, and a blank, data-less tool ready to accept data for a new agricultural loan application. Before plunging into trying your own borrowers’ data on your new tool, the developer strongly urges new users to thoroughly study this extensively illustrated example loan, going through it while reading the manual from beginning to end. Both versions (with and without sample data) of the tools are available in Excel 2013, 2010, 2007 and 2003 versions.SecurityThis tool makes extensive use of Microsoft’s Visual Basic for Applications (VBA) programming language. In fact, usage of this programming code is absolutely necessary to effectively use the tool. However, because VBA code is often used by malicious persons as a place to hide viruses and, recognizing that the vast majority of Microsoft Excel users are not VBA programmers, the ability to execute VBA code is turned off in Excel by default. Accordingly, in order to use this tool, users will have to specifically authorize Excel to execute macros. In Excel 2003, this can be accomplished within the Macros menu system. The developer recommends that users of Excel 2003 set their macro security level at either medium or low. Those using Excel 2013, 2010 and 2007 can authorize use of macros through the Excel Options command button, then clicking on the Trust Center, clicking on Trust Center Settings, and finally clicking on “Enable All Macros.” Potential users unwilling to lower their security settings a bit simply will be unable to use this tool. The tool will not work properly unless the user authorizes the use of macros. There is no workaround possible.Happy testing, dear readers. Please do let either the developers themselves, or preferably FAO know your frank observations and recommendations for improvement of the tool itself, as well as this manual and its distribution network. Although every effort was made to make the tool “bug-free”, to err is human, and particularly in Windows software development, errors do creep in unexpectedly where you least expect them. Accordingly, in the unlikely event of finding a “bug”, we’d equally appreciate it if you would send us a clear description of the possible bug and when and how it occurred.Thank you.Before You Begin: A Preface to Ag Loan AnalysisData CollectionThis agricultural loan appraisal tool was developed to be an integral part of the FAO’s financial and management training curriculum. Accordingly, it assumes that users will be using loan application forms and systems similar to those described in the FAO’s Agricultural Production Lending Toolkit. If an institution planning to use the tool does not currently use forms and procedures similar to those recommended by Bankakademie-FAO it should probably strongly consider revising their institution’s own loan application forms. This is not to say that lending institutions should throw out all their existing procedures, but rather to reorganize and reformat the forms they process to mirror as much as possible this model which tailor fits loan planning according to the needs and cash flows of the client. If a particular institution gathers information it considers essential but which is not in the Bankakademie-FAO model, they can continue to do so, but it may well want to reformat and reconfigure their forms to align with that model as possible. This will greatly ease the data take-on burden. Not to do so means that users will be quickly frustrated, jumping haphazardly around the tool’s various parts as they proceed through the take-on of loan application data.Overview of the Ag Loan Analyzer’s FeaturesAg Loan Analyzer.xls, as previously noted, is a tool to permit agricultural lenders to better analyze and structure loans to farmers. Its main features are the following:A “Control Panel” where users input basic information (client name and account number, currency information, formatting and language preferences, etc.)A pull-down menu exclusively devoted to the tool which has been integrated as a new element into the Excel main menu (or “Ribbon” starting with Excel 2007).Multiple alternative means of navigating the 20 worksheets making up the Excel spreadsheet tool. These include clicking on each worksheet’s tab; selecting the desired sheet from a “Table of Contents” (“ToC”) list (go instantly to the ToC by simultaneously pressing the Ctrl, Shift and T keys); choosing it from a dialog box accessed from the tool’s pull-down menu; or using the habitual “Ctrl - Page Up” and “Ctrl - Page Down” key sequences to move to the preceding or following sheet, respectively. If you get lost, you can always instantly return to the Control Panel by simultaneously pressing the Ctrl, Shift and A keys.Five data sheets to permit users to take on data on the borrower, his/er family, crops and livestock husbandry business, current balance sheet, proposed loan amount and repayment preferences, as well as specific estimated items of income and expenditure. Once these five data sheets are completed, the tool itself proposes a “base case” loan structuring.Once the base case is available, the tool provides an analytical framework consisting of :A ratio analysis of the loan’s apparent profitability, proposed guarantees and sureties and the borrower’s balance sheet strength, solvency, and degree of liquidityDetailed estimated monthly and quarterly cash flow statementsA new and expanded sensitivity analysis that allows loan officers to positively and negatively “shock” both price and yield inputs when determining clients’ cash outflows and inflowsComparative estimated balance sheets, before the loan, immediately after the loan and after the loan will have been repaidA series of seven charts to graphically illustrate what for many are difficult-to-digest tabular-form multi-columnar analysesA much-improved and more flexible “Loan Calculator” that permits the loan officer, in collaboration with the prospective borrower, to adjust the loan structure as necessary and desired away from the Base Case Loan automatically proposed by the tool. Within the loan calculator, the user can change the total amount of the loan, the disbursement schedule, repayment method, the number of installments, the interest rate and the month the repayment must begin. Easy printing of these iterative versions and the final loan amortization schedule and any or all of the various analysesTime RequirementsAs previously noted, agricultural lending is expensive, and depending on the take-on speed of the person doing the data entry, as well as the volume of individual income and expenditure items specified, it may take anywhere from 15 to 60 minutes to input all the required data for a single loan. For that reason, the tool is best used for larger loans to more successful medium to large scale farmers. It is not intended for use by microfinance institutions making small, unsecured loans, but such users can also utilize it if they so choose, assuming that the cost of doing so is not prohibitive.Avoiding Unrealistic ProjectionsThis tool is a perfect example of the old adage about “Garbage In, Garbage Out”. Despite the considerable investment of time and money in creating this powerful tool, if it is not used properly, the results will likely be disappointing. All possible efforts should be undertaken to obtain reliable information, especially on the borrowing family’s on- and off-farm expenditures. Underestimating or inputting incorrect data will often result in what initially appeared to be sound loans going sour in practice.Overview of the Ag Loan Analyzer ToolStructure of the Ag Loan AnalyzerOne of the fundamental changes made in this tool from the original tool developed by Calvin Miller, FAO Senior Officer, Rural Finance, was the addition of a Control Panel, as shown in the following screen dump:This is the screen that first appears (after a brief welcome message) when the user loads the dummy file analyzing a fictitious loan application from a certain Mr. Akoni Ndong Mba. Note how most of the fields requiring input on the Control Panel (and the five data input worksheets) have balloon mini-instructions concerning the data required. If the “bubbles” get in your way, just drag them aside. As shown above, some of these Control Panel fields also contain their own pull-down menus from which the user is obliged make a choice. Typing in inappropriate data will produce a swift refusal by Excel. The purpose of the Control Panel is to provide up front, once and for all, a place for the user to input certain basic information on the borrower and the proposed loan and which are used throughout the spreadsheet. Wherever else in the spreadsheet this information is needed, it is automatically fed there by formula, so that users won't have to re-enter the same data multiple times. This includes information about the borrower's and lender's name, local labour daily rates, production units, currencies used, desired presentation formats (wanting to present monetary tables in ‘000s, for example), etc. When performing the appraisal of a new loan application, users should always complete this sheet before proceeding to the other sheets. As in the succeeding data input worksheets, the user can move from one input cell to the next simply by pressing the Tab key. Try it; it’s much faster than clicking on each successive field with the mouse.26670082486500Notice also that the author has centralized a number of important commands by adding a new element to Excel's main menu (see the following screen dump). Clicking on the “Add-Ins” Excel main menu item entitled "Ag Loan Analyzer" from any sheet will produce the pull-down menu indicated in the screen shown immediately below. To be precise, this menu item will be a sub-menu of Excel’s “Add-Ins” main menu element if you use Excel 2013, 2010 or 2007. The new menu element appears at the extreme right on the Excel main menu line in Excel 2003. This major alteration of Excel’s main menu is done through VBA programming code. In fact, the spreadsheet has about 5,000 lines of VBA code, which largely explains why the tool’s file size is so large (around 1 MB). The use and utility of the special pull-down menu will be discussed below.Should the user find him/herself within a worksheet far to the right of the Control Panel, and the Control Panel’s tab is not visible, s/he may rapidly return to the Control Panel worksheet by using the key sequence Ctrl-Shift-A. Try to remember this; you’ll be glad you did later on.80645154114500This spreadsheet tool is composed of 24 closely interrelated worksheets; 6 of these are "housekeeping" worksheets which are hidden to keep desktop clutter to a minimum, as well as to prevent the user from accidentally damaging the complicated and essential background formulas that achieve the seemingly-miraculous results. The different sheets are also the subject of a Table of Contents worksheet entitled "ToC" (worksheet 3, illustrated below). Note the multi-colored tabs at the foot of the spreadsheet representing the different worksheets. To view any sheet, simply click on its tab. If a desired sheet’s tab is not visible, use the scrolling arrows at each end of the series of tabs to view tabs to the left or right of those currently displayed. Alternatively, you can use Ctrl-PgUp to go to a previous sheet, or Ctrl-PgDn to move to the next sheet. Arguably the easiest method of getting around, though, is to use the key sequence Shift-Ctrl-T to go to the ToC sheet (see screen dump above), and then clicking on the desired sheet's button. Try this feature and see how easy navigation can be! Finally, users can use the trilingual “Display Documents” dialog box (illustrated at the top right of the following page) accessible through the special pull-down main menu element. Once the dialog box appears, click on the desired document, click the Ok button, and the desired worksheet is instantly activated and displayed. In summary, the developers have, as readers can see, deliberately tried to build in redundancy and make it easy to get around in a variety of ways. The User Interface23317202095500The main activities of the user are to fill in the Control Panel, then input data from the prospective borrower’s loan application into the five data sheets following (to the right of) the Table of Contents, then once that is done, based on the analysis provided by the tool, modify as necessary how the loan is structured in as many iterations as necessary, and print-out of the final loan documents, amortization schedule, analyses and charts. To accomplish these feats, the user will avail him/herself of the various alternative means of navigation cited in the previous section. He will also make frequent use of the various routines built into the special pull-down menu element added to Excel’s own main me0nu. S/he will deal with dialog boxes similar to the one shown just above, and use (“click” on) “command buttons” affixed to the various worksheets, as well respond to the various dialog boxes built into the tool. In short, s/he will use all the standard features of modern Windows applications.Special Pull-Down MenuAt this point, let us discuss a bit further the special, 12-element pull-down menu inserted by the tool into Excel’s main menu when the Excel tool is loaded into memory (and removed when the file is closed, of course). This menu assists the user to effect what would be complex and tedious operations if the pull-down menu did not exist. After processing a few loan applications, users will become very comfortable using this menu and will come to rely on it heavily. The tasks the user can accomplish using the pull-down menu are, briefly, the following:Display a Document This displays a dialog box which allows the user to select a particular worksheet to display. Only unhidden worksheets are available here.Display Charts This simply moves the user instantaneously to the Charts worksheet.Print Selected Document(s) Selection of this menu element displays a dialog box that permits the user to print either a single document (i.e., a worksheet) or all documents at once.Print Charts This sends all seven charts to the printer.Save Ag Loan Analyzer File This element has four sub-menus, saving the file with its original name to the orthodox location, to a sub-folder within the C:\MBWIN folder or, alternatively, with a different name or to a different folder. When saving borrower loan files, users should use the second or third option, using the borrower’s full name (last first middle) plus the loan number as the file name to be able to relocate the file easily later on. MB Win users will use that programm’s naming convention.Erase All User-Input Data This extended macro deletes the borrower’s name and all the information related to his/her proposed loan will be deleted, clearing data fields throughout the data input worksheets. This will produce temporary error messages in some cells containing formulae, but this is not really an error to worry about. As soon as new data in typed in, these error messages will disappear.Change No. of Spouses This feature is primarily for areas of the world where polygamy is common, particularly Africa and other Moslem countries. It permits the user to specify up to four spouses and their respective activities and income. A command button on the Client Info worksheet does the same thing.Change Language in Tables/Charts This extensive macro is intended to change all the screen messages used to communicate with the user into the user’s preferred language. Although this multi-language feature is currently deactivated during the testing period, when re-activated it will let the user choose either English, French or Spanish, but there are also plans to include a fourth user-defined language in a subsequent version. If eventually incorporated into the tool, any language (and alphabet) supported by Windows could conceivably become one of the tool’s languages, if the user is willing to take the time to translate the nearly 1,000 items in the messages table.Hide Empty Columns in Chrono Tables The tool permits processing of agricultural loans having durations of from 3 to 24 months. This subroutine temporarily hides the columns not needed. For example, the last 14 month columns would be hidden for a 10-month loan.Adjust Size of Input/Output Tables By default, most cost and income tables in the Inflows and Ouflows tables are limited to 15 lines of input. However, more complex costings may require additional lines in a particular section. This menu, when executed, permits the user to open up 15, 30 or a maximum of 50 lines per table. If the user needs even more lines, then s/he should, if possible, input the 49 most significant items in first and group others in a final line. Do not try to insert additional lines, or you may destroy the spreadsheet’s internal logic.Use Full Screen This menu item frees up a bit more space on the desktop by minimizing information displayed at the top and the bottom of the screen. To subsequently return to a normal screen, just click on the tiny “Maximize” button in the upper right-hand corner of the screen.HelpUnfortunately, for budgetary reasons, neither an on-line help facility nor a tutorial has been integrated into the tool. However, if feedback from users of this Beta version of the tool is positive in general, FAO hopes to be able to include such facilities in the future, and make them all available on their respective Web sites. Similarly, no technical support of the product is available by either telephone or on-line, but again, if there is sufficient demand from initial users, these features can eventually be added on, as well. Users are free to and are actually encouraged to submit proposed improvements, both technical and in user-friendliness, as well as report any perceived “bugs” they may identify, along with clear explanations of how, when and where the problem occurred. Within budgetary limitations FAO will do its best to incorporate as many of these requested enhancements and improvements as possible in future updates and place them on its website.Installing and Using the Ag Loan Analyzer ToolSoftware Versions and Required HardwareTo be able to use the agricultural loan appraisal tool, users will need to have Microsoft Excel Versions 2003, 2007 or later installed on their computers. Earlier versions of Excel simply will not be able to load the tool into memory or use the extensive VBA-based functionality. At about 1.0 megabytes, the tool is a bit large compared to Excel files we normally use in the office, but it’s still small enough that most PCs with Excel 2003 or 2007 installed can utilize it. Speed is not really an issue with this tool; even on an older computer, the spreadsheet recalculates instantaneously each time the user inputs new data and hits the Enter key. The tool has not been tested on an Apple computer, so Apple aficionados out there should beware, particularly of Excel for Apple’s different date functions. Apart from Apple computers, however, nearly any modern office computer running Windows should be able to use the tool, provided they have a fairly recent version of Excel (2003 or later) and Windows (XP, Vista or later). Users should also have a good Windows Word Processor such as Word or Word Perfect, and presentation software such as Microsoft PowerPoint, so that the tool’s various analyses and charts can be easily exported into reports and presentations.User LicenseBy design, the sponsors of software generally want their software to be licensed. To the contrary, FAO wants it to remain the in “Freeware” realm but due recognition of FAO as the originator and developer is required. Accordingly, users can download it and use it for free with that recognition. If they have the technical skills necessary to modify this complex, yet easy to use, spreadsheet, they can even modify whatever they want to make it meet their own particular needs, such as adding that special financial ratio the developer didn’t include or adding an additional analytical table or chart. They should not underestimate the difficulty of doing so, however, and might easily upset the delicate programming that makes the officially-released version work properly. If possible, users finding the tool useful should communicate their proposals to the developers directly, but preferably channel these through the FAO, which in the future should have a web link or a link to a support network. Users are encouraged to propose modifications or improvements, or cite suspected “bugs” if the find them. FAO will endeavor to incorporate as many of these proposals as possible into future versions, within budgetary constraints, should the software prove useful to a good number of rural lenders.Installation ProcedureIn general, installing the tool on a PC is extremely simple. Users need only to follow the following two simple steps:Create a folder in their C:\ drive entitled Ag Loan AnalyzerCopy the files downloaded from the FAO website into the C:\Ag Loan Analyzer folder(Note: do not work directly from the downloaded file, but rather from the one you have saved in the folder.)That’s all there is to it! Once you complete these simple steps, you’re ready to launch Excel and open the tool. The download includes both all excel versions, so only use the version appropriate to your computer. As stated elsewhere, however, the developers highly recommend that new users thoroughly study the sample loan files provided before jumping into inputting their own loans. Users can, of course, install the software to a folder other than the one proposed above. The software will still work, with one exception. That is, within the special pull-down menu element inserted to the right of Excel’s main menu, there’s a routine where users have a choice of saving a file to either the above-mentioned folder or in another user-specified folder. If users choosing to install their files to an unorthodox folder name try, after installing the software, to use the menu element specifying saving in the standard folder, they will, of course, be confronted with an error message, after which they’ll have to escape using the Esc key one or more times.1905018859500Preferred LanguageOne last point: the loan appraisal tool is in theory trilingual, and as in previous versions, designed to operate in English, French or Spanish. The default language upon opening the file the first time is English, so users preferring French or Spanish should as a first order of business choose their preferred language (do this by clicking on the one of the three buttons on the welcome page). After selecting his/her preferred language, the operating language will be changed. Another important point concerning languages: multi-language users of this tool should resist the temptation to switch languages while they’re working on a particular loan. To do so will in many cases produce erroneous results. For example, if you start out in French and answer a certain question with a “Oui” and then change to English after starting data input, the formulas will be looking for a “Yes” instead of “Oui”, and some formulae will accordingly yield inaccurate results. Unfortunately for French and Spanish speakers, unless users go through some fairly technical but easy steps, every time they open and save this file, even after choosing a preferred language using the above dialog box, the next time they open the file, the tool re-opens in English. This can be frustrating. To facilitate use by Francophones and Hispanophones, the following measures can be taken by each to convert the basic file to one that always opens in their preferred language:For Francophones:Open the template Ag Loan Analyzer.xls (Excel 2003) or Ag Loan Analyzer.xlsm (Excel 2007, 2010, 2013)Open the VBA Editor (Alt-F11)Open the This Workbook VBA Code in the VBA Editor with a double-clickChange the first line of code to “Call DisplayFrench” (DisplayFrench is all one word)Change the second line of code to “Call ChangeTabNamesToFrench”Go to the end of the subroutine and change the sixth to last line of code to “Call AjouterNouveauMenu” (again, “AjouterNouveauMenu” is one word)Still in the This Workbook VBA code in the VBA Editor, and in the “Private Sub Workbook_BeforeClose”, change the next to final line to “Call DeleteFrenchMenu”.Save the file under any locally-appropriate file name to whatever folder they wish.For Spanish speakers:Open the template Ag Loan Analyzer.xls (Excel 2003) or Ag Loan Analyzer.xlsm (Excel 2007, 2010, 2013)Open the VBA Editor (Alt-F11)Open the This Workbook VBA Code in the VBA Editor with a double-clickChange the first line of code to “Call DisplaySpanish” (DisplaySpanish is all one word)Change the second line of code to “Call ChangeTabNamesToSpanish”Go to the end of the subroutine and change the sixth to last line of code to “Call A?adirNuevoMenu” (again, “A?adirNuevoMenu” is one word)Still in the This Workbook VBA code in the VBA Editor, and in the “Private Sub Workbook_BeforeClose”, change the next to final line to “Call DeleteSpanishMenu”.Save the file under any locally-appropriate file name to whatever folder they wish.The next time the user opens the file, it will appear and stay in his/her preferred language.19380207175500The first thing one sees after opening the tool is a welcome dialog box. It is automatically displayed for a few seconds, then disappears. The welcome dialog appears as shown to the right.Sharing the Results across a LANIn larger lending institutions using this software where several staff members are frequently inputting loan application data into the tool and negotiating loan applications with potential borrowers, it will probably be wise to install the C:\Ag Loan Analyzer folder on one of the lending institution’s LAN servers, and have the LAN administrator “share” the folder to each authorized user. Because many senior agricultural loan officers are either not very computer savvy or are very slow at inputting data, the lending institution may employ data take-on staff so that the loan officers are not bogged down in typing all day. Once the server’s folder is shared among all authorized staff, then the data input staff will just have to communicate the file names to the appropriate loan officers, who can then use the analysis to access the applications and negotiate with the prospective borrower. Finally, those using this tool frequently will want to place a shortcut on their desktop to facilitate its easy location and launching.Take-On of a New Loan Application’s DataCreating a New Loan File from the Template/ Clearing User-Input loan dataThe procedure here is straightforward. When a loan officer or his/her assistant need to process several loan applications on a particular day, they should open the master template which does not have any user data in it, and make copies of the file in the name of each borrower, saving each one to an agreed-upon folder, most probably a sub-folder of the C:\Ag Loan Analyzer folder. If a borrower has previous loans in the system, name the files something like “John Smith2” or “John Smith3”, for example, to distinguish their different loans. In order to take account of prior loans to prospective borrowers in the analysis, it would be useful if the entire loan appraisal tool could eventually be moved to a database platform where performance on previous loans to the prospective borrower could be integrated into the analysis. Previous loans are currently not a part of the analysis in the tool, except to the extent that borrowers themselves cite the lender as having provided him/her agricultural credit. Use of a good EDP system like the FAO-GTZ MicroBanking system (MBWin) would obviate the need for a separate monitoring system.Once all the day’s loan appraisal “shells” have been created, loan officers and their assistants, if any, can begin to input the prospective borrowers’ loan application data into the loan appraiser tool, re-saving the file after each session during which modifications have been incorporated.Inputting the Client’s Loan Application12573007937500The procedure is fairly simple here, as well. After the brief appearance of the Welcome Dialog Box, the user first completely fills out each field on the Control Panel. These fields are very important data, and each one is important, so do not skip any. Some of these fields also have pull-down menus from which users must choose; inappropriate data will be swiftly and categorically refused. An example of this is shown in the screen shown above, where the user must choose their principal unit of production.1765308191500The next step is to fill out the five data entry worksheets to the right of the ToC sheet. Users can navigate to these different sheets using the variety of means previously discussed. Each of the five data sheets are discussed below in turn:Client Info3429016764000Parts 1 and 2 at the top of this worksheet contain basic demographic data on the prospective borrower and his family. The top part of the sheet appears as in the illustration at the right.The bottom part of the Client Info worksheet contains data on the prospective borrower’s (and his family’s) farming activities and experience, as shown below to the right.Note the different colours on the data entry worksheets. Cells shown in white with black font are brought in automatically from data previously specified in the Control Panel. Cells shown in light blue with blue text are input zones, where the user will input information on the borrower. Certain areas throughout the worksheets contain protected cells, and the user is not allowed to enter data there. Attempts to do so will be met with a prompt reprimand from Excel. This same colour code is used throughout the 4 other input worksheets as well. What may appear a bit strange to users from regions other than Africa or the Middle East is the possibility of specifying up to four spouses, in accordance with African and Moslem practice. Other regions can simply ignore the second, third and fourth spouse. Remember that to quickly fill out these input forms, you can give your mouse a rest and simply hit the Tab key with your left little finger to move to each succeeding data input field. You’ll really save considerable time by bypassing the mouse.In the five data input sheets, you'll notice that the software developers have made considerable use of Excel's “concatenation” function, which facilitates the combination of multiple pieces of text and numeric data into a single phrase. For example, note the right-hand column heading, "Married to Akoni since…", a perfect example of concatenating a stock phrase with the borrower's name, as specified in the Control Panel.In addition to specifying the members of the borrower's household, the Client Info worksheet also is where the user specifies the household members' various economic activities, as well as up to ten different field crops grown and up to ten different types of livestock raised. It is important to be as complete as possible here, since as we'll see later, these types of crops and livestock are automatically passed on to special pull-down menus in the Inflows and Outflows worksheets where you specify inputs and outputs with respect to each specified crop or animal species. Aquaculture is a special case, and should be considered not a form of livestock, but rather as a field crop, since fish ponds' production yields are conventionally measured just like field crops, e.g., in tons (of fish) per hectare. If the prospective borrower has more than ten crops or ten livestock species, please specify the nine most important ones, and lump the others into an "Other" category.Users will also note at the foot of the Client Info worksheet the existence of a number of hopefully self-explanatory “command buttons.” In fact, buttons are widely used throughout the tool. These buttons are intended to help users quickly carry out certain tasks with a minimum of effort, e.g., the button that moves the cursor to the top of the page, i.e., to the very first data input field on the worksheet. It is equally important to point out the Crops Complete button. When you are sure that you have specified all crops and livestock planned for the period of the proposed loan, then kindly click on this button, which permits the Excel workbook to prepare some other tables you'll use shortly. If you discover later on, though, that you've omitted an important crop or livestock species, you can always come back and add it, but don't forget to click on the Crops Complete button again when you will have added the additional types of crops or livestock.120967515303500Past & RefThe next data worksheet users need to fill out is the “Past & Ref” sheet. The abbreviated title refers, of course, to previous loans and references on the farmer’s reliability and farm management abilities. The top part appears below.1747520127127000Just below the top part of the worksheet is the subsection on the client’s reliability and farm and risk management abilities. The second and bottom part appears approximately as shown below. Past & Ref is also where the user captures an assessment of the prospective borrower's character, reputation, farm and risk management capacities, as well as where up to three references are specified. The source of this information is the loan officer himself, if he knows the prospective borrower well enough, or s/he may seek this information from another knowledgeable person, typically a local extension agent or inputs dealer. The bottom part of Past & Ref is shown to the right. Note also the borrower’s preferred loan amount and terms, which may later be negotiated while using this tool and eventually reach a final loan package all parties can agree on.-71755242697000The Past & Ref worksheet is also where you input information on the prospective borrower's previous borrowings and current debts. You also specify the approximate desired amount (which can and probably will change as the analysis and negotiations proceed) and terms (duration, grace period, and number of monthly installments) the borrower is proposing. The model assumes monthly repayment; for balloon loans with a single payment after harvest, just specify a single monthly installment with a long grace period.Balance Sheet The top part of this data worksheet summarizes the farmer’s existing significant farm and personal assets. The bottom part provides an estimated personal balance sheet at the date of the loan application. The two parts of the worksheet are shown to the right and below in two separate screen dumps. 211328063500 worksheet's name is somewhat of a misnomer, since it contains several elements other than just the borrower's current balance sheet. It also contains a list of the prospective borrower's most important assets and their approxi mate sales value, as well as a list of collateral and/or sureties s/he proposes.Notice once again the information balloons to help explain to users exactly what information is required in particular fields. Since each section of the fixed asset inventory has only five lines, list the four most important ones, and lump any significant other items into an "Other" category.OutflowsIt is in the Inflows and Outflows input sheets that the software developer has most considerably altered Calvin Miller’s original Excel workbook, hopefully for the better. While this offered optimal flexibility in loan planning, one of the weaknesses of the version was that users had to specify the inflows and outflows in one place and then re-enter them into the summary cash flow projection. The developers have altered these two input sheets, therefore, so that users can enter this information just once in the five data input tables, and the spreadsheet takes this information and generates the cash flow projections automatically. In the case of the Outflows worksheet, there is a first table in which you specify inputs to be used with respect to specific crops, just as you do with crop sales in the Inflows worksheet (see below), choosing the crop from the pull-down menu. A second table permits you to specify inputs planned for each of the livestock species you previously specified. Yet a third table allows the user to specify required labour for each specific crop or animal type. The labour table also permits a special, unallocated quantity of labour not associated with any particular crop or type of livestock. You must also specify whether each element of labour involves (unpaid) family labour or hired labourers. The spreadsheet will apply the daily labour rates you specified on the Control Panel. Below the Labour table, the user will find an input zone for equipment to be financed by the proposed loan. The next table on this worksheet is intended to capture any other farm costs not easily allocated to particular crops, e.g., water usage fees, sharecropper fees, taxes, etc. The final table is where the prospective borrower's family expenses are input. Note the Max Work Space command button at the top of the Outflows sheet, which lets the user expand the number of available lines in the various tables (up to a maximum of 50, in most cases). This command can also be given through the special pull-down menu added to the Excel 2003 main menu or the “Add-In” menu in Excel 2007.The Outflows worksheet is by far the lengthiest of all the five data worksheets, since it collects all the estimated cost data on what the proposed loan would finance, including farm investment costs, operating costs, and family expenses. All of the Outflows worksheet tables use the same logic. That is, the user provides a description of the cost item, its unit cost, the number of units per month, and the month and year the cost starts and stops. Everything else, i.e., the actual cash flow projections, is automatically calculated by some rather involved formulae. It is important to understand this concept before proceeding, since the same concept is also used in the Inflows section capturing the farm’s projected sales and non-farm income.18649952739390001864995889000The top part of the Outflows work sheet is where costs related to crops (including aquaculture) and livestock rearing are entered. Note that when choosing a particular crop or livestock species, you must choose from pull-down menus that are based on what the user specified in the Past & Ref worksheet. If you forget to include a crop or livestock species, please return to Past & Ref and specify them before returning to Outflows to input their related costs. Note also that users must specify whether the labour consumed is paid (outside labourers) or family labor. That (top) part of the worksheet appears at the bottom of the previous page, and the other sections are shown to the right and below.0115443000As previously indicated, the final (bottom) part of the Outflows worksheet concerns non-farm (family) expenditures, which given the frequent low profitability of farming activities, can often be extremely important in determining whether a loan is likely to be repaid. The foot of the Outflows worksheet also is where data is collected on costs not directly attributable to a single crop, such as land rent or sharecropper fees for land used for multiple uses. Use of this part of the worksheet is shown in the following illustration:A number of observations are important here. First of all, when you click on or enter the leftmost blue data input column, a pull-down arrow will appear based on the crops and livestock species you specified previously, as shown in the screen dump above. Choose the crop or species concerned and hit the Tab key to go to the next field (if you insist on using the mouse, you will take five times as long, so please push your mouse aside while doing data input), and continue entering data. All you have to do is enter the unit costs and quantities, and the computer calculates total cost (in a blue column, of course). Notice the four blue columns to the right. Here you specify the month(s) the cost item starts and ends. If a cost is all incurred in a single month, the beginning and ending month both will be the same, obviously. However, suppose you expect to buy 4 tons of NPK fertilizer, but half will be bought in one month, and half in the following month. In that case, the "monthly" amount should be 2 Tons, so that the total (eventually calculated by the computer) will yield 4 tons. If that seems too complicated, just show 2 Tons as one item and another 2 Tons as a second item on another line. The livestock and livestock byproducts tables work in exactly the same manner. Notice that most of the cost and income input tables have only 15 lines on which users can input income items. What if you need more lines? Good question! Now you're really getting into the swing of things. See the “Max Work Space” button at the top of the worksheet? Just click it, and the number of lines in each table expands to 50 lines. If that's way too many lines for your needs, you can also specify an intermediary quantity of 30 lines by clicking (yes, you're allowed to use the mouse for that!) on the special Ag Loan Analyzer pull-down menu, choosing "Adjust Size of Input/Output Tables…" and selecting the "Medium" option which appears along with "Standard" and "Maximum." This expands (or contracts) both the Inflows and Outflows tables. The "Medium" option makes 30 lines available in each table.InflowsAs previously indicated, the Inflows worksheet uses the same financial projection techniques as the Outflows worksheet. That is, the user provides a description of each type of crop, livestock or by-product being sold, its estimated unit selling price, the number to be sold and consumed by the family each month, and the month and year in which the sales will start and stop. The Inflows worksheet shows income from three different types of sales: crops, livestock and livestock by-products (skins, eggs, etc.). The final section of this worksheet also collects information on income from non-agricultural sources. This final section differs from the others in both the Outflows and Inflows worksheets, in that the user inputs an amount directly under the month concerned, instead of specifying beginning and ending months. The screen dumps immediately below show both the top and the bottom parts of the Inflows worksheet.The final table on the Inflows worksheet is a freeform zone where you can specify up to five types of other kinds of income, e.g., remittances and salary income.0000-4000532702500Sensitivity Analysis – Outflows and Inflows:On both the Inflows and Outflows worksheets, you will notice an area in the excel banner that has a sign with the text “Click Plus/Minus Sign to Open/Close Sensitivity Analysis”. Once you click on the sign, a number of columns will be opened, allowing you to create a monthly sensitivity analysis for not only anticipated changes in the price of the commodity, but in the crop yields as well. The mechanism is rather simple – if you believe, for example, that due to macroeconomic or local market circumstances, the price of the crop reported by the farmer is 22580604762500actually likely to increase 1.5% every month for 3 months, and then decline by 5%, you would find the relevant months, and enter that information accordingly. The picture below illustrates this exact example. If however, you believe that due to a local pest infestation, the customer’s yields may be about 5% less that he/she reported, you can simply adjust the column entitled “Yield Adjustment in % (8)” for the relevant month, and it will automatically take this factor into calculating the relevant cash in- or outflow. Also, once the yield has been adjusted, you will be able to check the resulting effect by looking at the “Adjusted Yield (3 x 8)” column. This adjustment is illustrated in the picture below.6350018542000How to Recognize Cash Inflows and Outflows The following section gives a few examples on how to recognize the cash inflows and outflows, which can be seen on the table on the top of the next page. Step 1: input the first calendar month and year in which the CF is recognizedStep 2: input the last calendar month and year in which the CF is recognizedExample 1: one time, discrete CF$1,000 cash outflow occurring one time, in Jan. 2016Example 2: continuous, recurring CF$1,000 cash outflow occurring every month from Jan. 2016 – May 2016Example 3: non-continuous, recurring CF$1000 cash outflow occurring every month from Jan. 2016 – May 2016, and then again from July 2016 to Dec. 20169525026987500Epilogue on Inflows and Outflows:On both the Inflows and Outflows worksheets, notice the (mostly off-screen) 25 columns to the right of the cash inflow and outflow tables. This is where, through the magic of rather involved Excel array manipulation formulas, your specifications as to which month(s) particular cash flows take place are transformed into monetary projections associated with each month of the loan period. Users are not allowed to alter these far-right columns, but the software developer left them un-hid so that users not convinced that the computer has placed the information under the right month column can satisfy their curiosity.The Base Case Loan Once all the loan application data have been input into the five data worksheets, the spreadsheet will automatically (1) calculate, based on the data you just input, an initial loan plan; (2) perform a detailed financial analysis; (3) redraw all seven charts; and (4) prepare the estimated comparative balance sheets (pre-loan, post-disbursement, and post-repayment). But more than anything else, it will, in the (monthly) Cash Flow and Loan Planner and Quarterly Cash Flow Projections, specify what amount(s) to disburse when, and draft what the tool feels is an appropriate repayment plan closely fitting the farmer’s positive cash flow. That repayment plan is shown in both the twin monthly and quarterly cash flow projections. It is also available along with alternative fixed principal and annuity (mortgage type) repayment plans in the Loan Calculator worksheet. Where the user goes from there is the subject of the next chapter.Finalizing the Loan ProjectionsCash Flow and Loan PlannerIf you've gotten this far, congratulations! 90% of the work is already done! When you will have completed all the data input, all that remains is to customize the prospective borrower's loan plan, if necessary. You do that by toggling back and forth between the Cash Flow and Loan Planner and the Loan Calculator worksheets. As stated at the end of the previous chapter, once you've completed all the data input, the spreadsheet tool has also automatically prepared a draft loan disbursement and repayment schedule that it figures is appropriate to the cash flows you've input, and which you can inspect in the Loan Calculator, as well as in the Cash Flow and Loan Planner worksheet. These two worksheets are shown on the bottom of page 30 and the top of page 31. From this base case loan scenario, you have the following three options to proceed, with descriptions below:-6096017780000Option 1: Accept the base case loan plan proposed automatically by the tool:If you, the loan officer and the prospective borrower, are both happy with the cash flow-based loan repayment schedule proposed by the computer you are provisionally complete! You can now continue your loan analysis by proceeding to look over some of the output worksheets, which help you decide whether you want to grant the loan or not, or whether you need to further tweak the loan terms. If however, you decide that you need to tweak some of the parameters of the loan, it is quite easy and is described in options 2 and 3 below.Option 2: Accept the base case loan plan proposed automatically by the tool, but make changes to the interest and principal payment terms:If either the loan officer or the client prefer to make some changes for whatever reason, the user has quite a bit of flexibility to change loan terms (interest rate, month of first installment, number of installments, and type of repayment plan) on the Loan Calculator. Just press the “Change Base Case” button and the cells that you can change will be automatically highlighted in light blue. Once you are done making the necessary changes, please make sure to select the “Copy Repayments to Cash Flow” button so that the new interest and principal payments are copied to the monthly cash flow statement. You can inspect the changes by toggling back and forth between the two sheets.Option 3: Do not accept the base case loan plan proposed automatically by the tool and enter the “Full Custom” mode that allows you to tweak all of the loan parameters.Finally, if the loan officer would like to completely rework the parameters (including the loan amount and the timing of the disbursements) of the base case loan scenario, you can enable the Full Custom option by clicking on the button that reads “Full Custom Mode”. When you click on this option, the cells that you can change are automatically highlighted in light blue; once you make the changes, make sure to press the “Copy Repayments to Cash Flow” button so that the new data is copied back into the monthly cash flow sheet.Please note, in the full custom mode, the colume on the loan structuring table entitled “Interest Paid” still has a default to an automatic calculation, and accordingly, you will see the formula if you click on the individual cells. This is simply to facilitate the calculation of the monthly interest payments based on the interest rate you specify in cell H8. You may overwrite this easily if you would prefer to not use the automatic calculation.Note also the "Hide Empty Columns" buttons on each of the two worksheets. They both do the same exact thing, i.e., hiding the columns and rows that just show zeros, so that reviewing the printouts (see below) will be easier on your eyes. Note further that all negative amounts in the Cash Flow projections and the Loan Calculator appear in red to facilitate their easy identification.The following sections briefly discuss the various output worksheets.Users should note that the month-by-month cash flow projections shown in the Cash Flow & Loan Planner worksheet have been transformed into sister quarterly cash flow projections, i.e., the Quarterly Cash Flow worksheet. Some users of the tool can conceptually deal with the tool better using a few quarterly figures, rather than many monthly amounts. If you like it, it’s there. If you don’t like it, hey, you’re not forced to look at it!Once the base case loan has been generated, or some revisions to it have been incorporated, the user will probably want to examine the various analyses performed by the tool. Each has a different, but complementary, role to play in assisting loan officers to decide whether to grant a loan and structure it properly. A discussion of these various “assistants” follows the screen dumps of the Cash Flow and Loan Planner and Loan Calculator worksheets on the following page. 21463000 Review of the Tool’s Financial Analysis and RecommendationsThe “Financial Indicators” Analytical Worksheet -20002541846500This is the principal and most comprehensive analytical tool at the user’s disposal. It includes ratios in several key areas (profitability, solvency, collateral/surety adequacy, balance sheet structure and liquidity. Moreover, it calculates a weighted score based on the loan proposal’s and the borrower’s unique data, and actually makes a recommendation as to whether the loan should be granted or not. Curious users wishing to learn the formulae used in these ratios can carefully unprotect the worksheet, examine the formulae, then re-protect the worksheet without changing anything. Although protected, there is no password required to unprotect the worksheet, or any part of this tool, for that matter. The sheet can be referenced below:-8636062738000The credit scoring is based on a basket of indicators and scores with respect to each one that are totaled up to get an overall score. These indicators and the scores are defined in the hidden "Ratings" worksheet. Unhidden, that worksheet appears as shown below.Skilled Excel users can alter this scoring scheme by unprotecting the Ratings worksheet and changing the weights and scores. Remember to make sure that the maximum remains 100 points, however. Note also that three possible recommendations can be given: “Very Risky - Don't grant the loan”; “Grant the Loan”; and a notice “Moderately Risky – Use Good Judgement” and you'll have to use your judgment and knowledge of the borrower to make the final decision. Each of these three recommendations has its own color, with red signaling a "thumbs down" recommendation.CVP AnalysisThis worksheet presents "cost-volume-profit" information on each crop and livestock species, including comparisons of sales price and variable costs, calculation of gross margins on each product, and some sensitivity analysis to assess the effect of the risk that prices or volume sold are lower than expected, or that costs are more than estimated. If the prospective borrower has less than ten crops and ten livestock species, you will probably want to click on the "Hide Empty Columns" button to make the table more readable. This analysis is useful in identifying less profitable farming activities and in convincing the borrower to adopt or expand more profitable activities or approaches and cut back on unprofitable activities. The upper left portion of the CVP Analysis worksheet is shown in the following screen dump:11338624565700ChartsCurrently, seven charts are also generated with each loan application analysis, and these charts are also intended to help loan officers analyze and appraise prospective borrowers' loan applications. These charts include an overall cash flow summary (the uppermost chart), details of cash inflows and cash outflows, monthly family expenses, gross margin by crop/species, and crop inputs by crop/species type. The uppermost part of this worksheet appears as shown at the bottom of the previous page.Users exploiting this tool on the Excel 2007 platform will occasionally notice an Excel bug that doesn’t seem to occur in Excel 2003. That is, the charts seem to fold in on themselves. The charts are not damaged, but just appear jumbled. To reset the charts and view them normally, click on View, then Zoom, then specify a magnification of somewhere between 180 and 220. Do not panic; nothing is really wrong.Family ExpensesThe transformation of the data you've input on family expenses into a month-by-month summary is shown here, in both absolute amounts and in percentage terms, as shown below.091400Comparative BSThis final analytical worksheet shows the borrower's estimated balance sheet before the loan, immediately after disbursement, and after the loan is repaid. The tables appear as shown in the following screen dump:0000In addition to the comparative balance sheet, this worksheet also shows the overall income and expenses of the borrower household during the period of the loan, including the value of family labour and not including family labour, in order to attempt to see whether the farm has generated the equivalent of a living wage for family member labourers.Closing ChecklistThe final tab in the model is a checklist that is intended to assist loan officers in keeping track of the various stages of the credit underwriting process, to ensure that all necessary operationalsteps were taken. It can be printed out by clicking the “Print Checklist” command button at the top of the control panel tab and can be 051435000modified to suit the internal processes if any institution rather simply – please feel free to change it as you wish to align with your financial institution’s process flow. The checklist also includes signature lines where loan officers and supervisors can sign off, to ensure the integrity of the process and create an audit trail for each loan application. Printing documentsWe have seen the basics of both the data input worksheets and the output tables, analyses and charts. You will probably at some stage want to print some or all of these. The easiest way to do that is to choose "Print Selected Documents" from the special "Ag Loan Analyzer" Excel main menu pull-down sub-menu. The resulting dialog box is shown at the top of the following page. Just click on the radio button corresponding to the document(s) you wish to print, and it will be done, and a confirmation dialog is flashed to you. If you want to print everything, just choose the final radio button, "All but the Control Panel and ToC", click Ok, and the entire battery of input and output documents will be printed. 2460625-41084500Exporting to Other Windows Software using Windows ClipboardThose users familiar with Windows software all know that you can generally make a selection in one Windows programme, click on “Copy”, switch to another Windows programme, and click on “Paste” to insert the selection in the new programme. This works particularly well with graphics, such as charts, but often less well with text and tables, which this tool generates in abundance. A less known secret to copying data (tables or charts) from Excel into Word reports, for example, is to not use the Edit-Paste sequence we’re all used to, but rather choose Edit-Paste Special. This gives users much more flexibility, and you can copy charts and tables into Word (or Powerpoint…) with high quality. For charts and large tables, generally use Paste Special “Image”. This technique was, in fact, used in drafting this manual and the many screen dumps, analytical tables, charts and dialog boxes the tool displays.User Customization of the Ag Loan Analysis ToolAs “freeware”, users downloading this software tool can use it as they wish. If they are sure they are competent to do so, they can even modify the tool to meet their special needs. This may appear desirable if a particular lender needs to incorporate certain ratios, charts or analytical procedures that that current Ag Loan Appraisal tool does not yet include. Although all spreadsheet cells other than those requiring data input have been “protected” against accidental damage, no password protection has been put in place to prevent modification of any table, chart, table, formula or VBA subroutine. However, this is an extremely complicated spreadsheet containing thousands of lines of VBA programme code, and even very competent spreadsheets users may have difficulty understanding the logic. A more logical approach would be to make proposals to the tool’s sponsors (FAO) to incorporate your special needs in future, improved versions of the software. But if users just can’t wait, they’re free to modify anything at all in the spreadsheet. Just be careful not to destroy the logic that makes the tool do what it does so well! Those modifying the tool to meet their special needs cannot count on either FAO to help them do so, although perhaps some contractual help could be identified. VIII. Priorities for Future Development of the ToolThere are seven main areas where the tool should be improved in its next version (should there be one), all of them relating to providing improving users’ skills and understanding.Change the logic from one of an initial disbursement and up to 24 monthly repayments to one with alternative repayment frequencies that are automatically calculated (weekly, semi-monthly, bimonthly, quarterly, semi-annually, and annually, in addition to monthly), and extend the number of repayments further from the current 24 month capacity.Incorporation of an on-line Help functionDevelopment of a tutorialTranslation into French and Spanish of the worksheet tab titles and the 600 or so instructional “bubbles” attached to key worksheet cells that guide users as to what information is required in those cells, and development of macros to run them automatically when the file opens. Also, the manual has to be updated in English, then translated into French and Spanish.Addition of a fourth, user-defined language permitting the tool to operate in any language supported by Windows. This would involve users providing translations into another language for the 800 or so text messages used in the tool (in addition to the approximately 600 instructional “bubbles” attached to key worksheet cells.Rename sheets, tabs & charts following language choice; prepare clones of pull-down menus using the new names, so that users only see their own preferred language; revise concerned macros so that pull-down menu appears in chosen language. Allow multiple types of production units (currently only one type is allowed).In addition, any reported bugs and user-friendliness issues should also be fixed. Furthermore, the next version might include other features and modifications widely requested and recommended by users of this Beta version.Finally, there are serious limitations as to what can be done within a spreadsheet, even one as smart as Microsoft Excel. Conceptually, it would have been better from the start to have conceived this tool not as a spreadsheet, but rather as a database. While this is more difficult for many users and developers, this would permit easy access to clients’ files without constant opening, closing, saving and renaming of files. A database, moreover, would use only a fraction of the disk space that hundreds (or thousands) of 1 MB spreadsheets would occupy. Putting the loans in a database could also facilitate compilation of statistics on loan information and production data. AnnexesAnnex 1Output TablesLoan Calculator(Monthly) Cash Flow & Loan PlannerQuarterly Cash FlowFinancial AnalysisCVP AnalysisComparative BSFamily Expenses(Examples are shown on the following pages)Annex 2 Chartscenter-50863500358775-61023500 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download