D&B’s Risk Assessment Manager™
Portfolio Management
Topics Covered in Each Section
1 2 Topic Portfolio Management Overview RAM Score Thresholds Setting your RAM Score Thresholds Analyzing the Risk of your entire Portfolio Working with Account Filters Creating an Account Filter Deleting an Account Filter Database Specific Configurations Setting up Events Monitor Account Profiles Creating an Account Profile Sorting an Account Profile Editing an Account Profile Reordering a Column Printing from an Account Profile Exporting from an Account Profile Summarize by an Account Profile Days Sales Outstanding - DSO Calculating your Days Sales Outstanding Uncovering your DSO Opportunity Areas Customer Base Analysis - CBA Creating a New Customer Base Analysis - CBA Running a new CBA Creating a List of Accounts from your CBA Creating a Graph from a CBA section Creating a Filter from a CBA section Transferring Data Into and Out of RAM Importing Data Importing Procedure Creating an Export Template Running an Export Procedure Filter Examples Guidelines for Importing Files CBA Examples Page 3 4 4 5 6 6 9 10 10 12 12 13 14 16 17 17 18 20 20 21 23 23 25 26 28 30 31 31 32 35 37 39 41 43
3
4 5
7
8
9
Appendix A Appendix B Appendix C
2
Portfolio Management Overview
Portfolio Management is a process that allows Financial Professionals the opportunity to obtain a deeper and more comprehensive fact-based insight into their customers. This insight is the result of structured analytics that assist in uncovering missed opportunities and areas of overexposed risk. D&B’s Risk Assessment Manager (RAM) is a powerful and efficient Portfolio Management tool that allows you to quickly perform analytics for collections and/or credit monitoring purposes. As a result of integrating D&B data elements with your internal accounts receivable data, RAM is fully adaptable to your company’s needs. By capitalizing on the analytical features found throughout the RAM application, you are able to rapidly isolate any segment of your customer database for further action. The ability to identify these accounts allows you unparalleled control of your credit resources. As a key example, RAM customers who utilize Portfolio Management techniques are able to automatically identify that segment of their customer database which inherently exposes them to the highest amount of risk. In response, they can shift resources to focus on collections and further isolate themselves from future write-offs. Additionally, RAM customers have the ability to isolate their low-risk customers who have previously been undersold. In response, RAM customers can generate a list for their marketing and sales partners for instantaneous campaign list generation to further penetrate their high-performing, low risk customers. As one can conclude from these examples, Portfolio Management will help improve your bottom line by decreasing risk and increasing opportunities. This unique analytical ability is critical in today’s world of diminishing resources while focusing on increasing the result of tighter profit margins. This Workshop is intended to provide you, the Financial Professional, with a better understanding of how to maximize your investment in the RAM application by leveraging its Portfolio Management functionality.
3
RAM Score Thresholds
Setting your RAM Score Thresholds
RAM has a unique ability to quickly analyze the entire Risk of your portfolio. This information is clearly portrayed in a pie chart in the RAM Score Thresholds section. To set your RAM Score Thresholds, simply enter the numbers (0 – 10) in the Acceptance Point box and Rejection Point box, then click on small graph icon below.
Note: This section is where your cut off points are created for the Credit Analysis Section of RAM. When you change the numbers here, they will be modified in the graphed section of the RAM Score tab within the Credit Analysis Section.
4
Analyzing the Risk of your Portfolio
After clicking on the small pie chart Icon, a graphed chart will appear like the one below. The Red section is for the Rejected/COD/Deposit Accounts, Green section for approved accounts and theYellow section is for the accounts which need more analysis prior to making a decision. This analysis is based upon your thought process, which creates the RAM Score. Notice the breakdown of the various accounts is given. If you would like to know who these accounts are, simply create a Filter that segments the Accounts by RAM Score. An example is located in the Filters Appendix and will read something like “RAM Score less than _ “.
Note: The RAM Score Threshold screen is effected by Filters which maybe applied within RAM. If you are analyzing your Portfolio and it does not appear correct, check and make sure you do not have a Filter currently applied within RAM.
5
Working with Account Filters
Creating Account Filters
You can use Account Filters to create ad hoc queries against your database and then conduct all your account analysis within that subset of accounts. After a filter is created and selected, all of RAM’s views and analyses reflect that filter. With Account Filters, you map a business question to the RAM database in the form of a Standard Query Language (SQL) expression. SQL expressions often look and sound like simple English sentences. RAM’s Criteria Builder window (where all SQL expressions are built) displays an English translation of the rule by default.
Examples of SQL Expressions: If I ask this question… Can I view just my accounts that are corporations? Who are my high-risk accounts with a low Credit Score and low RAM Score? Do I have any accounts in Tennessee with a 5A1 D&B Rating who owe me more than $10,000.00 in the Past Due 30 Days category? Do any of the accounts in my RAM database have Accounts Receivable information since January 1st? The SQL Expression would read like this: Business Structure is equal to Corporation
Credit Score is less than 20 and RAM Score is less than 2
Rating D&B = ‘5A1’ and State is equal to ‘Tennessee’ and Past Due 30 is greater than 10000
Accounts Receivable refresh date is greater than ‘1999-01-01’
6
Building an Account Filter
Account Filters consist of one or more SQL expressions in a profile. Use the following example to better understand how to build an Account Filter. Steps 1. Select the Filters folder in the Tree Pane. The Filters screen displays. 2. Click the New Item icon located in the toolbar. The Create a New Filter window displays.
3. Type the new filter name in the Enter the new Filter Name field. 4. Click OK. The Criteria Builder screen displays.
5. Click the Database Fields drop-down list to select a database field. 6. Click the Operators drop-down list to select an operator. 7. Click the Suggested Values drop-down list to select a value. This will only populate if there is a Lookup Table associated with the selected Database Field. 8. Click the Add to Criteria button to display the criteria you specified.
9. Click the checkbox to Calculate the Number of Accounts Affected if you want to see how many accounts meet your criteria.
7
10. Click OK. The Filters screen displays the expressions that comprise this filter.
Note: If you are in a RAM LAN setup and you would like for other users to use the filter you created check the Global Filter box.
11. To apply this filter, navigate back to the Account List view by clicking on the Account Information folder. Click on the drop down menu and select the desired Filter. The current Filter selection will be depicted in the list by bold lettering. In the example below "Corporations" is the selected filter.
8
To make a compound expression
1. Double-click on the name of the expression you just created. The Criteria Builder screen displays. 2. Repeat steps 5-11 adding the appropriate expression. The new criteria displays on the Filter screen
Delete an Account Filter
You can delete an existing Account Filter using the Filters screen. Steps 1. Select the Filters folder in the Tree Pane. The Filters screen displays. 2. Click on the filter you want to delete. 3. Click the Delete icon located in the toolbar. The filter is deleted.
Note: Appendix A at the back of this Workbook has numerous examples of common filters which are frequently set up.
9
Database Specific Configurations
Setting Up the Events Monitor
The Events Monitor section is used to keep a running history of any changes that occur in specified data fields during batch or online refreshes. You have control over what fields are monitored, which allows you the freedom to track your business as you see fit. Also, these history elements are maintained from now until you decide to stop monitoring them, which should show long-term trends and yield new insights on your accounts. Steps 1. Select Events Monitor from the Configuration Settings/Database Specific Configuration folder in the Tree Pane. The Events Monitor screen displays.
2. Click on a data field check box to select or deselect an item. 3. Click the Save icon in the toolbar to save the events you selected.
Note: It is suggested to only choose data elements that change infrequently and a small change may significantly effect the risk of an account. Some suggestions are checked off in the box above. Elements that are not recommended to select are Paydex Score, Credit Score and Average D&B Credit. This information changes frequently and might provide little or no benefit while using space on your system. Events Monitor Performance Considerations 10
The Events Monitor has the potential to make your database grow significantly. For each item that you select, an independent row is written to the database. When you consider that a row is written for each account every time a change event occurs, you can calculate the potential impact. For a database with 1,000 accounts, if you select 10 Events Monitor items, there could be as many as 10,000 rows added to your database during a batch refresh process (1,000 accounts x 10 items = 10,000 rows). Please consider Note above and carefully consider the items to track.
Viewing Changes in the Event Monitor
The All Events Monitor tab on the Tree Pane screen displays changes regarding your accounts. A running history of any changes that occurred to specified data fields during batch or online refreshes. Select All Events Monitor folder in the Tree Pane. Choose from the date drop down box to track changes within a certain time period.
In addition to be able to view all of the changes which affect your accounts, you are also able to view specific changes regarding a single account. Simply identify the account you would like to inquire upon then navigate to the D&B Data View then click on the Event Monitor Tab. An example screen shot is below.
11
Working with Account Profiles
Creating an Account Profile
You can have multiple column layouts tailored for specific tasks. Steps
1. Select the Account Profile folder in the Tree Pane. The Profile screen displays. 2. Click the New Item icon located in the toolbar. The Create New Profile dialog displays.
3. Type the new profile name in the Enter the New Profile Name field. 4. Click OK. The Select Columns screen displays. 5. From the Available Fields list, select the appropriate field, such as Account Number and then click the button . The field you selected displays in the Selected Fields list.
6. To remove a field from the Selected Fields list, select the field and then click the button. Continue this process until you have selected or deselected all of the Available Fields you need.
Note: You can reorder the Selected Fields by clicking on the field name you want to move and then clicking on the Move Up or Move Down button.
7. Click OK. The Profile screen displays with the new profile listed.
12
Sort Your Account List
Your customized Account Profiles allow you to sort on any column that is displayed in your account listing. Steps 1. Select the Account Information folder in the Tree Pane. The Account List screen displays.
2. Click anywhere on the column header (e.g. Account Number or Customer Name). A shortcut menu displays.
3. Click on the sorting option you want to perform.
13
Editing an Account Profile
When you select Notes, ToDo’s, Bank or Trade References, Financial Statements or your Account List, you are shown the available records in a series of rows. These rows are a convenient way to review all available records for an account, and so they are called Summary Listings. Initially, each Summary Listing has a default configuration of columns that is displayed. However, you have the option to choose your own column configuration. The following steps show you how to select columns for your Account List. Steps 1. Select Account Information folder in the Tree Pane. The Account List screen displays.
2. Click on the column bar to display a sort menu. The column bar is the bar with items like account number, and customer name.
3. By clicking within this grey header bar a small drop down box appears.
14
3. Click on the Select Columns option. The Select Columns screen displays.
4. To add a field, highlight the desired Available Field and click the highlight the desired Selected Field and click the icon.
icon. To remove a field,
5. You can also arrange the order of the fields. This is the order they will appear in the Summary Listing. To do this, highlight a Selected Field and either choose or .
15
Reorder a Column
Using the drag-and-drop technique, you can re-arrange the order of columns in a Summary Listing without opening the Select Columns screen. Drag-and drop refers to clicking on a column heading and moving it while holding down the left mouse button. Once you position the column border in the desired place and release the mouse button, the column is moved automatically. Steps 1. Click and hold down the left mouse button on the column you want to move. This action will generate a solid black column border.
2. While holding down the left mouse button move the column border to the right or left and position it where you want it to appear. 3. Release the mouse button. The column is moved to its new location.
Note: Changes made to columns using drag-and-drop are temporary. Columns will move back to the previous settings when you change views.
16
Printing from an Account Profile
It is easy to print directly from the Account Profile when using RAM 5.5.
1. Click on the column bar to display a sort menu. The column bar is the bar with items like account number, and customer name.
2. Simply choose Print from the drop down box and all of the accounts within the current filter will be printed in a list format.
Exporting from an Account Profile
Exporting Account information is also easily done directly from the Account Information Screen when using RAM 5.5. Simply follow steps outlined above then choose Export. A small window will appear like below which will allow you to name and save this CSV file to a specific location.
17
Summarizing within your Account Profile
Another option within the Accounts Profile section is to summarize your accounts by the various columns listed within your Profile. Access this feature by clicking on the gray column header bar that houses the names of your columns. The following sort window will appear. Navigate your mouse to the bottom of this window to ‘Summarize By’ and you can summarize by any of the various columns within your current Profile.
Click on one of the various topics you would like to summarize your Database by and a window will appear like the one below.
This table quickly summarizes your Portfolio by the various elements listed within your Account Profile. The gray column on the far left breaks down the various categories for the element you picked. You 18
can also click on either the ‘Sum’ or ‘Average’ radio dial for various analyses, depending on what topic is chosen.
By double clicking into the white portion of any of the rows, you can drill down into a specific segment of accounts. An example of this step is outlined below. The Total Amount owing segment of between $100,000 and $250,000 was double clicked into. This step developed the following list of accounts. You can then further sort this information by clicking on the gray header bar as outlined on page 14, ‘Sorting your Account List’.
19
Days Sales Outstanding – DSO
______________________________________________________________________________
Calculating Days Sales Outstanding
After importing aging information into RAM, calculating Days Sales Outstanding is simple. RAM has an automatic Days Sales Outstanding calculator built directly in it. Simply navigate to the Days Sales Outstanding section just above the Filters section on the Tree Pane. The following window will appear.
Simply type in a Sales Figure (no commas or $ symbols) into this box then click on the respective Monthly, Quarterly, Semi-Annual or Annual radio dial which is associated with this Sales figure. The DSO calculation will automatically be calculated. Below is an example of typing in a Monthly Sales figure of 7000000 and the DSO calculation is automatically figured out to be 41.
20
Uncovering DSO Opportunity Areas
Now that you have figured out the DSO, this information can be further analyzed to determine exactly where you should be focusing your efforts to reduce your DSO. In the example below the DSO was calculated as 41. The columns to the right of the aging buckets break down various segments of your account portfolio. The first column ‘Amount’ refers to the total amount owed to you within each segment of your aging buckets. The next column ‘DSO Without Line-Item’ refers to what your DSO might be if you collected the aging dollars from this point backwards. The ‘DSO Line-Item Only’ simply refers to how many days you can reduce your DSO by if you collect this entire segment of aging. You can roughly minus the DSO Without Line Item by DSO Line Item Only and calculate what your DSO will be.
Once the DSO is calculated you can easily drill down into identifying which accounts to target for collection. Simply double click into a specific Aging Category to identify these accounts in a list format.
21
Note: RAM’s DSO calculation is rounded upward, thus not an exact specific number. This calculation is accurate although not precise.
RAM’s DSO calculation is as follows: (Accounts Receivables / Sales) X 365 The above example computes a Monthly Sales figure, thus the calculation would be multiplied by onetwelfth of 365 days or @ 30.41 days. Note: RAM’s DSO calculation has the ability to calculate DSO while working within a Filter. Thus it should be clearly understood what you are attempting to analyze when computing the DSO. Most calculations are done using the entire aging Portfolio.
22
Customer Base Analysis – CBA
_________________________________________________________________________________
Creating a Customer Base Analysis – CBA
RAM has an outstanding ability to automatically identify your bad or questionable accounts and then assist in making a Credit or Collection decisions on those accounts. Because RAM holds information electronically in a database, it can also be utilized for a variety of other analysis purposes. The Customer Base Analysis section of RAM is a very powerful way to manage your entire Portfolio of customers. The CBA section has the ability to analyze data within your database while comparing it to any other data held within RAM. Basically, you are able to quickly and easily ‘slice and dice’ your database in a variety of ways. To access the CBA section, simply click on the Customer Base Analysis section on the Tree Pane. To create a new CBA, simply click on the new Icon The screen will look like the example below. . You have now initiated creating a new CBA.
Now simply fill out the CBA Name by typing this in. The other choices are to choose various elements which you would like to analyze. This is done by simply choosing information from the various drop down boxes and filling out the radio button options.
23
The first drop down box ‘Analyze (rows)’ will simply create the elements in each of the rows while the ‘By (columns)’ drop down box simply fills in the data within each column of your matrix which you are going to create for analysis. The ‘Statistic to track’ drop down box is optional. If you keep this as ‘none’, the matrix will simply fill in the number of your accounts which fit the description of the elements chosen above. If a specific element is chosen from the ‘Statistic to track’ drop down box such as the Total Amount Owed, it will add up this calculation for you directly into the matrix. If you do choose to pull some element from the ‘Statistic to track’ drop down box, the other options on the right side of the window will also become available. The ‘Sum’ or ‘Average’ of the ‘Statistic to track’ can be automatically calculated by choosing these from the ‘Tabulation’ section. The ‘Criteria’ section simply provides and option of viewing your data normally or as a percent of the Total, Row or Column. After creating a CBA, it is easy to go back and switch or modify the choices in the rows, columns and statistics to track sections. Note: User Field information is one of the various options you can choose from within the CBA analysis. Combining D&B data and customer data often develops some very useful and effective analysis. Below is an example of the steps to create a CBA. The elements that were used were D&B Average High Credit for the ‘rows’ and D&B Paydex score for the ‘columns’. The statistic to track was the ‘Sum’ of the ‘Total Amount Owing’.
24
Running a CBA
After saving the created CBA then clicking on the which you can ‘Run’ or ‘Edit’ your created CBA’s. Icon, RAM brings you back to a screen, from
To run a specific CBA, simply highlight it and click the ‘Run CBA’ button. The output will vary depending on what elements were chosen within your CBA, but the format will always be similar. The row categories going down the left side of the screen while the various column segments across the top. The white area in the middle is the output of dollars or numbers of accounts to be analyzed. The example below runs the CBA on the previous page, which analyzes Average D&B Credit and D&B Paydex Score while tracking the Total Amount Owed to the customer.
25
Creating a List of Accounts from a CBA
Below is the previous CBA example computed. I then paged my screen down once and highlighted a specific segment of accounts by clicking and dragging on them with my mouse. The accounts highlighted in this example are all accounts that have Paydex Score between 75 – 79 and an Average High Credit of between $100,000 and $500,000. Generally this indicates these accounts are great paying customers with large, established lines of credit. Also note that the aging dollars which these customers owe is less than $20,000 in each category.
These accounts present a tremendous opportunity. They have and Average D&B High Credit of between $100,000 and $500,000 dollars and Paydex Scores between 75 – 80. Furthermore, as stated above the credit being extended to these accounts is less than $20,000 dollars to each segment.
26
After highlighting a segment of select accounts, simply click on the ‘View Accounts’ button near the top of the screen to create a list of these specific highlighted accounts as shown below.
Note: Like many area’s of the RAM Software, when a gray header bar appears listing the specific columns like above, you can further sort or modify this data simply by clicking into the gray header bar and following the options available.
27
Creating a Graphed Analysis using CBA
The steps to create a graph within the CBA section of RAM are similar to those outlined above. The first step is to identify a group of accounts that you would like to analyze graphically. After running a CBA, simply highlight the select group of data that you would like to graphically analyze. The example shown below highlights accounts with a Financial Stress Score and a Credit Score between 0 – 70, while tracking the Sum of the Total Amount Owing in the boxes.
Note: When analyzing an entire Portfolio, do not include the Total Amounts within your selected group. This will cause inaccuracies within your analysis due to double counting the accounts.
28
After highlighting a select group of accounts, simply click on the Graph icon on the top left side of the CBA Section. A graphed analysis of your accounts will appear like below.
This graph can be expanded, printed and even exported into a report simply by clicking on one of the various Icons at the top of the graph section.
29
Creating a Filter within CBA
Much like the previous two sections, the first step in creating a Filter within the CBA section of RAM is to identify a select group of accounts by highlighting these accounts with your cursor. The example below highlights accounts, which have a Paydex, score of between 50 – 70 and have dollars owed in the Past Due 30 Days category.
To create a Filter of these accounts which fit this description, simply click on the ‘Create Filter’ button located next to the ‘Graph’ Icon. This will create a Filter in your Filter drop down selection list which will appear as “CBA – ‘title of CBA here’ “. An example of how this will appear is shown below.
30
Transferring Data Into and Out of RAM
The following topics are critical whenever you are importing or exporting data. Account Number within RAM must match your other proprietary systems Import an ‘ASCHI’ type file from a location to the RAM/Import Folder Template and File must match up sequentially
These topics are expanded upon in Appendix A &B and will be emphasized while demonstrating Importing and Exporting Data on the proceeding pages.
Importing Data
If you registered with D&B to receive periodic batch refreshes, you’ll use RAM’s Import facility to insert that data into the database. However, you can import data under many other circumstances. The Import tool is RAM’s interface for updating data in bulk. RAM can import the following types of data:
D&B Data Industry Norms and Ratios Customer Data (e.g., name, address, Accounts Receivable data) Financial Statement Data (e.g., statement date, financial data) Trade Reference Data (e.g., references from trading partners) Bank Reference Data (e.g., references from banking partners) Custom Field Data Notes & To Do’s
31
Import Procedure
You can import the following types of data from import files: Customer, References, Bank References, Financial Statements, User Data, Industry Norms and Ratios, or Notes & To Do’s. In RAM, all imports are done under Administration Functions. In a stand-alone environment, this organizes your system by keeping all administrative tasks centralized within this facility. To import types of data from import files, complete the following. Steps 1. Select Transfer from the Administration Functions folder on the Tree Pane. The Transfer (Import) screen displays.
2. Click the New Item icon
. The New Template screen displays.
3. Type the template name and click OK. Your template name will now be added to the Import Templates menu with a .IXF extension. These templates are stored in the Import folder located under the RAM5x directory. 4. Select the type of Delimiter you want to use from the Delimiter drop-down list. You can select either Asterisk (*) or Comma (CSV). See Creating Import Files for further details.
32
5. Select the Data File for Import from the Data File drop-down list. Note: Your Data File must be stored in the Import folder located under the RAM51 directory. 6. Begin adding the fields from the Available fields list to be used for import. Click field over to the Selected fields side. Use the longer desired to move a
to remove any selected fields that are no
Note: The Account Number field is a primary key in the RAM database and, therefore, is required for every import file. It tells the Import engine exactly where to write the data. 7. After you have selected the appropriate fields, arrange the Selected fields in the proper order to match the Data File. Use the Up correct order. or Down arrows on the right to place the fields in the
Note: The order of the incoming fields is vitally important to RAM because it ensures that each field will be written to its correct location in the database tables. 8. If you have extra fields on the incoming data file that you do not want to import with this template, click the Filler button list. Use the Up and Down . The Filler will now be listed in the Selected fields arrows to place the Filler in the appropriate spot.
9. It is always recommended that you test the Import prior to running. This helps to make sure all the fields match up properly and you avoid errors. To do this, click on the Test Import button . The Import Test screen displays.
10. If any problems exist, an error message will appear under the Status column. To correct the
33
data click the Edit File
button. The file will open to Wordpad for editing.
11. To test and view other records from the incoming data file, use the First, Next, Prev, or Last button in the Navigation Group. 12. Click the Close button to close the Import Test screen. . The Data Transfer
13. To begin the Import process, click on the Run Import button Status screen displays, tabulating how many records are being imported.
You can minimize the window and continue working on other items. You can also click on the Abort button to cancel the import at any time.
When the import is completed, a message displays stating Import Completed.
34
Creating an Export Template
An export template must be created before you can export any data from your database. To create an export template, complete the following:
1. Select Transfer from the Administration Functions folder in the Tree Pane. Choose Export from the Template Type panel. The Transfer Export screen displays.
2. Select the New Item icon
. The New Template screen displays.
35
3. Type the template name in the New Template Name field and click OK. Your template name will now be added to the Export Templates menu with a .EXF extension.
4. Select the type of Delimiter you want to use from the Delimiter drop-down list. You can select either Asterisk (*) or Comma (CSV). See Creating Import Files for further details.
5. Type the name of the Data File to export the data in the Data File field.
6. Begin adding fields from the Available fields list to be used for export. Click over to the Selected fields side. Use the to remove any Selected fields.
to move a field
7. The order of the data fields may be important to your target application. After you have selected the appropriate fields, arrange the Selected fields in the proper order to match the Data File. Use the Up or Down arrows on the right to place the fields in the correct order.
Note: If you plan on re-importing this data back into RAM software, make certain to include the Account Number as one of your export fields.
8. Click the Save icon
to save your export template.
36
Running Export Procedure
Prior to running a data export, you must first have created an Export Template. If you have not already done so, refer to Creating an Export Template. After you have an export template, complete the following. Steps 1. Select Transfer from the Administration Functions folder in the Tree Pane. Choose Export from the Template Type panel. The Transfer Export screen displays. 2. From the Export Templates drop-down list, choose the appropriate export template.
3. Name the file that you want to export the data to in the Data Field.
37
4. Verify that the Delimiter field is set to the correct format. 5. Click the Run Export button .
The Data Transfer screen will display to indicate the status of the export process.
When the process has completed, the window will display Export Completed. Click the Close button . The exported file will reside in the directory you specified in the Default Export field on the Directory tab within Global Configuration (i.e. RAM5x\Export).
38
Appendix A
RAM Filter Examples
If you have any questions or need assistance, please contact your D&B RAM Consultant.
Rule Description
2000 Financial Statements A/R Refresh Date
Criteria
Financial Statement Date IS GREATER THAN '1999/12/31' Accounts Receivable Refresh Date IS GREATER THAN '2000/12/31' (Note: substitute the date prior to the current A/R refresh date) Bankruptcy Indicator IS EQUAL TO 'Y' Collector IS NOT null Collector IS EQUAL TO 'John Doe' (Note: substitute the name of the collector in single quotes instead of ‘John Doe’) Ultimate DUNS Number IS EQUAL TO '804735132' (Note: substitute the name of the Ultimate DUNS Number in single quotes instead of ‘804735132’) Account Number IS EQUAL TO '0000000001' (Note: substitute the account number for the customer under review instead of ‘0000000001’) ((Rating LIKE '%3') OR (Rating LIKE '%4') OR (Rating LIKE '_R4') OR (Rating LIKE '--%') OR (Rating LIKE 'DS%') OR (Rating LIKE 'NQ%')) AND Paydex Current IS LESS THAN 50 AND Credit Score IS LESS THAN 10 AND Financial Stress Score Percentile IS LESS THAN 10 AND RAM Score of Account IS LESS THAN 1 (Note: substitute any risk values you wish to make the filter more appropriate) Financial Statement Type IS NOT null
Bankruptcy is Present Collector Present Collector Specific
Corporate Exposure Report
DSO on One Account
Extremely High Risk Accounts
Financials are present
High Risk Accounts
RAM Score of Account IS LESS THAN 3 (Note: substitute any RAM score value you wish that represents your rejection threshold) Number of Judgments on Account >0 AND Number of Liens on Account >0 AND Number of Suits on Account IS GREATER THAN 0
Litigation Present
Low Credit & Financial Stress Scores Credit Score IS LESS THAN 10 AND Financial Stress Score Percentile IS LESS THAN 10 New Accounts Account Creation Date IS GREATER THAN OR EQUAL TO ‘2000/12/31’ (Note: substitute the date of your choice)
39
Opportunity Accounts
RAM Score of Account IS GREATER THAN OR EQUAL TO 7 (Note: substitute any RAM score value you wish that represents your acceptance threshold) Order Amount IS GREATER THAN Credit Limit of Account Out of Business Indicator IS EQUAL TO 'Y' Credit Limit of Account IS GREATER THAN Suggested Credit Limit + 10000 AND RAM Score of Account < 3 Past Due 30 Days IS GREATER THAN 0 AND Past Due 60 Days IS EQUAL TO 0 AND Past Due 90 Days IS EQUAL TO 0 AND Past Due 90 Plus Days IS EQUAL TO 0 AND RAM Score of Account IS LESS THAN 3 Past Due 30 Days IS EQUAL TO 0 AND Past Due 60 Days IS EQUAL TO 0 TO 0 AND Past Due 90 Plus Days IS GREATER THAN 0 AND RAM Score of Account IS GREATER THAN OR EQUAL TO 7 Past Due 30 Days IS GREATER THAN 0 AND Past Due 60 Days IS GREATER THAN 0 AND Past Due 90 Days IS GREATER THAN 0 AND Past Due 90 Plus Days IS GREATER THAN 0
Order Amt > Credit Limit Out of Business Indicator is Present Overextended High Risk Accounts
Past Due 30 & RAM < 3
Past Due 90+ & RAM >= 7
Past Due In All Aging Buckets
Paydex > 70 & Total Past Due > $1,000 Paydex - Current IS GREATER THAN 70 AND ((Total Amount Owed on Account - Current Amount Owed on Account) IS GREATER THAN 1000 ) (Note: substitute any risk or dollar values you wish that are appropriate)
RAM < 3 & Total Past Due > $1,000
RAM Score of Account IS LESS THAN 3 AND ((Total Amount Owed on Account - Current Amount Owed on Account) IS GREATER THAN 1000 ) (Note: substitute any risk or dollar values you wish that are appropriate) Sales Rep IS NOT null Sales Rep IS EQUAL TO 'John Doe' (Note: substitute the name of the sales rep in single quotes) Suggested Credit Limit IS GREATER THAN Credit Limit of Account + 10000 AND RAM Score of Account IS GREATER THAN OR EQUAL TO 7 AND ((Total Amount Owed on Account - Current Amount Owed on Account) IS EQUAL TO 0) (Note: substitute any RAM score or dollar values you wish that are appropriate) Paydex - Current IS LESS THAN ( Paydex - Previous 3 Month - 10 )
Sales Rep Present Sales Rep-Specific
Upsell Opps > 10 K & RAM Score >= 7
Paydex drop more than 10 points
40
Appendix B
Guidelines for Import Files
Import files consist of ASCII text characters whose fields match the fields in the RAM database. An asterisk or comma must separate each field. Following are some examples of what a customer data import file might look like.
Format Comma delimited Asterisk delimited
Example 1020304,TESTCO INC,100 CAMPUS ROAD,PARSIPPANY,NJ,07054,9735559842,500,100,400,0,0,100 0,0,MR. SMITH 020304*TESTCO, INC*100 CAMPUS ROAD*PARSIPPANY*NJ*07054*9735559842*500*100*400*0*0*1 000*0*MR SMITH
Note: In the actual import files, a record must have all its fields on one line.
The Account Number field is the primary key in the RAM database and, therefore, is required for every import file. It tells the Import engine exactly where to write the data. However, the account number is not required to be in the first position. In general, the order of the fields is not important, as long as you have the File Layout available when you create an import template. The file layout is used when mapping data to its location in RAM, and it is important that each field get mapped to the correct table and column location in the RAM database. Use the following guidelines when creating ASCII text files for importing data into RAM. 1. Avoid using a "header" record as the first record in the data file. Otherwise, it guarantees that the first record will generate an error message. Although this is not a serious matter, it might concern the user when they encounter an error on the first record during the Import Test stage. 2. Separate each field with a comma and quote or asterisk. Choose only one type of delimiter per file. 3. When using commas as your field delimiter, enclose text fields within single quote marks. This allows the Import tool to ignore commas that appear within the data itself. For example, ‘ABC, INC.’ is recognized as a single field because single quote marks shroud the comma and cause Import to treat it as text. Empty text fields do not require quotes.
41
4. When using asterisks as your delimiter, do not use quotes around character fields. Otherwise, your data will include the quotation marks as well. 5. Use the data field descriptions on the following pages to determine a field’s maximum length. The maximum field length refers to the largest length allowed by RAM. The actual field length should match your own data requirements, provided it does not exceed RAM’s maximum. Do not pad your text fields with leading or trailing spaces or zeroes (except for Account Number, where leading zeros are significant). [See Number 7 below.] 6. The account number (Acct_Num) must match the account-numbering scheme and actual account numbers used in your "Trade Tape" process or Accounts Receivable system. The account number links the D&B credit and demographic data with your accounts receivable data. If in doubt, contact your company’s Trade Tape coordinator and determine the accountnumbering scheme being used to send information to Dun & Bradstreet’s Trade Tape program (if your company participates).
Note Your Account Number (Acct_Num) field must be consistent in format with your accounts receivable system and, if applicable, with your D&B Trade Tape format if you intend to import your past due aging information.
7. In certain cases, the account number (Acct_Num) must contain leading zeros in order for account numbers to sort in sequence. For example, account number "20" will sort after account number "100". To avoid this, you can use a numbering scheme such as 00020 and 00100. 8. Numeric fields, such as past due, current owing, and so on, must be created on the import file as viewable ASCII characters. Packed, zoned, binary or other formats are not acceptable. The numbers should be readable to the eye as characters. 9. Numeric data can contain decimal points. Currency data cannot contain thousands separators (commas) or currency symbols. 10. The state field must be a two-character text field separate from the address line.
RAM will automatically format telephone numbers. The translation option allows RAM to ignore non-numeric characters.
42
Appendix C
__________________________________________________________________________________
RAM CBA Examples
These CBA examples illustrate outstanding ways to better understand and utilize the information within your RAM database. It is suggested the first element in the below Criteria section should be viewed in the ‘Row’ format and the second element in the ‘Column’ format when setting up a CBA in RAM. Total Amount Owing is used in most all of the below examples as the ‘Statistic to track’ element. This gives a quick understanding of the total potential risk of a specific segment of customers. The ‘Sum of’ the specified statistic to track’ button in the Tabulation section is simply checked to calculate these various totals within the white section of the matrix. If you have any questions or need assistance, please contact your D&B RAM Consultant.
Criteria
Ave. D&B High Crd. vs. Paydex
Rule Description & Benefit
This enables you to clearly understand if you might be under or over selling to your customers. For example, if their Paydex is excellent and their Ave D&B High Crd. is large, you maybe missing some selling opportunities. By tracking the Total Amount Owed category, it is easy to identify these opportunities or potential risk. This gives you the ability to clearly understand how a customer is paying you in relation to how they are making payments with other companies.
Payment Manner vs. Paydex
Credit Score vs. Financial Stress Score This analysis gives you a clear understanding of what D&B’s predictive scores suggest about your customers. You can gain a better understanding of which customers are considered a high risk, possibly may pay past 90 days due or a combination of these two variables.
Any Aging Bucket vs. Paydex (Ex: Past Due 30)
Note, Track the sum of the Specific Aging bucket which is being analyzed instead of Total Amount Owed in this last example.
This enables you to clearly identify a specific segment of your aging. Often this is utilized for collection activities.
43