More than a decade ago, US energy giant Enron collapsed due to fabricated financial statements and inaccurate accounting practices. More recently, the Dutch insurance firm Aegon reached a $100m settlement with US regulator the SEC after providing misleading financial information to investors. And in the UK, the bidding competition for the West Coast rai contract reportedly cost taxpayers £50m because basic processes, including governance and risk weren’t followed.
What these examples have in common is a flawed financial modelling process. The Enron scandal led to bankruptcy, job loss, prison sentences and, for Enron executive John Clifford Baxter, suicide. Deliberate spreadsheet manipulation is rare. Unintentionally flawed financial models are more common, and often the result of inexperience. A Whitehall-commissioned independent inquiry into the West Coast bidding – the Laidlaw report – found that financial modelling processes within government needed significant improvements.
According to financial modelling firm F1F9, nearly 90% of spreadsheets contain errors, while half of spreadsheet models used by large businesses have significant material defects. The impact on the business can be catastrophic, not just in financial terms, but in terms of reputation and damaged careers.
Financial modelling, used mainly within the public finance, energy and transport sectors because of the prevalence of large-scale, long-term projects, is relied on to forecast future outcomes and often uses Excel spreadsheets. “People need financial modelling projections to be as accurate as possible, to include as many flexibilities as possible and to be complete,” says David Lyford-Smith, technical manager at ICAEW.
“It might be a budget forecast, a cash flow forecast or a project appraisal. A lot of money will be riding on it. It has to be reliable.” Yet, as Alistair Hynd, corporate finance partner and head of financial modelling at RSM UK points out, financial modelling is fraught with costly inaccuracies: “Spreadsheet risk and error continues to make the headlines,” he says. “There is still a long way to go to properly embed spreadsheet best practice in even the largest and most sophisticated businesses.”
It’s one of the reasons why ICAEW and the ICAEW Excel Community Advisory Committee have produced the Financial Modelling Code, a non-commercial, high-level set of agreed standards for good financial modelling. Led by Lyford-Smith, the code is based on a review of seven existing methodologies and has input from over a dozen financial organisations. According to Lyford-Smith, many businesses are still “figuring things out as they go”, with junior staff carry - ing out financial forecasts who may not have adequate training. It could potentially cost organisations millions in settlements and translates into a wider problem: businesses don’t appear to be taking Excel seriously enough, despite it being a universal business tool. “You tend to get people picking up Excel skills on the job in quite an informal way. But it’s a serious bit of kit and it’s a big responsibility.”
The Financial Modelling Code identifies best practice in an otherwise complex and lengthy process. It encourages high-level discussion around key decisions and approaches. Lyford-Smith acknowledges that there are already financial modelling standards and codes in practice, but these tend to form part of a company’s own methodology standards, which can be very specific.
“That’s completely reasonable if an organisation has specific business standards,” he says. “They’ll tell you how you should lay out the report, what you should call the input, what colour you should make it. But that’s not what we’re trying to do; we’re not trying to set down one version of the truth. We’re asking, at the highest level, what is good financial modelling? What does a risk averse and efficient financial model look like?”
In essence, the code is for anyone who comes into contact with financial modelling, whether a professional modeller, a procurer or a client. A client may refer to it to ascertain best practice, and to check if the professional modeller they have hired is doing a good enough job. A procurer may want to use it to help vocalise what they want, whereas an expert financial modeller may use the guide to inform a conversation with clients and colleagues about what decisions they’ve made and why.
The code then, is as much about driving the conversation around good modelling in terms of quality, consistency and reliability as it is about encouraging thought and reflection around a variety of approaches. What it absolutely isn’t, Lyford-Smith insists, is a guide that “lays down the law”. It’s not about saying some formulas are good while others are bad.
It’s a “higher-level discussion” around whether a particular approach is “safe, reliable and consistent”. Rob Bayliss, head of financial modelling at Grant Thornton and chair of the Excel Community Advisory Committee, agrees. “Modelling experts with the right skills and experience should be trusted to make their own design decisions depending on need,” he says.
According to Bayliss, who worked alongside Lyford-Smith on creating the code, a good model portrays a picture of a client’s business in ways that key assumptions can be changed and key risks understood. Good models, he says, bring structure and rigour to the uncertainty of making investments and business decisions.
But Lyford-Smith insists that good models shouldn’t necessarily produce complicated outcomes. In his view, there’s an unhelpful assumption that a complex problem inevitably creates complex end results. “This should be challenged,” he says. “Yes, financial modelling is difficult and complex but it can be made simpler, if not simple. It’s about consistency and reliability as much as anything else.”
Not a how-to guide
RSM UK’s Hynd, a contributor to the code, is also the author of ICAEW’s original Corporate Finance Faculty full financial modelling best practice guidelines, which conforms to the code and focuses on techniques for reducing risk, increasing speed, improving audit trails, managing complexities and controlling the modelling process in a primarily trans - action-related environment. It targets corporate financial practitioners and offers specific applied guidance, while ICAEW’s Financial Modelling code is targeted towards a larger cross-section of industry specialists and promotes in-depth philosophical discussion around decisions and approaches.
As Lyford-Smith says, the latest code is a “why to” not a “how to”. “The Financial Modelling Code provides a broader and arguably more accessible framework for people to start from, in understanding what to look for from a model or modelling requirement,” says Hynd.
The code builds on ICAEW’s 2013 Twenty Principles for Good Spreadsheet Practice. Divided into sections, it addresses every element of a model’s construction, such as layout and structure, user interface and transpar - ency, consistency, error reduction and calculation techniques. Approaches are either advocated or discouraged with clear explanations, allowing the user to make informed decisions. Condensing the key elements of the Code from a practical modelling level, Hynd says: “Keep it short and simple, keep it transparent, keep it structur - ally and visually consistent.” He adds: “There is a wide agreement on what good looks like in modelling terms, so if you are going to deviate from or ignore the guidance in the code, be sure you have a good reason.”
The code guidance in summary
• Determine the scope and goals of your financial model
• Use consistent approaches, structures and formulas.
• Create clear and logical sections.
• Use clear and meaningful labels. Make navigation simple with easyto-find inputs.
• Minimise calculation complexity.
• Include user guidance.
• Keep every row visible.
• Clearly identify forecasts or dummy data.
• Include a master check.
• Build traceable references.
• Peer review and test model.