Аренда и управление недвижимостью: учет и отчетность по продажам

 

Какие задачи поможет решить Excel-модель?

Как учесть специфику бизнеса в справочниках Excel-модели?

Как создать прайс-лист?

Как сформировать информационную базу данных на основе Журнала учета договоров?

Как составить эффективные отчеты по аренде с использованием сводных таблиц?

 

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

КАКИЕ ЗАДАЧИ ПОМОЖЕТ РЕШИТЬ EXCEL-МОДЕЛЬ

Рассмотрим, какие задачи можно решить с помощью Excel-модели «Аренда и управление недвижимостью»:

 

1. Управлять реестром недвижимости

• вести иерархический справочник объектов недвижимости с широким набором характеристик;

• формировать реестр объектов недвижимости с возможностью гибкой группировки и отбора по различным характеристикам;

• «привязывать» объекты недвижимости к ценовым категориям, регистрировать цены на услуги с привязкой к ценовым категориям, хранить историю тарифов

2. Управлять договорами аренды

• гибко управлять способами начисления, ставками и тарифами на услуги, хранить историю изменения ставок и тарифов;

• многократно изменять ставки на услуги;

• учитывать аренду с перерывами;

• формировать графическую отчетность с цветовым выделением объектов в зависимости от их статуса и возможностью настройки состава выводимой информации;

• формировать реестр договоров аренды с возможностью гибкой группировки и отбора по различным характеристикам

3. Управлять взаиморасчетами по аренде

• рассчитывать аренду за неполный период;

• вести отчетность по взаиморасчетам с арендаторами с аналитикой по периодам, услугам, объектам аренды;

• формировать отчет об эффективности использования площадей с расчетом величины финансовых потерь от простоя объектов;

• анализировать продажи с развернутой детализацией и произвольной группировкой данных;

• планировать платежи по аренде и сопутствующим услугам, проводить план-фактный анализ

КАК УЧЕСТЬ СПЕЦИФИКУ БИЗНЕСА В СПРАВОЧНИКАХ EXCEL-МОДЕЛИ

Справочники — это основа рабочей модели данных компании. Обычно в дальнейшем их редко изменяют или добавляют новые. Используют на всех этапах работы в модели как часть аналитики.

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

Создаем простые справочники

Таблицы 1–9 — это простые справочники. Они представляют собой списки возможных значений того или иного параметра. Их используют, чтобы исключить неоднозначный ввод информации.

Мы будем составлять Excel-модель для условной компании ООО «Инвест». В управлении компании три здания (табл. 1) разного типа — офисы, склады, торговые площади (табл. 2).

В зависимости от типа объектов аренды и эксплуатационных характеристик, например ремонт эконом- или бизнес-класса (табл. 7), устанавливают различные тарифы (табл. 3).

Кроме непосредственно аренды, компания предоставляет клининговые, коммунальные услуги, услуги телефонии и т. д (табл. 5). Соответственно, и способ начисления арендной оплаты отличается (табл. 6):

  • индивидуальный график;
  • разовая оплата;
  • за м2 в месяц;
  • за объект в месяц.

Кроме того, для работы необходим справочник тарифов в условиях изменения арендных ставок с привязкой к дате изменения (табл. 8). Чтобы вести учет продаж по услугам и аренде, необходим справочник арендаторов (табл. 9).

 

 

Таблица 1. Здания

 

Таблица 2.

Тип объекта

 

Таблица 3.

Категория цен

 

Таблица 4.

Статус

 

Таблица 5.

Услуги

Здание

Фактический адрес

Общая площадь

Ед. изм.

 

Тип_объекта

 

Категория_цен

 

Статус

 

Услуга

Бизнес_центр_Южный

просп. Садовый, 53

2300

м кв.

 

Офисное помещение

 

Офисы

 

Арендуется

 

– Свободен

Торговый_центр_Меркурий

ул. Университетская, 102

1400

м кв.

 

Торговое помещение

 

Склады

 

В резерве

 

Аренда недвижимости

Промзона_Восточная

ул. Текстильщиков, 87

15 000

м кв.

 

Складское помещение

 

Торговые площади

 

План

 

Клининговые услуги

         

Бытовые услуги

 

Стандарт

 

Прочие услуги

 

Коммунальные услуги

         

Комплексный

     

Ремонт

 

Размещение рекламы

                 

Свободен

 

Телефония

 

Таблица 6. Способ начисления

 

Таблица 7. Характеристики

 

Таблица 8. Тарифы

 

Таблица 9. Арендаторы

Способ_начисления

 

Эксплуатационные_характеристики

 

Действующие_тарифы

 

Арендатор

Индивидуальный график

 

– Не указано

 

01.01.2021

 

– Свободно

Разовая оплата

 

Без ремонта

 

01.07.2021

 

ООО «Альфа»

Руб. за м кв./месяц

 

Бизнес-ремонт

   

 

ООО «Аршин»

Руб. за объект/месяц

 

Эконом-ремонт

   

 

Создаем многоуровневые справочники

Многоуровневые справочники содержат не только элементы, но и группы внутри справочника. Группы — это и есть уровни в справочниках.

Cправочник «Объекты»

Перейдем к более сложному справочнику «Объекты» (табл. 10). Он многоуровневый.

В нашем случае группы справочника — это здания, например, Бизнес_центр_Южный или Торговый_центр_Меркурий.

В дальнейшем при заполнении Журнала договоров при выборе здания Бизнес_центр_Южный в доступном к выбору списке объектов будут выведены только офисы и помещения именно этого здания, а при выборе Торгового_центра_Меркурий — только два павильона здания по ул. Университетская и т. д.

Чтобы справочник функционировал, важно соблюдать привязку объектов (Офис_101, Офис_102) к тому зданию, в котором они находятся. Тогда в первую очередь отображаются все помещения для сдачи в аренду Бизнес_центра_Южный, далее — все помещения Торгового_центра_Меркурий и т. д.

 

Таблица 10. Объекты

Объект кратко

Здания

Фактический адрес

Общая площадь

Ед. изм.

Объект недвижимости

Площадь объекта

Ед. изм. пл. объекта

Тип_объекта

Категория_цен

Эксплуатационные_характеристики

Бизнес_центр_Южный Офис_101

Бизнес_центр_Южный

просп. Садовый, 53

2300

м кв.

Офис_101

320

м кв.

Торговое помещение

Офисы

Эконом-ремонт

Бизнес_центр_Южный Офис_102

Бизнес_центр_Южный

просп. Садовый, 53

2300

м кв.

Офис_102

400

м кв.

Бытовые услуги

Офисы

Бизнес-ремонт

Бизнес_центр_Южный Офис_103

Бизнес_центр_Южный

просп. Садовый, 53

2300

м кв.

Офис_103

140

м кв.

Офисное помещение

Офисы

Бизнес-ремонт

 

 

 

 

 

 

 

 

 

 

Бизнес_центр_Южный Прочие площади

Бизнес_центр_Южный

просп. Садовый, 53

2300

м кв.

Прочие площади

626

м кв.

Комплексный

Стандарт

Эконом-ремонт

Торговый_центр_Меркурий Павильон_1

Торговый_центр_Меркурий

ул. Университетская, 102

1400

м кв.

Павильон_1

900

м кв.

Торговое помещение

Торговые площади

Эконом-ремонт

Торговый_центр_Меркурий Павильон_2

Торговый_центр_Меркурий

ул. Университетская, 102

1400

м кв.

Павильон_2

500

м кв.

Торговое помещение

Торговые площади

Эконом-ремонт

Промзона_Восточная Склад № 1

Промзона_Восточная

ул. Текстильщиков, 87

15 000

м кв.

Склад № 1

560

м кв.

Комплексный

Стандарт

Эконом-ремонт

Промзона_Восточная Склад № 2

Промзона_Восточная

ул. Текстильщиков, 87

15 000

м кв.

Склад № 2

320

м кв.

Офисное помещение

Стандарт

Эконом-ремонт

 

 

 

 

 

 

 

 

 

 

Промзона_Восточная Прочие помещения

Промзона_Восточная

ул. Текстильщиков, 87

15 000

м кв.

Прочие помещения

10 170

м кв.

Складское помещение

Склады

Без ремонта

 

При формировании справочника «Объекты» были задействованы простые справочники, которые подключены в качестве выпадающих списков для граф:

  • Здания;
  • Категория_цен;
  • Эксплуатационные_характеристики.

Вкладка Данные Работа с данными Проверка данных Тип данных Список.

В поле «Источник» указываем диапазон:

=Справочники!$A$2:$A$10 (Здания)

или

=ДВССЫЛ($I$1) (рис. 1).

В последнем случае $I$1 — это название столбца, которое одновременно является именем «умной» или отформатированной таблицы «Тип_объекта» (см. табл. 2). Эти способы подключения списков-справочников будем применять и в дальнейшем.

Графы таблицы 10:

  • Фактический адрес;
  • Общая площадь;
  • Ед. изм.

подтягиваются автоматически из табл. 1 на основании проставленных данных о здании по формулам ВПР (рис. 2):

=ВПР(B2;Здания[#Все];2;ЛОЖЬ);

=ВПР(B2;Здания[#Все];3;ЛОЖЬ);

=ВПР(B2;Здания[#Все];4;ЛОЖЬ),

где «Здания» — имя табл. 1, которая, как и другие таблицы Excel-модели, отформатированы (Вкладка Главная Стили Форматировать как таблицу);

«ЛОЖЬ» — точное совпадение. Точность совпадений мы предусмотрительно обеспечили, задав заполнение графы «Здания» выбором из списка.

Функции ВПР с обозначенными аргументами будем применять и в дальнейшем.

В графе «Объект кратко» табл. 10 задана автоматическая «сцепка» данных из двух столбцов:

=[@Здания]&" "&[@[Объект недвижимости]].

Аналогичные «сцепки» будем применять и в дальнейшем.

Вручную заполняются только графы:

  • Объект недвижимости;
  • Площадь объекта;
  • Ед. изм. пл. объекта.

 

Важно!

Заполнение справочника «Объекты» (табл. 10) не займет у вас много времени, но обеспечит базис для всей учетной системы.

 

Когда справочник «Объекты» сформирован, закрепим его многоуровневость, задав всем объектам недвижимости Бизнес-центра соответствующее имя, например: «Бизнес_центр_Южный» (имя в графе «В» с именем в справочнике «Здания» (см. рис. 1) должно точно совпадать).

В. С. Волкова, эксперт

Материал публикуется частично. Полностью его можно прочитать в журнале «Справочник экономиста» № 9, 2022.

Аренда и управление недвижимостью: учет и отчетность по продажам

 

Какие задачи поможет решить Excel-модель?

Как учесть специфику бизнеса в справочниках Excel-модели?

Как создать прайс-лист?

Как сформировать информационную базу данных на основе Журнала учета договоров?

Как составить эффективные отчеты по аренде с использованием сводных таблиц?

 

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

КАКИЕ ЗАДАЧИ ПОМОЖЕТ РЕШИТЬ EXCEL-МОДЕЛЬ

Рассмотрим, какие задачи можно решить с помощью Excel-модели «Аренда и управление недвижимостью»:

 

1. Управлять реестром недвижимости

• вести иерархический справочник объектов недвижимости с широким набором характеристик;

• формировать реестр объектов недвижимости с возможностью гибкой группировки и отбора по различным характеристикам;

• «привязывать» объекты недвижимости к ценовым категориям, регистрировать цены на услуги с привязкой к ценовым категориям, хранить историю тарифов

2. Управлять договорами аренды

• гибко управлять способами начисления, ставками и тарифами на услуги, хранить историю изменения ставок и тарифов;

• многократно изменять ставки на услуги;

• учитывать аренду с перерывами;

• формировать графическую отчетность с цветовым выделением объектов в зависимости от их статуса и возможностью настройки состава выводимой информации;

• формировать реестр договоров аренды с возможностью гибкой группировки и отбора по различным характеристикам

3. Управлять взаиморасчетами по аренде

• рассчитывать аренду за неполный период;

• вести отчетность по взаиморасчетам с арендаторами с аналитикой по периодам, услугам, объектам аренды;

• формировать отчет об эффективности использования площадей с расчетом величины финансовых потерь от простоя объектов;

• анализировать продажи с развернутой детализацией и произвольной группировкой данных;

• планировать платежи по аренде и сопутствующим услугам, проводить план-фактный анализ

КАК УЧЕСТЬ СПЕЦИФИКУ БИЗНЕСА В СПРАВОЧНИКАХ EXCEL-МОДЕЛИ

Справочники — это основа рабочей модели данных компании. Обычно в дальнейшем их редко изменяют или добавляют новые. Используют на всех этапах работы в модели как часть аналитики.

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

Создаем простые справочники

Таблицы 1–9 — это простые справочники. Они представляют собой списки возможных значений того или иного параметра. Их используют, чтобы исключить неоднозначный ввод информации.

Мы будем составлять Excel-модель для условной компании ООО «Инвест». В управлении компании три здания (табл. 1) разного типа — офисы, склады, торговые площади (табл. 2).

В зависимости от типа объектов аренды и эксплуатационных характеристик, например ремонт эконом- или бизнес-класса (табл. 7), устанавливают различные тарифы (табл. 3).

Кроме непосредственно аренды, компания предоставляет клининговые, коммунальные услуги, услуги телефонии и т. д (табл. 5). Соответственно, и способ начисления арендной оплаты отличается (табл. 6):

  • индивидуальный график;
  • разовая оплата;
  • за м2 в месяц;
  • за объект в месяц.

Кроме того, для работы необходим справочник тарифов в условиях изменения арендных ставок с привязкой к дате изменения (табл. 8). Чтобы вести учет продаж по услугам и аренде, необходим справочник арендаторов (табл. 9).

 

 

Таблица 1. Здания

 

Таблица 2.

Тип объекта

 

Таблица 3.

Категория цен

 

Таблица 4.

Статус

 

Таблица 5.

Услуги

Здание

Фактический адрес

Общая площадь

Ед. изм.

 

Тип_объекта

 

Категория_цен

 

Статус

 

Услуга

Бизнес_центр_Южный

просп. Садовый, 53

2300

м кв.

 

Офисное помещение

 

Офисы

 

Арендуется

 

– Свободен

Торговый_центр_Меркурий

ул. Университетская, 102

1400

м кв.

 

Торговое помещение

 

Склады

 

В резерве

 

Аренда недвижимости

Промзона_Восточная

ул. Текстильщиков, 87

15 000

м кв.

 

Складское помещение

 

Торговые площади

 

План

 

Клининговые услуги

         

Бытовые услуги

 

Стандарт

 

Прочие услуги

 

Коммунальные услуги

         

Комплексный

     

Ремонт

 

Размещение рекламы

                 

Свободен

 

Телефония

 

Таблица 6. Способ начисления

 

Таблица 7. Характеристики

 

Таблица 8. Тарифы

 

Таблица 9. Арендаторы

Способ_начисления

 

Эксплуатационные_характеристики

 

Действующие_тарифы

 

Арендатор

Индивидуальный график

 

– Не указано

 

01.01.2021

 

– Свободно

Разовая оплата

 

Без ремонта

 

01.07.2021

 

ООО «Альфа»

Руб. за м кв./месяц

 

Бизнес-ремонт

   

 

ООО «Аршин»

Руб. за объект/месяц

 

Эконом-ремонт

   

 

Создаем многоуровневые справочники

Многоуровневые справочники содержат не только элементы, но и группы внутри справочника. Группы — это и есть уровни в справочниках.

Cправочник «Объекты»

Перейдем к более сложному справочнику «Объекты» (табл. 10). Он многоуровневый.

В нашем случае группы справочника — это здания, например, Бизнес_центр_Южный или Торговый_центр_Меркурий.

В дальнейшем при заполнении Журнала договоров при выборе здания Бизнес_центр_Южный в доступном к выбору списке объектов будут выведены только офисы и помещения именно этого здания, а при выборе Торгового_центра_Меркурий — только два павильона здания по ул. Университетская и т. д.

Чтобы справочник функционировал, важно соблюдать привязку объектов (Офис_101, Офис_102) к тому зданию, в котором они находятся. Тогда в первую очередь отображаются все помещения для сдачи в аренду Бизнес_центра_Южный, далее — все помещения Торгового_центра_Меркурий и т. д.

 

Таблица 10. Объекты

Объект кратко

Здания

Фактический адрес

Общая площадь

Ед. изм.

Объект недвижимости

Площадь объекта

Ед. изм. пл. объекта

Тип_объекта

Категория_цен

Эксплуатационные_характеристики

Бизнес_центр_Южный Офис_101

Бизнес_центр_Южный

просп. Садовый, 53

2300

м кв.

Офис_101

320

м кв.

Торговое помещение

Офисы

Эконом-ремонт

Бизнес_центр_Южный Офис_102

Бизнес_центр_Южный

просп. Садовый, 53

2300

м кв.

Офис_102

400

м кв.

Бытовые услуги

Офисы

Бизнес-ремонт

Бизнес_центр_Южный Офис_103

Бизнес_центр_Южный

просп. Садовый, 53

2300

м кв.

Офис_103

140

м кв.

Офисное помещение

Офисы

Бизнес-ремонт

 

 

 

 

 

 

 

 

 

 

Бизнес_центр_Южный Прочие площади

Бизнес_центр_Южный

просп. Садовый, 53

2300

м кв.

Прочие площади

626

м кв.

Комплексный

Стандарт

Эконом-ремонт

Торговый_центр_Меркурий Павильон_1

Торговый_центр_Меркурий

ул. Университетская, 102

1400

м кв.

Павильон_1

900

м кв.

Торговое помещение

Торговые площади

Эконом-ремонт

Торговый_центр_Меркурий Павильон_2

Торговый_центр_Меркурий

ул. Университетская, 102

1400

м кв.

Павильон_2

500

м кв.

Торговое помещение

Торговые площади

Эконом-ремонт

Промзона_Восточная Склад № 1

Промзона_Восточная

ул. Текстильщиков, 87

15 000

м кв.

Склад № 1

560

м кв.

Комплексный

Стандарт

Эконом-ремонт

Промзона_Восточная Склад № 2

Промзона_Восточная

ул. Текстильщиков, 87

15 000

м кв.

Склад № 2

320

м кв.

Офисное помещение

Стандарт

Эконом-ремонт

 

 

 

 

 

 

 

 

 

 

Промзона_Восточная Прочие помещения

Промзона_Восточная

ул. Текстильщиков, 87

15 000

м кв.

Прочие помещения

10 170

м кв.

Складское помещение

Склады

Без ремонта

 

При формировании справочника «Объекты» были задействованы простые справочники, которые подключены в качестве выпадающих списков для граф:

  • Здания;
  • Категория_цен;
  • Эксплуатационные_характеристики.

Вкладка Данные Работа с данными Проверка данных Тип данных Список.

В поле «Источник» указываем диапазон:

=Справочники!$A$2:$A$10 (Здания)

или

=ДВССЫЛ($I$1) (рис. 1).

В последнем случае $I$1 — это название столбца, которое одновременно является именем «умной» или отформатированной таблицы «Тип_объекта» (см. табл. 2). Эти способы подключения списков-справочников будем применять и в дальнейшем.

Графы таблицы 10:

  • Фактический адрес;
  • Общая площадь;
  • Ед. изм.

подтягиваются автоматически из табл. 1 на основании проставленных данных о здании по формулам ВПР (рис. 2):

=ВПР(B2;Здания[#Все];2;ЛОЖЬ);

=ВПР(B2;Здания[#Все];3;ЛОЖЬ);

=ВПР(B2;Здания[#Все];4;ЛОЖЬ),

где «Здания» — имя табл. 1, которая, как и другие таблицы Excel-модели, отформатированы (Вкладка Главная Стили Форматировать как таблицу);

«ЛОЖЬ» — точное совпадение. Точность совпадений мы предусмотрительно обеспечили, задав заполнение графы «Здания» выбором из списка.

Функции ВПР с обозначенными аргументами будем применять и в дальнейшем.

В графе «Объект кратко» табл. 10 задана автоматическая «сцепка» данных из двух столбцов:

=[@Здания]&" "&[@[Объект недвижимости]].

Аналогичные «сцепки» будем применять и в дальнейшем.

Вручную заполняются только графы:

  • Объект недвижимости;
  • Площадь объекта;
  • Ед. изм. пл. объекта.

 

Важно!

Заполнение справочника «Объекты» (табл. 10) не займет у вас много времени, но обеспечит базис для всей учетной системы.

 

Когда справочник «Объекты» сформирован, закрепим его многоуровневость, задав всем объектам недвижимости Бизнес-центра соответствующее имя, например: «Бизнес_центр_Южный» (имя в графе «В» с именем в справочнике «Здания» (см. рис. 1) должно точно совпадать).

В. С. Волкова, эксперт

Материал публикуется частично. Полностью его можно прочитать в журнале «Справочник экономиста» № 9, 2022.

Антисанкционные меры — 2022 (специальные экономические меры и меры, направленные на поддержку бизнеса и граждан)
Управленческий учет и отчетность
Подборки
Подборки
Типичные ошибки построения системы бюджетного управления
Подарок подписчикам журнала «Справочник экономиста» или «Планово-экономический отдел» на 1 полугодие 2018 года
Подписаться на новости
Подписка для физических лицДля физических лиц Подписка для юридических лицДля юридических лиц Подписка по каталогамПодписка по каталогам