Sample payment calendar. Payment calendar as a budgeting tool

Today, the occurrence of cash gaps in a company largely depends on how accurately the company’s financial director can draw up a payment calendar. There are several practice-tested recipes. We will tell you in the article which sections of the company’s payment calendar it is important to provide for and provide a sample payment calendar.

Register of payments in the payment calendar

An important component of the worker payment calendar– register of planned payments. As a rule, this document is given to the financial or general director for signature. Moreover, ideally, the payment register contains not only such standard columns as the date of the payment request, account and agreement numbers, name of the counterparty and amount to be paid, but also a number of additional fields that will help the head of the treasury department (or the financial director, if so such a treasury function is not allocated) to generate a payment calendar, namely:

name of budget item , within which the payment will be made. One of the classic procedures performed when approving payment requests is checking for compliance with the budget. It will be much easier to carry out such a check not based on a separate application, but based on the payment register. To do this, for each declared payment it will be justified to indicate the name of the budget item;
source of payment – indicates from which current account (from which cash desk) the money is planned to be paid according to the application. Without this, when drawing up a payment calendar, it will be difficult to plan balances at the beginning and end of the day in the context of the company’s current accounts;
authorization – an additional sign reflecting the status of the application: agreed or not agreed. By the way, this field can be divided into several components, for example, by the positions of the responsible persons taking part in the approval. As a result, the financial director, who received the payment register for signature at the beginning of the week (or at the end of the previous one), will clearly see which applications were approved at the previous stages, and which are still being approved and by whom;
fact of execution of the application – a field that will contain a note indicating whether the application has been paid or not, as well as the date of payment. If necessary, the initiator will be able to quickly obtain information about the status of his application, and the financial director will be able to quickly update the payment calendar by entering into it those payments that have already been made this week.

Rules for collecting and processing applications

We can safely say that drawing up a payment calendar, anything reliable will not work for the next week if the company does not have payment regulations. There will always be heads of departments who will demand immediate payment of this or that invoice. And this despite the fact that in practice, situations extremely rarely arise when it is impossible to foresee the need for certain payments at least a week in advance. Most often, “urgency” is the reason for the forgetfulness and negligence of individual employees, which is simply unacceptable in relation to money today. The payment regulations determine the rules for filling out applications, the approval procedure, as well as the deadlines within which an application can be submitted and the time when it will be executed. For example, the formation of an application by the payment initiator and its acceptance by the head of the department - every Monday before 16.00, issuance of cash according to agreed applications - Wednesday, Thursday, Friday after 11.00; non-cash payment for first priority applications - every Thursday until 14.00, etc.

Regulations for making payments approved by order of the general director of the enterprise and brought to the attention of all employees. By the way, the most important thing in the payment regulations is to pay special attention to how the application will be processed if the deadline for its submission is violated or the limit on the budget item is exceeded. This will not only determine the course of action in an unforeseen situation, but will also motivate people to follow the regulations more strictly. For example, if the deadline for submitting an application is missed, and the regulations for this case provide for communication with the financial or general director, for the vast majority of employees this is a serious incentive to continue to inform the financial service about any payments in advance. You can download the payment regulations at the bottom of the article.

We plan the receipt of funds when drawing up a payment calendar

Paradoxically, the most difficult thing is to plan the amount of incoming funds per day in the payment calendar. Most often they act as follows. Receipts planned in the cash flow budget are divided by the number of working days in the month and the resulting figure is entered into payment schedule as the incoming cash flow of one day. Actually, there is no other solution, especially if we are talking about a company working with retail customers without concluding preliminary contracts. But this must be taken into account when planning payments. For example, a company has account balances at the beginning of the day of 150 thousand rubles, and 300 thousand rubles are expected to be received. Based on these data, it is planned to pay bills in the amount of 450 thousand rubles. As a result, it turns out that out of the planned 300 thousand rubles, only 200 thousand rubles were received by the end of the banking day. And the entire payment calendar needs to be redone. Moreover, the balances on the current accounts were planned to be zero, and the company has an agreement with the bank, according to which it undertakes to maintain a minimum balance on the account - 10 thousand rubles. Below we have provided payment calendar example.

It is better to determine in advance what share of the funds planned for receipt can be used to pay certain bills. Most often, this is done expertly, based on the company’s experience or based on an analysis of the flow of funds. Moreover, in the payment regulations it is better to determine in advance the size of the insurance balance at the end of each working day, which will be taken into account when drawing up the payment calendar.

And in addition to everything, it would not be amiss to include in the payment calendar data on the company’s existing undrawn limits within the framework of credit lines. For example, this information can be indicated in the same place as information about incoming balances. In this case, information about the financial capabilities of the enterprise in the payment calendar will be presented as completely as possible, and the financial director will be able to more effectively manage available resources.

Determining priorities if there is no money to pay all bills

Today, for many companies, it is quite typical that the available funds are not enough to pay for all accepted applications. Typically, this leads to a meeting with all department heads to discuss which payments can be deferred. A lot of time and effort is spent on such a procedure. You can manage money more quickly if each application indicates the priority of payment and the payment deadline under the contract. Having this data, the financial director will be able to independently make decisions about postponing certain payments to a later date. As a rule, wage arrears to the budget and banks are repaid first. The second priority is key counterparties, the rupture of relationships with which threatens the company with a stop in production or significant penalties. The third priority is all other payments.

By the way, in order to make it more convenient to track unpaid requests, it is better not only to indicate the fact of execution (or non-execution of the request), but also to provide in the payment calendar, in addition to the classic division of payments by cost items, also analytics by counterparties. The easiest way is to create a payment calendar in Excel. If created payment calendar in Excel, it will be enough to enter an additional column in which to indicate the recipient. As a result, it will be possible to quickly display a list of debtors who need to be paid in the near future.

Execution of the payment calendar

Once the payment calendar has been compiled, all that remains is to update it. As a rule, this is done at the end of the day based on an extract from the bank-client system. Instead of planned data, information about actually made payments, receipts and, most importantly, balances is entered into the payment calendar. If necessary, it is possible to provide the possibility of daily analysis of deviations of planned values from actual ones. But, as a rule, such an analysis is justified based on the results of the week.

Another nuance that is worth considering when drawing up a payment calendar for an enterprise is to make an enlarged plan of receipts and payments, divided by weeks (not by days) for the coming month. This will help you better navigate upcoming payments, especially if some of them have already been postponed due to lack of funds.

For more information on how to create a payment calendar and how to work with it, watch the video. Elena Mitrofanova, head of OFER projects at IBS, tells the story. She also provided a sample payment calendar.

Video

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 located.

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 (cash receipts) 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 the 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.

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 revenues 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

Effective management of an enterprise's cash flow is a prerequisite for its stable operation. One of the important points in this regard is balancing expenses and cash receipts over time. Without this, there is a high probability of problems arising when paying suppliers' bills, which ultimately leads to underutilization of capacity or payment of penalties.

On the other hand, correct accounting and analysis of cash flows can make it possible to identify untapped sources of additional income for an enterprise that is not related to the main areas of its activities (the simplest example is interest on short-term deposits); this is also necessary for competent management of accounts payable and receivable.

The payment calendar is a way to control the company’s liquidity. It represents the short-term cash flow schedule associated with all of the firm's activities. The payment calendar is necessarily linked to the enterprise’s budgets and most often involves daily detailing. To use this tool correctly, the entire budgeting system must first be analyzed (especially carefully in the part) and financial responsibility centers (FRC) must be clearly defined.

If you are interested in automation of budgeting, implementation of treasury or accounting according to IFRS, check out our.

The payment calendar can be implemented in various forms, but the list of required elements remains unchanged:

Get 267 video lessons on 1C for free:

information about receipts;
disposal data;
balance data.

It is this information that makes it possible to use the payment calendar as an analytical tool.

Stages of compiling a payment calendar

The procedure for drawing up a budget calendar can be divided into three main stages:

budgeting within the framework of BDDS;
formation of rules for making payments;
automation of the procedure for compiling a payment calendar.

At the stage of creating a BDDS, the budget structure is developed, the volumes and dates of cash receipts and necessary expenses are identified. This procedure is carried out in close cooperation with representatives of the Central Federal District. It is possible to use scenario planning. For each type of activity, net cash flow (NCF) is calculated.

One of the mandatory results of this stage is a balanced schedule of receipts and payments, eliminating the occurrence of “cash gaps”. At the same stage, it is necessary to identify reserves of unused funds and make a decision on their use (based on available opportunities and an assessment of the risks of the enterprise’s activities).

When forming the rules for making payments, it is necessary to accurately determine the powers and competencies of payment initiators, the mechanism for submitting applications, their approval or rejection. At this point, the procedure for compiling a register of payments for the day and week is regulated, the list of “protected” items is fixed, as well as the procedure for approving above-limit expenses.

As a rule, the following payment algorithm is used:

submission of a corresponding application by the initiator;
checking the compliance of the payment with the approved payment calendar (by the head of the financial service center or the financial service);
coordination of deviations from the budget if they arise;
coordination of payment with the financial service;
transfer of instructions to make a payment to the accounting department.

At the third stage, it is necessary to automate the passage of information flows related to the preparation of the payment calendar, making payments and the receipt of cash receipts. In this case, it is necessary to study the 1C databases used, regulate internal analytical forms and modes of access to information. For small-scale activities, it is possible to limit oneself to the use of corporate email and Excel.

An example of generating a payment calendar in Excel

A payment calendar is an important tool for effective cash management of an enterprise. Most often it is formed in Excel. Read how to create a payment calendar and save an example in Excel.

A financial director seeking to increase the value of the company cannot ignore the payment calendar, since this is a risk-free way to earn additional funds, strengthen financial discipline and identify problem areas in the structure of cash flow.

Why do you need a payment calendar?

The payment calendar in Excel is the main tool in the operational financial planning system. It helps to solve, first of all, the following important tasks:

forecasting cash gaps: allows you to reduce the amount of borrowed funds or develop measures to restore the required level of liquidity without attracting external financing.
maximizing interest income: receive an additional few percent from placing temporarily free funds in a short-term deposit instead of charging interest on the balance.
analysis of payment discipline of buyers/debtors: identification of unreliable payers through regular monitoring of deferred receipts.

How to Automate a Payment Calendar Using Excel

Download the Excel model to automate all the work of maintaining and filling out the payment calendar, as well as the payment register. Medium-sized companies may need minor adjustments, such as changing the composition of cash inflows and outflows. Large enterprises will find the model useful as a prototype for a future module for managing current payments of an integrated information system.

How to create a payment calendar in Excel. Example

Before you create a payment calendar, you need to take two key steps to develop the structure of the template: determine the data entry format and develop an interface for comfortable monitoring. This will require two worksheets.

Database sheet

To minimize the number of random errors (typos), data should be recorded in the form of separate operations with a unique set of analytics. The standard set for management accounting purposes consists of 10 analytics:

cash flow - receipt or payment (see also,);
Date of operation;
sum;
transaction item;

article code;
comment (explanation/transcription in free form);
counterparty;
chapter (major accounting section to which the transaction relates);
project;
financial responsibility center (FRC) .

A given set of fields can easily be supplemented if necessary (contract number, VAT, invoice, initiator and others).

Similar transactions are added by simple copying and minor adjustments (date, amount, counterparty). The values of all analytics (except for the “Amount” and “Comment” fields) are limited to lists to avoid duplication of names. And in the case of the name “Article” and “Article Code”, they are linked through the “VLOOKUP” function. Thus, adding a new operation takes only a few seconds. Filters are installed in the header of the table with the name of the analytics, which allows you to select several analytics and generate the required data array (Fig. 1).

Picture 1. Example of a database for a payment calendar (click to enlarge)

“Liquidity forecast” sheet

The “Database” sheet, for all its versatility with a wide range of information, is only an excellent assistant in the structure of the payment calendar, an example of which we are analyzing. The main role is given to the liquidity forecast schedule (Fig. 2). Visually, this is a standard cash flow statement, but with a time step of one day. Each transaction from the database finds its place in the liquidity forecast matrix. Data transfer is carried out through the “SUMSLIMS” formula with specified parameters by article code and date. Thus, any new operation is automatically reflected in the chart, and the situation when data is not translated due to input errors is excluded - there are no such problems in this model, since key analysts are specified through lists, which means that any operation will find its place on liquidity chart.

Figure 2. An example of drawing up a liquidity forecast in the payment calendar

Horizon and frequency of generating a payment calendar in Excel

The proposed sample payment calendar in Excel may go beyond the usual planning horizon of one month, since the entry order allows you to identify any operation. It is quite acceptable to monitor up to a quarter, but in this case there is a significant dependence of the quality of the forecast on the planning principles operating at the enterprise. In a situation where an organization has established a process for systematically updating planned data in all central financial districts, a quarterly liquidity horizon can be of high quality and useful for making management decisions.

The frequency of formation may vary, depending on the purpose of the payment calendar in the company’s financial management system, but data must be updated at least once a week, otherwise the information content and relevance of the forecast will be reduced to the level of a standard performance report.

How to Monitor Payment Calendar Corrections Using Excel

If the payment calendar in Excel is maintained by several people, a special change log will help track their edits. Thanks to it you will be able to control:

who made the edits – username (account);
what was corrected - the address of the changed cell, sheet;
how it was fixed – the value of the cell before and after the change;
when – date and time of edits.

In addition, the change log can be hidden from prying eyes.

What to consider when creating a payment calendar

A significant disadvantage of the payment calendar is the impossibility (limited possibility) of multi-user access. Unfortunately, today there is no technical solution to eliminate this problem. However, this barrier is successfully overcome by transferring the authority to maintain the calendar into the hands of an experienced employee of the economic planning department or the treasury. The advantages of this approach are obvious: having a single user of the system will reduce the number of errors to a minimum level, although it will take up a significant portion of working time.

Unlike a budget campaign, when each financial responsibility center creates its plan once a year and adjusts it quarterly, the payment calendar requires regular updating - weekly or more often). Formal collection of information from all central financial districts can be difficult, so economists must be deeply immersed in the situation with cash flow in all areas.

Receipts

Forecasting incoming cash flows from sales is by far the most difficult part of the job. If there are a large number of small, stable clients, it is possible to use the simplest assessment method - arithmetic average, when the planned volume of income for several periods is divided by the number of periods. Data can be updated using a number of indirect factors (seasonality, industry/market dynamics, influence of the current stage of the company's life cycle/strategy). Planning accuracy becomes more difficult when there are several large clients. In this situation, you should use the expert assessment method. Its essence lies in the fact that for each amount the probability of its receipt in three scenarios (conservative, optimistic, pessimistic) is determined and then a weighted average is derived taking into account the specific share of each scenario.

Payments

The date of many payments is determined without the participation of the payment initiators. It is useful to use empirical data, especially from established and long-standing suppliers. In addition, regular monitoring of payment deadlines under existing contracts would be a good addition (payment initiators have a strong habit of indicating payment dates earlier than the acceptable deadline under the contract). The same goes for paying taxes. A good practice may be to pay some bills a few days later than due. Such small tricks often go unnoticed by the counterparty, but you should not use such tactics in relation to key suppliers.

Assessing the quality of planning in the payment calendar

Measuring the effectiveness of any planning document is carried out by assessing the quality of forecasting. The payment calendar is no exception. set a maximum target deviation of the fact from the plan depending on the specifics of the business, the frequency of updating the report and the planning horizon. In the case of the payment calendar in Excel, an example of which we have analyzed, it should be noted that regularly exceeding the deviation norm will reduce the usefulness of such a document to a minimum, which means the company will not earn a few additional interest.

In practice, it often happens that the same digital financial centers are inaccurate. One of the options for dealing with weak “planners” may be the introduction of regular analytical reports for senior management, which will assess the lost resources due to poor-quality planning of individual central financial districts. Another way to motivate employees responsible for executing budgets is to fix permissible deviations in the map of key performance indicators to determine the size of the employee’s annual (quarterly) bonus.

The role of the payment calendar in the management accounting system

A high-quality payment calendar rightfully occupies a central position in the company’s management accounting system. Of course, the integrated use of this universal tool allows us to expand the boundaries of the classic purpose of monitoring liquidity problems. A logical continuation of the given vector for the formation of the structure will be the automatic preparation of a plan-actual report of the BDDS. To do this, you just need to enter a block of the cash flow plan on the chart (for a month or a quarter) and group the fact data to a similar period. In addition, as the database is filled, another powerful control and analysis tool is formed - the most flexible matrix of all monetary transactions of the enterprise with a high degree of data reliability.

Attached files

Example of a payment calendar in Excel.xlsx