Manage payments using Excel

Excel for financier

One of the most important tasks of a financier is managing the organization's cash flow . Large companies use specialized treasury systems to solve this problem, while Excel is a good choice for a small or growing organization.

Operational cash flow management is usually implemented using a special document called a payment calendar (financial calendar) . To manage payments in Excel you need:

collect information about planned payments;
structure this information (by cost items, financial responsibility centers and payers);
check for compliance with the approved cash flow budget ;
calculate the balance at the end of the planning period.

One of the simple examples that implements a payment calendar in Excel can be downloaded from the link: example of a payment register. This payment calendar is used to plan payments for a group of small companies (can be used for one company with different current accounts).

The example has two tables.

The upper table manually indicates the balance of cash flow for each payer at the beginning of the period. Expected receipts are also entered manually. The following columns automatically calculate total payment amounts and projected balances at the end of the period.

The lower table is a list of planned accounts. Key information: payment amounts, counterparties, payer, cost items, payment deadline.

Pay attention to a few handy “tricks”.

First . The bottom table is an Excel "smart table" . This is a very convenient tool for tables with a variable number of rows and columns: when adding rows to the end of the table, you do not need to “re-stretch” all the formulas, they are stretched automatically. In addition, addressing table cells has been simplified. Pay attention to the formula in cell D2: “ =SUMIF?(Payment Register[Payer], A2;Payment Register[payable]) “. Here “Register of Payments” is the name of the “smart” table (set on the Designer , this tab appears when a smart table is selected); “Payer” and “to be paid” are the names of the table columns. The formula searches the “Payer” column for the name of the payer from cell A2 and sums the values in the “payable” column.

Second . The main task of payment planning is to prevent a negative account balance (this cannot happen). Pay attention to column E of the top table. There these remainders are calculated, and conditional formatting : if the remainder is negative, Excel will tint the cell. To change payments, use the “paying?” column. bottom table. If it is 1, the payment is scheduled, if 0, it is not. The financial manager, by changing the values in this column, ensures that there are no negative planned balances.

Third . To eliminate errors in the lower table, partial verification of the entered data is applied. It is not possible to enter an arbitrary value in the “Payer” column; you can only select one of the values specified in the upper table. This is done using the Data Validation . In the same way, you can protect the columns “Business/division” and “Name of budget item” from input if you first create the corresponding directories. For “complete happiness”, you can protect the top table from changes.

Read more about the tools used in the Excel online tutorial:

About conditional formatting: Chapter 2. Working with cells: input, formatting

About data validation and cell protection: Chapter 4. Protecting data in Excel from unwanted changes

Vote:

The article was published in the journal “Economist's Handbook” No. 4 April 2020. All rights reserved. Reproduction, subsequent distribution, broadcast or cable communication, or bringing to the public attention of articles from the site is permitted by the copyright holder only with a mandatory link to the printed media indicating its name, number and year of issue.

End of article. Started in No. 3, 2020

How to avoid cash gaps?

How to create a payment register?

How to control the execution of payments?

How to establish budget control?

We create a payment register

The payment register (payment calendar) is the main tool of a financial manager. The interface of the “Payment Register” window is shown in Fig. 12. To create a payment register, a pivot table was used: tab Insert → Pivot table → To new sheet . The list of pivot table fields is shown in Fig. 13.

The payment register is a two-dimensional table, the columns of which contain periods with daily detail, and the rows contain grouped expense analytics.

Note!

To build a payment register, a complex (service) column “Counterparty+Application+Document” (for details of its configuration, see Part 1 of the article). It allows you to “compress” the registry. For interactive data filtering, slices are used: tab Insert → Slices .

Let's look at how to create a payment plan for the next (12th) week in the payment register.

The amount of all unpaid applications is RUB 663,350. (see Fig. 12). The register reflects the remaining amounts on applications (see the column “Balance on application, rubles” of the Application Journal (Table 4, part 1 of the article), i.e., partial payments on applications that have already been made are taken into account.

Since the financial manager is no longer interested in paid applications, applications with the statuses “Appealed” and “Partially” were selected (for more information about analysts, see Part 1 of the article).

The company has overdue applications (Fig. 7, part 1), therefore, in addition to the 12th week (March 18–24), the previous weeks were also selected using a slice - the 8th–11th, which have payment deadlines. We will not plan payments between the companies of the group, so in the Group filter we select External (Fig. 12).

According to the head of the financial service, the available funds to pay for all applications (663,350 rubles) are not enough. Therefore, for the 12th week we planned to pay for applications only with high priority (Table 5). But even in this case, the amount of payments remains significant - 593,400 rubles. Of these, overdue applications No. 4015 and 4020 will be paid. The main amount of payments will be for salaries (420.5 thousand rubles) and taxes (34.9 thousand rubles).

To reduce the risk of cash gaps, the financial service:

contracts with buyers provide for conditions for prepayment;
transfers certain categories of clients to prepayment payments;
opens current accounts in at least two banks. This will allow you to quickly send client payments to an active current account if one of the banks loses its license;
when sending financial resources to a bank deposit, enters into an agreement with the right to early withdrawal of funds;
enters into an overdraft or factoring agreement with the bank;
draws up loan agreements with the owners of the company or affiliates;
enters into assignment agreements, for example transferring debt to a group company that is currently more solvent.

For your information

With regular monitoring of the debts of holding companies among themselves, the financial service often discovers counter-obligations. Experienced financial managers do not pay them off in full - a partial balance of the debt can be used in the future to solve problems of cash gaps.

It is possible to quickly raise funds under an assignment agreement if, on the one hand, the receivables under the agreement for the assignment of the right of claim (debt transfer) to a third party are reflected, and on the other hand, loans provided earlier for the implementation of the project.

In case of sudden cash gaps:

assess the possible consequences of non-payment - fines, penalties, blocking of the current account. Penalties in contracts with suppliers are their right, not their obligation, so a slight delay in payment often does not entail fines and penalties;
ask buyers to repay the debt, make early payments or make an advance payment;
suspend payments to suppliers, while sending letters of guarantee indicating the payment deadline;
if the company is organized as a group, the problem of the cash gap is smoothed out by the operational intra-company cash flow.

After the problem of cash gaps has been resolved, it is necessary to replenish reserves (for example, direct funds to a deposit), and also take into account the partnership actions of suppliers and buyers that helped the company overcome the deficit and stimulate further cooperation (for example, repay accounts payable to the supplier).

Thus, the supplier of RTI OJSC was excluded from the register of high-priority payments (see Table 5), and he agreed to extend the payment terms without penalties. But after the cash gap is successfully eliminated, this supplier is paid the entire amount of the debt - 24,400 rubles. (Table 6).

The opposite situation is also possible - cash receipts exceed plans. In such cases, it is necessary to effectively use temporarily free money: purchase more materials (when ordering a large batch, some suppliers provide discounts), send it to a deposit or invest it in investment projects.

In our case, for example, it is possible to speed up the payment of existing applications for two investment projects (and therefore their implementation; Table 7):

42 “Construction of production building No. 3”;
45 “Modernization of the mass procurement workshop.”

Maintaining a register of documents

It is convenient to fill out the document register in the “Smart Table” of Excel if you do not intend to use merged cells.

Next, we will look at a registry with employee data, which can be used to store this data and print various documents. In practice, this was a register of Employment contracts, from which the following were printed: Employment contract, Appendix to the employment contract, Agreement on financial liability, Order for employment, Amendments to the employment contract, Agreement on termination of the employment contract.

In the example, the register is significantly reduced; only the Liability Agreement is left from the printed forms to show the very principle of maintaining the register and filling out the printed form with data.

Example of a document register in Excel Smart Table

To fill the registry with data, you can use an autoform, more about which you can read in the Working with Smart Table section.

Sample payment register

The payment activity register represents a list of invoices, invoices, and expense lines that an organization plans to reimburse . The payment register represents the most important element in the system that allows you to manage payment activities. An enterprise usually operates two registers - cash register and accounts register .

In a general sense, the payment register represents a list of justified and submitted applications that need to be executed on a certain date.

The first thing to consider in the process of forming the list is the absence of cash gaps . That is, planning activities must be carried out within the available funds, without debts and obligations.

Traditionally, available resources are represented by balances on current accounts and in the cash register, as well as planned income throughout the day. Sometimes an organization uses bills of exchange as payment instruments.

Basic details and regulation of payments

Payment actions are accompanied by the execution of documents, one of which is the payment register. If funds are accepted in cash, the process is regulated by Section 2 of Bank of Russia Regulation No. 199-P, adopted on 10/09/02.

This norm deals with the direct procedure for conducting operations at the cash desk of credit institutions on the territory of the Russian Federation.

In accordance with this, the legal entity deposits cash proceeds into a bank account at the cash desk.

If payments are made using bank cards, Bank of Russia Regulation No. 23-P “On the procedure for issuing and making payments” plays an important role.

The procedure for closing and opening, as well as organizing exchange points and carrying out certain transactions, must be special, at the same time, no prohibitions or restrictions on transactions are expected .

Rules for collecting and processing applications

Through the formation of applications and their competent collection, the following points :

ensure verification of the contents of applications that have been approved before direct export to the client bank;
compare the total amounts of approved applications with the amounts of the balance available on the current account;
select all or part of the payments for export.

An important role is played by the use of the Client-Bank service to go to the registry, within the program, a current account is selected, and a click is made on the registry link. The window that pops up subsequently displays all agreed payment actions that are subject to direct payment from the account that the user has selected.

At the bottom there is information on the total amount to be paid. Using this system, individual applications can be selected . To do this, you need to click on the “select” tab or hold down “CTRL” and click on the cell containing the payment amount. In the latter case, not only will the application be highlighted, but data on their amount will also be obtained.

Maintaining in 1C with examples and postings

Maintaining in 1C involves compiling records of business transactions. They all look like this:

Dt 50 Kt 90(1) – reflection of receipt of revenue to the enterprise’s cash desk;
Dt 90(2) Kt 68(2) – calculation of value added tax;
Dt 90(4) Kt 41(2) – reflection of the cost indicators of goods;
Dt 50 Kt 51 – receipt of funds from the current account to the cash desk;
Dt 55 Kt 51 – transfer of funds to another bank from a current account;
Dt 51 Kt 57 – receipt of funds in transit to the current account.

In general, to reflect business transactions, accounts 50 and 51 are traditionally used in various entries and variations.

Execution of the payment calendar

Financial planning is the most important task in an organization’s activities. One of the tools for settlement actions is the payment calendar, which helps to quickly solve a number of problems.

Reduction of forecast options into a single task.
Synchronization of cash flows in order to improve the efficiency of cash flow.
Weakening the priority of payments based on the criterion of their impact on business results.
Ensuring cash flow liquidity and increasing solvency.

Proper execution of the payment calendar guarantees the absence of debt formation and problems with the law. In addition, it provides the enterprise with a large number of advantages in the form of benefits, security, efficiency of transactions, and also allows you to solve many current and planned tasks.

You can learn how to create a payment register from this video.

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