ФИНАНСОВАЯ МОДЕЛЬ ТОРГОВОЙ ДЕЯТЕЛЬНОСТИ

• Торговля - двигатель экономики. Чтобы он не заглох, используйте наши финансовые модели для расчета будущей эффективности своей торговой сети. Приобретайте финансовые модели - с нами эффективнее!;
• классический ритейл с детализацией по направлениям бизнеса, регионам и товарным категориям 
• принцип бюджетирования «сверху вниз» 
• аутсорсинг логистических услуг 
• расчет Cash Flow, кассовых разрывов и их кредитование через овердрафт 
• отчет о возрастной структуре стока 
• экономика на один заказ 
• функциональный и маржинальный P&L 
• расчет оборачиваемостей и финансового цикла 
• управленческий баланс 
• расчет точки безубыточности 
• и т.д.

ИНСТРУКЦИЯ

Проект: Ритейл, Top-Down, P&L, Stock Flow, ОперАутсорсинг

Вкладки PL и UE - Отчеты финансовой модели

Входящие параметры финансовой модели - ячейки с выпадающим списком возможных значений

Во вкладке условия - ячейки с внесением любых цифровых данных вручную

Во вкладке CF_условия ячейки со * - поле справа от красной звездочки обязательно для заполнения

Для удобства работы с финансовой моделью во вкладке условия в первых 6-ти строках в столбцах P – AA приведена выжимка из отчета о прибылях и убытках, из вкладки PL, значений нижеследующих ключевых показателей:

Выручка

Валовая прибыль

Рентабельность продаж

Маржинальная прибыль

Рентабельность операционной деятельности

EBITDA

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

В случае, если при внесении изменений в исходные данные ничего не меняется в отчетах, то возможно у Вас в настройках включен ручной режим пересчета формул. В этом случае либо нажмите F9, либо включите режим автоматического пересчета формул!

Ячейки с выпадающим списком возможных значений

Во вкладке условия в ячейке M14 происходит разбивка по периодам, возможные значения:

ежемесячно

ежеквартально

При наведении мышки на ячейку, справа появляется кнопка, при нажатии на которую появляется выпадающий список возможных значений: ежемесячно/ежеквартально; другие значения недопустимы для данной модели

В случае если Ваша версия Excel не поддерживает функцию выпадающего списка и при наведении на M14 список не выпадает, то вручную вносите значения из списка выше!

В зависимости от выбора, бюджетный год (ячейки P9-AA10) разделяется на месяцы или кварталы соответственно

Во вкладке условия в ячейке M16 указывается метод бюджетирования, возможные значения:

финансовый – прямой

финансовый - приросты г/г

финансовый - приросты пер/пер

товары – прямой

товары - приросты г/г

товары - приросты пер/пер

заказы – прямой

заказы - приросты г/г

заказы - приросты пер/пер

При наведении мышки на ячейку, справа появляется кнопка, при нажатии на которую появляется выпадающий список возможных значений: см. список выше; другие значения недопустимы для данной модели

В случае если Ваша версия Excel не поддерживает функцию выпадающего списка и при наведении на M16 список не выпадает, то вручную вносите значения из списка выше!

В зависимости от выбора Метода бюджетирования Вам будет предложена соответствующая последовательность ввода входящих параметров финансовой модели в блоке строк 18-26

Метод бюджетирования: финансовый – прямой

При выборе данного метода в начале необходимо внести план продаж товаров на бюджетный год в тысячах рублей вручную в ежеквартальной или ежемесячной разбивке: ячейки P18-S18 или P18-AA18 соответственно

Метод бюджетирования: финансовый - приросты г/г

При выборе данного метода в начале необходимо внести факт продаж товаров за год предшествующий бюджетному году в тысячах рублей вручную в ежеквартальной или ежемесячной разбивке: ячейки P20-S20 или P20-AA20 соответственно

После чего необходимо внести вручную плановые приросты (год к году) продаж товаров для соответствующих кварталов/месяцев в %-тах: ячейки P22-S22 или P22-AA22 соответственно

Например, если фактические продажи в январе прошлого года составили 100тыс.руб. и предполагается, что в следующем бюджетном году прирост в январе составит 10% или в тыс.руб. 110=100*(1+10%), то при выборе ежемесячной разбивки, в ячейку P20 необходимо внести 100, а в ячейку P22 - прирост 10%

Метод бюджетирования: финансовый - приросты пер/пер

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

После чего необходимо внести вручную плановые последовательные приросты (месяц к предыдущему месяцу) продаж товаров для соответствующих месяцев бюджетного года в %-тах: ячейки P26-AA26

Например, если фактические продажи в декабре прошлого года составили 100тыс.руб. и предполагается что в январе бюджетного года прирост по отношению к факту декабря составит 10% или в тыс.руб. 110=100*(1+10%), а прирост февраля бюджетного года к январю этого же года планируется в размере 20% или в тыс.руб. 132=110*(1+20%), то в ячейку AA24 необходимо внести 100, в ячейку P26 - прирост 10%, в ячейку Q26 - прирост 20%. Тогда в ячейках P28 и Q28 появятся планы продаж на январь и февраль бюджетного года, равные 110 и 132 тыс.руб. соответственно. И т.д.

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

После чего необходимо внести вручную плановые последовательные приросты (квартал к предыдущему кварталу) продаж товаров для соответствующих кварталов бюджетного года в %-тах: ячейки P26-S26

Например, если фактические продажи в 4-ом квартале прошлого года составили 100тыс.руб. и предполагается что в 1-ом квартале бюджетного года прирост по отношению к факту 4-ого квартала составит 10% или в тыс.руб. 110=100*(1+10%), а прирост 2-ого квартала бюджетного года к 1-ому кварталу этого же года планируется в размере 20% или в тыс.руб. 132=110*(1+20%), то в ячейку S24 необходимо внести 100, в ячейку P26 - прирост 10%, в ячейку Q26 - прирост 20%. Тогда в ячейках P28 и Q28 появятся планы продаж на 1-ый и 2-ой квартал бюджетного года, равные 110 и 132 тыс.руб. соответственно. И т.д.

Метод бюджетирования: товары – прямой, товары - приросты г/г, товары - приросты пер/пер

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

Метод бюджетирования: заказы – прямой, заказы - приросты г/г, заказы - приросты пер/пер

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

Продажи товаров

После задания вручную характеристик объемов продаж в строке 28 производится окончательный расчет планового объема продаж в заданных единицах измерения (тыс. руб., шт товаров, кол-во заказов)

Во вкладке условия в ячейке M30 отмечается детализация по направлениям бизнеса, возможные значения:

да

нет

При наведении мышки на ячейку, справа появляется кнопка, при нажатии на которую появляется выпадающий список возможных значений: да/нет; другие значения недопустимы для данной модели

В случае если Ваша версия Excel не поддерживает функцию выпадающего списка и при наведении на M30 список не выпадает, то вручную вносите значения из списка выше!

При положительном выборе (да), будет предложено в строке 33 внести долю в %-тах от общего объема продаж по направлению онлайн VMI-торговли, естественно пользователь может поменять направление деятельности на свое усмотрение в зависимости от специфики собственной компании - во вкладке детализации, в ячейке F10

При положительном выборе (да), в строке 32 будет автоматически для каждого периода произведен расчет доли в %-тах от общего объема продаж по направлению оффлайн-торговли, как 100% минус %-нт из 33-тьей строки, естественно пользователь может поменять направление деятельности на свое усмотрение в зависимости от специфики собственной компании - во вкладке детализации, в ячейке F9

Таким образом, модель предусматривает возможность детализации бизнеса по двум принципиальным направлениям

Во вкладке условия в ячейке M37 отмечается региональная детализация, возможные значения:

да

нет

При наведении мышки на ячейку, справа появляется кнопка, при нажатии на которую появляется выпадающий список возможных значений: да/нет; другие значения недопустимы для данной модели

В случае если Ваша версия Excel не поддерживает функцию выпадающего списка и при наведении на M37 список не выпадает, то вручную вносите значения из списка выше!

При положительном выборе (да), будет предложено в строках 40 и 45 внести долю в %-тах от объема продаж соответствующего направления торговли для региональных продаж, естественно пользователь может поменять регион на свое усмотрение в зависимости от специфики собственной компании - во вкладке детализации, в ячейке H10

При положительном выборе (да), в строках 39 и 44 будет автоматически для каждого периода произведен расчет соответствующей доли продаж по Москве и МО, естественно пользователь может поменять регион на свое усмотрение в зависимости от специфики собственной компании - во вкладке детализации, в ячейке H9

Таким образом, модель предусматривает возможность детализации бизнеса по двум региональным направлениям продаж

В том случае если в ячейке M30 выбрано значение нет, т.е. детализация по направлениям бизнеса отсутствует, то региональная детализация задается аналогично детализации по направлениям бизнеса через строку 40

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

Аналогично первым двум описанным выше детализациям

Продажи товаров

ЕСЛИ пользователь модели дочитал до настоящего раздела подробно изложенный выше текст вместе с параллельным детальным анализом того, что происходит во вкладке условия при различных комбинациях выбора тех или иных детализаций и подходов к запуску задания плана продаж, ТО в этом случае пользователь мог убедиться в том, что коммуникация в модели устроена на основе следующих основных принципах:

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

Для корректности ввода начальных данных необходимо и достаточно следовать в буквальном смысле тому, что предлагает модель заполнить напротив звездочек (см. начало методологии) красного цвета в ограниченных пунктиром ячейках, а также внимательно отслеживать то, чтобы нигде не появлялось сообщение Ошибка!

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

Итак переходим к структуре продаж

Отталкиваться, как видимо уже стало понятным, в модели возможно от денег, от количества штук товаров и от количества заказов. Причем, если отталкиваться от стоимостей продаж, то в этом случае модель Вам предложит выбор - либо не задавать размер среднего чека и тогда модель не будет производить расчеты в операционных натуральных величинах, таких как количества. Либо задавать размер среднего чека и, тем самым, модель будет производить не только финансовые расчеты, но также и операционные, что немаловажно при планировании операционных затрат. Например, логистические затраты могут быть (а чаще всего это так и бывает) зависимы от количества перевозимых заказов или товаров - что невозможно будет учесть в случае чисто финансовых подходов, которые обычно используют подход расчета переменных расходов просто через %-нт от выручки, выраженной в деньгах

Аналогично будет предложено пользователю на выбор - задать среднее количество товаров в одном заказе

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

Рентабельность в модели - это классическая рентабельность продаж - %-нт валовой прибыли в выручке

Распределение по периодам

Поскольку речь идет о розничной торговле, в которой ежедневно количество единичных операций продажи может доходить до тысяч, все основные расчеты модели проводятся на уровне ежедневной детализации во вкладке расчеты_ежедн

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

Для задания ежедневного распределения выручки на весь бюджетный год в строках 175-191 вкладки условия пользователю необходимо задать еженедельное распределение продаж по дням недели - например, если это розничный классический магазин, то необходимо учесть выходные дни, при их наличии, а вот у онлайн торговли таких дней нет. В результате чего в строках до 264-ой во вкладке расчеты_ежедн рассчитываются ежедневные планы продаж для всех выбранных комбинаций направлений продаж. После чего оттуда они подтягиваются во вкладку PL и распределяются по месяцам или кварталам отчета о прибылях и убытках

Отдельно отметим, что под операционным циклом онлайн-продаж понимается срок между оформлением заказа клиентом на сайте компании и его доставкой, поэтому срок закупки для VMI-продаж имеет отрицательное значение, о чем пользователь будет проинформирован красным шрифтом

ГЛАВНАЯ МЕТОДОЛОГИЧЕСКАЯ ОСОБЕННОСТЬ

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

ВСЕ ПЕРИОДЫ ОБОРАЧИВАЕМОСТИ УЧИТЫВАЮТСЯ ПРЯМЫМ СПОСОБОМ ЕЖЕДНЕВНОГО РАСЧЕТА!!! Т.е. модель не использует классических расчетов через формулы оборачиваемости, которые приводятся во всех учебниках по финансовому управлению - на нашем сайте mngmnt.ru мы неоднократно приводим примеры практической непригодности данных формул

Приведем пример для понимания

Допустим наша модель распределила план продаж таким образом, что на 1-ое октября приходится ежедневный план продаж в размере 100 рублей и мы задаем во вкладке CF_условия срок оборачиваемости дебиторской задолженности в размере 5-ти дней. Тогда модель при формировании отчета о движении денежных средств во вкладке CF в части поступлений денежных средств, обратится за расчетом во вкладку расчеты_ежедн, где в свою очередь в строках 463-475 рассчитываются даты поступления выручки в виде притока денежных средств на расчетный счет компании, путем сдвига плана продаж на заданные 5-ть дней периода оборачиваемости дебиторской задолженности. Таким образом, напротив 100 рублей выручки в столбце соответствующем 1-ому октября (кстати это столбец KC, если речь идет о 2016 годе - не поленитесь, проверьте!) вкладки расчеты_ежедн будет стоять 6-тое октября

Поэтому еще одна вытекающая из этого методологическая особенность состоит в том, что суммирование в отчетных вкладках таких, как PL, CF и т.д. не всегда производится по строке 7 дат бюджетного года вкладки расчеты_ежедн (что вообще говоря в классических моделях является естественным), а в тех случаях, когда производится расчет показателей оборота капитала или составляющих компонент финансового цикла, то суммирование происходит по расчетным датам таким, как в предыдущем примере. Пользователь легко может в этом убедиться, например, посмотрев формулы в ячейках строк начиная с 36-ой вкладки CF

ВСЕ ОСТАЛЬНОЕ АНАЛОГИЧНО ВЫШЕСКАЗАННОМУ

Больше никаких методологических нюансов в данной модели нет. То есть расчет каждого показателя в модели полностью производится по принципам аналогичным вышеизложенным