Example database "Polyclinic" (Access)
Database structure
In the example, a very simple structure Database:
- table of doctors (Doctors);
- table of clients (Patients);
- table of visits by clients to doctors (Visits).
Download sample database "Polyclinic"(further examples will be offered with changes, but for learning how to work with a database, it is extremely useful to have the original version, not cluttered with objects (tables, queries ...)).
The first two tables are independent of each other, filled in advance or as needed. They also serve as entry sources for the hits table. The ID of the third table serves as the number for the ticket to the doctor.
Database objects
In addition to the tables listed above, the database has two forms(main and for filling in two tables - doctors and patients). Also in the database is one report- coupons to the doctor.
Working with the database "Polyclinic"
Before you can prescribe a ticket to a specific doctor for a specific patient (Visits table), you must have this patient and doctor in the Patients and Doctors tables, respectively. The main form includes a table where the patient's visit to the doctor is entered, but it is not updated automatically. To see the newly "listed" doctor or patient in the drop-down lists, you must close and reopen the main form. In fact, it is not necessary to enter data in the main form. After the database has been specified the relationships between the tables when creating the "doctor" record or the "patient" record, we are able to create a "visit" in the same table (note the "doctor" or "patient" appearing on the left after creating the record cross, by clicking on which you will see a sub-table of visits to the doctor; the ticket number (visitID) will be generated automatically and it will be unique). In order for this to happen "by itself", we combined the tables, or rather, established links between the fields of the table. Access allows you to link tables together easily and naturally.
Table relationships in a database
Namely: the visitDoctor field selects a record from the Doctors table, and the visitPatient field selects a record from the Patients table. To link tables, you need to find the icon on the panel indicating table links (circled in red) or right-click on any free space database window and select "Relationships".
Here's what it looks like: 
For practice, you can delete and create a link. To do this, right-click on the line denoting the relationship of tables, select "Delete" ("Delete") (the tables themselves or forms that use one of the tables in reports must be closed). Now let's create a link: click on the key field with the cursor patientID tables Patients(if you have deleted the connection with this table), without releasing the mouse, drag it to the field visitPatient tables Visits, when the cursor changes, release and work with the appeared dialog box Access (check the box as in the picture): 
Calculation of the cost of services provided in the database "Polyclinic"
The first solution that comes to mind is to add a field to the doctors table in which each doctor can compare the cost of his services. But if you think a little, it will be clear that the cost can change over time, and it is desirable for us to keep prices for different periods, at least for statistics. So, we need to create a new table in our database. Structure of the VisitCosts table. In addition to the visitCostID key field, we will at least need the following fields:
- visitCostDoctorID - "foreign key" (doctors)
- visitCostFrom - date, With
- visitCostTill - date, before which the cost of admission is valid
- visitCostValue - the cost of admission in the selected period.
Now we will create a form for working with this table (frmMoney, we will collect other financial information related to the clinic in this form) (we can, of course, fill in this table directly, but it is more convenient to work with it as a subordinate one).
Programming environment: Delphi 7.0
Job title: AIS "Analysis of the financial condition of the enterprise" (ADO + Access)
Type of work: Diploma work (VKR)
Topic of work: Database
Program scope: 7 (on a ten-point scale)
Difficulty level: 6 (on a ten-point scale)
Keywords: ais, analysis, finance, state, enterprises, firms, balance sheet, opiu, profit, loss, revenue, income, expense, forms, accountant, accounting, tax, liquid, solvent, asset, liability, main, funds, credit, debit, loan, stock, costs, turnover, assets, profitable, payback, capital, debtor, cost
Program functions: The data in the program was updated in February 2014!!! AIS provides the following main functions:
AIS "Analysis of the financial condition of the enterprise"
- adding, editing and deleting Form No. 1 (Balance Sheet) and Form No. 2 (Profit and Loss Statement) of the enterprise for any quarter of any year;
- to ensure the change in the composition of the lines of Forms No. 1 and No. 2 with possible changes to the existing tax and accounting legislation;
- formation of the following analytical comparative reports according to Forms No. 1 and No. 2 for any 3 selected quarters:
- absolute and relative liquidity and solvency ratios;
- absolute and relative indicators of financial stability;
- indicators of profitability and business activity of the enterprise;
- consolidated comparative analytical balance of the enterprise;
- analysis of profit (loss) of the enterprise.
The database is implemented in MS Access format. Access to data is carried out using ADO technology.
All reports are generated in MS Excel. Output templates can be modified as desired.
The program is accompanied by a report containing:
- a detailed statement of the problem with a description and interpretation of the formulas of all economic indicators used in the work;
- infoological design using the ERWin tool (2 diagrams), Full description all tables and fields of database tables.
Forms of the Balance Sheet and Profit and Loss Statement are valid for reporting up to the 3rd quarter of 2012 inclusive. Starting from the 4th quarter of 2012, reporting is provided in new forms (for the period 2012-2014, changes were made to the content and codes of reporting lines several times). Therefore, reports up to the 3rd quarter of 2012 can be entered into the program directly from the financial statements. The reporting of the following periods (for example, for 2013) can be entered into the program only after some transformation (excluding some lines in the financial statements). Thus, the program is suitable for delivery if the relevance of boo. forms are not important.
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, when shipping finished products to the customer, trusting him at least on the basis of a signed contract, he hopes that payment will arrive on time, according to the deferred payment signed in the same contract. 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?
AT 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 note right away that there can be several types of financial models of the same business, depending on internal system company management and business organization principles.
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 may have own service delivery, own warehouse, own call center, etc., 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 an 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 investment projects in EXCEL with calculations of investment indicators such as NPV, which can also be downloaded and .
Therefore, in order to bring more certainty and be as close to practice as possible, we will begin by describing the methodology of the simplest retail financial model, namely, a retail financial model with a top-down budgeting system (Top-Down) and outsourcing of the main operating units, such as customer service (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.
We immediately lay out this 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 lower. 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 site, we will present to the reader 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, 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. In this version, there is no balance sheet (Balance Sheet), a tab with a job of financial conditions (“CF_conditions”) of cash flow and, accordingly, a cash flow report (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 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 EXCEL concepts as a sheet / tab, cell, term, column, formula, etc. And in this regard, for a better understanding of what is at stake, the reader is invited to systematically look 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 left upper corner 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 more technical form. This tab contains full information only in the FULL version of the financial model, which we 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 to finalize the automated management system in terms of financial business modeling based on the 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
- "terms";
- "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 highlighted with a dotted line is that for cells with a solid line, a drop-down list is provided. possible values, and in the cells highlighted with a dotted line, you enter values manually from the keyboard.
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 that 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 the age structure of 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 items sold, in parallel, accompanied by a planned average sales receipt or a planned average price sales 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 an option when our hypothetical trading company can have 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;
Clothing.
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 some 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 this is not difficult to do. by converting the financial models posted here accordingly, either independently 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 you select monthly, or the 4th quarter - if you select a quarterly breakdown) of the last year and then entering the approved planned percent growth of each period of the budget year to the 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 equal to 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 the financial method of budgeting, then it is obvious that without much effort we get a sales plan in thousands of rubles, broken down by budget periods, and we can start detailing 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 terms, 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 the planned average cost of selling one product, depending on the selected budgeting method (“orders” or “goods”). 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 of 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 has been 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. It looks like this:
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 the planned cost of sales and the purchase 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 is what 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 the customer places an order on the site 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 turnover, and online trading floor– Market Place, where suppliers are invited to advertise their products, after which, when client orders arise, our company transfers these orders to suppliers, as a result of which it receives its agency fee as a percentage of the amount of the client order or, in general, from 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. Particular attention should be paid to the fact that purchases are usually shifted in time to 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 difference between the functional and marginal approach to management and reporting system.
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 (by law they have the right 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 fiscal 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 are approved by the owners of the trading enterprise either as a percentage of certain financial sales indicators, or through the average cost per product 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 machines from transport companies for the delivery of 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 warehouse and outbound logistics budgets 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 terms of the financial model 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 classical scheme for generating this report, also called the functional approach, is that all expenses fall within the period by the date of the expense transaction, with the exception of 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, the excess revenue 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 costs - 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, and regardless of when (in what periods) they were incurred. With this approach, we see the real economy of sales and can compare periods with each other in order to understand whether the efficiency of operating activities is increasing or decreasing, and if it is decreasing, then within which operating units.
In "advanced" companies, where financial directors convey the meaning of the functional and marginal approach to management to company management, usually management reporting in terms of the income statement 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;
Legal expenses.
The payroll budget is formed in the financial model by entering a planned staffing table, 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 allocating 5 square meters of office space per employee, setting the cost of renting one square meter per month and summing up 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 have already presented the format of the detailed P&L report of our financial model, so here we present its "folded" version:
For the convenience of using the financial model, in the "conditions" tab at the top in the first six lines, the values of the main income and expenditure indicators are displayed, 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 rather tedious and cumbersome, we invite the reader, after our hopefully short notes on cash flow planning and the formation of a forecast balance, to 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 .
Receipts of commissions or agency fees as part of the sales of B2B services usually occur as a cumulative total for the reporting month no later than some date specified in the contracts with suppliers of the month following the reporting month. 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 in our example online sales B2B services, then “one” opposite “No. month” means that we will receive the agency fee for the current reporting month 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 designed 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 formulas of the financial model - 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 are allocated (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, arrears in payment to suppliers of goods and services, arrears in payment of the payroll to employees, and arrears to off-budget funds for payment of social fees.
The Balance Sheet looks like this:
Let's pay attention to the line with control, located under the line "retained earnings / loss". Formula this control in general it 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 a marketing analysis of such economic entities as
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 potential clients,
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, which we will lay out, most likely, will not be in the next 16th section (there we will lay out the basics of investment analysis, which we have long promised), but nevertheless in the 17th, it will be built on the principle of budgeting "from below up" with the marketing calculation of the sales budget, based on these very "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 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, you can calculate receivables and payables through the turnover ratios for receivables and payables set for the period under consideration, 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 volume 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 for receipts from customers for goods sold by us, but also debts for 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 cash funds of DS(1) and equity of SC(1) are unknown, since the volumes at the end of the Period of commodity balances of TK(1), as well as receivables of DS(1) and accounts payable of SC(1), we "calculated" 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 get 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 are equal to 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 turnover ratio formulas 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 a universal financial and economic toolkit for assessing both current activities and the development prospects of existing enterprises and new projects was required in the banking and investment sector, to create conveyor decision-making processes for lending and investing. Moreover, it was necessary to create such a toolkit 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 have been appropriate formulas have been developed, some of which can be viewed on our website at, and some more, namely investment analytics, will soon appear on the next, 16th, page of the "financial management" section.
On the other hand, it is a question of the methodology of education 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, which means that you are inside the company and have access to the terms of contracts concluded, to statistics of typical transactions for the purchase and sale of goods, accruals of expenses, conditions for inflows and outflows of DC, etc. .p., then it is not reasonable to use methods of calculation through turnover ratios and the use of methods similar to the indirect method of calculating the financial flow.
In conclusion, we will touch on another important issue, namely, the calculation of the coverage of cash gaps. If you 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 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 result, 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 a credit line in the form of an overdraft facility 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 within the framework of a bank overdraft on the same day, after which all funds received on the company’s current account go as a priority to repay the overdraft, and we will assume that first the interest is 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, we implemented the financial models using EXCEL formulas all the above conditions for crediting cash gaps under the overdraft model, as a result of which our cash flow statement (Cash Flow) was supplemented with a block of financial activities with turnover on attracting and returning credit funds, as well as interest payments for the use of borrowed money. Now our Cash Flow looks like this:
We can see from the report that cash gaps appeared in every quarter, and not just in the third, so draw conclusions when you use the classic general schemes for calculating cash gaps in your financial models without deep detailing.
Also, do not forget that now, after taking into account 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.
Spreadsheets Excel financial analysis Repina V.V. calculate cash flows, profit-loss, changes in debt, changes in inventories, dynamics of changes in balance sheet items, financial indicators in the format GAAP. Allows you to conduct a coefficient financial analysis of the enterprise.
Excel spreadsheets for financial analysis from Malakhov V.I. allow you to calculate the balance in percentage form, assessment of management efficiency, assessment of financial (market) stability, assessment of liquidity and solvency, assessment of profitability, business activity, position of the enterprise on the securities market, the Altman model. Diagrams of the asset balance, revenue dynamics, gross and net profit dynamics, debt dynamics are built.
Excel spreadsheets Popova A.A. allow for the financial analysis: calculate business activity, solvency, profitability, financial stability, aggregate balance sheet, analyze the structure of balance sheet assets, coefficient and dynamic analysis based on forms 1 and 2 of the enterprise's financial statements.
Excel spreadsheets financial analysis of the enterprise Zaikovsky V.E. allow, on the basis of Forms 1 and 2 of external financial statements, to calculate the bankruptcy of an enterprise based on the model of Altman, Taffler and Fox, assess the financial condition of the enterprise in terms of liquidity, financial stability, the state of fixed assets, asset turnover, profitability. In addition, they find a connection between the insolvency of an enterprise and the state's debt to it. There are graphs of changes in the assets and liabilities of the enterprise over time.
Analysis in Excel Maslova V.G. will allow a spectrum scoring analysis of the financial condition. Spectrum scoring method is the most reliable method of financial and economic analysis. Its essence lies in analysis of financial ratios by comparing the obtained values with the normative values, the system of "distribution" of these values according to the zones of remoteness from the optimal level is used. Analysis of financial ratios is made by comparing the obtained values with the recommended standard values, which play the role of threshold standards. The further the value of the coefficients from the normative level, the lower the degree of financial well-being and the higher the risk of falling into the category of insolvent enterprises.
Financial analysis module for MS Excel
Allows you to get a general assessment of the degree of sustainability of the enterprise of interest based on standard summary reporting on its activities.
Assessment of financial and economic activities