450 million shares were undervalued.
Error Type: Data formatting
Reported on: November 2022
Iceland bank Islandsbanki sold off 22.5% (450 million) of its shares in March 2022. The National Audit Office discovered that the sales price of 117 kronur per share undervalued the shares based on the actual demand prices recorded.
Islandsbanki used an Excel based financial model to collate and analyse the demand price data. The National Audit Office found issues with the formatting of some of the demand price data used in the analysis.
They specifically noted that some entries had “foreign commas or amounts defined as text”. These entries were excluded from the calculations by Excel as they would have been interpreted as text rather than numbers.
First up I'd like to be clear - this is not an "Excel error".
The error was caused by data quality issues that were not properly identified and managed by the team performing the analysis.
The good management of the quality of data feeding into any financial model is paramount. As the old adage goes "rubbish in, rubbish out".
Excel's handling of numbers that have been formatted as text is an issue that almost every Excel modeller will have experienced. Excel has a built-in check for "numbers formatted as text or preceded by an apostrophe". Excel flags any such number with a green mark in the top left of the cell.
Using PowerQuery to process the data could have helped. As part of the ETL (Extract , Transform & Load) process through PowerQuery users are able to specify the format of each data field. The fields loaded into Excel will then be correctly formatted.
Ultimately though you have to ask the question if an Excel-based financial model is the right tool to use for such a business-critical exercise. Indeed the National Audit Office found that:
“A detailed analysis of the data, e.g. with the use of specially designed information systems for managing offers, could have provided a better overview of real demand and laid the foundation for a more accurate assessment of price formation,”
Read the article published by the Bloomberg here.
See our complete financial modelling error series here.