How to Analyze Financial Models When Clocks are Ticking

Have you ever been stuck at a moment wherein you were to understand a financial model built by others? And your mind is inundated by thoughts about where you should start? Let us consider a situation.

You are about to call it a day and retire to sleep when your phone rings. It has been an awfully long time since you took your lady friend out for a meal. She is upset and has taken a stance texting you that it is either tomorrow’s lunch or never again in life. Feeling guilty, you decide to spend some quality time with her the next day.

You walk into your office the next morning hoping to confront your boss and ask him for a half day. On seeing your boss, you jump up from your cubicle, walk up to him and before you could utter a word, he asks you to check your mail and get on a task immediately. You return in dismay to your seat and find a financial model resting in your inbox demanding analysis.

So now, you have two things to take care of. Your lady friend has already reserved the second half of your day leaving you and the financial model just a few hours to bond. As a dedicated employee, you face the challenge head on.

The first and foremost thing that you need to realize is the aim of the model. For instance, a model maybe aiming at optimizing the net present value of some project or might be looking into a variation of return rates with respect to some input variables. Studying the logic flow diagrams is the key to understanding the financial model. It will give you a visual understanding of all the relationships.

But then, what if the logic diagram is missing? Well, then we have two approaches.

First would be to draw the logic diagram yourself. That’s one uphill task for sure but if one can sketch the logic diagram then there is no more fight when trying to reach the core of the model.

And now comes the scary part. Or I shall rather say the challenging part. What if a logic diagram seems an implausible task? What if making the logic flow diagram becomes your biggest nightmare and all you wish is to do is run away from the diagram at a speed that is a notch faster than your maximum?! And to complicate the scene for you, the fear of offending your lady’s command leaves you drenched in sweat and all panicky.

To sum up, the situation is one like this – you are standing on a razor blade with a steep valley on one side, which implies losing your lady, and a deep well on the other, implying disappointing your boss. Well, I’d tell you to calm down because the task at hand would not require you to take the fall on either side.

To begin with, I suggest taking the uphill approach. Identify the end result of the model and move back step by step. In the process, use basic formatting to highlight your findings. The cells having correct data can be given a certain shade color. Similarly, a different color maybe used for cells where you have certain doubts and contains illogical data. Allow me to state that Excel’s inexpensive add-in, RefTreeAnalyzer, will do your work much faster with no problems coming up if data is spread in multiple sheets. The add-in is available at the developer’s website. You can just Google it and have it installed for a few bucks. At the end of this auditing process, you would have the collection of data for which you can get hold of the modeler and set him up for a review session.

We have presented you with the screenshots of the RefTreeAnalyser here picked up from the creator’s website http://www.jkp-ads.com/RefTreeAnalyser02.asp. RefTreeAnalyzer, when installed adds a tab to your ribbon on MS-Excel appearing as below.

The image below shows RefTreeAnalyzer showing dependents and precedents of the cell.

The RefTreeAnalyzer visualization shows improved trace precedent arrows.

RefTreeAnalyzer helps in identifying and locating circular referencing as well.

The task of going back from the end result may seem tedious, in fact will actually be tedious, take a lot of time and a small error on your hand can make you repeat the process, but then what is free in present world market. A little caution, sharp attention to detail and high levels of concentration is all you need. And eventually, the sharp razor blade will be transformed into a stage to dance on!

EduPristine: Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.