Financial Modeller's 10 Worst Nightmares
What are your financial modeling nightmares?
1. Cleaning up someone else’s models.
I used to love Kinder Surprise as a kid, and now opening up someone else’s financial model gives me the same sensation. Unnecessarily complex models are like those gifts that require an engineering background to assemble; the overly simplified models are like the readily assembled figurine of dinosaurs that ends up in the trash right away, and good financial models are like those gifts that you still keep in your secret shoe box.
Solution: Do a preliminary review and decide as early as possible whether you want to work with the inherited model or build your own model instead.
2. Circular references appearing where you did not expect them.
I do not understand people who have no problem with having circular references flowing freely in their models! I have no patience to wait for the recalculation of all worksheets every time I change something in the model. Plus, you cannot use many of Excel's functionalities like data tables and Goal Seek.
Solution: The most inefficient way to solve for circularity is to use a copy and paste macro. In many instances, circularity can be resolved with algebra or by using a user-defined function. For more on that, check edbodmer.com.
3. The financial model crashes, and you don’t have a backup.
Hours of work were lost. Yes, it has happened to everyone, but it doesn’t make it less painful.
Solution: Hit your head against the wall and learn to press Ctrl + S frequently.
4. Worksheet compatibility issues.
After finally and proudly sending the model to your clients, you get a comment that some of your functions are not compatible with their version of Excel. This happened to me recently with MINIF, MAXIF, and AVERAGEIF functions. Later, I realized that these are only available to subscribers of Office 365 with the current version of Excel 2016. The standalone version of Excel 2016 does not have these new formulas.
Solution: There are so many alternative ways of doing the same thing in Excel. Use simple functions and do your calculations in blocks to avoid long formulas. Test your models on different versions of Excel.
5. A balance sheet that does not balance.
The other side of the story is the fantastic feeling you get when you get to balance your balance sheet.
Solution: The first trick I do is to check for the difference and see if I can catch the difference in the P&L and cash flow statement. I also do sensitivity. For example, I change different elements to 0, like debt, working capital, and shareholder loan, and I see if the balance sheet balances without any of these elements in action and then try to figure out where the problem is coming from.
6. Changes in accounting standards or applicable fiscal regimes which, in turn, make your life miserable.
Project development is like a roller coaster ride, and a financial model is a tool that should accompany the project throughout the journey. So, you should be prepared for the unexpected. However, there is a trade-off between flexibility and simplicity.
Solution: Try to build enough flexibility into the model to accommodate expected future changes.
7. The first time you are asked to build a cash-flow waterfall when the only waterfall you knew was Niagara Falls.
There are plenty of acronyms and weird expressions in finance. I read somewhere that Elon Musk has said that he doesn’t want people to have to memorize a glossary to function at Tesla Motors.
Solution: Always have your end user in mind and consider that not everyone knows what CFADS or LLCR are. Always add a list of abbreviations and definitions on the cover page or guide page of your model.
8. Clients who have Excel phobia.
Yes, there are people out there who are afraid of Excel. I had some colleagues and clients who were calling me back for every small change that they wanted to apply to the model! I didn’t know why they wouldn’t just use the beautiful switches that I had included in the dashboard to see different scenarios. Later, I realized that they were afraid to mess things up if they did it on their own. In one of the cases, the person with the Excel phobia told me that once upon a time, he had to work on a very complicated financial model and he had to call upon the modeler every time he wanted to make any changes. This might be a smart way of financial modeling, if the financial modeler is getting paid on an hourly basis and is selfish.
Solution: When building your models, have the final user in mind. The same standard applies when you are writing a report: Your financial model should be readable and follow specific standards.
9. When you are given a financial model in Portuguese and your level of Portuguese is limited to the lyrics of Amar Pelos Dois from Salvador Sobral.
I love that song, and when it won the Eurovision Song Contest, it was proof to me that quality is still more powerful than marketing. If the model is in a language that you don’t know, it's still okay. The problem is when the main documents and agreements are in a foreign language and image file format.
Solution: To translate my models, I do the following:
1. I insert a column next to the label column in the financial model.
2. I copy all the labels from the original financial model to Google Translate.
3. I paste the translation into the added column in the model.
4. I go over all the translated labels and edit where necessary.
In my model, I also link labels. For example, at the early stage, I use a placeholder for “Senior tranche 1” in my Inputs sheet and link to wherever I am doing the debt calculations (“Debt” sheet, “Financing” sheet, etc.). Later, when I have a term sheet with a specific bank, I replace “Senior tranche 1” in the Inputs sheet with, as an example, “African Development Bank,” and this will change the label throughout the model. So if you do that, you can easily translate the whole model from one language to another.
10. Being called a data cruncher.
It might be a compliment for some people to be called a data cruncher, but to me, a financial modeler deals with more than just data. In my experience, if you want to make a good deal, send your financial modeler for negotiating contracts. An excellent financial modeler should know all aspects of the projects, from the technical, the legal, the financial, the tax, etc., and should understand the consequences of changing different aspects of the current case of the project’s economics. So invest in financial modelers on your team, make them involved in all aspects of the projects, and treat them as your colleagues and not as machines.
Some of the nightmares fade away as you get more experienced, but they are soon replaced with new challenges. That’s part of the journey and a constant learning process.
What are your financial modeling nightmares?
Questions & Answers
© 2018 Hedieh Kianyfard