DISCUSSION PAPER Report No. UDD-77 HFI ANALYST (Computer Manuals for the Appraisal of Housing Financial Intermediaries) by Brian F. Kane April 1985 (revised August 1985) Water Supply and Urban Development Department Operations Policy Staff The World Bank The views presented herein are those of the author(s), and they should not be interpreted as reflecting those of the World Bank. PREFACE In the summer of 1984 a LOTUS model was developed to aid in the on-site evaluation of a housing finance intermediary (HFI). TLis manual guides the user through the revised version of this model. It is hoped that this new version presents a clear and more detailed analysis of a typical HFI's financial statement. The revision has also strived to make the model as user friendly as is possible. The model has been designed in such a way as to let even those with limited knowledge of the microcomputer and LOTUS, to run it efficiently and quickly. This limited knowledge is.assumed here in the manual as every last step is not drawn out for the reader. HFI ANALYST MANUAL #1 for TEMPLATE (historical data) by Brian F. Kane August 15,1985 Water and Urban Development Department, The World Bank TABLE OF CONTENTS GETTING STARTED . . . .........1 MOVING AROUND THE WORKSHEET. . .......... . . . . . . 3 DATA INPUT. . . . . ........ ...... ...... ....................... TRACKING DOWN ERRORS............. . . . . . . *.... 9 PRINTING. .-............................................. . . 11 OTHER HELPFUL HINTS.................................. . . . . . .13 MODIFICATION OF THE MODEL.. . . . . . 19 GETTING STARTED Before starting you will need - LOTUS program disk - the HFI ANALYST disk supplied by WUD - a keyboard template (small card listing all the keyboard macros), also listed in this text. Once the LOTUS ,menu has been brought up and 123 selected, the file containing the model must be retrieved. The two files on the disk are; 1. TEMPLATE (Historical analysis) 2. TEMP2 (Forecasting or pro forma analysis) TEMP2 being the Forecast Model, we want to retrieve TEMPLATE. The file is rather large (over 200k bytes), so it will take some time to load. Both TEMPLATE and TEMP2 follow the same basic format. There are, however, some important differences. The most important being the inclusion of operational data in TEMP2. For a detailed description of this and other differences consult Manual #2 in the second half of this report. 1 4 4 $ % y -: MOVING AROUND .TFE WORKSHEET With the TEMPLATE file on the screen it would be wise to review some of the ways of moving around the worksheet. * The Arrow keys move the cursor to the next cell in the direction of the arrow. * The PgUp and PgDn keys scroll the screen up or down, one page at a time. A LOTUS page is 20 rows or one screen. * The Home key moves the cursor to cell Al. * The End key when used in combination with one of the arrow keys moves the cursor to the "end" of a row or column of blank (or filled) cells. * The F5 or GoTo key will move the cursor directly to a particular address. Pressing F5 will bring the prompt: 'Enter Address to go to:Al,. with the address listed being the current position of the cursor. Just type in your destination and press Enter. The cursor will move directly to that address, adjusting the screen so the cursor is in the uppermost left hand corner. Often there will be a need to move from one section of the spreadsheet to another. In a relatively large spreadsheet such as this one, the first three options can be very time consuming. The F5 key will move the cursor directly to the cell requested, but this requires memorizing several locations. "Goto macros" have memorized certain locations for you. Pressing the "Alt" key and one of the letters below will move the cursor to the top of that section.1 A income statement input B balance sheet input C income statement output D balance sheet output E statement of changes F statement of sources and uses G ratios T management ratios If the cursor is not already at cell Al, press the Home key. A copy of the keyboard template listing all the macros should appear on the screen. Now press Alt A (goto the income input). The cursor moves directly to the Income Input title but the screen does not adjust. That is, the screen is not adjusted so that Al is in the upper right-hand corner. The screen will only adjust when the destination desired is not on the screen at the time the A macro is 1Macros do not have to be entered as capitals. 3 invoked. Try moving around the worksheet with the Macros, you will see that each time the cursor is brought to the title of the section. From there, the other cursor movement keys can be used to get you to your exact destination. 4 DATA INPUT The worksheet has been divided into two separate parts, an input side and an output side. The input side contains an Income Statement and Balance Sheet. The output side also contains an Income Statement and Balance Sheet, but it also contains the Statement of Changes, State- ment of Sources and Application of Funds, and Performance Indicators. The Income Statements and Balance Sheets from both the input and output sections are identicle in form only. There are no formulas in the input section, only spaces to enter data. All calculations are done in the output section. This separation was made to reduce errors and prevent the inadvertant writing over a formula by a user. If you were able to look at the whole worksheet at one time, the input section would be on the left side (the Management Ratios and Macros are below the Income Statement), the ouptut section is on the right. Pressing the Calc key (F9) "pulls" numbers from the input sections over and down through the output side. There are two methods of entering data: (1) using printed input sheets, and (2) direct computer entry. We recommend the first, as it reduces errors and is eventually faster for many users. 1. Using Input Sheets Printing a Data Sheet If you were not supplied with several blank input sheets others can be printed by using the H Macro. Filling the Data Sheet The printout should contain input forms for the Income Statement, the Balance Sheet, and the Management Ratios sections. - On the printed form encer the name of the institution in the blank line in the upper left-hand corner. - Next to 'ACTUALS ENDING FY' enter the date of the end of the fiscal year. Above that write in the currency and amount. for example: Billion Won - Above the columns of data write in the corresponding years. If you are analyzing less than six years of data, the remaining columns to be left blank can either be on the left or right side. Unless there is the possibility that another year of data would become available (for the current or last 5 fiscal year) or you do not plan on using the Forecast Model2, we recommend leaving the columns on the left blank. for example: If five years of data are to be analyzed, leave the first column on the left blank. If four years, leave the first and second columns blank, and so on. Write the individual figures into the spaces on the input sheet. If a line item doesn't apply, leave it blank. Later it will appear as a zero in the output. ( .......} cells are for optional entry. Depending on the details of the financial statements, these figures may not be available. If they are available, fill them in but leave the first 1.......] cell directly below them empty. i.e. use a top down approach to filling in the cells. for example: In the Income Statement the first few lines appear: loan type {.......} loan type {.......} loan type {.......} TOTAL LOAN INTEREST [....... I If the breakdown of Loan Interest is given by type of loan then fill in the (.......} cells, also write in the type of loan over "loan type". Total Loan Interest is usually given (in an annual report, etc.) in total form as well but do not enter it in the [.......] cell as the model will calculate the amount. If the breakdown is not given then leave the {.......} cells blank and fill in the C.......] cell. Fixed assets-gross, Accumulated depreciation, and Property P & E, net: if figures for all three are available insert the gross amount and depreciation do not enter the net amount. The worksheet will calculate this amount when the gross figure and accumulated depreciation are given. Transferring Data from Input Sheets to the Computer After the data is'on the input sheet it can be transferred to the model. Transfer data from the Input sheets to the Input section. 2Data must be entered on the right side of the worksheet if the Forecast Model is to be used later. The Forecast Model automatically copies the last year of actual data (column Q) into the first data column of the Forecast. This is done as soon as the file is retrieved. This was done so that one year of actual data could be compared to the forecasted years. 6 - Start with the title and years. Enter the titles and year headings in the Income Statement (input section) only. The S Macro will copy these entries to the other parts of the worksheet. - Entet the figures just as they are on the sheet. If two decimals are needed then use the R Macro, for no decimals use the Q Macro. - The data input section for the Management Ratios is not directly below the Balance Sheet section. The T Macro will bring the cursor to the section. - When entering the data it is not aecessary to type in the number and press Enter each time. In this case the Arrow keys --an perform the same function. for example: The entries for Depreciation have to be entered into the worksheet. -B- -C- -D- Depreciation {.......}{.......){.......) With the cursor in row B type in the figure for that year, but instead of pressing the Enter key, press the right arrow. This moves the cursor to column C ready for the next entry while entering the number in column B. 2. Direct Data Entry The data can be entered without input sheets. While this initially may save some time in entering the data, it could take longer to track down data input errors. The worksheet has been set for Manual Recalculation. Normally, each time a number is entered the entire worksheet is recalculated with the new number. In a large worksheet, such as Template, it is a time consuming process. With Man.ual Recalculation, every time a number is entered Calc will appear in v box at the bottom of the screen. This tells you that the worksheet needs to be recalculated. Pressing the F9 key will recalculate the worksheet. * After all the data has been entered into the computer, press the F9 key. This transfers the data to the output sections filling in the Statement of CThanges, Statement of Sources and Application of Funds, and the Ratios sections. 7 난 TRACKING DOWN ERRORS Once the data has been transferred, check Total figures from TEMPLATE's against your source of data (annual reports, etc.). Check items such as Total Income, Expenses, Assets, etc. These items should match exactly. Error Indicators To track errors there are error indicators at the bottom of the Income Statement, the Balance Sheet, the Statement of Changes, and the Statement of Sources and Application of Funds. Income Statement This error indicator '(transfer in(out)) measures the difference between the reported year to year change in Unappro- priated Retained Earnings from the Balance Sheet and the change that has been calculated in the Income Statement. Theoretically this should trap a hidden transfer of funds, into or out of a HFI. If the Error Indicator is not zero, or a very small number, check to see if Unappropriated Retained Earnings figures are even given in the Balance 3heet. In many instances a HFI will not report this so the error g.lill equal the calculated change. If this is the case then an Ajustment has to be made later in the Statement of Sources and Application of Funds. Balance Sheet The error indicators at the bottom of the Balance Sheet measure the difference between TOTAL ASSETS and TOTAL LIABIL- ITIES & EQUITY. Errors here are usually the result of data input errors., If there is a non-zero value here double check to make sure that numbers were tyr. d into the computer correctly, or entered onto the input sheet correctly. The error indic ' ators at the bottom of the Statement of Changes may help in pinpointing the location of these errors. See below. Statement of Changes The Error Indicator (rounding) measures the difference in the changes between TOTAL ASSETS and TOTAL LIABIL- ITIES & EQUITY. Subtotal figures in the Statement of Changes are not added, but are the difference of subtotal figures from the Balance Sheet. The Error Indicators for (Assets), (Liabilities), and (Equity) measure the difference between the change in these totals from the Balance Sheet and the addition of their components in the Statements of Changes. Statement of Sources and Application of Funds TOTAL CHANGES in the WORKING CAPITAL section should equal the BALANCE of the "PROVIDED BY:" and "USED FOR:" sections. A * ny difference is measured in the BALANCE CHANGE line. Many times simplified data from the banks themselves are available for analysis, In many instances it is hard to identify sources and uses of funds that may be hidden in these reports. For this reason, you may not be able to balance out this section. If the error in the Income Statement is caused by the fact that Unappropriated Retained Earnings were not reported in the Balance 9 Sheet then the formulas in the line. item imputed transfer in must be erased. There may in fact have been a transfer, but it will be very difficult to identify without the Unappropriated Retained Earnings figures from the Balance Sheet. To erase the formulas simply type in a zero over the formulas. 10 PRINTING Just as "goto" functions have been saved, so have the commands needed to print certain sections of the worksheet. The steps needed and the area in the spreadsheet to be printed have already been entered. Simply press the Alt key and one of the letters below to print a particular section of the spreadsheet. Make sure the printer is on and ready to print before executing a print macro, and that the worksheet has been recalculated if necessary. IMPORTANT: Macros perform their task almost instantaneously, printing will start one or two seconds after a print macro is entered. Make sure that the following keys are used only when a printout is desired. H entire input section (inc. stmt., bal. sheet, and mgmt. ratios) I entire output J income statement K balance sheet L statement of changes M statement of sources and uses N ratios The print has been set for condensed type so that all columns will fit on a regular 8 1/2" x 11" sheet of paper. Only the Statement of Sources and Applications of Funds is short enough to fit on one sheet of paper. The other statements will need two sheets of paper. It is advisable to separate the print jobs. That is, after printing the Income Statement output, advance the page so that the Balance Sheet output, or whatever section needs to be printed, can start on a new page. Important: to advance the paper using LOTUS and not from the printer. Using the printer to advance the paper will cause the printer to be out of alignment with LOTUS, this causes page breaks in the middle of a sheet of paper. To control your printer using LOTUS, enter the Print Menu by typing: /pp Press p to advance the paper to the next page. Press 1 to advance the paper one line at a time. To print a part of the worksheet without using a Macro: - Locate the upper left and lower right cells of the "box" to be printed. - Type /ppr 11 - Enter the range of the "box". for example:. al..g12 Where al is the upper left hand cell, and g12 is the lower right hand cell. - Press the Enter key - Double. check the alignment by making sure the top edge of the paper is at the print element, then press a. - To print press g. - Press q !to quit) after the printing has stopped and LOTUS is in the READY mode, - Advance the paper using LOTUS. 12 OTHER HELPFUL HINTS Other Macros Depending on the number of years of data, and which columns you have selected to leave blank, there may be a column or two off the screen to the right when a titles is on the screen. The o Macro lets you keep the titles on the screen and still see columns to the right. for example: - You are on the "output" side of the worksheet, the first column on the left is the K column containing titles. Columns L, M, N, and 0 are on the screen, but columns P and Q are off screen. K L M N 0 P Q - Your four years of data are in columns N, 0, P, and Q. Leaving columns L and M empty. - When you scroll the screen to the right to see column P,. column K will go off the screen to the left. K L M N 0 P Q R S - To keep the titles on' the screen and see columns P and Q use the 0 Macro. - With column K on the screen (to the left), move the cursor to column L. Type Alt 0 (set Vertical Titles). This has frozen the column to the left of the cursor, column K in this case. Try moving the cursor to the left. You should not be able to move into column K. 13 - Move the cursor to the right edge of the screen (column 0).. Move the cursor to the right once more. Now, instead of K moving off the screen L has disappeared, and P has appeared on the right. - K M N 0 P Q R - Once more to the right and Q will appear. K N 0 P Q R S - To get back to the original set up of: K L M N 0 P Q Just keep moving the cursor to the left until the L column appears. - To unlock column K use the P Macro. - NOTE: make sure Vertical Titles are off before using a "goto" Macro. -14 Graphing The Management Ratios If operational data is available for the Management Ratio Table (L Macro) then a graph can be generated using these variables with the U Macro. The graph plots: * loans per operating staff * deposits per operating staff * compensation per operating staff - To generate the graph simply use the U Macro. - To exit the graph screen press the Space bar, then type q to get out of the graph Menu. - Due to the complexities of printing a graph, no print Macro is available for this function. This is due to the fact that another LOTUS package disk is needed (PrintGraph) to amke a printout. - If you would like to save the graph for printing at some point, press s in the main graph menu to save the graph. InPut Line items that are similar, but do not match exactly. 1. Make a note on the Input Sheet of the correct (or new) title. Note: If the change is only cosmetic there is no problem, however, if the new item is slightly different, it may change the meaning of a ratio. Take special care in analyzing those ratios in which this item is a component. If the "new" ratio is totally different from the original the best thing to do is rename the ratio, or erase the line completely. These two suggestions are strongly recommended if the report is to be circulated. 2. Type in the new title over the old one in the Input section, and in all other sections in which the item is listed. Again, take special care with ratios involving this item. -15 Ni N 'I y "NAs" If you look at the Statement of Changes, Sources and Application of Funds, or Ratios before any of the data has been entered you will notice all of the cells register as "NA". This is because most of the cells in these sections rely on two years of data for their calcula- tions. For instance, in the Statement of Changes entries are cal- culated by subtracting this years entry in the Balance Sheet from last years. If the previous column is empty then the "change" is the entire reported amount, this may or may not be true since we do not know the amount for that year. To guard against this problem cells have logic statements in them whereby they will register an "NA" if the previous column is blank. The logic statement is actually reading the cell containing the first year (title entry) in the input section, so if the previous cell really is zero it will not register "NA". 17 14 MODIFICATION OF THE MODEL The model has been designed with two basic thoughts in mind: 1. To try and accommodate as many different types of institutions as possible. 2. To construct a model even the inexperienced user could operate. To meet the first objective a number of line items have been included that may pertain to only a few HFIs. On the other hand, the model is compact so that a study can be done in the field in a matter of hours. To meet the second objective, over twenty-five macros have been included to speed up routine tasks, and perform more complicated operations.. While the layout of the worksheet and the macros should benefit the experienced LOTUS user as well, it could also turn out to be a blndrance if a more detailed analysis is needed. If a more detailed analysis or expansion of the model is needed, it is recommended that a separate file be set up with the one or two sections of the worksheet that need to be expanded. These sections can be copied into the new file by using the File Combine Command. The model can then be expanded through the use of the Copy Command. Lines can be inserted or deleted without concern for disrupting a macro. If a new file is set up, the macros can also be combined into that new file, but the corresponding Range Names will have to be entered again. If there is not enough time to set up a new file, adjustments can be made to the present model, while still keeping the macros intact by editing the macros. The Z Macro will move the cursor to the Macro section. The macro ranges and settings can be changed to reflect the modified worksheet. IMPORTANT: Changes in the Income Statement and Balance Sheet affect the other parts of the worksheet. These changes must be documented, and adjustments made throughout to insure output consistent with the present format. Two excellent sources of additional information on LOTUS are: Us- ing 1-2-3 and 1-2-3 TIPS,TRICKS, AND TRAPS, both published by the QUE corporation. 19 4 s lo .L • Q 4 - & * Å r .. 7 $ & 6 a . x3 rn icome I npUt.' 'K pri nt Ba ancd",sheet-TML8'_ B to Balance Sheet input L print Stmt of Changes C to Income output M print Sources & Uses D to Balance Sheet output N print Ratios E to Stmt of Changes 0 set Verticle Tites U graph Mgmt Ratios F to Sources & Uses P clear Verticle Titles G to Ratios 2 No Decimals-Global H print Input R Two Decimals-Global I print Entire Output S copy Titles>Output J print Income Stint T to Mgmt Ratios Z to Macros 21 긔 MACROS ----------------------------------------------------------- -- \b (gotola9l* bal sheet input \z tgotolal6l' macros \a (gotolal4l income input \c (gotolk2l income output \d (gotoIK791 bal sheet output \e {goto)k149' stat of changes \f (gotolk222' sources & uses (gotolk2791 ratios \h /pp print. input ral3..hl57*gq /pppq /pp ra309..g3I5'gq agat ratios \i /pP " print entire rk2..r372' output gq \j Ipp print inc stat rk2..r751 gq \k /pp print bal sheet rk79..rl45' gq /pp print stat of rkl49..r2lB' changes qq /pp print s & u rk222..r2751 gq \n Ipp print ratios rk279..r3721 gq \0 /Wtv verticle titles \p IwtC clear titles /C copy title to a1412' output Ic copy currency dIS'n3' title /C copy fisc3l yr el6'o4' title Ic copy yr headings bI8..qIB' to inc stat 23 199 16. .q6 /c copy yr headings b18..gI8' to bal sht out 179. .q79r IC copy yr headings cl8.gl8 to stat chgs m149..ql49' /c copy yr headings cle..gl8 to s & u a224.. q224' Ic copy yr headings bI8..gl8' tu ratios (1) 1279.. q279* /c copy yr headings ble..g18 to ratios (2) 1296.. q296' /c copy yr headings bI..gl8 to bal sht in b91. .g91 /C copy yr headings bI8..gl8 to agat ratios* b310. .g310'~ /rffO' format yrs 16..1q6 no decimals /rf4Q' 179..q79' /rffO' m149..qI49' /rffO" a224..q224' /rffO' 1279..279' /rff O' 1296..q296' /rff0' b91..g91' /rffO' b310. .g310' /rffOI blB..gl8' \t (goto}a309' to agat ratios \U /grgtl graph agat ratio xb31O..g310' ab3l6..g316' bb317. .g3l7' cb320..g320' olaDeposits' IbLoans' IcCompensation' tfManagement Ratios' tsper operating staff' qv \q /rffO' no decimals k2..r275' output except ratios /rffO' al3..h157' input /rffOI 25 引 b-)03..gn6% eget ratios \r* IrffVe two decitals kL.r372' output lrf f 2" a13.å15711 input IrffO' b303..g306' format yrs lrffV no decimais blL.glg% Irf+0*4 b91..g9løø 16.,qå' lrffO 179..q79Pr IrffO' %149-.q149' @224..q224' Irf f 0' 1279..q279' Irf f T, q296, 27 & a n • 4 4 5 # # & - fu Z oQo c -s -vi Juu CAic ew 013 HFI ANALYST MANUAL #2 for TEMP2 (pro forma analysis) by Brian F. Kane August 15,1985 Water and Urban Development Department, The World Bank. TABLE OF CONTENTS INTRODUCTION.......................................... . . . GETTING STARTED................ . . . . . . . MOVING AROUND THE WORKSHEET................ . 3 DATA INPUT . . . . . . . . . . . . . . . . . . .. . . . . . . 4 Using Input Sheets. . . . . . . . . . . . . . . . . . . 4 Direct Data Entry . . . . . . . . . . . . .. .... *. 7 LOAN ANALYSIS. . . . . . . . . . . . . . . . . . . . . . . . Amortization Table. . . . . . . . . . . . . . . . . . . 11 Consolidated Amortization Table . . . . . . . . . . . . 11 Projected Financing Plan. . . . . . . . . . . . . . . . 13 TRACKING DOWN ERRORS........... . . . . . . . . . 15 PRINTING . ......................... 17 OTHER HELPFUL HINTS................... . . 19 Other Macros................ . . . . . . 19. Input . . . . . * . . . ................21 MODIFICATION OF THE MODEL............. . . . . 23 APPENDIX A.................................. . . . . . . . . 27 INTRODUCTION This model was developed in the spring of 1985 to complement and expand on the TEMPLATE (historical data) model for the analysis of housing finance institutions. TEMP2 (pro forma analysis) can be used during appraisals and for the preparation of Staff Appraisal Reports. It is designed to investigate the financial plan of a potential borrower and to determine the magnitude of impact that a World Bank loan would have on the borrower's financial position. TEMP2 does not attempt to forecast the future operational or financial condition of a HFI, it merely offers a standardized format for the analyst's own assumptions. The analyst should provide figures on the projected lending operations, approval and disbursement figures, and potential sources of funds including World Bank loans. From this input, TEMP2 computes yearly payment streams and outstanding balances for the financial statements. Remaining entries in the Income Statement and Balance Sheet must be filled in according to the analyst's own assumptions. As with TEMPLATE, TEMP2 automatically calculates the Statement of Changes, the Statement of Sources and Application of Funds, and Ratios. The two models differ in that TEMP2 does not have a separate input and output section. Because of the increased space needed for the operational analysis, a separate input section was not included. NOTE: The model has been designed in such a way as to let even those with limited knowledge of the microcomputer and LOTUS to run it quickly and efficiently. However, this model contains some rather complicated, interactive macros. These macros stop temporarily while they are running to ask the user for additional information. (See the Loan Analysis section.) For this reason we recommend that the analyst run the TEMPLATE model to become familiar with the general design of the worksheets, and to read this manual thoroughly before attempting to run TEMP2. Redesigning the TEMP2 worksheet to suit special situations is clearly feasible. However, potential users are discouraged from attempting such changes until they have determined why the existing model cannot cope with this problem(s) and what specific changes are needed. After reaching this threshold they have to determine whether they have the skills and time required to modify the present structure of powerful and sophisticated macros used in TEMP2. If so, consult the last section of this paper, Modification of the Model for some helpful hints in this area. 1 GETTING STARTED Before starting you will need - LOTUS program disk - the HFI ANALYST disk supplied by WUD - a keyboard template (small card listing all the keyboard macros), also listed in this text. Once the LOTUS menu has been brought up and 123 selected, the file containing the model must be retrieved. The two files on the disk are; 1. TEMPLATE (Historical analysis) 2. TEMP2 (Forecasting or pro forma analysis) TEMPLATE being the Historical Analysis Model, we want to retrieve TEMP2. The file is rather large (over 200k bytes), so it will take some time to load. Both TEMPLATE and TEMP2 follow *the same basic format. There are, however, some important differences. For a detailed description of this and other differences consult Manual #1 for TEMPLATE. ., � ��,�'" ,.�' � �� ���*г,f r�" . .� � . Г � . v�. ч е�.. в -� 4 •. . 2. . rл пь .�чГ. ,и , • л k� � � � - . . � � .f , в . � а ь , , .�ar . w . . ♦. -. .и .- . . ,ц . . н , ч . . ^ r ^ � й._ • �� MOVING AROUND THE WORKSHEET With the TEMP2 file on the screen it would be wise to review some of the ways of moving around the worksheet. The Arrow keys move the cursor to the next cell in the direction of the arrow. The PgUp and PgDn keys scroll the screen up or down, one page at a time. A LOTUS page is 20 rows or one screen. The Home key moves the cursor to cell Al. The End key when used in combination with one of the arrow keys moves the cursor to the "end" of a row or column of blank (or filled) cells. The F5 or GoTo key will move the cursor directly to a particular address. Pressing F5 will, bring the prompt: 'Enter Address to go to:A11, with the address listed being the current position of the cursor. Just type in your destination and press Enter. The cursor will move directly to that address, adjusting the screen so the cursor is in the uppermost left hand corner. Often there will be a need to move from one sectidn of the spreadsheet to another. In a relatively large spreadsheet such as this one, the first three options can be very time consuming. The F5 key will move the cursor directly to the cell requested, but this requires memorizing several locations. "Goto macros" have memorized certain locations for you. Pressing the "Alt" key and one of the letters below will move the cursor to the top of that section.1 A projected lending operations B amortization table C income statement D balance sheet E statement of changes F statement of sources and uses G ratios L management ratios V consolidated amortization table Y projected financing plan NOTE: Some Macros common to both models are represented___12y different letters. Be sure to have the keyboard template for TEMP2, or this manual on hand for a complete list of all the Macros. Macros are also listed on the worksheet and can be found by pressing the Home key. lMacros do not have to be entered as capitals. 3 기 DATA INPUT Another' reason we recommend the analyst run TEMPLATE prior to TEMP2 is that TEMP2 incorporates results generated by running TEMPLATE. In TEMP2 there are six columns for data on the financial analysis side, five for the forecast, and one for a year of actual data from the TEMPLATE model. When the file TEMP2 is retrieved you will notice that it is in the middle of a macro the instant it comes on the screen. TEMP2 is automatically copying the "last year" of data (the right most column from the output side), into the first data column in TEMP2. Every time TEMP2 is called up it will perform this procedure. (See the Modification of the Model section on how to delete this proce- dure.) Unlike TEMPLATE, TEMP2 does.not have separate input and output sections. The area in TEMPLATE that is reserved for the input section is the location of the Loan Analysis section in TEMP2. Because there is not a separate input section the user must take speclal care to not enter a number in over a formula with the exception of one instance listed below. There are two methods of entering data: (1) using printed input sheets, and (2) direct computer entry. Because there is no separate input section in TEMP2 it is strongly recommended that data input sheets be used. Data input sheets reduce errors and are eventually faster for many users. 1. Using Input Sheets Printing Data Sheets If you were not supplied with several blank input sheets then others can be printed by using the T, J, K Macros (to print the blank output sections before starting). Filling the Data Sheet These printouts should provide input forms for Projected Lending Operations, the Income Statement, and the Balance Sheet (it will also prin-';., the Statement of Changes, but ignore that for now). - On the Projected Lending Operations sheet enter the name, of the institution an the dotted line in the upper left hand corner. - Above the Projected Income Statement title write in the currency and amount. for example: Billion Won - Above the columns of data,, in the Projected Lending Operations section, write in the corresponding years. If you are analyzing less than five years of data, the remaining columns to be left blank should be on the.right side. 5 For the Income Statement and Balance Sheet write the individual figures into the spaces on the input sheets. As mentioned in the Introduction, TEMP2 only attempts to forecast figures in the Loan Analysis section. All other Income Statement and Balance Sheet figures must be supplied based on assumptions made by the user. Aside from the Loan Analysis section, TEMP2 only tries to provide a standardized format for these assumptions. If a line item doesn't apply, leave it blank. If a line item title doesn't match exactly, write in the new one over the old one. DO NOT if at all possible insert or delete a row or column. This could possibly throw off a macro. See Modification of the Model at the end of the manual. {.......} cells are for optional entry. Depending on the details of the projection, these figures may not be available or needed. If they are needed, fill them in but leave the first [.......] cell directly below them empty. i.e. use a top down approach to filling in the cells. for example: In the Income Statement the first few lines appear: loan type {.......} loan type (.......} loan type (......) TOTAL LOAN INTEREST- [... ] If the breakdown of Loan Interest is available by type of loan then fill in the (.......} cells and also write in the new line type over "loan type" on the screen. Total Loan Interest is usually given (in an annual report, etc.), but do not enter it in the [.......] cell as the model will calculate the amount. If the breakdown is not given then leave the {.......} cells blank and fill in the C.......] cells, This is the only case where you will need to write over a formula. Simply type in the number over the formula. If you find that you have accidentally entered a number in over a formula in a [.......I cell (or other formula cell), simply copy the formula from an adjacent column to the cell where the formula was inadvertantly erased. (Loan interest figures are generated by the Amortization Table.) Fixed assets-gross, Accumulated depreciation, and Property P & E, net: if figures for all three are available insert the gross amount and depreciation (rows 104 and 105), do not enter the net amount. The worksheet 6 will calculate this amount when the gross figures and accumulated depreciation are given. -There is no print macro for the Management Ratios. To obtain a data input sheet for the Management Ratios this section will have to be printed out with the LOTUS print function. See the section on Printing later in the Manual. Use the L Macro to get to the Management Ratio section. Transferring Data from Input S1heets to the Computer After the data is on the input sheet it can be transferred to the model. Data must be transferred directly to the output sections. - Start by entering the title and year headings in the Projected Lending Operations section only. The S Macro will copy these entries to the other parts of the worksheet. - Enter the figures just as they are on the sheet. If two decimals are needed then use the R Macro, for no decimals use the Q Macro. - When entering the data it is not necessary to type in the number and press Enter each time. In this case the Arrow keys can perform the same function. for example: The entries for Depreciation have to be entered into the worksheet. -B- -C- -D- Depreciation {.......){.......}{....... With the cursor in row B type in the figure for that year, but instead of pressing the Enter key, press the right arrow. This moves the cursor to column C ready for the next entry while entering the number in column B. 2. Direct Data Entry Data can be' entered without the input sheets. While this may save scime t,Ime in entering the data, it could take longer to track down data input errors. The worksheet has been set for Manual Recalculation. Normally, each time a number is entered the entire worksheet is recalculated with the new number. In a large worksheet, such as TEMP2, it is a time consuming process. With Manual Recalcula- tion, every time a number is entered Calc will appear in a box at the bottom of the screen. This tells you that the worksheet needs to be recalculated. Pressing the F9 key will recalculate the worksheet. After all the data has been entered into the computer, press 7 the F9 key. This transfers the data down through the worksheet filling in the Statement of Changes, Statement of Sources and Application of Funds, and the Ratios sections. 8 LOAN ANALYSIS A key feature of the model is the Amortization and Consoli- dated Amor.tization Tables. In the Amortization Table, interest and principal payments along with outstanding balances are calculated in a 5 by 5 table. i.e. five disbursements for five years. Each column, B thru F is an individual disbursement. Payment streams for each disbursement are calculated "down" that column. Total for individual years are added across to column G. Loans are amortized on a simple fixed payment scheme. Other more sophisticated amortization tables have been developed that can handle balloon and progressive payment schedules. These models were developed in conjunction with TEMP2 and follow the same format presented here (these are loan analysis models only). Results from these models can be used in TEMP2. Contact WUD for further details on these models. To save space only one amortization table is offered, even though there may be many types of loans. The Table must be run separately for each loan. The results of each run are then saved in the Consolidated Table for comparison and to arrive at a total figure to transfer to the Income Statement and Balance Sheet. The addresses that follow correspond to the example on the following page, this example should match the location of the Amortization Table in TEMP2 as well. If the model is modified in any way then the cell addresses will not apply to TEMP2, but they will still apply to the example. Loan analysis starts with the Projected Lending Operations section. The Section is split in two, an area for Approvals (Commitments) and an area for Disbursements. There is space enough for six different types of loans. Five different sub- components of a project are used as examples in the model. The analyst may choose to look at on subproject and break out its parts (as is done in the example), or may choose to analyze total commitments to subprojects. For example, the Bank is proposing to lend funds to the Ministry of Housing (MOH) country X. The MOH, inturn will lend these funds out for several subprojects A thru D. Each of the subprojects can usually be broken out further (like has been done in the model); Al, A2, A3, etc. So, the user may analyze only the loan to the MOH, subcomponents A thru D, or every component in each subproject. Loans as sources of funds can also be analyzed in the same manner. The user could analyze an IBRD loan for example. 9 -A- -B- -C- -D- -E -F--G- 35 AMORTIZATION TABLE Low-cost housing loans 36 end of period calculations 38 DISBURSEMENT AMOUNT 80000 150000 247500 302500 340000 39 INTXPER YR - 14.00% 14.00% 14.00% 14.00% 14.007. 40, TOTALIPMTS 25 25 25 25 25 41 #PMTS PER YR 1 1 1 1 1 42 CALC PMT 11639.87 21824.76 36010.86 44013.27 49469.46 TOTAL 1982 45 interest payment 11200.00 11200.00 46 principal payment 439.87 439.87 47 balance outstanding 79560.13 79560.13 1983 50 interest payment 11138.42 21000.00 32138.42 51 principal payment 501.45 824.76 1326.22 52 balance outstanding 79058.67 149175.24 228233.91 1984 interest payment 11068.21 20884.53 34650.00 66602.75 principal payment 571.66 940.23 1360.86 2872.74 balance outstanding 78487.01 148235.01 246139.14 472861.17 1985 interest payment 10988.18 20752.90 34459.48 42350.00 108550.56 principal payment 651.69 1071.86 1551.38 1663.27 4938.19 balance.outstanding 77835.32 147163.15 244587.77 300836.73 770422.97 1986 interest payment 10896.95 20602.84 34242.29 42117.14 47600.00 109728.68 principal payment 742.93 1221.92 1768.57 1896.13 1869.46 7499.00 balance outstanding 77092.40 145941.23 242819.20 298940.61 338130.54 1102923.97 10 Amortization Table 1. Enter the loan category in cell B35. This is the loan type to be analzed from the Disbursement section of the Projected Lending Operations Table. 2. Move the cursor to cell B38, the first column in the row of disbursements. The entries in these cells have to be edited to bring down the desired disbursement from the Projected Lending Operations section. for example: - The entry in cell B38 may be +B$27 - If the loan category to be analyzed is in row 25, the entry in cell B38 has to be edited to read +B$25. 3. Copy cell B38 to cells 038 thru F38. 4. The yearly interest rate is entered in row 39. Starting in cell B39 enter the interest rate in decimal form. 8 percent would be entered as .08. 5. Copy cell B39 to cells C39 thru F39. 6. In cell B40 enter the total number of payments. Not the total number of payments per -year, but the total number of payments for the life of the loan. In the example, the loan is for 25 years with yearly payments so the total number of payments is 25. 7. Copy cell B40 to celss 040 thru F40. 8. Enter the number of payments per year in cell B41. 9. Copy cell B41 to cells 041 thru F41. 10. Pressing the F9 or "Calc" key will generate the rest of the Table. 11. Since we must use the Table to amortize the other loans the only way to save the current run (and the others when they are run) is to make a printout. The H Macro will print the Table. Consolidated Amortization Table The Consolidated Amortization Table simply formats the total figures (column G) from the Amortization Table. The Total figures from each run of the Table must be transferred to the Consolidated Amortization Table (Consol). The Consol calculates the total interest received (or paid) for transfer to the Income Statement. 12. The U Macro copies the total figures to the Consol. After starting the U Macro the program will flash on the screen for a few moments then the macro will stop and the box in the upper right-hand corner will read CMD POINT. The Macro is asking for directions on where to put the data transferred from the Amortization Table. For the first loan, construction loans, the cell address is B73. Enter B73 and press Enter. Steps 1 through 12 must be repeated for each loan. The cell addresses for Step 12 are: 2nd Loan (prefinancing) B76 3rd Loan (site & service) B79 4th Loan (low-cost housing) B82 5th Loan (commercial) B85 6th Loan (other) B88 13. Formulas have been entered in the bottom of the Consol to calculate interest payment, TOTAL and principal payment, TOTAL, unfortunately after using the U Macro these formulas change to ERR messages. This is due solely to the way LOTUS operates and is not an error on the users part. The only way to correct this problem is to enter in the correct formula after the Consol has been filled in. The following formula should be placed in cell B91: +B73+B76+B79+B82+B85+B88 This will sum all of the interest payments for the year. Copy this cell to the remaining cells in row 91 (copy B91 to C91 thru F91). Then copy the cell to row 92 (copy B91 to B92 thru F92). The ERR messages in Row 93, for TOTAL PAYMENTS, will correct themselves after rows 91 and 92 are edited and the worksheet is recalculated. 12 Projected Financing Plan The Projected Financing Plan is truly the culmination of the entire worksheet. It is here that we will find out if the proposed financing is adequate to support the project. The table is cumulative in that it includes all *five years of projections. Entries to the table must be the total of the five years. TEMP2 can provide all of these entries if the analyst uses the Loan Analysis section for both sources and uses of funds. The analyst must look at all funds going into and flowing out of the project. 13 * tZV /9Q TRACKING DOWN ERRORS Error Indicators To track errors there are error indicators at the bottom of the Income Statement, the Balance Sheet, the Statement of Changes, and the Statement of Sources and Applica- tion of Funds. These indicators should guide the analyst in identifying the sections of the worksheet that do not balance out. Income Statement This error indicator (transfer in(out)) measures the difference between the reported year to year change in Unappropriated Retained Earnings from the Balance Sheet and the change that has been calculated in the Income Statement. Theoretically, in an historical analysis, this should trap a hidden transfer of funds, into or out of a HFI. If the Error Indicator is not zero, or a very small number, check to see if Unappropriated Retained Earnings figures were even given in the Balance Sheet. In many instances a HFI will not report this so the error should equal the calculated change. If this is the case then an adjustment has to be made later in the Statement of Sources and Application of Funds. Balance Sheet The error indicators at the bottom of the Balance Sheet measure the difference between TOTAL ASSETS and TOTAL LIABILITIES & EQUITY. Errors here are usually the result of data input errors. If there is a non-zero value here double check to make sure that numbers were typed into the computer correctly, or entered onto the input sheet correctly. The error indicators at the bottoii of the Statement of Changes may help in pinpointing the location of these errors. See belwo. Statement of Changes The Error Indicator (rounding) measures the difference in the changes between TOTAL ASSETS and TOTAL LIABILITIES & EQUITY. Subtotal figures in the Statement of Changes are not added, but are the difference of subtotal figures from the Balance Sheet. The Error Indicators for (Assets), (Liabilities), and (Equity) measure the difference between the change in these totals from the Balance Sheet and the summation of their components in the Statements of Changes. Statement of Sources and Application of Funds TOTAL CHANGES in the WORKING CAPITAL section should equal the BALANCE of the "PROVIDED BY:" and "USED FOR:" sections. Any difference is measured in the BALANCE CHANGE line. If the error in the Income Statement is caused by the fact that Unappropriated Retained Earnings were not reported in the Balance Sheet then the formulas in the line item imputed transfer in must be erased. There may in fact have been an unreported transfer, but it will be very difficult to identify without the Unappropriated Retained Earnings figures from the Balance Sheet. To erase the figures simply type in a zero over the formulas. 15 * r'.Ç / 9 1 /64 - * , * * --. - - -,- - * ,.*m- PRINTING Just as the "goto" functions have been saved, so have the commands needed to print certain sections of the worksheet. The steps needed and the area in the spreadsheet to be printed have already been entered. Simply press the Alt key and one of the letters below to print a particular section of the spreadsheet. Make sure the printer is on and ready to print before executing a print macro, and that the worksheet has been recalculated if necessary. IMPORTANT: Macros perform their task almost instantaneously, printing will start one or two seconds after a print macro is entered. Make sure that the following keys are used only when a printout is desired. H amortization table I projected financing plan J income statement K balance sheet and statement of changes M statement of sources and uses N ratios T projected lending operations W consolidated amortization table The print has been set for condensed type so that all columns will fit on a regular 8 1/2" x 11" sheet of paper. Only the Statement of Sources and Applications of Funds is short enough to fit on one sheet of paper. Other statements will need two sheets of paper. It is advisable to separate the print jobs. That is, after printing the Income Statement advance the page so that the Balance Sheet, or whatever section needs to be printed, can start on a new page. Important: to advance the paper using LOTUS and not from the printer. Using the printer to advance the paper will cause the printer to be out of alignment with LOTUS, causing page breaks in the middle of a sheet of paper. To advance the page using LOTUS, enter the Print Menu by typing: /pp Press p to advance the paper to the next page. Press 1 to advance the paper one line at a time. To print a part of the worksheet without using a Macro: - Locate the upper left and lower right cells of the "box" to be printed. 17 - Type /ppr - Enter the range of the "box". for example: al..gl2 Where al is the upper left hand cell, and g12 is the lower right hand cell. - Press the Enter key - Double check the alignment by making sure the top edge of the paper is at the print element, then press a. - To print press g. - Wait will flash in the upper right corner of the screen while LOTUS is printing. - Advance the paper using LOTUS. 18 OTHER HELPFUL HINTS Other Macros Depending on the number of years of data, and which columns you have selected to leave blank, there may be a column or two off the screen to the right when a titles is on the screen. The o Macro lets you keep the titles on the screen and still see columns to the right. for example: - You are on the "financial statement" side of the worksheet, the first column on the left is the K column containing titles. Columns L, M, N, and 0 are on the screen, but columns P and Q are off screen. K L M N 0 P Q - Your four years of data are in columns N, 0, P, and Q. Leaving columns L and M empty. .- When you scroll the screen to the right to see column P, column K will qo off the screen to the left. K L M N 0 P Q R S - To keep the titles on the screen and see columns P and Q use the 0 Macro. - With column K on the screen (to the left), move the cursor to column L. Type Alt 0 (set Vertical Titles). This has frozen the column to the left of the cursor, column K in this case. Try moving the cursor to the lef', You should not be able to move into column K. 19 - Move the cursor to the right edge of the screen (column . 0). Move the cursor to the right once more. Now, instead of K moving off the screen L has disappeared, and P has appeared on the right. K M N 0 P Q R - Once more to the right and Q will appear. K N 0 P Q R S - To get back to the original set up of: K L M N 0 P Q Just keep moving the cursor to the left until the L column appears. - To unlock column K use the P Macro. - NOTE: make sure Vertical Titles are off before using a "goto" Macro. 20 Graphing The Management Ratios If operational data is available -for the Management Ratio Table (-L Macro) then a graph can be generated using these variables with the X Macro. The graph plots: ** loans per operating staff * deposits per operating staff * compensation per operating staff - To generate the graph simply type the X Macro. - To exit the graph screen press the Space bar, then type q to get out of the graph Meru. - Due to the complexities of printing a graph, no print Macro is available for this function. This is partly due to the fact that another LOTUS package disk is needed (PrintGraph) to make a printout. - If you would like to save the graph for printing at some point, type s in the main graph menu to save the graph. Input Line items that are similar, but do not match exactly. 1. Make a note on the Input Sheet of the correct (or new) title. Note: If the change is only cosmetic there is no problem, however, if the new item is slightly different, it may change the meaning of a ratio. Take special care in analyzing those ratios in which this item is a component. If the "new" ratio is totally different from the original the best thing to do is rename the ratio, or erase the line completely. These two suggestions are strongly recommended if the report is to be circulated. 2. Type in the new title over the old one in the Input section, and in all other sections in which the item is listed. Again, take special care with ratios involving this item. 21 긔 MODIFICATION OF THE MODEL The model has been designed with two basic thoughts in mind: 1. To try and accommodate as many different types of institu- tions as possible. 2. To construct the model even the inexperienced user could operate. To meet the first" objective a number of line items have been included that may pertain to only a few HFIs. On the other hand, the model is compact so that a study can be done in the field in a matter of hours. To meet the second objective, over twenty-- five macros have been included to speed up routine tasks, and perform more complicated operations. While the layout of the worksheet and the macros should benefit the experienced LOTUS user as well, it could also turn out to be a hindrance if a more detailed analysis is needed. If a more detailed analysis or expansion of the model is needed, it is recommended that a separate file be set up with the one or two sections of the worksheet that need to be expanded. These sections can be copied into the new file by using the File Combine Command. The model can then be expanded through the use of the Copy Command. Lines can be inserted or deleted without concern for disrupting a macro. If a new file is set up, the macros can also be combined into that new file, but the corres- ponding Range Names will have to be entered again. It was for this reason that the two additional loan amor- tization models were developed. The original 5 x 5 fixed payment amortization table was combined into an empty worksheet. it was then expanded (to a 15 x 15 table) and modified for progressive and balloon payments. If there is not enough time to set up a new file, adjust- ments can be made to the present model, while still keeping the macros intact by editing themacros. The Z Macro will move the cursor to the Macro section. The macro ranges and settings can be changed to reflect the modified worksheet. The program can easily be edited so that the year of actual data is not automatically copied each time TEMP2 is retrieved. To stop this procedure type: /rnd\o "Enter" explanation: range name delete \0 (zero, the name of the macro) IMPORTANT: Changes in the Income Statement and Balance Sheet affect the other parts of the worksheet. These changes must be 23 I? C documented, and adjustments made throughout to insure output consistent with the present format. 24 Two excellent sources of additional information on LOTUS are: Using 1-2-3 and 1-2-3 TIPS,TRICKS, AND TRAPS, both pub- lished by the QUE corporation. 25 기 APPENDIX A The following example has been taken from a Staff Appraisal Report (Report No. 4151-Mor) on the first housing loan credit to Credit Immobilier et Hotelier (CIH), dated, November 11, 1982. There are several major discrepancies between the financial statements generated by the worksheet and the financial state- ments provided in the report. These discrepancies may have arisen in that in preparing the example the author. had no prior knowledge of the Morocco case, and conducted the analysis of CIH solely "with the numbers" and no qualitative information. The answers to questions raised in implementing the model were not available so the analysis stands in its present state. In a real sense this situation shows how the template can be used to identify areas where more information is needed and of what kind. The following financial statements therefore, are intended as a general example of the type of analysis possible with TEMP2 and of the possible questions it may generate. Some of these questions for CIH are listed here. INCOME STATEMENT Normally in the Income Statement loan interest figures are available for the outstanding portfolio (or can be generated in the Amortization Table in the case of new loans). Loan interest was calculated (pages 26-31), but these totals (page 32) did not agree with interest received in the SAR. This discrepancy is probably due to the fact that this figure included interest earned on the National loan and other investments, though these amounts were not separated. The figure in the example is from the report, total loan interest from page 32 has not been entered separately. There was no entry for interest payments on the foreign debt. It is presumed that this is included in Interest Paid on Borrowings (in the report, but entered here as Total int on brrwgs, dpsts). The Error indicator/tfr n(out) shows that there may have ,,.( , en (or will be) a transfer (unreported) in 1982, 1984, and in 1986. This figure is taken from the Balance Sheet and may be due to an error there. BALANCE SHEET The problem alluded to earlier about the possible transfer may be caused by a question in the Equity section. In the report, only the unappropriated retained earnings are added to Total Liabilities to get Total Liabilities and Equity. Neither Paid-in capital nor Reserves were included in the calculation. However, they are included in the example resulting in the large error (Error indicator (rounding)). These questions carry through to the Statement of Changes and Statement of Sources and Application of Funds. 27 STATEMENT OF SOURCE AND APPLICATION OF FUNDS The most important thing to note here are the figures for 1982. Even if the worksheet "balanced out" figures for this first year of projec- tions (second column) are not usually reliable (in most cases). This is because they are pulled down from the Statement of Changes which calculates the change from the previous column. If the previous column is empty then the "change" is the entire reported amount, this may or may not be true since we do not know the amount for that year. The only case where this would be a reliable figure is when the institution is new. This problem has been corrected in TEMPLATE (historical data), the cell will register an "NA" if the calculation calls for two years of data and they are both not given. This correction was not available here in TEMP2 because the model transfers in actual data from TEMPLATE. In this example no actual data was transferred in resulting in this problem. If no "actual" data is transferred in (see Modification of the Model) figures in this section, in the second column, are more or less meaningless. 28 TOTAL EXPENSES 0 328778 351381 444266 459550 537138 Special profit 1.. H ... .......11.......I Special loss C.......11.......I .......11.......11.......11.......I Net Income before tax 0 47364 129876 157698 276083 338019 Tax provision I.......1 25008 68575 83265 145772 178473 NET INCOME AFTER TAX 0 22356 61301 74433 130311 159546 Allocation, prev.yr's NIAT Reserves, Legal E.......1 1118 3065 3722 6516 7977 C. Reserves, general C.......1 3178 40176 47493 100576 123187 C. I Dividends & Dir. fees C.......1 18060 18060 23220 23220 28380 1. Directors fees C ....... 1..............1.......11....... ,,J Unappropriated Ret. Earnings 0 0 0 -2 -1 2 Change in RE fros B/S 0 18060 0 5160 0 5160 Error indicator/tfr in(out) 0 18060 0 5162 1 5158 29 CIH-MOROCCO DH Thousands PROJECTED INCOME STATEMENTS INCOME STATEMENT 1981 1982 1983 1984 1985 1986 loan type .. . it. ... loan type I . I a. 1 a. I loan type .I.... I loan type {. . .. .I loan type .. I I. I1.. loan type .I...I . )t. . TOTAL LOAN INTEREST Q 305401 389536 480361 578905 679241 Money instruments/Tbill Disct Interest, penalty . 3 50052 59538 71630 82637 95887 Interest, subsidy I. 3 3677 12705 28168 49417 73870 Profit on sales invstts-net ....... ....... ....... ....... .. . Total investment income 0 5,V29 712243 99798 132054 169757 Total Interest Received 0 359130 461779 580159 710959 848998 Commissions I .......{. 8012 9478 11605 13474 14959 Other income {.......3) 9000 10000 10200 [1200 11200 TOTAL OPERATING INCOME 0 376142 481257 601964 735633 875157 Non operating income .. .........)H......1.. TOTAL INCOME 0 376142 481257 601964 735633 875157 Staff Compensation & benefits I...1 25000 .28750 33063 380222 43725 Occupancy expenses-net ( ... 600 700 800 900 1000 Equipment 1....... .. 7600 8700 9800 10900 12000 epreciation ...... 6075 6579 7783 8882 10404 Bad debt contingency ....... 30044 33809 38608 42977 4235 Forex risk contingency {.......)f.......1.......11...... .......I.......J Subtotal/Bus Admin Expns 0 69319 78538 97054 101681 115364 other i .......11.......1.......11..... 31...... 31.3...] TOTAL OPERATING EXPENSES 0 69319 78538 90054 101681 115364 Interest-demand deposits{.. . I ... Interest-hsg instiont savgs {. .1. . 5H6 Interest a n brrwgs-hsg debentures {. . .H) 60 70 80 9 Others {. ....) 760Ia. .0 1 20. Total t on brrwgs, dpsts 0 259459 272843 354212 357869 421774 Other & comiitment charge .. 3 . 13 . 3 . Housing issue expense .......3.......I[.......{.......3E.. 1....){ Lottery issue expense .......11 .......33. 11. . 3 Total Ot I charges-domestc 0 259459 272843 354212 357869 421774 Long-tern debt,foreign I.......){.......3.......It.......H.......){.......) Exchange losses . .3 . ..............{...... Total financial expenses 0 259459 272843 354212 357869 421774 Non operating expenses 1.......11.......11.......11.......11.......1.......1 30 PROJECTED BALANCE SHEETS 1981 1982 1983 1984 1985 1986 Cash/due from banks ....... 1 45250 59875 64557 70110 80181 Remittances in transit I.......Hf............H,..,. Jl......][,.....I Loans and advances I...... J f.......f11....., 1.......11.......11.......I Due from banks/ see above I.......1 623024 745587 906351 1051848 1230310 Bank deposit accounts 1.......I .......I.......I1.......11.......11.......I Current maturities, loans f.......1 329347 373247 627636 630426 726589 Current maturities, deposits 1.......If.......I1.......I1.......31.......11.......I Govt securities- Treas Bills f.......11.......11......11f.......11.......11....... Short-term securities, corp. f.......11.......I1.......f.......11.......11.......I Other I. ,... 11.......11.......11.......If....... l.......I Total current assets 0 997621 1178709 1598544 1752384 2037080 Housing loans f.......1 2083158 2606180 3305235 3911980 4646239 Commercial & consumer loans f.......1 1124954 1209966 1285913 1384669 1499675 Total loan portfolio-gross 0 3208112 316146 4591148 5296649 6145914 Less current maturities .6......1 329347 373247 627636 630426 726589 Total loan portfolio-net 0 2878765 3442899 3963512 4666223 5419325 National loan f.......1 2000 2000 2000 2000 2000 Investment Assets f.......1 68518 58339 48160 37981 27802 Other investments-Equipment bonds 1.......1 13131 15272 22737 32020 48796 Total equity portfolio 0 83649 75611 72897 72001 78598 Fixed assets-gross 1.......1 36734 37734 38734 39734 40734 Accumulated depreciation f.......1 7001 9269 11618 14047 16556 Property P & E, net I.......1 29733 28465 27116 25687 24178 Deferred assets I.......I 31666 34136 43391 53238 65122 Other f....,...I .......11.......11.......11.......11.......I TOTAL ASSETS 0 4021434 4759820 5705460 6569533 7624303 Accrued interest f.......11.......If.......I.......11.......11.......I Deposits-demand f.......I1.......11.......11.......If.......11.......I Deposits-housing installment f.......If.......11.......11.......f1.......11.......I Total Deposits 0 0 0 0 0 0 Loan in process I.....I f.......1.......If.......11.......11.......I .Due to banks, interbank E.......1 764828 877093 1168294 1839791 2167883 Short-term debt 1......I......11f.......[.......If,,....[.,.... I Accrued expenses 1.......11.......11.......1f.......11.......11.......I Accrued taxes 1.......I 25008 68575 83265 145772 215064 Other (ncl unearned inc) 1.......1 238464 281920 347662 420478 632195 Total current liabilities 0 1028300 1227588 1599221 2406041 3015142 Long-term debt 1.......1 1564849 1730981 1929161 2155688 2355888 Medium-tera debt .. 1 187290 307290 442590 609090 820000 Other 1.......1 508601 600131 616385 651270 699290 Total domestic L/T debt 0 2260740 2638402 2988136 3416048 3875178 Foreign long-term debt 1.......1 952798 1157692 1442545 1144701 1337798 Total LT debt (net curr. mat.) 0 2975074 3514174 4083019 4140271 4580781 Credit guarantee reserves 1....... If.......11.......11.......I.......11.......I Housing lotteries I.......11.......11.......11.......11.......11,......I 31 TOTAL LIABILITIES 0 4003374 4741762 5682240 6546312 7595923 Paid-in Capital 1...... o 210000 210000 270000 270000 330000- Reserves, legal 1,.....1 5715 6833 9898 13620 20135 Reserves, general- 1.......3 52685 55863 96039 143531 244107 Unappropriated Ret. Earnings I.. .1 18060 W060 23220 23220 28380 TOTAL EQUITY 0 286460 290756 399157 450371 622622 TOTAL LIABILITIES & EQUITY 0 4289834 5032518 6081397 6996683 8218545 Error indicator (rounding) 0 -268400 -272698 -375937 -427150 -594242 PROJECTED STATEHENT OF CHANGES 1981 1982 1983 1984 1985 1986 Cash/due from banks E..,.3 45250 14625 4682 5553 10071 Remittances in transit 1.......] 0 0 0 0 0 Loans and advances I.......1 0 0 0 0 0 Due from banks/ see above E.......1 623024 122563 160764 145497 178462 Bank deposit accounts 1.......1 0 0 0 0 0 Current maturities, invstats I.......1 329347 43900 254389 2790 96163 Current maturities, deposits 1.......3 0 0 0 0 0 Govt securities- Treas Bills 1....01 0 0 0 0 0 Short-term securities, corp. 1.......3 0 0 0 0 0 Other I.......1 0 0 0 0 0 Total, current assets C.......3 997621 181088 419835 153840 284696 Housing loans I.......3 2083158 523022 699055 606745 734259 Commercial & consumer loans 1....... 3 1124954 85012 75947 98756 115006 Total loan portfolio-gross 1.......1 3208112 608034 775002 705501 949265 Losses written off .. 3 329347 43900 254389 2790 96163 Total loan portfolio-net 1.......1 2878765 564134 520613 702711 753102 Long-term securities r.....1 2000 0 0 0 0 Investment Assets 1.......I 68518 -10179 -10179 -10179 -10179 Other investments-affiliates at costl.......1 13131 2141 7465 9283 16776 Total equity portfolio 1.......1 83649 -8038 -2714 -896 6597 ,Fixed assets-gross C.....1 36734 1000 1000 1000 1000 Accumulated depreciation 1. ..1 7001 2268 2349 2429 2509 Property P & E, net E....... 1 29733 -1268 -1349 -1429 -1509 Deferred assets I.......1 31666 2470 9255 9847 11884 Other 1... 3 0 0 0 0 0 TOTAL ASSETS 1.......3 4021434 738386 945640 864073 1054770 Accrued interest I.......1 0 0 0 0 0 Deposits-demand 1.1..6 0 0 0 0 0 Deposits-housing installment 1#..... 1 0 0 0 0 0 Total Deposits [$..... 1 0 0 0 0 0 Loan in process 1....11 0 0 0 0 0 Due to banks, interbank E.......I 764828 112265 291201 671497 328092 Short-term debt C.......1 0 0 0 0 0 Accrued expenses C.......1 0 0 0 0 0 32 Accrued taxes ....... 3 25006 43567. 14690 62507 69292 Other (incl unearned inc) 1...4... 236464 43456 65742 72816 211717 Total current liabilities C. 1 1028300 199288 371633 806820 609101 Borrowing from government .. 11564849 166132 198180 226527 200200 Housing debentures . . 3 187290 120000 135300 166500 210910 National Housing Bonds C.......3 508601 91530 16254 34885 01 Total domestic L/T debt 1.......1 2260740 377662 349734 427912 459130 Foreign long-term debt LO.G..] 952798 204894 284853 -297844 193097 Total long-term debt 1.......1 2975074 539100 568845 57252 440510 Credit guarantee reserves 1.......1 0 0 0 0 0 Housing lotteries .. 3 0 0 0 0 0 TOTAL LIABILITIES I.....34003374 738388 940478 864072 1049611 Paid-in Capital 1.......J 210000 0 60000 0 60000 Reserves, legal I.......I 5715 1118 3065 3722 6515 Reserves, general 1....... 52685 3178 40176 47492 100576 Unappropriated Ret. Earnings 1.......3 18060 0 5160 0 5160 TOTAL EQUITY I.......3 286460 4296 108401 51214 172251 TOTAL LIABILITIES & EQUITY I ....... 4289834 742684 1048879 915286 1221862 Error indicator (rounding) 0 -268400 -4298 -103239 -51213 -167092 Error indicator (Assets) 0 0 0 0 0 0 Error indicator (Liabilities) 0 0 0 0 0 0 Error indicator (Equity) 0 0 0 0 0 0 33 4¼ VI » - PROJECTED STATEMENT OF SOURCE AND APPLICATION OF FUNDS 1981 1982 1983 1984 1985 1986 PROVIDED BY: Operations Income 0 22356 61301 74433 130311 159546 Depreciation 0 6075 6579 7783 8882 10404 Bad debt contingency 0 30044 33809 38608 42977 48235 Forex risk contingency 0 0 0 0 0 0 Total From Operations 0 58475 101689 120824 182170 218185 Increases in: Deposits 0 0 0 0 0 0 LT 1)ebt 0 2975074 539100 569845 57252 440510 Accrued interest 0 0 0 0 0 0 Housing lotteries 0 0 0 0 0 0 Credit guarantee reserves 0 0 0 0 0 0 Paid in capital 0 210000 0 60000 0 60000 Decrease in equity portfolio 0 0 8038 2714 896 0 0 3185074 547138 631559 58148 500510 imputed transfer in 0 18060 0 5162 1 5158 TOTAL SOURCES 0 3261609 648827 757545 240319 723853 ----------------------------------------------------------------------- USED FOR: Increases in: Loans 0 2878765 564134 520613 702711 753102 Equity portfolio 0 83649 0 0 0 6597 P&E-net 0 29733 -1268 -1349 -1429 -1509 Fixed assets 0 36734 1000 1000 1000 1OOQ Dividends 0 18060 18060 23220 23220 28380 Other uses, non-current 0 0 0 0 0 0 Deferred assets-net 0 31666 2470 9255 9847 11884 TOTAL USES 0 3078607 584396 552739 735349 799454 BALANCE 0 183002 64431 204806 -495030 -75601 CHANGES IN WORKING CAPITAL Increase (Decr.) in curr. assets Cash/due from banks 0 45250 14625 4682 5553 10071 Other curr. assets 0 952371 166463 415153 148287 274625 Decrease (Incr.) in curr. liab. ST Debt 0 0 0 0 0 0 Other curr. liab. 0 -1028300 -199288 -371633 -806820 -609101 TOTAL CHANGES 0 -30679 -18200 48202 -652980 -324405 BALANCE CHANGES 0 213681 82631 156604 157950 248804 35 CIH-MOROCCO PROJECTED LENDINS OPERATIONS 1982 1983 1984 1985 1986 APPROVALS construction loans 350000 400000 450000 500000 550000 prefinancing loans 250000 300000 350000 375000 400000 site and services loans 15000 10000 15000 15000 15000 low-cost housing loans 100000 200000 275000 300000 350000 hotel loans 100000 150000 180000 200000 220000 commercial and tourism loans 16000 18000 23000 32000 40000 TOTAL 831000 1078000 1293000 1422000 1575000 DISBURSMENTS construction loans 324589 373570 422500 472500 522500 prefinancing loans 180725 250547 310000 350000 380000 site and services loans 11311 13500 11500 15000 15000 low-cost housing loans 80000 150000 247500 302500 340000 hotel loans 188288 .143754 149000 180000 202000 coamercial and tourism loans 16298 16400 20000 27400 36400 TOTAL 801211 947771 1160500 1347400 1495900 36 -A- -B- -C- -D- -E- -F- -G- 35 AMORTIZATION TABLE Low-cost housing loans 36 end of period calculations 38 DISBURSEMENT AMOUNT 80000 150000 247500 302500. 340000 39 INTIPER YR 14.00% 14.00% 14.00% 14.00% 14.00% 40 TOTALIPMTS 25 25 25 25 25 41 IPMTS PER YR 1 1 1 1 1 42 CALC PMT 11639.87 21824.76 36010.86 44013.27 49469.46 TOTAL 1982 45 interest payment 11200.00 11200.00 46 principal payment 439.87 439.87 47 balance outstanding 79560.13 79560.13 1963 50 interest payment 11138.42 21000.00 32138.42 51 principal payment 501.45 824.76 1326.22 52 balance outstanding 79058.67 149175.24 228233.91 1984 interest payment 11068.21 20884.53 34650.00 66602.75 principal payment 571.66 940.23 1360.86 2872.74 balance outstanding 78487.01 148235.01 246139.14 472861.17 ------------------------------------------------------------------------------------------ 1985 interest payment 10988.18 20752.90 34459.48 42350.00 108550.56 principal payment 651.69 1071.86 1551.38 1663.27 4938.19 balance outstanding 77835.32 147163.15 244587.77 300836.73 770422.97 1986 interest payment 10896.95 20602.84 34242.29 42117.14 47600.00 109728.68 principal payment 742.93 1221.92 1768.57 1896.13 1869.46 7499.00 balance outstanding 77092.40 145941.23 242819.20 298940.61 338130.54 1102923.97 37 AMORTIZATION TABLE Construction loans end of period calculations DISBURSEMENT AMOUNT 324589 373570 422500 472500 522500 INT%PER YR - 14.00% 14.007 14.00% 14.00% 14.00% TOTALIPMTS . 3 3 3 3 3 IPMTS PER YR 1 1 1 1 1 CALC PMT 139810.70 160908.36 181984.05 203520.62 225057.20 TOTAL 1982 interest paysent 45442.46 45442.46 principal payment 94368.24 94368.24 balance outstanding 230220.76 230220.76 1983 interest paysent 32230.91 52299.80 84530.71 principal payment 107579.79 108608.56 216188.35 balance outstanding 122640.97 264961.44 387602.41 1984 interest paysent 17169.74 37094.60 59150.00 113414.34 principal payment 122640.97 123813.76 122834.05 369288.77 balance outstanding .00 141147.68 299665.95 440813.63 1985 interest paysent 19760.68 41953.23 66150.00 127863.91 principal payment 141147.68 140030.82 137370.62 418549.13 balance.outstanding .00 15963513 335129.38 494764.51 1986 interest payment 22348.92 46918.11 73150.00 221174.23 principal payment 159635.13 156602.51 151907.20 468144.84 balance outstanding .00 178526.86 370592.80 549119.67 38 AMORTIZATION TABLE Prefinancing loans end of period calculations DISBURSEMENT AMOUNT 180725 250547 310000 350000 380000 INTXPER YR - 14.00% 14.00% 14.00% 14.001 14.00% TOTALIPMTS 3 3 3 3 3 IPMTS PER YR 1 1 1 1 1 CALC PMT 77843.95 107918.48 133526.76 150756.02 163677.96 TOTAL 1982 interest payment 25301.50 25301.50 principal payment 52542.45 52542.45 balance outstanding 128182.55 128182.55 1983 interest payment 17945.56 35076.58 53022.14 principal payment 59898.39 72841.90 132740.29 balance outstanding 68284.16 177705.10 245989.26 1984 interest payment 9559.78 24878.71 43400.00 77838.50 principal payment 68284.16 83039.77 90126.76 241450.69 balance outstanding .00 94665.33 219873.24 314538.57 1985 interest paysent 13253.15 30782.25 49000.00 93035.40 principal payment 94665.33 102744.51 101756.02 299165.86 balance outstanding .00 117128.74 248243.98 365372.72 1986 interest payment 16398.02 34754.16 53200.00 161630.14 principal paysent 117128.74 116001.86 110477.96 343608.56 balance outstanding .00 132242.12 269522.04 401764.16 39 AMORTIZATION TABLE Site and services loans end of period calculations DISBURSEMENT AMOUNT 11311 13500 11500 15000 15000 INT%PER YR 14.00% 14.00% 14.00% 14.00% 14.00% TOTALIPMTS 3 3 3 3 3 #PMTS PER YR 1 1 1 1 1 CALC PMT 4872.00 5814.87 4953.41 6460.97 6460.97 TOTAL 1982 interest payment 1583.54 1583.54 principal payment 3288.46 3288.46 balance outstanding 8022.54 8022.54 1983 interest payment 1123.16 1890.00 3013.16 principal payment 3748.85 3924.87 7673.72 balance outstanding 4273.69 9575.13 13848.81 1984 interest payment 598.32 1340.52 1610.00 3548.83 principal payment 4273.69 4474.36 3343.41 12091.46 balance outstanding .00 5100.77 8156.59 13257.36 1985 interest payment 714.11 1141.92 2100.00 3956.03 principal payment 5100.77 3811.49 4360.97 13273.23 balance.outstanding .00 4345.10 10639.03 14984.13 1986 interest payment 608.31 1489.46 2100.00 6458.75 principal payment 4345.10 4971.51 4360.97 13677.58 balance outstanding .00 5667.52 10639.03 16306.55 40 AMORTIZATION TABLE Hotel loans end of period calculations OISBURSEMENT AMOUNT 188288 143754 149000 180000 202000 INTXPER YR 14.00% 14.00% 14.00% 14.00% 14.00% TOTALIPMTS 3 3 3 3 3 #PMTS PER YR 1 1 1 1 1 CALC PHT 81101.57 61919.37 64178.99 77531.67 87007.76 TOTAL 1982 interest payment 26360.32 26360.32 principal payment 54741.25 54741.25 balance outstanding 133546.75 133546.75 -------------------------------------------------------------------------------------- 1983 interest payment 18696.55 20125.56 38822.11 principal payment 62405.02 41793.81 104198.84 balance outstanding 71141.73 101960.19 173101.91 -------------------------------------------------------------------------------------- 1984 interest payment 9959.84 14274.43 20860.00 45094.27 principal payment 71141.73 47644.95 43318.99 162105.66 balance outstanding I .00 54315,24 105681.01 159996.25 -------------------------------- ------------------------------------------------------ 1985 interest payment 7604.13 14795.34 25200.00 47599.47 principal payment 54315.24 49383.65 52331.67 156030.56 balance outstanding .00 56297.36 127668.33 183965.69 -------------------------------------------------------------------------------------- 1986 interest payment 7881.63 17873.57 28280.00 84482.96 principal payment 56297.36 59658.10 58727.76 174683.22 balance outstanding .00 68010.23 143272.24 211282.47 ---------------------------------------------------------------- 41 AMORTIZATION TABLE Commercial and tourism loans end of period calculations DISBURSEMENT AMOUNT 16298 16400 20000 27400 36400 INT%PER YR - 14.00% 14.00% 14.00% 14.00% 14.00% TOTALIPMTS 3 3 3 3 3 IPMTS PER YR 1 1 1 1 1 CALC PMT 7020.06 7064.00 8614.63 11802.04 15678.63 TOTAL 1982 interest payment 2281.72 2281.72 principal payment 4738.34 4738.34 balance outstanding 11559.66 11559.66 1983 interest payment 1618.35 2296.00 3914.35 principal payment 5401.71 4768.00 10169.71 balance outstanding 6157.95 11632.00 17789.95 1984 interest payment 862.11 1628.48 2800.00 5290.59 principal payment 6157.95 5435.52 5814.63 17408.09 balance outstanding .00 6196.49 14185.37 20381.86 1985 interest payment 867.51 1985.95 3836.00 6689.46 principal payment 6196.49 6628.68 7966.04 20791.21 balance.outstanding .00 7556.69 19433.96 26990.65 1986 interest payment 1057.94 2720.75 5096.00 14361.32 principal payment 7556.69 9081.29 10582.63 27220.61 balance outstanding .00 10352.67 25817.37 36170.04 42 CONSOLIDATED AMORTIZATION TABLE - 1985 1986 1987 1988 1989 construction loans interest payment 45442.46 84530.71 113414.34 127863.91 221174.23 principal payment 94368.24 216188.35 369288.77 418549.13 468144.84 prefinancing loans interest payment 25301.50 53022.14 77838.50 93035.40 161630.14 principal payment 52542.45 132740.29 241450.69 299165.86 343608.56 site and services loans interest payment 1583.54 3013.16 3548.83 3956.03 6458.75 principal payment 3288.46 7673.72 12091.46 13273.23 13677.58 low-cost housing loans interest payient 11200.00 32138.42 66602.75 108550.56 109728.68 principal payment 439.87 1326.22 2872.74 4938.19 7499.00 hotel loans interest payment 26360.32 38822.11 45094.27 47599.47 84482.96 principal payment 54741.25 104198.84 162105.66 156030.56 174683.22 commercial and tourism loans interest payment 2281.72 3914.35 5290.59 6689.46 14361.32 principal payment 4738.34 10169.71 17408.09 20791.21 27220.61 interest payment, TOTAL 112169.54 215440.87 311789.28 387694.84 597836.07 principal payment, TOTAL 210118.61 472297.13 805217.42 912748.17 1034833.81 TOTAL PAYMENT 322288.15 687738.00 1117006.70 1300443.01 1632669.88 43 М � ,���.,� � - ��� ��� � � �� ь в . � л � �� A to Pjtd Lending Oper. K print Bal Sht&Stmt Chgs, U copy Amort->Consol B to Amortization Table L to Mgmt Ratios V to Consol table C to Income Stmt. Output M print Sources & Uses W print Consol. D to Balance Sheet N print Ratios X graph Mgmt Ratios E to Stmt-of Chgs 0 Set Verticle Titles Y to Financing Plan F to Sources & Uses P Clear Verticle Titles Z to Macros 6 to Ratios 0 No Decimals-Global 0 copy Act->Frcst H print Amort Table R Two Decimals-Global I print Financing Plan S copy Year Headings TEMP2 J print Income Stmt T print Pjtd Lending Oper. -PROJECTION MIODEL- 45 P a a 4 1 * - /wtv verticie titles p /MC clear titjRs \9 /rff 0 no decimals k2..r275" \R /rff2 two fecisals k2. .r372 /rffo" format yrs 15..q5' no decinals /rff0' 179..q794 /rffO* 1149..q149% /rff0" 1224..q2241 /rffO' 1279..q279% Irf0 1296.. q296* \/ copy yrs oper B14,A44' /c c14'A49" /c d14A54' e14'A59' Ic copy to inc b14..f 14 stet a5.. q5 Xcalc}/c copy yr headings 15. .q5' to stat chgs 1149. .q1491 /c copy yr headings 15..q5 to s & u 1224..q2241 Ic copy yr headings £5..q5, to ratios M 1279.. q279* Ic copy yr headings 15,.qS" to ratios (2) 1296..q296% Ic copy yr headings 15..q5' to bal sht out 179..q79% !cf 14'a64" /c 15.. q5% bl28. .q128* mgat ratios /c ai!*kl* copy title 47 /c b14. f14' yrs to consol b71..b71 \Y {goto}a96* to fin plan \T /pp print pjtd loans ral3.. f33' gq {goto}a69" to consolidated amortzn table /pp print consol ra69..f93'gq \U {goto}g45" builds consol {edit}{calc}* table (goto}g46' {edit}{calc}' {goto}g50, .edit}{cal c} {goto}g511 {edit}{calc}" {goto}g55' {edit}({calc}' (goto}g56' {edit}{calc}' (goto}g601 (edit}{calc}" {goto}g61' {edi t} {cal c}' {goto}g65" {edit}{calc}r {goto}g66' {edit} {calcY g45"b3701 g46*'b371" / g50"c370' I@ g51"c3711 g55»d370' /ø g56'd3711 g60'e370' la gål'we371' g65"f370" Ie g66'f3711 48 la b370.. f 371{?} /c i45'g45' /c' - i46"g461 /c i 50'g50o, iS1g51, /c i55,g55 . /c i56'g56' i60'g60' /c 161,g61, /c i 65 g65' /c i66'g66" (goto}a34* \0 {goto}l7' bring in /fcan "actualP frma actual, TEMPLATE tempilate, {goto}k2' lca13'113" Icm19'119" /cø21'1211 /cm25'125' /cm28'1281 /c*37'1371 /c m401 40 /cf46'146' /cm51*151' /cm55,1551 /c158,4158, /cf62'1621 /c#65'1651 /cm72'1721 /cm74'174" /ca75 175' c*92 1 92' /c§95,1951 /c*98'198" lc&103'1103" /c&110'1110' /ci116%1116 /cm124»1 124" ./cam128"1 128" /ca131,1131" /cm135'1135" /ca142a1 142" 49, /cml44"1 144" IcaI45qI 145, {goto}bl3o, /fcan actmgmt, template' 5 50 긔