How to keep records in construction for objects using ExcelАртем
In this article, we consider a way of maintaining costs for facilities in a small enterprise in construction or other unit production.
Please note that this is not a complete accounting method. Full accounting is maintained in specialized accounting programs (such as 1C).
But if for some reason you want to keep a “blitz account”, then this method is suitable for you. In fact, this is an accounting of expenses using the cash method, which I write about in the article “How to compile a report on expenses as of today – deadline yesterday” only in Excel.
In order to conduct such a calculation of costs, it is necessary to make such a table in spreadsheets, in this file this sheet is called “Cash Flow”:
Let’s consider it in more detail:
Column A – date;
Columns B, C, D – income, expense and balance of the current account. They reflect the full movement of the current account. ALL movements are reflected, regardless of whether the movements relate to the object or not. The balance should correspond to the balance in the current account;
Columns E, F, G – income, expense and balance at the cash desk. They reflect the full movement at the cash desk. ALL movements are reflected, regardless of whether the movements relate to the object or not. The balance should correspond to the cash balance;
Column H – the object to which the financial transaction relates. This column indicates the objects under construction. Objects must be named exactly the same as on the card of this object. Two exceptions: “fixed costs” and “transit”. We will talk about them later.
Column I – type of operation (cost, payment). Types of operations are indicated here, for example: salary, procurement of materials (I recommend simply calling “materials”), prepayment of the customer, etc. Accordingly, on the sheet of the object it will be necessary to indicate all those types of costs that will be written in this column. If some kind of costs will not be written in the card of the object, then accordingly these costs will not fall into the card of the object. You can add cost types to the object card or delete all those cost types that I wrote in the object cards and write your own. The main thing is that in this column the same types of costs are indicated that are written in the object card.
Column J – here are indicated the counterparties to whom money is paid or from which they receive payments. This feature does not participate in object cards, but to verify settlements, you can filter payments by any counterparty and see all payments made.
Column K – Note. Any information that you consider necessary to write on each financial transaction is indicated here. For example, if this is a purchase of materials (that is, “materials” is indicated in column I, then in this column it is worth writing which materials, 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.
The very top line of the sheet (line 1) shows: in column A – the current date, and all other formulas allow you to calculate the results of the filtering results. Try filtering the table by any criterion and you will see that the formulas of the top row only recalculate the values in the filtered rows.
When you enter data in the above table, the data from it is distributed on the object cards that you specify in column H.
The name of the object is indicated in the upper cell of column E (the cell is marked in orange). Accordingly, it is this word that you should use when specifying the object of a financial transaction in the “Cash Flow” table.
The main table shows the costs by type of cost. Orange cells that are the cells where you should write the type of costs by which they will be distributed.
Below the table with costs there is a table with data on income received.
In the box highlighted in orange, you must specify the name of the counterparty, which are the customer and payer for this object.
If the costs of the object exceed the amount of income for the object, the revenue line will turn red.
Fixed costs (expenses associated with the objects)
To collect these costs, there is a separate sheet, which is called “Fixed Costs”.
Cells that you must fill out yourself are highlighted in orange.
Accordingly, on the “Cash Flow” sheet, if the expense does not relate to any object, in the column “Object” you should write “Fixed costs”, and in the column “Cost type” you need to indicate only those costs that you wrote on the sheet “Fixed costs”.
Objects and Costs
The next sheet of the file in question is the “Objects and Costs” sheet.
This is a summary sheet for all cost allocation cards, whether it is object costs or fixed costs not associated with objects. This sheet is necessary to verify the completeness of the distribution of all costs and revenues from the sheet “Cash Flow”.
The top line, designated as “Cash Flow”, reflects the sum of all financial transactions from the “Cash Flow” sheet minus the expenses designated as “Transit”.
Directly in the table on this sheet is information on the distribution of income and expenses objectively:
Column A – the name of the object.
Columns B, C, D – receipts for the current account, receipts at the cash desk and the amount.
Columns E, F, G – current account expenses, cash desk expenses and amount.
If the total amount of expenses exceeds the total income of the object, then the line of this object will turn red.
Since this table reflects the distribution of all revenues and expenses, the total amount of operations by objects and fixed costs should be equal to the sum of all operations on this column minus the costs of “Transit”. Otherwise, the corresponding column on the “Cash Flow” sheet will turn red.
This is how the “Cash Flow” sheet will look like if you forgot to put the name of the object:
This is what it will look like if you forgot to set the type of costs:
With this indication, you can always see that not all of the mandatory signs for the correct distribution of costs have been set.
What is “Transit”?
If you are conducting a cash-out transaction, then from the point of view of your finances, this operation cannot be related to either expenses or income. Accordingly, such an operation cannot be posted to objects. Therefore, in the “Cash Flow” table and in the transaction object, you must write “Transit” in the form of costs.
You can write the expense from the current account and income to the cash desk in one line (as shown above) or split into two lines:
The meaning of this will not change. The main thing is that there are all the necessary signs and on the sheet “Objects and Costs” in cell G2 was the word “Transit”.
Tips for filling and maintaining a file.
- Fill in all cells for each operation. There should not be passes. Firstly, it will allow you to save more information on each operation, and secondly, it will give you more options for filtering data.
- If one payment at a bank or cash desk is intended for several objects – break the payment into the required number of parts.
- If one of the columns in the “Cash Flow” has turned red (this means that the corresponding cells are not filled in the “Object” or “Cost Type” columns). Use the auto filter to select empty cells in these columns. And so you can catch those lines in which there are numbers, but there are not enough signs for distribution.
- In addition to not writing the necessary attribute, there may be a mistake if you write the attribute (object or type of cost) incorrectly. It is necessary to verify the data for each object and each type of cost.
- Dates in the first column of the table “Cash Flow” must be entered manually, but initially there are links to the previous cell driven along the entire column. This allows you to enter the date once a day (over the link), and all subsequent links immediately reflect the date just entered. If you need to remove all date changes, you just need to copy the link to all the cells of the column again.
Where can you get this file?
It can be purchased in our store.