Оглавление
Инструкция создания финансовой модели в Excel
Инструмент финансового моделирования бизнес-проекта создан как обычный Excel файл без макросов и скриптов. Его можно копировать, переносить, передавать, использовать для работы несколькими сотрудниками, но при этом необходимо соблюдать правила коллективной работы при использовании Excel.
Файл выполнен в виде законченного продукта, не требующего доработки. Это шаблон финансового моделирования, который при внесении в него исходных данных автоматически рассчитывает необходимые финансовые показатели и генерирует необходимые для бизнес-планирования отчеты.
Шаблон можно использовать многократно для различных профильных проектов.
Для создания финансовой модели необходимо в Excel шаблон внести:
Общие исходные данные проекта;
Данные о продажах.
Рекомендуем перед началом заполнения шаблона финансовой модели собрать исходные данные в отдельном файле.
Данные о продажах для финансовой модели
Создайте краткое описание вашего товара или услуги, заполнив следующие данные для финансовой модели:
Наименование продукта или услуги;
Цена по которой продажи продукта или услуги;;
Количество продаваемого товара и дата начала продаж.
Для удобства просмотра разверните видео на полный экран.
Механизм управления планом продаж позволяет:
Быстро спланировать ежемесячные продажи, рассчитать равномерное изменение цены и количества реализуемого товара или услуг во времени с учётом сезонности и ограничений периода продаж;
Построить нелинейный план продаж в денежном выражении. Изменяя ежемесячный показатель в процентах, вы формируете свое видение на реализацию товаров и услуг;
(НОВОЕ) Свободное планирование планом продаж в количественном и денежном выражении исходя из начальной стоимости продукта или услуги;;
Для использования инструмента свободного планирования:
Перейдите в раздел «Продукты», установите название реализуемого продукта, цену за единицу товара или услуги, количество реализуемых в месяц и дату начала продаж;
Выберете столбец «План продаж». Функция подключает дополнительный инструмент расчета в данном разделе.
Перейдите в подраздел «План продаж». Вы увидите две колонки таблиц:
левая — управляем продажами путем установки объема продаж в процентах,
правая — управление продажами путем произвольного определения количества реализуемого товара или услуг в месяц.
ВАЖНО! Для использования нужного способа планирования необходимо произвести переключение между таблицами инструментом select.
Данные о затратах для финансовой модели
Затраты разделены на несколько групп, каждая из которых предполагает наличие минимального необходимого объема информации, позволяющий учесть 99% затрат вашего проекта. Руководствуйтесь разумной достаточностью чтобы создание финансовой модели в Excel не стало обременительным.
Группы затрат финансовой модели
Для создания финансовой модели в Excel необходимо ввести в шаблон:
Самый очевидный вариант использования раздела – производство. Определяем затраты, связанные с созданием единицы продукта или услуги. Если выделить затраты за единицу товара невозможно, то планируем закупки материалов с заданной периодичностью. Для проектов, связанных с продажей услуг, или проектный бизнес не всегда можно выделить себестоимость затрат. В этих случаях раздел может не заполнятся.
Для удобства просмотра разверните видео на полный экран.
Недвижимость, оборудование, автотранспорт, средства производства без которых ваш продукт или услугу произвести невозможно. Планируем затраты, обеспечивающие бизнес средствами производства и(или) оказания услуг. Покупаем здание, станки, дорогостоящую оргтехнику, лицензии, проектную документацию. Всё, что составляет материальную ценность проекта размещаем в данном разделе.
(см. видео ниже)
Купленный актив нужно обслуживать, ремонтировать, планировать его модернизацию. Эти данные заполняем в данном разделе. Информация для планирования затрат в раздел «инвестиции» попадают автоматически из раздела активы, планирование не займет много времени. Подробно об этой группе рассказано в видео инструкции по созданную финансовой модели.
(см. видео ниже)
Аренда офиса или оборудования. В финансовом плане аренда позволяет экономить существенные денежные средства. Старайтесь оптимизировать затраты чтобы стимулировать продажи, и не тратить деньги там, где можно этого не делать в данный период времени.
(см. видео ниже)
Затраты на обеспечение бизнеса – важная часть финансового планирования. Коммунальные платежи, ИТ поддержка, транспортные и рекламные расходы, вода в офис, кофе и чай. Эти и многие другие затраты обеспечивают ежедневные будни бизнеса. Обратите внимание: сюда не стоит включать затраты, являющиеся вашим коммерческим продуктом.
Например «реклама». Если ваш бизнес — реклама, то реклама – ваш продукт. Затраты на рекламу переходят в «Себестоимость».
Для удобства просмотра разверните видео на полный экран.
Должности, затраты на оплату труда сотрудников, распределение затрат во времени, схемы и фонд оплаты труда.
(см. видео ниже)
Инструмент планирования ФОТ имеет простой и удобный интерфейс заполнения исходных данных. Обязательные поля для заполнения:
Строка для указания должности. Если данная строка не будет выбрана, то расчет указанных цифровых значений исключится из расчёта.
Укажите размер оклада на старте проекта, размер индексации и дату начисления.
Если работник имеет срочный контракт, используйте поле «Период найма» для указания длительности в месяцах.
Если предполагается вознаграждение, в зависимости от результатов работы проекта, воспользуйтесь колонкой «Cхема начисления оклада» и установите один из вариантов:
Расчет результативности инвестиций в EXCEL
Как быть уверенным, что инвестиции приближают нас к поставленным задачам? В инвестициях практически всегда вместе с любой задачей параллельно следует необходимость «не потерять». Не потерять в мире инвестиций – это значит получать доходность выше инфляции. Переформулировав – портфель должен иметь реальную доходность выше нуля.
При учете результатов инвестиций почти всегда необходимо быть уверенным, что на длинных сроках доходность инвестиционного портфеля выше инфляции. Второй важный элемент — это сравнение доходности с «безрисковыми» инструментами. Инвестор, вкладывая деньги в ценные бумаги, берет на себя дополнительные риски. Подразумевается, что вместе с дополнительными рисками он получает возможность более высокой доходности. Если доходность инвестиций (мы всегда говорим о длинных сроках) ниже, скажем, средней ставки депозита, то зачем брать на себя дополнительные риски?
Есть и другие важные параметры, которые следует учитывать, но все они так или иначе сводятся к необходимости считать доходность. Доходность может быть разной – среднегодовой или накопленной, но считать и понимать эти цифры очень важно для любого инвестора. Без них непонятно, приближают ли нас инвестиции к целям или наоборот – удаляют от них.
Как считать доходность?
Почему большинство инвесторов часто имеют неправильное представление о том, какова настоящая результативность их инвестиций.
Сложность заключается в том, что большинство подходов к расчету доходности подразумевают простую формулу:
А – полученный доход
В – стартовые инвестиции
Представим себе жизненную ситуацию, когда человек в январе инвестировал 10 000 р, а в декабре – 90 000 р. К концу года на инвестиционном счете оказалось 110 000 р (ценные бумаги выросли в цене). Какова доходность инвестиций? Что на что делить? Если мы возьмем доход в 10 000 р и разделим на сумму всех инвестиций – 100 000 р, то получим очень сложно интерпретируемый результат – 10%. Ведь большую часть срока на счете находилось всего 10 000 р, а остаток добавлен только за месяц до конца года …
Или еще более интересный пример. В январе инвестор положил на брокерский счет 100 000 р, а в декабре забрал с него 90 000 р. К концу года на брокерском счете фигурировала сумма 15 000 р. Если просто сложить пополнения и изъятия получится что суммарная инвестиция равна 100 000 – 90 000 = 10 000 р. Разделив доход на суммарные инвестиции, получим слишком оптимистичные 50%. Очевидно, что так делать нельзя …
IRR или Внутренняя норма доходности (ВНД)
Одним из самых простых и распространенных способов измерить результативность инвестиций является расчет IRR (Internal Rate of Return, Внутренняя норма доходности). IRR – это не совсем доходность. Формально IRR или Внутренняя норма доходности (ВНД) – это процентная ставка, при которой приведённая стоимость денежных поступлений (списаний) равна размеру исходных инвестиций. IRR очень распространен в бизнесе и финансах. При помощи этой величины считается, например, рентабельность проектов в бизнесе. Аналогично считается доходности к погашению для облигаций. IRR можно считать это своего рода стандартом при измерении результативности.
Еще одно важное преимущество – IRR легко считается в EXCEL и других электронных таблицах.
Если IRR меньше ставки по депозитам в Сбербанке, то надо задуматься, все ли нормально с инвестиционной стратегией.
Шаблон для расчета IRR инвестиций в EXCEL
Для быстрого расчета результативности инвестиций предлагаем простой шаблон в EXCEL.
Шаблон считает IRR для каждого из периодов инвестиций, и за последние 6 периодов (колонка «IRR за 6 периодов»). Периоды могут быть произвольными: один месяц, один год. Более того, в калькуляторе используется функция XIRR (ЧИСТВНДОХ), которая умеет считать IRR даже для неравных между собой периодов. Это значит, что в колонке «Дата» можно указывать любую дату, а не только начало месяца или, например, конец года. Удобнее всего вносить новые данные каждый раз, когда пополняется портфель или когда происходит изъятие средств. Для интереса можно вносить новые данные чаще, даже когда нет пополнений портфеля. Например можно указывать даты, когда в размере портфеля происходят какие-то значимые изменения или просто с некоторой заданной регулярностью.
Кроме IRR инвестиционного портфеля в шаблоне можно посмотреть общий прирост портфеля (на сколько размер портфеля отличается от объема инвестированных средств).
Учет результатов инвестиций для сложных портфелей
Важное свойство калькулятора – это возможность измерения результативности инвестиций для широко диверсифицированных портфелей. Часто встречаются ситуации, когда у инвестора несколько брокерских счетов (российский и зарубежный), часть денег размещено в ПИФах через Управляющую компании. Кроме всего, может быть открыт ОМС (Обезличенный металлические счета – используются для покупки драгоценных металлов), куплена недвижимость и тому подобное. В таком случае рассчитать результат инвестиций для итогового портфеля бывает довольно проблематично… Предлагаемый калькулятор поможет справиться с этой задачей. Достаточно регулярно (например, один раз в год) считать суммарный размер всех активов в портфеле и вносить в таблицу пополнения и изъятия.
Расчет доходности к погашению для облигаций
Хотя это и не основная функция калькулятора, но его довольно просто можно использовать для расчета доходности к погашению для облигаций. Доходность к погашению для облигаций определяется именно как IRR всего денежного потока.
Для вычисления доходности к погашению необходимо внести сумму покупки облигации и планируемые поступления в виде дивидендов.
В примере показан прогноз доходности к погашению для облигации с купоном 40 руб (два раза в год) и текущей стоимостью 98% (980 р) и погашением в 2024 году. Предполагается, что облигация держится до погашения. В данном случае имеет релевантность только последнее значение IRR (в момент погашения), так как изменение цены облигации прогнозировать очень сложно. IRR за 6 периодов тоже большого смысла для облигаций не имеет.
Ограничения калькулятора
Калькулятор будет показывать, в том числе, нереализованный доход. Например, если ценная бумага выросла в цене, но еще не продана, то такой доход инвестора называется нереализованным. Поэтому предлагаемый шаблон не может быть использован для расчета налогов (НДФЛ). Нереализованный доход не считается налоговой базой.
Другие финансовые калькуляторы для EXCEL можно найти разделе Калькуляторы.
Калькулятор результативности инвестиций в EXCEL
Файл: investment_tracker.xlsx
Размер: 48684 байт
Для скачивания файлов необходимо зарегистрироваться или авторизоваться
Срок окупаемости инвестиций (PP, DPP, BO DPP). Формула расчета в Excel
Рассмотрим такой инвестиционный показатель как срок окупаемости инвестиций, его модификации, примеры и формулы расчета.
Срок окупаемости инвестиций (англ. PP, payback period) – это минимальный период времени возврата вложенных средств в инвестиционный проект, бизнес или любую другую инвестицию. Срок окупаемости является ключевым показателем оценки инвестиционной привлекательности бизнес плана, проекта и любого другого объекта инвестирования. Рассмотрим различные показатели срока окупаемости используемые на практике:
- Срок окупаемости инвестиций (PP).
- Дисконтированный срок окупаемости инвестиций (DPP).
- Срок окупаемости инвестиций с учетом ликвидационной стоимости (BO PP).
Инфографика: Срок окупаемости инвестиций (PP, DPP, BO DPP)
Данный показатель позволяет сравнивать между собой различные проекты по степени их эффективности возврата капитала.
#1 Срок окупаемости инвестиций (PP). Формула
IC (Invest Capital) – первоначальные инвестиционные затраты в проекте;
CFi (Cash Flow) – денежный поток от проекта в i-й период времени, который представляет собой сумму чистой прибыли и амортизации.
Для расчета денежного потока необходимо воспользоваться следующими формулами:
где:
А (Amortization) – амортизация, вид денежного потока, который не является затратами;
NP (Net Profit) – чистая прибыль инвестиционного проекта.
 | ★ Программа InvestRatio – расчет всех инвестиционных коэффициентов в Excel за 5 минут (расчет коэффициентов Шарпа, Сортино, Трейнора, Калмара, Модильянки бета, VaR) + прогнозирование движения курса |
Cрок окупаемости инвестиций (PP). Пример расчета в Excel
Рассчитаем срок окупаемости инвестиций в проект с помощью программы Excel. Для этого необходимо определить первоначальные затраты, которые в нашем примере составили 100000 руб., далее необходимо спрогнозировать будущие денежные поступления (CF) и определить с какого периода сумма денежного потока превысит первоначальные инвестиционные затраты. На рисунке ниже показан расчет срока окупаемости проекта. Формула расчета денежного потока нарастающим итогом следующая:
Денежный поток нарастающим итогом (CF) =C6+D5
Пример расчета срока окупаемости инвестиций в Excel
На пятом месяце сумма денежных поступлений окупит первоначальные затраты, поэтому срок окупаемости составит 5 месяцев.
Основные недостатки использования данного показателя в оценке инвестиций заключаются:
- Отсутствие дисконтирования денежных потоков бизнес проекта.
- Не рассматриваются денежные поступления за пределами срока окупаемости.
#2 Дисконтированный срок окупаемости инвестиций (DPP). Формула расчета
Дисконтированный срок окупаемости (англ. DPP, Discounted Payback Period) – период возврата денежных средств с учетом временной стоимости денег (ставки дисконта). Главное отличие от простой формулы срока окупаемости – это дисконтирования денежных потоков и приведение будущих денежных поступлений к текущему времени.
DPP (Discounted Payback Period) – дисконтированный срок окупаемости инвестиций;
IC (Invest Capital) – первоначальные инвестиционные затраты в проекте;
CF (Cash Flow) – денежный поток, создаваемый инвестицией;
r – ставка дисконтирования;
n – срок реализации проекта.
Расчета дисконтированного срока окупаемости инвестиций в Excel
Рассмотрим пример оценки дисконтированного срока окупаемости инвестиций для бизнес-плана. Первоначальные инвестиции составили 100000 руб., денежный поток изменялся ежемесячно и отражен в столбце «С». Ставка дисконтирования была взята равной 10%. Для расчета дисконтированного денежного потока воспользуемся следующей формулой:
Дисконтированный денежный поток =C7/(1+$C$3)^A7
Денежные поступления нарастающим итогом =E7+D8
Пример расчета дисконтированного срока окупаемости инвестиции в Excel
Проект окупится на 5 месяц, в котором денежные поступления составят 100860 руб.
Мастер-класс: “Как рассчитать срок окупаемости для бизнес плана: инструкция”
#3 Срок окупаемости инвестиций с учетом ликвидационной стоимости
Срок окупаемости с учетом ликвидационной стоимости (англ. Bail-Out Payback Period) – представляет собой период возврата денежных средств с учетом остаточной стоимости активов, созданных в инвестиционном проекте. При осуществлении инвестиционного проекта могут создаваться активы, которые могут быть проданы (ликвидированы) в результате этого срок окупаемости проекта существенно сокращается.
где:
IC (Invest Capital) – первоначальные инвестиционные затраты в проекте;
RV (Residual Value)– ликвидационная стоимость активов проекта;
CFi (Cash Flow) – денежный поток от проекта в i-й период времени, который представляет собой сумму чистой прибыли и амортизации.
Ликвидационная стоимость может, как увеличиться в результате создания новых активов, так и уменьшаться за счет износа.
Расчет срока окупаемости инвестиции с учетом ликвидационной стоимости в Excel
На рисунке ниже показан расчет периода окупаемости проекта с учетом ликвидационной стоимости. Формула в Excel достаточно простая и имеет вид:
Денежные поступления с ликвидационной стоимостью =C6+E5+D6
Пример оценки срока окупаемости с учетом ликвидационной стоимости в Excel
В итоге, срок окупаемости с учетом ликвидационной стоимости составит
4 лет. Данный способ оценки целесообразно применять при высокой ликвидности создаваемых активов. Как можно заметить, в данном варианте расчета срока окупаемости тоже может быть использована ставка дисконтирования.
Кроме срока окупаемости инвестиций существуют другие показатели оценки эффективности, позволяющие более точно провести анализ проекта. Более подробно о них вы можете узнать в статье: “6 методов оценки эффективности инвестиций в Excel. Пример расчета NPV, PP, DPP, IRR, ARR, PI“.
Резюме
Срок окупаемости является важнейшим показателем инвестиционного анализа проектов и бизнеса. Он позволяет определить целесообразность вложения в тот или иной проект. Использование дисконтирования денежных потоков и ликвидационной стоимости активов позволяет инвестору более точно оценить период возврата капитала. Помимо данного коэффициента необходим анализ через другие показатели эффективности: чистой приведенной стоимости (NPV), внутренней нормы доходности (IRR) индекса прибыльности (PI). Кроме точечной оценки необходим анализ динамики денежных потоков и их равномерность.
Автор: к.э.н. Жданов Иван Юрьевич
http://itillect.ru/financemodel/manual/
http://rostsber.ru/publish/stocks/tracking_investments.html
http://finzz.ru/srok-okupaemosti-investicij-raschet-v-excel.html
