Dr. Franz Fasching designs, develops, and manages IT and telecommunications solutions. more ...
Complex Excel spreadsheets may impose a significant risk to completeness and accuracy of the data processed with them. Complex formulas, pivot tables, and lots of external references increase the possibility of human errors, as already a small error such as a wrong VLOOKUP offset or even just a misplaced SUM cell range are both hard to catch and may cause significant errors. Additionally, the fraud potential increases as well, as fraudulent manipulations can be hidden more easily in complex spreadsheets than in simple ones.
There are whole organizations dealing with spreadsheet risks, as e.g. the EuSpRIG (European Spreadsheet Risks Interest Group), where significant effort is put into spreadsheet complexity and risk evaluation.
XLscore is an Excel risk scoring tool developed by order of Graser Consulting GmbH with simplicity of use in mind. It can be used to evaluate the complexity of an Excel spreadsheet and thus its susceptibility to human errors or fraud. It does this by assigning risk scores to formula complexity, external references, or pivot tables, and calculates a total risk score of spreadsheets. Additionally, errors contained in the spreadsheet are counted and categorized, and the absolute sum of all numbers is calculated and displayed. The results are displayed in a textual format, but may be saved as a CSV or XLSX file as well.
The weighted risk scoring is based on an article by Bing Chien Quek, Spreadsheet Complexity, published in the CompAct newsletters by the Society of Actuaries (SOA), Technology Section. Basically, an indivdual weight is assigned to each formula operator or function, and all weights over all formulas in all worksheets are summed up. Besides that numerous other characteristics of the spreadsheet are analysed, like number of pivot tables or external references, as well as unique formula counts and statistics. Thus a number of quantitative measures is calculated that can be used to quantitatively judge the risk a specific Excel spreadsheet poses to the control target.
In order to start, first select one or more *.xlsx files by clicking on “Select”.
Then click “Analyse” to start the risk analysis. The result of the analysis run will appear in this window.
Later on, you may click “Save” if you’d like to save the result as a text file (*.txt), comma-separated value (CSV) file (*.csv), or as an Excel file (*.xlsx).
Click “Exit” if you want to quit this tool.
You may save XLscore results in either text (*.txt), CSV (comma-separated value), or XLSX (Excel OpenDocument *.xlsx files).
Text output files are just a copy of the anaylsis results displayed on screen in the XLscore result window, saved in a text (*.txt) file.
CSV and XLSX files save the results in a table format, where each analysed Excel file (workbook) occupies one row of the result file. The columns (fields) of the result file have the following meaning:
A final row named TOTAL placed at the end of the result table contains total sums of all of the above fields over all workbooks analysed in the current XLscore run.
If you are an XLscore licensee and have questions regarding its usage, you may write to support.nosp@m.@email@example.com@firstname.lastname@example.org.
If you are interested in acquiring an XLscore license, please contact sales.nosp@m.@email@example.com@firstname.lastname@example.org.