Excel для финансиста

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

Основы построение финансовой модели в Excel

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

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

Инвестиционный план

Основные элементы:

  • строительство и/или покупка зданий;
  • покупка оборудования;
  • расходы будущих периодов;
  • инвестиции в ЧОК (чистый оборотный капитал).

Исходные данные для расчета затрат на покупку или строительство зданий:

Расчеты:

Затраты на приобретение оборудования и элементы расходов будущих периодов в инвестиционном плане составляются аналогично. Особенности затрат будущих периодов:

  • оприходуются на баланс в составе текущих активов;
  • не облагаются налогом на имущество (в отличие от оборудования);
  • амортизируются быстрее, в течение 1-2 лет.



Прогнозирование доходов

Чтобы построить план продаж, нужно определить объем в натуральном выражении (для каждого вида продукции) и цену реализации (каждого вида продукции). Выручка определяется по каждому виду товара (работ и услуг) как произведение объема и цены.

В Excel составляются таблицы для каждого периода планирования и для каждого вида продукции с планируемым объемом выпуска (в натуральных единицах).

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

Формулы:

  • Цена без НДС = цена с НДС / (1 + налоговая ставка).
  • Величина НДС = (цена с НДС * налоговая ставка) / (1 + налоговая ставка).

Расчетная таблица может выглядеть следующим образом:

  1. Формула для расчета цен без НДС: =C7/(1+$B$8).
  2. Расчет налога на добавленную стоимость: =C7-C9.
  3. Выручка с налогами: =C4*C7.

План текущих расходов

Элементы затрат:

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

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

Затраты на сырье и материалы увеличиваются прямо пропорционально объему выпуска. Если, к примеру, на пошив одной сорочки требуется полтора метра ткани, то на две единицы продукции – 3 метра и т.д. Расход считается по формуле:

Количество материалов = удельный вес * объем производства.

Пример таблицы учета текущих затрат на сырье и материалы:

Формула для расчета налога на добавленную стоимость – в строке формул.

Формула вычисления цены без НДС: =C5/(1+$B$6).

Расчет затрат с НДС: =C4*C5.

Налог на ДС: =C4*C6.

Затраты без НДС: =C4*C7.

Прочие расходы:

  • аренда,
  • реклама,
  • оплата связи;
  • ремонт и т.д.

При составлении финансовой модели предприятия в Excel учитывается каждая статья расходов.

Финансовая модель предприятия в Excel

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

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

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

План движения денежных средств:

По теме: Финансовая модель в Excel при покупке бизнеса.

Предполагается, что предприятие не будет привлекать заемные средства. Поэтому раздел «Финансовая деятельность» отсутствует.

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

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

Рисунок 1

Начальное сальдо (рис-1)

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

  • количество денежных средств, которые имеются в наличии;
  • ориентировочная стоимость имеющихся активов;
  • размер дебиторской задолженности;
  • размер кредитной задолженности.

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

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

Рисунок 2

Макро (Рис-2)

Второй лист «Макро» включает в себя важнейшие макроэкономические данные по компании. То есть:

  • дата начала проекта;
  • длительность проекта;
  • выбор валюты ведения проекта;
  • даты запуска различных проектных фаз;
  • флаги проектных фаз.

Макроэкономические показатели по фазам делятся на три этапа:

  1. предынвестиционный — период до первого вложения денежных средств в проект;
  2. инвестиционный — непосредственно отрезок времени, в течение которого работают инвестиционные средства;
  3. операционный — фаза выпуска проекта после завершения инвестирования.

Важными элементами в раздели макро являются пункты дисконтирования и налогов (Рис-3). Данные в данных местах необходимо вносить с учетом рисков и в соответствии с законодательством РФ.

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

Рисунок 3

Продажи

Рисунок 4

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

В первой части листа мы видим раздел для внесения данных о товарах и их фиксированной стоимости. Если нужно внести две цены на одну и ту же единицу, то указываются данные через «/». Второй раздел включает в себя данные о приблизительном объеме продаж для каждого указанного товара. Рассчитывать можно, как в штуках, так и в количестве раз оказания услуги.

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

Переменные расходы

Рисунок 5

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

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

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

Постоянные расходы

Рисунок 6

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

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

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

  1. закуп сырья и материалов;
  2. закуп упаковочных изделий;
  3. производство или заказ этикеток;
  4. закуп сопутствующих материалов.

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

ФОТ

Рисунок 6

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

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

Инвестиции

Рисунок 7

Раздел «Инвестиций» необходимо заполнять в случае, если таковые планируются. Различия имеются относительно инвестициям по объектам или по товарам. В первом случае в обязательном порядке следует вписывать всю информацию относительно типа имущества, его вида или времени эксплуатации.

Вне зависимости от вида инвестирования, всегда указываются следующие данные:

  • размер предполагаемых инвестиций;
  • стоимость объекта или товара с учетом НДС;
  • учет амортизации;
  • остаточная стоимость.

Финансирование

Рисунок 8

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

Во второй части листа вписываются собственные финансовые вложения, в том числе:

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

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

Отчет

Рисунок 9

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

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

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

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

Также учитывается размер неоплаченные и предполагаемых налогов.

Финансовый анализ

Рисунок 10

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

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

Финансовый анализ является главным показателем эффективного развития предприятия.

Инвестиционный анализ

Рисунок 11

Лист «Инвестиционного анализа» содержит информацию о чистой прибыли, инвестиционных вложениях, NPV и IRR.

Точка Безубыточности

Рисунок 12

Раздел «Точки безубыточности» автоматически определяет количества продаж, при которых компания никогда не будет нести убытки.

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

Анализ чувствительности

Рисунок 13

Лист «Анализ чувствительности» включает в себя все возможные финансовые риски, размер необходимого объема инвестирования, а также все объемы продаж и расходов.

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

Для этих средств можно использовать такое приемы, как привлечение новых инвесторов или резкое сокращение постоянных расходов на производство.

Сценарный анализ

Рисунок 14

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

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

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

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

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

Более подробно ознакомится с данной финансовой моделью и с другими финансовыми моделями можете пройдя по или написав мне на почту Этот адрес электронной почты защищён от спам-ботов. У вас должен быть включен JavaScript для просмотра. .

Андрей Дата: 27.09.2017 Рубрика: Бизнес-план — пошаговая инструкция

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

Короткое видео о том, что из себя представляет моя Финансовая модель в Excel

Последнее обновление модели 12.07.2020г.

Не ищите аналогов, в свободном доступе их нет!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

13. Сведем прогнозный баланс за расчетный период (за 3-и года).

14. Построим график реализации бизнес-проекта с разбивкой по основным этапам.

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

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

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

Вот перечень исходных данных, которые вам понадобятся для планирования бизнеса в предлагаемой мной модели (период планирования – 3 года):

— макропоказатели: прогноз инфляции на расчетный период или фактическая инфляция прошлых лет и средняя ставка по банковским депозитам (будем максимально упрощать расчет дисконтирования);

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

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

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

— перечень сотрудников и их предполагаемые оклады или тарифы;

— список маркетинговых мероприятий с разбивкой затрат по месяцам;

— перечень операционных (постоянных и переменных) затрат с ежемесячной разбивкой;

— цена конкурентов (максимальная и минимальная) и цена, которую готов платить потребитель (максимальная и минимальная) по каждому продукту (услуге) или группе товаров;

— прогноз продаж по каждому продукту (услуге) или группе товаров с разбивкой по месяцам;

— основные этапы реализации проекта с указанием сроков начала и окончания по каждому этапу.

Как видите, исходных данных нужно не так уж и много, при этом проект все равно будет проработан глубоко и грамотно.

Как работает моя финансовая модель для планирования бизнеса. Все очень просто. Заполняете только лист «Исходные данные», выбирая с помощью стандартных фильтров Excel необходимый вам блок. Данные нужно вписывать только в ячейки, которые имеют желтую заливку. В ячейках с зеленой заливкой выбираются данные из предложенных системой списков. Внимание, выпадающие списки менять нельзя, т.к. часть списков участвует в формулах модели. Остальные ячейки трогать без особой надобности не нужно, т.к. они имеют формулы или несут исключительно информативный характер. Для удобства печати, все основные таблицы вынесены на отдельные листы, в которые абсолютно все данные подтягиваются автоматически. Для тех, кто не уверенно работает в Excel, ячейки с формулами я закрыл защитой листа, которая не имеет пароля и вы, при необходимости, можете легко ее снять. Как это сделать, я детально объясню вам в одном из следующих постов.

В каком порядке заполнять финансовую модель

Последнее обновление модели 12.07.2020г.

При возникновении вопросов пишите на E-mail: andrei@blogbusiness.com.ua

Модель с гибким горизонтом планирования от 3-х до 10-ти лет представлена

Все анонсы новых постов и инструкций после публикации я выставляю на странице блога в Facebook.

Хотите первым узнать о новой публикации на моем блоге, подписывайтесь на страницу блога в Facebook!

Если материал поста был для Вас полезен, поделитесь ссылкой на него в своей соцсети:

При использовании материалов сайта наличие активной ссылки на www.blogbusiness.com.ua обязательно

Материалы партнеров:

… другие посты автора Андрей

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *