Как рассчитать коэффициент вариации в Excel
Коэффициент вариации , часто обозначаемый как CV, представляет собой способ измерения степени разброса значений в наборе данных по отношению к среднему значению. Он рассчитывается как:
CV = σ / μ
σ = стандартное отклонение набора данных
μ = среднее значение набора данных
Проще говоря, коэффициент вариации — это просто отношение между стандартным отклонением и средним значением.
Когда используется коэффициент вариации?
Коэффициент вариации часто используется для сравнения вариации между двумя разными наборами данных.
В реальном мире он часто используется в финансах для сравнения среднего ожидаемого дохода от инвестиций с ожидаемым стандартным отклонением инвестиций. Это позволяет инвесторам сравнивать соотношение риска и доходности между инвестициями.
Например, предположим, что инвестор рассматривает возможность инвестирования в следующие два взаимных фонда:
Взаимный фонд A: среднее = 7%, стандартное отклонение = 12,4%
Взаимный фонд B: среднее = 5%, стандартное отклонение = 8,2%
При расчете коэффициента вариации для каждого фонда инвестор находит:
CV для взаимного фонда A = 12,4% / 7% = 1,77
CV для взаимного фонда B = 8,2% / 5% = 1,64
Поскольку взаимный фонд B имеет более низкий коэффициент вариации, он предлагает лучшую среднюю доходность по сравнению со стандартным отклонением.
Как рассчитать коэффициент вариации в Excel
В Excel нет встроенной формулы для расчета коэффициента вариации для набора данных, но, к счастью, его относительно легко вычислить, используя пару простых формул. В следующем примере показано, как рассчитать коэффициент вариации для заданного набора данных.
Предположим, у нас есть следующий набор данных, содержащий экзаменационные оценки 20 студентов:
Чтобы рассчитать коэффициент вариации для этого набора данных, нам нужно знать только два числа: среднее значение и стандартное отклонение. Их можно рассчитать по следующим формулам:
Среднее значение: =СРЕДНЕЕ(A2:A21)
Стандартное отклонение: =СТАНДОТКЛОН(A2:A21)
Чтобы вычислить коэффициент вариации, мы затем делим стандартное отклонение на среднее значение:
Коэффициент вариации оказывается равным 0,0864 .
Обратите внимание, что мы также могли бы использовать только одну формулу для расчета CV:
Это приводит к тому же CV 0,0864 .
Как рассчитывается коэффициент вариации и как его проанализировать
Коэффициент вариации, VAR или CV, – ключевой показатель в оценке риска проектов и доходности ценных бумаг. Он позволяет заранее проанализировать сразу два показателя, которые обладают меняющимися во времени значениями. Если показатель оказывается менее 0,1, направление инвестирования характеризуется низким уровнем риска. При показателе свыше 0,3 уровень риска необоснованно высок. Для расчета удобнее всего использовать функции СТАНДОТКЛОН и СРЗНАЧ табличного редактора Excel.
Для того чтобы сформировать качественный инвестиционный портфель, инвесторам порой приходится прибегать к оценке входящих в него активов, которые обладают разным уровнем риска и доходности. Для этой цели используется широко известный в инвестиционном анализе и эконометрике показатель.
Коэффициент вариации (Coefficient of variation – CV, VAR) – относительный финансовый показатель, который демонстрирует сравнение рассеивания значений двух случайных показателей, которые имеют разные единицы измерения относительно ожидаемого значения.
Справка! Поскольку коэффициент вариации позволяет получить сопоставимые результаты, то его применение оптимально в рамках портфельного анализа. В ней он позволяет эффективно объединить значения риска и доходности и вывести результирующее значение.
Coefficient of variation – показатель из числа относительных методов статистики, который, как NPV и IRR, применяется в рамках инвестиционного анализа. Он измеряется в процентах и может применяться для сравнения вариаций двух несвязанных между собой критериев. Его чаще всего используют финансовые и инвестиционные аналитики.
Справка! На базе коэффициента вариации оценивается так называемый «унифицированный риск» (unitized risk), поскольку он оценивает относительный разброс двух показателей по отношению к прогнозному значению.
Для чего используют показатель VAR:
- в целях сравнения двух разных показателей;
- для определения степени устойчивости прогнозных моделей (в основном по инвестициям и портфельному инвестированию);
- для осуществления XYZ-анализа.
Справка! XYZ-анализ – аналитический инструмент, в рамках которого продукция компании оценивается по двум параметрам: стабильность потребления и продаж.
Формула расчета коэффициента вариации
Суть расчета коэффициента вариации состоит в том, что по множеству значений рассчитывается сначала среднее квадратичное отклонение, а затем – среднее арифметическое, а после – найти их соотношение.
В общем виде формула расчета показателя VAR выглядит следующим образом:
CV = σ / t ср, где:
CV – коэффициент вариации;
σ – среднее квадратическое отклонение;
t – среднее арифметическое значение для случайной величины.
Формула расчета показателя VAR может принимать самые разнообразные интерпретации в зависимости от объекта оценки.
Для анализа проектов
Для оценки ценных бумаг
CV = σNPV / t срNPV
(по доходности ценной бумаги)
Важный момент! Очевидно, что применение представленных выше формул вручную, в особенности при наличии широкого круга значений, весьма затруднительно. Оттого для расчета применяется инструментарий табличного редактора Excel.
Значения показателя VAR в инвестиционном анализе
CV – показатель, который можно считать более универсальным, чем дисперсия или среднее квадратичное отклонение: на его основе можно сопоставлять риски и доходности двух и более активов, существенно отличающихся друг от друга.
Нормативного значения этого показателя не существует. Однако имеются некоторые опорные критерии, которые помогают при его анализе и интерпретации.
Степень риска – несущественна; вложения крайне желательны.
Средняя степень риска.
Высокая степень риска.
Модель крайне неустойчива, инвестиции не обоснованы.
Важный момент! Коэффициент CV имеет несколько недостатков – он не учитывает величины первоначальных вложений, предполагает симметричность разбросанных значений по отношению к среднему, а также не может использоваться для опционов, доходность которых может быть меньше 0. Оттого при наличии сомнений стоит дополнительно использовать показатели IRR и NPV.
Примеры расчета VAR в Excel
Расчет коэффициента вариации вручную − сложная и затяжная по времени процедура. Если выборка значительная, то расчёт по ней среднего квадратического отклонения вручную крайне чреват ошибками и неточностями.
Удобный способ определения VAR предлагает табличный редактор Excel. На его базе можно рассчитать:
- среднее квадратическое отклонение (функция СТАНДОТКЛОН);
- среднее арифметическое (функция СРЗНАЧ).
Для того чтобы разобраться в тонкостях использования CV, имеет смысл привести пример его расчета.
Пример расчета: оценка двух проектов с разной прибылью
Существует два бизнеса, которые на протяжении 5 лет демонстрируют разный финансовый результат. Для того чтобы сделать выбор между ними, инвестору стоит рассчитать коэффициент вариации.
Коэффициент вариации по 44-ФЗ. Пример расчёта, формула
Одной из ключевых стадий подготовки закупочной документации становится расчет начальной максимальной цены контракта (НМЦК). Законодательно предусмотрено несколько способов, с помощью которых можно производить расчеты. Чаще всего используется метод сопоставимых рыночных цен. При этом итоговая НМЦК должна определяться с учетом коэффициента вариации. Поэтому всем заказчикам необходимо понять, что включает в себя этот показатель и как его правильно определить.
Что такое коэффициент вариации
Размер НМЦК определяется еще на этапе планирования. Эта сумма должна быть отражена в плане и план-графике. Непосредственно перед подготовкой извещения она корректируется с учетом сложившейся на тот момент экономической обстановки. Вопросы, связанные с НМЦК рассматриваются в статье 22 44-ФЗ. Методики ее расчета описаны в Приказе Министерства экономики и развития № 567 от 02 октября 2013 года. В этом же документе приводятся правила определения коэффициента вариации.
Разработано несколько методик выявления НМЦК: нормативная, тарифная, проектно-сметная, затратная. Самым приоритетным считается метод сопоставимых рыночных цен. Именно его рекомендуется использовать при определении стартовой цены. Он предполагает сравнение коммерческих предложений, предоставляемых потенциальными поставщиками по запросу заказчика. Для проведения такого анализа и применяется коэффициент вариации. Он выражается в процентах.
Под коэффициентом вариации понимается мера относительного разброса предлагаемых цен. Он показывает, какую долю занимает средний разброс цен от среднего значения цены. Этот показатель может принимать следующие значения:
- Меньше 10%. В таком случае разница в ценах признается незначительной.
- От 10% до 20%. Разброс считается средним.
- От 20% до 33%. Разница признается значительной, но допустимой.
- Свыше 33%. Данные неоднородны. При расчете НМЦК не допускается использовать данные с коэффициентом вариации свыше 33%.
Для определения коэффициента разработана специальная формула. По ней легко подсчитать параметр, подставив соответствующие данные. Упростить себе задачу можно, используя калькуляторы, которые сегодня широко представлены в интернете.
Что делать, если коэффициент завышен
Если при расчете коэффициента вариации получилось значение меньше 33%, то выборка признается однородной. Следовательно, полученное значение можно использовать для определения НМЦК.
Если возникла такая ситуация, что значение коэффициента оказывается выше 33 процентов, тогда потребуется внесение корректировок в используемые данные. Для этого проводится дополнительное исследование рынка. Необходимо собрать коммерческие предложения от большего количества поставщиков и повторить расчет на основе новых данных. Если собрать дополнительные предложения не получается, можно воспользоваться сведениями из ранее заключенных договоров, которые хранятся в реестре контрактов.
В крайней ситуации, когда никак не получается добиться нужного коэффициента вариации можно исключить из выборки неподходящие предложения. Вы также можете попросить поставщика указать в своем предложении нужную вам сумму.
Правила расчета
Методика расчета коэффициента вариации прописана в приказе Минэкономразвития № 567. Согласно действующим нормам заказчик должен направить не менее пяти запросов коммерческих предложений потенциальным поставщикам. Для расчета используются не менее трех предложений, полностью соответствующих требованиям заказчика.
Стоит отметить, что приказ № 567 не является нормативным актом, следовательно, его исполнение не обязательно. За его нарушение никаких штрафных санкций не предусматривается. Однако во избежание спорных ситуаций заказчика рекомендуется пользоваться именно этими правилами расчета.
Для определения коэффициента вариации применяется следующая формула:
Среднеквадратичное отклонение позволяет определить разброс данных. Для его определения выбирают среднюю цену и меру разброса. Вычислить среднеквадратичное отклонение удается по следующей формуле:
В ситуациях, когда закупка включает в себя одновременно несколько позиций, расчет ведется по каждой из них. Это позволяет выявить товары с наибольшим разбросом цен.
Пример расчета
Предположим, что государственное учреждение проводит закупку принтеров для собственных нужд. Потенциальным поставщикам были отправлены соответствующие запросы. Было получено четыре коммерческих предложения цен: 2500 рублей, 2800 рублей, 2450 рублей и 2600 рублей.
В первую очередь необходимо рассчитать среднеарифметическое значение цены
Следующим шагом становится расчет среднеквадратичного отклонения
Осталось только рассчитать коэффициент вариации
Полученное значение коэффициента меньше 33%, следовательно, все собранные данные подходят для расчета стартовой цены контракта. Расчет НМЦК и коэффициента вариации оформляются в форме отчета, который становится обязательной частью закупочной документации.
Коэффициент вариации – важный инструмент, позволяющий оценить правильность ценовых предложений, полученных от поставщиков. Поэтому при составлении документации заказчикам необходимо учитывать правила расчета этого показателя и особенности его применения.
Коэффициент вариации
Онлайн калькулятор для расчета коэффициента вариации, коэффициент вариации – это величина равная отношению стандартного (среднеквадратичного) отклонения случайной величины к ее математическому ожиданию, выражается в процентах.
Формула коэффициента вариации:
В статистике принято, что:
— при коэффициенте вариации меньше 10%, степень рассеивания данных считается незначительной;
— если коэффициент от 10% до 20% — средней;
— больше 20% и меньше или равно 33% — значительной.
Совокупность считается однородной если коэффициент не превышает 33%, а если больше 33%, то – неоднородной.
Одно из основных применений коэффициента вариации — оценка инвестиционных рисков, например для расчет начальной максимальной цены контракта (НМЦК).
Пожалуйста напишите с чем связна такая низкая оценка: