УНИВЕРСАЛЬНАЯ ПРОИЗВОДСТВЕННАЯ ФИНАНСОВАЯ МОДЕЛЬ ВЕРСИИ 2.0

• срок моделирования от 1 года до 20 лет;
• инвестиции в производственную инфраструктуру;
• 4-ре типа капвложений: земля, строительство, оборудование, транспорт с привязкой к цеховой детализации;
• до 3-ех цехов с возможностью их включения/выключения;
• номенклатура готовой продукции до 28-ми позиций с привязкой к цеховой детализации;
• подробная себестоимостная структура: до 50-ти ингредиентов для каждой номенклатурной позиции;
• 2 подхода формирования бюджета продаж: коммерческий через торговую наценку и маркетинговый через среднерыночный чек;
• расчет однопараметрического коммерческого риска 
• без коэффициентов оборачиваемости:
• прямой расчет финансового потока через отсрочки платежей;
• подробное структурирование расходной части: постоянные / переменные / производственные / коммерческие / управленческие расходы 
• основные отчеты PL, CF, BS;
• функциональный и маржинальный P&L;
• кредитование кассовых разрывов Cash Flow - овердрафт;
• NPV-анализ, расчет IRR методом дихотомии;
• возможность масштабирования по любым направлениям.

ИНСТРУКЦИЯ

Проект: Создание производства

Входящие параметры финансовой модели

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

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

Данные ячейки принимают оранжевую заливку, если они пустые

Предлагается внести данные в поле справа от красной звездочки

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

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

Вкладка: управление

В ячейку F9 пользователь вручную вносит курс доллара

Курс доллара используется в модели только во вкладке себестоимость в столбце L, т.е. учитывается возможность закупки товара, цена которого исчисляется в долларах. В случае если все цены рублевые, то курс доллара можно выбрать равным единице, и в столбце K вкладки себестоимость задавать рублевые цены

Вкладка: управление

Значения в ячейках P9 и S9 пользователь выбирает из выпадающего списка

В P9 задается год начала проекта, в S9 - завершения

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

Например, в нашем случае в ячейках соответственно стоят выбранные значения: J-18 и J-34, что означает, что началом проекта является 2018-тый год, а годом окончания - 2034-тый. При этом в строках 6 и 7 автоматически выделяются соответствующие ячейки (закрашиваются синим), которые определяют столбцы под каждый год из заданного периода проекта. Строка 6 - даты начала годов, строка 7 - окончания годов

В случае если Ваша версия Excel не поддерживает функцию выпадающего списка, то обратитесь к нам или самостоятельно настройте на массив ячеек D12-D42 вкладки списки

Вкладка:управление

В ячейки D13-J13 пользователь вручную вводит названия Своих цехов

Модель позволяет использовать три цеха

Если необходимо большее кол-во цехов, то обратитесь к нам

Вкладка:управление

В ячейке F13 значения выбираются пользователем из выпадающего списка

В ячейках L13 и S13 выбираются годы запуска каждого из цехов

В случае, если необходимо отключить цех, то выбираем нет

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

В случае, если Ваша версия Excel не поддерживает функцию выпадающего списка, то обратитесь к нам или самостоятельно настройте на массив ячеек D11-D42 вкладки списки

Вкладка:управление

В строках: 17-93 cводный блок ключевых отчетов и экономических показателей

Вернемся к описанию этого блока ниже

Вкладка:капитал

Строки:11-80Типичный блок для задания условий на кап/затраты

Выделяется всего 30-ть таких блоков - по 10-ть для каждого цеха, Каждому блоку соответствует какой-либо из объектов кап/затрат из списков, находящихся в столбцах P,S и V вкладки списки. Значения этих списков поставляются из вкладки управление, из блоков ячеек: P51-P60; P67-P76; P83-P92 - где можно самостоятельно вручную задать свои названия объектов кап/вложений

Предполагается, что каждый объект кап/затрат формируется (покупается, строится и т.д.) в течение не более 5ти лет, чему соответствуют столбцы: T,U,V,W,X

Общая сумма кап/затрат на один объект

В строке 11 задается общая сумма кап/затрат для каждого года

В ячейке M13 из выпадающего списка выбирается метод распределения общих годовых сумм кап/затрат по месяцам каждого года. Возможные значения методов следующие:

равномерно по году

При выборе этого метода в блоке строк 33-44 автоматически общие суммы из строки 11 равномерно распределяются по месяцам (делятся на 12ть и значение проставляется в каждую ячейку диапазона соответствующего столбца)

равномерно с … по …

При выборе этого метода в блоке строк 15-16 с использованием выпадающих списков вручную задаются годы начала и завершения равномерного распределения кап/затрат из строки 11, после чего в соответствующих выбранным месяцам ячейках из диапазона строк 33-44 равномерно распределяются суммы кап/затрат

вручную, доли %

При выборе этого метода в блоке строк 19-30 предлагается вручную задать процентное распределение для каждого года общих сумм кап/затрат из строки 11, после чего в блоке строк с 33 по 44 автоматически появятся соответствующие ежемесячные суммы кап/затрат

В строке 46 задаются номера месяцев ввода объектов кап/затрат в эксплуатацию, по умолчанию, если таковые месяцы не заданы, то берется последний месяц с ненулевой суммой кап/затрат

В ячейке Q48 с помощью формулы забирается количество лет амортизации с листа управление из блоков ячеек: T51-T60; T67-T76; T83-T92, где пользователь их вносит вручную на свое усмотрение

График оплат

В ячейке M50 с помощью выпадающего списка задается метод распределения оплат кап/затрат

Методы распределения оплат следующие:

один-в-один с работами

При выборе этого метода оплаты в точности будут повторять распределение кап/затрат и автоматически будут внесены в соответствующие ячейки строк с 67ой по 78ую

аванс+опл+ГО

При выборе этого метода в ячейке Q53 необходимо задать процент предоплаты, после чего общие суммы кап/затрат будут распределены по принципу: в первый месяц аванс (заданный в ячейке Q53) после чего равномерное распределение доплат в соответствии с объемами затрат из блока строк 33-44

вручную, доли %

Аналогично такому же методу распределения затрат - действует независимо от принципа распределения кап/затрат

Далее по аналогии

Для остальных 29-ти блоков задания других типов кап/затрат необходимо повторить по аналогии все описанные выше действия для блока строк с 11-той по 80-тую

Попадание в расчеты модели

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

Вкладка:продукция

В столбцах G и I происходит задание номенклатуры готовой продукции

В столбце G для каждого цеха вручную задаются названия готовой продукции (ГП), идущей на продажу

В модели предусмотрена возможность задания максимум 6-ти наименований ГП для первого цеха и по 11-ть для 2-ого и 3-его

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

В столбце I можно включать и выключать каждую из номенклатурных позиций из производства и продажи, для этого достаточно напротив соответствующей позиции поставить либо 1, либо 0 соответственно

Вкладка:себестоимость

Задание себестоимости готовой продукции

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

Например, для Цеха-1 в строках с 8-ой по 37-ую можно задать до 30-ти различных ингредиентов каждой из 6-ти номенклатурных позиций ГП Цеха-1. Путем копирования 38-ой строки внутрь блока можно добавлять ингредиенты. А начиная с 39-той строки и по 74-ую задаются уникальные себестоимостные ингредиенты для каждой из 6-ти номенклатурных позиций ГП Цеха-1 - по 5-ть для каждой и с возможностью добавления, аналогичной описанной выше для общих ингредиентов

Начиная с 75-той строки и ниже себестоимость для остальных ГП задается аналогично

Вкладка:условия

Здесь задаются все операционные (оборотные) условия модели

ЦЕХ-1. В ячейке Q12 происходит задание максимальной выработки ГП в месяц

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

ЦЕХ-1. В ячейке Q14 происходит задание максимальной емкости склада ГП

Задается %-нт от максимальной выработки ГП Цеха, который можно разместить на складе готовой продукции. Например, если 0%, то это будет означать, что склада ГП нет. Если по результатам расчета модели, получится, что на складе должно находиться в некий момент времени большее чем указанное количество ГП, то модель автоматически добавит в расходы модели затраты на аутсорсинг склада ГП

ЦЕХ-1. В ячейке Q16 происходит задание максимальной емкости склада сырья и материалов (СиМ)

Задается %-нт от себестоимостных сырья и материалов, соответствующих максимальной выработке ГП Цеха, который можно разместить на складе СиМ. Например, если 0%, то это будет означать, что склада СиМ нет. Если по результатам расчета модели, получится, что на складе СиМ должно находиться в некий момент времени большее чем указанное количество СиМ в пересчете на единицы ГП, то модель автоматически добавит в расходы модели затраты на аутсорсинг склада СиМ

ЦЕХ-1. В строках 18-24 происходит задание маркетинговых характеристик ГП

В строке 18 задается оценка количества потенциальных клиентов - покупателей ГП Цеха-1 (например, если это розничная продажа, то кол-во человек), в строке 20 задается целевая доля собственного рынка сбыта. В строке 24 задается частота покупок в год одним клиентом, т.е. сколько единиц ГП один клиент в среднем в год покупает

ЦЕХ-1. Операции

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

Для проведения ежемесячных расчётов во вкладке monthly в блоке строк Q28-Q39 вкладки условия задается долевое помесячное распределение объемов продаж в натуральных величинах - например, если продажи товаров носят сезонный характер

В блоке строк с 42ой по 47ую для каждого года проекта задается целевое долевое распределение продаж в единицах ГП по номенклатуре продукции

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

Путем наложения условия на максимальную выработку ГП, а также условий на периоды оборачиваемости в строке 30, начиная со столбца T, формируется итоговый план продаж в натуральных величинах

В блоке ячеек T53-T58 с использованием данных из вкладок продукция и себестоимость задаются для каждой активной номенклатурной позиции ГП ее себестоимость. После чего с использованием заданной инфляции из строки 60 в строках 53-58, начиная со столбца U, рассчитываются себестоимости для следующих годов проекта

В блоке строк 63-68 необходимо задать торговые наценки для каждого года и каждой номенклатурной позиции ГП

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

Наконец в строке 70 с использованием расчетов из вкладки monthly получаем бюджет продаж в деньгах в разрезе ГП Цеха-1

Далее в блоках строк с 74той по 147ую и со 151ой по 224ую соответственно для второго и третьего Цехов производятся аналогичные расчеты

Периоды оборачиваемости задолженностей

Для целей расчета операций притока и оттока денежных средств, чтобы корректно построить CashFlow (ДДС - вкладка CF") в строках 227 и 229 задаются целевые периоды оборачиваемости в днях дебиторской и кредиторской задолженностей, отражающих договорные финансовые условия с покупателями ГП и поставщиками СиМ (других контрагентов эти данные не включают)

Начиная с 233-ей строки и ниже во вкладке условия необходимо просто читать название показателя и вносить соответствующие исходные данные в ячейки, напротив которых стоит красная звездочка. Здесь вносятся все условия на расходы, номенклатуру которых можно посмотреть во вкладках PL_m и PL_f. Также здесь вносятся ставки налогов, дисконтирования и кредитования, а также остальные периоды оборачиваемости кредиторки при оплате различных типов расходов

Вкладка: маркетинг

Расчет коммерческих рисков

На примере Цеха-1. В блоке ячеек Q35-Q40 вносим средние чеки продажи ГП в разрезе номенклатуры, согласно маркетинговому исследованию рынка сбыта. В строке 42 вносим инфляционные ожидания, не обязательно равные тем, которые указаны во вкладке условия - это может быть независимый взгляд другого подразделения компании или каких-либо экспертов. Далее в строке 30-ть рассчитываем разницу между Бюджетом продаж из вкладки условия и Бюджетом продаж, рассчитанным через средний чек маркетингового видения. Таким образом, получаем в деньгах оценку коммерческого риска, или в %-тах к Бюджету продаж модели в строке 28. И так для каждого цеха, после чего все это суммируется в итоговые строки 14 и 16, откуда попадает на лист управление в блок ячеек D37-H41

Вкладка: управление

В строках 17-93 cводный блок ключевых отчетов и экономических показателей

В блоке строк 49-61, начиная со столбца V производится помесячное распределение совокупных кап/затрат на Цех-1 заданных во вкладке капитал, с учетом заданного в ячейке F13 года начала формирования Цеха-1

Блок ячеек P51-T60 представлен выше.

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

В блоках строк 65-77 и 81-93 производятся аналогичные расчеты для второго и третьего цехов

В блоке строк 33-45, начиная со столбца V, производится распределение операционного финансового потока (из вкладки monthly) по месяцам для определения максимального кассового разрыва и соответственно расчета объема кредитных средств (в блоке ячеек P34-S45), которые помимо инвестиционных необходимо привлечь для ведения операционной (оборотной) деятельности проекта. В модели предполагается, что эти средства привлекаются в проект на пополнение оборотных средств по принципу овердрафтного кредитования, как наиболее экономного. В 378-ой строке вкладки условия можно задать %-ную ставку указанного кредитования

В блоке ячеек D17-F23 приведен расчет источников финансирования проекта: инвестиции, кредит (овердрафт) и чистая операционная выручка (аналог валовой прибыли, только посчитанной по отчету о ДДС). Соответственно в блоке ячеек J17-L29 представлена структура направлений, по которым были потрачены денежные средства из указанных выше источников

В блоке ячеек D25-H29 собраны все ключевые инвестиционные показатели проекта с листа NPV

В блоке строк 26-29, начиная со столбца V, предоставлен расчет получения и возвратов кредитных средств, оплаты %-тов банку по овердрафту, а также итоговый финансовый поток проекта с закрытием кассовых разрывов

В итоговых отчетных вкладках NPV, PL, CF и BS - просто классически с использованием формул представлена отчетная информация по спискам ключевых финансово-экономических и инвестиционных показателей

Вкладка: KPI

Система KPI

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

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

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

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