Credit Controller User Manual

Please take a few minutes to read this guide before you start using Credit Controller. It contains important information that you will need to get the most out of the product. We hope Credit Controller proves a useful tool in your company’s credit policy. We welcome any feedback you have regarding this product and will endeavour to incorporate your suggestions into future releases.

1. Introduction

Every company has its own credit policy. Terms and conditions of sale vary between countries, industries and companies. Credit Controller is designed to emulate your credit policy and the way the person responsible for credit decisions within your company (probably you as you’re reading this) would allocate credit limits and conditions to your customers.

Credit Controller automates the credit decision process so that if the limit requested by the customer or salesman is less than that recommended by Credit Controller, a junior member of staff can authorise the process without referring it up the management hierarchy. By only referring requests rejected by Credit Controller, management time can be used more efficiently.

To do this Credit Controller needs to be tuned or parameterised to think like your credit controller so it makes systematic and consistent decisions time and time again just like he or she does.

2. Work from a Copy

We strongly recommend you work from a copy of Credit Controller. In the unlikely event of the file becoming corrupt or damaged you will always have a backup available. Simply save Credit Controller with another file name, for example My Credit Controller or Credit Controller back-up. Use the Save As option from the Excel File menu. There is no restriction on the number of copies you make of Credit Controller as it is licensed by the number of users not the number of copies.

3. Starting Credit Controller

Before starting Credit Controller Excel needs to be configured to run macro. Please follow the installation instructions for your version of Excel.

To start Credit Controller click the Credit Controller Excel file. You may be warned that the file contains macros and macros can contain viruses. Select Enable Macros to continue.

4. Design Conventions

Generally white cells indicate data entry areas, while all other cells are protected and cannot be changed. An exception to this is in the Customer Information worksheet where some data may be flagged grey. This may indicate a data entry error as Credit Controller has detected a significant difference between the values entered. The flag is for information purposes only and can be ignored. This function can be turned off or adjusted in the Tuning Mode (see below).

Help text has been added as cell comments, indicated by a red triangle in the top right hand corner of a field. If you hover over the triangle with your pointer, a pop-up box will appear. Alternatively you can toggle help on and off by selecting Comments from the Excel View/Comments option on the Excel menu. In comments containing formulae, the symbol ^ means "raise to the power of".

5. How It Works

Back in the 1960’s Edward I. Altman, a financial economist at New York University’s Graduate School of Business, set out to find a method of predicting the likelihood of business failure and impending bankruptcy. He came up with the Z Score Model which is still used today for forecasting financial distress in companies.

Altman's Z Score ModelAltman combined six financial elements into four ratios which were weighted in importance. Credit Controller takes 27 financial elements, both objective and subjective, from the latest three years of information available, and scores them based on the current value, the average over the three years and the trend. Furthermore, it allows you to weight them in accordance with your own credit policy and experience.

The information comes from your current sources: credit agencies, banks, suppliers and your own account history in the case of existing customers. In the current financial crisis it is more evident then ever that not all sources of information are equally reliable. If it didn’t before, your due diligence should now include information cross checked from several sources. Credit Controller brings it all together, scores it, presents it in an easily digestible format and highlights potential problems.

6. Tuning Mode

Credit ControllerWhen you first enter Credit Controller you will be presented with the Menu. By default it will be in Tuning Mode. Along the bottom you will find the traditional Excel spreadsheet tabs with all the worksheets that make up Credit Controller.

There are two steps to parameterise Credit Controller: global tuning in the Tuning worksheet and fine tuning in the various worksheets that make up the rating elements.

6.1 Tuning

Click on the Tuning sheet and you will be presented with a list of all the tuneable elements. This is where you can do your main adjustments at a global level.

  • Value
  • In the Criteria column, Units and Value are the equivalent weighted value for each rating element. It is the value the score is based on after taking into consideration the values for the latest, average and trend data, calculated from information entered in the Customer Information worksheet.

  • Tuning Credit ControllerWeight
  • Use this to increase or decrease the score of each of the rating elements so they become more or less important in the overall credit score.

  • Flags
  • They have no impact on the score; they simply highlight values when they reach the established limits. The Check Flag highlights possible data entry errors in the Customer Information worksheet. The lower the tolerance the more sensitive the flag. To switch this function off, enter a very high value, i.e. 9999%.

  • Trend Tolerances
  • These are similar to Flags in that they have no effect on the total score. They are simply used to determine when to highlight small increase and decrease in a trend as being a positive or negative development. If this value is set to zero, there would be no “insignificant change” flag (<->), only increasing or decreasing trends.

The remainder of the Tuning section provides information about the current settings in other worksheets. The Actual Score is based on Credit Controllers current parameters. The Maximum and Minimum Score can be adjusted in the worksheets for each individual rating element (see Fine Tuning below).

To get an overview of your current settings go to the Mini Graphs worksheet and Page Down to the last graph on the sheet. The graph list all the rating elements, their scores and flags settings.

Credit Controller tuning summary

6.2 Fine Tuning

Each rating element has its own worksheet for fine tuning. The principle is the same for each element. In the example below, Net Profit has been used.

  1. Fine tune Credit ControllerWeight
  2. This moves the emphasis between actual data, the trend and the average over the three years. By default we have given more importance to the trend but this may not suite your industry.

  3. Score
  4. Enter the range of Profit values you want to consider relevant in terms of a maximum and minimum value.
    Then add the maximum and minimum scores for those values. The greater the range, the more sensitive Credit Controller will be to this rating element.

  5. Score Distribution
  6. The next step is to choose a method of distributing the score between the maximum and minimum Profit values enter in step 2 above.

    There are six to choose from. The graph Score Distribution illustrates the distribution curves of each. The shape of each curve can also be changed by altering the various growth and constant functions for each curve. The curve currently selected is indicated by a dotted line on the graph.

    Getting the shape of the distribution curves right is a matter of trial and error. By default we have entered what we consider appropriate for most industries, but feel free to play around with other curves. You may well decide to keep it simple and go for a straight line distribution for all rating elements or create your own distribution in the Manual Distribution option.

    The greater the score range for a rating element the more impact it will have on the credit limit Credit Controller recommends. The distribution of the score will be more relevant in the rating elements you consider important and have a marginal effect on less important elements. We recommend you concentrate on those rating element you consider important, as it can be a time consuming and finicky task playing around with the distribution curves.

  7. Score Converter
  8. The box under the Score Distribution Graph is a simple score conversion calculator. It has no effect on the score. To see what an equivalent Net Profit would be for a particular score just enter a score value in one of the fields and its corresponding value is given in the default currency and a second currency if desired (see Language Dictionary for setting up a second currency). Five scores have been entered by default to give an idea of how the score distribution relates to Net Profit.

    The Result fields at the end of the converter are the actual score for this rating element and its equivalent monetary value.

The other rating elements are tuned in a similar way to the above Net Profit example with the exception of Credit Agencies, Banks and Supplies:

  1. Credit Agencies
  2. You can enter information from up to three agencies. Enter the names of the agencies you use in the Score header row. Then enter their ratings. Credit Controller standardises the ratings into five categories. Match the agencies as closely as possible to these standards. Enter the score you want Credit Controller to attribute to each of these ratings in the Score column. If you feel an agency is more or less reliable than the others you can weight it to compensate for this in the Weight column at the top of the screen.

  3. Banks
  4. This rating element works in a similar way to the Credit Agencies element above. However, information obtained from banks is usually more subjective, obtained, perhaps, from a phone call to ask their opinion about the customer. The ratings are, therefore, standardised. The person who fills in the Customer Information worksheet must decide which rating is the most appropriate.

    The bank being used for each particular credit request is also entered in the Customer Information worksheet and will probably vary from customer to customer. For this reason we recommend that you don’t use the Weight field unless you can standardise the banks you use for credit information.

  5. Suppliers
  6. It is a good idea to get the opinion of other companies who sell to your customer regarding their collection experiences and the terms of trade they apply.

    Banks and Suppliers follow the same principles and have similar standardisation problems. In fact, your customers’ suppliers are probably more variable than your customers’ bankers.

6.3 Altering the Credit Conditions

Changing the credit conditionsBefore you can start using Credit Controller you need to change the credit limits and conditions of sale to match your own company’s credit policy. This is done in the Credit Limits worksheet.

  1. Conditions Table
  2. You can have up to five credit terms in days and five condition types which you combine to form your credit policy in the Look-up Table (see below). They should be listed in order of the risk involved for your company. For example, start with cash (very safe) and end with cheques (high risk). Equally, the longer the terms of credit the higher the risk of a delinquency.

  3. The Look-up Table
  4. The conditions are linked together in a table that should form the basis of your credit policy.

    The first row is to catch scores lower than you first entry. In such cases, Credit Controller will automatically suggest the sale is rejected.

    Your first entry should be your worst case scenario. It’s doubtful you would accept anything more than a cash sale or perhaps a bank guarantee. As both these conditions are “safe” you may decide that the value can be high. There is nothing wrong with receiving € 1,000,000 cash, even if your customer appears to be on the verge of bankruptcy; unless of course, you suspect it’s counterfeit or that there are possible legal complications.

    Continue down the table entering all the permutations and combinations for increasing scores and decreasing risk. Decreasing risk should also mean increasingly favourable terms and conditions of sales.

    You can change the range of the score to one more suitable to your credit policy, but remember there should be a logical connection between the score range in the Look-up Table and the rating elements. If your score starts at – 1,000 and goes through to + 1,000 in the Lookup Table, but the sum of all the rating elements scores gives a minimum possible value of -10 and a maximum of 100, the results probably won’t match your current credit policy.

    You can use the Tuning worksheet or Mini Graphs (see below) to determine the maximum and minimum possible scores.

Total Credit Score

The fact that the total minimum score could be -600 doesn’t mean you have to start your scores in that range. It’s unlikely that you would consider anything but cash form zero and below, and it’s equally unlikely that a customer would score the minimum for every single rating element. By default we have entered 9 as the lowest score but you may be less risk averse than we are at Markitsoft and start at zero or -10, for example. Equally you may be more prudent then we are and only start at 20 or higher.

6.4 Internal Credit Rating

The Internal Rating is designed to give quick overall risk assessment of you customers. We recommend you use it to classify customers in you accounts receivable database so you can produce risk assessment reports giving overdue and delinquent accounts by types of risk and the importance of the customer.

Internal credit ratingsThe rating is made up of three codes:

  1. Sales History
  2. This indicates the importance of the customer in terms of past sales. The codes are based on the “good” and “poor” flag values in the Tuning worksheet. By changing these values you can change how Credit Controller assigns the A, B, C codes.

  3. Risk
  4. These codes are based on the Total Credit Score and can be changed in the Tuning worksheet in a similar way to the Sales History codes.

  5. Collection Experience
  6. These codes are based on the equivalent score in the Collection worksheet for the “good” and “poor” flags. By changing the Collection Experience flags in the Tuning worksheet, you can change how Credit Controller assigns the rating codes 1, 2 and 3.

7. The Language Dictionary

Credit Controller is designed as a multilingual application. At present it is available in English and Portuguese, other language version are due to be released in the near future. The Language Dictionary simplifies the translation process and allows users to translate the majority of the application themselves.

In the Dictionary worksheet, simply use the second column to translate the field labels and headings (help text cannot be translated in this way) and select your new language from the Menu.

You can use Excel's drop down AutoFilter function to locate words more quickly.

You can also change the second currency used by the Score Converter in the rating element worksheets. By default the second currency is in thousands of US dollars. To change the currency, select USD (thousands) from the drop down list, add the new currency in the second language column and enter a conversion factor in the Conversion column.

Credit Controller Language Dictionary

8. Security

Before putting Credit Controller into operational use you should save a tuned version and file it in a safe place. It shouldn’t be password protected as a precaution against forgetting the password, and it shouldn’t be available for distribution to unauthorised personnel.

To place Credit Controller in Operational Mode do the following:

  1. From the Menu select Operational Mode. This will hide all the tuning worksheets and present users with only the worksheets they need.
  2. Password protect the application. From the Excel menu select Tools/Protect/Protect Workbook. The Protect Workbook window will open.
  3. Make sure the Structure field is checked and enter your password. Re-enter your password in the Confirm Password window.

  4. Use the Save As option from the Excel menu option File to save the file for distribution.

To unprotect Credit Controller for further tuning, select Tools/Protect/Unprotect Workbook from the Excel menu. You will then be prompted to enter your password. Then select Tuning Mode from the Menu.

9. Operational Mode

It is now simply a question of collecting the data from your normal sources and filling in the Customer Information worksheet. The Account History, Ratings and Credit Limit sections have drop down lists in some fields. When you move your cursor to the field an arrow appears to the right of the cell. Click the arrow to see the list, and then click your choice.

New customers won't have a history. In this case simply enter zero for Sales, and 4) 90 - 120 overdue in the Collection Experience cells for each year. This will give a zero score for the Account History element.

Credit Report and Credit ScoreYou can see how the credit scores change as you progress. The Internal Rating and Total Credit Score and Credit Controller’s suggested conditions of sale are presented at the bottom of the form.

We recommend you run Credit Controller in parallel with your current system until you are sure it is correctly tuned and the suggested limits are in line with your own credit policy. You may decide to tune it so you have a safety margin between what it suggests and what you would accept so that boarder line credit request are still sent to management for approval.

If you want to keep a record of your customers credit requests, simply save each request with a different file name, for example: customer code and date of request. Create a new directory on your computer for credit requests to store the history for future reference.

10. Copy/Paste Functions

You can use Excel's Copy/Paste functions to enter data more quickly.

Caution! When pasting data, always use Paste Special/Values or Formulas to avoid copying cell boarders and formats to other cells. If you forget, use the Excel Undo icon. We recommend you add the Past Values icon to your toolbar. To add the icon, do the following:

  1. Select View/Toolbars/ ....Customize from the Excel menus
  2. Select Edit from the list of Categories in the Customize window.
  3. Scroll down the list of icons to Paste Values
  4. With the mouse drag and drop the icon onto your toolbar next to the Copy icon.

To remove the icon, drag it back to the Customize window.

Note: The Enter key will paste formats so please take care not to use it inadvertently.

11. Deleting Data

Select the data to delete and use the Del key to remove it from your worksheet.

Caution! Don't use the Edit/Clear option on Excel's menu as this can delete the conditional formatting used to flag errors.

12. Final Note

Remember, Credit Controller suggests the conditions of sale. If a customer is asking for a higher limit than that suggested, management still has the difficult decision of whether or not to lose a potential sale or risk gaining a possible bad debt. Our objective in developing Credit Controller has been to make that process more transparent and systematic, allowing managers to concentrate on the more complicated credit requests by filtering them out from the majority of requests that can be processed automatically.