STRATEGIC CREDIT RISK MANAGEMENT TEMPLATE INSTRUCTIONS -2007This Excel template has been designed to assist in calculating the credit risk profile of financial institutions based upon the model presented in the Strategic Credit Risk Management book written by John E. McKinley and John R. Barrickman, and published by RMA. The workbook template contains four worksheets. 1) The first worksheet is the Credit Risk Profile report which assesses the information you enter about your institution. 2) The second worksheet contains an Geographic Concentration Matrix worksheet that can be used and printed to determine the Geographic Concentration for your financial institution. 3) The third worksheet contains a Line of Business Risk worksheet that can be used and printed to determine the Intrinsic Risk Score for each "Line of Business" to which your financial institution lends. 4) The fourth worksheet contains a Profile Chart worksheet that can be printed to graphically summarize the Credit Risk Profile conclusions. To access the worksheets, open the file on the project website called “Template” (it’s under the Credit Risk Management project section). Credit Risk Profile Press the CTRL + HOME keys to move to the beginning of the template report, and then use the arrow keys to move around. To input data, use the arrow keys to move the cursor around the spreadsheet. General instructions: -- Input data into cells which are either underlined or marked in BLUE. -- Enter dollar amounts in thousands. If too large of number is entered, the cell will display "#######". -- Do not enter "," (commas) in numbers. -- Do not enter "$" (dollar signs) in front of currency amounts. -- Enter percentages as decimals (e.g., .01 rather than 1%). Do not enter the "%" (percent sign). -- Enter data in either the "8-Point Risk Rating System" or the "10-Point Risk Rating System". Do not enter data into both Systems. -- Be sure to enter the Intrinsic Risk Scores in cells g76, g93, g110 and g131 ("Remaining Portfolio" for the C&I, Agricultural, and Commercial Real Estate Industry Risk Profiles and "Remaining Portfolio" for the Lines of Business Profile, respectively.) -- If the bank is a "community" bank and/or the portfolio is not coded by geographical locations, a "Geographic Concentration Risk Profile Point" can be intuitively assigned rather than calculated. Once all data is entered, simply select File, and then select Print. An Industry Risk worksheet is included in this workbook, but on a separate worksheet. Simply enter either the Industry name, and then enter an “X” to respond to each question. Once all data is entered, simply select File, and then select Print. Once the report is printed, simply highlight columns E through H, and rows 4 through 48, and press the Delete button. This will clear the prior answers and prepares the worksheet for the next Industry. A Geographic Concentration Matrix worksheet is included in this workbook, but on a separate worksheet. This chart is provided to help you determine the point on the concentration scale based on the percent of concentration and the economic diversity of the geographic entity. "Size" should relate to both size and diversity of the economy. A Profile Chart is included in this workbook, but on a separate worksheet. Once all data is entered in the Profile Template, simply select File, and then select Print. If you want to mark the Risk Scores on the Chart, you need to simply write “T”, “I”, and “C” on the chart in the appropriate places.