В этой статье рассмотрим способ ведения учета расчетов с арендаторами за предоставленные жилые и нежилые помещения. В этом файле возможен учет расчетов по обязательствам не более, чем с 10 арендаторами.

Прошу обратить внимание – это готовый шаблон, но не совсем полноценный метод ведения учета. Полноценно учет ведется в специализированных бухгалтерских программах (таких как 1С).

Но если вы не бухгалтер и не владеете специализированными бухгалтерскими программами, то с помощью рассматриваемого файла сможете вести учет расчетов, процентов с вашими арендаторами даже посуточно и поддерживать порядок в финансовых отношениях с ними.

Данные таблицы похожи на аналогичные рассмотренные в статье «Как вести учет в строительстве по объектам при помощи Excel». Основное отличие в том, что в упомянутом файле по строительству используется исключительно кассовый метод учета. То есть все финансово-хозяйственные операции учитываются по факту списания или получения денежных средств. Подробнее этот метод учета рассмотрен в статье «Как составлять отчет по затратам по состоянию “сегодня на вчера”».

В данном файле используется смешанный способ учета. Все расходы учитываются по кассовому методу, а доходы учитываются по методу начисления. То есть доход от аренды считается не тогда, когда Вам принесли деньги, а в момент когда вы ставите (начисляете) арендатору долг и выставляете ему счет.

Лист «Движение средств»

Рассмотрим все по порядку. Для учета денежных средств в Excel создаем таблицу как показано ниже. В нашем файле этот лист называется «Движение средств».

Пройдемся по столбцам:

Столбец А – дата;

Столбцы В,С,D – приход, расход и остаток по расчетному счету. Отражается полное движение по расчетному счету. Отражаются ВСЕ движения, независимо от того, относятся движения к объекту или нет. Остаток должен соответствовать остатку по расчетному счету;

Столбцы E,F,G – приход, расход и остаток по кассе. Отражается полное движение по кассе. Отражаются ВСЕ движения, независимо от того, относятся движения к объекту или нет. Остаток должен соответствовать остатку по кассе;

Столбец Н – объект аренды к которому относится данная финансовая операция. В данной колонке указываются арендаторы (объекты). Объекты должны называться точно так же, как и на карточке арендатора. Два исключения: «постоянные затраты» и «транзит». О них поговорим позже. В колонке предусмотрен выбор из списка арендаторов (объектов). Список объектов формируется в справочнике.

Столбец I — вид затрат/платежа. Здесь указывается либо вид дохода «аренда», либо вид затрат (канцтовары, заработная плата и т.д.). Наименование затраты должно полностью совпадать с наименованием затраты в отчете о прибылях и убытках (ОПУ). Если какая-то затрата не будет точно соответствовать списку затрат указанных в ОПУ, то соответственно сумма этой затраты не попадет в ОПУ и финансовый результат будет искажен.

Столбец J — здесь указываются контрагенты, которым деньги платят или от которых получают платежи. В карточках арендаторов и ОПУ данный признак не участвует, но для сверки расчетов можно отфильтровать платежи по любому контрагенту и увидеть все совершенные платежи. Возможно в вашем случае наименование объекта (арендатора) будет совпадать с наименованием контрагента, тогда в столбце H и столбце J вы пишете одно и тоже значение.

Столбец K — примечание. Здесь указывается любая информация, которую считаете необходимым написать по каждой финансовой операции. Например, если это хозяйственные расходы (то есть в столбце I указано «Хозяйственные расходы»), то в данной колонке стоит написать что именно, по какой цене и в каком количестве вы купили. Чем больше информации вы напишете, тем легче вам будет понять через некоторое время – что это был за платеж.

Столбец L — месяц. В столбце автоматически отображается месяц на основании даты указанной в столбце А.

В самой верхней строке листа (строке 1) указаны: в столбце А – текущая дата, а все остальные формулы позволяют рассчитывать итоги по результатам фильтрования. Попробуйте отфильтровать таблицу по любому признаку и увидите, что формулы верхней строки пересчитывают только значения в отфильтрованных строках.
Когда вы вносите данные в вышеуказанную таблицу, то данные из неё распределяются по карточкам арендаторов (объектов), которые вы указываете в столбце H, а также в отчет о прибылях и убытках (ОПУ) по видам затрат, которые вы указываете в столбце I.

Лист «Начисление аренды»

На этом листе мы формируем суммы для выставления счетов арендаторам.

Столбец А – дата;

Столбец В — арендная плата за сданную площадь согласно тарифу указанному в договоре.

Столбец С — расходы за коммунальные услуги, которые не входят в сумму арендной платы.

Столбцы D и Е — расходы за связь и уборку. Если у вас другие составляющие арендной платы за месяц, то просто поменяйте название соответствующих столбцов.

Столбец F — дополнительные расходы включенные в арендную плату.

Столбец G — итоговая сумма арендной платы за месяц. Формируется автоматически путем суммирования столбцов В, С, D, E, F.

Столбец H — объект аренды. Вписывается название арендатора, которое указано на карточке арендатора. В ячейках столбца предусмотрен выпадающий список из которого выбирается необходимое значение.

Столбец I — контрагент, которому выставляют счет. Значения столбцов «Объект» и «Контрагент» могут совпадать. В этом случае в столбцы вписываются одинаковые значния.

Столбец J — примечание. В случае необходимости записывается дополнительная информация.

Столбец L — месяц. В столбце автоматически отображается месяц на основании даты указанной в столбце А.

Когда вы вносите данные в вышеуказанную таблицу, то данные из неё распределяются по карточкам арендаторов (объектов), которые вы указываете в столбце H,

Лист «А_» Карточка арендатора (объект)

Карточка арендатора состоит из двух частей. Шапки и таблицы.

В шапке указывается:

Арендатор — здесь автоматически указывается название арендованного объекта, которые вы укажете в справочнике.

Комната, площадь аренды, цена за 1 кв метр — справочная информация по договору. Заполняется вручную. Запомните только поля выделенные оранжевым цветом на данном листе заполняются вручную! Все остальные значения на листе формируются автоматически.

Состояние расчетов — автоматические маркеры, которые говорят о состоянии расчетов с арендатором.

Таблица по расчетам формируется автоматически. То есть все начисления и оплаты попадают на карточку арендатора с листов «Движение средств» и «Начисление аренды». Вручную в таблице указывается только задолженность на начало периода (ячейка выделена оранжевым цветом).

Еще раз прошу запомнить: на листе «Карточка арендатора» существует только четыре ячейки, куда данные вносятся вручную. Это ячейки: «Комната №», «Площадь аренды», «Цена за 1 кв. м», «Задолженность на начало периода». Все они выделены оранжевым цветом. Все остальное на листе формируется автоматически.

Лист «ОПУ» (Отчет о прибылях и убытках)

Служит для формирования финансового результата помесячно. Лист формируется полностью автоматически.

Вручную указывается только название компании в шапке таблицы (ячейка выделена оранжевым цветом).

Столбец В — указываются статьи затрат, которые вносятся автоматически из справочника.

Столбцы с С по N — доходы и расходы помесячно.

Столбец О — итог за год.

Лист «Расчеты с арендаторами»

Демонстрирует фактическое состояние с арендаторами. Лист формируется полностью автоматически. Здесь ничего не вносится вручную!

Расшифровывать столбцы не имеет смысла и так все понятно. Только столбец F нуждается в пояснениях, об этом ниже.

В этой таблице, если у кого-то из арендаторов будет долг по оплате аренды, то соответствующая строка будет окрашена в красно-желтый цвет:

Если по какой-то причине начисленная сумма аренды не попала в таблицу «Расчеты с арендаторами», то это будет выглядеть вот так:

И как раз для демонстрации этой ошибки используется столбец F «Проверка».

Таким образом, на данном листе вы будете видеть и общее состояние расчетов с арендаторами и правильность отнесения начисленной арендной платы.

Лист «Проверка»

В таблицах Excel очень легко сделать ошибку. В случае данного файла не написать признак в столбце I или написать этот признак неправильно. Чтобы вы не смогли написать неправильно вид хозяйственной операции (столбец I), внесение данных в этот столбец производится через выпадающий список, а сам список находится на листе “Справочник”. И поэтому в этот столбец вы сможете писать только то, что есть в этом списке. Кстати, эту проверку можно обойти если внести данные туда путем копирования – в этом случае проверка, к сожалению, не работает. Поэтому не копируйте данные в этот столбец. Если же вы просто забудете написать признак в столбце I по описываемой хозяйственной операции, то файл вам напомнит, окрасив столбцы внесения в красный цвет. Вот так:

В данном случае показано, что по расходной операции в январе не указан вид затраты. Это же предупреждение появляется на листе “Проверка” и выглядит вот так:

Именно таблица на листе “Проверка” выявляет эту ошибку, а на листе “Движение средств” появляется только красная индикация. После того как вы увидели красные столбцы вам необходимо отфильтровать пустые ячейки по столбцу I

и найти пропущенную ячейку. После того как вы её заполните, красная индикация исчезнет. Аналогичные действия необходимо провести, если будет пропущен признак по доходной операции, но только окрасятся красным другие столбцы (на листе «Движение средств»).

А лист «Проверка» будет выглядеть вот так:

В этом случае необходимо сортировать на наличие пустых ячеек столбец H «Объекты».

А вот если у вас окрасятся красным столбцы А “Дата” и L «Период»

это значит вы случайно удалили либо дату в столбце А, либо формулу в столбце L. Индикация для обоих случаев будет одинаковой. И искать с помощью фильтра надо будет либо пустую ячейку в столбце A, либо значение “0” в столбце L.

Лист “Проверка” в данном случае будет выглядеть вот так:

Лист «Справочник»

На этом листе собраны данные, которые используются на разных листах в таблицах, в выпадающих списках ячеек, в формулах индикации и т.д.

В таблице контрагентов и объектов вы указываете тех контрагентов и арендаторов (объектов), которые будут у вас. Наименование контрагента и арендатора могут совпадать, а может нет. Тут уж как вам будет удобно.

В таблице статей движения денежных средств вы пишете те статьи, которые будут у вас, но постарайтесь вложится в то количество строк, которое есть. Не плодите лишние статьи расходов. И правильно указывайте тип операции.

В статусах расчетов я вам рекомендую ничего не менять, но теоретически это тоже можно сделать, главное чтобы смысл сохранялся тот же.

Советы по заполнению файла

1. Заполняйте все ячейки по каждой операции. Пропусков должно быть минимально. Во-первых, это позволит сохранять больше информации по каждой операции, а во-вторых, даст вам больше возможностей для фильтрации данных.
2. Следите за тем, чтобы красных столбцов не было. Наличие красной индикации говорит об ошибке в заполнении и следовательно некорректных отчетах.
3. Даты в первой колонке таблицы «Движение средств» необходимо вносить вручную, но изначально там вбиты по всему столбцу ссылки на предыдущую ячейку. Это позволяет вносить дату один раз в день (поверх ссылки), а все последующие ссылки тут же отражают только что внесенную дату. Если необходимо убрать все изменения дат, то нужно просто опять скопировать ссылку на все ячейки столбца путем протягивания ссылки по всему столбцу (см. рисунок ниже).

Проблема с передачей форматов

Этот файл создан в MS Excel 2016. По неизвестной мне причине при пересылке файлов на другие компьютеры, иногда пропадает формат списка проверки данных (возможно это связано с тем, что на принимающих компьютерах стоит более старая версия MS Excel). То есть в этом случае, в колонках «Объект», «Вид затраты» может пропасть выпадающий список . Все расчеты в файле сохраняют свои корректность, но список проверки данных может пропасть. Формат списка очень просто восстановить. Как это сделать – прочитайте вот здесь.

Где можно приобрести файл?

Приобрести можно в нашем магазине. После оплаты вам будет выслано две ссылки на скачивание. По одной ссылке будет пример использования файла, по второй шаблон готовый к использованию.

Если вам нужен файл для большего количества арендаторов, то у нас на сайте есть таблицы для 30 арендаторов. Этот файл дороже, чем рассмотренный выше, но имеет гораздо больший функционал. Ознакомиться с файлом Вы можете в статье "Как вести учет расчетов по аренде при помощи Excel (не более 30 арендаторов)".

Остались вопросы? Напишите мне в Telegram!

Бесплатная консультация

Если вы заходите с PC, но хотите открыть чат на смартфоне, отсканируйте QR-код