Пример функция тенденция в excel

Расчет NPV в Excel (пример табличный)

Простой срок окупаемости проекта

Шаг 1

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

Удобнее брать период сглаживания в виде нечетного числа, тогда формула для расчета уровней сглаженного ряда:

yi — фактическое значение i-го уровня ряда,

yt — значение скользящей средней в момент времени t,

2p+1 — длина интервала сглаживания.

Но так как мы решили использовать месячную динамику в виде четного числа 12, то данная формула нам не подойдет и мы воспользуемся этой:

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

Сглаживаем наши уровни ряда и растягиваем формулу вниз:

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

В качестве сглаженной я выбрала полином третьей степени, так как он лучше всего описывал уровни временного ряда и имел наибольший R^2.

Функция ПРЕДСКАЗ.ETS — Служба поддержки Office

Как рассчитать простой срок окупаемости в Excel

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

Расчет примера №1

Расчет простого срока окупаемости выглядит следующим образом:

Таблица 1: формулы расчета.

Таблица 2: результаты расчетов:

Расчет примера №2

Для более сложного варианта расчета простого срока окупаемости расчет в Excel производится следующим образом:

Таблица 1: формулы расчета.

Таблица 2: результаты расчетов:

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

Создание сводной таблицы в Excel

Средний чек

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

Формула расчета:

AOV = Revenue / Sales,

где AOV – средний чек, от английского Average Order Value,

Revenue – выручка,

Sales – число сделок за период.

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

Подсчет процентов в табличном редакторе

Табличный редактор хорош тем, что большую часть вычислений он производит самостоятельно, а пользователю необходимо ввести только исходные значения и указать принцип расчета. Вычисление производится так: Часть/Целое = Процент. Подробная инструкция выглядит так:

1. Жмем на необходимую ячейку правой клавишей мышки.
2. В возникшем маленьком специальном контекстном меню необходимо выбрать кнопку, имеющую наименование «Формат ячеек».

1

3. Здесь необходимо щелкнуть левой клавишей мышки на элемент «Формат», а затем при помощи элемента «ОК», сохранить внесенные изменения.

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

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

2

2. В строчку D2 вводим такую формулу: =С2/В2.
3. Используя вышеприведенную инструкцию, переводим поле D2 в процентный вид.
4. Используя специальный маркер заполнения, растягиваем введенную формулу на всю колонку.

3

5. Готово! Табличный редактор сам высчитал процент реализации плана для каждого товара.

Расчет доли в процентах (удельного веса).

#3 Срок окупаемости инвестиций с учетом ликвидационной цены

Срок окупаемости с учетом ликвидационной цены (англ. Bail-Out Payback Period) – представляет собой период возврата валютных средств с учетом остаточной цены активов, сделанных в вкладывательном проекте. При осуществлении вкладывательного проекта могут создаваться активы, которые могут быть проданы (ликвидированы) в итоге этого срок окупаемости проекта значительно сокращается.

где:

IC (Invest Capital) – начальные вкладывательные издержки в проекте;

RV (Residual Value)­– ликвидационная стоимость активов проекта;

CF_i (Cash Flow) – валютный поток от проекта в i-й период времени, который представляет собой сумму незапятанной прибыли и амортизации.

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

Конверсия лидов в сделки

Эта метрика отвечает на один из самых важных для менеджеров коммерческих отделов вопросов: сколько лидов преобразуется в продажи?

Формула: Conv = Sales / Leads,

Где Conv – конверсия;

Sales – количество завершенных сделок;

Leads – число потенциальных покупателей, лидов.

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

Конверсия улучшает понимание идеального клиента – для кого лучше всего подходит продвигаемое ценностное предложение. А на основе этого знания, двигаясь в обратную сторону по воронке, можно разбираться, как лучше преподносить ценностное предложение такому клиенту, как улучшить воронку и где «водятся» эти клиенты?

Из чего состоит временной ряд

Уровни временного ряда (Yt) представляют из себя сумму двух компонент:

1. Регулярную составляющую
2. Случайную составляющую

В свою очередь регулярная составляющая состоит из:

1. Тренда
2. Сезонности
3. Циклической составляющей

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

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

То есть, в общем случае временной ряд представляет из себя наличие четырех составляющих:

1. Тренд (Tt)
2. Сезонность (St)
3. Цикличность (Ct)
4. Случайные возмущения (Et)

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

Сезонные колебания. Индексы сезонности. Метод постоянной средней

Продолжаем тему, начатую статьей Аналитические показатели динамики.

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

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

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

Где i — порядковый номер сезонного цикла (года), j — порядковый номер внутрисезонного периода (месяца).

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

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

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

Здесь

,

где

, средняя по каждому внутрисезонному периоду j (месяцу) для всех n сезонов

, общая средняя по всем сезонам (n) и внутрисезонным периодам (m)

Калькулятор ниже.

addimport_exportmode_editdelete

Показатели

chevron_leftchevron_right

Для разделения полей можно использовать один из этих символов: Tab, «;» или «,» Пример: -50.5;-50.5;-50.5;-50.5;-50.5;-50.5;-50.5;-50.5;-50.5;-50.5;-50.5;-50.5

Знаков после запятой: 1

Индекс сезонности

Сохранить share extension

Созданные возможности продаж

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

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

Формула проста – число потенциальных продаж инициированных за отчетный или планируемый период:

SOC = ∑Customersp_t,

Где SOC – созданные возможности продаж (Sales opportunities created),

Customersp– потенциальные покупатели – те, кому позвонил менеджер и кто заинтересовался сделанным ему предложением;

t – отчетный период.

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

1. Эффективно ли продвижение? Сопоставим количество созданных возможностей с количеством звонков на одного менеджера.
2. Эффективен ли используемый скрипт продаж? Сравним число созданных возможностей с числом состоявшихся сделок.

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

География продаж

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

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

Популярные статьи

1

PEST-анализ: что это такое и как его провести на примерах

Стратегический менеджмент – это работа с неопределенностью во внутренней и, особенно, во вне…

23.08.2019

 • 
Евгения Чернова

2

Расчет себестоимости

Расчет себестоимости – очень сложный процесс

Важно не только правильно обобщить все затраты. Надо..

17.03.2020

 • 
Ольга Воробьева

3

Анализ финансовых результатов деятельности компании: пошаговый алгоритм

Анализ финансовых результатов деятельности предприятия дает понимание, насколько эффективно оно ра…

31.01.2020

 • 
Ольга Воробьева

4

Система 5S на производстве: секреты успешного внедрения

Термин «5S» стал популярен в 1980-х годах в производственном секторе Японии. В это время успехи ко…

22.07.2019

 • 
Ильнар Фархутдинов

Для расчета значений тренда:

1. Определим коэффициенты уравнения линейного тренда y=bx+a с помощью функции Excel =Линейн(). Для этого в ячейки Excel вводим функцию =линейн(объёмы продаж за 5 лет; номера периодов; 1;0). Выделяем 2 ячейки, в левой — формула =линейн(), нажимаем комбинацию клавиш в следующей последовательности (F2 + Ctrl+Shift+Enter). Excel рассчитает для нас значение коэффициентов a и b.
2. Рассчитываем значения тренда. Для этого в уравнение y=bx+a подставляем рассчитанные коэффициенты тренда b и а, x – номер периода во временном ряде. Получаем y-значения линейного тренда для каждого периода (см. вложенный файл).

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

Прогноз посещаемости с помощью функции ТЕНДЕНЦИЯ в Excel

Пример 2. В кинотеатре фильмы показывают в различные сеансы, которые начинаются в 12:00, 16:00 и 21:00 соответственно. Каждый фильм имеет собственный рейтинг, в виде оценки от 1 до 10 баллов. Известны данные о посещаемости нескольких последних сеансов. Предположить, какой будет посещаемость для следующих фильмов:

1. Рейтинг 7, сеанс 12:00;
2. Рейтинг 9,5, сеанс 21:00;
3. Рейтинг 8, сеанс 16:00.

Таблица исходных данных:

Для расчета используем функцию:

1. Перед вводом функции необходимо выделить ячейки C11:C13;
2. Расчет производим на основе диапазона значений A2:B10 (учитывается как время сеанса, так и рейтинг фильма)

В результате получим:

Не забывайте, что ТЕНДЕНЦИЯ является массивной функцией поэтому после ее ввода не забудьте выполнить ее в массиве. Для этого жмем не просто Enter, а комбинацию клавиш Ctrl+Shift+Enter. Если в строке формул по краям функции появились фигурные скобки , значит функция выполняется в массиве и все сделано правильно.

Шаг 1

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

Удобнее брать период сглаживания в виде нечетного числа, тогда формула для расчета уровней сглаженного ряда:

Но так как мы решили использовать месячную динамику в виде четного числа 12, то данная формула нам не подойдет и мы воспользуемся этой:

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

Сглаживаем наши уровни ряда и растягиваем формулу вниз:

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

В качестве сглаженной я выбрала полином третьей степени, так как он лучше всего описывал уровни временного ряда и имел наибольший R^2.

Общая характеристика

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

Понятие и предназначение

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

ТР = Текущая величина/Базовая величина*100%.

Когда итоговая сумма получается более 100%, происходит рост коэффициентов, а при цифрах ниже 100 — снижение. Формула расчета темпа роста в процентах показывает, во сколько раз новое значение отличается от предыдущего или постоянного базисного. Показатель ТР может определяться как увеличение, рост, сокращение уровня.

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

Темп прироста = (Текущее значение — базовое значение)/ базовое значение*100%.

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

Среднемесячный ТР и ТП применяется для определения усредненной величины изменения показателей за год или другой срок.

Средние показатели:

1. ТР = корень в степени n Y 1/ Y 0.
2. ТП = средний темп роста — 100.

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

Основные вычисления

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

Методы расчета:

• цепной;
• базисный.

В программе Excel на примере наглядно виден способ расчета с помощью базисного и цепного ТР, ТП.

Чтобы рассчитать базисный ТР, нужно произвести расчет темпов роста всех показателей. ТР и ТП первого показателя не должен считаться. За базисную величину принимают Показатель 1, поэтому базовые ТР и ТП должны рассчитываться исходя из этого положения. При расчете Показатель (П)2 делится на Показатель 1 и умножается на 100, затем П3 делится на П1 и умножается на 100.

В расчет цепного темпа роста (ЦТР) используются все показатели, кроме первого. Способ вычисления отличается тем, что Показатель 2 делится на П1, умножается на 100. Затем П3 делят на П2 и умножают на 100. База вычисления — основной показатель. П4 делят на П3 и умножают на 100, при расчете ЦТП из каждого показателя ЦТР вычитают 100.

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

Между двумя значениями имеется прямая зависимость. Разница равна 100% и отражается в формуле ТП.

Средние уровни и индексы сезонности

Месяц	Средний уровень,	Индекс сезонности, %,
январь	5,74	104,2
февраль	6,45	117,1
март	5,27	95,6
апрель	5,4	88,0
май	4,63	84,0
июнь	5,01	91,0
июль	5,34	96,9
август	5,64	102,4
сентябрь	5,0	90,7
октябрь	5,39	97,8
ноябрь	6,13	111,3
декабрь	6,14	111,4

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

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

Индекс
сезонности
,

где
— исходные уровни ряда:

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

i
— номер месяца или квартала, для которого
определяется индекс сезонности:

n
— число лет наблюдения за процессом.

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

Пример 6.

Определить
скользящие средние по трем уровням
ряда.

Таблица 14

Реализация
сахара в продовольственных магазинах
города, т

Месяц	1999	2000	2001
Исходные уровни,	Сглажен. уровни,	Исходные уровни,	Сглажен. уровни,	Исходные уровни,	Сглажен. уровни,
январь	78,9	——-	108,6	106,2	129,1	131,3
февраль	78,1	81,0	107,9	107,8	128,6	129,5
март	86,0	87,2	106,8	115,4	130,7	137,4
апрель	97,5	88,9	132,1	117,3	152,8	141,1
май	83,3	88,9	113,0	119,0	139,8	146,7
июнь	86,0	86,6	111,8	116,4	147,4	150,3
июль	90,6	87,6	124,4	116,8	163,8	152,5
август	86,1	86,0	114,1	115,6	146,3	149,3
сентябрь	81,3	90,8	108,4	115,6	137,8	145,4
октябрь	105,1	94,5	124,0	117,0	152,2	144,4
ноябрь	97,2	101,5	118,0	126,2	143,2	150,6
декабрь	102,1	102,6	136,3	128,0	156,5	——-

На
основе исходных и сглаженных уровней
ряда строятся индексы сезонности:

Так
для января:

Для
февраля:

и
т. д.

Таблица
15

Индексы сезонности
по месяцам

Месяц	1	2	3	4	5	6	7	8	9	10	11	12
Индекс сезонности, %,	100	98	96	110	95	98	106	96	93	107	95	103

Таким образом,
повышенный спрос на сахар в магазинах
города наблюдается в апреле, июне,
ноябре, декабре.

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

Объем
продаж сахара, т.

Индекс
сезонности, %

Виды моделей временного ряда

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

1. Аддитивная модель

Мультипликативная модель

Смешанная модель

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

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

Построение этих моделей сводится к расчету тренда (Tt), сезонности (St) и случайных возмущений (Et) для каждого уровня ряда (Yt).

Как считать: по отдельному товару или по группе?

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

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

Шаг 4

Имея рассчитанные значения S(t) и T(t) мы можем рассчитать прогнозные значения уровней ряда Y(t). Для этого накладываем уровни сезонности на тренд.

Теперь построим график известных значений Y(t) и спрогнозированных за 2018 год.

Вот мы и нашли спрогнозированные значения уровней продаж на 2018 год. Значения отражают возрастающую тенденцию и сезонные пики. Конечно, эти данные не дают 100% точности, ведь существует множество внешних воздействий, которые могут изменить направление тренда, поэтому к прогнозным значениям обычно строят доверительный интервал, это такой коридор, внутри которого могут колебаться прогнозные значения с заданной вероятностью (чаще всего выбирают 95%). Но об этом я расскажу в следующей статье.

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

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

Важно не терять своих клиентов как можно дольше.

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

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

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

Использование Excel для оценки эффективности проекта

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

Мы уже рассматривали пример расчета NPV и IRR по формулам. Также были предоставлены формулы всех перечисленных показателей и их расчет ручным методом .

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

Здесь вы можете бесплатно скачать электронную таблицу Excel для расчета NPV, внутренней нормы прибыли (IRR), простых и дисконтированных периодов амортизации. Мы предоставим таблицу для расчета NPV за 25 лет и менее, просто введите в нее значения предполагаемого размера инвестиций, размера ставки дисконтирования и суммы годовых денежных потоков. И NPV будет рассчитываться автоматически.

Вот таблица. 

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

Теперь поговорим о том, как использовать эту таблицу для расчета NPV, IRR, срока окупаемости. Здесь уже приведен пример расчета NPV.

Если вам нужно рассчитать NPV за 5 лет. Знайте ставку дисконтирования 30% (т.е. 0,3). Годовые денежные потоки известны:

Сумма вложений 500 тыс. Руб.

В таблице Excel мы скорректируем значение ставки дисконтирования на 0,3 (2-я строка сверху), мы скорректируем стоимость инвестиции (5-я строка, 3-й столбец) на 500.

Списываем денежные потоки и их сумму за 25 лет. (также удалите строки чистых денежных потоков с 6-го по 25-й год и значение NPV для дополнительных лет). Мы вставляем ценности, которые мы знали за 5 лет. Получаем следующие данные.

Годы

Сумма инвестиций, тыс. Руб

Денежные потоки, тыс. Руб. (CF)

Чистые денежные потоки, тыс. Руб

Дисконтированная чистая прибыль, тыс. Руб. (NPV)

Общий

500.00

1350,00

562,09

62,09

Как видите, нам не пришлось рассчитывать NPV самостоятельно, таблица Excel рассчитала этот показатель за нас.

Теперь давайте узнаем, как рассчитать IRR в Excel на конкретном примере. В MS Excel есть функция, называемая сопоставлением параметров. В 2003 Excel эту функцию можно найти в Сервис-> Выбор параметров.

Ранее мы говорили, что IRR — это ставка дисконтирования, при которой NPV равна нулю.

Нажмите Excel Service-> Parameter Selection, откроется окно,

Мы знаем, что NPV = 0, мы выбираем значение ячейки с NPV для 5-го года, присваиваем ему значение 0, изменяя значение ячейки, в которой расположена ставка дисконтирования. После расчета получаем.

Следовательно, NPV равна нулю при ставке дисконтирования 35,02%. Те. Внутренняя норма доходности (IRR) = 35,02%.

Теперь давайте рассчитаем простую и дисконтированную стоимость периода окупаемости с помощью этой таблицы Excel.

Срок погашения прост:

Видим из таблицы, что у нас вложения 500 т.р. Через 2 года получим доход 300 т.р. За 3 года получим 600 т.р. Это означает, что срок окупаемости составит более 2-х и менее 3-х лет.

В ячейке F32 (строка 32 файла Excel) нажмите F2 и исправьте, вместо «1+» у нас будет «2+», мы изменим с 1 на 2 и преобразуем формулу следующим образом, вместо «= 1 + (- (D5 -C5) / D6) «, у нас будет» = 2 + ((((D5 + D6) -C5) / D7) «, другими словами, мы добавили инвестиционный долг в конце второй год, разделенный на денежный поток третьего года. Получаем 2,66 года.

Пример расчета дисконтированного периода амортизации:

NPV колеблется от меньшего до большего с 4-го по 5-й год, что означает, что период амортизации с учетом дисконтирования будет больше 4 и меньше 5 лет.

В ячейке F3 3 (строка 33 файла Excel) нажмите F2 и исправьте, вместо «2+» у нас будет «4+», измените с 2 на 4 и преобразуйте формулу следующим образом вместо «= 2». + (- F6 / E7) «У нас будет« = 4 + (- F8 / E9)) », другими словами, мы добавили соотношение между последней отрицательной NPV и чистым денежным потоком в следующем году (4 + — (-45,64 / 107,73) .

Получаем 4,42 года — срок окупаемости с учетом скидки.

Этот пример предназначен для практической практики. Кандидат экономических наук, доцент Одинцова Е.В.

#1 Срок окупаемости инвестиций (PP). Формула

ИК (Invest Capital) — первоначальные инвестиционные затраты в проект;

CFi (Cash Flow) — денежный поток проекта в i-м временном периоде, который складывается из чистой прибыли и амортизации.

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

где это находится:

A (Амортизация) — амортизация, вид денежного потока, не являющийся затратами;

NP (Net Profit) — чистая прибыль инвестиционного проекта.

Программа InvestRatio — расчет всех инвестиционных коэффициентов в Excel за 5 минут (расчет коэффициентов Sharpe, Sortino, Trainor, Kalmar, Modilyanka beta, VaR) + прогноз движения по маршруту

Виды моделей временного ряда

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

1. Аддитивная модель

Мультипликативная модель

Смешанная модель

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

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

Построение этих моделей сводится к расчету тренда (Tt), сезонности (St) и случайных возмущений (Et) для каждого уровня ряда (Yt).

Пример формул таблицы для анализа прогноза продаж в Excel

Ниже на рисунке представлены исходные данные. Допустим по этим данным необходимо составить прогноз продаж на 2020-й год, не смотря на то что собранные статистические данные заканчиваются в декабре 2019-го года. Первым шагом является использование функции ПРЕДСКАЗ и расширение десезонализированных данных на очередные 12 месяцев. Формулы в таблицах:

Для прогнозирования будущих значений функция ПРЕДСКАЗ использует метод линейной регрессии. Функция содержит 3 аргумента:

1. X – в данном аргументе будет указан месяц, для которого следует получить текущее прогнозируемое значение.
2. Известные значения y – аргумент содержит десезонализированные данные столбца C.
3. Известные значения x – здесь указаны месяца соответствующие данным по продажам в столбце A.

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

Алгоритм прогнозирования объёма продаж в MS Excel