How to create a table for accounting income and expenses in Excel: step-by-step instructions

Goals and objectives of cash accounting

Accounting for amounts in the form of cash or non-cash assets is necessary to obtain information about the availability of the necessary funds at the moment in the organization. This makes it possible to conduct economic activity and redistribute finances to the necessary areas of use. Process objectives:

• the presence of documented records indicating the movement of finances of an economic entity, that is, an enterprise;
• compliance with all legal regulations and maintaining the necessary records, which indicate the absence of various violations;
• redistribution of funds for specified purposes that are most important at the moment;
• inventory and control;
• settlements with various individuals and organizations.

When receiving information about the availability of the required amounts through reporting, the organization makes settlements with various legal entities and individuals, including employees or creditors.

PBU 23/2011 includes the entire theoretical basis. The document used for this procedure has Form 4 (Cash Flow Statement). Among the main features is the need for an organization to maintain separate reporting when there are several taxation options.

Sample Cash Flow Statement

Another important task is the analysis of information regarding taxation, since the report makes it possible to obtain data on actual costs for a specified period.

Order of the Ministry of Finance of the Russian Federation dated 02.02.2011 N 11n “On approval of the Accounting Regulations “Cash Flow Statement” (PBU 23/2011)

What are cash flows and their balances

Cash Flow is the receipt and payment of cash and cash equivalents. Cash flow is not considered to be any change in the form of money: the exchange of cash for cash equivalents and vice versa, the purchase or sale of currency, the transfer of money from one account to another, the withdrawal or deposit of cash. But only the change in form itself: if the amount of money changes, the difference forms a separate cash flow.

Cash Flow Balance is the difference between receipts and payments. If there were more receipts during the reporting period, the balance is positive. If there were more payments, the balance is negative.

Example 3.

Yesterday the seller sold the product for $100 and received payment. The rate was 69 rubles. for a dollar. Today he exchanged dollars for rubles. The course is already 70 rubles. for a dollar. Cash flow (receipt) yesterday amounted to 6,900 rubles. Today there was no cash flow due to currency conversion, but due to the difference in exchange rates, an additional flow of 100 rubles arose. The total positive balance of cash flows amounted to 7,000 rubles.

In some months the cash flow balance may be negative. If the business has fat in the form of account balances and other types of money, this can be survived. Especially if profit is fixed at the same time. But a persistently negative cash flow balance is a warning sign. It means that debtors live at your expense. If possible, this should be avoided.

Accounting for an organization's funds

Accounting is considered to be control over the fact of receipt or use of funds. This highly liquid asset includes:

• money in the bank;
• cash on hand;
• money issued on account;
• other assets that have a high level of liquidity.

Reporting is generated by the financially responsible person.

Attention! Any non-cash movement must be recorded using a special record.

If cash payments are used, then such an operation is performed using cash rules. It is possible to maintain cash reporting using a simplified form that is available to small entrepreneurs or individual entrepreneurs.

The formation of the process and reporting itself is carried out using a number of options:

1. Cash desk - 50.
2. Estimated - 51.
3. Foreign exchange - 52.
4. Bank special accounts - 55.
5. Transfers on the way - 57.

It turns out that when receiving or sending money it is necessary to use the established reporting form indicating the option.

Read also: ​​Signs of business fragmentation

Income and expenses accounting table in Excel

Nowadays there are a lot of different applications developed for smartphones, but it seems to me more convenient to use a table for recording income and expenses in Excel.

Because if this is your first time focusing closely on your budget, then using paid applications is not your option. The reason not to buy expensive services is simple human laziness; you’ll spend money but won’t use it. And laziness is the main enemy on the way to systematizing expenses.

But a habit is developed in 21 days, so you just need to start and take small steps towards your goal - reducing costs.

A table for accounting income and expenses in Excel will allow you to independently add and remove the necessary columns, columns, and positions. You do everything for your convenience and without the constant appearance of intrusive advertising, which endlessly pops up in free applications. So, let's begin to analyze step by step where to start and where to click.

Creating an Excel table “Income”

At the end of the article, you will be able to download the resulting file, in which it is easy to keep track of expenses and income in Excel, but if you want to figure it out and do it yourself, then first you need to create an Excel file where there is absolutely nothing yet.

First, let’s create the “Income” table by holding down the left mouse button and selecting the required area. By clicking the “Borders” button and then “All borders”, the required area will be highlighted. For me it is 14 columns and 8 rows.

The table is ready, you need to fill it out. In the summary column “Income” you need to list all the income you may have and not forget anything, for the accuracy of the calculations.

By adding the resulting lines “Total” and “Total” you will immediately see how much the total family income is per month and for the year as a whole, as well as for each item. Let's fill them in with a summing formula for automatic calculation.

To do this, in the required column or row, write the following combination without spaces “=SUM(”, then select the area necessary for calculation, for example, from January to December and press Enter. The formula bracket will close automatically and will count when filling out these lines.

I filled out the table for greater clarity and highlighted the resulting rows in bold so that they would immediately catch the eye and not be confused with the general list.

Creating an Excel table “Expenses”

First, let's create new sheets in our file in order to record home accounting of expenses and income in Excel by day of each month, because it is better to enter information every day a little bit than to then sit for half a day and remember what was spent and where. And it’s not at all a fact that you’ll remember it exactly.

To do this, click on “Plus” at the bottom of the sheet and a second sheet will be added. Let’s immediately rename it and call it “January”. To do this, double-click on the “Sheet2” inscription with the left mouse button and it will become active for correction. I’ll similarly correct “Sheet 1” by writing “Income and Expenses”.

There is no point in immediately adding 11 more sheets for other months, because we will simplify our work by creating one and simply copying the rest.

We create the table borders in the same way. I'll highlight 31 columns and 15 rows. I will fill out the top line by day of the month and at the end a separate column will “total” the total.

Now you need to decide on the costs, I will give the most common ones, and you can adjust them depending on your needs:

• products;
• Communal expenses;
• credit;
• mortgage;
• cloth;
• cosmetics;
• household chemicals;
• expenses for children (paid classes, pocket money, etc.);
• medicines;
• paid services of doctors (appointment, ultrasound, tests, etc.)
• present;
• transportation costs (taxi, bus/tram, gasoline)
• unforeseen expenses (car repairs, buying a TV if the old one suddenly stopped working, etc.).

The last line will summarize the results of expenses per day, so that you can see which day you were especially “on the loose.” You can use this table not only for home accounting, but also keep track of the organization’s income and expenses in Excel, simply by changing the necessary columns and lines of your choice.

And, in order not to enter a formula in each line, you can simply mark the completed one, move the mouse cursor over the lower right corner, hold down the left button and drag in the desired direction. The formulas will be “stretched” automatically.

If you have a line with an expense “creeping out” onto an adjacent cell, you can expand it by hovering your mouse over the separator between two columns, holding down the left key and dragging it to the left.

Creating a new sheet in Excel

Next, let’s create the remaining months; to do this, move the mouse pointer over the name of the sheet, in our case “January”, press the right key and select “Move or copy”.

Next, you need to select “Move to end” and do not forget to check the “Create a copy” box. If you miss one of these points, then the sheet may not be added or copied in random order, but we need each month to go as in the calendar. This is convenient and there will be no confusion.

We copy the new sheet and immediately rename it to the next month. In principle, we could finish here, but I want to see the whole picture, that is, expenses by month without expenses by day and at the same time, without switching between sheets.

Creating a Pivot Table

We will do everything quickly and without unnecessary hassles. First, let's go to the “Income and expenses” sheet and copy the “income” table. This can be done by “standing” on the left column in which the lines are numbered.

Holding down the left mouse button, you need to go down to the end of the table that we plan to copy. Next, release and press the right mouse button to display the context menu. There you need to click “Copy”. The table we need is on the clipboard and now all that remains is to add it to the file.

In the same way, mark the row below with several cells, right-click and select “Paste copied cells” from the context menu.

Now change the name of the table to “Expenses” and delete the filled rows. Next, you need to enter all the items of our costs. This can be done in different ways, for example, by simply filling it out by hand, but I will choose another option.

I calculated that there were only 6 rows in the table with income, and 13 with expenses. Select the empty rows and copy them to the clipboard.

Go to the top cell, in my case number 14, and click “Paste copied cells”. Now we have 12 lines, but I need one more, I’ll add it in a different way, simply by clicking “Insert” in the context menu.

Go to the “January” sheet and select the column with our costs for copying. To do this, click the “Products” cell, hold down the left mouse button and drag to the last cell “Unforeseen expenses”. Press the right mouse button, in the context menu that appears, click on “Copy”.

We return to the “Income and Expenses” sheet, mark the first empty cell in our table, in the context menu you need to select the “Insert” icon in the “Insert Options” section.

We are nearing the finish line to create our income and expense accounting table. All that remains is to enter formulas for the total calculation of expenses for each month and “compile” the result.

Maintaining formulas for calculating expenses

There is no need to think that now we will get confused with the formulas and it will take us a lot of time, everything is not like that at all. It is enough to fill out one cell correctly, and we will simply “stretch out” the rest.

Let’s start filling it out, in an empty cell we click the “=” sign, then we click on the “January” sheet, there we click the corresponding cell and put “+”, go to the next sheet, clicking the same cell. We continue this every month.

The picture clearly shows that all the cells in the formula are the same and the months follow one after another.

I recommend checking everything carefully before moving on to stretching the formula.

To “stretch” the formula, you need to click on the filled cell, move the mouse cursor to the lower right corner, hold down the left mouse button and drag down and then to the right. That's it, the income and expense accounting table in Excel is ready for use. Hooray!

Now all that remains is to start entering daily records of income and expenses into Excel and everything will automatically “flow” into the summary. It’s convenient and clear that everything is on one sheet.

Additional functions of the income and expenses table

You can supplement the table by adding the line “Balance”, that is, the difference between income and expenses. To do this, in the cell below the tables, add a form subtracting total income and expenses for each month.

Having marked with the mouse an empty cell under the tables, press the “=” sign, then the total amount of expenses for January, then the “–” sign and the total expenses for the same month, done, press Enter.

“Stretch” this formula through all the cells and you can now immediately see how much money is left in the black, and if not, it means you forgot to enter something

How to keep accounting records of cash in the cash register?

Working with cash is subject to a number of conditions that are specified in the special instruction of the Central Bank of the Russian Federation No. 3210-U. Important details:

• the presence of a maximum and minimum limit on amounts at the cash desk;
• the person in charge is selected from among the employees by the employer. Most often, this person is appointed as a cashier;
• any cash transaction is recorded in a cash book;
• The receipt of finance means the use of PKO (cash receipt order), and the departure - RKO (cash expenditure order).

  
  Accounting for cash transactions

Cash transactions using a cash register are accompanied by the use of an account. 50 (checkout). Receipt of money means making an entry with Dt50, and issuing Kt 50, where Dt is a debit and Kt is a credit.

Additionally, corresponding numbers can be used. For example, 60 is used when receiving payment from a supplier, and 70 is used to pay employees.

An example of posting funds through a cash register. For a month of work, employees receive a salary totaling 400,000 rubles. For this purpose, a statement is drawn up, to which the RKO is attached indicating the amount and posting Dt 70 Kt 50.

Directive of the Bank of Russia dated March 11, 2014 N 3210-U “On the procedure for conducting cash transactions by legal entities and the simplified procedure for conducting cash transactions by individual entrepreneurs and small businesses”

Step-by-step budgeting on paper

The most difficult part of home accounting is collecting expense data. Large purchases are easy to track and record, but with small expenses, especially those made by household members, things are more difficult. But taking them into account is also mandatory, because every little detail is important here.

In addition to making notes in a notebook, it is recommended to collect receipts, and after checking or initially recording the data, they can be thrown away.

All expenses, even the smallest ones, must be recorded in detail. You can calculate their amount and make a note next to it “Pocket expenses”.

Step 1 - calculating income

The beginning of home accounting should be counting the funds received . The total amount of family earnings is a key concept for budget planning. If earnings are not regular, or fluctuations in the amount of remuneration are possible, then proceed from the minimum planned amount.

If the salary is a fixed amount, then the calculation is very simple: husband’s salary + wife’s salary = family income

.

For example, if the husband’s salary is 30,000, and the wife’s is 20,000, then the total family income will be 50,000 rubles.

So, what do you need to do when starting to do home accounting?

• buy a notebook or notepad;
• on the first sheet determine the amount of monthly income;
• take the minimum amount (if its size varies);
• update income information every 3-6 months.

Step 2 - distribution

This step involves allocating the total family income for various purposes. One of the popular methods that is used for convenient distribution is the “Seven Envelopes”. According to it, household expenses are divided into 7 main goals (and, accordingly, laid out in 7 envelopes) in different percentages.

Table 1. Seven envelopes.

Rules for using funds

:

1. If there is a need to use additional funds, then envelopes 4, 5 and 7 are used first, and the rest can be touched only if absolutely necessary.
2. If earnings in the current month exceeded the usual amount of income, then envelopes 4, 5, 6 and 7 are filled first.

Example: distribution of family income with a budget of 50,000 rubles.

What should you do after analyzing this plate?

• adjust the percentage according to your own needs;
• distribute income among different envelopes;
• write down information for a specific month in a notebook.

The most convenient way is to put money in real envelopes bought at the post office.

Each can be covered with colored paper of the appropriate color and signed according to the purpose of accumulation.

Step 3 - conditional packaging

After distributing the funds among the envelopes, they can be divided into two groups:

• dynamic - that is, it is not known exactly what amount will be needed for this or that purchase;
• static - the purchase amount is known in advance.

The static part of the funds is set aside once, when you receive a salary, but the dynamic part of the budget will have to be managed daily.

After distributing income according to the proposed scheme, all data must be recorded in a notebook.

Step 4 - management

The dynamic part of expenses requires constant monitoring. To make this easier, you need to make a simple calculation:

• we take the maximum number of days in a calendar month - 31;
• add a couple of days to it in case of force majeure - it turns out to be 33;
• divide the amount of dynamic expenses by the number of days;
• We get the amount that can be spent per day.

After calculating the amount of daily allowable expenses, it is more convenient to draw tables in a notebook.

You need to try not only to fill them out every evening, but also to close them without a negative balance.

Step 5 - analysis and summing up

At the end of each week, the results are summed up and the balance is calculated. If the week can be closed with a positive balance, then the planning has been successfully followed. If a negative balance is formed, the next week's budget is reduced by the amount of the shortfall.

At the end of the month, the balance is calculated according to the same scheme.

Important! If a shortage is detected, then the next month's budget in terms of dynamic expenses should be reduced by the amount of the resulting gap.

Some tips for creating a positive balance:

• the results of the week and month should be written down on a separate sheet;
• when a shortage occurs, you need to think about which expense item can be used to eliminate it painlessly for the family;
• The money that you managed to save should be used for other purposes (vacation or expensive purchases) - you can give yourself incentives in the form of unscheduled entertainment or your favorite treats.

How does accounting for individual funds differ from accounting for an enterprise?

For special modes of activity, for example, individual entrepreneurs or small entrepreneurs, the procedure has a number of nuances.
According to Federal Law No. 402-FZ, such persons are allowed not to use the standard version of the accounting type. It turns out that a small entrepreneur or individual entrepreneur may not keep a cash book and not use PKO and RKO.

Additionally, the exemption from financial reporting allows you not to prepare Form 4.

An important point is the activity that is included in the exceptions, since if there is an obligation to maintain and there is no reporting, the entrepreneur may be subject to fines and liability.

Federal Law of December 6, 2011 N 402-FZ “On Accounting”

Changes in the processing of documents at the cash desk

A new approach to documents drawn up when using cash registers was outlined with the appearance of the updated text of Art. 1, paragraph 1 of which now states that when using cash register equipment one should be guided by:

• Law No. 54-FZ itself;
• regulations adopted in accordance with it.

That is, this automatically abolished the need to prepare previously considered mandatory documents, the unified forms of which were approved by Decree of the State Statistics Committee of December 25, 1998 No. 132:

1. Acts:

• on transferring cash meter readings to zero (form KM-1);
• on taking meter readings when transferring the cash register for repairs (form KM-2);
• on the return of money to customers (form KM-3);
• on checking cash at the cash desk (form KM-9).

• Magazines:
• cashier-operator (form KM-4), letter of the Ministry of Finance of Russia dated June 16, 2017 No. 03-01-15/37692;
• registration of meter readings (form KM-5);
• recording calls from technical specialists (form KM-8).
• Reference reports:
• certificate-report of the cashier-operator (form KM-6);
• information on meter readings and revenue (form KM-7).

At the same time, new documents that are related to cash payments made using online cash registers have been approved by Law No. 54-FZ (clause 4 of article 4.1):

• shift opening report;
• correction cash receipt;
• report on closing the fiscal drive;
• operator confirmation.

Other documents have changed their form and requirements for details. With the use of the main cash document (check or BSO), it became possible not only to process receipts for purchases, but also such operations as (Clause 1, Article 4.7 of Law No. 54-FZ):

• refund to the buyer;
• issuing funds to the client;
• receiving funds from the client.

However, changes in documents drawn up using cash registers did not in any way affect the rules for maintaining documentation for the operating cash desk, approved by the Bank of Russia Directive No. 3210-U dated March 11, 2014. Therefore, it is still required to issue cash orders for receipts and expenses (clause 4.1) and maintain a cash book (clause 4.6). In relation to cash revenue, it will be mandatory to register it daily according to a receipt order, drawn up on the basis of a shift closing report generated on the online cash register, which is an analogue of the z-report created at the cash register with ECLZ.

You can find out more about the documents that are required to be completed using CCP in various situations in ConsultantPlus. Trial access to the legal system is free.

Let's look at how to organize cash accounting in the cash register.

Why control the family budget?

The problem of lack of money is relevant for most modern families. Many people literally dream of getting out of debt and starting a new financial life. In times of crisis, the burden of low wages, loans and debts affects almost all families without exception. This is why people strive to control their expenses. The point of saving expenses is not that people are greedy, but to gain financial stability and look at your budget soberly and impartially.

The benefit of controlling financial flow is obvious - it is cost reduction. The more you save, the more confidence you have in the future. The money you save can be used to create a financial cushion that will allow you to feel comfortable for a while, for example, if you are unemployed.

The main enemy on the path of financial control is laziness. People first get excited about the idea of ​​controlling the family budget, but then quickly cool down and lose interest in their finances. To avoid this effect, you need to acquire a new habit - constantly control your expenses. The most difficult period is the first month. Then control becomes a habit, and you continue to act automatically. In addition, you will see the fruits of your “labor” immediately - your expenses will be amazingly reduced. You will personally see that some expenses were unnecessary and can be abandoned without harm to the family.