Management accounting presented inunderstandablehumanaccessiblelanguage.

Management accounting presented inhumanaccessiblelanguage.

Your cart is empty

Rent

Information section

How to keep records of rent calculations using Excel (no more than 10 tenants)

In this article, we will consider the way of accounting for settlements with tenants for the provided residential and non-residential premises. In this file, it is possible to account for the settlement of obligations with no more than 10 tenants.

 

Please note - this is a ready-made template, but not quite a complete method of accounting. Fully accounting should be made in specialized accounting programs (such as 1C).

But if you are not an accountant and do not own specialized accounting programs, with the help of the file under consideration you will be able to keep records of settlements, interest with your tenants even daily and maintain order in financial relations with them.

These tables are similar to those discussed in the article "How to keep track of construction projects using Excel". The main difference is that the mentioned construction file uses exclusively the cash method of accounting. That is, all financial and business transactions are recorded by the fact of writing off or receiving money. This method of accounting is discussed in more detail in the article "How to report costs as of 'today for yesterday'".

This file uses a mixed method of accounting. All expenses are recorded on a cash basis and income is recorded on an accrual basis. That is, rental income does not count when the money was brought to you, but when you put (accrue) the tenant's debt and bill him.

"Cash Flow" sheet

Let's look at everything in order. For cash accounting in Excel, create a table as shown below. In our file, this sheet is called "Cash Flow".

Table Rent 1

Let's go through the columns:

Column A- date;

Columns B,C,D- income, expenditure and balance of the current account. Reflects the full movement on the current account. ALL movements are reflected, regardless of whether the movements are related to the object or not. The balance must correspond to the balance of the current account;

Columns E,F,G- income, expenditure and cash balance. Reflects the full movement of the cash register. ALL movements are reflected, regardless of whether the movements are related to the object or not. The balance must match the cash balance;

Column H- the object of lease to which this financial transaction relates. Tenants (objects) are listed in this column. Objects should be named exactly the same as on the tenant card. Two exceptions: "fixed costs" and "transit." We'll talk about them later. The column provides a selection from a list of tenants (objects). The list of objects is formed in the directory.

Column I- type of cost/payment. This specifies either the type of income "rent" or the type of expenses (office supplies, salaries, etc.). The name of the cost must be exactly the same as the name of the cost in the profit and loss statement (PLS). If any cost will not exactly match the list of costs specified in the PLS, then accordingly, the amount of this cost will not fall into the PLS and the financial result will be distorted.

Column J- here you can see the counterparties to whom the money is paid or from whom you receive payments. This attribute does not participate in the cards of tenants and PLS, but you can filter payments by any counterparty and see all payments made to reconcile the settlements. Perhaps in your case, the name of the object (tenant) will coincide with the name of the counterparty, then in column H and column J you write the same value.

Column K - note. Any information you feel you need to write about each financial transaction is entered here. For example, if it is a household expense (i.e., column I indicates "Household expenses"), then this column should write what exactly, at what price and in what quantity you bought. The more information you write, the easier it will be for you to understand after a while - what kind of payment it was.

Column L - month. The column automatically displays the month based on the date specified in column A.

The topmost line of the sheet (line 1) contains: in column A- the current date and all other formulas allow you to calculate the totals from the filtering results. Try filtering the table by any attribute and you will see that the top row formulas recalculate only the values in the filtered rows.
When you enter the data in the above table, the data from it is allocated to the tenant (facility) cards that you enter in column H, and to the income statement (P&L) by the cost type that you enter in column I.

"Rent Accrual" sheet

On this sheet, we generate amounts for billing tenants.

Table Rent 2

Column A- date;

Column B- the rent for the rented area according to the tariff specified in the contract.

Column C- costs for utilities, which are not included in the amount of rent.

Columns E - communication and cleaning costs. If you have other monthly rent components, just change the name of the corresponding columns.

Column F- additional costs included in the rent.

Column G - the total amount of rent per month. Formed automatically by adding columns B, C, D, E, F.

Column H is the object of the lease. Insert the tenant's name as it appears on the tenant card. In the cells of the column is a drop-down list from which you select the desired value.

Column I - the counterparty that is billed. The values of the "Object" and "Counterparty" columns may be the same. In this case, the columns contain the same values.

Column J - note. If necessary, additional information is entered.

Column L - month. The column automatically displays the month based on the date specified in column A.

When you enter data into the above table, the data from it is distributed to the tenant (object) cards that you enter in column H,

Sheet "A_" Tenant (object) card

The tenant card has two parts. Headers and Tables.

Table Rent 3

The header specifies:

Tenant- the name of the leased object, which you specify in the directory, is specified here automatically.

Room, rental area, price per square meter- background information on the contract. Filled in manually. Remember only the fields highlighted in orange on this sheet are filled in manually! All other values on the sheet are generated automatically.

Settlement status- automatic markers that tell you the status of settlements with the tenant.

The calculation table is generated automatically. That is, all accruals and payments go to the tenant's card from the "Movement of funds" and "Rent accrual" sheets. Only the debt at the beginning of the period (the cell is highlighted in orange) is indicated manually in the table.

Once again, please remember that there are only four cells on the "Tenant Card" sheet, where the data is entered manually. These cells are: "Room No.", "Rental area", "Price per square meter", "Debt at the beginning of the period". They are all highlighted in orange. Everything else on the sheet is formed automatically.

"P&L" (Profit and Loss Statement) sheet

Serves to form the financial result on a monthly basis. The sheet is generated fully automatically.

Table Rent 4

Only the company name in the table header (the cell is highlighted in orange) is specified manually.

Column B is for cost items that are entered automatically from the directory.

Columns C through N are monthly income and expenses.

Column O is the total for the year.

"Settlements with tenants" sheet

Demonstrates actual condition with tenants. The sheet is generated fully automatically. Nothing is entered manually here!

Table Rent 5

There is no point in deciphering the columns, as everything is clear. Only column F needs explanation, which you can find below.

In this table, if any tenant has a rent debt, the corresponding row will be coloured red-yellow:

Table Rent 6

"Croop Agro" tenant has a rental debt

If, for some reason, the accrued amount of rent does not appear in the "Settlements with tenants" table, it will look like this:

Table Rent 7

LLC "Ukrkom" reflected incorrectly the amount of accrued rent

And, just to demonstrate this error, Column F "Check" is used.

Thus, on this sheet you will see both the general state of settlements with tenants and the correctness of the accrued rent.

"Check" sheet

It is very easy to make a mistake in Excel tables. In the case of this file, do not write the attribute in column I or write the attribute incorrectly. So that you could not write the wrong type of business operation (column I), entering data in this column is made through a drop-down list, and the list itself is on the sheet "Reference". That is why you can write in this column only what is in this list. By the way, this check can be bypassed if you put the data there by copying - in this case, unfortunately, the check does not work. Therefore, do not copy data into this column. If you simply forget to write the attribute in column I for the described business transaction, the file will remind you by colouring the columns of the entry in red. Example:

Table Rent 8

This case shows that the expenditure operation in January does not specify the type of cost. The same warning appears in "Check" sheet and looks like this:

Table Rent 9

It is the table in "Check" sheet that reveals this error, and only the red indication appears in "Flow of Funds" sheet. After you see the red columns you need to filter out the empty cells in column I

Table Rent 10

and find the missing cell. After you fill it out, the red indication will disappear. Similar steps should be taken if the sign on the income operation will be missed, but only other columns will be coloured in red (in "Flow of Funds" sheet).

And "Check" sheet will look like this:

Table Rent 11

In this case, you need to sort for empty cells in column H "Objects".

But if columns A "Date" and column L "Period" are coloured red

Table Rent 12

it means you accidentally deleted either the date in column A or the formula in column L. The indication for both cases will be the same. In addition, you will need to use the filter to look for either an empty cell in column A, or the value "0" in column L.

"Check" sheet will look like this in this case:

Table Rent 13

"Reference" sheet

This sheet collects data that is used on different sheets in tables, in drop-down lists of cells, in display formulas, etc.

Table Rent 14

In the table of counterparties and objects, you specify those counterparties and tenants (objects) that you will have. The name of the counterparty and the tenant may or may not be the same. It's up to you how you want it.

In the cash flow line item table, you write the items that you will have, but try to fit in the number of lines that you have. Don't make unnecessary expense items and correctly specify the type of operation.

I recommend you not to change anything in the status of the calculations, but theoretically, this can also be done, as long as the meaning remains the same.

Tips for filling out the file

  1. Fill in all cells for each operation. Skips should be minimal. First, it will save more information on each operation, and second, it will give you more options for filtering data.
  2. Make sure there are no red columns. The presence of a red indication indicates an error in filling and therefore incorrect reports.
  3. The dates in the first column of the "Flow of Funds" table need to be entered manually, but initially the references to the previous cell are entered there throughout the column. This allows you to enter a date once per day (on top of the reference), and all subsequent references immediately reflect the date you just entered. If you want to remove all the date changes, you just need to copy the reference to all the cells in the column again by dragging the reference across the column (see figure below).

Table Rent 15

Problem with format transfer

This file was created in MS Excel 2016. For some unknown reason, when sending files to other computers, sometimes the format of the data checking list disappears (this may be due to the fact that on the receiving computers is an older version of MS Excel). That is, in this case, in the columns "Object", "Cost Type" may disappear drop-down list. All calculations in the file retain their correctness, but the data checklist may be missing. The list format is very easy to restore. Read here how to do it.

Where can I purchase the file?

You can buy it in our store. After payment, you will be sent two download links. One link will give you an example of how to use the file, and the other will give you a ready-to-use template.

 

 

 

If you need a file for more tenants, we have tables for 30 tenants on our website. This file is more expensive than the one discussed above, but has much more functionality. You can read about the file in the article "How to keep records of lease payments using Excel (not more than 30 tenants)".

 

1 000 руб each Rent accounting. 10 renters

Your cart is empty