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

Для того, чтобы вся информация по файлу была в одной статье рассмотрим весь файл полностью.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На этом листе мы формируем суммы для выставления счетов и актов арендаторам. Кроме того, на основании внесенных данных вы можете автоматически формировать счета и акты в формате PDF.

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

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

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

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

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

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

Столбец I — сумма НДС. Столбец формируется автоматически. НДС рассчитывается на основании данных столбца H, ставки НДС, а так же статуса контрагента, как плательщика НДС, который выбирается на листе "Реквизиты".

Столбец J — сумма к уплате. Именно эта сумма будет указана в счете и акте. Определяется как сумма столбцов H и I.

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

Столбец L — арендодатель. Юридическое лицо от которого будут выставляться счета и подписываться акты выполненных работ.

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

Далее идут колонки связанные с учетом и регистрацией выписываемых документов

Столбец N — дата выписки счета.

Столбец O — исходящий номер счета. Формируется автоматически на основании порядкового номера строки (столбец В) и даты выписки (столбец N). После того как счет будет сформирован в этой ячейке будет сформирована гиперссылка на выписанный счет в формате PDF. Текст в ячейке с гиперссылкой синего цвета и подчеркнут. (смотри первые семь строк на картинке).

Столбец P — дата выписки акта.

Столбец Q — исходящий номер акта. Формируется автоматически на основании порядкового номера строки (столбец В) и даты выписки (столбец P). После того как акт будет сформирован в этой ячейке будет сформирована гиперссылка на выписанный акт в формате PDF. Текст в ячейке с гиперссылкой синего цвета и подчеркнут. (смотри первые семь строк на картинке).

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

Формирование первичных документов.

Справа от таблицы в верхней строке находятся две кнопки: "Создать счет" и "Создать акт".

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

На картинке показан пример нажатия кнопки "Создать счет", но так как работают кнопки совершенно одинаково, то при нажатии кнопки "Создать акт" будет совершенно тот же порядок действий. Только данные по учету счетов сохраняются в столбцах N и O, а данные по актам сохраняются в столбцах P и Q.

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

Образцы формируемых документов вы можете посмотреть в конце этой статьи.

Где сохраняются созданные счета и акты

При первом нажатии кнопки "Создать счет", макрос проверяет наличие папки "Счета" в том, каталоге, где лежит основной файл. И если такой папки нет, он ее создает и далее складывает все счета в эту папку.

Аналогично работает макрос при нажатии кнопки "Создать акт". То есть все акты будут храниться в созданной макросом папке "Акты".

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Лист "Реквизиты"

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

Столбец А - наименование предприятия. Именно так будет написан контрагент в счете. ОЧЕНЬ ВАЖНО! В названии контрагентов не используйте специальные символы. Лучше всего использовать только буквы и цифры. Ну и тире тоже можно. Использование специальных символов типа "/'# может привести к некорректной работе макросов. Так что просто не используйте их.

Столбцы с В по G - банковские реквизиты

Столбец Н - адрес. Чем полнее вы его напишете, тем полнее этот адрес будет написан в счете.

Столбец I - здесь указывается, является ли контрагент плательщиком НДС. Просто да или нет.

Столбец J - подписант. ФИО представителя компании, который уполномочен подписывать акты выполненных работ.

Столбец K - дата подписания и номер заключенного договора.

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

Обратите внимание, что светлозеленым цветом выделен арендодатель. То есть в этой таблице должны быть обязательно и данные вашего юридического лица.

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

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

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

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

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

Ну и с правого края указана ставка НДС. Поставьте в данной ячейке актуальную в вашей стране ставку.

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

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

 

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

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

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

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

Если вам не подходят формы формируемых первичных документов (счет на оплату и акт выполненных работ) - мы можем их изменить под ваши требования .

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

Приложение: Образцы формируемых документов

Вернуться к месту прочтения (гиперссылки на листе "Начисление аренды")