How to create Financial models flawlessly?

Recently, I met one of my friend working in strategy team of a bank over lunch. I felt bad for something which he mentioned casually. Here is what he said:

Every time there is a Financial model required by our leadership, we end up doing multiple iterations and thus creating numerous versions of the model. While this not only ends up taking more resources than required, it also delays the important business decisions.

This situation is not unique to his company, It happens in most of the Organizations across the globe. In the remaining article, I’ll explain what is a financial model, the process involved in creating one and some of the best practices to make them flawlessly.

[stextbox id=”section”]What is Financial modeling and how is it used?[/stextbox]

Financial modeling is a process by which we calculate or estimate financial numbers in various situations or scenarios. Financial models can vary from simple calculations to complex simulations which can take hours to run. A simple excel file in which you project your monthly income and expenses is a basic financial model.

Following is a sample excel model to project yearly savings with annual interest rate of 12%:

These models are used across the globe for business planning and taking important decisions for the business. Following are some questions which business owners typically answer with help of financial modeling:

• Will it be profitable to offer new Credit cards to existing customers?
• At what price point can a start up break even in 3 years? 5 years?
• How many tele-callers do we need to call all the customers once every day? How much will be the cost?
• How would the business model look when we acquire a competitor?

While a lot of organizations might be using advanced tools for a lot of granular analysis (e.g. Customer level predictions and recommendations), most of the macro / strategic analysis continues in spreadsheets.

[stextbox id=”section”]Challenges in Financial modeling[/stextbox]

While building financial models is inherently not difficult, there are some common mistakes which people make and then think that financial modeling is difficult. Some of the common one are mentioned below:

• A single Financial model is looked as panacea to all business problems. While this might be good intent, it becomes difficult to implement practically. Financial models are used best to answer specific questions. If you want to check whether a particular tranche of customer is profitable or not, don’t try and evolve the model to answer whether the business overall is profitable or not.
• Inputs and assumptions are not called out explicitly: Any financial model is built basis some inputs and assumptions. Typically, people use hard coded values for assumptions and collect inputs at various places. This leads to confusion. Also, their is a risk that you will end up making wrong conclusion, if you missed changing one of the inputs. As a best practice, call out all the inputs and assumptions in single place and link the code / file accordingly.
• Inconsistency in outcomes: Another common reason for errors is that the analyst looks at the financial model as just a mathematical exercise. He doesn’t tie up numbers or models impact of changing numbers in one department over another. If you want to buy more raw material or keep more inventory, not only you would need more space, you would also need more human resources and maintenance.

[stextbox id=”section”]Process of building a financial model[/stextbox]

Below is a structured approach to financial modeling. Following these in disciplined manner would ensure that the common errors mentioned above are avoided and you achieve the desired result in a single attempt. At the start, these steps might seem time consuming and extra effort, but, if followed diligently they would end up saving multiple days and iterations.

We will cover best practices involved in each step below

[stextbox id=”section”]Step 1: Understand business requirements[/stextbox]

It is very important that you understand all the business requirement at the outset. If you don’t, your financial model is doomed for failure. Some important questions to ask business users are:

• What are the answers business is looking from the model?
• What are the levers in control of business owners? What is out of their control?
• What is the period for which they want to take the decisions?
• What are the constraints (resources / budgets / capacity)?
• What are the dependencies across various departments?

Once you have a fair understanding of what business wants, agree on set of inputs, output and assumptions. It is a good practice to keep all your inputs, output and assumptions together. If you are using excel, keep them in a sheet each. If you are using a coding environment, define inputs and assumptions at the outset. By doing so, you make sure that creating scenarios later on is easy and can be done without creating any confusion.

[stextbox id=”section”]Step 2: Finalize dimensions and granularity[/stextbox]

This should come directly from business need. Granularity of a financial model is usually determined by level at which business wants to take decision. Is it at country level? Product line level? Product level? If time permits, I recommend creating model at one level more granular than the customer needs initially. So if customer is looking at country level financial model, try creating something at Regional level.

This ensures that not only the decision can be made at the required level, but you also get a texture of how to implement that decision. It answers the next level of questions for the business owners.

[stextbox id=”section”]Step 3: Apply business logic and formulas[/stextbox]

Once all the requirements are clear and granularity is decided, just apply business logic to build the model. By business logics, I mean the the mathematical translation of business understanding. What parameters and values will be impacted by various inputs? What is the profit margin? Are there any over head expenses which you have not included?

One of the good practices here is to keep the entire worksheet / code logically linked with out any hard coded values. If there is constant which is being used, it should go in either inputs or assumptions. In order to simulate any scenario, you should not be required to change inputs in multiple sheets or places.

Another benefit of keeping all calculations formula driven is that you need to only make sure that your inputs are consistent and the business logic are correct. If you ensure these two things are done diligently, your model will always remain consistent.

[stextbox id=”section”]Step 4: Test scenarios[/stextbox]

Once the framework of the model and base scenario is ready, you need to test whether the model holds under various scenarios. What are the boundary conditions when business logic would no longer hold true? You need to define the minimum and maximum values for various inputs and set of inputs.

Once this is ready, you should train your business users on the limitations of the model.

[stextbox id=”section”]Step 5: Document[/stextbox]

Finally, document all the needs, granularity, calculation and limitations in a document. Again the recommendation is to do need based documentation. The purpose of this documentation is to make sure that any one can pick this document and understand the model. A typical document has following sections:

• Background
• Business need
• Problem statement
• Scope of the model
• Areas out of scope of modeling
• Business logic and calculations
• Limitations
• References (if any)

Once this documentation is ready, your model is ready to roll out.

As mentioned before, if you follow these steps diligently, it will avoid a lot of re-work. Further, the more you practice them, the better you will become at financial modeling. Keep practicing these and become flawless financial modeler!

In case you are aware of any other good practices, please feel free to reach out to me or write them in comments below.

If you like what you just read & want to continue your analytics learning, you can subscribe to our emails or like our facebook page

Kunal Jain

Kunal Jain is the Founder and CEO of Analytics Vidhya, one of the world's leading communities of Al professionals. With over 17 years of experience in the field, Kunal has been instrumental in shaping the global Al landscape. His expertise spans diverse markets, from developed economies like the UK to emerging ones like India, where he has successfully led and delivered complex data-driven solutions. As a recognized thought leader, Kunal has empowered countless individuals to realize their Al ambitions through his visionary approach to Al education and community building. Before founding Analytics Vidhya, Kunal earned both his undergraduate and postgraduate degrees from IIT Bombay and held key roles at Capital One and Aviva Life Insurance across multiple geographies. His passion lies at the intersection of analytics, Al, and fostering a thriving community of data science professionals.