Как построить финансовую модель в excel пошагово

Как построить финансовую модель в Excel: пошаговая инструкция

Финансовая модель — это функциональный инструмент, который поможет оперативно просчитать влияние различных факторов и изменений на результаты работы и финансовое состояние организации. За счёт формул и уравнений расчётов после изменения исходных данных все зависимые характеристики, будут пересчитаны программой автоматически.

Финансовая модель — это функциональный инструмент, который поможет оперативно просчитать влияние различных факторов и изменений на результаты работы и финансовое состояние организации. За счёт формул и уравнений расчётов после изменения исходных данных все зависимые характеристики, будут пересчитаны программой автоматически.

Для финансового моделирования наиболее удобен и доступен формат Excel. Использовать его могут как бухгалтеры, так и руководители различных уровней.

Набор показателей для финансовой модели зависит от специфики деятельности организации. Основных групп, как правило, четыре:

• активы — имущество, принадлежащее организации;
• пассивы — обязательства;
• финансовые потоки — различные денежные поступления и платежи;
• доходы и расходы.

Результаты расчётов можно представить в виде баланса, отчётов о доходах и расходах и о движении денежных средств (по состоянию на определённую дату).

Алгоритм построения финансовой модели

Начните финансовое моделирование в Excel с создания простой модели. Из внешних параметров можно, например, взять стоимость продукции и спрос на неё. Например, в качестве внутренних показателей можно взять размер выручки и затрат. На первом этапе количество элементов может быть минимальным, а особой точностью можно пренебречь. Основная цель — установить рабочие взаимосвязи для автоматического пересчёта. Далее модель можно развивать, детализировать и усложнять.

Рассмотрим пример простого моделирования в Excel с небольшим количеством ключевых переменных. Для каждой таблицы необходимо отвести отдельную страницу.

1. Задаём исходные параметры

Попробуем спрогнозировать выручку. За основу можно взять план реализации товаров или услуг за год. Размер выручки на первом этапе можно округлить или указать приблизительные данные (рисунок 1).

Рисунок 1 — План реализации услуг (тыс.руб.)

Выручка рассчитывается как сумма услуг.

2. Определяем переменные затраты

В качестве переменных введём затраты на заработную плату сотрудникам. Допустим, она зависит от объёма реализованных услуг и составляет 25% от выручки. Зарплата рассчитывается помесячно как произведение коэффициента 0,25 (25/100) и плана продаж на конкретный месяц.

Расходы на аренду и управление внесём как фиксированные значения.

Например, чтобы посчитать зарплату за январь, берём план реализации на январь и умножаем на коэффициент

151 * 0,25 = 38 тыс. руб.

В Excel прописываем формулу: fx = 0,25*название страницы с таблицей по плану реализации!B8

Общий размер планируемых затрат будет равен сумме зарплаты, аренды и управленческих расходов (рисунок 2).

Рисунок 2 — План затрат

3. Составляем план доходов и расходов

Обратите внимание на строки «операционные доходы» и «операционные расходы» (рисунок 3). Чтобы их заполнить, потребуется прописать ссылки на соответствующие ячейки функциональных планов.

Рисунок 3 — План доходов и расходов, тыс. руб.

Так, операционные доходы будут равны суммам от услуг 1-4. Например, чтобы рассчитать операционный доход за январь, складываем 15+30+46+60. Получаем 151 тыс. руб.

В формулу прописываем: fх =СУММ(B5:B8)

Числовые значения по услугам прописываем ссылками на ячейки таблицы «План реализации услуг».

Графа «Итого» считается как сумма ячеек с B4 по M4

Аналогично рассчитываются операционные расходы. Данные синхронизируем с таблицей «План затрат».

Операционная прибыль рассчитывается как разность операционные доходы — операционные расходы.

Например, операционная прибыль за январь равна: 151 — 96 = 55 тыс. руб.

Рентабельность рассчитывается как отношение операционной прибыли к операционному доходу помноженное на 100.

За январь получаем: 55/151*100 = 36,69%

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

Прибыль нарастающим итогом — это прибыли (убытки) за прошлый и текущий отчётные периоды. В январе мы берём данные операционной прибыли, равные 55 тысячам рублей. В феврале прибавляем 58 тысяч. Получаем 113 тысяч рублей. В марте прибавляем ещё 64 тысячи. Получаем 176 тысяч. И так суммируем по каждому месяцу.

В нашем финансовом плане прибыль нарастающим итогом за февраль будет прописываться формулой: fх=B15+C13

4. Составляем план движения денежных средств

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

Платежи по заработной плате и аренде происходят в месяце, следующем за месяцем их начисления, а управленческие расходы — в месяц их осуществления (рисунок 4).

Рисунок 4 — План движения денежных средств, тыс. руб.

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

Сальдо по операционной деятельности — это разность поступлений по операционной деятельности и платежей по операционной деятельности.

Сальдо операционной деятельности за январь будет равно: 151 — 38 = 113 тысяч рублей.

Сальдо на конец периода рассчитывается как сумма сальдо на начало периода и сальдо по операционной деятельности. Если принять сальдо на начало января равное 10 тысячам рублей, сальдо на конец периода будет равно 123 тысячам рублей.

5. Делаем прогнозный баланс

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

Рисунок 5 — Прогнозный баланс, тыс. руб.

В графу «денежные средства» подставляем значение сальдо на конец периода предыдущего месяца. Так, денежные средства за февраль = 123 тысячам рублей.

Активы будут равны сумме: денежные средства + основные средства + дебиторская задолженность.

Кредиторская задолженность рассчитывается: кредиторская задолженность предыдущего периода + планируемые затраты на заработную плату и аренду предыдущего месяца — планируемы движения денежных средств по зарплате и аренде предыдущего месяца.

Так, кредиторская задолженность на 1 февраля составит 58 тысяч рублей

Капитал = капитал за предыдущий период + операционная прибыль предыдущего периода.

Капитал на 1 февраля составляет 65 тысяч рублей. 10+55.

Пассив — это сумма капитала и кредиторской задолженности

Обратите внимание, что дебиторская задолженность будет рассчитываться как дебиторская задолженность за предыдущую дату + отгрузка периода — поступление денежных средств.

Далее финансовую модель можно детализировать. Например, разбить по видам управленческие расходы, расписать зарплаты по сотрудникам, детализировать план продаж.

Финансовую модель можно корректировать, заменять плановые данные фактическими, отслеживать риски и контролировать финансовые результаты.

Узнайте больше о финансовом моделировании в Excel и прогнозировании финансового состояния бизнеса на семинаре в Учебном центре «Финконт».

Смотрите также:

• Профессии будущего: какие специалисты будут востребованы в строительстве, медицине и сельском хозяйстве
• Пути назад не будет: как цифровая трансформация меняет бизнес
• Как ставить цели обучения по Блуму

Подпишитесь, чтобы не пропустить интересные мероприятия и получите подарок на почту!

Вы сможете выбрать только актуальные для вас темы.

Финансовая модель: зачем нужна и как ее построить

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

Что такое финансовая модель

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

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

Например, в финмодели подробно расписывается воронка продаж. Это помогает детально увидеть, от чего зависит выручка: от конверсии на сайте или конверсии в покупку в офлайн-магазине.

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

Финмодель универсальна — ей пользуются предприниматели в разных отраслях: строители, логисты, проектные студии, магазины, производственники, разработчики сайтов.

Финмодель не нужно путать с бизнес-планом и бизнес-моделью. Часто в обиходе финмодель путают с бизнес-планом. На самом деле это разные вещи.

Бизнес-план — объемный документ, в котором описан будущий бизнес. Он содержит много разделов: например, общее описание проекта, маркетинг-план, производственный и финансовый планы, анализ рынков и рисков. Бизнес-план показывает, как будет делаться бизнес, бизнес-модель — что, кому, как продавать, и насколько это выгодно, а финмодель — какими силами и с каким финансовым результатом.

Зачем финмодель нужна бизнесу

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

Часто у предпринимателей можно встретить убеждения, что планировать невозможно. Никто не знает, что случится даже завтра. Построение финансовой модели дает возможность спрогнозировать будущее бизнеса.

Финмодель помогает ответить на вопросы:

1. Сколько компания заработает за год выручки и прибыли.
2. Какая у компании точка безубыточности. То есть сколько нужно продать товаров или оказать услуг, чтобы выйти в ноль.
3. Какие ключевые показатели кратно влияют на прибыль: конверсия сайта, трафик, средний чек или что-то еще.

Финмодель полезна в двух ситуациях.

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

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

Франчайзи рисковали кассовыми разрывами

Кто в компании должен заниматься финмоделью

Если компания молодая, у нее еще нет сложных процессов, а данные по выручке и затратам можно собрать вручную — это делает собственник компании, разбирается сам.

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

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

Структура финансовой модели

Финансовая модель бизнеса состоит из четырех блоков:

1. Выручка и воронка продаж.
2. Постоянные и переменные расходы.
3. Амортизация, налоги, кредиты, чтобы рассчитать чистую прибыль.
4. Оборотный капитал и движение денежных средств, баланс.

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

Делают финансовую модель в Google Таблицах или Excel. Надо уметь хотя бы базово работать с формулами, чтобы выстроить зависимости между показателями.

Разберем построение элементарной финансовой модели для будущего бизнеса, на примере компании, которая производит постельное белье и продает его через соцсети.

Планируем выручку и воронку продаж

Выручка — это сколько компания отгрузила товаров или оказала услуг.

Выручку считаем по каждому месяцу. В финмодели расписываем подробно всю воронку продаж.

Воронка продаж производства белья может состоять из:

1. Количества просмотров в соцсети.
2. Процентов роста просмотров каждый месяц.
3. Конверсии в заявку.
4. Количества заявок.
5. Конверсии в продажу.
6. Количества продаж.
7. Среднего чека.

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

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

Так мы планируем выручку по каждому месяцу: вбиваем нужные формулы в Excel и таблица сама считает план на год.

Планируем расходную часть

Ниже в таблице считаем расходы, которые компания несет в течение года. Их много, и они разные. В самом общем понимании расходы состоят из постоянных и переменных расходов.

Переменные расходы. Это те расходы, которые зависят от объема выручки. То есть если нет выручки, нет и переменных расходов. Это могут быть расходы на закупку товара, доставку, упаковку, бонусы продавцам — для каждого конкретного вида бизнес они будут свои.

В нашем производстве постельного белья переменные расходы:

• материалы;
• упаковка;
• доставка;
• зарплата швеям — в процентах от выручки.

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

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

Когда мы поняли, как бизнес продает, переходим к подсчету постоянных расходов — тех, что бизнес понесет в любом случае.

Постоянные расходы могут быть прямыми или косвенными.

Постоянные прямые расходы — те, что прямо связаны с направлением бизнеса.

Например, у производства два цеха: массовый пошив и пошив на заказ. У каждого свой цех, разные сотрудники, расходы.

В нашем примере в компании, которая сама же производит постельное белье и сама его продает, к постоянным прямым расходам отнесем:

• аренду цеха;
• аренду склада;
• зарплату производственных рабочих на складе.

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

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

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

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

Постоянные косвенные расходы. Это те расходы, которые бизнес несет независимо от того, есть у него выручка или нет.

В нашей компании по продаже белья к постоянным расходам можно отнести:

• зарплату бухгалтера, гендиректора и офисных сотрудников;
• аренду офиса;
• коммунальные платежи;
• налоги на офисных сотрудников;
• логистику;
• расчетно-кассовое обслуживание;
• плату за CRM.

Когда мы расписали все расходы по месяцам, ниже рассчитывается тот самый EBITDA — показатель операционной прибыли.

Этот показатель очень важен. Он показывает, насколько бизнес вообще способен генерировать прибыль. Если операционная прибыль отрицательная, это значит, что такой бизнес не жизнеспособен или требует кардинальных изменений в структуре.

Рассчитываем чистую прибыль

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

Амортизация. Рассчитывается из времени службы техники и показывает, как этот ресурс техники изнашивается.

В каждом бизнес своя техника — ее экономисты называют основными средствами. В нашем примере это швейные машины, оверлоки. Мы их купили за 200 000 ₽ и планируем использовать 5 лет.

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

В нашем производстве кредитов нет, но есть налог на УСН 6%.

Вот теперь наконец-то можно посчитать чистую прибыль, а также рентабельность по чистой прибыли.

Рентабельность по чистой прибыли показывает сколько копеек компания заработала на 1 ₽ выручки. Она помогает понять, сколько денег собственник может вывести себе в качестве дивидендов или вложить в развитие бизнеса.

Считаем, сколько у нас денег в кассе, и планируем движение денежных средств

Это сложная часть для новичков. До этого момента мы поняли, какие именно показатели влияют на прибыль и выручку: конверсия, себестоимость материалов, переменные и постоянные расходы — и прикинули, насколько вообще прибыльный бизнес.

Эти расчеты не дают полной картины того, как именно будут двигаться деньги внутри бизнеса от месяца к месяцу. Дело в том, что деньги поступают и тратятся в разное время, по разному графику, поэтому чистая прибыль почти никогда не равна деньгам в кассе.

Бывает, что в компании по всем отчетам прибыль есть, а денег в кассе нет. Или наоборот: денег полный сейф, а в отчетах убыток. Самая болезненная ситуация, когда по отчетам прибыль есть и вроде все идет неплохо, а в реальности в конце месяца платить зарплату нечем, — это называется кассовый разрыв.

Вот тут продолжение финмодели поможет разобраться, что именно создает эту разницу между деньгами в таблице и деньгами в реальности.

Для этого нужно рассчитать операционный оборотный капитал.

Сразу объясним каждый пункт из формулы.

Запасы — стоимость продукции, которая сейчас лежит у вас на складе или едет к вам от поставщика.

Дебиторская задолженность — сколько вам должны, то есть сколько вы оказали уже услуг или продали товаров, за которые вам еще не заплатили деньги.

Кредиторская задолженность — это сколько уже должны вы.

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

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

Например, может оказаться так, что вся чистая прибыль компании съедается запасами на складе и тем, что компания платит своим поставщикам авансом, а получает товары гораздо позже. Или наоборот, что услуги оказываются в одно время, а деньги клиент платит с большим опозданием. Эти моменты как раз и создают кассовый разрыв.

Наше производство белья платит ткацкой фабрике за заказ на несколько тонн тканей сегодня, но сами ткани приедут только через 30 дней. Финмодель может показать, что при тридцатидневной отсрочке компании не хватает денег и появляется кассовый разрыв, а если поменять срок на 28 дней, то денег хватает. Отсюда можно сделать вывод, что надо передоговариваться с фабрикой о сроках поставки.

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

Зачем так много всего считать и что это дает

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

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

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

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

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

Финмодель покажет, как именно это скажется на выручке. Условно: если Света стала работать плохо и ее плановый показатель по просмотрам в 10 000 человек в день снижается от месяца к месяцу, в финмодели вы увидите, что это может стоить компании несколько миллионов рублей.

Без финмодели может показаться, что просмотры — это не так уж важно, можно дать Свете еще один шанс. Но когда вы посчитаете, сколько именно убытков может приносить плохая работа каждого конкретного сотрудника, сразу появится понимание, когда кого-то из них стоит заменить и как распределить ресурсы.

Или, например, менеджер по продажам Антон вместо конверсии в продажу 7% стал выдавать 6% — компания потеряла условно 5 млн рублей за год. Всего 1% — но в целом влияет на большие суммы в компании. Без финмодели не увидеть этой четкой зависимости.

Чем еще помогает финмодель. Финансовая модель компании помогает понять, от какого вида деятельности можно отказаться, где повысить план для отдела продаж, маркетинга или отдела закупок, где передоговориться с поставщиками или сделать что-то еще, что повлияет именно на ваш конкретный вид бизнеса.

Например, закрыть магазин, который убыточен по валовой прибыли.

Изменить систему оплаты труда, перейти на процентную систему, если расходы на ФОТ носят постоянный характер, а бизнес не приносит выручки.

Решить, как именно влиять на конверсию. Например, конверсия в покупку из отложенных в корзину товаров маленькая. Тут можно внедрить дополнительную рассылку о том, что товар до сих пор в корзине, отправить СМС об изменении цены или что-то еще. Без финмодели будет сложно понять, что клиенты теряются именно на этом этапе продаж.

Финмодель стоит делать как минимум раз в год. Это поможет следить за тем, куда движется бизнес, что больше всего приносит денег, а что съедает.

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

Финмодель — инструмент планирования: посчитали, определились с планами, поняли, какие показатели нужно улучшить, отложили и пошли работать.

Как отличить качественную финансовую модель от некачественной

Главный показатель качественной финмодели — вы из нее можете сделать выводы, которые потом можно применить в жизни. Например, вы увидели, что снижение закупочной цены материалов сильно влияет на общую годовую прибыль. Это будет повод перестроить работу отдела закупок или поиска нового поставщика.

Главное

1. Финмодель нужна как будущему, так уже работающему бизнесу.
2. Составляют финмодель в Excel или Google Таблицах. Для этого потребуется поднять всю информацию о доходах и расходах бизнеса и все посчитать
3. Финмодель поможет спрогнозировать прибыль, поставить плановые показатели и понять, как развивать бизнес в ближайший год.

Разработка финансовой модели в Excel

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

Финансовая модель — это бизнес-план в цифрах

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

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

Структура финансовой модели

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

Классическая финансовая модель в Excel не должна содержать скрытых или заблокированных ячеек. Специалист, который будет анализировать составленную вами модель должен иметь возможность самостоятельно оценить используемые формулы и данные, заменить их и просмотреть влияющие и зависимые ячейки. Поэтому в составе формул могут присутствовать только ссылки на ячейки или на результаты расчетов.
Классическая финансовая модель должна давать возможность вносить изменения в исходные допущения и автоматически корректировать результаты после такого изменения.
К наиболее сложной части финансовой модели в Excel относится расчет финансовых показателей (NPV, IRR, PI, EBITDA и т.д.).
Также технически сложно создать автоматический модуль анализа чувствительности. Однако, все это возможно «сделать руками» с небольшим напряжением головного мозга. Примеры финансовых моделей можно взять тут

Исходные данные для финансовой модели

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

На схеме справа (кликабельна) данные для финансовой модели бизнес-плана представлены в структурированном виде, разбитые по группам «инвестиции — доходы — расходы». Схема дает представление, какие исходные данные для расчетов надо собрать.

ЗАДАЧА: собрать данные в одном месте, сгруппировать, систематизировать и обеспечить обзорность

Мы разработали анкету исходных данных финансовой модели для удобства их сбора и структурирования. Вы тоже можете ей пользоваться, скачать анкету финансовой модели бизнес-плана можно тут.

Исходные данные для финансовой модели бизнес-плана

Инструкция по работе с анкетой данных для модели

Если вы не занимаетесь финансовым моделированием каждый день — даже заполнение анкеты может вызывать трудности.

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

Инструкция «Как пользоваться анкетой данных для финансовой модели» находится по этой ссылке .

Как построить хорошую финансовую модель?

1. Будьте проще
2. Отделите входы (данные) от расчетов и выходов (результатов)
3. Не вставляйте константы в формулы
4. Пользуйтесь цветовой кодировкой ячеек
5. Не используйте длинные формулы
6. Встраивайте проверки на ошибки
7. Используйте анализ чувствительности и сценарный анализ
8. Создайте сводный лист с диаграммами и таблицами
9. Не допускайте циклических ссылок
10. Думайте о пользователях

1. Будьте проще

Очень неприятно, когда пользователь заявляет, что модель слишком сложная и громоздкая. Однако помните, что простота — понятие, которое должно соотноситься с их уровнем знаний.

Так, стандартная финансовая модель может быть названа сложной человеком, который мало знаком с Excel и финансовым моделированием. И та же самая модель при анализе специалистом по Excel может быть названа примитивной.

Пусть ваша модель будет максимально проста – но не проще!

2. Отделите входы (данные) от расчетов и выходов (результатов)

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

3. Не вставляйте константы в формулы

Это уже похоже на проблему борьбы за чистоту окружающей среды в финансовых моделях. Вечный вопрос – почему некоторые люди кидают мусор на землю? Не то чтобы входы, которые вы задали путем ввода константы в формулу, стали от этого мусором. А вот вы, когда сделали это, действительно отнеслись к ним, как к мусору, который будет похоронен в модели без надгробия или таблички с надписью! И никто и никогда их не найдет.

4. Пользуйтесь цветовой кодировкой ячеек

Используйте последовательную цветовую кодировку для обозначения различных типов ячеек и данных. При этом будьте проще. Не стоит злоупотреблять яркими цветами или вычурным оформлением.

5. Избегайте длинных формул

Лучше иметь 10 последовательных формул типа «=a*b» чем одну общую формулу, которую вы не сможете с легкостью проверить.

6. Встраивайте проверки на наличие ошибок

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

Ошибки в таблицах Excel встречаются часто. Даже очень системный и методичный подход к моделированию не позволяют от них избавиться.

Один из способов помочь уменьшить количество ошибок — включить в модели проверки на ошибки и целостность. Современные стандарты моделирования предусматривают создание контрольного листа, на котором собираются все проверки модели. Это позволяет разработчику модели убедиться, что модель или ее части работают правильно.

7. Используйте анализ чувствительности и сценарный анализ

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

8. Создайте сводный лист с диаграммами и таблицами

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

На этом листе вы можете разместить эстетичные графики, диаграммы и таблицы.

9. Не допускайте циклических ссылок

Правильнее назвать их «порочный круг» — вот во что они превращают финансовую модель.

10. Не будьте эгоистичными

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

Крупные консалтинговые компании, такие как EY, KPMG и PWC , следуют ряду наилучших практик в разработке финансовых моделей. Некоторые из ключевых аспектов, которые необходимо проверить, чтобы убедиться, что финансовая модель соответствует этим стандартам, включают в себя:

• Понимание целей моделирования: модель должна соответствовать целям и потребностям компании. Необходимо проверить, что модель точно отражает все аспекты бизнеса, которые компания хочет изучить, и что она не содержит ненужных деталей.
• Качество данных: модель должна основываться на точных и надежных данных. Необходимо проверить, что данные, используемые в модели, актуальны, проверены и соответствуют требованиям бизнеса.
• Гибкость и масштабируемость: модель должна быть гибкой и масштабируемой, чтобы соответствовать потребностям изменяющегося бизнеса. Необходимо проверить, что модель может легко изменяться и адаптироваться к новым ситуациям.
• Прозрачность и документация: модель должна быть прозрачной, чтобы ее можно было легко понять и проверить. Необходимо проверить, что модель хорошо задокументирована и что ее можно легко проверить и понять.
• Контроль ошибок: модель должна быть разработана в соответствии с процедурами контроля ошибок, чтобы гарантировать точность и надежность расчетов. Необходимо проверить, что модель прошла тест.
• Разумность допущений : модель должна основываться на разумных предположениях, которые легко объяснить и проверить. Необходимо проверить, что предположения, используемые в модели, логичны и имеют смысл для бизнеса.
• Применение лучших практик: необходимо проверить, что модель разработана с применением лучших практик, которые используют EY, KPMG и PWC в своей работе.

1. Не смешиваем исходные данные, расчеты и результаты. Размещаем их на разных листах.
2. Структура расчетов должна быть логичной и понятной.
3. Столбцы, даже находящиеся на разных листах, должны соответствовать друг другу .
4. Используйте однотипные формулы в одной строке.
5. Не используйте в формулах значения входов .
6. Не допускайте циклических ссылок в финансовой модели.
7. Не используйте скрытые строки и столбцы.
8. Ограничьте использование имен массивов
9. Не усложняйте модель. Чем проще — тем лучше.
10. Всегда думайте об удобстве того, кто будет использовать эту модель.

• Разделяйте входы, расчеты и результаты финансовой модели.
• Используйте разные рабочие листы и разделы для разных «тем».
• Используйте одинаковые периоды времени в столбцах во всей модели.
• Не используйте скрытые столбцы или строки.
• Используйте единообразное форматирование во всей модели.
• Используйте одни и те же формулы в аналогичных строках и столбцах.
• Соблюдайте логическую последовательность расчетов — предыдущие расчеты слева и выше, последующие расчеты — справа и ниже.
• Не используйте в формулах числа — только ссылки на ячейки модели.
• Ограничивайте использование массивов.
• Избегайте внешних ссылок.
• Не допускайте циклических ссылок.
• Упрощайте финансовую модель!

NB! Финансовые модели — это не об абсолютных числах; они о связях. Хорошая финансовая модель демонстрирует связи, допущения и условия, которые вместе создают потенциал прибыльности бизнес-идеи.

Если вы понимаете связи, драйверы доходов, источники затрат и критические факторы успеха — вы понимаете суть моделируемого бизнеса.

Скачать примеры финансовых моделей

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

• Модель от Номос-Банка можно скачать тут
• Модель от Газпромбанка можно скачать тут
• И модель из бизнес-плана, сданного в ВЭБ можно скачать тут

Цветовая кодировка финансовых моделей

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

Для этого используются универсальные стандарты цветовой кодировки финансовых моделей – формул, констант и ссылок.

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

Но в любом случае, таблицы Excel должны легко читаться и быть понятными.

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

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

NB! Даже если в вашей модели полно ошибок, а ее структура – непоследовательна, пусть у нее будет хотя бы правильная цветовая кодировка.

Стандарты цветовой кодировки

Цветовая кодировка позволит любому аналитику или пользователю быстро понять, что в модели можно изменять (константы) и чего нельзя трогать (формулы).

Конечно, все это можно понять, нажимая на ячейках F2, но это трудоемко и неудобно. Так что – используйте правильную цветовую кодировку.

Ссылки, константы и формулы.

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

• Синий (или голубой) шрифт: все константы (то есть числа, вводимые вручную); используемые при отображении исторических финансовых данных, а также в ячейках раздела модели «Допущения». Чтобы выделить ячейки, предназначенные для ввода исходных данных, в нашей компании принято делать их с желтой заливкой. Это позволяет выделить такие ячейки относительно других, но такое форматирование не является обязательным.
  
• Черный шрифт: Все формулы, в том числе – формулы, в составе которых присутствуют ссылки на другие листы (кроме формул, в которых участвуют прямые ссылки на другие листы. Например – если мы перемножаем два числа, которые находятся в ячейках на других листах. Для таких формул используется зеленый шрифт).
  
• Зеленый шрифт: Все прямые ссылки на ячейки в других листах и формулы, в состав которых входят прямые ссылки на другие листы.

А что насчет текста? С технической точки зрения он должен был бы быть синим, так как является «константой». Однако, это сильно затруднило бы чтение модели, поэтому оставляем текст черным.

За исключением текста, который вводится в ячейку в качестве данных – он, конечно, должен быть синим.
Примеры цветовой кодировки модели

Проверка финансовой модели

Как проверить финансовую модель?

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

Проверка механики финансовой модели

Если вы проееряете чужую финансовую модель (ну и свою — тоже), то в первую очередь имеет смысл проверить ее на предмет того,что она правильно сконструирована и работает.

• Используется ли в модели цветовая кодификация ячеек?
• Нет ли битых формул в модели (рабочих листов, диапазонов, макросов. )?
• Нет ли ссылок на внешние файлы?
• Отделены ли входы от вычислений и результатов?
• Однородная структура столбцов на всех рабочих листах?
• Заданы ли единицы измерения каждого элемента модели? Нет «хвостов» из прошлого проекта?
• Используются ли однородные формулы в строках?
• Модель не содержит слишком длинных и сложных формул?
• Есть ли проверка на ошибки?
• Нет ли в модели циклических ссылок?
• Все макросы, включенные в модель, работают правильно?
• Модель позволяет проводить анализ чувствительности к таким параметрам, как отсрочки, рост затрат,…
• Модель включает расчет финансовых коэффициентов и стоимости компании?
• Модель включает в себя итоговый лист, содержащий основные входы и выходы и графики?
• Нет ли скрытых или защищенных паролем листов или макросов?

Проверка результатов расчетов

Если с механической финансовой модели все в порядке, проверяем результаты расчетов с точки зрения лица, которое будет принимать решения на ее основе.

1. Прибыльность: необходимо оценить, насколько прибыльна модель. Это можно сделать, оценив доходы и расходы на основе прогнозируемых продаж, амортизации, налогов, процентных ставок и других факторов.
2. Потребность в финансировании: следует оценить, сколько средств потребуется для финансирования проекта. Необходимо проанализировать капитальные затраты, операционные расходы, потребность в оборотном капитале и другие расходы.
3. Ликвидность: необходимо убедиться, что у компании будет достаточно денежных средств для покрытия ее текущих обязательств. Это можно сделать, оценив соотношение текущих активов и текущих обязательств.
4. Рентабельность инвестиций: следует оценить, насколько эффективно использование средств в данном проекте по сравнению с альтернативными вариантами инвестирования. Это можно сделать, рассчитав соответствующие коэффициенты, такие как NPV (чистая приведенная стоимость), IRR (внутренняя норма доходности) или ROI (рентабельность инвестиций).
5. Чувствительность: необходимо оценить, насколько изменение ключевых параметров (например, объем продаж, цены, стоимость производства) влияет на рентабельность проекта. Это может помочь оценить риски и подготовиться к возможным изменениям в бизнес-среде.
6. Перспективы роста: необходимо проанализировать, какие перспективы роста у компании в долгосрочной перспективе. Это может помочь определить, насколько инвестиция в данном проекте выгодна в долгосрочной перспективе.
7. Срок окупаемости: следует оценить, через какой период времени инвестиции начнут окупаться. Это может помочь принять решение о том, насколько длительным должен быть период инвестирования.

Зачем нужно тестировать финансовые модели?

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

Тестирование модели и проверка модели

Многие эксперты часто путают тестирование модели с проверкой модели, отождествляя эти выражения. Давайте разберемся, в чем же заключается различие между этими двумя понятиями.

Тестирование модели означает проверку на точность и корректность каждой математической операции, осуществляемой в рамках модели, в то время как проверка модели – это выявление соответствия основных элементов модели будущей деятельности компании и потребностям бизнеса. То есть в первом случае анализ чисто математический, во втором же – смысловой. Рассмотрим следующий пример: клиент попросил создать десятилетнюю финансовую DCF-модель. На что при работе обратит проверяющий (аудитор), а на что – тестировщик? Первый будет смотреть на сколько лет была составлена финансовая модель, а также, соответствует ли она базовым предпосылкам и учитывает ли особенности бизнеса (например, учтена ли налоговая система страны, если бизнес будет открыт за рубежом; учтены ли отраслевые особенности налогообложения). Тестировщик же, помимо пунктов, перечисленных выше, включит в свою работу проверку каждой математической операции, производящейся в модели, а также проведет анализ будущих денежных потоков компании с поправкой на инфляцию.

Кто должен тестировать модель?

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

Когда нужно тестировать финансовую модель?

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

Наиболее распространенные виды тестирования

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

1. Входное тестирование

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

2. Числовое тестирование

Числовое тестирование включает в себя проверку каждой математической операции, выполняемой моделью. В самой финансовой теории уже есть некоторые внутренние сдержки и противовесы. Например, если модель включает в себя балансовый отчет, то совокупные активы компании должны быть равны сумме собственного и акционерного капитала.

3. Сравнение моделей

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

4. Техническое тестирование

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

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

Финансовая модель бизнеса в Excel: пошаговая инструкция построения, примеры финмодели в Эксель

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

Общее представление

• доходы и расходы;
• прибыль;
• денежные потоки;
• активы и обязательства.

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

Построение финмодели в Excel от Microsoft или в Google docs предусматривает внесение части информации вручную, тогда как зависимые данные определяются с помощью базовых формул. Благодаря этому любая корректировка позволяет моментально пересчитать итоговые значения, оценить риски и проанализировать перспективы получения прибыли.

За счет проделанной работы формируются три типа отчетности:

• балансовые показатели предприятия;
• аналитика результатов (ОФР);
• отчет по движению денежных средств (ОДДС).

Грамотное применение полученных результатов упрощает управленческий процесс — так же, как и использование программных решений от компании «Клеверенс», исключающих вероятность технических ошибок при учете активов организации.

Финансовое моделирование проекта в Excel: что нужно знать

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

1. Создайте предварительный вариант с минимальным количеством данных, и задайте локальные критерии расчета для зависимых переменных. В качестве примера можно привести выручку относительно цены продукции и объемов реализации, фонд оплаты труда при сдельной работе, социальные отчисления ФОТ и т.д.
2. Обобщите параметры в рамках общей системы, и проведите предварительную проверку функциональности готовой структуры. Ключевой критерий — схождение баланса, отклонения в котором свидетельствуют о допущенных ошибках. Небольшое количество формул позволяет без особых усилий выявить проблемные зоны, требующие корректировки.
3. Детализируйте финансовый расчет бизнес-плана в Excel, дополнив его новыми базовыми показателями. Раскройте специфику формирования себестоимости продукции, уточните периоды взаиморасчетов с поставщиками исходя из имеющихся коммерческих условий, привяжите к производственным объемам корреляцию, учитывающую величину переходящего товарного остатка. При добавлении параметров также следует проверять работоспособность системы, чтобы исключить «перекос» формульных значений.

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