The purpose of a financial model is to represent reality in the simplest way possible while still being a realistic reflection of the company’s situation and thus allow projecting the company’s financial statements based on current and historical data.

In this article, we discuss these aspects of financial modeling

  1. How is a financial model made?
  2. What elements does a financial model contain?
  3. For whom is a financial model important?
  4. What advantages does the financial model bring?
  5. Who designs the financial model?
  6. Who needs to master financial modeling?
  7. What risks does modeling entail?
  8. What is essential to know to make good models?
  9. Where do you learn to model?

1.When are financial models used?

Financial modeling is the ability to capture the reality of the company in an abstract model that provides a transparent image of the global situation of a company. A financial model to open a street kiosk is undoubtedly more straightforward than a financial model for a multinational with different sectors and divisions.

The objective of a financial model is to be able to project the impact of any change in the operations or financial structure of a company to help us make a decision.

The key to build a solid financial model is to create a fully flexible set of integrated financial statements to ensure that when we change any input, the model flows correctly to estimate the approximate financial impact of our decisions.

2.How do you build a financial model?

The key to building a sound financial model is to structure it logically and clearly.

A good model should have the right amount of detail, not too much, and not too little. It is easier said than done, but you should always aim for simplicity if possible.

Typically, the structure follows the order of the financial statements, operating assumptions related to the business model, and then financing, regarding the capital structure of the company.

What is a financial model in Excel?

To this date, most of the professionals use excel for financial modeling. There are already numerous alternatives with enterprise resource planning (ERP) and business intelligence (BI) software, as well as modeling with programming languages ​​and metalanguages; but the reality is that Excel is still the dominant tool for financial modeling.

In the first place, many consulting firms and banks have not modernized their management systems and operate in areas and departments entirely unrelated to others, using Excel for internal communication. Excel is an almost universal application that everyone has installed on their computer.

But the most important thing is flexibility. To program the model of a multinational, a company can invest a lot of money in specially customized software or buy standard solutions from the management software brands.

But when it comes to analyzing individual and particular projects, Excel allows flexibility that management software cannot.

This can be Excel, but it could also be any other program such as LibreOffice, Lotus, or the like. The most common is Excel since it is ubiquitous in all companies, and its programming and automation functionalities were designed for use in office environments.

3.What elements do a financial model contain?

The basis of a financial model will be the Profit and Loss Account, the Balance Sheet, and the Cash Flow.

Depending on the structure of a company, this is further complicated with subsidiaries, currencies, and other complications. In the case of complex and multinational structures, the individual models could roll to consolidate in the general model.

This means that financial models can become very complex and therefore the data and its visualization must be carefully studied. A model that is useless due to its complexity leads to the same errors as an incomplete or unrealistic model.

The task of incorporating historical data into your model can be daunting. You have to locate the sources of the data to be used, filter and clean that data so that it is usable, check for errors, and ensure that it is updated correctly regularly.

Depending on the business environment, data will have to be collected from other applications, from data warehouses, from different departments, and all of this needs to be sorted, classified, and prepared for integration into the financial model.

Modeling can be structured in several phases:

  1. In the first phase, the information, the input, is collected in spreadsheets.
  2. Calculations are then made on this data collected in a second phase.
  3. Once calculated, the data will be displayed in the model.
  4. Finally, all this will be presented in dynamic graphics that allow the visualization of important information in a comfortable and easy-to-understand way.

The process of designing a financial model, the modeling process, is vital for the model to be functional.

4.To who is a financial model important?

The financial model is important to both the company and the investor. The company needs to have a clear vision of its business. The investor needs to know where he puts his money.

All companies need a business plan to organize themselves. From the moment in which external financing enters, be it by credit or investment, the financial model becomes an essential instrument for planning.

Investors expect a transparent, complete, and correct presentation of the financial situation, especially in matters relating to the financing of a company, especially of the projected income, capital, and liquidity situation. Investors only invest their money if they believe that the company will be able to return credits and interests in the future, making their investment profitable.

In practice, financial models are used internally, especially in budget planning processes, and in liquidity management or cash management, and of course, by the company’s management.

In many scenarios where business decisions are made regarding purchasing, suppliers, staff, equipment, or investments, the financial model can help better understand the impact of any decision on the financial structure of the company. It allows you to play with different scenarios, changing variables such as costs, income, interests, or provisions, and show a projection with the consequences that a decision on basic issues can have.

Being an abstract model, you can simulate scenarios before making a decision, and naturally, this helps to make the right decisions and not get bogged down with irrational decisions.

For example, outsourcing or acquisition options, and their impact on results, liquidity, or the overall value of the company can be analyzed.

Financial models fit each company like a glove. There are several standardized models, just as there are many companies that work in the same way, but in general, the more adjusted a model is to the reality it describes, the more usable it will be.

In the field of auditing and credit, financial models are already an essential tool for decision-making, since they allow simulating and analyzing the situation of the company in all its aspects.

Our financial models only used for financing purposes?

A financial model can be used as a management tool in many areas. In the framework of the management of a company, for example, models can be used for the optimization of production and storage processes, for purchasing decisions, for risk assessment, break-even analysis, to model amortizations, for strategic planning, or expansion decisions.

In the framework of financial management, models are used to close the year, to analyze or plan investments, to manage cash flow, plan budgets, or to analyze changes in the capital structure.

And naturally, in the investment banking framework, the financial model is vital for the valuation of companies, portfolio management, derivatives models, bond operations or to simulate investments in the stock market.

5.What advantages does the financial model bring?

A well-designed financial model brings many benefits. The first is that it facilitates strategic decision making. Even though it should never be forgotten that a model is based on the abstraction and simplification of business reality, the trends or possible consequences of a decision will always be broadly visible. The potential impact of a change in line, prices, wages, interest rates will be quantified in a matter of seconds after changing the corresponding variables. You can answer “what if…? visually and intuitively, since the model allows you to play with various scenarios and better adapt to any structural change.

Another great advantage is the flexibility that a financial model brings. Experienced financial modelers can change models substantially in the short term, adapting them to new realities within the company (such as mergers or acquisitions). When there are sudden changes in the basic conditions of a company, the famous unforeseen events, the modelers can react by adapting the financial model to the new reality. In this way, a company can react and manage structural changes in a more agile way.

6.Who designs the financial model?

Generally, the financial modeler is a financial analyst. In a large company with a full finance department, there will likely be one person or group of people who design and maintain the financial model.

But the vast majority of modelers work in companies that offer financial services and make the models for clients or investors. In SMEs, it is common for financial models to be designed by specialized personnel from consulting companies, banks, incubators, investment funds, insurance companies, etc.

However, in SMEs, franchises, and micro-businesses, the owner or investor will probably make their financial models as long as the structure of the company is not excessively complex and the entrepreneur or manager has basic skills to do so.

Financial modelers in banking

Banks and especially investment banking often employ financial modeling specialists. Large bulge brackets (the most important investment banks) and elite boutiques (consulting firms that carry out large operations and compete with investment banks) usually train their modelers during the first years in the same company or send them to specialized courses.

These professionals tend to develop financial models linked to specific projects that rarely see the light of day since they are used for internal operations. The knowledge of these professionals stays in the bank or migrates when they change jobs and often the modeler can and does put a price on their information.

Financial modelers in companies

In large companies, there are communication channels and standards. Here each teacher has her booklet and there are no standardized models that apply to everyone. The more closed a company is, the more individualized its model will be.

Modelers in audits and consultancies

Modelers who work for the big four auditing and consulting firms often use more or less standardized solutions. Large consulting firms often have modeling departments that set the tone for consultants or auditors.

Modelers who work in smaller or specialized consultancies more opt for their recipes and thereby atomize their models, adapting them to each case.

7.Who needs to master financial modeling?

The financial model becomes important for many companies and consultancies in the framework of financing operations and capital inflows. When it comes to financing, the investor expects a fair and transparent presentation of the company’s earnings, capital, and liquidity expectations.

This planning is done with Excel since it is an almost universal instrument in business communication. In practice, the financial model focuses on Cash Management aspects, presenting the availability of cash in the short and medium-term. With the model, the company or the consultancy can simulate the different options and business strategies. In this way, decision-making with the model as a reference is facilitated.

Banks also use financial models when analyzing the financial situation of a company and especially when evaluating it.

With a good model, you can compare various business strategies and their consequences, you can plan break-even or simulate capital inflows.

8.What risks does modeling entail?

Like any good instrument, music only sounds good if the player knows how to play it. Using a poorly prepared financial model, with false or erroneous data, and that does not take into account important variables for the company, is perhaps worse than having nothing.

Since the financial model can be the basis for important decisions such as the sale or acquisition of a company, any unrealistic factor can lead to a bad decision.

Planning an investment, a financing plan, the entry of a capital partner, or planning for medium-term profitability will be science fiction if they are done with a faulty model.

Whenever a complicated calculation is performed with tables and spreadsheets there are complex operations and formulas involved. A well-developed model allows considering dependencies on economic indicators, scenarios, and sensitivity analysis to perform simulations such as the Monte-Carlo simulation.

There are three risks:

  1. Elaboration with false premises
  2. Elaboration with errors
  3. Over-simplified processing

A financial model built on faulty premises or data is not much more than a milkmaid’s tale. In extreme cases, the consequences can lead to bankruptcy or court if data has been intentionally misrepresented.

Elaboration with errors can lead to incorrect decisions due to simple miscalculations. All relevant factors may have already been considered, the data checked, and soft factors such as market valuations included… if there are failures in the use of the tool (usually Excel), the model will be defective and inconsistent. This leads to a loss of confidence in the model that can overshadow correct reasoning, discarding a potentially correct model due to minor calculation problems.

One way to avoid these failures is to check for errors and monitor or control the results. This is very important, considering that according to the ICAEW (Institute of Chartered Accountants in England and Wales) 90% of spreadsheets in Excel contain errors and more than half of them have been prepared without any formal method.

The third risk is something that happens quite a bit in actual practice. To avoid mistakes in abstracting from very complex situations, there is a tendency to oversimplify. This simplification can lead to obviating influencing factors that, when absent, lead to inappropriate decisions.

Modeling is also a matter of trust.

These risks are real and therefore confidence in the quality of a financial model is vital. The client, auditor, or bank needs to know that they are deciding on a solid basis and not on smoke.

9.What is important to know to make good models?

The financial modeling process comprises knowledge of various processes, techniques, and skills.

On the one hand, a domain of Excel is important.

But above all, it is important to know and understand the basic principles of accounting and finance as well as the basic principles of running a business.

For complex cases, it is also necessary to understand complex financial concepts. Only a good understanding allows us to simplify in a coherent way and without losing the logic of a business.

Excel and standardization

In practice, mastery of Excel is vital to the model successfully. It is also vital for the modeler to be able to focus on business processes and financial structure without dwelling on Excel calculations and formats that distract from what matters.

The great advantage of Excel is also a weakness: Being a very flexible tool, many models end up reinventing the wheel with each model. So you have to learn to use common standards for common situations.

There are several options for standardizing financial models. An example is a FAST model, which includes rules and guidelines for structuring and modeling details with Excel.

10.Where do you learn to model?

In universities, there are financial modeling subjects in some master’s degrees for business and economics. However, theoretical learning is one thing and actual practice. In a general studies framework, modeling is one more topic.

In practice, modeling requires the use of Excel and/or other spreadsheet programs in addition to a basic financial knowledge base.

The more complex the reality of a company, the more complex a good model will require and with it, the knowledge necessary to be able to abstract reality and design a financial model will have to be more specialized.