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

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

ИНСТРУКЦИЯ

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

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

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

Во вкладке 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-продаж имеет отрицательное значение, о чем пользователь будет проинформирован красным шрифтом