How to keep records in a small trade or any other non-manufacturing enterprise using ExcelАртем
In this article, we will overview a method of keeping records and obtaining a profit and loss statement in a non-manufacturing enterprise.
PAY ATTENTION! THIS IS NOT A COMPLETE RECORDING METHOD. ACCOUNTING IS KEPT IN AN APPROPRIATE MANNER ONLY IN SPECIALIZED ACCOUNTING PROGRAMS (SUCH AS 1C).
That is the simplest way to keep records in a small trade or any other non-manufacturing enterprise. Keeping records in such a way, you will not get a balance sheet and will not see the whole financial picture of your company. However, you can get a financial result for a month compiled on a cash basis.
Cash accounting is an accounting method in which all expenses and incomes are included as they are paid. When the expense or income on the cash desk or current account has passed – it means that the income (or expense) has taken place, then it is reflected in the accounting and takes part in the formation of the profit and loss statement.
So, we complete the main table in which we will directly enter data on payments through the current account and cash desk.
Let’s overview it in more detail:
Column A – “Period.” That is a service column in which you do not enter any data. The text value of the month to which the corresponding business transaction will refer will automatically be calculated here;
Column B – “Date.” In this column, you manually enter the transaction date. Pay attention, in a column for 9 thousand lines, there are consecutive links to the previous table cell. The date which is manually entered in any of the column cells will be duplicated below. It allows you to save a little time making transactions in one day;
Columns C, D, E – inflow, outflow, and balance of the current account. The complete movement on the current account is reflected here. ALL movements are reflected, regardless of whether they are related to an object or not. The balance should correspond to the balance of the current account;
Columns F, G, H – inflow, outflow, and cash balance. The full movement of the cash register is reflected. ALL movements are reflected, regardless of whether they are related to an object or not. The balance should correspond to the cash balance;
Column I – the type of operation (cost, payment). The types of transactions are indicated here, for example, salary, stationery, buyer’s payment, etc. In this column, you can specify only those types of operations that are indicated on the sheet “Reference” and will be in the drop-down list in each cell of this column. Accordingly, the indicated types of operations on the sheet “Profit & loss” will appear in the “Costs” section on the sheet “Reference.” However, such types of operations as “Customer payment” and “Prime cost of the sold goods” correspond to the articles of the report on the sheet “Profit & loss” – “Sales volume (goods, works, services)” and “Prime cost of goods sold”. You can also use the business transaction I wrote or write your own. The main thing is that all used names of operations are linked to the corresponding articles of the report on the sheet “Profit & loss.” These links will remain even if you rename some operations on the “Reference” sheet. You may have problems only by adding new names but obtaining primary skills in working with Excel tables will solve this problem.
Column J indicates the counterparties to whom money is paid or from whom payments are received. Such a feature is not included in the report, but to check calculations, you can filter payments by any counterparty and see all made payments. The only exceptions are the names of the owners of the enterprise, which are also indicated in this column in the case of withdrawals or investments, however, I will describe it below.
Column K – additional information. Any information about each financial transaction that you consider necessary to write is indicated here. For example, if it is a purchase of goods (in column I it noted “Purchase of goods”), then in this column it is worth writing which goods, at what price, and in what quantity. The more information you write, the easier it will be for you to understand after a while what kind of payment it was.
Besides, pay attention to the top line of the table. There in columns C, D, F, G you will see the sums calculated by the formula “SUBTOTAL.” The formula calculates the amounts that are selected by the autofilter in the head of the table. Such a function is very convenient when you need to calculate transactions with one feature.
Sheet “Profit & loss”
The main report of this file. You will see the financial result of your company compiled on a cash basis with the help of this sheet.
On this page, you manually enter only your company name in table cell B3. Everything else is calculated automatically. You can see the financial result here every month and for the year as a whole. Besides, this sheet contains 16 more lines in the “Operating expenses” section. It was made in order the cost types added on the “Reference” sheet will automatically and correctly be included in this report.
Transactions that are neither income nor expense are reflected on this worksheet. Such operations include:
- Owners’ investments.
- Owners’ withdrawals.
- Transit operations (“Transit”).
Nothing is manually entered on this sheet. But on the sheet “Reference” it is necessary to enter the names of the owners of the enterprise. There are three owners in our calculation but it also assumes up to six people. Their names are entered in the corresponding table on the “Reference” sheet.
In the first table on this sheet, the owners can see which of them has withdrawn a particular sum of money and make calculations among themselves.
In the second table, owners see their investments in the enterprise.
In order for these two tables to work correctly, it is necessary to indicate the names of the owners in column J on the “Flow of funds” sheet. Moreover, you need to specify data in the same way as indicated on the sheet “Reference.”
If the names of the owners on the sheet “Flow of funds” are indicated incorrectly, then in this table the column for the corresponding period will turn in red. Like this:
It will mean that you need to check the spelling of the owner’s name in the June transactions in column J on the sheet “Cash flow.”
In lines 7 and 8 of the “Owners’ withdrawals” table, you will see the difference by the amount that is not taken into account in the calculations. Line 8 is introduced to check how it works.
That is how the sheet “Cash flow” will look in case you filter the data in column I by the value “Withdrawals.”
After correcting this error, all red indications on both sheets will disappear.
What is “Transit”?
The last table on this sheet is the table “Transit.” It specifies transfer funds operations from the current account to the cashier or vice versa. In this case, there may be (and usually appear) the costs of transferring money from one form to another, that is, the costs of cashing out or clearing money. Since this operation is neither income nor expense, it does not participate in financial result determining.
You can write the outflow from the current account and the inflow to the cashier on one line (as shown above) or split into two lines:
That will not change the meaning. It is necessary only to have all the necessary signs and to have the word “Transit” on the sheet “Investments-Withdrawals-Transit” in cell B24.
It is very easy to make mistakes in Excel calculations. In this case, it is to write the attribute in column I or write it incorrectly. Entering data into this column is done through the drop-down list which is on the “Reference” sheet and that was made to prevent you from writing the wrong type of business transaction (column I). Therefore, in this column, you can write only what is on this list. By the way, you can avoid this check if you enter data by copying and pasting – then, the check, unfortunately, will not work. Try not to copy data to this column. If you just forget to write the attribute in column I for the described business transaction, then the file will remind you to do that by coloring the entry columns in red. Like this:
In this case, it is shown that cost type is not specified for the outflow transaction. The same warning appears on the sheet “Check” and looks like that:
Only the table on the sheet “Check” reveals this error, and only the red indication appears on the sheet “Cash flow.” After you have seen the red columns, you need to filter empty table cells by column I and find the missing cell.
After you fill it in, the red indication will disappear. The same actions should be made if the indicator for an income transaction is missed but then other columns will only turn in red.
But if your column B “Date” turns in red
it means you accidentally deleted either the date in column B or the formula in column A. The indication for both cases will be the same and you will have to search using the filter either an empty cell in column B, or the value “0” in column A.
The sheet “Check” in this case will look like that:
In such a way, you can notice errors related to the absence of a sign in column I or accidental deleted date.
Another sign that also causes a red indicator on the sheets “Cash flow” and “Check” is the incorrect spelling of the names of the owners in case of inflow or outflow. I described this error in the section devoted to the sheet “Investments-Withdrawals-Transit.”
On this sheet, you form data of business transaction types and put on information about owners.
Lines for expenditure types of transactions are highlighted in gray, each line in the list corresponds to a line in the report on the sheet “Profit & loss account.”
You can simply supplement the lists or create them completely from scratch, the report will work correctly. But! Do not swap the lines, it may lead to the incorrect operation of the file.
The same is about the list of owners. You can complete the list or completely replace the values in the list, but do not swap the lines.
Pay attention, the blank lines in the sheet “Profit & loss account” has hidden lines in the “Operating expenses” section. After adding new types of expenses on the “Reference” sheet, you will need to show hidden lines on the “Profit & loss account” sheet (look at the figure below).
Tips how to fill in the file
- Fill in all table cells for each operation. There should be minimal gaps. First of all, it will allow you to save more information on each operation and, secondly, it will give you more options for filtering data.
- Make sure that there are no red columns. The presence of a red indication indicates an error in filling out and, therefore, incorrect reports.
- The dates in the first column of the “Cash flow” table should be entered manually, but, initially, there are links to the previous table cells in the entire column. It allows you to put in the date once a day (over the link), and all subsequent links will immediately reflect just the entered date. If you need to remove all date changes, you just need to copy the link to all cells of the column again by dragging the link along the entire column (look at the figure below).
Where to buy the file.
You can purchase it in our store.