Как посчитать nps в excel

Индекс лояльности клиентов

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

В самом общем виде этот вопрос выглядит как: «Могли бы вы посоветовать нашу компанию друзьям?». Формулировки могут меняться, но об этом поговорим чуть позже. Ответом на вопрос служит оценка от 0 до 10.

Зачем измерять NPS?

Это один из главных показателей в маркетинге. Метрика позволяет:

• собрать большой объем мнений потребителей о компании за короткий промежуток времени;
• сегментировать клиентов по степени лояльности;
• обозначать KPI нужного уровня индекса;
• делать финансовые прогнозы.

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

Как посчитать NPS

Расчет NPS не требует крупной подготовки. Порядок действий следующий:

1. Формулировка вопроса.
2. Сбор формы для опроса с помощью профильных сервисов.
3. Запуск опроса и его проведение, по крайней мере, в течение месяца.
4. Выгрузка ответов и анализ результатов.
5. Усовершенствование опроса.

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

1. Будете ли вы рекомендовать нас друзьям?
2. Какова вероятность, что Вы будете рекомендовать нас друзьям?
3. Отметьте по шкале от 0 до 10, насколько велика вероятность, что вы будете рекомендовать нас своим знакомым? 0 — не стал бы рекомендовать, 10 — обязательно порекомендую.

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

Интерпретация результатов

Количество баллов

Обозначение потребителя

Описание портрета

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

Не испытывают неприязни к компании, однако при первой же возможности уйдут в другую.

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

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

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

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

Оценки и их расшифровки

• NPS > 50 — дела идут хорошо;
• NPS = от 30 до 50 — неплохо, но есть над чем работать;
• NPS !Если результаты не меняются с течением времени, нужно работать над его ростом!

Методы сбора оценок для определения NPS

Для онлай-продуктов оценка NPS выполняется с помощью email-опросов или напоминаний, сопровождающих получение продукта.

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

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

• опрос на планшетах, размещенных в офисе;
• онлайн-опрос на сайте;
• смс-рассылка;
• обратный звонок;
• заполнение офлайн-анкеты.

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

Выборка для NPS

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

ABCXYZ-анализ. Он позволит охватить все категории контрагентов.

Важный момент: опрос клиентов не стоит доверять лицам, которые с ними связаны, особенно если это влияет на KPI специалиста. Определять степень потребительской лояльности должны не заинтересованные в результате лица.

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

Как правильно применить полученный расчет NPS

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

Приемы, которые помогают повлиять на показатель NPS

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

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

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

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

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

Современные техники использования и повышения NPS

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

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

Дополнить основной вопрос можно рядом уточняющих, например таких: «Что повлияло на оценку?», «Что хотели бы улучшить?» и т.д. Выше мы говорили, что смысл определения NPS в том, что ему предстоит ответить на ОДИН короткий вопрос, однако уточнения помогают лучше понять ситуацию и изменить стратегию продвижения компании. Современные методики позволяют расширять опрос до 8 вопросов.

Иногда, помимо NPS, имеет смысл использовать и другие метрики.

В их числе: ASCI, CES, WoMI и др.

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

Чистая приведенная стоимость NPV (ЧПС) и внутренняя ставка доходности IRR (ВСД) в EXCEL

history 3 февраля 2015 г.

Группы статей
• Расчет будущей и приведенной стоимости
• Управление проектами

Рассчитаем Чистую приведенную стоимость и Внутреннюю норму доходности с помощью формул MS EXCEL.

Начнем с определения, точнее с определений.

Чистой приведённой стоимостью (Net present value, NPV) называют сумму дисконтированных значений потока платежей, приведённых к сегодняшнему дню (взято из Википедии). Или так: Чистая приведенная стоимость – это Текущая стоимость будущих денежных потоков инвестиционного проекта, рассчитанная с учетом дисконтирования, за вычетом инвестиций (сайт cfin. ru) Или так: Текущая стоимость ценной бумаги или инвестиционного проекта, определенная путем учета всех текущих и будущих поступлений и расходов при соответствующей ставке процента. (Экономика . Толковыйсловарь . — М . : " ИНФРА — М ", Издательство " ВесьМир ". Дж . Блэк .)

Примечание1 . Чистую приведённую стоимость также часто называют Чистой текущей стоимостью, Чистым дисконтированным доходом (ЧДД). Но, т.к. соответствующая функция MS EXCEL называется ЧПС() , то и мы будем придерживаться этой терминологии. Кроме того, термин Чистая Приведённая Стоимость (ЧПС) явно указывает на связь с Приведенной стоимостью .

Для наших целей (расчет в MS EXCEL) определим NPV так: Чистая приведённая стоимость — это сумма Приведенных стоимостей денежных потоков, представленных в виде платежей произвольной величины, осуществляемых через равные промежутки времени.

Совет : при первом знакомстве с понятием Чистой приведённой стоимости имеет смысл познакомиться с материалами статьи Приведенная стоимость .

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

Как было сказано, в MS EXCEL для вычисления Чистой приведённой стоимости используется функция ЧПС() (английский вариант — NPV()). В ее основе используется формула:

CFn – это денежный поток (денежная сумма) в период n. Всего количество периодов – N. Чтобы показать, является ли денежный поток доходом или расходом (инвестицией), он записывается с определенным знаком (+ для доходов, минус – для расходов). Величина денежного потока в определенные периоды может быть =0, что эквивалентно отсутствию денежного потока в определенный период (см. примечание2 ниже). i – это ставка дисконтирования за период (если задана годовая процентная ставка (пусть 10%), а период равен месяцу, то i = 10%/12).

Примечание2 . Т.к. денежный поток может присутствовать не в каждый период, то определение NPV можно уточнить: Чистая приведённая стоимость — это Приведенная стоимость денежных потоков, представленных в виде платежей произвольной величины, осуществляемых через промежутки времени, кратные определенному периоду (месяц, квартал или год) . Например, начальные инвестиции были сделаны в 1-м и 2-м квартале (указываются со знаком минус), в 3-м, 4-м и 7-м квартале денежных потоков не было, а в 5-6 и 9-м квартале поступила выручка по проекту (указываются со знаком плюс). Для этого случая NPV считается точно также, как и для регулярных платежей (суммы в 3-м, 4-м и 7-м квартале нужно указать =0).

Если сумма приведенных денежных потоков представляющих собой доходы (те, что со знаком +) больше, чем сумма приведенных денежных потоков представляющих собой инвестиции (расходы, со знаком минус), то NPV >0 (проект/ инвестиция окупается). В противном случае NPV Примечание3 . Естественно, все проекты индивидуальны и никакого единого правила для определения периода существовать не может. Управляющий проекта должен определить наиболее вероятные даты поступления сумм исходя из действующих реалий.

Определившись со сроками денежных потоков, для функции ЧПС() нужно найти наиболее короткий период между денежными потоками. Например, если в 1-й год поступления запланированы ежемесячно, а во 2-й поквартально, то период должен быть выбран равным 1 месяцу. Во втором году суммы денежных потоков в первый и второй месяц кварталов будут равны 0 (см. файл примера, лист NPV ).

В таблице NPV подсчитан двумя способами: через функцию ЧПС() и формулами (вычисление приведенной стоимости каждой суммы). Из таблицы видно, что уже первая сумма (инвестиция) дисконтирована (-1 000 000 превратился в -991 735,54). Предположим, что первая сумма (-1 000 000) была перечислена 31.01.2010г., значит ее приведенная стоимость (-991 735,54=-1 000 000/(1+10%/12)) рассчитана на 31.12.2009г. (без особой потери точности можно считать, что на 01.01.2010г.) Это означает, что все суммы приведены не на дату перечисления первой суммы, а на более ранний срок – на начало первого месяца (периода). Таким образом, в формуле предполагается, что первая и все последующие суммы выплачиваются в конце периода. Если требуется, чтобы все суммы были приведены на дату первой инвестиции, то ее не нужно включать в аргументы функции ЧПС() , а нужно просто прибавить к получившемуся результату (см. файл примера ). Сравнение 2-х вариантов дисконтирования приведено в файле примера , лист NPV:

О точности расчета ставки дисконтирования

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

Пусть имеется проект: срок реализации 10 лет, ставка дисконтирования 12%, период денежных потоков – 1 год.

NPV составил 1 070 283,07 (Дисконтировано на дату первого платежа). Т.к. срок проекта большой, то все понимают, что суммы в 4-10 году определены не точно, а с какой-то приемлемой точностью, скажем +/- 100 000,0. Таким образом, имеем 3 сценария: Базовый (указывается среднее (наиболее «вероятное») значение), Пессимистический (минус 100 000,0 от базового) и оптимистический (плюс 100 000,0 к базовому). Надо понимать, что если базовая сумма 700 000,0, то суммы 800 000,0 и 600 000,0 не менее точны. Посмотрим, как отреагирует NPV при изменении ставки дисконтирования на +/- 2% (от 10% до 14%):

Рассмотрим увеличение ставки на 2%. Понятно, что при увеличении ставки дисконтирования NPV снижается. Если сравнить диапазоны разброса NPV при 12% и 14%, то видно, что они пересекаются на 71%.

Много это или мало? Денежный поток в 4-6 годах предсказан с точностью 14% (100 000/700 000), что достаточно точно. Изменение ставки дисконтирования на 2% привело к уменьшению NPV на 16% (при сравнении с базовым вариантом). С учетом того, что диапазоны разброса NPV значительно пересекаются из-за точности определения сумм денежных доходов, увеличение на 2% ставки не оказало существенного влияния на NPV проекта (с учетом точности определения сумм денежных потоков). Конечно, это не может быть рекомендацией для всех проектов. Эти расчеты приведены для примера. Таким образом, с помощью вышеуказанного подхода руководитель проекта должен оценить затраты на дополнительные расчеты более точной ставки дисконтирования, и решить насколько они улучшат оценку NPV.

Совершенно другую ситуацию мы имеем для этого же проекта, если Ставка дисконтирования известна нам с меньшей точностью, скажем +/-3%, а будущие потоки известны с большей точностью +/- 50 000,0

Увеличение ставки дисконтирования на 3% привело к уменьшению NPV на 24% (при сравнении с базовым вариантом). Если сравнить диапазоны разброса NPV при 12% и 15%, то видно, что они пересекаются только на 23%.

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

После определения сумм и сроков денежных потоков, руководитель проекта может оценить, какую максимальную ставку дисконтирования сможет выдержать проект (критерий NPV = 0). В следующем разделе рассказывается про Внутреннюю норму доходности – IRR.

Внутренняя ставка доходности IRR (ВСД)

Внутренняя ставка доходности (англ. internal rate of return , IRR (ВСД)) — это ставка дисконтирования, при которой Чистая приведённая стоимость (NPV) равна 0. Также используется термин Внутренняя норма доходности (ВНД) (см. файл примера, лист IRR ).

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

Для расчета IRR используется функция ВСД() (английский вариант – IRR()). Эта функция тесно связана с функцией ЧПС() . Для одних и тех же денежных потоков (B5:B14) Ставка доходности, вычисляемая функцией ВСД() , всегда приводит к нулевой Чистой приведённой стоимости. Взаимосвязь функций отражена в следующей формуле: =ЧПС(ВСД(B5:B14);B5:B14)

Примечание4 . IRR можно рассчитать и без функции ВСД() : достаточно иметь функцию ЧПС() . Для этого нужно использовать инструмент Подбор параметра (поле «Установить в ячейке» должно ссылаться на формулу с ЧПС() , в поле «Значение» установите 0, поле «Изменяя значение ячейки» должно содержать ссылку на ячейку со ставкой).

Расчет NPV при постоянных денежных потоках с помощью функции ПС()

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

В этом случае все денежные потоки (диапазон В5:В13 , 9 одинаковых платежей) дисконтируются на дату первой (и единственной) суммы инвестиции, расположенной в ячейке В4 . Ставка дисконтирования расположена в ячейке В15 со знаком минус. В этом случае формула =B4+ЧПС(B15;B5:B13) дает тот же результат, что и = B4-ПС(B15;9;B13)

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

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

Функция ЧИСТНЗ() возвращает Чистую приведенную стоимость для денежных потоков, которые не обязательно являются периодическими. Расчеты выполняются по формуле:

Где, dn = дата n-й выплаты; d1 = дата 1-й выплаты (начальная дата); i – годовая ставка.

Принципиальным отличием от ЧПС() является то, что денежный поток привязан не к конкретным периодам, а к датам. Другое отличие: ставка у ЧИСТНЗ() всегда годовая, т.к. указана база 365 дней, а не за период, как у ЧПС() . Еще отличие от ЧПС() : все денежные потоки всегда дисконтируются на дату первого платежа.

В случае, когда платежи осуществляются регулярно можно сравнить вычисления функций ЧИСТНЗ() и ЧПС() . Эти функции возвращают несколько отличающиеся результаты. Для задачи из файла примера, Лист ЧИСТНЗ разница составила порядка 1% (период = 1 месяцу).

Это связано с тем, что у ЧИСТНЗ() длительность периода (месяц) «плавает» от месяца к месяцу. Даже если вместо месяца взять 30 дней, то в этом случае разница получается из-за того, что 12*30 не равно 365 дням в году (ставка у ЧПС() указывается за период, т.е. Годовая ставка/12). В случае, если денежные потоки осуществляются ежегодно на одну и туже дату, расчеты совпадают (если нет високосного года).

Внутренняя ставка доходности ЧИСТВНДОХ()

По аналогии с ЧПС() , у которой имеется родственная ей функция ВСД() , у ЧИСТНЗ() есть функция ЧИСТВНДОХ() , которая вычисляет годовую ставку дисконтирования, при которой ЧИСТНЗ() возвращает 0.

Расчеты в функции ЧИСТВНДОХ() производятся по формуле:

Где, Pi = i-я сумма денежного потока; di = дата i-й суммы; d1 = дата 1-й суммы (начальная дата, на которую дисконтируются все суммы).