Расчет коэффициента вариации в Microsoft Excel

Содержание

Как рассчитать коэффициент вариации и другие статистические величины в excel

Расчет коэффициента вариации в Microsoft Excel

Добавил:

Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.

Вуз:

Предмет:

Файл:

6. показатели вариации.doc

Скачиваний:

199

Добавлен:

Размер:

246.78 Кб

Скачать

Калькулятор

Сервис бесплатной оценки стоимости работы

  1. Заполните заявку. Специалисты рассчитают стоимость вашей работы
  2. Расчет стоимости придет на почту и по СМС

Номер вашей заявки

Прямо сейчас на почту придет автоматическое письмо-подтверждение с информацией о заявке.

Оформить еще одну заявку

Вычисление коэффициента вариации

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

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

Шаг 1: расчет стандартного отклонения

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

Для расчета стандартного отклонения используется функция СТАНДОТКЛОН.

Начиная с версии Excel 2010 она разделена, в зависимости от того, по генеральной совокупности происходит вычисление или по выборке, на два отдельных варианта: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В.

Синтаксис данных функций выглядит соответствующим образом:

  1. Для того, чтобы рассчитать стандартное отклонение, выделяем любую свободную ячейку на листе, которая удобна вам для того, чтобы выводить в неё результаты расчетов. Щелкаем по кнопке «Вставить функцию». Она имеет внешний вид пиктограммы и расположена слева от строки формул.
  2. Выполняется активация Мастера функций, который запускается в виде отдельного окна с перечнем аргументов. Переходим в категорию «Статистические» или «Полный алфавитный перечень». Выбираем наименование «СТАНДОТКЛОН.Г» или «СТАНДОТКЛОН.В», в зависимости от того, по генеральной совокупности или по выборке следует произвести расчет. Жмем на кнопку «OK».
  3. Открывается окно аргументов данной функции. Оно может иметь от 1 до 255 полей, в которых могут содержаться, как конкретные числа, так и ссылки на ячейки или диапазоны. Ставим курсор в поле «Число1». Мышью выделяем на листе тот диапазон значений, который нужно обработать. Если таких областей несколько и они не смежные между собой, то координаты следующей указываем в поле «Число2» и т.д. Когда все нужные данные введены, жмем на кнопку «OK»
  4. В предварительно выделенной ячейке отображается итог расчета выбранного вида стандартного отклонения.

Урок: Формула среднего квадратичного отклонения в Excel

Шаг 2: расчет среднего арифметического

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

  1. Выделяем на листе ячейку для вывода результата. Жмем на уже знакомую нам кнопку «Вставить функцию».
  2. В статистической категории Мастера функций ищем наименование «СРЗНАЧ». После его выделения жмем на кнопку «OK».
  3. Запускается окно аргументов СРЗНАЧ. Аргументы полностью идентичны тем, что и у операторов группы СТАНДОТКЛОН. То есть, в их качестве могут выступать как отдельные числовые величины, так и ссылки. Устанавливаем курсор в поле «Число1». Так же, как и в предыдущем случае, выделяем на листе нужную нам совокупность ячеек. После того, как их координаты были занесены в поле окна аргументов, жмем на кнопку «OK».
  4. Результат вычисления среднего арифметического выводится в ту ячейку, которая была выделена перед открытием Мастера функций.

Урок: Как посчитать среднее значение в Excel

Шаг 3: нахождение коэффициента вариации

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

  1. Выделяем ячейку, в которую будет выводиться результат. Прежде всего, нужно учесть, что коэффициент вариации является процентным значением. В связи с этим следует поменять формат ячейки на соответствующий. Это можно сделать после её выделения, находясь во вкладке «». Кликаем по полю формата на ленте в блоке инструментов «Число». Из раскрывшегося списка вариантов выбираем «Процентный». После этих действий формат у элемента будет соответствующий.
  2. Снова возвращаемся к ячейке для вывода результата. Активируем её двойным щелчком левой кнопки мыши. Ставим в ней знак «=». Выделяем элемент, в котором расположен итог вычисления стандартного отклонения. Кликаем по кнопке «разделить» (/) на клавиатуре. Далее выделяем ячейку, в которой располагается среднее арифметическое заданного числового ряда. Для того, чтобы произвести расчет и вывести значение, щёлкаем по кнопке Enter на клавиатуре.
  3. Как видим, результат расчета выведен на экран.

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

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

    Вместо наименования «Диапазон значений» вставляем реальные координаты области, в которой размещен исследуемый числовой ряд. Это можно сделать простым выделением данного диапазона. Вместо оператора СТАНДОТКЛОН.В, если пользователь считает нужным, можно применять функцию СТАНДОТКЛОН.Г.

  2. После этого, чтобы рассчитать значение и показать результат на экране монитора, щелкаем по кнопке Enter.

Существует условное разграничение. Считается, что если показатель коэффициента вариации менее 33%, то совокупность чисел однородная. В обратном случае её принято характеризовать, как неоднородную.

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

К сожалению, в приложении пока не существует функции, которая высчитывала бы этот показатель в одно действие, но при помощи операторов СТАНДОТКЛОН и СРЗНАЧ эта задача очень упрощается.

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

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Источник: //alfa-service42.com/tehnologii/raschet-pokazateley-variacii-v-excel.html

Коэффициент вариации – что это, что показывает, формула расчета в Excel, среднеквадратичное и стандартное отклонение

Расчет коэффициента вариации в Microsoft Excel

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

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

Расчет стандартного отклонения

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

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

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

Этот документ сохраняете.

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

Вписываете знак «=» и прописываете далее формулу. Знак равенства обязателен. Иначе программа не посчитает предложенные данные. Формула вводится без пробелов.

Утилита выдаст названия нескольких формул. Выбираете «СТАНДОТКЛОН». Это формула вычисления стандартного отклонения. Существует два вида расчета:

  • с вычислением по выборке;
  • с вычислением по генеральной совокупности.

Выбрав одну из них, указываете диапазон данных. Вся введенная формула будет выглядеть так: «=СТАНДОТКЛОН (В2: В5)».

Затем кликаете по кнопке «Enter». Полученные данные появятся в отмеченном пункте.

Расчет среднего арифметического

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

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

Расчет коэффициента вариации

Формула расчета коэффициента вариации:

V= S/X, где S – это стандартное отклонение, а X – среднее значение.

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

Для этого открываете Excel, заполняем два поля, куда следует вписать полученные числа стандартного отклонения и среднего значения.

Теперь выделяете ячейку, которую отвели под число для вычисления вариации. Открываете вкладку «», если она не открыта. Кликаете по инструменту «Число». Выбираете процентный формат.

Переходите к отмеченной ячейке и кликаете по ней дважды. Затем вводите знак равенства и выделяете пункт, куда вписан итог стандартного отклонения. Затем кликаете на клавиатуре по кнопке «слэш» или «разделить» (выглядит так: «/»). Выделяете пункт, куда вписано среднее арифметическое, и кликаете по кнопке «Enter». Должно получиться так:
  Excel для чайников – изменение столбцов, строк и ячеек

А вот и результат после нажатия «Enter»:

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

Среднеквадратическое отклонение

Среднеквадратичное отклонение в Excel решается с помощью двух формул:

Простыми словами, извлекается корень из дисперсии. Как вычислить дисперсию рассмотрено ниже.

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

Коэффициент осциляции

Соотношением размаха вариации к среднему – называется коэффициентом осциляции. Готовых формул в Экселе нет, поэтому нужно компоновать несколько функций в одну.

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

Дисперсия

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

Переменные принимают такие значения:

В Excel есть две функции, которые определяют дисперсию:

  • Дисп.Г – используется относительно небольших выборок.
  • Дисп.В – вычисление несмещенной дисперсии.

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

Максимум и минимум

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

Чтобы вычислить максимум, выделяете весь диапазон необходимых чисел в таблице и отдельную ячейку, затем кликаете по значку «Σ» или «Автосумма». В выпавшем окне выбираете «Максимум» и, нажав кнопку «Enter» получаете нужное значение.

  Excel для чайников – основы работы с ячейками

Тоже самое делаете, чтобы получить минимум. Только выбираете функцию «Минимум».

Источник: //composs.ru/koefficient-variacii-v-excel/

Коэффициент вариации по 44-ФЗ. Пример расчёта, формула

Расчет коэффициента вариации в Microsoft Excel

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

Чаще всего используется метод сопоставимых рыночных цен. При этом итоговая НМЦК должна определяться с учетом коэффициента вариации.

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

Что такое коэффициент вариации

Размер НМЦК определяется еще на этапе планирования. Эта сумма должна быть отражена в плане и план-графике. Непосредственно перед подготовкой извещения она корректируется с учетом сложившейся на тот момент экономической обстановки.

Вопросы, связанные с НМЦК рассматриваются в статье 22 44-ФЗ. Методики ее расчета описаны в Приказе Министерства экономики и развития № 567 от 02 октября 2013 года. В этом же документе приводятся правила определения коэффициента вариации.

Разработано несколько методик выявления НМЦК: нормативная, тарифная, проектно-сметная, затратная. Самым приоритетным считается метод сопоставимых рыночных цен.

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

Для проведения такого анализа и применяется коэффициент вариации. Он выражается в процентах.

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

  1. Меньше 10%. В таком случае разница в ценах признается незначительной.
  2. От 10% до 20%. Разброс считается средним.
  3. От 20% до 33%. Разница признается значительной, но допустимой.
  4. Свыше 33%. Данные неоднородны. При расчете НМЦК не допускается использовать данные с коэффициентом вариации свыше 33%.

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

Что делать, если коэффициент завышен

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

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

Необходимо собрать коммерческие предложения от большего количества поставщиков и повторить расчет на основе новых данных.

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

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

Правила расчета

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

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

Для определения коэффициента вариации применяется следующая формула:

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

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

Пример расчета

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

В первую очередь необходимо рассчитать среднеарифметическое значение цены

Следующим шагом становится расчет среднеквадратичного отклонения

Осталось только рассчитать коэффициент вариации

Полученное значение коэффициента меньше 33%, следовательно, все собранные данные подходят для расчета стартовой цены контракта. Расчет НМЦК и коэффициента вариации оформляются в форме отчета, который становится обязательной частью закупочной документации. 

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

Источник: //GoszakupkiRF.ru/poleznye-stati/219-koeffitsient-variatsii

XYZ анализ – коэффициент вариации – подготовка данных к прогнозу

Расчет коэффициента вариации в Microsoft Excel

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

Что такое коэффициент вариации и для чего он нужен?

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

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

Коэффициент вариации принято измерять в процентном выражении и отображать с его помощью однородность временного ряда.

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

Так, если вы видите, что значение коэффициента равно 0%, то с уверенностью заявляйте о том, что ряд является однородным, а значит, все значения в нём равны один с другим.

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

Как найти среднее квадратичное отклонение?

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

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

Рассчитываем коэффициент в Экселе

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

Для того чтобы рассчитать показатель вариации в Excel, необходимо вспомнить школьный курс математики и разделить стандартное отклонение на среднее значение выборки. То есть на деле формула выглядит следующим образом – СТАНДОТКЛОН(заданный диапазон данных)/СРЗНАЧ(заданный диапазон данных). Ввести эту формулу необходимо в ту ячейку Excel, в которой вы хотите получить нужный вам расчёт.

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

  1. Откройте вкладку «».
  2. Найдите в ней категорию «Формат ячеек » и выберите необходимый параметр.

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

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

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

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

Из данной статьи вы узнаете:

  • ;
  • Как сделать XYZ анализ в Excel;
  • Применение XYZ анализа при подготовке данных к прогнозу.

Как рассчитать коэффициент вариации в Excel

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

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

Если коэффициент равен 0%, то ряд абсолютно однородный, т.е. все значения между собой равны.

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

Например:

Однородный ряд
Неоднородный ряд

Как сделать XYZ анализ?

Теперь сегментируем наши коэффициенты вариации и присваиваем каждому одну из 3-х букв X Y и Z

  • X – для рядов с коэффициентом вариации от 0% до 10%
  • Y – для рядов с коэффициентом вариации от 10% до 25%
  • Z – для рядов с коэффициентом вариации от 25% и больше

Вводим в ячейку Excel формулу

ЕСЛИ(N3

Источник: //comuedu.ru/solutions/xyz-analysis-coefficient-of-variation-preparation-of-data-for-the-forecast.html

Коэффициент вариации

Расчет коэффициента вариации в Microsoft Excel

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

Используйте пошаговые руководства:

Коэффициент вариации (Coefficient of variation, или CV) – это мера относительного разброса случайной величины. Он показывает, какую долю составляет средний разброс случайной величины от среднего значения этой величины.

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

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

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

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

Аналитики используют коэффициент:

  1. Для определения устойчивости прогнозной модели.
  2. Для сравнения нескольких прогнозных моделей (в основном инвестиционных) с разными абсолютными уровнями дохода и риска.
  3. Для проведения XYZ анализа.

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

Коэффициент вариации рассчитывается по формуле:

где CV – коэфф вариации,

σ – среднеквадратическое отклонение случайной величины,

tср – среднее значение случайной величины.

Формула коэффициента вариации для инвестиционных финансовых моделей:

где NPV – чистый приведенный доход.

Формула коэффициента вариации для инвестиций в ценные бумаги:

где:%год – доходность по ценной бумаге в % годовых.

Коэффициент вариации в Excel

В Эксель можно посчитать коэффициент вариации с использованием формулы:

=СТАНДОТКЛОНПА(диапазон значений)/СРЗНАЧ (диапазон значений)

Или с использованием встроенного пакета «Анализ данных».

Анализ коэффициента вариации

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

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

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

Принято считать, что, если коэффициент вариации модели:

  • меньше 10%, то степень риска проекта является незначительной,
  • от 10% до 20% – средней,
  • больше 20%  – значительной,
  • если значение коэффициента вариации больше 33%, то финансовая модель считается неоднородной, неустойчивой. По ней нельзя принимать объективных инвестиционных решений

Примеры расчета коэффициента вариации в Excel

Пример 1

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

Первый – открытие сети розничных точек для торговли ювелирными изделиями в Москве и Санкт-Петербурге.

Второй – открытие сети розничных точек по всей России в городах-миллионниках.

Финансовый аналитик предприятия составил финансовые модели обоих проектов в Excel и по модели Монте-Карло сделал по 5000 прогонов для NPV в каждом проекте (см. также, как создать наглядную финансовую модель в Excel). Далее с помощью пакета анализа «Анализ данных» получил следующие статистические показатели (см. таблицы 1 и 2).

Таблица 1. Показатели по проекту 1

Среднее14,05
Дисперсия выборки1,72

Таблица 2. Показатели по проекту 1

Среднее25,23
Дисперсия выборки6,30

Средний предполагаемый NPV от Проекта 1 составит 14,05 тысяч долларов, дисперсия (она же среднее квадратическое отклонение) будет равна 1,72 тысяч долларов.

Коэффициент вариации для первого проекта равен:

CV = 1.72/14.05 = 12%

Проект признается среднерисковым.

Средний предполагаемый NPV от Проекта 2 составит 25,23 тысяч долларов, дисперсия будет равна 6,30 тысяч долларов.

Коэффициент вариации для второго проекта составит:

CV = 6,30/25,23 = 24,97%

Проект признается высокорисковым.

Если сравнивать проекты 1 и 2 по коэффициенту вариации, то следует выбрать Проект 1, так как соотношение доход/риск у него лучше.

Пример 2

Компания «Сигма» проводит XYZ анализ товарного ассортимента по показателю изменчивости продаж. Продуктовая линейка компании представлена пятью товарами: А, В, С, D и E.

Имеется помесячная статистика продаж за последний год по каждому товару (см. рисунок). На практике лучше иметь статистику за период более трех лет/

Рисунок. Статистика продаж за последний год по каждому товару

Финансовый аналитик компании рассчитал коэффициент вариации для каждого товара

CVа = СТАНДОТКЛОНПА(B2:В13)/СРЗНАЧ (В2:В13) = 30%

CVb = 6%

CVc = 12%

CVd = 4%

CVe = 38%

В компании установлены следующие интервалы для групп XYZ:

X – 0–10%,

Y – 11–30%,

Z – 31–100%.

Значит, товары B и D относятся к категории X. Спрос на них постоянный, запасы на складах по ним должны быть под пристальным контролем и постоянно пополняться.

Товары A и C относятся к категории Y. Спрос на них отклоняется в пределах 30% от месяца к месяцу. Возможно, имеет место сезонность спроса. Нужно глубже анализировать статистику продаж и выработать оптимальную политику по остаткам на складах для данной группы.

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

Выводы

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

  1. Объемы первоначального инвестирования.
  2. Возможную асимметричность распределения. При расчете коэффициента вариации предполагается, что разброс значений случайной величины расположен симметрично к среднему (часто по нормальному распределению). Но это не всегда соответствует действительности. Например, для опционов, доходность которых не может быть ниже нуля, имеет место асимметрия распределения, и анализировать коэффициент вариации по ним нужно с оглядкой на другие методы статистического анализа.
  3. Инвестиционную политику субъекта инвестирования.
  4. Другие нечисловые факторы.

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

Источник: //fd.ru/articles/158998-koeffitsient-variatsii-17-m12

Коэффициент вариации в статистике: примеры расчета

Расчет коэффициента вариации в Microsoft Excel

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

  • Показатели описательной статистики
  • Среднее арифметическое
  • Стандартное отклонение
  • Коэффициент вариации
  • Расчёты в Microsoft Ecxel 2016

Показатели описательной статистики

Существует несколько показателей, которые использует описательная статистика.

Среднее арифметическое

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

168, 171, 175, 177, 179, 187, 174, 176, 179, 169.

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

  • Ширина интервала, куда попадает рост всех студентов, – 18 см.
  • В распределении рост наиболее близок к середине этого интервала.
  • Встречаются и исключения, которые наиболее близко расположены к верхней или нижней границе интервала.

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

Для этой цели достаточно привести всего два, которые в статистике называются параметрами распределения. Это среднеарифметическое и стандартное отклонение от среднего арифметического.

Если обратиться к росту студентов, то формула будет выглядеть следующим образом:

Среднеарифметическое значение роста студентов = (Сумма всех значений роста студентов) / (Число студентов, участвовавших в измерении)

Если свести все к строгим математическим терминам, то определение среднего арифметического (обозначается греческой буквой – μ («мю»)) будет звучать так:

Среднее арифметическое – это отношение суммы всех значений одного признака для всех членов совокупности (X) к числу всех членов совокупности (N).

Если применить эту формулу к нашим измерениям, то получаем, что μ для роста студентов в группе 175,5 см.

Стандартное отклонение

Если присмотреться к росту студентов, который мы измерили в предыдущем примере, то понятно, что рост каждого на сколько-то отличается от вычисленного среднего (175,5 см). Для полноты описания нужно понять, какой является разница между средним ростом каждого студента и средним значением.

На первом этапе вычислим параметр дисперсии. Дисперсия в статистике (обозначается σ2 (сигма в квадрате)) – это отношение суммы квадратов разности среднего арифметического (μ) и значения члена ряда (Х) к числу всех членов совокупности (N). В виде формулы это рассчитывается понятнее:

Значения, которые мы получим в результате вычислений по этой формуле, мы будем представлять в виде квадрата величины (в нашем случае – квадратные сантиметры). Характеризовать рост в сантиметрах квадратными сантиметрами, согласитесь, нелепо. Поэтому мы можем исправить, точнее, упростить это выражение и получим среднеквадратичное отклонение формулу и расчёт, пример:

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

Получается, что наша группа студентов исчисляется по росту таким образом: 175,50±5,25 см.

Коэффициент вариации

Среднее квадратичное отклонение хорошо работает с рядами, в которых разброс значений не очень велик (это хорошо прослеживалось на примере роста, где интервал был всего 18 см).

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

в процентах, относительно средней величины).

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

  • Квадратический коэффициент вариации.
  • Размах вариации.
  • Коэффициент осцилляции.

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

Для нашего примера со студентами, определить Vσ несложно — он будет равен 3,18%. Основная закономерность – чем больше будет изменяться значение коэффициента, тем больше разброс вокруг среднего значения и тем менее однородна выборка.

Преимущество коэффициента вариации в том, что он показывает однородность значений (асимметрия) в ряду наших измерений, кроме того, на него не оказывают влияния масштаб и единицы измерения. Эти факторы делают коэффициент вариации особенно популярным в биомедицинских исследованиях. Будет считаться, что эксцесс значения Vσ =33% отделяет однородные выборки от неоднородных.

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

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

Расчёты в Microsoft Ecxel 2016

Можно рассчитать описанные в статье статистические показатели в программе Microsoft Excel 2016, через специальные функции в программе. Необходимая информация приведена в таблице:

Наименование показателяРасчёт в Excel 2016*
Среднее арифметическое=СРГАРМ(A1:A10)
Дисперсия=ДИСП.В(A1:A10)
Среднеквадратический показатель=СТАНДОТКЛОН.В(A1:A10)
Коэффициент вариации=СТАНДОТКЛОН.Г(A1:A10)/СРЗНАЧ(A1:A10)
Коэффициент осцилляции=(МАКС(A1:A10)-МИН(A1:A10))/СРЗНАЧ(A1:A10)

* — в таблице указан диапазон A1:A10 для примера, при расчётах нужно указать требуемый диапазон.

Итак, обобщим информацию:

  1. Среднее арифметическое – это значение, позволяющее найти среднее значение показателя в ряду данных.
  2. Дисперсия – это среднее значение отклонений возведенное в квадрат.
  3. Стандартное отклонение (среднеквадратичное отклонение) – это корень квадратный из дисперсии, для приведения единиц измерения к одинаковым со среднеарифметическим.
  4. Коэффициент вариации – значение отклонений от среднего, выраженное в относительных величинах (%).

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

Источник: //1001student.ru/ekonomika/koeffitsient-variatsii-v-statistike-primery-rascheta.html

Поделиться:
Нет комментариев

    Добавить комментарий

    Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.