Online payment calendar. The main stages of developing a payment calendar system

Represents the organization's basic operational financial plan or cash flow plan. In the process of compiling it, all cash expenses are supported by real sources of cash receipts. The payment calendar reflects real cash flows regarding the receipt and expenditure of funds and financial resources.

Operational financial planning includes the preparation and execution of a payment calendar.

In the process of compilation payment calendar the following tasks are solved:

organization of accounting for the temporary connection of cash receipts and upcoming expenses of the organization;
formation of an information base on the movement of cash inflows and outflows;
daily accounting of changes in the information base;
analysis of non-payments (by amounts and sources) and organization of specific measures to overcome them;
calculation of the need for a short-term loan in the event of a temporary discrepancy between cash receipts and fulfillment of obligations and prompt acquisition of borrowed funds;
calculation (by amounts and terms) of the organization’s temporarily available funds;
analysis of the financial market from the perspective of the most reliable and profitable placement of the organization’s temporarily free funds.

compiled for short periods of time(month, 15 days, ten days, five days). The term is determined based on the frequency of the main payments. It is most advisable to draw up a plan for a month with a ten-day breakdown. The payment calendar covers all expenses and receipts of funds of the organization, both in and out.

The first section the calendar is its expenditure part, reflecting all upcoming calculations and transfers of funds, second- revenue part.

The relationship between both parts of the payment calendar must be such as to ensure their equality, or, even better, excess of income and receipts over expenses and deductions. The excess of expenses over income indicates a decrease in the organization’s ability to cover upcoming expenses. In this case, part of the priority payments should be transferred to another calendar period, the shipment and sale of products should be accelerated if possible, and measures should be taken to find additional sources.

When compiling a payment calendar, accounting data for transactions on a bank account, information about urgent and overdue payments to suppliers are used, and the schedule of shipment of products and the transfer of payment documents to the bank, financial results of sales of products, planned contributions to the budget for income, property and tax taxes are also taken into account. others, contributions to social extra-budgetary funds, the status of settlements with debtors and creditors.

Payment calendar example

Calculation of the need for a short-term loan

If there is a lack of funds, it can be used as a source of financial resources short term loan.

The need for a short-term loan is calculated when, according to the payment calendar, the organization lacks funds. The algorithm for calculating the need for a short-term loan is presented in table. 3.5.

Tab. 3.5. Payment calendar when calculating loan requirements

According to the payment calendar, it is clear that the organization is experiencing a lack of funds at the beginning of the month. Before the expected funds from the customer for the work are received, the total deficit of funds is 10,221 thousand rubles. Payment calendar taking into account receipt of a loan in the amount of 10,500 thousand rubles. for a period of 5 days is given in table. 3.6.

Based on the payment calendar data, you can determine a specific loan period - 5 days. On the fifth day of lending, you can fully repay the loan received and interest on the use of borrowed funds. Interest is calculated using the formula:

Let's say P = 13%, then the amount of interest payable will be equal to 10500 · 0.13 · 5: 365 = 18.5 (thousand rubles). Thus, 12,618.7 thousand rubles will leave the current account on the fifth working day of the month. (10,500 (loan) + 18.7 (interest on loan) + 2,100 (current expenses)), and the organization will have 560.3 thousand rubles at its disposal.

Tab. 3.6. Payment calendar taking into account the receipt of a loan

Payment schedule- this is one of the types of operational financial plan of an enterprise, developed for individual types of cash flow (tax payment calendar, payment calendar for payments to suppliers, payment calendar for loan servicing, etc.) and for the enterprise as a whole (in this case it details the current financial plan for the receipt and expenditure of funds).

Drawing up a payment calendar

The payment calendar is usually drawn up for the coming month (broken down by day, week and decade). Consists of the following two sections:

schedule for spending funds (or schedule for upcoming payments);
cash receipt schedule.

The cash expenditure schedule reflects the timing and amount of payments of the enterprise in the coming period for all (or specific) types of financial obligations.

The cash receipt schedule is developed according to those types of movement of these funds for which there is a return flow; it fixes the timing and amount of upcoming payments to the enterprise.

The payment calendar is developed with the aim of timely repayment of urgent financial obligations of the enterprise and ensuring constant solvency in the current period.

Was the page helpful?

More found about the payment calendar

Optimization of enterprise cash flows in real time
The specific period of time for which the payment calendar should be drawn up is not clearly defined. The choice of time intervals covered by the cash budget depends on the nature of the company
How to improve financial management during a crisis
We acted by eye, we’ll estimate how much money will come in and how much will go out; if we don’t guess right, we’ll quickly take out a loan and close the cash gap. The payment calendar was essentially fictitious; the received requests for payment were compared with it, of course, but
Practice-tested ways to manage company money
Nevertheless, there are several simple steps that will help you develop a payment calendar. Step 1. Develop the structure of the articles of the payment calendar. The easiest way to do this is to take
Deposit as a way to ensure the solvency of an enterprise
The payment calendar will help you plan upcoming payments in more detail by counterparty and payment purpose. The payment calendar is a planned financial document that reflects daily receipts and
Ensuring a balance of receivables and payables of organizations in Russian regions as a direction for strengthening regional budgets
Wilson, widely used in foreign countries, the development of a payment calendar that allows synchronizing positive and negative cash flows to the maximum extent possible, thereby increasing the efficiency of the enterprise’s cash flow. The payment calendar is one of the types of operational financial plan of the enterprise, developed for individual types
Current issues in cash flow management
It reflects the planned cash balances at the beginning and end of the period. The payment calendar is compiled on the basis of receipts and requests for expenditure of funds. The operational planning module decides
Financial planning
When creating a payment calendar, the following tasks are solved: organizing the accounting of the temporary connection of cash receipts and upcoming expenses of the enterprise
Cash asset management policy
For these purposes, a plan for the receipt and expenditure of funds, a payment calendar and other documents ensuring this turnover are developed 3. Determination of the minimum required balance of monetary assets
Systematic approach to accounts receivable management
Implementation of the control function requires monitoring and analysis of the state of receivables based on maintaining a payment calendar and comparing it with the schedule of expected cash receipts from debtors in order to
Policy for attracting trade credit
Settlements on trade credit are included in the payment calendar and are controlled in the process of monitoring current financial activities In the process of forming the attraction policy
Credit policy
Loan payments are included in the payment calendar and controlled in the process of monitoring current financial activities For enterprises attracting large volumes
How to overcome a crisis if the company is on the verge
Strict control of funds; compilation of payment calendars; ranking of payments by priority and observance of priority; setting limits and introducing additional authorization
How to change established rules for planning and controlling payments in a month
It is now possible to quickly create a payment calendar for any period not exceeding a month. The departments initiating payments fared the worst. Now
Working capital management of an economic entity as an important direction of its short-term financial policy
Other debtors make a forecast of cash inflow from debtors using collection ratios, compiling a payment calendar, refinance receivables 2 As effective measures for refinancing receivables on
Tax planning
Small organizations include tax payment indicators in the general payment calendar Synonyms tax planning The page was helpful
Form “cash flow report”: features of preparation and assessment of the solvency of a commercial organization on its basis
In B Kovalev defines solvency as the ability of an enterprise to fulfill the payment calendar without violations, that is, the presence of a company with sufficient funds to pay debt obligations
Accounts payable management policy
Payment of funds is included in the developed payment calendar and is controlled in the process of monitoring the current financial activities of the company Taking into account the projected growth
Borrowing policy
Payments for loan servicing are included in the payment calendar and are controlled in the process of monitoring current financial activities. At enterprises attracting large volumes
Bank loan attraction policy
These payments are included in the payment calendar and are controlled in the process of monitoring the current financial activities of the enterprise 7. Ensuring timely and
Regulations that will allow optimizing the holding’s working capital
The procedure for balancing the payment calendar is carried out 4.6.4. The person responsible for the expenditure side can carry out balancing only in items that are in

Payment calendar tasks

1. Avoid cash gaps and failure to fulfill the company’s obligations to counterparties. The main purpose of creating a payment calendar is to combat cash gaps, in which there is no money in the cash register or in the current account. Being actually a planned “schedule” of the enterprise’s DDS flow, the payment calendar allows you to predict cash gaps and take measures in advance to eliminate situations where it is necessary to make payments in the absence of sufficient funds in the company’s account. Following the principle “forewarned is forearmed,” you can quickly change plans for spending DS, thereby preventing a cash gap.

You can make preliminary “estimates”, change the dates of receipts and payments, coordinate them with counterparties - and reflect all this directly in the payment calendar.

2. Do not allow funds to be spent in excess of the approved amounts. Even if a company has a lot of money, this does not mean that it can be spent thoughtlessly. Spending is allowed only within the accepted budgets. Unforeseen situations that require going beyond the budget are rather exceptions to the general rule. The payment calendar allows you to control whether the payment is within the budget.

3. Manage the company's liquidity. One of the main criteria for the correctness of management decisions made in the financial sector is the positivity of the total flow of capital assets at any given time.

4. Provide reliable information online. It is important to receive financial information promptly. The disadvantage of the payment calendar implemented in Excel is the time spent on rebuilding the reporting. The payment calendar must be integrated into the operational accounting system so that double entry is not required to obtain reliable data.

5. Ensure that procedures for agreeing to pay expenses are properly followed. The approval procedure should include a certain delegation of responsibility, depending on the importance of a particular payment.

6. Eliminate the human factor as much as possible.

The main purpose of using a payment calendar is to combat cash gaps. Presentation of the payment schedule in a simple, visual form allows you to clearly see the picture of cash flows formed by operational planning data based on information about planned cash receipts and write-offs.

Rice. 1

payment calendar cash flow

The main stages of developing a payment calendar system

1. Formation of planned data (this process is implemented as part of budgeting and is a mandatory preparatory stage, since the possibility of payment is checked according to planned data).

It is assumed that the company has a financial structure with dedicated central financial districts, budgeting as a management tool is implemented and working. This stage is the basis for the payment calendar, because The possibility of payment/receipt is checked against certain planned data.

It is important to draw up a BDDS regarding cash flows for core, investment and financial activities, for which it is necessary to have budgets for both core activities and investment projects. It is advisable to assign a person responsible for each article (i.e., an employee who makes decisions and is responsible for both planned and actual indicators, deviations from the fact plan)

2. Determination of the list of analytics (DDS article, counterparties, contracts, source of DS), in the context of which the possibility of payments and receipts will be checked.

The list of analytics allows you to create a tool for checking payments/receipts for feasibility.

You can use the following analytics: CFD, source of DS, counterparty (recipient of DS), agreement with the counterparty (under which payments will be made), DDS item (under which DS payments will be made), project (for which investment payments will be made), payment priority.

When identifying analytics in the accounting system, it is necessary to remember the principle of economic feasibility - excessive requirements for the list of analytics make the system labor-intensive and immobile. Therefore, you should focus on the list that is the minimum necessary to solve the problems set by the company for the accounting system.

3. Construction of a mechanism for generating registers of payments and applications.

After determining the analytics and payment details, a payment/receipt toolkit is generated. The system for operational financial management of an enterprise, implemented through the “Payment Calendar” business process, includes several participants (employee, department, service), and everyone needs a tool with which they will work in this system. Employees of departments and services submit applications for payment/receipt of DS, and management or employees authorized to make decisions implement them through application registries. An application is a request document for receipts or payments of DS, containing descriptive information about this operation. The application reflects all approved analysts, supporting details, priority, if necessary, and a field for comments from each participant in the future payment calendar process.

The register of applications is a list of applications consolidated according to a certain characteristic (date, responsible person, source of DS, type of articles).

Both the application and the register of applications for payment of DS must contain a complete and sufficient amount of information to make a decision on the payment/receipt of money.

4. Description of the “Payment calendar” business process, identification within its framework of responsible persons for procedures and tasks.

After verification and approval of payment for submitted applications, it is considered that the payment calendar as a business process is implemented in the registers of approved applications.

Next you need to answer the questions: who, what, when and in what time frame. It is at this stage that the process of developing a payment calendar is important, which establishes the order of interaction between employees and, therefore, determines the result of the company’s payment discipline and its reputation as a conscientious partner. There are many options for employee interaction in the process of approving applications, and each enterprise develops a scheme that is appropriate and convenient for it.

5. Regulation and documentation of the “Payment calendar” business process.

At the final stage, the business process of cash flow management is enshrined in regulatory documents, which are approved by an internal order and are mandatory for execution by all departments and employees of the company. An internal document defining the rules for the functioning of the company's payment system must contain information about the procedure for processing applications for payment, deadlines, persons responsible for coordination and approval, duties and powers of employees, and the sequence of actions.

6. Automation of the “Payment calendar” business process.

To maintain operational cash flow management, automation of relevant business processes will be required. The software must allow:

· create electronic accounting documents of the payment system (for example, applications for payment or registers);

· generate electronic reporting necessary to control the execution of payments, compliance with the regulations of the payment system, BDCS (for example, payment calendar);

· implement support for control and approval procedures (budgets, requests for payment, etc.).

· differentiate access rights to financial information for different levels of responsibility in the company.

Some enterprises use Excel and other non-core programs to automate cash flow management business processes, although this method has a number of disadvantages: low efficiency in reflecting information and generating reports, vulnerability to failures, the problem of double data entry, and the need to spend time rebuilding reports. Therefore, more and more companies are inclined to choose specialized software.

One of the features of the payment calendar is its constant updating. In case of insufficient funds to satisfy all received applications, rules for ranking payments are used depending on their type and level of priority.

The Payment Calendar report is the main tool for monitoring the cash flow of an enterprise. The data displayed in the report allows you to compare calendar schedules of payments and cash receipts with their execution, as well as assess the level of solvency of the enterprise in the short term. The structure of the "Payment Calendar" report is a two-dimensional table, the columns of which contain periods with daily detail, and the rows contain groupings of analytical sections used in planning payments and receipts, as well as when reflecting transactions of the actual movement of funds through the current accounts of the enterprise and its checkout.

The procedure for calculating the amounts of balances of non-cash and cash funds withdrawn in the payment calendar depends on whether the displayed calendar period (day) belongs to the past, current or future (relative to the working date) and is subject to the following rules:

Past calendar periods- planned and actual balances at the beginning of the day correspond to the actual balances for the previous day, the planned balance at the end of the day is calculated taking into account planned receipts and payments, and the actual balance is calculated based on data on actual cash flows;
Working day- data for a working day is calculated similarly to previous calendar periods;
Future calendar periods- for future calendar periods, only the values of planned amounts are available, the calculation of which is carried out sequentially, based on the planned payments and receipts of each day, starting from the working date, starting from the planned cash balance at its end. Wherein should be considered, what if there is a need to calculate future calendar periods from the actual balances of the working date ( such a situation, for example, may arise at the end of the working day, when it is known for sure that no more receipts and payments are expected today) it is enough to shift the working date one day forward, which in turn will shift the current day to the previous calendar period and allow you to plan movements and balances of future periods based on the actual data of the current day.

Below are examples of "Payment calendar" report forms.

"Payment calendar" report form

Report details required to be filled out

To generate the report, you must fill in the following fields: the period for generating the payment calendar, the working date and the organization for which the payment calendar will be generated. When you open a report form, a default period is set where the start date is calculated from the current date minus two days, and the end date is calculated from the current date plus seven days. Thus, by default, the viewing depth of the payment calendar is two days, and the planning horizon is seven days. The value of the "Working date" field is set equal to the value of the current system date. The value of this attribute must be within the interval of the report generation period. The “Organization” attribute, when opening a report form, automatically takes on the value of the main organization from the user’s personal settings.

Available analytical views, row and column grouping options

When generating a report, the user can independently configure row groupings according to the following analytical sections:

Type of funds- provides grouping of funds by type: non-cash, cash;
Type of cash flow- provides grouping of information in accordance with the general classification of cash flows: settlements with suppliers, customers, employees, payments to the budget, etc.
Cash flow item- provides a detailed classification of cash transactions;
Counterparty- Counterparty, individual;
Agreement, on which the calculations were made.

Report columns are generated only by periods with detail by day; changing groupings by columns is not available.

Report settings are managed using the settings panel, which opens by clicking the “Settings” button on the command panel. Report settings allow you to set grouping, selection and sorting of data.

Additional information on using the report

When you call to decrypt the data contained in the payment calendar line, the “Cash Flow Card” report will open.

The report form provides the ability to quickly enter the following documents: “Application for payment”, “Cash receipt plan” and “Cash expenditure plan”. To do this, in the report command panel, open the “Enter document” menu item and select the required document type.

In accordance with the rules of administration of the "Payment Calendar" system, the "Payment Calendar" report is available to users who have the following sets of rights: "Full Rights" or "Payment Planning".

The table with total cash flows discussed in Chapter 19 serves more to display the total daily cash flow (DC) and cash balances (CF), according to which, if they are insufficient, the cost of borrowed funds can be calculated. Financial department specialists thought about the opposite problem - the lack of detail or analytics of these cash flows in the context of one banking day. Such detail is necessary for the analysis of ongoing cash transactions and, if necessary, subsequent adjustments to the management decisions made to correct possible troubles. Therefore, it was decided to supplement the cash flow forecasting model with another worksheet with a payment calendar table, which will display:

balance of DS at the beginning of each day;
receipt and write-off of DS during each day, both as a total flow and in the context of management items of their accounting;
net cash flow for the day, as the difference between the receipt and write-off of DS;
receipt and write-off of DS on an accrual basis per day;
total turnover of DS for the month for all items of management accounting.

The strategic meaning of the payment calendar itself is to find bottlenecks in detailed DS flows and, if possible, eliminate them. Indeed, with a positive flow for the month as a whole, there may be surges in DS balances below the zero mark within this month. This situation, when there is not enough money to conduct operational business activities, is called a cash gap. Without using modeling, it is almost impossible to predict the possible behavior of cash, and also to correct it. And this is a failure to fulfill one’s obligations with possible subsequent disruption of the business as a whole or additional costs for eliminating undesirable consequences when they become known too late. After all, it is known that the cost of purchased goods and services often depends on the partner’s fulfillment of his obligations.

Another possible meaning of the payment calendar is the optimization of cash flow (CFC) in order to maximize the efficiency of using resources, which is money. DDS items in the payment calendar allow you to take a more effective look at ongoing processes in order to create a deficit-free payment calendar based on the analysis of previous events and forecasting future ones. There may be different approaches, for example, the desire to “meet” the planned monetary resources or the calculation of the minimum possible amounts of attracted loans and their cost in the event of a shortage of DS. The payment calendar will allow you to model future cash flows on a “what-if” basis and find more optimal options, for example, in terms of payment. If there is a short-term excess of money, a decision is made not to place it on deposit, but to pay suppliers for materials (goods, services) with a certain pre-agreed discount. Or, on the contrary, preliminarily negotiate with the buyer in order to pay for the goods earlier than the established deadline and provide him with a discount, rather than attracting a short-term loan.

Example of a classic payment calendar

Logically, cash flows, as a means of payment, include cash directly - a commodity with instant liquidity - and their equivalents. The equivalents are various assets received as payment for goods (services) sold. This is, as a rule, another product (offset, barter), bills, etc. But when these equivalents arrive, the amount in the bank account remains unchanged and the liquidity of the equivalents is not instantaneous. Therefore, we consider the payment calendar only to forecast and record cash flow directly.

As part of the classic payment calendar (Fig. 21.1.), three components of DDS can be distinguished:

cash flow from core activities;
cash flow from investing activities;
cash flow from financing activities
detailing them into separate articles.

Filling out the payment calendar and the calculations that are made in it can be carried out using a variety of methods. For example, entering data into a calendar using a keyboard based on previously made calculations or assumptions. But this method, when an enterprise carries out active and multifaceted activities, does not suit the financial department. There must be a certain coherent system that will allow both simultaneously changing arrays of data in the payment calendar and analyzing this data. It is assumed that the data in the calendar will be updated automatically, based on the developed algorithm, and all previously obtained results in the cash flow forecasting model will be used as initial data.

The payment calendar acts as the final report on the basis of which the cash life of the enterprise will be planned and controlled. As an example of possible automation of filling out the payment calendar, consider the operation of supplying goods and receiving funds under a credit line, discussed in previous chapters.

Rice. 21.1. Example of a classic payment calendar

Tasks of automating the formation of a payment calendar

The Cash Flows model has a significant drawback - the inability to view ongoing transactions in the context of one banking day if there are a sufficient number of entries about ongoing transactions in the source table on the SourceData worksheet. For this purpose, enter into the model a PC worksheet with a payment calendar (Fig. 21.2.). Immediate tasks of automating the formation of the payment calendar:

automatically determine the names of incoming and outgoing items of cash transactions and enter them in the Name column, taking into account their belonging to either the incoming or outgoing component of the payment calendar;
based on the entered month number for which the payment calendar is generated, select cash transactions only for this month;
determine the number of the year for which the payment calendar is being generated and generate the dates of each day in Excel format;
sum up the flow of cash transactions for each day for each item in the payment calendar and enter this amount into the payment calendar at the intersection of the line with the name of this operation and the column indicating this number of the day of the month in which the operation was carried out;
determine the amount of daily cash receipts and expenditures, as well as the net cash flow for the day;
determine from the beginning of the month on an increasing basis the amount of receipts and expenditures of funds;
carry out final final calculations of the payment calendar.

Rice. 21.2. PC worksheet with created payment calendar and numerical example for the month of February

Creating a payment calendar

The table of the payment calendar itself is located in the area of cells C6:AI83. Lines 12:29 of the calendar are allocated for the formation of itemized data for incoming funds, and lines 32:79 for written-off funds.

Before automatically filling out the payment calendar, the number of the month for which the payment calendar will be generated is entered into cell D2 using the keyboard. Intermediate calculations when forming the payment calendar will be made in columns BA:BC.

Formation of names of articles of the payment calendar

To generate the names of the items in the payment calendar, the subprogram Formation of Calendar Items is intended (Fig. 21.3.). Items are formed based on the names of operations entered into the source table on the Source Data worksheet. Moreover, if the name of the operation is present in the source table, but was not carried out in the month for which the payment calendar is being generated, this item will still be present in the calendar. This is explained by the need to unify payment calendars for any period of time. The meaning of such unification lies, first of all, in visibility, or a person’s desire to look for things in a place familiar to him. For example, in one month the company conducted active financial and economic activities and there were 50 expense items for which operations were carried out, and in another month there were only 10, and only items with ongoing operations are reflected in the calendar. A manager, having placed two sheets of paper with printed payment calendars on the table, will spend more time searching for identical items in these tables than directly analyzing the information contained and making decisions.

The subroutine includes two other procedures: entering formulas for selecting the names of income items (RevenuePart) and write-off items (ExpenditurePart). The subroutine itself only inserts the selected text of article titles directly into the calendar.

In our example, in the created payment calendar you can place up to 18 items in the income part of the calendar and 48 items in the expenditure part. If necessary, the number of articles can be reduced or increased, but then it will be necessary to edit the payment calendar generation macros described below and the size of the table in which the calendar is placed.

If there is no need to constantly update the names of articles when forming a payment calendar, then this subroutine can be omitted by placing a note sign in front of its name in the Filling out the Payment Calendar subroutine discussed below (Fig. 21.5.).

Rice. 21.3. Subprogram Formation of Calendar Items for generating the names of cash receipt items

Formation of names of cash receipt items

Before you start recording the RevenuePart macro, enter the formulas in cell BA12:

=IF(Original Data!I10=0;"";IF(Original Data!H10=1,Original Data!C10,0))

which first checks whether the value in cell I10 of the Input Data worksheet (receipt of funds) differs from the value 0. If it differs, then an additional check is carried out to see whether the cash flow attribute is entered in cell H10 of the Input Data worksheet. If these conditions are met, the formula returns the text of the name of the operation entered in cell C10 of the OutputData worksheet.

And in cell BB12 the formula:

=IF(BA12=BA13,0,BA12)

which comes into its powers after replacing the contents of the range of cells BA12:BA5002 containing the first formula with the values determined by it and their subsequent sorting (in descending order, not counting the first row as the header line). After sorting the values, the text of the same article names will be contained only in adjacent cells. Then this formula, having compared the values in two adjacent cells, will return the value 0 if the text is identical, and if there is a mismatch, the text of the title of the article in the cell located above. Thus, of all the cells containing the same article title text in the entire data array, only one will be selected.

To record a subroutine, do the following after you start recording the macro:

select the area of cells BA12:BA22 and press the F2 key, followed by the Ctrl+Enter key combination;
Without moving the table cursor, copy the selected range to the clipboard and paste into the same range by calling the Paste Special dialog box, in which activate the Values switch;
Without moving the table cursor, sort the selected range in descending order by clicking the Sort Descending button on the Standard toolbar. Thus, the entire existing list of operations related to cash receipts will be collected in the upper part of the selected range, and in descending alphabetical order;
select the area of cells BB12:BB22 and repeat all the described steps;
stop recording the macro and, opening the Visual Basic Editor, edit the selected ranges of cells, replacing their addresses with BA12:BA5002 and BB12:BB5002, and also edit the text of the VBA code and enter comments.

Rice. 21.4. Macro ProfitablePart

Formation of names of cash write-off items

When creating a ConsumablePart macro, there is no need to repeat all the steps performed when recording the previous one. Copy the RevenuePart macro in the Visual Basic Editor and replace the name and edit it, which consists of replacing the reference to the cell with I10 when entering the formula in the BA cell range:

""=IF(Original Data!R[-2]C[-44]=0, ......"

""=IF(Original Data!R[-2]C[-43]=0, ...."

This is a relative reference system and indicates that column I is 44th, and column J is 43rd to the left in relation to column BA, in the cells of which formulas are entered.

Filling out the payment calendar with daily cash turnover amounts

The subroutine Filling out the Payment Calendar by Dates (Fig. 21.5.) consists of three components:

subroutines Formulas for Filling out the Payment Calendar directly for entering formulas that perform calculations;
For-Next cycle for sequentially entering the calculated daily turnover of cash flows for each item into the payment calendar;
clearing the contents of the area with entered formulas for intermediate calculations.

The criteria for selecting data are:

the month number entered in cell D2 before starting calculations;
the number of the day of the selected month, entered by the For-Next cycle using the variable X in cell BA1, and when executing the subroutine, changing the value from 1 to 31;
the name of the management accounting item generated earlier in the payment calendar in the range of cells C12:C79.

Fig.21.5. Subroutine Filling out the Payment Calendar by Dates

Formulas for calculating daily cash turnover

To enter formulas for determining daily cash turnover, write the Payment Calendar Filling Formula macro, having previously entered the following formulas. In cell BA10 the formula is:

=IF(MONTH(OriginalData!D10)=$D$2,IF(DAY(OriginalData!D10)=$BA$1,OriginalData!C10,0);0)

which first determines whether the month number in the date found in cell D10 of the OutputData worksheet matches the month number entered in cell D2 of the PC worksheet. When this condition is met, the following condition is checked - whether the number of the day of the month of this date corresponds to the number of the day of the month entered into cell BA1 of the PC worksheet. If these conditions are met, the formula returns the name of the operation being performed, entered in cell D10 of the OutputData worksheet. Otherwise, the formula returns 0.

Formula in cell BB10:

=IF(BA10=0,0,SUM(OriginalData!I10:J10))

checks the result of calculating the formula in cell BA10. If the result is not zero, then the formula sums up the value of the receipt and debit of funds for this operation, contained in cells I10:J10 of the InputData worksheet. The summation of the values of income and expense items is explained by the fact that cells I10:J10 cannot contain values at the same time. This must be taken into account when creating a table on the SourceData sheet, because otherwise such a record will not make any sense.

Formula in cell BC12:

=SUMIF($BA$10:$BA$5000,$C12,$BB$10:$BB$5000)

summarizes the cash flow turnover in the area of cells BB10:BB5000 for the number of the day of the month entered in cell BA1 and provided that the text of the title of the article in cell C12 is identical to the text of the name of the article in the area of cells BA10:BA5000.

Recording a macro consists of sequentially entering these formulas into the cell ranges BC12:BC79, BA10:BA5000 and BB10:BB5000.

Rice. 21.6. Subroutine for entering formulas for determining turnover per day for each item of management accounting

The cycle of filling the payment calendar with daily cash flow turnover

The main element of automatically filling the payment calendar with daily DDS turnover is the For-Next cycle. The sequence of operations performed by the cycle in the subprogram Filling out the Payment Calendar by Dates (Fig. 21.5.) is as follows:

preliminary, the Row variable (line number) is assigned the value 12, which corresponds to the line number from which the text of the names of management accounting articles begins to be formed;
the execution of cycles from 1 to 31 is set, which corresponds to the maximum number of days in a month;
variable X is assigned a value equal to its previous value increased by one. The value of X was not specified and at the beginning of the loop it will be equal to zero;
the current value of variable X is assigned to cell BA1 (number of the day of the month), from which data will be sampled using previously entered formulas in the range of cells BA10:BA5000;
recalculation of the entered formulas is specified;
the contents of the range of cells BC12:BC79 are copied, the formulas of which return different values when the value of cell BA1 changes;
a cell is defined in the working area of the payment calendar with an address at the intersection of the row number (Row) and the column number equal to the value 3 (columns A:C), increased by the value of the variable X. Line of code VBA Cells(Row, 3 + X). The values of the copied range BC12:BC79 are inserted into this cell. The X variable increases by one with each cycle and with each cycle a transition occurs one cell to the right - D12. E12, F12, G12, etc. You can not use the Row variable in this subroutine, but simply specify the value 12, but this is more clear.

Entering dates and balances at the beginning of the day

The task of the CalendarDatesBalance macro (Fig. 21.7.) is to enter the formulas:

determining the number of the year in which the month number entered in cell D2 may be located;
the date for which daily calculations are made in the payment calendar;
cash balances at the beginning of each day.

As you remember, when creating the table on the SumFlows sheet, the vertical position of the table was limited to line 200 or, based on the workspace, the table covers a time interval of 198 days. The limitation was due to an increase in the speed of information processing. Moreover, the algorithm for creating this table independently determines the date from which this time interval will begin. As a consequence, if the start date is in the second half of the year, then the time interval will move to the next year.

In addition, by creating various applications and models throughout the book, we strived to ensure that the user makes as few unnecessary movements as possible when working with the computer. Such “movements” include entering the number of the year for which the payment calendar will be generated. Let's let Excel select the year itself. The main criterion is that the year must be in the time range formed on the SumFlows sheet and determined by the number of the month entered in cell D2 on the PC sheet.

Despite the apparent complexity, the task is quite simple and is determined by one formula, according to the criteria: a given time range and the value of the month number. The formula for determining the year number in cell D3:

=IF(OR(YEAR(MIN(SumThreads!B3:B200))=YEAR(MAX(SumThreads!B3:B200));MONTH(MIN(SumThreads!B3:B200))

In the first argument of the IF function, the OR function checks two conditions:

Whether the maximum year in the date area on the SumFlows worksheet is equal to the minimum year number in the same range. In simple words - will there be a New Year's Eve in this interval?;
is greater than or equal to the minimum month number in the same date range to the month number in cell D2.

If one or both of the conditions are true, the formula returns the minimum year value in the date area of the SumFlows worksheet. Otherwise, the number of the maximum year in the same area is returned.

Formula for forming a date in cell D6:

=IF(EOSH(CONCATENATE(D7;".";$D$2;".";$D$3)*1);0;CONCATENATE(D7;".";$D$2;".";$D$3 )*1)

using the CONCATENATE function, combines the day (cell D7), month (cell D2) and year (cell D2) into a single whole. The algorithm for calculating such formulas is described in Chapter 7. The cycle of filling out the payment calendar assumes that there are 31 days in a month. Therefore, as in our example, if you create a calendar for February, which has 28 days, then in this month, starting from the 29th day, the #VALUE error value will be returned. Therefore, the CONCATENATE function in the first argument of the IF function is checked by the EOSH function, which will return TRUE if an error is detected. In this case, the IF function will return the value 0, otherwise the value of the Excel date generated by the CONCATENATE function.

For this date, the formula in cell D9 determines the cash balance at the beginning of the day, using the cash balance in the cash flow table on the SumFlows worksheet:

=IF(END(VLOOKUP(D6-1,SumThreads!$B$3:$E$200,4,FALSE)),0,VLOOKUP(D6-1,SumThreads!$B$3:$E$200,4,FALSE))

The main function of searching for formula data is the VLOOKUP function. If there is no date in the search range, to eliminate the error value returned by the formula, the UND function is used in the first argument of the IF function. And the IF function selects - if there is no date, it returns the value 0, otherwise the value of the cash balance on a given date, found by the VLOOKUP function.

Recording the CalendarDatesBalance macro involves sequentially entering these formulas into cells D3, D6:AH6 and D9:AH9 and then editing the VBA code after recording the macro.

Rice. 21.7. Subroutine CalendarDatesBalance

Final calculations of the payment calendar

The CalendarFinalCalculations subroutine (Fig. 21.8.) is quite simple and consists of entering all the summing formulas into the appropriate ranges of cells. Finally, the subroutine selects the entire worksheet and replaces the formulas with values, after which it enters a formula for determining the current date and time into cell C1.

Rice. 21.8. Subroutine CalendarFinalCalculations

Full cycle of filling out the payment calendar

In order for all operations to fill out the payment calendar to occur automatically, write a subroutine Filling out the Payment Calendar (Fig. 21.9.). This subroutine consists of the names of previously recorded macros that perform the calculation procedures specified by them. To run a macro for execution, create a button Filling the payment calendar (Fig. 21.2.) and assign the created subroutine to it.

Rice. 21.9. Subroutine Filling out the Payment Calendar

Full cycle of cash flow calculation

To fully calculate the entire model from recalculating the data on the Output Data sheet to calculating the credit line and filling out the payment calendar, write the Full Calculation of Cash Flows subroutine (Fig. 21.10), which carries out the entire calculation cycle described in the chapters of creating the Cash Flows model. Entrust the execution of the subroutine to the Complete cash flow calculation button (Fig. 21.2.).

Rice. 21.10. Subroutine Full Calculation of Cash Flows for complete calculation of the cash flow model

The subroutine delegates to the previously created procedures Refine Calculation of the Credit Line (Fig. 20.12.) and Filling out the Payment Calendar (Fig. 21.9.) to perform the entire cycle of calculations from the moment the table is recalculated on the SourceData sheet until the payment calendar is filled out. In addition, the subroutine is supplemented with service functions:

a timer that records the execution time of the entire calculation cycle;
a dialog box that pops up when calculations are completed;
minimizing the Excel window for the entire duration of the calculation and then restoring it to full screen upon completion of the subroutine.

The speed of calculations for the entire model depends on the power of the computer and can range from tens of seconds to several minutes. In our subroutine, the implementation of the timer is more of a training nature.

The timer works based on the Timer function, which returns a value representing the number of seconds that have passed since midnight. At the beginning of the execution of the subroutine, the variable X is assigned the value of the Timer function at the moment the button is pressed, which starts the calculation. After executing the subroutine, variable X is assigned the difference between the current value of the Timer function and the previously recorded value of variable X. The resulting value is rounded using the Round function:

X = Application.Round((Timer - X), 0)

Otherwise, in the dialog box indicating the end of the calculation cycle, several decimal places will be present after the integer value of seconds.

Creating a Dialog Box Using the MsgBox Function

The possibility of creating custom dialog boxes in Excel using the Visual Basic Editor was written in Chapter 5 (Fig. 5.16.). Let's look at creating and using a dialog box using the MsgBox function. The MsgBox function displays a dialog box containing a message and waits for the user to press a button.

Function syntax:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

contains the following main named arguments:

prompt is a required argument. This is a string expression that is displayed as a message in the dialog box. The maximum length of a prompt string is approximately 1024 characters, depending on the width of the characters used.
buttons is an optional argument. This is a numeric expression that represents the sum of values that indicate the number and type of buttons displayed, the type of icon used, the primary button, and the message box modality. For help on this argument, place the cursor over this function and press F1 to display Visual Basic Editor Help. The default value of this argument is 0.
title is an optional argument. This is a string expression that appears in the title bar of the dialog box. If this argument is omitted, the application name, such as Microsoft Excel, is placed in the title bar.

In our routine, in a line of VBA code:

MsgBox "Full calculation duration - " & X & " second(s)", 0, "Cash flow forecasting model"

The MsgBox function has the following arguments:

prompt - displayed text message containing the text - Full duration of calculation, then the variable value - X and text - seconds(s);
buttons - assigned the value 0, which means that there is one number of displayed buttons - only the OK button is displayed;
title - text expression displayed in the title bar of the dialog box: Cash Flow Forecasting Model.

Rice. 21.11. Created a Cash Flow Forecasting Model dialog box to display the timing of the model calculation and indicate when the calculations have been completed

Minimizing the Excel window for the entire duration of calculations

A full model calculation cycle can last more than one minute, and in order to avoid eye fatigue when calculating the credit line parameters, screen updating was disabled (Fig. 20.12.). A more efficient way is to minimize the Excel window while the calculation procedure is running. So add two lines of VBA code. At the beginning of the subroutine:

Application.WindowState = xlMinimized

and in the end:

Application.WindowState = xlMaximized

Setting the WindowState property to xlMinimized will minimize the Excel window before performing calculations, while setting the WindowState property to xlMaximized will restore it to full screen. During this time, you can work in other programs.

Results

So, over the course of five chapters, a cash flow forecasting model was gradually created. This chapter is the final one for creating a model to carry out the necessary calculations.

This chapter described the process from the need to use a payment calendar in the cash management activities of an enterprise to the implementation of a project for its automatic generation. It differs somewhat from the classic one (Fig. 21.1), but this can be easily corrected, for example, by organizing the export of data from the created calendar to the classic one.

The practical application of the payment calendar for a manufacturing enterprise is discussed in Chapter 26.