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

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

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

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

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

Этот файл отличается от файла рассмотренного в статье "Как вести учет расчетов по аренде при помощи Excel (не более 10 арендаторов)" тем, что:

1. В этом файле есть лист "Навигатор" для удобных переходов внутри файла.

2. Есть функция "Расчеты с учредителями". Вы можете вести расчеты между владельцами бизнеса (их вложения и изъятия).

3. Можно вести учет расчетов с 30 арендаторами.

Но рассмотрим весь файл подробнее.

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

Для учета денежных средств в 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 кв. м», «Задолженность на начало периода». Все они выделены оранжевым цветом. Все остальное на листе формируется автоматически.

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

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

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

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

У арендатора «Крупп-агро» долг по аренде.

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

У арендатора «Крупп-агро» неправильно отражена сумма начисленной аренды

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

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

Лист «Расчеты собственников-Транзит»

На этом листе все формируется автоматически. Ничего вручную не вносится. Данные по собственникам попадают с листа "Справочник". И вы видите, по партнерам, кто сколько внес и кто сколько изъял.

Отдельно поговорим про транзит.

Что такое «Транзит»

Если вы совершаете операцию по обналичиванию средств или просто перебрасываете денежные средства с банковского счета в кассу, то с точки зрения ваших финансов эта операция не может относиться ни к расходам, ни к доходам.  Поэтому на листах движения средств и в колонке «Объект» для этой операции необходимо написать «Транзит» и тоже самое писать в колонке «Вид затрат».

Вы можете написать расход с расчетного счета и приход в кассу в одной строке (как показано выше) или разбить на две строки:

Смысл от этого не поменяется. Главное, чтобы стояли все необходимые признаки. А на листе «Расчеты собственников-Транзит» в ячейке B24 стояло слово «Транзит».

Если транзит не будет закрыт (то есть деньги с расчетного счета ушли, но в кассу не поступили), таблца по транзитам будет выглядеть вот так:

Так же о незакрытых транзитах будет информация на листе "Навигатор", но об этом мы расскажем ниже.

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

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

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

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

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

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

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

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

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

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

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

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

А вот если у вас окрасятся красным не только столбцы прихода или расхода (В, С, E, F), но и столбцы А “Дата” и L «Период»

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

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

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

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

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

В таблице собственников укажите всех тех, кто является собственниками бизнеса и осуществляет вложения в бизнес и изъятия из него.

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

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

Лист "Навигатор"

На этом листе вы видите сводную информацию по вашему бизнесу в целом. Если этот файл сделать доступным через сеть, то вы или ваш руководитель, заходя в этот файл, будет видеть первичную сводную информацию по аренде, а так же состояние учета (наличие ошибок в введении данных). Кроме того, с этого листа очень удобно перейти на любой объект и вернуться обратно на лист «Навигатор». Так как сам по себе файл очень большой и «бегать» по листам без навигации крайне неудобно.

Колонка А - переход к общим расчетным и служебным листам. С помощью гиперссылок настроены переходы между листами. Учитывая количество листов (почти 40 штук) это значительно облегчает работу в файле.

Колонка В - переход к объектам. На каждом листе в правом верхнем углу расположены три ссылки для переходов внутри файла.

Колонка С - в этой колонке отображается состояние расчетов с арендаторами. Если ячейки напротив арендатора окрашены зеленым цветом - это значит все в порядке. А если арендатор просрочил платеж то тогда ячейка окрасится в красный цвет.

Колонка D "Транзит" - что такое транзит описано выше в соответствующем разделе. В этой колонке сигнализируется если какой-то транзит не закрыт.

Ниже показано как выглядит лист "Навигатор" в случае каких-то "проблем".

В «Навигаторе» вручную вы не вносите ничего. Здесь вся информация подтягивается автоматически с других листов. Любое ваше изменение в ячейках этого листа может привести к нарушению работы файла.

Навигация на остальных листах

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

На листах карточек объектов и общих листах гиперссылки находятся в правом верхнем углу:

 

Внимание! Чтобы гиперссылки работали ни в коем случае не переименовывайте листы!

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

1. Заполняйте все ячейки по каждой операции. Пропусков должно быть минимально. Во-первых, это позволит сохранять больше информации по каждой операции, а во-вторых, даст вам больше возможностей для фильтрации данных.
2. Следите за тем, чтобы красных столбцов не было. Наличие красной индикации говорит об ошибке в заполнении и следовательно некорректных отчетах.
3. НЕ ПЕРЕИМЕНОВЫВАЙТЕ ЛИСТЫ!
4. Если у вас на компьютере файл работает очень медленно. То есть после ввода любой цифры компьютер зависает на некоторое время, то эту проблему во многих случаях можно решить путем уменьшения количества строк в таблице на листе "Движение средств". В стандартном файле, который вы покупаете, для этой таблицы предусмотрено 9100 строк. Это довольно много и именно из-за этого ваш компьютер может "зависать".  Можно удалить строки из этой таблицы (например оставить 3000 строк). Тогда ваш файл будет работать быстрее.

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

 

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

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

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

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

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