A financial model is a very common instrument used by companies to visualize the financial state of a company. Financial models are commonly used by finance teams when projecting the future situation of a company. It is important to differentiate the financial model from the annual budget, or from a liquidity financial model, or liquidity analysis. Generally, the financial model is an instrument that tries to reflect the situation of the company in the long term, or several years ahead. The budget, however, tries to define the financial situation of the coming current year and its objective is to have a reference of the financial situation of the coming year and also serves as a way of establishing the objectives of the year. The liquidity model is a more short-term financial model that tries to analyze the financial situation during the coming months to have greater short-term visibility of the business, and how to deal with the following payments to suppliers, tax payments, debt payments, etc.

A financial model is normally prepared in Excel. Excel for this purpose is probably one of the most suitable tools on the market and to this day, it is the main technology where they are prepared and there has been practically no tool capable of replacing it. The good thing about using Excel is the flexibility that this tool provides, since it allows you to make multiple changes, scenarios, and work with multiple variables.

As we said before, financial models are very commonly used in financial departments. It is usually the usual instrument for a financier to make his business projections and serves as a source of information to discuss future plans with the board of directors of the company. Furthermore, financial models are very common in the investment world. The “Private Equity” funds base their purchase intentions on a value obtained through financial models. In these Excels, which are generally huge, the investment funds carry out the growth projections of the company they want to buy and with this they obtain a value of the company that ends up translating into the purchase price. Furthermore, the financial model is not only common in investment funds. Investment banks, which are generally on the other side, also use financial models as an instrument to value companies that they are advising to sell, or on the other side, which they are advising to buy. That is why the world of investment banking, and investment in general, is one of the main users of financial models, since major decisions are made based on information from the financial model.

Before you start building a financial model, you may wonder. What do I need to know to be able to build a financial model? What knowledge áreas should I have?

• Excel: The first, and one of the most important, is to have a great knowledge of Excel. Having an advanced knowledge of Excel is not easy, hours and hours are required in front of the computer to achieve an advanced level. It is very important to know all the “Shortcuts” or “Shortcuts” of Excel, to be able to do operations much faster and not waste so much time. To move faster, there are also a series of applications or “macros” that allow you to customize certain actions so that you can perform certain operations and calculations, as well as create number formats, currency, negative, positive, etc. much faster.

• Accounting: Accounting is one of the most important knowledge areas required when preparing a financial model. Let’s say that, without accounting knowledge, it is practically impossible to prepare a model, and even more impossible, to make it balance in the end. A general understanding of accounting, the profit and loss statement, the cash flow statement, and the balance sheet is required. Only with knowledge of all this will a person be able to balance a financial model.

• Taxation: Tax management is also very important when preparing a financial model. Since financial projections ultimately have a great impact on future taxes to be paid, it is important to have an advanced knowledge of taxation, in order to adequately model the tax part and thus accurately calculate the expected taxes to be paid each year. Serious errors in this part can cause substantial distortions in the valuation of a company and that is why it is usually also advisable to review the tax part through a tax advisor.

If we ask finance personnel who are dedicated to preparing financial models regularly in their work, probably the vast majority would agree that there is a code of good practices when preparing a financial model that are basic, since the objective is that the models financial statements are easy to understand and easy to follow, that is, that there is ease and traceability in the calculations throughout the entire financial model.

• Use Macros:

First, the first good practice in my opinion, after having prepared very complex and extensive financial models for years in the world of private equity, is to use a small application or macro that allows you to create some formats predefined and create some shortcuts, which once you know them by heart allows you to do actions in seconds and not use the mouse. Beyond these, for me the most important is to use a macro that allows you to trace dependents or, trace the dependencies, so that you can audit formulas and that the macro will take you to each page automatically. Without this tool it is almost impossible for me to model nowadays.

• Use different formats for different types of information:

This is very common to differentiate different information within a model. For example, the formulas are always in black, that is, a format is not applied to them. On the other hand, the “inputs”, or the information that goes in the form of a fixed value within a cell, are usually colored blue, for example, and all those formulas that come from another page are usually colored. green, to know that they are connected from another page.

• Don’t hardcode inputs across the financial model

One of the most common errors, and also the most hated, is when a fixed value is introduced into the financial model. In other words, a value is included in an immense Excel full of formulas and it is not able to distinguish it from the rest of the formulas. That is why, as we said in the previous point, it is necessary to differentiate the different types of information. Entering fixed values ​​without marking them in a different way is one of the most common errors in financial models, and they also have to create larger errors if they break formulas or do not follow the logic that the model should have.

The structure of a financial model is not fixed, it is a decision that usually remains in the hands of the person who ends up preparing the financial model. It is often said that first of all, structurally there are two types, vertical models and horizontal models. The vertical ones are those in which almost only a single tab or tab of Excel is used, and it is modeled downwards, that is, the model is completed by introducing sections downwards on the same page. Another way of modeling is horizontally, that is, adding sections or pages with different parts of the model. Good practices generally recommend creating horizontal financial models, so that the information is more organized and there is not so much information that is difficult to find on the same page.

On the other hand, sophisticated financial models often have the following pages or tabs. An inputs page, where all the initial fixed values ​​are inserted. From salary, operational costs, sales hypotheses, etc. On the other hand, we would have a timing page, where a series of flags are established that determine when events occur throughout the model period. On the other hand, we would have a macro page, where we would insert the macro variables that will affect the financial model, such as inflation, CPI, and other macro variables that may affect the business. Finally, we would create a scenario selector page, which will allow us to change scenarios in one click, instead of creating rigid scenarios that can break the model every time we are asked for a change. Subsequently, we will begin to connect the model and create the different financial sections, the most important for us being the cash flow section. We generally recommend building a monthly model, to later create summary pages with either quarterly or annual information, ending up creating a kind of main dashboard where we see the relevant information condensed and summarized in one place.

This field will be unknown to many people, but there are also audits of financial models. The audit of financial models is very useful to ensure and guarantee the reliability of a financial model and its conclusions. The audit of financial models is usually carried out in almost 100% of M&A transactions (“Mergers and Acquisitions”) in English. Also in debt operations, such as project financing and construction (Project finance), in which banks or bondholders will participate in the project debt. The model audit generally consists of a consultant doing a thorough review of the model, cell by cell. Also use a small program that does a cell-by-cell review of all the formulas to ensure that they are accurate and that there are no errors.

Snab is a pioneering treasury cloud platform in Europe and totally complementary to financial models, and that helps precisely in each one of the functions and tasks related to cash management and treasury management, among which are also finds the generation of cash financial models. Snab serves first as a bank aggregation platform, allowing companies to access all their bank accounts in different banks and in different countries from a single place and with absolute independence. Snab also serves as a billing tool, that is, invoice reception, scanning, and automatic registration of the same. In addition, Snab is a payment and collection management tool, providing total control and visibility to the accounts payable and receivable functions, since the platform has innovative technology that allows you to pay and collect invoices in one click, without the need to leave the platform. Snab not only shows you alerts and helps you monitor the status of payments and collections, but also generates treasury forecasts in real time, totally complementary to the financial model, and which are based on both historical and financial information. future. Finally, Snab is synchronized with the main ERPs in the market, which eliminates the disconnect between systems that exists in today’s world where banking platforms and ERPs have no connection.