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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

или

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Важно!

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

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