The most common valuation model economic efficiency is the financial model of the investment project. This financial model should be complex to take into account all possible factors, but its basis, of course, is the apparatus of analysis economic indicators, consisting of the following bundle: balance sheets - financial results - cash flow.
When it comes to assessing the economic efficiency of an enterprise, this link is filled out on the basis of three main types of accounting reports, respectively: balance sheet, income statement and cash flow statement. If we are evaluating an investment project, the initial information on the balance sheets will be information on the existing assets of the enterprise in the context of their structure, as well as on possible sources cash receipts and the formation of new assets (that is, about investments). Expenses as a result of the project, as well as income from it, cash payments and receipts, the balance of funds and, finally, the financial result are predictable values that will be determined in the financial model we are developing.
The main thing in the financial model of an investment project is to create a correct, interconnected, comprehensive picture of the project in dynamics for the period of its implementation. A comprehensive financial model should contain the following elements:
Financial plan;
Resource engagement plans;
Development of possible scenarios for the development of the project;
Development of business processes;
Investment evaluation and project cost forecast.
These elements in practice can be expressed in various forms of the program part of the model. Its financial base is, first of all, a cash flow plan, or a forecast of receipts and payments ( cash inflows and outflows). Based on it, it is easy to get an idea of the net cash flows during the implementation of various stages of the project, the lack or excess of funds, that is, to calculate how much money the company has at its disposal and what need it needs.
The financial cash flow plan (Cash Flow) can be drawn up in different ways: the articles in it can follow both in the direction of financial movement (first - all receipts, then - all payments), and by type of activity (receipts and payments separately for the first type of activity, then - on the second). There are three types of activities for the company: main, investment (associated with investments, long-term acquisitions) and financial (associated with loans, loans, loans, that is, with the attraction and return of debt financing). The construction of a financial plan for cash flow can be carried out both by a direct method, directly changing cash flows, and indirectly, based on plans and balances of profits and losses. Calculated based on the results of the cash flow plan, net cash flow allows you to evaluate the value of the enterprise.
The plan, or forecast, of profits and losses includes income items - sales proceeds, expense items - costs, expenses, as well as the amount of tax and other deductions. Based on these indicators for billing period it is possible to determine the profit that will remain at the disposal of the enterprise as the project is implemented and after its completion. Actually, the profit and loss forecast allows you to find out whether the company will make a profit at all, that is, whether the project is economically profitable (effective). If it is written as a comparative forecast for the implementation of various projects, it is possible to analyze the relative effectiveness of different options for the development of an enterprise.
You should understand the difference between the cash flow plan and the profit and loss plan in the financial model of the investment project. These two plans would completely coincide in the event that all expenses and income from the implementation of the project would appear simultaneously with their actual payment, and there would be no movement on loans and depreciable property of the company.
There is also a balance plan, or forecast. He notes strengths and weak sides enterprises from a financial point of view at the current moment. In the context of an investment project, the balance plan captures not only the initial balance sheet of the company, but also the features of its development and results. financial activities during the implementation of the project. Each element of balance is not considered on its own - it gives little. All elements are analyzed in a complex, in relation to each other, then you can get an idea of financial solvency project.
The three plans described above represent the three components of the bundle that was given at the very beginning of the article: balance sheets - financial results - cash flow. These three main components of the financial model of an investment project must be interconnected, “join” with each other (assets must converge with liabilities, balances in the overall balance sheet of the company and cash forecasts must match, etc.). In this case, the associated values are dependent on each other, but not equal to each other. However, if any data is missing from one of the generated documents, it can be calculated using data from the other two documents. This is illustrated in the figure below.
The financial model of the investment project is based, calculated and analyzed according to the formulas given in our article "Efficiency of the investment project". Accordingly, it takes into account the fact that money changes its value over time, so all flows must be recalculated at the current moment (discounting must be performed). And the main question that arises in the analysis of investment projects is the following: what size of the discount rate should be taken?
Options for determining the discount rate are different; depending on the specifics of the project, it can serve as:
Weighted average cost of capital (as in the mentioned article);
Project equity cost;
The level of income that can be obtained as a result of a risk-free investment;
Nominal (market average) rate of return for a given level of risk;
inflation rate;
Some generalized coefficients that take into account market conditions, the impact of the macroeconomic environment, etc.
Owners of companies that compile financial models to attract investments should understand that the figures and calculations given in the model must inspire investor confidence, and the financial model itself must be of high quality, detailed and realistic. Nowadays, this is becoming a decisive factor for obtaining funding. Therefore, the parameters of the project should be based on in-depth market research, and it will not be superfluous to confirm some of the data with preliminary documentary agreements with future partners (both suppliers and implementers or buyers).
back to previous page - to sales and stock turnover management
It is extremely difficult to overestimate the role of trust in our lives, especially in business. The merchant, trusting his supplier, makes an advance payment for the goods, which will be delivered in a number of days specified in the contract. Or by uploading finished products customer, trusting him at least on the basis of the signed agreement, hopes that the payment will arrive on time, according to the deferred payment signed in the same agreement. When approving the company's budget, the CEO, trusting his commercial director, tries to be sure that the required volumes of products will be sold during the budget period, and after which he will not be ashamed to report to the shareholders and investors of the company.
Based on Trust, we build financial plans. What is our trust based on?
In this section, the reader is offered an immersion in the practical aspects of developing a financial business model, as one of the key pillars of the system for making both tactical and, in some cases, strategic decisions. We will start by building a financial retail model. Moreover, we immediately note that there can be several types of financial models of the same business, depending on the internal management system of the company and the principles of business organization.
In any case, mastering the methods of financial modeling gives a fairly deep understanding of how to approach solving the issues of choosing a system of key performance indicators (KPI system) of a company and, most importantly, how to calculate the target values of the KPI system indicators in practice.
For example, depending on the management system, the budgeting process in a company can be built “top down” or “bottom to top”, and the management approach can be functional or process. Depending on the principles of business organization, a trading company can have its own delivery service, its own warehouse, its own call center, etc. on its balance sheet, or “outsource the whole thing”. Just taking into account such nuances will distinguish different types financial models of the same business.
Colleagues, many questions come about downloading an EXCEL file with a financial model for investment projects or investment model. We inform you: you can read about investment modeling in EXCEL and the corresponding investment analysis (NPV, IRR, etc.) on the next page, where you can also download an example of a financial model of investment projects in EXCEL with calculations investment indicators type NPV, which can also be downloaded and .
Therefore, in order to bring greater certainty and be as close to practice as possible, we will begin with a description of the methodology of the simplest financial model. retail, namely the retail financial model with a top-down budgeting system (Top-Down) and outsourcing of the main operational units, such as customer support (call center), inbound, warehouse and outbound logistics. Also, at the initial stage, we will omit accounting for non-current assets, for example, such as fixed assets, since they obviously do not play a significant role in creating a financial model for trading activities.
Posting this immediately financial model in the form of an EXCEL file, so that it is easier for the reader to perceive the description of the financial modeling methodology, which we will start a little later. For the sake of convenience of presentation and description of the financial model, for example, the initial data in the tabs with initial conditions are included in it.
EXCEL file with an empty, blank financial model, i.e. with zero input parameters can be downloaded at the end of the section.
We think that it will be quite fair to offer the interested reader to buy the full FULL version at a price of 750 rubles. depending on the content of the analytics. In addition, we offer everyone who has bought any of our financial models a free consultation within 5 days in order to refine our model for your specific tasks.
Of course, further in the sections of our website, we will present to the reader's attention a description of both other types of retail financial models (for example, when budgeting occurs “from the bottom up” and operating units are not outsourced), and financial models of other business areas, for example, such as sales , installation and commissioning of equipment, construction, operation of real estate, production of high technology products, production of heavy engineering products, etc.
A list and description of all financial models presented on our website can be found.
We also immediately post a truncated DEMO version of the retail financial model in the form of the following EXCEL file for commercial divisions, sales department managers, category managers, etc. This version does not have a balance sheet (Balance Sheet), a tab with a task financial terms(“CF_conditions”) of cash flow and, accordingly, a cash flow statement (Cash Flow report). Thus, on the basis of this financial model, at least, it is possible to model the structure of the revenue side (P&L report - profit and loss), as well as the volume and structure of turnover in the context of sales directions and product categories.
Attention! This model was developed in EXCEL-2013. The model uses drop-down lists, which most likely will not work if you have Excel-2007. Contact us and we will help you set up the model for correct operation.
EXCEL file with the same, but only an empty, unfilled financial model, i.e. with zero input parameters in the "conditions" tab can be downloaded at the end of the section.
You can buy a full FULL version of the financial model for merchants at a price of 450 rubles.
So, let's proceed to the description of the methodology of financial modeling. Let's start with the content of our financial model. Since the models are presented by us in the form of EXCEL files, we will use such structural concepts EXCEL like sheet/tab, cell, term, column, formula, etc. And for the reader in this regard, for a better understanding of what in question, we suggest systematically looking into the downloaded EXCEL files with financial models.
The structure of the financial model begins with the "table of contents" tab, which provides a description of all the main sections, where each section is a separate tab.
For the convenience of using the financial model, the transition to all sections is organized by means of hyperlinks from the table of contents, and you can go back to the table of contents from each section using the hyperlink located in the upper left corner of each sheet of the EXCEL file.
All sections in our financial model are divided into the following groups:
Communication and methodological tabs;
Tabs with initial data - initial conditions of the financial model;
Tabs with calculations - the functionality of the financial model;
Tabs with reports are the result of financial modeling.
We refer to the communication and methodological tabs:
- "methodology";
- "details";
- "sections_methods";
- "indicators".
The "methodology" tab roughly repeats the content of this section of the site, only in a more technical form. This tab contains full information only in the FULL version of the financial model, which we just sell. The content of this tab can be extremely useful for your company's IT specialists, if, for example, you decide to independently implement our financial model in your company's financial management process.
Using the materials of the "methodology" sheet, you can, on the one hand, figure out how to adapt the financial model to the specific specifics of your company, for example, add or remove a product category from consideration or add another line of business, for example, sales in Belarus and Kazakhstan, or calculate the break-even point not by revenue, but by the number of goods, etc. On the other hand, you can use to write terms of reference for revision automated system management in terms of financial business modeling based on a corporate information system.
The remaining three tabs are of a communication nature, which will be fully revealed when presenting everything planned in this section of our site. We will only note that if one or another indicator used in the financial model and located in the list of the "indicators" tab should, in your opinion, have a different name, then feel free to change it and automatically wherever it occurs in the model, its the name will be changed to your new one.
In the full FULL version of the financial model, the "indicators" tab contains a Glossary with definitions and descriptions of all indicators included in the model.
Source data tabs
- "conditions";
- "CF_conditions";
they are just tabs where the user manually enters all the main values of the key indicators of the financial model. To start using the model here, you need to consider the following nuances. Values can only be entered in cells that are marked with either a solid black border line or a dashed solid border line and at the same time preceded by a red asterisk:
Moreover, the difference between cells with a solid line and those marked with a dotted line is that for cells with a solid line there is a drop-down list of possible values, and you enter values manually from the keyboard into the cells marked with a dotted line.
In many cases, if data is incorrectly entered into the tabs with initial conditions, a red inscription will appear: “Error!”. Therefore, be careful and if you have any questions, send them to us, for example, through the feedback form, we will try to answer promptly.
The functionality of the financial model is concentrated in two tabs
- "calculations";
- "calculations_daily"
and is a system set of EXCEL-formulas that convert the initial data of the model from the "conditions" tabs into the final reporting data of the tabs with the results of the financial model, structured according to the management forms of financial and economic reporting.
Finally, tabs with financial model reports are tabs with management reporting forms that are automatically filled in and recalculated when initial data changes, which in turn are usually divided into two groups: standard financial reporting forms such as a profit and loss statement (P&L), a report Cash Flow and Balance Sheet, plus we add the Stock Flow report to this list as an important report for retailers; and additional forms, which are designed for more detailed and comprehensive disclosure of financial and economic information, taking into account the specifics of the type of business that is formalized within the framework of the financial model. In our case, the following reports are presented as additional reporting forms:
- "SF_age" – report on age structure commodity stocks;
- "Turnover" - a report on the turnover;
- "FinCycle" – financial cycle calculation;
- "mPL" – profit and loss margin statement;
- "UE" - economy per sold order.
As an example, here is the format of the management income statement (P&L report), which we use in the financial model:
We start modeling the sales budget by specifying the budget term for modeling. In our case, we offer standard modeling for one year, and to set the budget period, it is enough to indicate the start date of the budget year in the "conditions" tab. Let's say it will be 2016, then we enter the date 01/01/16 into the corresponding cell manually from the keyboard (cell with a dotted border):
Next, we set the breakdown of the budget year into periods that we need to take into account the dynamics of changes in the key indicators of the financial model: we suggest choosing either quarterly or monthly breakdowns. Let's choose quarterly dynamics:
Please note that the cell for selecting the type of periodicity has a solid border, which means that only values from the specified drop-down list can be used as the values of the specified cell:
Monthly
Quarterly.
We hover over the cell, a button for selection appears on the right, click on it and select the required value - we chose "quarterly". As a result, a breakdown into quarters will automatically appear in the header of the value columns:
Or if you select "monthly", then this "scale" will appear:
Let's start with the budgeting method. First, we recall that we initially believe that the management system of our company involves building the budgeting process from top to bottom, see above. Usually, with this approach, the sales plan is “set from above” by the owners or large investors on an enlarged basis. Most often, this happens either through the approval by the owners of a specific sales volume, expressed in money, for the budget year, with a possible breakdown by periods, or through the approval of the planned percentage of growth in sales volumes of the budget year in relation to the actual sales volumes of the previous year. Sometimes it also happens that the sales volume plan is approved not in money, but in the number of customer orders or in the number of sales of goods, in parallel, accompanied by the planned average sales receipt or the planned average sales price of one product, which, by the way, is much better. Such a "plan" descends on the shoulders of the General Director. And then "Forward Top Management!"
There are three types of sales in our financial model:
Retail sale of goods/orders (B2C);
Income from the sale of services for the delivery of customer orders;
Sale of B2B services.
Moreover, B2C sales, in turn, can be detailed in three “dimensions”:
Detailing by business lines;
Regional detail;
In order for our example not to be trivial, but, on the other hand, not to be too overloaded, we presented a variant when our hypothetical trading company There can be two business lines in terms of B2C sales:
Sales from a warehouse (offline sales);
VMI sales (online sales);
Two regional directions:
Sales in Moscow and the Moscow region;
Regional sales;
Electronics;
Cloth.
By “stock sales” we mean classic sales from ordinary offline stores, by VMI sales we mean online sales through our company’s online store, when the corresponding stock from Suppliers is blocked for client orders (such sales are also called Block Stock), more about VMI Sales can be read.
Examples of operational financial models of the online sales budget in the form of EXCEL files from the point of view of the marketing and commercial divisions of online retail, as well as the possibility of scenario analysis of the visions of the marketing and commercial departments, can be found.
In terms of B2B sales, we only consider the possibility of detailing in two areas of business, in our case, we chose the following areas:
Commission sales offline;
Sales of Market Place online services.
You can read about what Market Place is.
Thus, the revenue structure in our financial model can have a maximum of fourteen combinations - twelve for direct sales of goods and two combinations for sales of B2B services.
Of course, if you need to add any additional detail dimensions, for example, add customer categories to implement a loyalty program, or supplement any current detail, for example, add a few more product categories or expand the regional breakdown to federal districts, then it is not difficult to do this by converting the financial models laid out here accordingly, either on your own, or by contacting us with such a request through the feedback form.
And this means that within the framework of our restriction on the model of the budgeting process (from top to bottom), it is necessary that our financial model, after entering into it data on sales volumes approved from above, could distribute these volumes among all the fourteen combinations indicated above.
Let us describe how all this is implemented in the financial model.
There are nine options for entering the approved top-level product sales plan into the model, which are selected from the drop-down list, as shown in the figure:
The full list of budgeting methods is as follows:
Financial-direct;
Financial-growth y/y;
Financial-increments per/per;
Goods-direct;
Growth goods y/y;
Goods-increments per/per;
Orders - direct;
Orders-gains y/y;
Orders-increments per / per.
The financial method of budgeting sales of goods involves entering data on planned sales volumes by quarters / months (periods) immediately in thousands of rubles. The “goods” budgeting method involves entering data on sales in pieces of goods, respectively, the “orders” method - in the number of orders.
Additional attributes of the method "direct", "increases y / y" and "increments per / per" mean the method of setting sales volumes:
"direct" - is set manually by entering sales volumes (in money, in pieces of goods or in the number of orders) directly into the budget year plan for each period;
“straight y/y” – set manually by first entering the actual sales volumes for each period of the previous year and then entering the approved target annual growth percentages for each corresponding period of the budget year;
“straight lane / lane” - is set manually, by first entering the actual sales volume of the last period (either December if monthly, or the 4th quarter - if quarterly breakdown is selected) of the last year and then entering the approved planned percent growth of each period of the budget year to previous period.
The previous figure shows the completion of sales volumes when choosing the financial-direct budgeting method in the case of a quarterly breakdown of the budget year - we see on it that sales volumes of 260, 300, 270 and 380 million rubles are planned from the first to the fourth quarter. respectively, which are simply entered into the cells "with dotted borders" manually from the keyboard.
For example, let's consider a couple more options for entering sales volumes for the "products-increases y/y" and "orders-increments per/per" methods.
Suppose in the previous year, in fact, the company sold 40 thousand. pieces of goods in the first quarter, 50 thousand. pieces in the second quarter, 45 thousand. pcs - in the third and 60 thousand. - in the fourth. Let also for the budget year, the owners of the company approved the planned increase in sales volumes in pieces of goods in the following amounts: 10% - the planned increase in the volumes of the first quarter of the budget year compared to the first quarter of the previous year; 20% - increase in the second quarter; 15% and 30% - respectively, the growth of the third and fourth. Then, choosing the budgeting method "goods-increments y / y", we get the following "picture" when filling in the above data in the "conditions" tab of the financial model:
We see that the first line, in which we entered data with the direct method, is empty and, moreover, there is no “red asterisk” opposite it, i.e. the financial model does not offer to fill in this line. And it is proposed to fill in the following two lines (with "asterisks") - one for the actual data of the last year, and the other for the planned percentage of growth year on year. In the last final line, the financial model calculated for us the sales plan in pieces of goods for the budget year: 44 thousand, 60 thousand, 51 750 and 78 thousand. pieces of goods for each quarter.
When choosing the “orders-increases per / per” budgeting method, we enter the number of orders sold in the fourth quarter of last year, let this number be 70 thousand, after which we enter the planned increases in sales volumes in the number of orders: let’s say (-5%) - the planned increase the number of orders in the 1st quarter of the budget year to the fact of the 4th quarter of the previous year; 10% - increase in 2Q to 1Q of the budget year; 5% and 30% - respectively, the increase in 3Q and 4Q in relation to 2Q and 3Q of the budget year. Then we get:
Thus, we get the final plan for sales of goods in the number of orders in a quarterly breakdown: 66,500, 73,150, 76,808 and 99,850 orders.
Note that, depending on the choice of budgeting method, the financial model will offer the necessary cells to fill in, putting a red asterisk in front of them, as well as in the "indicator" field and the "unit of measure" field. will highlight adequate this method indicator and unit of measure. That is, it is necessary to carefully monitor the communication of the presented financial models.
B2B sales planning is much easier, so we'll get to that section a little further down.
Now comes the next nuance. If we choose financial method budgeting, it is obvious that without much effort we get a sales plan in thousands of rubles, broken down by budget periods, and we can begin to detail it. Another thing is when the sales plan is initially entered in pieces of goods or in the number of orders sold. In this case, in order to obtain a sales plan in money, we need either the planned average cost of selling one commodity unit (one piece) or the planned average checks for one sold order.
In this case, the financial model naturally suggests (see EXCEL files with financial models) to enter data on the planned average bill of one client order or on the planned average cost sales of one product, depending on the selected budgeting method (“orders” or “products”). But it is already necessary to enter these average values not in one amount for each budget period, but for each type of detailing separately - in the most general case, we have 12 of them for B2C sales.
Therefore, immediately after entering the planned sales volumes of the top level, and it doesn’t matter in money, in pieces of goods or in the number of orders, the financial model “asks” three questions:
Do you need detailing by business lines;
Is regional detailing necessary?
To answer these questions, cells with a solid border line are provided, when you hover over them, a list of two possible values \u200b\u200bdrops out: “yes” or “no”. If the answer is “yes”, it is proposed to fill in the percentages of the distribution of planned sales volumes in the corresponding areas of the selected type of detailing.
If the user answers “no” to all three of these questions, i.e. no details are required, then only in this case, with the “orders” or “goods” budgeting methods, it is enough to enter the average values \u200b\u200bof the cost of a check or one product in one amount for each budget period - the financial model will automatically indicate where this data is required to be entered.
For example, if a company has sales directions both offline and online, then accounting for the distribution of the sales plan for these business lines will be set as follows:
Here, percentages of sales volumes are set manually for the online direction, and for offline directions they are calculated as “100% minus the percentage of online”.
Well, in our case, when all possible details are present, the introduction of percentages of the distribution of planned sales volumes looks like this (see EXCEL files with financial models):
The distribution of sales is set in the "conditions" tab in the form of distribution percentages, and the calculation in money, pieces of goods or the number of orders occurs in the "calculations" tab.
Now that the distribution of the sales plan is set for all the necessary combinations of business detail, the average cost of sales of one product or one order (in the case of choosing a non-financial budgeting method) for each combination is manually set. Looks like this in the following way:
After that, in the "calculations" tab, the final average checks (average costs of one product) are calculated for each budget period of the financial model, and they are displayed in the final line under the entered data on average checks in various combinations, see the last figure - the last line.
In the case of choosing the budgeting method "financial", the model asks the question: "Is it necessary to set the average check for one order?" If the answer is “yes”, everything happens as in the above figure, if the answer is “no”, average checks are not set and, accordingly, there will be no analytics in the financial model in terms of the number of orders and pieces of goods.
Further, if necessary, you can set the choice of the number of goods in one order, and then the model will make full calculations in all sales units: in thousands of rubles, in the number of orders and in the number of pieces of goods.
The next step is to have the financial model calculate planned cost sales and purchasing budget. To do this, the model will prompt you to enter the values of such indicators as profitability of sales, daily distribution of sales by days of the week or average percentage distribution of weekly traffic and sales turnover periods in days for each combination of business details.
You can see the scheme for building a procurement budget.
By analogy with setting the average check, we manually set the return on sales:
The final profitability is also calculated through the "calculations" tab and displayed in the final line of profitability in the "conditions" tab, see figure.
We calculate the cost according to the formula:
COGS = Sales * (1 - R).
To improve the accuracy of financial model calculations, we propose to set the average distribution of sales by days of the week. For example, if Sundays are days off in our company's offline stores, and the main influx of visitors falls on Friday and Saturday, then it is advisable to take this into account. Or, for example, there are no days off for the direction of online trading, and the main volume falls on the middle of the week:
Here, for each day except Monday, the sales distribution percentage is entered manually, and for Monday it is calculated as 100% minus the sum of the percentages for all other days.
Finally, by analogy, we set the target sales turnover periods in days, as the average number of days from the moment the product is purchased to the moment it is sold to the client.
But not everything is so simple with online sales. Online sales usually begin with the formation by customers of orders for the purchase of goods on the company's website, after which, if it is, as in our case, VMI sales, then the order is transferred to the purchasing service, which in turn redeems the ordered goods from VMI within one day. suppliers. Then the goods go to the warehouse and there they are distributed according to customer orders, enter the shipping area and are delivered to customers. Thus, for online orders, it is necessary to specifically take into account the length of the operating cycle, expressed in the number of days from the moment the client places an order on the company's website and until it is delivered directly to the client, which our financial model does:
In connection with the above, the sales turnover period for directing online sales in our financial model must be set as the number of days from the moment a customer places an order on the website until the moment the goods of this order are purchased from VMI suppliers, and with a minus sign.
We note here that in this financial model we take into account the specifics of online trading very superficially, for example, we do not even include the fulfillment level in the model (by default, we have it equal to 100%). The fact is that a complete detailed financial model of online retail, taking into account all the features of the online sales operating cycle, is posted on our website. But still, in order to draw the attention of users to this, we introduced the specifics described above into the conditions of the financial model.
Revenue from the provision of delivery services within the B2C sales direction is set through the percentage of sales delivered to the client. It is clear that such a percentage for offline trading is low (we set it at the level of 20-25%, see the financial model), and for the online direction it tends to 100% (we have it at the level of 90-95%).
We have two types of B2B direction - this is the usual commission offline sale of goods, for which our hypothetical trading company receives a commission from suppliers, expressed as a percentage of the turnover, and an online trading platform - Market Place, where suppliers are invited to advertise their goods, after which, when customer orders arise, our company transfers these orders to suppliers, as a result of which it receives its agent's commission, as a percentage of the amount of a client order or, in general, of B2B turnover.
The budget for sales of B2B services is set through the turnover expressed in thousands of rubles and the percentage of commission or agency fee (B2B commission).
The calculation of the procurement budget in the financial model is made in the "calculations_daily" tab using the following technology. In accordance with the daily distribution of sales, which the user sets, as indicated above, in the “conditions” tab, the distribution by days of the budget year occurs in the “calculations_daily” tab of the general sales plan for all selected business areas. After that, the cost price is calculated for each day. Next, against each daily cost amount, using the formulas of the financial model, taking into account the data entered by the user on sales turnover periods, purchase dates are put down. Summing up the cost of sales for budget periods (quarters or months of the budget year) with respect to these purchase dates, we obtain a procurement plan.
To understand this uncomplicated technology, let's compare our method of calculating the procurement budget with the classical method presented in "all books" on this topic, the essence of which is that, first, starting from the volume of commodity stocks at the beginning of the Period (we denote TK(0)), through cost of the approved sales budget of the Period and specified in the financial model inventory turnover ratio ObTR (not to be confused with the turnover period!) Calculate the inventory balances at the end of the Period (let's designate TK(1)) using the formula:
TK(1) = 2 * C / ObTK - TK(0).
After that, the SF(+) procurement budget for the Period is calculated using the following formula:
SF(+) = TK(1) + C - TK(0).
Everything looks very logical, unless you go into the meaning of the "classic" formula for calculating the inventory turnover ratio or the formula for calculating the turnover period P (ObTR) in days through the turnover ratio of ObTR for the Period:
P(ObT) = (number of days of the Period) / ObT =
= (number of days of the Period) / [ C / (TS(0) + TS(1)) / 2].
Consider a simple example, similar to those that we have repeatedly offered to attention on the pages of our site. Suppose we bought a product for 100 rubles. July 31st and sold it on August 2nd. Then if we take August as our Period, then
TK(0) = 100 rub.,
TK(1) = 0 rub.,
C \u003d 100 rubles,
P(ObTZ) \u003d 31 days / [ 100 rubles. / (100 rubles + 0 rubles) / 2] = 15.5 days.
That is, the classic formula presented in all textbooks tells us that the inventory turnover period in our case is a little more than 15 days, but after all, we physically had only two days of goods!
The main conclusion is this: in real practice, similar formulas are not applicable if you want to receive reliable, correct results. At the end of the section, we will once again return to the discussion of this problem, where we nevertheless explain the meaning of these well-known formulas.
The difference between our approach from the "classical" one to financial modeling is that we use the direct method of budgeting, for example, as in this case, the procurement budget, the meaning of which is that all typical transactions are distributed by dates of the budget period. If, as part of the daily distribution of the sales plan of our financial model, we plan to sell electronics for 100 rubles on August 16th. in the cost price and at the same time in terms of inventory turnover for August, a planned turnover period for the category "Electronics" in the amount of 15 days is included, then, accordingly, the amount of 100 rubles will appear in the procurement budget on August 1st. Further, collecting all planned purchases by days of the Period under consideration, we obtain the purchase budget of the Period.
Let's continue. Should be paid Special attention on the fact that purchases are usually shifted in time in the past relative to sales of the budget period within which sales are planned. Therefore, when generating a report on the movement of inventory, the balance of inventory at the beginning of the budget year does not have to be zero, which also means that a number of transactions, in this case, the purchase of goods, do not necessarily all fall within the framework of the budget period under consideration, but nevertheless relate specifically to him. We will talk more about this when we touch on the differences between the functional and marginal approach to management and reporting systems.
Looking ahead, we only note that the result of the functional income statement (functional P&L) for the entire budget year, in general, does not coincide with the increase in equity in the forecast balance sheet, but the result of the marginal P&L does. At the same time, in the context of budget periods, the situation is exactly the opposite. And also on the basis of the management marginal income statement, the management system is built more efficiently.
We set the conditions for the return of goods by customers (they have the right by law and this often happens) by entering the percentage of return of goods relative to sales in the context of product categories and the average term for returning goods:
Now we have everything to generate a report on the movement of inventory (Stock Flow) and calculate the turnover periods for sales and inventory in general:
Note that in this report, inventory balances at the end of the budget year match the total cost of returns. This happened as a result of the fact that we do not set conditions in the financial model for the disposal of returned goods. Partly in practice, such goods make up a significant amount of illiquid stock in the warehouses of trading companies, and usually they get rid of stale goods by conducting all kinds of marketing campaigns with the sale of such goods at large discounts.
In the following retail financial models with the “bottom-up” budgeting principle, we will supplement the financial models with conditions for the disposal of illiquid and stale goods through the formalization of the conditions for conducting marketing campaigns.
Let's move on to the block of variable costs of the financial model. Remembering that we are considering the case of top-down budgeting, we understand that variable costs in this case, they are approved by the owners of the trading enterprise or as a percentage of certain financial indicators sales, or through the average cost per item or order.
In our financial model, we distinguish the following items of variable expenses:
Marketing expenses;
Inbound logistics costs;
Warehouse logistics costs;
Outbound logistics costs;
Call center expenses;
Rent of retail space;
Motivation of commercial personnel;
Variable financial expenses.
Marketing expenses are set in the financial model in two stages: first, the total allocated marketing budget is set as a percentage of sales in money, after which its percentage distribution between all considered sales detail areas is set.
The truth is there is one important nuance– The financial model asks for the marketing expenses turnover period relative to the sales plan. The fact is that usually the effect of various marketing campaigns is not momentary. In practice, a certain amount of time passes from the moment of carrying out certain marketing activities to real sales caused by this particular marketing activity. Just these terms should be included in the terms of the financial model.
There are two options for specifying inbound logistics costs in the financial model. If the user opted out of planning sales in the number of orders, then he will be prompted to enter the approved budget for the costs of incoming logistics, as a percentage of the volume of purchases in money.
In the event that the user is supposed to account for sales in the number of orders, then the costs of incoming logistics are proposed to be calculated according to the following scheme. Since we are in an environment where operating costs are outsourced, this means that our company will need to order vehicles from transport companies to deliver purchased goods / orders from Suppliers. Therefore, to calculate the budget for incoming logistics, the type of vehicle is set in terms of possible transportation volumes and the cost of using the vehicle per day, and then by setting the average volume of one order in the “calculations_daily” tab, the required number of vehicles for each day is calculated. Entering data on the conditions for calculating the budget of incoming logistics looks like this:
In the "calculations_daily" tab, the costs of inbound logistics are related to the dates of the corresponding purchases of goods.
The budgets for warehouse and outbound logistics are set in the same way, with the only exception that when calculating order-by-order budgets, simply the cost per order is set, and outbound logistics is based on the volume of delivered sales, which in turn are also entered on the "conditions" sheet in a separate field.
The call center budget is set as a percentage of sales that require call center services, and entering into the conditions of the financial model the approved spending volumes for each budget period for the call center as a percentage of this sales volume.
In the "calculations_daily" tab, the costs of warehouse logistics and the call center for B2C sales are related to the arithmetic average of the dates between the dates of sale and purchase of the corresponding goods.
Finally, the cost of renting retail space, variable financial costs and the motivation of commercial personnel are set by entering these costs into the financial model conditions as a percentage of sales and gross profit, see downloaded models.
Before moving on to the block of fixed costs, let's look at the methodology for generating a profit and loss statement, or in short - a P&L report. The usual classic scheme for generating this report, it is also called the functional approach, is that all expenses fall within the period by the date of the expense transaction except for cost costs.
Suppose we are planning some promotions for the March 8 holiday, so that on this day the sales volumes are significantly higher than on a normal day. Relevant marketing activities can start up to a month before March 8th, i.e. in February. Then it turns out that, so to speak, in excess of the proceeds of this holiday will be in March, and marketing expenses, which are actually directly related to these sales, will "fall" in February, according to the P&L functional report methodology. As a result, such an income statement is simply a statistical statement of income and expenses, distributed by functional divisions of the company and by period.
By the way, with a functional approach, it is not at all necessary to divide costs into variable and fixed - it does not make sense, it would be more correct if the costs are divided according to the principle of dividing the enterprise into functional units. Although we share, but in the financial model for bottom-up budgeting, we will make a P&L report with a functional breakdown.
On the other hand, the P&L margin report looks at the economy of the enterprise (in the financial model, this is the “mPL” tab). The marginal income statement methodology assumes that variable expenses fall into a period if they are directly related to the revenue of this period, regardless of when (in what periods) they were incurred. With this approach, we see real economy sales and we can compare the periods with each other in order to understand whether the efficiency increases or decreases operating activities and if reduced, within which operating units.
In "advanced" companies, where CFOs convey the meaning of the functional and marginal approach to management to company management, usually management reporting in terms of the income statement, it contains two forms at the same time: functional and marginal. Moreover, usually the P&L margin report in such companies can even have daily detailing (in one of the largest Russian online trading companies, the author created such daily financial statements - this company was one of the first among the top 30 Internet retailers to break even).
Note that in our financial model in the margin P&L report, we do not take into account returns of goods.
Also, with a marginal approach, it makes sense in the concept of effective margin, see the "Turnover" tab, as well as in the concept of "economy per order", see the "UE" tab.
Fixed costs in our financial model are broken down as follows:
Social fees (PFR, FSS, FFOMS);
Office rent;
IT expenses;
Office expenses;
Household expenses;
Staff costs;
Representation expenses;
Travel expenses;
Fixed financial expenses;
The payroll budget is formed in the financial model through the introduction of a planned staffing, which includes a list of positions, the number of employees and their salaries:
By analogy, percentages of deductions to off-budget funds are set, see financial model.
Finally the rest fixed costs are set either by making a monthly allowance per person, or by directly depositing amounts in the appropriate cells, see financial model. For example, office rent is calculated by introducing a norm for assigning 5 square meters of office space per employee, setting the cost of renting one square meter per month and summation for all employees.
To receive a profit and loss statement, we only need to pay the value added tax (VAT) rate. In our case, we propose to introduce two rates into the financial model: for outgoing VAT and for incoming VAT, since, for example, sales can always go at a rate of 18%, but purchases are most often mixed, so in the financial model we filled out, we entered the incoming VAT rate. VAT at the rate of 17%.
Having entered all the necessary data in the “conditions” tab, our financial model will automatically calculate and generate a profit and loss statement, see the “PL” tab.
Earlier in this section, we already introduced the format detailed report P&L of our financial model, respectively, here we present its "folded" version:
For the convenience of using the financial model, in the "conditions" tab, the first six lines display the values of the main income and expenditure indicators, which automatically change depending on the conditions you specify in the same tab.
This allows you not to jump every time to the “PL” tab in order to see what result you get after you make the next changes to the initial conditions.
Since our presentation has already turned out to be quite tedious and cumbersome, we suggest that the reader, after our next hopefully short notes on cash flow planning and the formation of a forecast balance, ask specific questions on the methodology and technical nuances of financial models through the feedback form, and we, in our In turn, we will post interesting questions and answers to them, which we hope will complement the current description of the financial model with specifics.
The conditions that determine the cash flow are entered in the "CF-conditions" tab. It is necessary that for each income-expenditure operation taken into account in the “calculations_daily” tab and then included in the P&L report, the order of its payment should be determined. The order of payment for one or another action is understood as the distribution of shares of payment over time. For example, we buy a batch of goods from a supplier with a total value of 100 rubles. on the following terms of payment: an advance payment of 30% 15 days before shipment and an additional payment or full payment 45 days after shipment in the amount of 70%.
For example, the period during which the proceeds received after the sale of an order to a customer is credited to the current account in the form of cash is called the receivables turnover period. Accordingly, in order for the financial model to calculate the cash flow plan (CS) as part of the formation of a forecast cash flow statement (CFD) or, as it is also called, Cash Flow, it is necessary to manually set the receivables turnover period.
Entering the receivables turnover period for B2C trading is quite understandable - the number of days is set for each combination of sales details from the moment the goods are sold to the client until the receipt of the DS on the company's settlement account - usually it takes one or two days, depending on the time of day and the quickness of the collection service .
The receipt of commissions or agency fees within the framework of the sales of B2B services usually occurs as a cumulative total for the reporting month no later than some date of the month following the reporting month specified in contracts with suppliers. It is these parameters that the financial model asks the user to enter in the “CF_conditions” tab:
Here, the numbering of months starts from zero - if zero, then this is the reporting month, if the first, then this is the month following the reporting month, etc. If we consider online sales of B2B services in our example, then “one” opposite “No. month” means that the agency fee for the current reporting month will be received in the form of DS for the next month, and to be precise, on the 20th next month behind the reporting one, as indicated by the number 20 opposite "number_months".
The accounts payable turnover period or, which is the same, the terms of payment for the purchase of goods and expenses in terms of payments for goods and marketing conditions are set by entering into the tab "CF_conditions" the average percentage of prepayments / additional payments and terms in days relative to the time of the purchase or accrual of expenses. Moreover, the periods of turnover of prepayments are entered with a minus sign, and surcharges - with a plus sign.
The terms of payment for other expenses, except for the payment of the payroll, are set by entering the number of the month of payment, where zero is the reporting month, and the day of the month of payment. After that, in the "calculations_daily" tab, all these conditions are processed and summarized in the DDS report in the "CF" tab.
Payroll payments are set through the percentage of the advance payment and payroll payment to employees, as well as through the numbers of months and days of the advance payment and additional payment - in our case, the advance payment is 60% and is paid on the 25th of the reporting month, respectively, a full settlement with employees in the amount of 40% of Payroll is made on the 10th day of the month following the reporting one.
So, entering at the very beginning of the “CF_conditions” tab the initial capital of the budget year, as the amount of cash remaining from previous periods, we get a cash flow report (Cash Flow report), which is located and automatically generated in the “CF” tab of our financial model:
The forecast balance, placed in the “BS” tab, from the point of view of its formation, is perhaps the easiest report, but from the point of view of controlling the reliability of the financial model, it is the most important.
Why the easiest to form? The fact is that our financial models are arranged in such a way that they most directly distribute all typical transactions (purchase, sale, payments, receipts of DS, etc.) in the form of daily flows that permeate the entire budget year. For example, if we want to know the accounts receivable of customers for payment or accounts payable in the form of underdelivery of goods to them on an arbitrary date of the budget year (not necessarily at the end of the month or quarter), then we simply compare the sales volumes to customers and the volumes of receipts from them DS for the entire budget year up to of the selected date and if the sales turned out to be more than payments, then an accounts receivable has formed, and if vice versa, then accounts payable, i.e. We have received advances from clients.
Why the most important? Control in the form of the main balance sheet equality of assets and liabilities will not converge if any types of errors are made in the financial model both in the financial model formulas - errors of a technical nature, and in the methodology for calculating certain financial and economic indicators - methodological errors.
In our balance sheet assets, only current assets(at the beginning of the section, we said that in the financial models of this section we omitted the accounting of non-current assets, such as, for example, fixed assets, so as not to complicate the models) divided into the following articles:
Cash;
commodity stocks;
Accounts receivable;
Moreover, receivables, in turn, are divided into debts of customers for payment and advances issued to suppliers of goods and services.
Forecast balance liabilities are divided into items:
Initial capital;
Retained earnings/loss;
Accounts payable;
VAT debt.
Moreover, accounts payable, in turn, are divided into advances received from customers, debt on payment to suppliers of goods and services, payroll debt to employees and debt to off-budget funds upon payment social fees.
The Balance Sheet looks like this:
Let's pay attention to the line with control, located under the line "retained earnings / loss". The formula for this control is general view has the following form;
EBITDA margin P&L
In our case, when the marginal P&L is formed without taking into account the returns of goods, the control formula is as follows:
Retained earnings/loss at the end of the fiscal year
EBITDA margin P&L
Gross profit by returns
This is exactly what we talked about above about the difference between functional and marginal P&L.
In conclusion, we note the following. Of course, top-down budgeting has the right to exist, but still, the formation of plans and forecasts based on marketing analysis of such economic entities How
Market volume,
The volume and structure of channels of probable traffic, taking into account the competitive environment,
conversion levels,
Levels of average checks or purchasing power of potential customers,
Reasons for not buying;
return of customers;
It represents a more fundamental study of the company's capabilities, and hence the formation on this basis of much more responsible budgets.
The next financial model of retail that we will lay out, apparently, is not in the next section 16 (there we will lay out the basics investment analysis, which has been promised for a long time), but still in the 17th, it will be built on the principle of bottom-up budgeting with a marketing calculation of the sales budget, based on these same “entities”, the list of which is presented a little higher. Also in this financial model there will be a minimum of outsourcing - it will be assumed that most of the operating units are located within the company.
And for current models - download, ask questions, and we will definitely answer them. I wish you success!
In the section on calculating the procurement budget, we have already touched on the issue of comparing our direct method of financial modeling with the classical approach to creating financial models based on the use of turnover ratios. Let's continue this topic here, so that finally "dot the i".
By analogy with the above calculation of inventory balances at the end of the Period through the inventory turnover ratio, it is possible to calculate receivables and payables through the accounts receivable and accounts payable turnover ratios set for the period under consideration. accounts payable, the formulas for calculating these coefficients can be viewed. For example, for receivables at the end of the Period, the calculation looks like this:
DZ(1) = 2 * S / ObDZ - DZ(0),
DZ(1) - accounts receivable at the end of the Period;
S - sales budget for the Period;
ObDZ - planned receivables turnover ratio;
DZ(0) - the volume of receivables at the beginning of the period.
Hypothetically, knowing the volume of sales for the Period, as well as the volumes of receivables at the beginning and end of the Period, it is possible to calculate the volume of CF (+) DS receipts for the Period:
CF(+) = DZ(0) + S - DZ(1).
But at the same time, it is necessary to take into account advances, as well as the fact that receivables, in the general case, are not only debts on receipts of VA from customers for goods sold by us, but also debts on shipments of goods from suppliers.
All of the above is similarly applicable to accounts payable. Let's give one more similar example of application of "beautiful" formulas. We will talk about the so-called indirect method of calculating the cash flow (Cash Flow). Let's pay attention to the following. If we have a balance at the beginning of a Period:
Assets(0) = DS(0) + TK(0) + DS(0);
Liabilities(0) = SC(0) + KZ(0),
where all rows are known
and there is a balance at the end of the Period:
Assets(1) = DS(1) + TK(1) + DS(1);
Liabilities(1) = SC(1) + KZ(1),
where only the volumes of funds of DS(1) and equity of SC(1) are unknown, since we “calculated” the volumes at the end of the Period of commodity balances of TS(1), as well as receivables of DS(1) and accounts payable of SC(1) through turnover ratios. Then, obviously, subtracting line by line from the balance at the end of the Period, the balance at the beginning of the Period should remain the main balance equality:
D(Assets) = Assets(1) - Assets(0) = Liabilities(1) - Liabilities(0) = D(Liability),
where through D we denote "delta" or the difference between the values of the indicator at the end and beginning of the Period. Expanding this equality by balance sheet items, we obtain the identity:
D(DS) + D(TK) + D(DZ) = D(SK) + D(KZ),
elementarily transforming which, we obtain the formula for the indirect method of calculating the Cash Flow financial flow (recall that the financial flow of the Period is the difference between the inflow and outflow of DS for the Period, see for more details, which is also equal to our delta D(DS)):
D(DS) = D(SK) + D(KZ) - D(TK) - D(DZ).
Finally, given the fact that in our case, the change in equity is equal to the total of the income statement (functional P&L statement) or equal to EBITDA:
D(SK) = EBITDA,
we obtain the final form of the formula for indirect calculation of the financial flow for the Period:
CF = D(DS) = EBITDA + D(KZ) - D(TK) - D(DZ).
In general, the formula of the indirect method is similar to the one given here, only it additionally contains changes in the remaining lines of the balance sheet and the remaining terms from the lines of the full income statement (P&L), from which the essence does not change. By the way, we did not take into account VAT, simply so as not to overload the presentation of calculations. In fact, the formula of the indirect method is the usual balance sheet identity, presented in a slightly different form, and if it converges in the financial model, no matter what principle this model is built on, i.e. assets equaled liabilities, then, other things being equal, a certain level of financial and economic correctness is already present in the model.
Here is the arithmetic!
Now, once again, we recall numerous examples of the fact that the formulas for turnover ratios are far from real life and come to the appropriate conclusions. And of course, then the question arises: why are they (these formulas) needed?
The fact is that initially the development of universal financial and economic tools for assessing both current activities and the prospects for the development of existing enterprises and new projects was required in banking and investment sphere, to create conveyor decision-making processes for lending and investing. Moreover, it was necessary to create such tools on the basis of external financial reporting, since, in turn, financial reporting itself is universal and, which is certainly important, must be formed and publicly provided by law. (The bank simply cannot physically "enter" every company that applies to it for a loan in order to analyze the entire primary account and make calculations taking into account each individual specificity.) And, of course, numerous theoretical economists in financial institutions for a couple of decades, the corresponding formulas have been developed, some of which can be viewed on our website at, and some more, namely investment analytics, will soon appear within the next, 16th, page of the "financial management" section.
On the other hand, this is a question of education methodology and writing methodological literature - in order to write a book on financial management, it is always extremely tempting to be able to "immediately embrace everything." After all, the presentation of the material through financial and economic ratios, which can be calculated for any enterprise in the same way through the balance sheet and income statement, turns out to be concise and beautiful, although far from real practice.
In short, if you want to create a reliable financial model of the business of the company of which you are an employee, and therefore you are inside the company and have access to the terms of contracts, to statistics typical operations purchase and sale of goods, accrual of expenses, conditions of inflows and outflows of DS, etc., then it is not reasonable to use methods of calculation through turnover ratios in practice and the use of methods similar to the indirect method of calculating the financial flow is not reasonable.
In conclusion, we will touch on another important issue, namely, the calculation of the coverage of cash gaps. If we look at the cash flow statement (Cash Flow) of our financial model, we see that for the third quarter, the line “Cash balance at the end of the period” has a negative value in the amount of “minus 12 million rubles.” - this is precisely the cash gap or the amount of lack of funds for the implementation of the simulated plans.
Accordingly, we immediately upload for download an EXCEL file with a financial model that calculates cash gap lending:
It's good when, with a monthly or quarterly, as in our case, detailing, the cash gap is clearly visible. But actually it is not necessary. For example, if the financial cycle is significantly less than a month, and everything else is not stable from month to month, then a situation may occur in which a shortage of funds occurs within each month, for example, for several days, but in general for each month the financial the flow is positive and within the framework of the classic format of the Cash Flow report, cash gaps are not clearly detected.
In part, it was precisely this circumstance that led the author at one time to develop financial models based on the methodology of daily detailing, similar to those presented here. With this approach, we need to decompose all inflows and outflows of funds by days of the budget year in the “calculations_daily” sheet of our financial model, as a result of which we will get the daily financial flow as a cumulative total, which in our particular case revealed exactly what we were talking about higher - it turns out that within each quarter there are small periods of cash gaps in terms of the number of days.
We will assume that our trading company has the potential to conclude an agreement with any bank for credit line in the form of an overdraft, with a sufficient limit. The user can set the annual interest rate in the "CF_conditions" tab, we set it at the level of 17%. Usually, no collateral is required for an overdraft, since this is an operational tool for financing the lack of funds for making current payments, but of course it is assumed that the borrower has a “good” financial condition, which you can see, for example.
If the company understands that today it does not have enough funds to pay current payments, it receives these same funds on the same day as part of a bank overdraft. cash, after which, all funds received on the company's current account go in priority to repay the overdraft, and we will assume that interest is first repaid based on the number of days the money is used, and then the body of the loan.
Without going into the subtleties of solving the issues of financing cash gaps, we will only note that such a lending model is the cheapest.
In the “calculations_daily” tab of the financial model, using EXCEL formulas, we implemented all the above conditions for crediting cash gaps according to the overdraft model, as a result of which our Cash Flow report was supplemented with a financial activity block with turnovers for attracting and returning credit funds, and as well as interest payments for the use of borrowed money. Now our Cash Flow looks like this:
From the report we can see that the cash gaps were in every quarter, and not just in the third, so draw conclusions when using classic general schemes calculation of cash gaps in their financial models without deep detailing.
Also, do not forget that now, after accounting for financial activities below EBITDA, it is necessary to add interest on loans, and in our case, as a result of the P&L report, instead of EBITDA we get EBT - Earnings Before Tax or profit before income tax, since we assumed that in current financial models, we will do without fixed assets and depreciation.
Debt on credits and loans;
Debt on the return of the body of loans;
Arrears in payment of interest on loans;
And we reconfigured the formulas for the item “retained earnings / loss” from P&L report EBITDA to EBT. Similarly, we treated the "cash" assets of the balance sheet of our financial model.
This chapter discusses the main components of information support and the assumptions used in forecasting, which together provide the input data for the financial model of the project (see § 11.1; 11.3-11.6), its basic structure and the results derived from it (see § 11.2), as well as the impact of accounting and tax problems per project and financial model (see § 11.7).
This chapter also discusses the use of the financial model by investors in the process of assessing the rate of return on their investments (see § 11.8), by lenders in the process of calculating coverage levels for their loans (see § 11.9), and for the baseline scenario (see § 11.10), and when performing a sensitivity analysis.
It also discusses the ways in which investors determine their return requirements and how they may change over time or as a result of a subsequent sale of an investment or loan restructuring (see § 11.12).
An adequate financial model is a very important tool in the process financial evaluation project. It serves several purposes.
Before all financial documentation is properly executed:
- initial evaluation and subsequent re-evaluation of the financial aspects of the project and sponsors' income during the construction phase;
- formulating financial clauses of project contracts (including use as a bidding model, when calculating the tariff, if sponsors hold an auction for the right to participate in the project, and to control the settlement of liquidated losses, etc.);
- structuring finances and considering the benefits received by sponsors under different financial conditions;
- verification of the conscientiousness of participants in contractual relations, which is carried out by lenders as part of the due diligence procedure;
- in identifying critical issues in the funding negotiation process;
- creating a base scenario (see § 11.10).
After the financial documentation is completed:
- as a budgeting tool;
- as initial assumptions for lenders in the process of considering changes in the long-term prospects of the project and forming their position.
The financial model covers all the activities of the project company, and not just issues related to the project, and therefore takes into account, for example, taxes and issues accounting that can affect the company's bottom line cash flow. While sponsors and lenders can develop separate financial models in parallel, as shown in § 4.1.6, it is often more efficient to create a single model together. This may mean that the sponsors start to develop the model and then the lenders join this work, depending on the point in time when they join the project. Sponsors can then use it to calculate their revenues, taking into account the ownership structure of the project company; the results of such calculations are not related to the activities of lenders.
§ 11.1. Initial data for the model
The assumptions for the financial model of the project company can be classified into five areas:
1) macroeconomic (see § 11.3);
2) project costs and funding structure (see § 11.4);
3) operating income and expenses (see § 11.5);
4) use of the loan and debt service (see § 11.6);
5) taxation and accounting (see § 11.7).
These inputs should be used in drafting project contract clauses, taking into account expected and stated completion dates, payment or revenue schedules, fines and bonuses.
The grounds for the initial data must be recorded; usually, a “set of assumptions” is used for this, which considers each direction of the financial model and indicates the source of initial data or calculations for it with attached documents that are the basis for such conclusions.
These assumptions are used to calculate project cash flow projections (see § 11.2; 11.10), which in turn form the basis for calculating investor returns (see § 11.8) and debt coverage ratios for lenders (see § 11.9) . This model must necessarily calculate an acceptable number of sensitivity scenarios (see § 11.11).
Inputs are usually entered into separate statements (ie a statement for individual assumptions such as project costs, a statement for long-term macroeconomic and operational assumptions that cover the entire life of the project). The initial data should not be chaotic so that it is always possible to understand on the basis of which the corresponding conclusions are drawn.
The financial model should refer to the period as a whole, from the time of the first construction expenditure to the end of the operation of the project, although, from the point of view of lenders, it should cover the period from the date of signing of all financial documents, taking into account past expenditures. The life of the project is determined either by the duration of the project agreement or by the expected economic life of the project if no agreement has been entered into. By the end of the life of the project, the residual value of the entire equity capital of the sponsors is usually assumed to be zero.
As a rule, the model is prepared for 6 months. During the construction period, when there is not enough detailed information (for example, calculation of interest payments, exact payment schedule for the contractor, etc.), individual forecasts can be made for a month and combined into the main model.
§ 11.2. Model Inferences
Model inferences are a series of calculations:
- costs during the construction phase;
- use of own capital;
- use and repayment of the loan;
- interest payments;
- operating expenses and income;
- taxes;
- profit and loss account (profit and loss statement);
- balance sheet;
- cash flow (sources and use of funds);
- lender coverage rates (see § 11.9) and investor returns (see § 11.8).
The summary sheet usually presents key results on one page:
- a summary of project costs and sources of funding;
- total cash flow;
- lender coverage ratios;
- investor returns.
§ 11.3. Macroeconomic Assumptions
Initial macroeconomic assumptions are assumptions that do not directly affect the project, but do affect its financial results. These should include:
- inflation (see § 11.3.1);
- product prices (see § 11.3.2);
- interest rates (see § 11.3.3);
- exchange factors (see § 11.3.4);
- economic growth (see § 11.3.5).
Ideally, macroeconomic assumptions for model forecasting should be taken from objective sources not associated with sponsors. For example, the vast majority of large banks conduct general economic research and receive relevant forecasts that can be used in the process of financial modeling of the project.
§ 11.3.1. Inflation
In the process of financial modeling, inflation must be taken into account, since it can lead to erroneous conclusions in the forecasting process (see § 8.1).
It may be necessary to use different indices as the basis for forecasting the inflation rate when calculating various kinds expenses and income, for example:
- the consumer price index in the country where the project is located, when calculating the total operating costs;
- labor cost indices in the country of the service provider for the project when calculating the same costs;
- industrial price inflation when calculating the cost of spare parts;
- special price indexes for goods produced or purchased by the project company (demand and supply of goods in its own market may affect the price to a greater extent than general inflation).
Care should be taken to avoid using a higher inflation rate in the calculation of income than in the calculation of expenditures.
If the project company has signed a project agreement in which revenues are indexed to inflation (see § 5.1.6), then the financial model should also reflect this fact.
§ 11.3.2. Product prices
As a rule, it is impossible to treat prices in the same way as inflation (that is, to assume that they will continue to rise). The dependence of the project on the cyclical nature of commodity prices, which is characteristic of most goods, must be considered in the process of financial modeling.
A key problem with project finance is that very often a project is developed at a time when prices are high and therefore it is assumed that they will last, while underestimating the impact of the project itself and other similar projects on the commodity market. (Or vice versa: a project is developed when fuel or raw material prices are low, and it is assumed that such price levels will continue.)
Changes in commodity prices can be very dramatic in the short term, while project finance is inevitably long term; therefore, it is necessary to demonstrate that the project is robust enough to survive a significant price change (see § 7.8.6).
§ 11.3.3. Interest rates
If the interest rate of a loan is fixed for the entire period (see § 8.1), then assumptions for it should be used when calculating forecast values. However, even in such cases, a different “floating” (short-term) interest rate must be taken into account when forecasting the return on excess capital that the project company uses as collateral for lenders or before payments to investors (see § 12.5.2).
There are two approaches to forecasting short-term interest rates: assumptions can be made directly on the rate itself, or "real" interest rates (after adjusting for inflation) can be used, and the actual interest rate is determined based on the consumer price index rate. In the latter case, as shown in Table. 11.1, if a real interest rate is used, say 4%, then the projected nominal interest rate is the real interest rate adjusted for the inflation rate based on the "Fischer formula".
§ 11.3.4. Exchange rate and currency used in the model
If the project company raises financing for a loan and investment in equity in national currency, receives revenue and bears all expenses in the process of construction and operation of the project in the same currency, then the exchange rate is not necessary to be taken into account.
Otherwise, the financial model must also be prepared for calculations in local currency and be able to accept assumptions in long term regarding changes in the exchange rate of the national currency and other types of currencies used to finance the project. Foreign investors and lenders may find it more beneficial to create a model for their national currency, but it is possible that this may give inaccurate or erroneous results (for example, as a result of the influence of the exchange rate on the amount of taxes paid - see § 11.7.7, or because that some costs must be in the national currency of the country in which the project is located). It is easy for the model to issue a report that translates the local currency forecast results into the corresponding foreign currency; in this way, the accuracy of the calculation is preserved and the way the results are presented becomes more readable.
As with interest rate forecasting, there are two approaches to the currency forecasting process: one can make an ad hoc assumption about future rates, or one can use purchasing power parity rates. In the latter case, the calculation takes into account the difference in the predicted inflation rate for the two currencies and adjusts the exchange rate based on the assumption that it will change in accordance with the inflationary difference (Table 11.2). In year 1, with a 6% difference in inflation rates in favor of currency B, currency A will depreciate by 6% against it, and so on.
| Now | Year 1 | Year 2 | Year 3 | |
| Projected inflation rates,% | ||||
| Currency A | 9 | 10 | 9 | |
| Currency B | 3 | 4 | 3 | |
| Forecast rates: currency A/currency B | 10,00 | 10,60 | 11,24 | 11,80 |
§ 11.3.5. GDP and volume growth
Infrastructure projects may be affected by the overall growth rate of the economy, which will translate into increased use of the product or service (see § 7.8.7). For example, there has been a strong correlation between the long-term growth rate of air travel and GDP growth, with traffic growth at twice the rate of GDP growth. Thus, GDP growth rate assumptions are key for airport projects. A similar approach is applicable for traffic related projects.
§ 11.4. Project costs and funding
The next step in the detailed modeling process is for the project company to prepare a budget for the costs of the construction phase and identify sources of funding.
§ 11.4.1. Project costs
The project cost budget takes into account the costs from the start of construction until the time it is ready for operation. Typical budget for manufacturing enterprise or infrastructure project (see § 7.5.4) is likely to contain the following items:
- development costs. These are the costs incurred by the sponsors (and paid to the project company) or by the project company itself during the financial documentation process. Sponsors need to agree on a distribution methodology own expenses(including staff overheads and travel expenses), which are likely to be significant over a long development period. It is also necessary to take into account the costs associated with the payment of consultants who represent the interests of the sponsors and the project company;
- royalties during development. The project structure may allow one or more sponsors to receive an initial fee from the project company for the development of the project; thus they have the opportunity to profit before the agreements go into effect (see § 11.12.2). These figures are subject to change as the financial estimate of the project changes;
- project company costs. This item includes expenses incurred after the financial documentation is signed and related to:
Paying staff;
Payment for office and equipment;
Payment for permits and licenses;
Payment for the services of an independent engineering company - a technical consultant of the design company (for supervision of the construction process);
Training and recruitment costs (this also includes any O&M contractor fees);
- price of a "through" contract(see § 6.1.4);
- construction insurance(see § 6.6.1);
- start-up costs. These are the costs associated with paying for fuel or raw materials that are necessary for the contractor to test and start the project, before the interested parties acknowledge the fact that the work has been completed; in some projects it is also possible to receive revenue from the sale of products produced during this period of time;
- initial accumulation of spare parts. These are the costs associated with the organization of the initial accumulation of spare parts (if they are not included in the "through" contract);
- working capital. This is the capital required for the project, the amount of money to cover the difference in time between the project company invoicing for the reimbursement of operating costs and the receipt of cash income. In fact, this is a short-term (usually 30-60 days) cash flow cycle of the project, which cannot be directly calculated based on the financial model planned for a 6-month period during the operation stage. Initially, working capital can be calculated as the costs that the project company must incur until it receives the first payments from the proceeds. They may include:
The cost of purchasing an initial supply of fuel or raw materials;
Office and staff expenses;
The cost of the insurance premium paid at the initial stage of operation. Therefore, changes in the amount of working capital required are usually the result of a significant change in the volume of sales or purchases of raw materials, which should be reflected in the overall cash flow;
- taxes. Article includes tax payments for various project costs such as VAT or sales taxes;
- financing costs which include:
Rewards for loan approval and placement;
Expenses associated with the registration of the loan and its security;
Expenses related to the payment of remuneration of consultants who represent the interests of lenders (this includes expenses incurred in the period before the signing of the financial documentation, as well as after it is executed);
Interest payments during the construction period;
commission fees;
Agency fee for the loan;
- financing of reserve accounts.§ 12.5.2 discusses which reserve accounts should be funded as part of the project costs;
- unseen circumstances. The costs associated with unforeseen circumstances (see § 7.5.4) must be taken into account in the costs of the project.
§ 11.4.2. Project funding sources
The financing plan, based on the expenditure plan, includes all sources of full financing, broken down into debt and equity (see Chapter 12; this also includes a calculation of the size of the loan that can be raised).
If a separate source of funding is for specific purposes only (for example, a loan secured by an export credit agency can only be used to cover the costs of an export contract from the country in which the agency is located), then this should be taken into account when making calculations. Thus, if the project costs are 100 c.u. e. include equipment costs of 70 c.u. e. in accordance with the export contract, then a financial plan that uses financing provided by the export credit agency in the amount of 80 c.u. e., and additional funding at 20 a.m. e. will be ineffective.
The project company should not use short term loan as working capital: these are amounts that are needed on an ongoing basis and should be recovered on the basis of long-term project financing. However, it may be useful to obtain part of the project financing in the form of a revolving loan (i.e. the project company has the ability to repay part of the loan when it has excess cash and re-borrow if it is short of cash). This can help to reduce the size of sponsors' equity, and therefore it will also be beneficial.
Separate short-term loans may be required to pay VAT and other taxes during the construction period. They are repaid in the payment of taxes or on account of the proceeds after the operation begins.
§ 11.5. Operating income and expenses
Let's take a manufacturing company as an example. The main elements of operating cash flow may include operating income from the sale of products minus fuel and raw materials costs, the project company’s own operating costs (personnel, office, etc.) (see § 7.7.3), maintenance costs, expenses under an operation and maintenance contract, insurance (see § 6.6.2).
The first step in forecasting operating income and cost of fuel and raw materials using the model is to determine the key operating assumptions - for example, for a manufacturing enterprise:
- what is the initial volume of output;
- how it will change over time;
- how long the maintenance will take;
- what period of time should also be scheduled for unscheduled stops;
- what is the rate of consumption of fuel or raw materials;
- how consumption will change over time.
Revenues from sales and expenses for fuel or raw materials result from:
- those operating assumptions;
- terms of project agreements, such as a contract for the supply of raw materials or a contract for the purchase of products;
- assumptions about market prices in the absence of such contracts.
In addition, it is necessary to take into account forecasts regarding changes in all these indicators.
§ 11.6. Loan and Debt Service Scheme
At the construction stage, the model takes into account:
- the required ratio between equity and debt (see § 12.1);
- any restrictions on the use of the loan (for example, loans issued by an export credit agency are used only for exported equipment, or expenses in a certain currency are financed by loans in the same currency).
After that, the schedule for the use of equity and loan is calculated. The use of a loan allows you to increase the interest payments (during the construction period), which must also be financed. During the operation period, the model takes into account:
- prioritization of the distribution of net operating cash flow (see § 12.5.1);
- allocation of funds to the relevant payment items to repay the debt (see § 12.2.4);
- calculation of interest payments, which allows contracts to be hedged (see § 8.2).
§ 11.7. Problems of accounting and taxation
Although the decision to invest in a project should primarily be based on an estimate of the cash flow (see § 11.8), accounting figures are important for sponsors who do not seek to publicize the accounting losses from investing in the project company they have created. In fact, they may decide to abandon what at first glance seems to be ideal financing and use another (for example, through leasing - see § 2.4) if this provides a higher accounting profit.
Thus, although financial modeling for project finance is considered in terms of cash flow rather than accounting figures, it is usually necessary to attach an accounting statement to the model (i.e., an income statement and balance sheets for each period).
In addition, it is necessary to control the results of accounting for the profits of sponsors. There are many reasons why accounting metrics are important to the financial model of a project company:
- tax payments are calculated on the basis of accounting indicators, and not on the amount of cash flow (see § 11.7.1);
- accounting ratios determine a company's ability to pay dividends (see § 11.7.2) and may affect its ability to continue trading shares (see § 11.7.3);
- having a balance sheet is good way error control in the model: if it does not converge, then an error has crept in somewhere.
§ 11.7.1. Capitalization and amortization of project costs
The most important differences between accounting and project cash flow calculation are determined by the capitalization and subsequent amortization of project costs.
If the project company were to write off the costs of the project at the time of implementation, the result would be huge losses during the construction phase, accompanied by huge profits during the operation phase. Obviously, this does not reflect the real situation.
In most countries, project costs are capitalized (that is, added to balance sheet assets) rather than immediately written off. Costs in this case include not only construction-related costs (i.e. fixed assets), but also variable costs incurred prior to operation (financing and development costs (including interest payments during the construction phase), consultants’ fees). etc.).
Subsequently, capitalized costs are amortized (written off) and deducted from income. Standard straight line accounting depreciation for a project may allow the project company to write off the project asset over a period of, say, 20 years. Thus, the depreciation of the design cost of 1000 c.u. e. should be 5% of its original value (50 c.u.) annually. If this depreciation is covered tax income with a rate of 50%, depreciation compensation will reduce the amount of tax by 25 c.u. e. for 20 years.
The project company can benefit from large initial tax deductions because investments in fixed assets are subject to accelerated tax depreciation. For example, if the tax depreciation rate for project costs is 25% of the current book value(example " accelerated depreciation”, which is a typical investment incentive), this means that the depreciation on an investment of 1000 c.u. e. is:
- year 1: 25% of expenses, i.e. 250 c.u. e.;
- year 2: 25% of expenses, i.e. 188 c.u. e. (minus depreciation in year 1), or a total of 438 c.u. e.;
- year 3: 25% of expenses, i.e. 144 c.u. e. (minus depreciation between years 1 and 2), a total of 578 c.u. e.;
- year 4: 25% of expenses, i.e. 105 c.u. e. (minus depreciation between years 1 and 3), in total 684 c.u. e.;
- year 5: 25% of expenses, i.e. 79 c.u. e. (minus depreciation between years 1 and 4), in total 763 c.u. e., etc.
Thus, in the first 5 years, more than 75% of project costs can be taxed, compared to 25% for a 20-year linear depreciation, which we considered earlier. AT last years the existence of the project, tax payments in the case where accelerated depreciation has been applied increase, since the costs of the project have already been deducted from taxes; therefore, by the end of the 20-year period, the total tax deduction due to the depreciation compensation (at a tax rate of 50%) will be the same (ie CU500).
Another typical case of tax depreciation is "double depreciation" - if the normal depreciation rate of an asset is 10% annually, then double depreciation allows depreciation at a rate of 20% annually for the first 3 years and then at a rate of 10% annually. Thus, by the end of the year 5 80% of the cost of expenses should be written off for taxes.
In some countries (for example, the US and the UK), depreciation is handled differently for tax and accounting purposes: for accounting, a project asset is depreciated over its lifetime, thus relating the costs associated with that asset to the profit it generates. provided, and increases accounting profit in the early years of the project; while tax accounting uses accelerated depreciation.
The difference between these two amounts is credited (or deducted) to the tax reserve in the balance sheet liabilities. In other countries (for example, in Germany and France), accounting and tax depreciation must necessarily be the same.
Different depreciation rates may apply to different parts of a project (for example, buildings and equipment). In such cases, the contractor will be required to allocate lump-sum payments under the "through" contract to these components for the purpose of tax systematization.
§ 11.7.2. Dividend "trap"
“Equity” may not always be provided by sponsors in the form ordinary shares. For tax and accounting purposes, it is often more beneficial for sponsors to provide a portion of it in the form of a subordinated loan, in part because interest payments on this loan can be tax deductible, unlike dividends paid on ordinary shares.
In addition, this approach (making investors' rights dependent on lenders' rights - see § 12.13.5) avoids what is known as the dividend trap, in which the project company has cash flow but cannot pay dividends to its investors from -for the negative balance sheet on profit and loss, as shown in Table. 11.3.
The following assumptions were made in the calculation:
- project costs are 1500 c.u. e., 1200 c.u. e. financed from a loan and 300 from equity;
- income and expenses are constant and amount to 475 and 175 c.u., respectively. e. per year;
- the depreciation charged for project costs is 25% of the residual value (see below);
- accounting depreciation is equal to tax depreciation;
- the tax rate is 30%;
- if the project company bears tax loss, a tax credit of 30% of its amount is carried forward and applied to future tax payments;
- the principal amount of payments on the debt is 200 c.u. e. annually;
- indicators are presented for 6 years, although the project life is much longer.
Calculations show that the project company has a positive cash flow that allows it to make payments to investors from year 1, but it still will not be able to pay dividends because its balance sheet shows negative balance at 75 at. e. in the income statement resulting from accelerated depreciation, which creates an accounting loss in year 1, and only in year 3 it will be eliminated. Thus, the project company cannot pay dividends until this moment. Even by year 6, excess funds cannot be fully paid back to investors and a significant delay in receiving payments significantly reduces their rate of return.
In general terms, the dividend "trap" here is a function of the difference between depreciation and the amount of payments on the principal debt: if the first indicator is significantly higher than the second, then the size of the dividend "trap" increases, otherwise it is eliminated. This problem is less relevant in countries where accounting depreciation is not a mirror image of tax depreciation.
| Year 1 | Year 2 | Year Z | Year 4 | Year 5 | Year 6 | Total | ||
| (a) Income | 475 | 475 | 475 | 475 | 475 | 475 | 2375 | |
| (b) Expenses (including interest) | -175 | -175 | -175 | -175 | -175 | -875 | ||
| (c) Depreciation | -281 | -211 | -158 | -119 | -89 | -1144 | ||
| (d) Taxable income/loss | [(a) + (b) + (c)] | -75 | 19 | 89 | 142 | 181 | 211 | 567 |
| (e) tax credit payable | [(-g) × 30%] | 23 | -6 | -27 | -43 | -54 | -63 | |
| (f) Tax credit used | 6 | 17 | 0 | 0 | 0 | |||
| Tax credit deferred | 23 | 17 | 0 | 0 | 0 | 0 | ||
| (g) Tax payments | [(g) + (e)] | -10 | -43 | -54 | -63 | -170 | ||
| (h) Net income | [(d) - (f)] | -75 | 19 | 79 | 99 | 127 | 148 | 397 |
| (i) Debt payments/ | -200 | -200 | -200 | -200 | -200 | -200 | -1200 | |
| (j) Dividends paid | -23 | -99 | -127 | -148 | -397 | |||
| (k) Cash flow Cash balance |
[(h)-c) + (i) + (k)] | 100 100 | 100 200 | 67 267 | -42 225 | -81 144 | -111 33 | 33 |
|
(l) Accumulated profit at the beginning of the period Accumulated profit at the end of the period |
[(m) + (h) + (k)] | 0 -75 | -75 -56 | -56 0 | 0 0 | 0 0 | 0 | 0 |
If the equity of investors is partially paid in the form of a subordinated loan and reflected in equity, then payments to investors in the first years when dividends cannot be paid can be made in the form of payments of a subordinated loan. In this way, the project company will be able to pay all excess amounts.
The next conclusion that can be drawn from these figures is that tax benefits from rapid depreciation is wasted: a tax credit of 23 at. e. in year 1 may not be fully taxable until year 3. In such a case, the project company may decide:
- do not fully use the fast depreciation compensation (i.e. deduct project costs from taxes at a lower rate), which is allowed in many countries; in this case, there will be no negative balance on the profit and loss account, and therefore it will be possible to pay dividends in the first years;
- use the tax base of the lease (see § 2.3) and pass the depreciation on to the leasing company, which can immediately use the funds, and pass the benefits to the project company in the form of low financing costs.
Other benefits of using subordinated debt rather than equity by shareholders to finance a project company's equity include the ease of returning funds to investors in the event of refinancing and an increase in principal or at later stages of the project when investors may want their money back.
§ 11.7.3. Negative equity
The project company must ensure that by avoiding the dividend trap, it does not fall into a situation of completely eliminating shares from the company's equity. If the majority of a project company's financing comes from a subordinated loan and it incurs significant accounting losses in the early years of the project's existence, this can lead to a complete liquidation of equity. In most countries, the shares of a company with negative equity (which has a share capital less than a negative income statement) are required to be withdrawn from trading and subject to liquidation.
For the case presented in Table. 11.3, if the project costs are 1500 c.u. e. financed with 20% equity (i.e. $300), of which 267 is a subordinated loan and the remaining 33 is equity, the loan must be repaid in the first 3 years, after which dividend payments will begin . The project company incurred an accounting loss of CU75 in year 1. e. (even without taking into account interest payments on a subordinated loan), which significantly exceed the share capital; such a gap should not exist (perhaps in this case the project company should consider options for a lower rate for tax depreciation).
Similar results may also emerge with linear depreciation, but with a lower rate of return in the early years (eg due to high taxes on interest payments with a subordinated loan).
Because low equity is distinguishing feature project financing, it is necessary to carefully control the accounting indicators of the project company in the process of financial modeling. This allows the following condition to be met: even if there is a cash flow, it can be legally paid to investors and the project company's equity capital is positive.
§ 11.7.4. tax payment schedule
Very often, corporate tax payments are transferred at the end of the period, which means that there is a gap between the date of accrual and the actual date of payment. Therefore, the financial model must necessarily show and tax calculations in the income statement, and the amount of payments in cash flow calculations on those dates.
§ 11.7.5. Value Added Tax (VAT)
In some countries (for example, countries of the European Union), VAT on costs associated with the construction of the project will be paid by the project company, but these amounts can be returned when VAT is paid on sales after the start of operation of the project. It is very common for lenders to provide a separate credit for VAT in accordance with short-term financing requirements.
§ 11.7.6. Tax deductions
The project company may be required to deduct local income taxes from the interest payments of lenders who do not reside in the country, or from dividend payments foreign investors. However, lenders are able to recover these amounts when paying taxes on other income; they usually require the project company to bear these costs (see § 8.2.4). Therefore, it may be more beneficial for a company to attract domestic lenders, if possible.
Investors may in some cases be able to recover tax withholdings from their dividends when paying taxes on other income, but if they are not able to do so, then the amount of the deduction should be taken into account when calculating the income on investments in the project, even if it is not reflected in the accounting report of the project company or her cash flow.
§ 11.7.7. Exchange rate and tax
If the project company borrows in a foreign currency, then the change in the exchange rate will have an impact on tax payments and, consequently, on the income of investors, even if revenue and operating expenses are indexed to this currency.
This can be seen from the data presented in Table. 11.4. It also clearly shows why it is necessary to calculate the financial model in the local currency, and not in the foreign currency used in the country where the investors or lenders live.
The investor's income is calculated in US dollars, the project company maintains accounting and calculates taxes in euros. Two calculations are presented: one is based on a financial model calculated in dollars, and the other is based on a calculation in euros. The following assumptions have been made:
- all project costs, revenues and costs (including interest and principal payments) are either denominated or indexed to the dollar; thus, theoretically, the project is not related to the exchange rate;
- initial exchange rate is EUR 1.10 = USD 1. EUR is depreciated by 5% per annum at the start date of the project;
- the project cost is $1,000, which is equivalent to €1,100 at the date of cost incurrence;
- tax depreciation in dollar terms is not $150, as it might seem on the basis of the conclusions of the financial model, but $130. Therefore, the model calculated in dollars does not reflect this and exaggerates the value of cash flow.
| Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Total | ||
| Calculations, USD | ||||||||
| (a) Start-up costs | 1000 | |||||||
| (b) Depreciation | [(a) × 10%] | 100 | 100 | 100 | 100 | 100 | 500 | |
| (c) Tax deductions | [(b) × 30%] | 30 | 30 | 30 | 30 | 30 | 150 | |
|
Settlements, euro | ||||||||
|
(d) Start-up costs |
1100 | |||||||
|
(e) Depreciation | 110 | 110 | 110 | 110 | 110 | |||
| (f) Tax deductions | [(d)×30%] | 33 | 33 | 33 | 33 | 33 | 165 | |
| (g) Exchange rate | 1,10 | 1,16 | 1,21 | 1,27 | 1,34 | 1,40 | ||
| (h) Depreciation cost, USD | [(e)/(g)] | 95 | 91 | 86 | 82 | 78 | 433 | |
| (i) Cost of tax deductions, USD | [(hedgehog)] | 29 | 27 | 26 | 25 | 24 | 130 | |
Thus, in a project that uses foreign currency financing, even if it is fully hedged, it is always necessary to track the change in the exchange rate.
§ 11.7.8. inflation and tax
A project that operates with high inflation and revenues and whose costs are fully indexed to the inflation rate will still not generate income that rises in parallel with the inflation rate, because the tax depreciation is based on the initial costs; this is largely due to factors that affect the project (these are discussed in § 11.7.7).
In some countries, project costs in balance sheet companies will also be revalued at inflation rate before tax depreciation is calculated. Again, this confirms the importance of calculations based on “nominal” rather than “real” cash flow rates for project financing (see § 8.1) (that is, taking into account the impact of various inflationary scenarios).
§ 11.8. Return on equity
The standard calculation of return on equity for project investors is usually based on the calculation of cash flow, taking into account:
- the time of depositing funds. As will be shown in § 12.3, it is likely that there may be some time gap between the date of the official transfer of equity and the actual date of the cash deposit. The vast majority of investors evaluate their profitability based on the transferred funds, and not on the planned investments;
- dividend schedule. What is important is not when the project company makes a profit, but when it is paid to investors in the form of compensation (dividends or interest payments or payments on a subordinated shareholder loan); there may be a significant time gap between these dates (for example, because lenders may require that funds be accumulated in reserve accounts and dividends paid 2 times a year, taking into account the company's financial results for half a year - see § 12.5.3).
In order to measure the return of investors from investments in different periods of time, it is necessary to bring it to a common basis by discounting the calculations. Basically, two interrelated quantities are used: net present value(NPV - see § 11.8.1) of cash flow and an internal rate of return (IRR - see § 11.8.2), which are measured by the value of future earnings, adjusted for the currency at that moment. However, these values must be used with caution (see § 11.8.3), as they can lead to erroneous conclusions if a significant part of the investment is not made in cash (see § 11.8.4).
As shown in § 11.7, companies will also evaluate how their investment in the project is presented in published reports as well as in cash flow calculations.
§ 11.8.1. Net Present Value (NPV)
NPV is the present value of an amount due in the future, adjusted for a discount rate. The formula for calculating NPV is as follows:
where With is the amount of future cash flow, i- percentage or discount rate, n- period number. (The discount rate can be annual or, for example, semi-annual.)
Thus, if the discount rate is 10% annually and the amount expected in one year is $1,000. That is, the NPV for this amount is:
or 909.1 c.u. e. Let's do the opposite: if 909.1 y. e. is the amount of investment for the year at a rate of 10%, 1000 c.u. e. (that is, 909.1 × 1.10) will be paid at the end of the year. NPV for the amount of 1000 c.u. e. when calculating for 2 years and a discount rate of 10%, calculated for half a year (5% for half a year), is:
or 822, at. e.
NPV cash flow determines the present value for future sums of money. It is calculated as follows:
We calculated the amount of net cash flow for each future period (usually for project financing this is half a year); it is discounted to NPV at a rate (it is not necessary to use a formula or spreadsheet to calculate NPV - this can easily be done using a financial calculator or related software).
The application of NPV calculations can be illustrated by comparing the cash flows for two investments, which are presented in Table. 11.5. The initial amounts for each of them are 100 USD. e., the cash flow for 5 years is 1359 c.u. e. and generates income (net initial investment) of 350 c.u. e. Cash flow for each year is discounted to NPV at an annual rate of 10%. “Year 0” is the first day of the project after the investment; the remaining cash flows are shown for subsequent semi-annual intervals.
| Investment A |
Investment B |
||||
| (a) Year | (b) Discount factor [(1 + 0.1) (a) ] | (c) Cash flow | NPV [(c)/(b)] | (d) Cash flow | |
| 0 | 10 000 | –10 000 | –10 000 | –10 000 | |
| 1 | 11 000 | 340 | 309 | 200 | |
| 2 | 12 100 | 305 | 252 | 235 | |
| 3 | 13 310 | 270 | 203 | 270 | |
| 4 | 14 641 | 235 | 161 | 305 | |
| 5 | 16 105 | 200 | 124 | 340 | |
| Total | 350 | 49 | 350 | ||
As you can see, although the undiscounted cash flows are equal, the NPV for investment A is 49 (i.e. discounted cash flows from years 1 to 5 at 1049 cu less than the amount of the initial investment), while for investment B = - 2.
The discount rate used by investors for the project company's equity is the minimum required rate of return, which is usually derived from the investors' cost of capital (see § 11.12.1). If the NPV using this discount rate is a positive number, then the investment meets the minimum requirements; if not, then it is not worth investing. If investors demand a return of at least 10%, then it is quite clear that investment A meets these minimum requirements, since the result is positive, while investment B does not meet them. The calculation of NPV can also be used when choosing a project (but it is necessary to take into account the conclusions presented in § 11.8.3) - it is clear that for the case presented in Table. 11.5, investment A is a more profitable investment option. Such a difference in the calculation of NPV demonstrates the importance of the distribution of cash flows over time.
As will be noted in § 11.9, NPV is also used by lenders when calculating loan coverage ratios.
§ 11.8.2. Internal rate of return (IRR)
The internal rate of return (IRR) measures the rate of return on an investment over its lifetime. This is the discount rate at which the NPV of the cash flow is 0. Thus, in the example presented in Table. 11.5, the IRR for investment A is 12.08%, and for investment B - 9.94%, which once again proves that investment A is more profitable; the calculation can be verified by discounting two cash flows with the appropriate rate (Table 11.6). In the process of calculating the IRR, one must be very careful, such calculations cannot be used if the cash flow is in different periods time can take both positive and negative values, since they can give several answers.
In addition to the IRR for a project company's equity investment, it is also possible to calculate the IRR of the entire project, which is based on the cash flow before debt service payments and equity returns are paid, and which is determined by the return on the required investment (for loan or equity) . Sometimes this operation is carried out at the initial stage of project development in order to test its viability without regard to the specific financial structure. Otherwise, IRR is of limited use in project finance, where the main advantages of using financial leverage in a project with a loan is an opportunity to improve the return on equity. IRR can still be used by investors in a portfolio of projects on balance sheet and project financing to compare options. It can also be used in the calculation of compensation, as it is equivalent to the mixed debt servicing costs and income of the project's equity (see § 5.8.1).
| The end of the year | Investment A |
Investment In |
||
|
Cash flow |
NPV at 12.08% |
Cash flow |
NPV at 9.94% |
|
| 0 | –1000 | –1000 | –1000 | |
| 1 | 340 | 303 | 200 | |
| 2 | 305 | 243 | 235 | |
| 3 | 270 | 192 | 270 | |
| 4 | 235 | 149 | 305 | |
| 5 | 200 | 113 | 340 | |
| Total | 350 | 0 | 350 | |
§ 11.8.3. Using IRR and NPV values in the process of making investment decisions
In the process of making a decision to invest in a project and analyzing the impact of changes in the accepted assumptions on the return on investment, investors consider the values of IRR and NPV. However, when using these quantities, care must be taken to understand how they are calculated. This consideration can be illustrated by the example of two investments, which are presented in Table. 11.7: it is clear that investment D provides better profitability and the NPV value confirms this conclusion, but the IRR values for both investments are the same, since the standard IRR calculation process assumes that the funds withdrawn from the project are refinanced at the IRR rate until the end of the calculation period (thus, as shown in the third column of Table 11.7, if the cash flows for 1, 2, 3 and 4 years are reinvested at 15% annually, then the total amount will reach 2011 c.u. by the end of the 5th year). It should be noted that investment C generates cash flow faster, but the assumption that these funds can be reinvested at 15% is perhaps incorrect, or at least implies double counting the return on investment. Thus, IRR initially overestimates cash flows; lengthening the period leads to an increase in IRR when using a high reinvestment ratio.
| Year | Investment C | Investment D |
Investment C |
| Cash flow | Cash flow | Reinvested annual cash flow at 15% up to year 5 | |
| 0 | –1000 | –1000 | |
| 1 | 298 | 0 | |
| 2 | 298 | 0 | |
| 3 | 298 | 0 | |
| 4 | 298 | 0 | |
| 5 | 298 | 2011 | |
| Total | 492 | 1011 | |
| NPV at 12% | 75 | 141 | |
| IRR,% | 15 | 15 |
There are two ways to account for this type of distortion:
1) modified IRR (MIRR). The MIRR value suggests more low rate reinvestment (i.e. the cost of capital of investors for NPV instead of the rate for IRR) for the funds withdrawn from the project. In this case, the overall picture becomes more realistic. In the example presented in Table. 11.7, if the investment rate is taken as 12%, then MIRR for investment C will decrease to 13%, while for investment D it will certainly remain unchanged;
2) payback period. The IRR analysis ignores the problem of reinvestment, but requires that investments also have maximum period payback (that is, the period of time required to return the initial investment amounts). This somewhat counterbalances the effect of the IRR exaggeration for longer-term cash flows, but the calculation remains an approximation—in particular, it does not take into account returns received after the payback period. However, this approach can be a useful control tool. The payback period for investment C is less than 4 years, for investment D - 5 years. At the same time, in the process of making decisions on new investments, investors also require that the maximum payback period does not exceed a certain value.
Again, when comparing two different projects, the indicators should be comparable, as shown in Table. 11.8. Investment F has a higher NPV than investment E, but only because of volume. Obviously, investments E are more profitable; investment F co stable growth in 1000 u. e. provide lower returns.
§ 11.8.4. Non-cash transactions when investing
Another factor that also has a significant impact on the NPV and IRR values is the actual time of contributions to the equity of the project company (see § 12.3.3). Moreover, if investors commit to invest in equity only if the project company's cash flow is inadequate, the IRR value is not affected in any way (see § 12.3.3).
The values of NPV and IRR reflect the return on cash investments, and not the return on all investments that the investor has risked. Thus, if the project company has significant amounts of unused equity capital, then the NPV and IRR indicators can mislead investors.
In order to take into account unused equity, it is necessary to assume in the process of calculating IRR that this capital is used on the 1st day of the project's life and generates income, equal to the cost investors' capital until it is actually used by the project company. This is a more accurate measure of an investor's risk return.
§ 11.9. Debt coverage rates
The level of debt is determined primarily by forecasts of the project's ability to pay interest and guarantee the return of the principal amount in accordance with the agreed schedule. To assess this strength, lenders calculate coverage rates, as follows:
- the annual debt service coverage rate (see § 11.9.1);
- coverage rates for the lending period (see § 11.9.2);
- the average annual debt service coverage rate and the coverage rate for the period of the loan (see § 11.9.3);
- the coverage rate for the life of the project (see § 11.9.4) and the reserve coverage rate (see § 11.9.5).
These rates for typical project are given in § 11.9.6. It should be noted that none of these can be calculated until the project company is operational, as they reflect the relationship between operating cash flow and the level of debt or amounts required to service it.
§ 11.9.1. Annual Debt Service Coverage Rate
The Annual Debt Servicing Coverage Rate (ADSCR) evaluates the project company's debt service capability and is calculated as follows: operating cash flow of the project for the year(that is, operating revenue minus operating expenses - taking into account the amounts deducted to reserve accounts for conducting Maintenance etc. held for other purposes (see § 12.5.2) and excluding any non-monetary items such as depreciation; it may be similar to EBITDA (earnings before interest, taxes and depreciation) used in corporate finance, but the project's operating cash flow for the year should be based on cash flow, not accounting figures) divided by the amount required to service the debt of the project for the year — that is, payments of interest and principal, excluding amounts from reserve accounts.
Thus, if the operating cash flow for the year is 120. e., interest payments - 55 c.u. e. and payments on the loan - 45 c.u. e., then the rate of coverage of the annual amount of debt service will be: 1.2 / 1 (120 / (55 + 45)) c.u. e.
Typically, the annual debt service coverage rate is calculated in six-month increments as an average annual value. Obviously, it can be calculated only after a year from the start of the operation of the project; however, it may affect the ability to pay dividends (see § 12.5.3), and therefore in the first period it can be calculated for half a year.
In the initial assumptions of the base case (see § 11.10), lenders consider the annual debt service coverage rate for each period and ensure that this rate does not fall below a required minimum. The actual annual debt service coverage rate is reviewed (subject to change in assumptions) after the start of operation of the project (see § 12.5.3).
Different projects determine different minimum annual debt service coverage rates, but the following can be taken as approximations for ordinary projects:
- 1.2/1 for infrastructure projects where there is no risk of use (such as a public hospital or prison);
- 1.3/1 for projects related to the operation of power plants or industrial enterprises in which a contract has been concluded for the sale of manufactured products;
- 1.4/1 for infrastructure projects where there is a risk of use, such as toll roads or public transport projects;
- 1.5/1 for mining projects;
- 2.0/1 for projects related to commercial power plants for which there is no contract for the sale of electricity or a price hedging contract.
Higher coverage rates should be used in projects that have unusual risks or are located in countries with a very low credit rating.
It should be noted that, unlike corporate loans, the cash flow coverage rate for annual interest payments is generally not considered as a significant indicator. This is because corporate loans are very often renewed, while project finance loans must be repaid after a certain period of time; therefore, the project company must necessarily be able to reduce its debt in accordance with the schedule and, in general, payment of interest alone is not considered acceptable.
§ 11.9.2. Coverage rates for the loan period
Lending Period Coverage Rates (LLCR) are calculated in a similar way, but for the entire lending period: projected operating cash flow(calculated in a similar way) from the projected start date of the project to the date when the debt is due, discounted to NPV at the same interest rate that was assumed for the debt (taking into account interest exchanges or other hedging options), divided by the outstanding amount of the debt on the date of settlement minus the balance of reserve accounts, which accumulate amounts to service the debt.
The minimum initial coverage rate for the lending period for the base case is projected to be approximately 10% higher for “standard” projects than for the debt servicing case.
In addition, the rate can be recalculated throughout the life of the project by comparing the projected cash flow for the balance of the debt period with the outstanding debt at the settlement date.
The coverage rate for the period of the loan is a useful indicator in the initial assessment process, which helps to determine whether it will be possible to service the debt as a whole. It is also used in the monitoring process during the loan period, but it is clear that its usefulness is reduced in case of significant changes in the size of the cash flow. In this case, the annual debt service coverage rate may be a more significant indicator of the project company's debt service capability.
§ 11.9.3. Average coverage rates for the annual amount of debt service and the coverage rate for the loan period
If the projected values of the annual debt service coverage rate are constantly at the same level, then the average value will be exactly the same as for the coverage rate for the loan period. However, if it is higher at the initial stage, then the average value will exceed the average coverage rate for the lending period, and vice versa. Thus, the average annual debt service coverage rate as a long-term indicator is sometimes more significant for lenders than the coverage rate for the period of lending; in this case it is likely that minimum requirements will be equivalent to the minimum value for the coverage rate for the loan period.
The average value for the coverage rate for the period of lending (that is, the average value for indicators that are recalculated every 6 months) is also used as a criterion by lenders, although the significance of this indicator is debatable.
§ 11.9.4. Coverage rate for the period of project operation
Lenders also review the project to see if it is possible to repay the loan after what was originally assumed to be the final maturity date if there are difficulties in making payments on time. These additional features known as the “tail” and lenders typically expect cash flow to be generated at least a year or two after the end of the loan term. The tail size calculation can be based on:
- on the general ability of the project company to continue operating the project and thus generate cash after the loan expires (in any case, the life of the project should technically exceed the life of the loan);
- the existence of a contract for the sale of products, a contract for the supply of fuel or raw materials, or a concession agreement, in which there are articles that determine the functioning of the project company.
The cost of such a tail to lenders can be calculated using the Lifetime Coverage Rate (PLCR); in this case, the net cash flow before debt service payments for that period (and not just for the life of the debt, as in the case of calculating the coverage rate) is discounted to its NPV, and this value is divided by the value of the loan outstanding. Obviously, the coverage rate for the entire life of the project will be higher than the coverage rates for the life of the debt; lenders may want the first rate to be 10-15% higher than the minimum rate for the second.
§ 11.9.5. Reserve coverage rate
In mining projects, the lifetime coverage rate (in this case referred to as the reserve coverage rate) becomes more significant due to special requirements for the residual volume of minerals (i.e. proven reserves that can be produced after the period of debt expires - see § 7.9.4).
For guaranteed success, the reserve coverage rate should be 2:1 based on prudent commodity price forecasts by lenders, and obviously not less than 1:1 for the minimum acceptable forecasts.
§ 11.9.6. Calculation of coverage ratios
Table 11.9 shows the coverage rates for a typical project that has:
- annual cash flow before debt service payments is CU220. e.;
- the loan is $1,000. e. and paid in equivalent amounts over 10 years;
- The interest rate on the loan is 10% annually and is equal to the NPV discount rate.
| Year | 0 | |||||||||||
|
Operating cash flow | ||||||||||||
|
NPV of operating cash flow | ||||||||||||
| (in) | Debt payments | |||||||||||
| (G) | Outstanding loan (end of year) | 1000 | ||||||||||
| (e) | Interest payments | |||||||||||
| (e) | Total debt service (c) + (e) | |||||||||||
| Annual (a) / (e) debt service coverage rate (X) | ||||||||||||
| Annual (b) / (d) debt service coverage rate | 1,35 | |||||||||||
| Average annual debt service coverage rate | 1,65 |
Assuming that the project generates an annual profit of $200. e. for the next 3 years after the repayment of the loan (that is, in the period from 11 to 13), then the NPV of the total cash flow for 12 years is 1499 c.u. e. and thus the coverage rate for the entire life of the project will be 1.50:1 (1499:1000).
It is necessary to decide whether to deduct tax payments from net cash flow before paying debt service amounts, especially when calculating the annual debt service coverage rate, since changes in interest payments also affect tax payments. It may be prudent to proceed in this way if there is a significant change in taxes (for example, as a result of the impact of offsets for accelerated tax depreciation) that needs to be taken into account. An argument against is the fact that taxes are paid only after deducting interest expenses, which are not included in the indicators of operating cash flow; in addition, the problem arising from a significant change in the amount of taxes can be solved by placing funds in reserve accounts intended for paying taxes (see § 12.5.2). However, as long as the decision-making process on the level of the rate takes into account whether the amounts of tax deductions are included in it, the choice of option does not play a serious role.
It should be noted that "booking" rates, such as current or quick ratios, are generally not used in project finance (short-term liquidity is provided by creating reserve accounts). The debt/equity ratio used in calculating the level of investment in the project company's equity (see § 12.1.4) is also based on cash injections rather than performance. accounting report.
§ 11.10. Base case and changes in assumptions
Once lenders and sponsors agree that the financial model structure and calculation formulas reflect the specifics of the project and contracts, key assumptions are identified, and the financial structure and timing are agreed and put together (see Chapter 12); the final calculation of the model, taking into account these assumptions, is called the "base case" (base case) or "banking scenario" (banking case). This final settlement is usually carried out immediately prior to the signing of the project financial documentation, so that lenders can verify, using revised assumptions and final project contracts, that the project will be able to provide them with adequate coverage for the loan being made.
But subsequently, the project cannot remain unchanged, and lenders will continue to monitor emerging options. As will be shown below, adverse changes in the annual debt service coverage rate and the coverage rate for the period of the loan may affect the project company's ability to pay dividends to investors (see § 12.5.3) or even result in the project company defaulting on the loan (see § 12.11).
However, if new projections are made during project implementation, someone must decide how to modify the assumptions that have been used up to that point. If the right to make decisions on the admission is given to the project company, then the lenders may disagree with the decision, and vice versa.
There is no standard solution for this problem, but whenever possible one should strive to use objective sources to revise forecasts, for example:
- macroeconomic assumptions (including commodity prices) may be based on an economic review published by one of the lenders or others external source, since it is performed for general purposes, and not for a specific project;
- changes in revenue or other performance assumptions should be based primarily on the actual performance of the project company;
- lenders usually have a voting say in the decision to change assumptions, but where possible, investors should ensure that decisions are sound and based on qualified advice from technical advisors who work on behalf of lenders or their market or insurance advisors, not the right to make the final decision.
§ 11.11. Sensitivity analysis
The financial model must also have some flexibility to allow investors or lenders to calculate the series various options(also known as project development scenarios) that take into account the impact of changes in the key input assumptions for the baseline scenario when the project is initially reviewed. These options may include calculating coverage and yield ratios depending on:
- from budget overruns construction works(usually based on the full use of contingency funding);
- payments for liquidated damages in accordance with the "through" contract, allowing you to compensate for costs as a result of downtime or non-compliance of production indicators with the plan;
- completion of work late (for example, 6 months) without payment of liquidated damages under a "through" contract;
- longer downtime and less workload;
- decrease in sales volumes or volumes of use of the project;
- reduction in the selling price;
- selling prices of goods at the break-even point level;
- higher costs of fuel and raw materials;
- higher operating costs;
- increase in project payments (if they were not fixed);
- changes in exchange rates.
Finally, the sensitivity analysis considers the financial implications of alternative financial and commercial risk options for a project that does not deliver on the expected performance.
Lenders also routinely conduct a “combined critical point analysis” to determine the impact of multiple adverse factors at the same time (for example, construction completed 3 months behind schedule, 10% reduction in sales prices, and 10% increase in downtime). The calculation of the simultaneous impact of several different factors is also called "scenario analysis".
§ 11.12. Investor Analysis
Investors usually aim for some minimum level of equity IRR (see § 11.12.1), which may vary depending on the period they are involved in the project (see § 11.12.2). Resale of shares upon completion of construction and in case of successful operation can give investors who came to the project at the initial stage of its implementation, the possibility of a faster return on investment (see § 11.12.3); also, investors' returns can be increased if the loan is refinanced at this stage (see § 11.12.4).
§ 11.12.1. Investor income
Typically, investors have "barrier rates" for IRR on their stocks; investments for which the IRR is higher are considered eligible. As a rule, "barrier rates" are based on:
- on investors' capital expenditures (based on a combination of equity and debt), which are commonly used as the discount rate in NPV calculations;
- additional returns in excess of the capital costs required to deal with a particular type of risk (e.g. project type, project location, risk hedging under the project agreement, increase or decrease in risk to the investor's portfolio after funds are deposited, etc.).
Establishing a required return according to risk based on the project company's equity IRR can be a cyclical process because equity IRR depends on financial leverage, which in turn depends on risk.
Equity IRR in projects with moderate risk, such as energy projects with an agreement to purchase electricity, or infrastructure projects with limited use risk, will typically be 12-20% (pre-tax and in nominal values, that is, adjusted for inflation in the cash flow forecasting process). This is relatively low compared to returns on other types of new equity investments, and reflects a lower level of risk: in fact, the returns received are similar to returns from a subordinated or mezzanine loan, and not returns on “true” equity.
Market rates for equity IRRs are designed for industries such as power generation and public infrastructure, in which projects are very often proposed to governments or product buyers (see § 3.6).
Investors may require that their investments have a positive NPV and a minimum payback period (see § 11.8.3), and also meet limit value IRR.
§ 11.12.2. Equity investment schedule
Investor requirements for profitability also depend on the moment when they entered the project. They come to projects at different stages of implementation and with different strategies. Any project at different stages of development is characterized by different levels risk (Table 11.10).
* The level of risk depends on the following factors:
If the project develops successfully, then the IRR of equity for new investors gradually decreases.
A sponsor who was present in the project at the development stage and brought in another sponsor to invest in the project's equity near the due date for which all financial documentation is due expects to be compensated for taking the highest risk. This condition can be met if the new sponsor pays bonuses on its shares (the price per share is higher than for the original sponsor) or makes a loan to the original sponsor with a theoretical high stake the amount already spent on the project. This fact is taken into account when calculating the share of organizational expenses of each sponsor and the distribution of shares among them, taking into account the funds spent.
In addition, the initial sponsor may withdraw money from the project as a result of the project company's application fee, which is usually paid at the time the financial documentation is signed. In fact, it will be an early return on investment, which is partly funded by the lenders as part of the development costs of the project. Thus, the registration fee can be used as an alternative option for compensation by one sponsor to another of the risk associated with the development of the project. The amount of organizational fees can be challenged by lenders, but may also be acceptable to them, if at the same time cash investment the original sponsor will not be substantially below the acceptable level; it is obvious that the increase in the size of the loan should be acceptable to the project.
§ 11.12.3. Share resale effect
Another investor may not be willing or able to bear high level costs and risks associated with the project during the design and construction phases, but may purchase shares in the project company from the original sponsors after construction has been completed and successful operations have commenced at a higher price that reflects the lower rate of internal rate of return, now considered acceptable as the degree of risk has been reduced.
Selling some or all of the equity investment after the project has gone live offers sponsors or other initial investors the chance to significantly improve their stock returns from their original projections. Indeed, the achievement of the investment target for some investors, such as project finance funds, will depend on the profitability of selling their shares at this stage.
The benefits derived from such a sale are presented in Table. 11.11 for a project that has:
- expenses amount to 570 c.u. e.;
- construction period: 2 years, half of the cost is paid on the 1st day, the balance is calculated at the end of each following year;
- financing: 85% of debt for 15% of equity used pro rata during the construction period;
- net income: 75 c.u. e. per year until the required debt service amounts are paid, the project is designed for 20 years;
- debt servicing: debt is paid on a rent basis (see § 12.2.3) for the first 15 years of operation (that is, by the 17th year of the project) from interest rate 7% annually (note that interest payments during the construction period are added to the debt and financed as part of the project costs, the impact of taxes is not taken into account, figures are rounded to whole numbers).
The data presented in table. 11.11 indicate that the internal rate of return for investors' initial equity capital was 18%; shows the result of the sale of shares at the end of the second year of operation to a buyer willing to accept a lower IRR of 15%, reflecting the lower risk inherent in successful project. Purchase of shares in the project company for 130 USD. e. provides 15% IRR for the buyer; this sale raises the IRR for the original investors to 25% and generates a profit of $43 on an initial equity investment of $87. e. (Although the IRR for the original investors has improved significantly, some of the project's benefit from deferred income has been lost.)
Unplanned profits for this type of investor may create problems with the buyer of the product or contract partner under the project agreement (see § 5.9.2), and the sale of sponsor shares at this stage should be agreed with the lenders (see § 3.1).
| Building | Exploitation | |||||||||||
| Year | 0 | 1 | 2 | ... | 18 | 19 | ... | 22 | ||||
|
(1) Initial Project Financing | ||||||||||||
| (a) Project costs (including interest payments during the construction phase) | -190 | -190 | -190 | |||||||||
| (b) Net income | ... | 70 | 70 | ... | ||||||||
| (c) Use of debt/payments | 162 | 162 | 162 | ... | ... | |||||||
| Net cash flow (a) + (b) + (c) | -29 | -29 | -29 | ... | 70 | 70 | ... | 70 | ||||
|
(2) Sale at the end of the 2nd year of operation (Year 1 of the Project) | ||||||||||||
| (d) Position of the original investor | ||||||||||||
| Project cash flow | -29 | -29 | -29 | |||||||||
| Sale | ||||||||||||
| Net cash flow | -29 | -29 | -29 | |||||||||
| Equity IRR = 25% | ||||||||||||
| (e) Position of the new investor | ||||||||||||
| Purchase | ||||||||||||
|
Project cash flow | ... | 70 | ... | 70 | ||||||||
| Net cash flow | ... | 70 | 70 | ... | 70 | |||||||
| Equity IRR = 15% | ||||||||||||
| Building | Exploitation | |||||||||||
| Year | 0 | ... | 18 | 19 | ... | 22 | ||||||
| (1) Initial Project Financing | ||||||||||||
|
(b) Net income |
... | 70 | 70 | ... | 70 | |||||||
|
(c) Use of debt | ||||||||||||
|
(d) Debt repayment | -22 | ... | -50 | |||||||||
| (e) Outstanding debt at the end of the year [(e) previous year + (c) + (d)] | 162 | ... | ||||||||||
| ... | ||||||||||||
|
(g) Debt service [(e)+(f)] | ... | |||||||||||
| ... | ... | |||||||||||
| ... | ||||||||||||
|
Coverage rate for the entire period | ||||||||||||
|
project operation at the end of the year 4 | ||||||||||||
|
Equity IRR = 18% | ||||||||||||
|
(2) Refinancing | ||||||||||||
|
(a) Project costs including interest during the construction phase | ||||||||||||
|
(b) Net income | ... | 70 | ... | 70 | ||||||||
| (c) Use of debt | 162 | |||||||||||
|
(d) Debt repayment | ... | ... | ||||||||||
| (e) Outstanding debt at end of debt [(e) previous year + (c) + (d)] | 162 | ... | ... | |||||||||
|
(f) Interest payments [(e) at 7%] | ... | ... | ||||||||||
|
(g) Debt service [(e) + (f)] | ... | ... | ||||||||||
|
(h) Net cash flow [(a) + (b) + (c) + (g)] | ... | ... | ||||||||||
|
Debt Coverage Rate [(b)/(g)] | ... | ... | ||||||||||
|
Coverage rate for the entire period of operation of the project at the end of the year 4 | ||||||||||||
|
Equity IRR = 24% | ||||||||||||
§ 11.12.4. Benefits of refinancing
The data in table 11.12 are presented for the same project as in table. 11.11, but the profit received from debt refinancing in the 2nd year of operation (in the 4th year of existence) is considered. Refinancing extends the loan repayment period by 2 years and also increases the amount outstanding loan by the end of the 4th year at 125 c.u. e.
Refinancing is based on the assumption that, at this stage, lenders are satisfied with the terms for the next 2 years of the project's existence, with a lower annual debt service coverage rate (and lifetime coverage rate) for future periods of 1.25 (and a coverage rate of for the entire life of the project, reduced to 1.38 compared to the end of the year 2). As a result, investors will receive 125 USD. i.e. in year 4 and thus recover all of their initial equity investment at that date and increase their total IRR to 24%. (These calculations do not take into account the amount of fees, as well as legal and other costs associated with the refinancing itself, which can be 1-1.5% of the refinancing amount.)
However, refinancing may create prerequisites for problems with the purchaser of the product or the contract partner under the project agreement (see § 5.9.1). In addition, appropriate items must be included in the lending documentation to enable refinancing (see § 12.6.3).
A financial model is a special document that contains the calculation of certain financial indicators of a company based on information about the projected and planned costs. The main task of this model is to assess the efficiency of the use of available resources.
Based on practice, the financial model includes the calculation of the organization's revenue, taking into account the cost and physical parameters of sales, as well as purchases, product costs, production volumes, other income and expenses, investments, company obligations and cash flows. The final stage of building this model is the formation of budget revenues and expenditures. The purpose of the work carried out is considered to be the determination of the values of the change in the enterprise for any dynamics of the parameters involved in these calculations.
The financial model is based on such a key principle as the definition of barrier capital. In other words, the identification of the minimum level of return on investment should be provided by a group of managers in the enterprise. It is its identification that will help to clearly formulate the requirements for the result.
The financial model is based on another principle - focusing the analysis on the level of liquidity of the company's economic activity. This concept is directly related to focusing on the value of the business for the founders.
The financial model of an enterprise can be defined as a simplified mathematical representation of the real financial side of the company's economic activity.
This definition of a model means that, with its help, management attempts to represent the complex nature of a certain financial situation or a set of certain relationships in the form of simplified mathematical equations.
Financial model, like any economic category, has its purpose, which is to assist the head of the company in making a decision. The purpose of such modeling can be considered in more detail when examining some such simple samples as estimates, and analyzing the magnitude of production volume and profit.
As mentioned above, the financial model provides guidance on the necessary analytical information used as the basis for making more informed decisions. Said information can be analyzed under two headings:
1. Achievement of the goal. Using a financial model, the manager includes some data in an analytical image and, thus, receives an answer whether the results will contribute to the achievement of the company's goal. For example, for a manufacturing enterprise - profit maximization.
2. This is a fairly important element of the decision-making process, contributing to the instantaneous decision.
It should be noted that the financial model is closely related only to the quantitative side of decisions. When making the right decision, qualitative aspects should also be taken into account, which are no less important than quantitative ones.
