How to build a financial model

One of the main myths that I have to fight in my work is that "we make and sell financial models." This is fundamentally wrong and interferes with work.

The purpose of the settlement process is to understand what the prospects are, what are the possible options for action, the formation of a negotiating position, the conduct of negotiations, the adoption of decisions and their implementation. A financial model is never a goal in the settlement process – neither for the bank nor for the borrower. You may be "sold" the opposite - feel free to refuse the services of such a consultant.

At the same time, the financial model can be a very convenient and useful tool in the process of assessing the prospects for debt settlement. My colleague, the manager of our group, Dmitry Astakhov, will tell you how to approach the construction of financial models correctly.

Modeling for restructuring purposes

The difference between restructuring models than, for example, equity valuation models (in which the output is the value of 1 share rounded to, say, the hundredth or thousandth) is perhaps less focus on the operational part of the model, but with more attention to the company's ability to service debt. In terms of debt servicing, the restructuring model can include various mechanisms that the user can "play with" and see the result (when the debt will be paid and whether it will be paid at all!) depending on the option chosen.

In this release, we do not touch on individual "specific" aspects of modeling (e.g. how to use offset, index, match functions together, or how to technically model VAT, or use so-called "flags"). There is a large amount of open source material for this (see the list at the end of the article as an example of sources where you can find examples of models with comments and videos on how to approach the prediction of a particular part of the model). Instead, I will talk about some of the features of building financial models for companies in financial difficulty.

From the point of view of the forecasting horizon, models can be divided into short-term, medium-term and long-term. The most popular is the long-term model. It allows the user to look at the company's cash flow forecasts for several years ahead, to understand whether this company is able to service its debt.

Short-term models are less popular, but in critical situations, they can be the most important tool for a company to take the right steps and avoid bankruptcy (in the most pessimistic scenario). Also, in practice, medium-term models – up to a year, which are a kind of hybrid between short-term and long-term models, have been rare, but rare.

Next, I will talk about building a long-term model as the most commonly used.

Simplifying the operating model

If possible, it is necessary to strive to simplify the operational part of the company's financial model.

Often, distressed companies do not have high-quality and reliable information to use as input data for the model. In this case, detailing will only lead to the well-known "garbage in – garbage out" issue. For example, one of the clients did not have accounting at all. Or rather, it was present, but it was absolutely useless, since it did not reflect the real financial condition of the borrower. For now, it's a booze. Accounting was restored "from scratch", we built a long-term financial model on the basis of management accounting, in which the cash flow statement was formed by the direct (!) method, from which the statement of financial results (P&L) was reconstructed (!) on the basis of incomprehensible working capital balances. There was no balance as such, I had to make a synthetic balance (pro forma). Of course, in this or a similar situation, no one cares about modeling fixed assets by fixed asset categories (buildings, structures, etc.) through their depreciation rates, as well as not about excessively detailed modeling of working capital (other "receivables" and "payables" through the gradual normalization of turnover periods, or VAT, unless the company reimburses some significant amounts in connection with the implementation of capital expenditures). Even interest can be accrued on the initial debt balance in order to avoid circular reference (despite the fact that there are mechanisms for bypassing the so-called "circular reference", Dmitry Miguel and I do not see anything wrong with the deliberate use of the iteration mechanism in excel, the main thing is to check the integrity of the model and not get carried away with cyclicality).

Banks do not care whether the borrower repays the debt in the 4th quarter of 2022 or a quarter later. It is rather important for the bank to answer questions such as "the patient is more likely to be alive or...". He is interested in having at hand a tool in which he can simply and clearly select scenarios (revenue, expenditure, financing block) and look at the overall level of the projected free cash flow and the repayment period – for example, within 5-7 years, or, say, no earlier than 2030-2035.  than in the second.

Meanwhile, it is not always possible to simplify the model, which is often due to the specifics of the company and its operating model of the business (for example, sometimes work-in-progress is modeled in detail as part of the "inventory") or to specific customer requests. For example, on one of the projects, the client wanted a very detailed forecast of working capital (for each item (!) of income and expense with its own contractual payment terms, to see a quarterly forecast of working capital components, for an existing business (flows for which "fade" and disappear within a few years due to the client's business model), as well as for a "new" business – flows for which come to replace the old "fading" business). At the same time, it was necessary to distinguish cash flows in the "roll" format (using the example of "accounts payable"):

["balance of the accounts payable item by expense item XX at the beginning of the period + accrual of expense XX for the period – payment of funds on the expense item XX for the period = balance of the payable item by expense item XX at the end of the period"].

Not surprisingly, the calculations in the model on the "working capital" tab took about 1,500 lines.

Meanwhile, the more simplified nature of the operating block compared to the usual valuation model is "compensated" by a more complex financing block.

Financing Unit

The result of the operating part of the financial model is the cash flow from operating activities (before interest payments) and investment cash flow, which together represent, in fact, free cash flow (FCFF – free cash flow to the firm). After adding shareholders' funds to the FCFF, we get the so-called cash flow available for debt service (let's call it a term often used in project financing modeling – CFADS, cash flow available for debt service).

Then comes the fun part: how to use this Debt Service Cash Flow (CFADS) to repay that debt. Various options can be "sewn" into the model.

Firstly, this flow itself can periodically (in case of cash gaps) be negative. In this case, it is necessary to attract new (revolving) financing (sometimes called "revolving"). At the same time, it is possible to specify how this financing will be attracted – from existing banks, from one of them (for example, the main lender), or from the "no name" of the bank.

Secondly, you can include in the model the option of refinancing the entire debt at any time. For example, if it is a syndicate of banks, and the borrower has a lot of loans, then all of them can be transferred, say, to the main lender, or the "no name" bank, indicating, if necessary, new interest rates.

Thirdly, you can "play" with interest – both with rates and with a moratorium on payments, while capitalizing the unpaid interest either in the body of the debt (and accruing interest on %), or not including the compound interest formula, but simply accumulating interest in the debt with subsequent payment in the future.

Finally, the model can take into account different priorities of payments, whether in terms of (1) creditor banks, or in terms of (2) the "body" of debt and interest, or a combination of (1) and (2). For example, first, the body of the debt of creditor banks is paid in accordance with the structure of the debt, a moratorium is imposed on the payment of interest, after the repayment of the body of the debt, the repayment of interest begins. When repaying debt, the model, as a rule, takes into account the "cash sweep" mechanism - the minimum cash balance for operating activities that the company must have at the end of each forecast period, and everything that the company manages to generate in excess goes to repay the debt. The formula looks like this:

[Free cash that can be used to repay debt in period N = max(CFADS for the period + cash balance at the beginning of the period – minimum amount of cash at the end of the period; 0)]

With the help of such a formula, a debt recovery waterfall is built in the financing block depending on the parameters selected by the user. You can build this waterfall quite easily, the main thing is to set the order and rule.

I have described just a few of the most common examples of working with financial models for debt settlement purposes. In life, everything is different: both more complicated and simpler, it depends on the specific problem situation. As an example, see the illustration of the dashboard of one of the restructuring models, as well as a real-world example of a very simple implementation of a financial model.

Information on financial models from open sources:

https://edbodmer.wikispaces.com/+Home

http://www.youtube.com/user/edbodmer

https://www.macabacus.com/

http://www.f1f9.com/

http://www.financialmodellinghandbook.com/

http://www.youtube.com/user/financialmodeling/videos