1. Accounting for all payments by date

The calendar takes into account all types of payments: transportation payments, salary and loan payments, etc.

2. Automatic report generation

The report is automatically generated based on the planned payment date, which is displayed in the application form in the “Mutual settlements” section.

How to use the payment calendar?

2. In the form that opens, select the period for generating the report and the principle of its construction: “By contractors” or “By organizations”. Filter payments using selection buttons: partially paid, overdue, important, unpaid.

3. The report displays only payments by dates on which planned payments were registered - in the application or manually.

Planned payments are highlighted in green, which paid counterparty in the selected period, blue - partially paid, and yellow - payments that not paid.

The balance of funds comes from the date of construction of the calendar.

If you plan many payments with one counterparty or from one organization, use the “Counterparty” or “Organization” filters.

Open the report to the applications: you will see the TTN numbers for each shipment, as well as the dates and numbers of the invoices issued.

4. The “Balance (planned)” section helps to avoid cash gaps in the company: you see how much more or less money will be in the cash register on the selected date.

How to schedule a payment that is not related to freight transportation?

• in the "Payment calendar";

• in the journal “Planned Payments”.

Let's consider the second option:

1. Find the “Planned payments” tab in the “Accounting” section.

2. Launch the required form - click “Schedule receipt” or “Schedule expense”.

3. Now fill in all the required fields - they are highlighted in red.

4. If the payment is recurring monthly, check the “Monthly payment” box so you don’t forget about it.

5. To highlight the payment amount in the calendar in red, check the “Important payment” field and click “Post and close”.

6. The important payment is now highlighted in red.

You have entered a scheduled payment.

Today, the main problem for many enterprises is the search for free financial resources. After all, the inability to attract them leads to a decline in production, and a decrease in the level of income of the population and consumer demand leads to a reduction in enterprise revenues and an increase in accounts receivable

Operational planning

During an economic downturn, the terms of supply from enterprises become more stringent: to minimize risks, companies prefer to work on an advance payment basis. Therefore, the shortage of cash (hereinafter referred to as CF) becomes the main problem that an enterprise has to solve in order to ensure its liquidity and solvency in a crisis reality.

A mechanism that allows you to control the company’s liquidity and make the most productive use of DS is the payment calendar.

Operational planning of cash flow for a certain period is carried out by drawing up a payment calendar within the framework of the cash flow budget (hereinafter referred to as the cash flow budget) based on the current state of payments, concluded contracts, signed agreements and actual obligations.

From a budgeting point of view, the payment calendar is a system for reserving funds from the plan. But unlike the BDDS, this is a more detailed document. After all, if with strategic planning it does not matter from whom exactly the money will be received or to whom the money will be paid, then with operational planning it’s all clear: here are the debtors, and here are the creditors.

Therefore, in the payment calendar you can describe in detail: to whom, when, from whom, for what and how much should be paid (received).

The payment calendar is a cash flow plan (hereinafter referred to as the cash flow plan) in the short term (week-month), reflecting all types of activities of the enterprise (core, financial, investment), approved by management within the limits and capabilities of the enterprise.

Who needs a payment calendar?

The information presented in the company’s payment calendar is necessary for business owners, top and middle managers, heads of financial responsibility centers (hereinafter referred to as the FRC) and employees of the financial and economic block.

From a budgeting point of view, the payment calendar is a system for reserving funds from the plan

The range of issues within the competence of the financial director, one of the most important managers of the enterprise, is unusually wide - from the tasks of operational cash management to the problems of strategic development of the company. In addition, in any enterprise, financial management is closely related to its core activities - production, trade or provision of services, and therefore to resource management. The introduction of a payment calendar will reduce the labor costs of the financial director to control the expenditure of funds. If previously he had to review and sign each application for payment, then with the introduction of a payment calendar, when the payment amounts are approved in the budgets, and the procedure for approving payments is formalized, control of cash flows can be entrusted to the employee financial service. The financial director will only approve a limited number of payments, usually over-limit, large or irregular. For example, it is enough to agree on the amount of payment for office rent once when approving the budget, leaving control of the payment procedure itself and the compliance of the amounts with the budget with the financier.

Properly structured business processes help to minimize the risk of abuse by company employees by separating the functions of monitoring payments and their initiation. For example, the head of a business area accepts all requests for payment in his financial center and is responsible for implementing the budget, and a financial service employee monitors the compliance of requests with budget limits and the implementation of regulatory procedures of the payment system.

Problems that a payment calendar will allow you to solve

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

Scheduling of DO flows assumes the possibility liquidity management companies. The liquidity indicator of an enterprise takes into account the condition and value of its current assets and liabilities.

The payment calendar is a cash flow plan in the short term, reflecting all types of company activities, approved by management within the limits and capabilities of the enterprise

The main components of current assets are inventories, accounts receivable and VA, current liabilities are accounts payable.

The payment calendar allows you to manage accounts payable and receivable. This is especially true when the number of buyers or suppliers increases, since it becomes possible to clearly define payment dates for the timely receipt of raw materials and services, ensuring the efficiency of the enterprise, and preventing the occurrence of penalties.

Managing DS related to inventory immediately poses the task of managing inventory turnover. The faster it is, the fewer warehouse stocks, the cheaper they cost the company, the more effectively the DS are used to purchase the necessary raw materials, supplies, and goods for the main activity.

When there is a shortage of “real” money, the clear work of the enterprise in agreeing on a payment plan at all levels of management and competent, thoughtful prioritization are especially important.

In practice, a situation often arises when the revenue plan is not fulfilled, but the DS expenditure plan is fulfilled in full; accordingly, the total amount of requests for payment exceeds the actual receipt of DS. To avoid cash gaps, it is advisable to rank all payments according to their priority or importance. Payment for items with the highest priority is mandatory, for items with a lower priority, subject to additional conditions. For example, requests for payment of debts to major suppliers of products and taxes are satisfied first of all, while expenses for training and modernization of office equipment are financed when the sales plan is fulfilled by at least 90%.

In this regard, the analysis of so-called fixed payments is very useful: enterprises often have expenses that they are accustomed to and do not question their appropriateness. A fresh look at your fee structure will help determine whether these expenses are truly necessary. Drawing up a DDS plan only makes sense when you can be sure that all necessary payments have been taken into account. The plan is drawn up in order to eliminate the need for “sudden” financing of any “super important” projects. It is necessary to think through the directions for spending VA in advance; in a crisis situation, it is appropriate to introduce more stringent deadlines for agreeing on a payment plan at all levels of enterprise management.

As for the execution of the plan, the practice of daily reconciliation of the cash balance of DS deserves attention, which will eliminate possible abuses and give managers reliable information about the current balance of funds in the accounts and cash register of the enterprise, which is necessary for making decisions on making current payments.

Principles, rules, sequence of formation

When building a cash payment system, a company should highlight two areas of work. First — formation of financial plans in the medium term, DDS items and other analytics (organizations, contracts, counterparties), limiting DDS payments. The result is a document - “Payment calendar”. Second — description of the movement of applications (payment registers) in the context of process participants, time frames, which is considered as a business process of the enterprise aimed at implementing the “Payment Calendar” document from the point of view of interaction between departments and management of the enterprise, requiring clear coordination and close attention from managers and executive personnel.

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

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.

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

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

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

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

1. Formation of 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 actual plan).

2. Determination of the list of analytics

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

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.

When designing a business process, you must follow certain principles:

• sufficiency of powers and functions of participants in the process (this means that each of the coordinating and approving parties has the right to make one or another decision on payment);
• organizational security (any business payment or application for receipt must have a single body of approval and approval, and not a single unit whose participation in the process is envisaged can be excluded from the cycle of generating the payment calendar);
• possibility of control function (all applications must have a sufficient level of payment verification to avoid unauthorized payments);
• dynamism (the cash payment system must have an optimal approval procedure that meets the requirements of the enterprise’s payment discipline (usually lasting from two to five days), and the payment approval process is optimized in accordance with work with suppliers).

The most visual representation of a business process is graphical, in the form of a diagram (figure).

5. Regulation 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 approval 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, payment requests or registers);
• generate electronic reporting necessary to control the execution of payments, compliance with the regulations of the payment system, BDCS (for example, the 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.

Controlling cash flow is the main task facing the financial department of any enterprise. At the same time, a tool that allows you to manage cash flows in a visual form is the payment calendar of the enterprise or the payment schedule of the organization.

Drawing up a payment calendar

The payment calendar is the most useful and frequently used tool of the treasurer in terms of operational financial planning, which allows you to obtain comprehensively detailed information on the balances and movement of cash resources in the future for an arbitrarily set period of time.

It can be developed both in terms of individual cash flows and for the company as a whole.

Historically, in many enterprises, the preparation and maintenance of a payment calendar is carried out using spreadsheets in Excel (download an example of a payment calendar in Excel). This method, which has proven itself over many years, allows for basic financial planning, since it strongly depends on the “human factor”. An advanced option that allows you to unlock the full potential of such a tool as a payment calendar is to compile and maintain it using an automated financial system.

The company's payment calendar compiled in a specialized program developed on the basis of "1C: Enterprise" is essentially a cash flow plan for a certain period with the necessary level of detail sufficient for making decisions on cash flow management (CFM).

Preventing cash gaps

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

Figure 1. An example of a payment calendar in the professionally specialized program “WA: Financier”.

Information about the forecast cash flow with possible cash gaps facilitates the prompt adoption of measures to prevent this situation.

Very important for using this cash flow management tool is its interactivity and the ability to customize analytics of any depth in a useful context.

The ability to transfer a planned payment directly in the form with a prompt change in the situation according to the plan for receipts and expenditures of funds gives the user a clear picture of the situation regarding changes in the cash flows of the enterprise.

Customizable tool groupings provide the user with the level of detail that he really needs (from summary turnover for each application to detailed ones).

Using information about the minimum balance can be an effective mechanism for accumulating amounts in an account by a certain date (for example, to pay taxes or pay wages).

The result of optimizing the payment calendar is an orderly cash flow plan (forecast) in which there are no cash gaps.

Payment order

Based on this data, a payment register and a payment schedule are formed, with the help of which payment orders are created to the bank.

All these requirements are met by the payment calendar implemented in the 1C-based software product - “WA: Financier. Cash management."

The payment calendar in the system is an interactive tool with which the treasurer manages the cash flows of the enterprise.

Figure 2. An example of a payment calendar in the program “WA: Financier. Cash management."

The payment calendar form consists of the following areas

Figure 3. Report settings area in the “WA: Financier” program. Cash management."

In the payment calendar settings you can:

All these functions give the user the opportunity not only to manage data output, but also to customize the structure of the payment calendar, set selections, and create a payment register. System settings created by the user once can be either saved and used by the user himself or copied for others.

In accordance with the user’s settings, the company’s payment calendar is generated with varying degrees of detail.

Figure 7. An example of payment calendar detail in the “WA: Financier” program. Cash management."

The data area displays information about the planned cash flow or payment schedule; areas within which the treasurer can move the payment date for applications without violating the terms of the contract are visually displayed. Moving an application to another date does not require editing the document. The user simply “drags” the application to another date. In this case, the cash flow plan is automatically recalculated by date. The user can drag and drop orders both between date intervals and between places where funds are stored.

If information about minimum account balances is required for planning, the user can enable the display of this information by selecting the appropriate item in the payment calendar settings.

Figure 8. Entering information about the minimum balance of DS in the “WA: Financier” program. Cash management."

To enter data on the minimum balance of funds, a special assistant is used, called by the button

Once cash flows are “normalized,” the user can apply changes to the system. After the changes are accepted, the schedule of scheduled payment dates will be recorded in the applications.

Based on applications, you can create a register of payments or directly generate payment orders to send them to the system for interacting with the Client-Bank program.

Introduction

It is assumed that the reader already knows what a payment calendar is and why it is needed; therefore, only practical issues of organizing a payment calendar will be discussed further. In principle, if you have at least a small budget for automating the payment calendar in 1C, you can boldly skip the next article and go here . In addition, it is desirable to have a minimum knowledge of budgeting terminology (understanding of terms such as “budget”, “limiting”, “CFD”). If all of the above is clear to you, I suggest you decide whether the approach that involves maintaining a payment calendar in MS Excel is generally correct? There are different directly opposite opinions, because... It's not that simple here. Let's try to be objective. If you have a software product in which you maintain operational or accounting records, then most likely it is better for you to maintain a payment calendar in this product, even if its functionality does not fully satisfy you. If we are talking about 1C, then perhaps it makes sense to modify it in order to maintain a payment calendar in the database. But, as always, there are exceptions to the rules, which you can find out about at the end of the next block of the article.

Pros and cons of using MS Excel as a platform for organizing a payment calendar

Before talking about organizing a payment calendar in MS Excel, I want to list the main disadvantages of this approach.

• The problem with the multi-user operating mode is that you can organize some kind of multi-user mode in MS Excel if we are talking about two or three users, but if there are more users, it is better to think about the database;
• Access control and confidentiality - all passwords in MS Excel are very easy to break; if desired, even a person far from the IT industry can easily find several such methods in open sources. Thus, access to the file should only be given to trusted people;
• Re-entry of data - unlike 1C, there are no ready-made exchange processing between MS Excel and the bank client, of course it can be done, but it will be cheaper to modify the database you are using in order to maintain a payment calendar in it. However, for small volumes of operations, the use of processing does not provide significant savings in labor costs;
• Limitation on the size of the database - MS Excel will not handle a large data array, although MS Excel is sufficient for a medium-sized company.

• The volume of payment transactions per day does not exceed 20-30 documents;
• No more than three trusted persons work with the file;
• You do not have payment calendar functionality in your accounting or operational accounting software product.

I hope it is clear that the above conditions are not a dogma, but allow us to “feel” the acceptable limits.

Now a few words about the nuances that can outweigh the disadvantages of organizing a payment calendar in MS Excell. The fact is that in addition to the payment register itself and the payment calendar, this file can also perform additional useful functions - for example, drawing up budgets and limiting payments relative to budgets, and the shortcomings of MS Excel in re-entering data can be completely compensated for by organizing a two-way exchange with the 1C payment subsystem. The fact is that if you are going to work closely with budgeting, then the relative effectiveness of MS Excel in comparison with specialized budgeting software products can easily outweigh the disadvantages of using MS Excel as a payment calendar platform. Therefore, the example discussed in this article immediately contains elements of budgeting, namely limiting payments (without this I see no practical point in using MS Excel to organize a payment calendar). If you have a budget system in MS Excel, then by setting up the translation of payment limits into the payment calendar using fairly simple formulas, you will receive a cheap, simple and effective budgeting tool. Now, having clarified the pros and cons of the MS Excel payment calendar, you can decide for yourself whether to use MS Excel for the payment calendar or not.

I also want to immediately warn you that the purpose of the article is not to describe the process of developing a ready-made payment calendar that you can take and start using, because in this case the article would not be needed, it would be enough to take the attached file. And this would no longer be an article, but a book. Therefore, the purpose of the article is to show the methodology and principles of creating a payment calendar, taking into account the practical nuances of setting up tables and formulas in MS Excel. In the example under consideration, several simplifications have been made so that you can understand the example more easily and quickly, but I promise that if you have minimal knowledge of MS Excel and some time to carefully study, then you can customize the given example for yourself and it will work .

Concept of setting up a payment calendar in MS Excel

The concept is as follows:

• We create key classifiers on separate sheets of the MS Excel book (cash flow items and financial statements; for a working example, you may also need, for example, counterparties, organizations, bank accounts/cash desks);
• We set the correspondence between the articles of the DDS and the Central Federal District;
• We create a sheet with a cash flow budget (CFB);
• We create a sheet with a register of payments (this sheet will be the main workplace);
• We create a sheet with a register of increasing limits (for extra-budgetary requests for cash expenditures);
• We create a sheet with a data table that will collect all the data necessary for the payment calendar;
• We create a summary table “Payment calendar” - this, in fact, is your goal.

I will also add that you can additionally configure the uploading of payment orders into 1C, and the loading from 1C of the fact of payments and account/cash balances. In addition, you can set up the exchange of directories with 1C, especially such large ones as counterparties. The process is creative - but if you have an excess of time and resources, the main thing is not to get carried away with the polishing process, because MS Excel still has a lot of shortcomings, and it may be better to direct your creative energy in this case to transferring the payment calendar into a normal database.

We create classifiers

For each classifier we create a separate sheet, in our case these are the sheets “Central Federal District Classifier” and “DDS Article Classifier”.

There is another nuance with the directory of DDS articles - in many cases the full version of the classifier is not needed; the full version understands the presence of groups and income items. Sometimes you just need a list of DDS items for payments. For these purposes, create an additional sheet “Items DDS_Payments”

Let the full version of the directory of DDS articles “live” on the sheet “Classifier of DDS articles”.

After filling out the payment items, create a named range-list (formulas tab - Assign a name).

Values ​​in the range DDS_Payment Items can be specified using links from the full classifier of DDS articles.

An important step is linking DDS articles with the Central Federal District. In addition to the fact that this is the right step from the point of view of the methodology for managing an enterprise through budgets, it will also greatly facilitate the entry of payment data and will reduce the number of errors. Technically, this opportunity is organized quite simply - a table is created on a separate sheet, the columns of which are named by the names of the CFO, and the DDS articles corresponding to the CFO are entered in the rows.

After this, in the previously shown way (by assigning a name to a range), its own named list is created for each CFD.

In addition to the listed classifiers, it is also necessary to create a list of planning periods, which will serve as a separator for budget periods. In particular, this list is useful when entering a cash flow budget, increasing limits and adding requests for spending funds.

Budget (limits) management

In order to control current payments for compliance with the budget, you will need two tables - “DDS Budget” and “Increase in Limits”. Let's create sheets of the same name and place these tables on them.

To ensure that the CFD and planning periods do not need to be typed in each time, you can add functionality for selecting values ​​from the list. Let's show this using the example of the “CFD” column - highlight a obviously large range of CFD (for a training example, 20 lines are enough, for a working example, you need to provide at least ten thousand lines). After selecting the range on the “Data” tab in the “Working with Data” button block, click on the “Data Validation” button, in the dialog box that opens, in the “Data Type” field, select “List”, and in the “Source” field enter = and the name of the named list, in our example it will look like this: = CFO.

After these manipulations, the CFO field will be filled in with a selection from the list, which is undoubtedly very convenient.

The selection in the “Planning period” column should be configured in the same way.

However, with DDS articles everything is a little more complicated. Selecting from a complete list is inappropriate, and here we will need the ability to select values ​​from a subordinate list.

By specifying in the source field not the name of the list, but the function INDIRECT(), which returns a list by name, we will be able to display for selection only those articles that relate to this Central Federal District!

If you want to control payments for their compliance with the budget (limiting payments), then you will inevitably face the task of reflecting the fact of increasing limits. For these purposes, on the sheet “Increasing limits”, create a table that will contain registration fields (number and date), as well as meaningful fields (CF, DDS item, Planning period and amount). In addition, you can add custom terms, such as “Reason for increasing the limit.” Setting up the selection of the Central Federal District, Planning Period and DDS Items is done in the same way as we did when developing the DDS Budget table.

Payment register

The payment register is a table in which planned, completed and canceled payments should be recorded. If there are payment limits, you will be able to see your budget balances, as well as set up a filter that will provide you with a payment approval tool for a specific date.

You should take the most responsible approach to the design (convenience) of this particular table, because... you have to work with her every day. In order to at least a little demonstrate the capabilities of MS Excel in terms of user-friendliness of the interface, the example includes a macro that helps simplify the selection of dates, below the functionality of the macro will be shown in action, but you can see the text of the code itself in the example, the article will go into detail on this makes no sense - especially since dates can easily be entered manually.

In order to register transactions, create columns “Application number”, “Application date”, “Payment number” and “Actual payment date”. From the point of view of limiting payments (budgeting), the key columns are “CFA”, “DDS Item”, “Planning Period” and, of course, “Amount”. The column “Budget balance if we pay” should be calculated - i.e. calculated automatically to inform the user about the current state of the budget.

The “Desired payment date” column is necessary for the convenience of approving payments (in the working version, it would be advisable to add another “Payment deadline” column). To record the fact of payment approval, add the columns “Approved” and “Planned payment date”. By filtering the table by the parameters “Paid” = FALSE, “Approved” = TRUE, you get a table in which you can create a payment calendar by changing the planned payment date. By adding the filter “Planned payment date” = current date, you will receive a list of payments for today.

Let's discuss technical aspects. Using macros, as mentioned above, you can simplify entering dates by making it possible to select them from the calendar by double-clicking on a cell.

Set up the CFO and Planning Periods by selecting from ranges of the same name, and set up the selection of DDS items from subordinate lists in the same way as was shown in the section on BDDS. Probably the most difficult moment is calculating the budget balance after the application. To do this, you should use the excellent SUMIFS() function.

The formula should “pull out” the DDS budget (the main limit from the “DDS Budget” sheet) using the link CFO-DDS-Item-Planning Period, add to it an additional limit from the “Increase in Limits” sheet and subtract all previously approved amounts in the “Register of Payments” table according to the data CFO-Article DDS-Planning period, including the current line. You can read exactly how to use this formula in the MS Excel help - everything is explained there quite clearly.

Payment calendar

A payment calendar implies a schedule of cash receipts, payments, and balances by period (usually days). In MS Excel, the “pivot table” tool is ideal for a graphical representation of the payment calendar; below is an example of what we should get as an “output”.

However, in order for a pivot table to be generated, a table with data must be prepared. In our case, for these purposes we will need a four-dimensional data table with the “Sum” resource.

The measurements are as follows:

• Date – columns will be grouped by this date
• Type – four possible values ​​– initial balance, income, expense and final balance.
• Indicator – for the “Expense” type this is a DDS item; for other types the indicator duplicates the type itself (initial balance, income and final balance).
• Counterparty – counterparties for planned expenses will be displayed here.

The proposed example assumes that the initial opening balance and planned receipts will be entered manually by the user (the fields are highlighted in yellow). The remaining fields will be calculated. Before you start constructing the table, decide on what time horizon for cash flow planning in terms of days you will rely on. I chose 9 calendar days. The first lines corresponding to the planning horizon must be fixed. For each day I create three lines, with the initial balance, receipt and final balance, then the same lines for the next date and so on until the end of the planning horizon.

Place the consumable part under the fixed blocks. This data will change dynamically depending on what you have entered into the “Payment Register” table.

Now a little about formulas. The first two lines, relating to the first planning day, are filled in manually. This is the initial balance and the planned income. In the working version, it is advisable to configure the incoming balance so that it is calculated based on real cash balances, taking into account money in transit (a table periodically loaded from the same 1C will do). The receipt field contains the projected amount of funds that you expect to arrive on the first date. In terms of income, the same applies to the remaining fixed lines - the question of what to put in the forecasted income is decided for each enterprise separately. The specifics of retail chains make it possible to create calendar profiles of projected revenues; for design organizations, a revenue schedule is drawn up individually for each act/stage, etc.

The principle of the formula for calculating the final balance is simple - we take the initial balance of the calculated date (equal to the final balance of the previous day), add the forecasted income and subtract all the expense amounts that were selected by the SUMIFS() function by the current date in the block containing the expense part.

How to use the developed payment calendar.

Working with the payment register, you indicate the planned payment dates. In the payment calendar data table, enter the planned cash balance and projected receipts. After that, go to the “PC” sheet and click “Update” on the “Payment Calendar” summary table. After this, you will receive a payment calendar in a visual form.

Let’s say you are not satisfied with the current schedule, for example, you want to avoid a cash gap on October 16 - go to the “Register of Payments” table, change the planned date of payment for the application (which is 600 thousand rubles) to, say, October 18.

You update the pivot table with the payment calendar again and it moves the payment of 600 thousand rubles. on 18.10 and recalculates the beginning and ending cash balances!

Everything is simple and effective!

P.S.:
The picture for the announcement is the famous Mayan calendar, which either ends or does not end in 2012. But what is noteworthy is that its very presence makes it clear that ancient people were already dealing with the topic of calendars. The Mayans definitely didn’t have payment calendars, because they had war communism and didn’t have money, but I’m sure that if they had capitalism, now we would find clay payment calendars during excavations :)

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