Excel is one of the main tools used in financial modelling and valuation. People input historical data and financial assumptions to model scenarios like future profits and losses, revenue or income forecasts.

“How many of you have ever opened up an Excel file, started looking around, and you thought, ‘My God, I’ll never understand what is going on in this crazy beast of file?’” asked Ian Schnoor, executive director at the Financial Modelling Institute, during his keynote presentation at a valuation conference held at PWC Luxembourg on 10 February 2025. Based in Ontario, the FMI offers accreditation programmes for financial modelling. “This happens to all of us. That’s not your fault. It was not built optimally; it was not well-designed; it was not well-structured. The modeller did not incorporate optimal discipline, and what that’s called is risk. Tremendous risk [is] inherent in any file when you can’t follow and understand it.”

Schnoor highlighted three major failures linked to valuation in recent years. The first was JP Morgan’s $6bn trading loss in 2012 due to an error in its value-at-risk (VAR) model. “The model was poorly designed and poorly built. There was a lot of manual requirements in this value-at-risk model. It came out after the fact that the person who built the model was not qualified to build the model; they had never built a VAR model before. There was no checking, there was no process around the validation of the process. And all of this led to a collapse.”

The second example was a distribution error in Fidelity Investment’s Magellan Fund in 1994. “After the fact, they discovered that a capital gain--a billion-dollar capital gain--was really a capital loss,” said Schnoor. “So they had booked something and recorded something in their model as a capital gain, when--in fact--it was a capital loss.” That isn’t just a $1bn problem, he pointed out. That’s actually a $2bn problem.

The third case took place in Canada, where Schnoor is based. Reported in 2024, this was “a billion-dollar M&A deal [between Aspen Investments and TC Energy Corp that] collapsed because they couldn’t get the bond deal done. And the bond deal failure was a result of a financial model error,” he explained.

A valuation model is made up of two things: the valuation and the model. It’s not a single entity. “I’ll tell you a dirty little secret,” said Schnoor. “The secret is this: when a valuation model goes wrong, it’s rarely in the valuation. It’s in the model.”

Swiss army knife for error detection

Since errors are more likely to be found in models than in the valuation itself, Schnoor presented six tips to help “minimise and mitigate errors” and reduce risk.

“Spreadsheet models now support the entire global financial system,” argued Schnoor. They “are the cornerstone of everything that happens in capital markets, and as a result, they need to be checked.” By way of a quick show of hands, the majority of the audience had been tasked with checking, validating and confirming an Excel file that they had not built.

Model checking is a key skill but “can be very, very stressful,” he continued. “There’s all sorts of problems in the way people build and design them.” But finding errors during a check is actually a good thing, said Schnoor, and it’s important to keep a clean, itemised list of issues. “If someone discovers something later, then of course, it’s your problem.” A saying he likes to use is: “If you bless it, you own it.”

“The six Swiss army knife tools for error detection are what I call: drone view, camouflage, land mines, formation, navigation and intelligence,” said Schnoor.

“There are two reasons I wanted you to see all of these techniques today,” Schnoor concluded. “First of all, I was hoping that you would all leave with something that you could take back and help yourself discover errors and make sure that the files that you are looking at are clean and working well.”

But second, he said, “I wanted you to leave with an appreciation for risk mitigation and risk management. It doesn’t take much to mess up a spreadsheet model. These models are so important, and there is a whole discipline around it.”

The conference was organised by the Luxembourg chapter of the Caia, together with PWC Luxembourg, the CFA Society, Financial Modelling Institute and Chartered Institute for Securities & Investment (Cisi). It also featured a  Alain Hoscheid and the PWC Luxembourg director and Caia Luxembourg chapter executive Rafaël Le Saux.