Расчет окупаемости инвестиционного проекта Мощная Excel-таблица

Posted on by admin

Новая версия Excel-таблицы
«Расчет инвестиционных проектов»

Расчет всех показателей
Анализ чувствительности
Сравнение инвестиционных проектов

версия 7.1 от 22 мая 2019

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

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

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

  1. Cтавку дисконтирования методом WACC
  2. Все показатели инвестиционного проекта: NPV, IRR, ROI, PI, Срок окупаемости инвестиций, Дисконтированный срок окупаемости
  3. Новое: Min и Max границы объема инвестиций. Если планируемый объем инвестиций выходит за эти границы — скорее всего допущена ошибка при расчете потребности в инвестициях.
  4. Чувствительность к отклонениям в статьях доходов и расходов
  5. Запас прочности по статьям доходов и расходов. Т.е. на сколько процентов должна увеличиться или уменьшиться статья чтобы проект перестал приносить доход (NPV = 0)

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

Сравнивает до 7 инвестиционных проектов

  1. Качественно (визуально)
  2. Количественно

Строит графики

  1. Доли инвестиций
  2. Стоимость и взвешенная стоимость инвестиций
  3. Доход, расход, чистый доход и чистый дисконтированный доход по шагам инвестиционного проекта
  4. Чистый доход и чистый дисконтированный доход нарастающим итогом. Переход этих двух графиков через 0 соответствует сроку окупаемости и дисконтированному сроку окупаемости.
  5. Сводные графики для сравнения показателей окупаемости 7 проектов.

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

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

Но, расчет показателей доходности — только первый шаг.

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

Другой способ решения проблемы неопреденности будущих доходов и расходов — определить запасы прочности базового варианта инвестпроекта по различным статьям. Другими словами, на сколько процентов должна измениться статья доходов или расходов, чтобы проект перестал приносить доход (NPV = 0). Но и этот способ требует огромного количества расчетов, т.к. запас прочности будет разным для разных статей доходов/расходов.

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

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

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

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

Новое в этой версии:

Таблица работает под Mac и Windows.

Новое в предыдущих версиях:

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

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

Подробные пояснения включены в таблицу, в том числе в демо-версию.

В таблицу можно вводить данные семи инвестиционных проектов одновременно, для каждого предназначен отдельный лист (листы «Проект 1», … «Проект 7»).

Для каждого проекта можно выполнять анализ устойчивости, результаты выводятся на листы «Анализ 1», … «Анализ 7» соответственно.

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

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

Как работает таблица «Расчет инвестиционных проектов»

Вы вводите

  1. Суммы инвестиций
  2. Все расходы по статьям на каждом шаге инвестиционного проекта, в том числе налоги и другие платежи
  3. Все доходы по статьям на каждом шаге проекта

Используя введенные данные таблица делает следующее:

Рассчитывает все показатели инвестиционного проекта

  1. Ставку дистонтирования по методике WACC — средневзвешенной стоимости капитала
  2. Чистый доход
  3. Чистый дисконтированный доход (NPV)
  4. Внутреннюю норму доходности (IRR)
  5. Индекс прибыльности (PI)
  6. Коэффициент окупаемости инвестиций (ROI)
  7. Срок окупаемости (PBP или PP)
  8. Дисконтированный срок окупаемости (DPBP или DPP)

Выполняет анализ устойчивости инвестиционного проекта

  1. Рассчитывает Чувствительность показателей инвестпроекта к изменениям указанных статей доходов и расходов.
  2. Рассчитывает Запас прочности показателей чистый доход и чистый дисконтированный доход (NPV) по указанным статьям доходов и расходов.

Сравнивает до семи инвестиционных проектов

  1. Качественно (визуально) по всем показателям.
  2. Количественно по указанным показателям.

Если Вы хотите сделать расчет окупаемости инвестиционного проекта, исследовать его сильные и слабые стороны и при этом не утонуть в расчетах, а сосредоточиться на самом проекте — Вам поможет моя таблица «Расчет инвестиционных проектов».

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

Таблицы работают в Excel для Windows (Excel 2007, 2010, 2013, 2016, 365) и Mac (Excel 2011, 2016, 365). В ОpenOffice не работают.

Как говорится, лучше один раз увидеть — Посмотрите подробнее в Демо-версии

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

Листы защищены от случайных изменений и порчи формул. Ячейки для ввода (не защищенные) помечены фоном . При желании Вы можете снять защиту, как это сделать написано на первом листе с инструкциями.

Если нужно, вот подробное описание.

Подробное описание

  • Сумму инвестиций и стоимость привлеченных ресурсов в процентах годовых. Если инвестиции привлечены из разных источников с разной стоимостью, вводите каждую часть отдельно. Таблица рассчитывает общую сумму инвестиций и средневзвешенную стоимость капитала (WACC). Значение WACC рекомендуется взять в качестве ставки дисконтирования.
  • Можно рассчитывать окупаемость инвестиций с переменной ставкой дисконтирования. Для длительных инвестиционных проектов ставка дисконтирования может увеличиваться с течением времени, отражая увеличение неопределенности и рисков.
  • Шаг инвестиционного проекта. Это период, по итогам которого осуществляется промежуточное подведение итогов произведенных затрат и полученных доходов. Длина шага может быть любой. Число шагов проекта неограничено.
  • Суммы доходов и расходов по статьям на каждом шаге инвестпроекта. В исходном состоянии таблица содержит 15 статей (столбцов) доходов и 15 статей (столбцов) расходов. Если этого мало, можно вставить нужное количество столбцов для новых статей доходов и расходов.
  • Отклонения от базового варианта в статьях для расчета чувствительности проекта. Можно задать отклонения для первых 10 статей доходов и первых 10 статей расходов. На всякий случай поясню: Вы задаете денежные потоки (доходы и расходы) только для одного базового варианта. Потом для нужных статей задаете отклонение от базового варианта в процентах. Сами денежные потоки с учетом заданного отклонения пересчитывает таблица и соответственно пересчитывает показатели проекта.

Для всего инвестиционного проекта таблица рассчитывает:

  1. Ставку дистонтирования по методике WACC — средневзвешенной стоимости капитала
  2. Чистый доход — сумму всех доходов и расходов
  3. NPV — Чистый дисконтированный доход
  4. IRR — Внутреннюю норму доходности в годовом исчислении
  5. IRR — Внутреннюю норму доходности в пересчете на шаг
  6. PI — Индекс прибыльности
  7. ROI — Коэффициент окупаемости инвестиций
  8. PBP — Срок окупаемости в годах
  9. PBP — Срок окупаемости в шагах.
  10. DPBP — Дисконтированнный срок окупаемости в годах
  11. DPBP — Дисконтированнный срок окупаемости в шагах
  12. Чувствительность показателей к изменениям статей доходов/расходов
  13. Запас прочности чистого дохода и чистого дисконтированного дохода (NPV) по статьям доходов и расходов

На каждом шаге проекта таблица рассчитывает:

  1. Доход суммарно по всем статьям
  2. Расход суммарно по всем статьям
  3. Чистый доход (доход минус расход)
  4. Дисконтированный чистый доход
  5. Чистый доход нарастающим итогом
  6. Дисконтированный чистый доход нарастающим итогом

Анализ устойчивости проекта

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

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

На приведенном рисунке зарплата увеличилась на 10%, нижняя граница инвестиций увеличилась на 2%, верхняя на 2.39%, чистый доход уменьшился на 10%, NPV уменьшился почти на 20%.

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

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

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

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

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

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

Как получить таблицу

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

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

  • Вариант Полный. Расчет показателей, анализ устойчивости и сравнение до семи проектов — 2990 руб.
  • Вариант Анализ. Расчет показателей и анализ устойчивости одного инвестпроекта — 1890 руб.
  • Вариант Окупаемость. Расчет показателей одного инвестиционного проекта — 1490 руб.

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

Вам будет интересно  Как ставить шорт в втб мои инвестиции

Олеся [22.05.2019 в 06:02]
Добрый день уважаемый Владимир! Спасибо Вам большое за таблицы по расчету инвест.проектов! Первое время конечно нужно немного разобраться, но в целом, таблица очень удобная для быстрого расчета окупаемости инвест.вложений и наглядного анализа. Очень удобно считать новые проекты.

Александр [Кемерово] [04.12.2017 в 10:40]
Здравствуйте Владимир.
При разработки бизнес-плана строительства крупного обьекта в Европе, использовали таблицу ОКУПАЕМОСТИ ИНВЕСТИЦИЙ. Бизнес план прошел контроль по ЮНИДО, таблица соответствовала всем международным требованиям. Спасибо.

Евгения [25.07.2015 в 19:39]
Большое спасибо за таблицу «Инвестиции. Вариант АНАЛИЗ». Очень помогла, удобна в обращение, намного быстрее считать, чем в ручную Спасибо, очень довольны

Михаил [23.06.2014 в 07:45]
Владимир, спасибо за таблицу! Расчет окупаемости инвестиций.Сначала было немного непонятно, но с Вашей помощью быстро разобрался, директор по инвестициям проверил- оценил скорость и правильность расчетов.

Ирина [Москва] [02.05.2012 в 12:19]
Спасибо Вам Владимир, Ваша таблица «Окупаемость инвестиций» очень выручила. За день надо было проверить цифры по строительству жилого многоквартирного дома. 1 мая 2012г. нашла сайт этот в интернете и решила сразу оплатить, рискнула и не ошибласть. К вечеру 1 мая получила таблицу и быстро все посчитала , очень благодарна автору. Сегодня приобрела «Объединенную таблицу». Спасибо Вам Владимир, так как, если отдавать на просчет специалистам, по времени долго и деньги другие. Очень рекомендую.

Гапликов Алексей [Санкт-Петербург] [16.04.2012 в 14:58]
www.doctor911.ru Большое спасибо за таблицу! Очень пригодилась!Спасибо за сэкономленное время. Использовали при составлении бизнес-плана по строительству коммерческого дома престарелых.

Вячеслав [18.02.2012 в 18:34]
Спасибо! Очень нужна была таблица окупаемости инвестиций для бизнес-плана. Получил в течение 2-3-х часов после оплаты. Все выполнено понятно и доступно, никаких трудностей для заполнения. Ожидания были полностью оправданы. До этого пытался сам написать программку, но выходило очень примитивно. Здесь охвачен широкий спектр параметров. Хорошая наглядность. Особенно понравились графики.

Лия [14.02.2012 в 20:42]
Спасибо огромное! Второй раз купили таблицу «Окупаемость инвестиций» (новую версию со скидкой — В.П.). Нарадоваться не могу. Очень сильно облегчает расчеты. Качество, оперативность, доходчивость выше всяких похвал.

Яна [30.08.2011 в 10:07]
Работаем с инвестиционной таблицей, проверили все данные в аналогичных программах, результатом остались довольны, спасибо)))))

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

При выходе новой версии:

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

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

Сравнение вариантов и цены

Таблица
«Расчет инвестиционных проектов»
версия 7.0 для Windows, 7.1 для MAC и Windows

Таблицы работают в Excel для Windows (Excel 2007, 2010, 2013, 2016, 365) и Mac (Excel 2011, 2016, 365). В ОpenOffice не работают.

Вариант
«Окупаемость»

Рассчитывает для одного проекта:

Показатели окупаемости:

Вариант
«Анализ»

Рассчитывает для одного проекта:

Показатели окупаемости:

  1. Ставку дисконтирования методом WACC
  2. NPV
  3. IRR
  4. PI
  5. ROI
  6. PBP — Срок окупаемости
  7. DPBP — Дисконтированный срок окупаемости

Учет рисков и анализ устойчивости:

Вариант
«Полный»

Рассчитывает для семи проектов:

Показатели окупаемости:

  1. Ставку дисконтирования методом WACC
  2. NPV
  3. IRR
  4. PI
  5. ROI
  6. PBP — Срок окупаемости
  7. DPBP — Дисконтированный срок окупаемости

Учет рисков и анализ устойчивости:

  1. Отклонения показателей проектов от базовых значений при изменениях статей доходов и расходов
  2. Чувствительность показателей проектов к изменениям статей
  3. Запас прочности чистого дохода и NPV для каждого проекта

Сравнивает качественно и количественно семь инвестиционных проектов:

  1. Формирует сводную таблицу показателей
  2. Строит сводные графики сравнения показателей
  3. Позволяет выбрать критерии (показатели проектов) для сравнения
  4. Позволяет задать вес (важность) каждого критерия
  5. Вычисляет суммарные веса проектов с учетом весов критериев

Рекомендую для упрощенных формальных расчетов (дипломы, курсовые)

Рекомендую для расчета реальных проектов

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

Windows 1490 руб.
Mac и Windows 1788 руб.

Оформить заказ
Windows

Оформить заказ
Mac и Windows

Windows 1890 руб.
Mac и Windows 2268 руб.

Оформить заказ
Windows

Оформить заказ
Mac и Windows

Windows 2990 руб.
Mac и Windows 3588 руб.

Оформить заказ
Windows

Оформить заказ
Mac и Windows

Таблицы работают в Excel для Windows (Excel 2007, 2010, 2013, 2016, 365) и Mac (Excel 2011, 2016, 365). В ОpenOffice не работают.

Телефон для вопросов оплаты и доставки +7 927 055 9473
почта v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru)

Способы оплаты:

  1. Robokassa
    • Карты Visa, MasterCard, Maestro, Мир
    • Интернет-клиент Альфа-Банк, Русский Стандарт, Промсвязьбанк и др.
    • Терминалы
    • Салоны связи Евросеть, Связной
    • Электронные деньги Яндекс.Деньги, QIWI, WebMoney, Wallet One (W1), Элекснет
  2. Банк
    на расчетный счет Прохоров Владимир Викторович (ИП), ИНН 645400330452,
    р/с 40802810711010053426, Филиал «Бизнес» ПАО «Совкомбанк» г. Москва,
    к/с 30101810045250000058, БИК 044525058.

Обратите внимание, что указание формы собственнсти (ИП) является обязательным.
После оформления заказа можно распечатать счет для юр. лиц
или квитанцию по форме ПД-4 для физ. лиц.

Также можно получить счет, написав на v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru).

В назначении платежа, пожалуйста, указывайте номер счета, например:
По счету № . За электронные таблицы «Расчет инвестиционных проектов». Без НДС.

При платежах не из России в начале назначения платежа укажите код валютной операции
Например, назначение платежа:
По счету № . За электронные таблицы «Расчет инвестиционных проектов». Без НДС.
Где VO — заглавные латинские буквы, 10100 — цифры. Фигурные скобки обязательны. Пробелы внутри фигурных скобок не допускаются.

Cсылка на скачивание таблиц будет отправлена на указанный в заказе e-Mail после поступления денег. Если письма долго нет, проверьте папку СПАМ.

Для способа 1 (Robokassa) письмо со ссылкой отправляется автоматически, сразу после поступления денег. Обычное время поступления денег — минуты. Сервис Robokassa высылает электронный чек согласно 54-ФЗ «О применении контрольно-кассовой техники».

Для способа 2 (Банк) письмо со ссылкой отправляется после поступления платежа на расчетный счет. Время поступления денег 1-2 рабочих дня. Юридическим лицам и ИП высылаю pdf-копии накладной по форме Торг 12. Оригинал накладной высылается по запросу, в запросе укажите, пожалуйста, почтовый адрес.

Если нужны еще какие-либо документы, прошу согласовать этот вопрос ДО ОПЛАТЫ.

Дальнейшая переписка по поводу полученных таблиц

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

Оформить заказ
Windows
Вариант ОКУПАЕМОСТЬ

Оформить заказ
Mac и Windows
Вариант ОКУПАЕМОСТЬ

Оформить заказ
Windows
Вариант АНАЛИЗ

Анализ инвестиционного проекта. Пример расчета NPV и IRR в Excel

Рассмотрим анализ инвестиционного проекта: рассчитаем основные ключевые показатели эффективности инвестиционного проекта. Среди ключевых показателей можно выделить два наиболее важных – NPV и IRR.

  • NPV – чистый дисконтированный доход от инвестиционного проекта (ЧДД).
  • IRR – внутренняя норма доходности (ВНД).

Рассмотрим данные показатели более детально и рассчитаем простой пример работы с ними в таблицах Excel.

Чистый дисконтированный доход (NPV )

NPV (Net Present Value, Чистый Дисконтированный Доход) – пожалуй, один из наиболее популярных и распространенных показателей эффективности инвестиционного проекта. Рассчитывается он как разница между денежными поступлениями от проекта во времени и затратами на него с учетом дисконтирования.

Расчет чистого дисконтированного дохода (NPV):

  1. Определить текущие затраты на проект (сумма инвестиционных вложений в проект) – Io.
  2. Произвести расчет текущей стоимости денежных поступлений от проекта. Для этого доходы за каждый отчетный период приводятся к текущей дате (дисконтируются) – PV.
  3. Вычесть из текущей стоимости доходов (PV) наши затраты на проект (Io). Разница между ними будет чистый дисконтированный доход – NPV.

PV что это такое и как рассчитать? Расчет дисконтированного дохода

Расчет чистого дисконтированного дохода (NPV)

NPV=PV-Io

CF – денежный поток от инвестиционного проекта;
Iо – первоначальные инвестиции в проект;
r – ставка дисконта.

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

Формула чистого дисконтированного дохода (NPV) изменяется если инвестиционные вложения в проект осуществляются в несколько этапов (периодов) и имеет следующий вид.

CF – денежный поток;
It – сумма инвестиционных вложений в проект в t-ом периоде;
r – ставка дисконтирования;
n – количество этапов (периодов) инвестирования.

Внутренняя норма доходности (IRR). IRR что это за показатель

Внутренняя норма доходности (Internal Rate of Return, IRR) – второй наиболее популярный показатель оценки инвестиционных проектов. Он определяет ставку дисконтирования, при которой инвестиции в проект равны 0 (NPV=0). Другими словами затраты на проект равны доходам от инвестиционного проекта.

IRR = r, при которой NPV = 0, находим из формулы:

CF – денежный поток;
It – сумма инвестиционных вложений в проект в t-ом периоде;
n – количество периодов.

Расчет IRR позволяет сравнить эффективность вложения в различные по протяженности инвестиционные проекты (по NPV это сделать нельзя). Данный показатель показывает норму доходности/возможные затраты при вложении денежных средств в проект (в процентах).

Пример определения NPV в Excel

Для наглядности рассчитаем расчет NPV в MS Excel. Для расчета NPV используется функция =ЧПС().
Найдем чистый дисконтированный доход (NPV) инвестиционного проекта. Необходимые инвестиции в него – 90 тыс. руб. Денежный поток, которого распределен по времени следующим образом (как на рисунке). Ставка дисконтирования равна 10%.

Анализ денежных поступлений от инвестиционного проекта

Произведем расчет чистого дисконтированного дохода по формуле excel:

Где:
D3 – ставка дисконта.
C3 – вложения в 0 периоде (наши инвестиционные затраты в проект).
C4:C11 – денежный поток проекта за 8 периодов.

Расчет NPV в Excel. Пример расчета

В итоге, показатель чистого дисконтированного дохода равен NPV=51,07 >0, что говорит о том, что есть целесообразность вложения в инвестиционный проект. К примеру, если бы мы вложили 90 тыс. руб в банк со ставкой 10% годовых, то через год получили бы чуть меньше 9 тыс., что меньше чем 51,07 от вложения в инвестиционный проект.

Мастер-класс: “Как рассчитать NPV для бизнес плана”

Пример определения IRR в Excel

Для определения IRR в Excel воспользуемся встроенной функцией =ЧИСТВНДОХ().
У нас в примере доход от проекта поступал в разные интервалы времени. Для этого можно использовать функцию Excel =ВСД(C3:C11). В итоге доходность от вложения в инвестиционный проект равна 38%.

Расчет IRR в Excel

В завершение картинка финансового анализа проекта целиком.

Мастер-класс: “Как рассчитать внутреннюю норму доходности для бизнес плана”

Автор: Жданов Василий Юрьевич, к.э.н.

Доход Excel

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

Срок окупаемости инвестиций (англ. PP, payback period) – это минимальный период времени возврата вложенных средств в инвестиционный проект, бизнес или любую другую инвестицию. Срок окупаемости является ключевым показателем оценки инвестиционной привлекательности бизнес плана, проекта и любого другого объекта инвестирования. Рассмотрим различные показатели срока окупаемости используемые на практике:

  • Срок окупаемости инвестиций (PP).
  • Дисконтированный срок окупаемости инвестиций (DPP).
  • Срок окупаемости инвестиций с учетом ликвидационной стоимости (BO PP).

Инфографика: Срок окупаемости инвестиций (PP, DPP, BO DPP)

★ Инвестиционная оценка в Excel. Расчет NPV, IRR, DPP, PI за 5 минут

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

#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».

★ Инвестиционная оценка в Excel. Расчет NPV, IRR, DPP, PI за 5 минут

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

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

Формула расчета валовой прибыли в процентах

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

На рисунке представлен финансовый отчет определенной производственной фирмы:

Валовая прибыль находится в ячейке B4, а процентная валовая прибыль в ячейке C4: =B4/$B$2

Формула расчета валовой прибыли просто вычитает значение ячейки B3 от B2. В формуле вычисления валовой прибыли в процентах значение ячейки B4 разделено на значение в $B$2:

Обратите внимание здесь используется процентный формат ячеек – это значит не нужно умножать на 100. Такой же результат мы получим по выше описанной формуле =B4/B2*100, но здесь не используем процентный формат ячеек.

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

Формула расчета процента наценки в Excel

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

Наценка рассчитывается путем деления цены на расходы и от результата вычитается число 1:

Устанавливая наценку в размере 32% получаем валовую прибыль в процентах 24%. То есть как описано выше в предыдущем примере =B8/B6. Чтобы определить размер наценки покрывающую процентную валовую прибыль в 32% (как в столбце D) используйте следующую формулу:

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

Интересный факт! Главное отличие процентной валовой прибыли от наценки заключается в формуле вычисления. Разницу очень легко заметить в этих двух показателях по их сути – валовая прибыль никогда не будет равна или превышать 100% в отличии от наценки. Для наглядного доказательства приведем простой пример, умножим цену на 10 000:

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

Суть понятия коэффициента рентабельности

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

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

Если рентабельность растет:

  1. Увеличение выручки происходит скорее, чем увеличение затрат (либо выросли объемы продаж, либо изменился ассортимент).
  2. Затраты снижаются быстрее, чем уменьшается выручка (компания либо подняла цены на продукцию, либо изменила структуру ассортимента).
  3. Выручка растет, а затраты становятся меньше (выросли цены, поменялся ассортимент либо изменились нормы затрат).

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

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

Если рентабельность снизилась:

  1. Затраты растут быстрее, чем выручка (под влиянием инфляции, снижения цен, увеличения норм затрат или изменения структуры ассортимента).
  2. Снижение выручки происходит быстрее, чем снижение затрат (упали продажи).
  3. Выручка становится меньше, а затраты – больше (увеличились нормы затрат, понизились цены или поменялся ассортимент).

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

Как рассчитать рентабельность продаж в Excel

Международное обозначение показателя – ROS. Всегда рассчитывается коэффициент рентабельности продаж по прибыли от продаж.

ROS = (прибыль/выручка) * 100%.

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

Формула валовой рентабельности продаж (маржи):

(Валовая прибыль / выручка от реализации) * 100%.

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

Информация для расчета:

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

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

Рентабельность продаж по операционной прибыли (EBIT):

(Операционная прибыль / выручка от реализации) * 100%.

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

Как рассчитать коэффициент рентабельности продаж – формула по балансу (форма 2):

((Стр. 2300 + стр. 2330) / стр. 2110) * 100%.

Данные для расчета:

Рассчитаем рентабельность по операционной прибыли – подставим в формулу ссылки на нужные ячейки:

Формула рентабельности продаж по чистой прибыли:

(Чистая прибыль / выручка) * 100%.

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

Покажем коэффициент рентабельности продаж на графике:

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

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

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

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

Калькуляция себестоимости товаров в торговле

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

Для расчета заполним следующую таблицу:

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

  1. Транспортные расходы, как сообщил отдел логистики, составят 5% от закупочной цены.
  2. Размер пошлины будет отличаться по разным группам товаров. Для товара 1 и 4 – 5%. Для товара 2 и 3 – 10%. Чтобы было удобнее проставлять проценты, отсортируем данные по столбцу «Наименование товара».
  3. Для вычисления используем формулу: закупочная цена + транспортные расходы в денежном выражении + пошлина в денежном выражении.
  4. Формула для расчета планового коэффициента – себестоимость в денежном выражении / закупочную цену.

Уровень затрат на доставку товаров 1 и 4 составит 10%, 2 и 3 – 15%.

Формулы расчета плановой себестоимости продукции в Excel

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

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

Отразим нормы расхода сырья в таблице Excel:

Здесь нам удалось автоматизировать только один столбец – расход с учетом технологических потерь. Формула: =E3+E3*F3.

Обратите внимание! Для столбца «Потери» мы выставили процентный формат. Только в таком случае программа посчитает правильно. Нумерацию строк начинаем выше шапки. Если данные перепутаются, их можно восстановить по номерам.

Вам будет интересно  Нью-Йоркская фондовая биржа начала делистинг трех китайских компаний

Зная нормы, можем рассчитать стоимость материалов (расчет ведется для тысячи единиц товара):

В этой таблице вручную придется заполнить только одну графу – «Цена». Все остальные столбцы ссылаются на данные листа «Нормы». В графе «Стоимость» работает формула: =D3*E3.

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

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

Данные для вычислений таковы:

Расценка рассчитывается по формуле: =C3*D3.

Теперь мы можем посчитать основную зарплату рабочих:

Чтобы заполнить первые два столбца, не считая номер по порядку, мы связали данные этой таблицы с данными предыдущей. Формула для начисления премии: =C3*30%. Основной зарплаты – =C3+D3.

Дополнительная заработная плата – это все выплаты, положенные по закону, но не связанные с производственным процессом (отпуска, вознаграждения за выслугу лет и т.п.).

Другие данные для расчета калькуляции себестоимости мы сразу внесли в таблицу:

В столбце «Расчет показателя» указано, откуда мы берем данные. Если мы ссылаемся на другие таблицы, то используем оттуда итоговые суммы.

Шаблон расчета себестоимости продукта с формулами:

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

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

Дата публикации: 01.02.2018

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

Цель данной статьи — показать, как разработать факторную модель выручки в соответствии с потребностями бизнеса. На практике такая модель может быть достаточно сложной, и для того, чтобы не тратить время на выполнение факторного анализа в Excel, мы будем использовать надстройку Fincontrollex® Variances Analysis Tool, которая позволяет полностью автоматизировать этот процесс. Благодаря такому подходу мы сможем сфокусироваться на анализе данных, а не на разработке формул в Excel.

Как разработать факторную модель выручки

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

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

Управление ассортиментом

Если вы продаете несколько видов продуктов по разной цене, то вы можете управлять ассортиментом продаж. Для этого следует разделить фактор объёма на общий объём реализации всех продуктов и удельный вес каждого продукта в общем объеме:

На практике менеджеры по продажам часто не понимают суть этого фактора, что приводит к неправильному пониманию результатов анализа. Этот фактор следует трактовать как изменение структуры объёмов продаж в сторону продуктов с большей или меньшей ценой. Например, если фактор ассортимента оказал положительное влияние, это означает, что в структуре продаж увеличилась доля продуктов с более высокой ценой, а доля продуктов с более низкой ценой сократилась. Если фактор ассортимента оказал отрицательное влияние — то картина будет противоположной: рост доли продуктов с более низкой ценой и сокращение доли продуктов с более высокой ценой. Не стоит путать влияние этого фактора с изменением цены, так как при расчете фактора ассортимента влияние других факторов элиминируется (исключаются).

Управление каналами продаж

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

Горизонтальный и вертикальный рост

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

Горизонтальные изменение — это изменения объемов продаж за счет открытия новых точек.

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

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

Ввод новых продуктов

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

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

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

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

Вывод продуктов из ассортимента

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

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

В результате вы сможете оценить снижение выручки за счёт вывода продуктов из ассортимента.

Оценивая одновременно ввод новых и вывод старых продуктов, вы сможете оценить эффективность изменения ассортимента.

Управление конверсией

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

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

Управление размером среднего чека

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

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

Управление скидками

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

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

Управление ретро-бонусами

Если вы являетесь производителем продукции, то у вас есть возможность стимулировать интенсивность продаж своих товаров в розничных сетях с помощью ретро-бонусов. Ретро-бонус — это вознаграждение выплачиваемое дистрибьюторам и дилерам за продвижение продукции. Для оценки эффективности стимулирования продаж с помощью ретро-бонусов обычно рассчитывается процент ретро-бонусов к выручке (без учета скидок). Для того, чтобы оценить влияние ретро-бонусов на выручку, необходимо добавить этот фактор в модель:

Собираем все вместе: факторная модель выручки производителя FMCG

В качестве примера, давайте рассмотрим факторную модель выручки производителя FMCG. Для производителей FMCG характерно продавать свою продукцию с помощью каналов дистрибуции, предоставляя дополнительные скидки за объём. Чтобы отразить эту особенность, добавим в базовую модель расчета выручки формулы для управления ассортиментом, скидками и ретро-бонусами, которые мы рассмотрели выше.

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

  1. Общий объём
  2. Ассортимент
  3. Ретро-бонус
  4. Скидка
  5. Цена

Факторная модель готова, и теперь можно перейти к факторному анализу в Excel.

Факторный анализ выручки в Excel — это просто!

Выполнение факторного анализа в Excel достаточно трудоемкая задача даже для опытного пользователя. Поэтому чтобы её существенно упростить мы будем использовать специальную надстройку для Excel Fincontrollex® Variances Analysis Tool. Для активации бесплатной пробной версии вам понадобится зарегистрировать адрес своей электронной почты, на который придет сообщение с ключом активации и ссылкой для скачивания.

Эта надстройка избавит вас от необходимости вводить формулы расчета каждого фактора в книге Excel, самостоятельно создаст сводный отчет по всем факторам и детальный отчет по продуктам, а также, если вы используете Excel 2016 или Office 365, построит диаграмму waterfall (если вы не знакомы с этой диаграммой, то обязательно прочтите статью «Как анализировать диаграмму waterfall (водопад)» т.к. эта диаграмма является превосходным способом показать результаты факторного анализа).

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

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

Для того, чтобы запустить надстройку Fincontrollex® Variances Analysis Tool на ленте Excel перейдите на вкладку fincontrollex.com и в группе Variances Analysis Tool нажмите кнопку Выполнить. Откроется окно надстройки.

Введем название модели

Далее настроим параметры модели. Для этого развернем этот раздел, щелкнув на нем левой кнопкой мыши. Установим вид, единицы измерения и описание модели, как на скриншоте ниже.

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

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

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

У нас остался ненастроенным последний параметр: диапазон с названиями продуктов. Давайте его настроим. На ленте Fincontrollex® Variances Analysis Tool на вкладке Главная в группе Модель нажмите кнопку Диапазон названий.

Все готово и теперь можно выполнить факторный анализ. Для этого на ленте Fincontrollex® Variances Analysis Tool на вкладке Главная в группе Анализ нажмите кнопку Выполнить. Через пару секунд вы получите результат факторного анализа, который будет создан в новой книге Excel.

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

Заключение

Мы рассмотрели базовую модель выручки и основные формулы по приведению её в соответствие с потребностями бизнеса. Эти формулы приведены в качестве примера и могут служить отправной точкой для разработки факторной модели выручки в соответствии с вашими целями и задачами. Использование надстройки Fincontrollex® Variances Analysis Tool для факторного анализа позволяет выполнить анализ моделей любой сложности. Благодаря этому вы сможете сфокусироваться на управлении факторами, которые оказывают влияние на выручку в вашем бизнесе.

Автор Владимир Плахотник

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

http://finances-analysis.ru/buy-xls-invest.htm
http://finzz.ru/analiz-investicionnogo-proekta-raschet-npv-irr-v-excel.html
http://owenural.ru/dohod-excel/