Применение методов математической статистики (дисперсионный анализ) и программного продукта (excel) в маркетинге

3.4. Виды моделей. Моделирование

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

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

Основные свойства детерминированного подхода к анализу:

1. построение детерминированной модели путем логического анализа;
2. наличие полной (жесткой) связи между показателями;
3. невозможность разделения результатов влияния одновременно действующих факторов, которые не поддаются объединению в одной модели;
4. изучение взаимосвязей в краткосрочном периоде.

Моделирование — процесс представления исследуемого показателя с факторами, которое передается в форме конкретного математического уравнения.

Различают четыре типа детерминированных моделей.

1. Аддитивные модели представляют собой алгебраическую сумму показателей и имеют вид:

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

2. Мультипликативные   модели   в   обобщенном   виде   могут быть представлены формулой:

Примером мультипликативной модели является двухфакторная модель объема производства продукции:

где Ч — среднесписочная численность работников;
CB — средняя выработка на одного работника.

3. Кратные модели:

Примером кратной модели служит показатель срока оборачиваемости товаров (в днях) Т_ОБ.Т:

где З_Т — средний запас товаров;О_Р — однодневный объем реализации.

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

Примерами таких моделей служат показатели затрат на 1 руб. товарной продукции, показатели рентабельности и др.

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

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

A = a + b;                 b = c + d;                  A = a + c + d или

A = a * b;                   b = c * d;                   A = a * c * d

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

Кратные модели преобразуются следующими способами:

1. Удлинение.

2. Формальное разложение.

b = b1 + b2 + b3

3. Расширение.

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

4. Сокращение.

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

Процесс   моделирования   сложный   и   ответственный   момент. От реальности и точности моделей зависят конечные результаты анализа.

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

В основе их построения лежат следующие принципы:

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

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

Бальжинов А.В., Михеева Е.В. Анализ и диагностика финансово-хозяйственной деятельности предприятия: Учебн.пособ., — Улан-Удэ, 2003.

Анализ чувствительности инвестиционного проекта в Excel

Метод анализа чувствительности в сфере инвестиций

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

1. Чистая приведенная стоимость – вычисляется путем вычитания размера вложения из объема доходов.
2. Внутренняя норма доходности/прибыли – указывает, какую прибыль требуется получить с вложения за год.
3. Коэффициент окупаемости – отношение всей прибыли к начальному вложению.
4. Дисконтированный индекс прибыли – указывает на эффективность инвестиции.

Формула

Чувствительность вложения можно вычислить с помощью этой формулы: Изменение выходного параметра в % / Изменение входного параметра в %.

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

1. Необходимо узнать результат при стандартных условиях.
2. Заменяем одну из переменных и следим за изменениями результата.
3. Вычисляем процентное изменение обоих параметров относительно установленных условий.
4. Вставляем полученные проценты в формулу и определяем чувствительность.

Пример анализа чувствительности инвестиционного проекта в Excel

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

10

1. Заполним таблицу, чтобы анализировать проект по ней.

11

2. Вычисляем денежный поток с помощью функции СМЕЩ. На начальном этапе поток равен вложениям. Далее применяем формулу: =ЕСЛИ(СМЕЩ(Номер;1;)=2;СУММ(Приток 1:Отток 1); СУММ(Приток 1:Отток 1)+$B$5)Обозначения ячеек в формуле могут быть другими, это зависит от размещения таблицы. В конце прибавляется значение из начальных данных – ликвидационная стоимость.

12

3. Определяем срок, за который проект окупится. Для начального периода используем эту формулу: =СУММЕСЛИ(G7:G17;»<0″). Диапазон ячеек – это столбец денежного потока. На дальнейших периодах применим эту формулу: =Начальный период+ЕСЛИ(Первый д.поток>0; Первый д.поток;0). Проект оказывается в точке безубыточности за 4 года.

13

4. Создаем столбец для номеров тех периодов, когда проект окупается.

14

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

15

6. Определяем коэффициент дисконтирования по этой формуле: =1/(1+Ставка диск.%) ^Номер.

16

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

17

8. Рассчитаем PI (индекс рентабельности). Приведенная стоимость в отрезке времени делится на вложения в начале развития проекта.

18

9. Определим внутреннюю норму прибыли с помощью функции ВСД: =ВСД(Диапазон денежного потока).

Дисперсионный анализ в Excel

Условно цель дисперсионного способа можно сконструировать так: вычленить из общей вариативности параметра 3 личные вариативности:

• 1 – определенную действием всякого из изучаемых значений;
• 2 – продиктованную связью меж исследуемыми значениями;
• 3 – случайную, продиктованную всеми неучтенными обстоятельствами.

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

Работа начинается с дизайна таблицы. Правила:

1. В любом столбце должны быть значения 1-го исследуемого фактора.
2. Столбцы расположить по возрастанию/убыванию величины исследуемого параметра.

Разглядим дисперсионный анализ в Excel на примере.

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

Внесем данные в таблицу Excel:

1. Открываем диалоговое окно нашего аналитического инструмента. В раскрывшемся перечне избираем «Однофакторный дисперсионный анализ» и жмем ОК.
2. В поле «Входной интервал» ввести ссылку на спектр ячеек, содержащихся во всех столбцах таблицы.
3. «Группирование» назначить по столбцам.
4. «Характеристики вывода» — новейший рабочий лист. Если необходимо указать выходной спектр на имеющемся листе, то переключатель ставим в положение «Выходной интервал» и ссылаемся на левую верхнюю ячейку спектра для выводимых данных. Размеры обусловятся автоматом.
5. Результаты анализа выводятся на отдельный лист (в нашем примере).

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

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

Дисперсионный анализ в Excel

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

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

Проведем дисперсионный анализ через надстройку Excel «Анализ данных». Если он не включен, его можно включить в настройках.
Исходная таблица должна подчиняться двум правилам: для каждого значения имеется один столбец, а данные в ней располагаются в порядке возрастания или убывания. Необходимо проверить влияние уровня образования на поведение в конфликте.

19

1. Найдите инструмент «Анализ данных» на вкладке «Данные» и откройте его окно. В списке необходимо выбрать однофакторный дисперсионный анализ.

20

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

двадцать один

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

Факторный анализ в Excel: пример

Проанализируем взаимосвязь данных в сфере продаж: необходимо выявить популярные и непопулярные товары. Исходная информация:

22

1. Нам необходимо выяснить, на какие товары спрос увеличился больше всего за второй месяц. Мы составляем новую таблицу для определения роста и падения спроса. Рост рассчитывается по следующей формуле: =ЕСЛИ((Спрос 2 — Спрос 1)>0; Спрос 2 — Спрос 1,0). Формула уменьшения: =SI(Рост=0; Спрос 1- Спрос 2;0).

23

1. Рассчитаем рост спроса на товары в процентах: =SI(Прирост/Результат 2 =0; Снижение/Результат 2; Рост/Результат 2).

24

1. Для наглядности построим график: выделим диапазон ячеек и создадим гистограмму через вкладку «Вставка». В настройках вам нужно удалить отступы, это можно сделать с помощью инструмента Format Data Series».

25

Двухфакторный дисперсионный анализ в Excel

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

26

1. Открываем «Анализ данных», в списке нужно найти двусторонний дисперсионный анализ без повторов.
2. Входной диапазон: ячейки, содержащие данные (без заголовка). Выводим результаты на новый лист и нажимаем «ОК».

27

Значение F больше F-критического, а это значит, что пол влияет на скорость реакции на звук.

28

Дисперсионный анализ в Excel

Условно цель дисперсионного метода можно сформулировать так: вычленить из общей вариативности параметра 3 частные вариативности:

• 1 – определенную действием каждого из изучаемых значений;
• 2 – продиктованную взаимосвязью между исследуемыми значениями;
• 3 – случайную, продиктованную всеми неучтенными обстоятельствами.

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

Работа начинается с оформления таблицы. Правила:

1. В каждом столбце должны быть значения одного исследуемого фактора.
2. Столбцы расположить по возрастанию/убыванию величины исследуемого параметра.

Рассмотрим дисперсионный анализ в Excel на примере.

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

Внесем данные в таблицу Excel:

1. Открываем диалоговое окно нашего аналитического инструмента. В раскрывшемся списке выбираем «Однофакторный дисперсионный анализ» и нажимаем ОК.
2. В поле «Входной интервал» ввести ссылку на диапазон ячеек, содержащихся во всех столбцах таблицы.
3. «Группирование» назначить по столбцам.
4. «Параметры вывода» – новый рабочий лист. Если нужно указать выходной диапазон на имеющемся листе, то переключатель ставим в положение «Выходной интервал» и ссылаемся на левую верхнюю ячейку диапазона для выводимых данных. Размеры определятся автоматически.
5. Результаты анализа выводятся на отдельный лист (в нашем примере).

Значимый параметр залит желтым цветом. Так как Р-Значение между группами больше 1, критерий Фишера нельзя считать значимым. Следовательно, поведение в конфликтной ситуации не зависит от уровня образования.



Определяем причины изменчивости исходных данных

По аналогии с однофакторным дисперсионным анализом общую изменчивость (разброс) значений Y относительно общего среднего (SST = Sum of Squares Total, общая сумма квадратов) определим как сумму нескольких компонентов, в данном случае 4-х:

SST=SSA+SSB+ SS взаим +SSE

• SSA – изменчивость, которую можно объяснить выбором метода обработки (фактор А)
• SSВ – изменчивость обусловленная выбором материала детали (фактор В)
• SS взаим – изменчивость обусловленная взаимодействием 2-х факторов
• SSE – ошибка модели (Error Sum of Squares).

SST и все 4 компонента вычисляются на основании имеющихся исходных данных:

Примечание : Вычисления SST и всех 4-х компонентов выполнены в файле примера .

Также в дисперсионном анализе используется понятие среднего квадрата отклонений (Mean Square) или сокращенно MS. Соответственно для SST имеем MST=SST/(N-1), где N= a*b*m является общим количеством измерений (18). Для других SS степени свободы приведены в таблице ниже.

Таким образом, MS имеет смысл средней изменчивости на 1 наблюдение (с некоторой поправкой). Эта поправка отражает тот факт, что MS должна вычисляться не делением SS на соответствующее количество наблюдений, а делением на число степеней свободы (degrees of freedom, DF). Например, чтобы вычислить MST, мы из N (общего количества наблюдений) должны вычесть 1, т.к. в выражении SST присутствует одно (1) среднее значение (аналогично тому, как мы делали при вычислении дисперсии ).

В случае двухфакторного дисперсионного анализа формируется 3 нулевых гипотезы .

• Гипотеза Н 0 взаим об отсутствии взаимодействия Фактора А и Фактора В. Альтернативная гипотеза Н 1взаим формулируется о наличии взаимодействия.
• гипотеза Н 01 заключается в том, что уровень фактора А (метод обработки поверхности) не влияет на измеренные значения Y (количество дефектов), т.е. средние значения выборок, относящиеся к различным уровням Фактора А не отличаются статистически значимо (их различие может быть объяснено лишь случайностью выборок).
• гипотеза Н 0 2 заключается в том, что уровень фактора В (Исходный материал) не влияет на измеренные значения Y (количество дефектов), т.е. средние значения выборок, относящиеся к различным уровням Фактора В не отличаются статистически значимо.

Сначала тестируют гипотезу об отсутствии взаимодействия между факторами. Мы можем отклонить Н 0 взаим в пользу Н 1взаим при заданном уровне значимости α (альфа), если вычисленное значение тестовой статистики F= MS взаим /MSE больше F критич альфа – значения случайной величины F имеющей распределение Фишера с (b-1)*(a-1) и a*b*(m-1) степенями свободы.

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

Чтобы проверить гипотезы необходимо вычислить значения тестовых статистик и сравнить их с соответствующими критическими значениями F крит ич , вычисленными для заданного уровня значимости альфа . Если вычисленное значение F 01 = MSА/MSE больше F 1крит ич , то нулевую гипотезу Н 0 1 об отсутствии влияния уровней Фактора А отклоняют. Аналогичные умозаключения справедливы и для Фактора В.

Проверить гипотезу Н 01 можно и через вычисление p -значения, которое представляет собой вероятность того, что случайная величина F 1 = MSА/MSE примет значение более F 01 . Далее p -значение сравнивают с уровнем значимости. Если p -значение менее уровня значимости, то нулевую гипотезу отклоняют. Действительно, если вычисленное значение F 01 получить маловероятно, то это ставит под сомнение справедливость того, что случайная величина F 1 = MSА/MSE имеет распределение Фишера с a -1 и a * b *( m -1) степенями свободы, а следовательно и саму нулевую гипотезу. В этом случае мы можем считать, что справедлива альтернативная гипотеза: уровни фактора А влияют на зависимую переменную Y.

Двухфакторный дисперсионный анализ в Excel

Показывает, как влияет два фактора на изменение значения случайной величины. Рассмотрим двухфакторный дисперсионный анализ в Excel на примере.

Задача. Группе мужчин и женщин предъявляли звук разной громкости: 1 – 10 дБ, 2 – 30 дБ, 3 – 50 дБ. Время ответа фиксировали в миллисекундах. Необходимо определить, влияет ли пол на реакцию; влияет ли громкость на реакцию.

1. Переходим на вкладку «Данные» – «Анализ данных» Выбираем из списка «Двухфакторный дисперсионный анализ без повторений».
2. Заполняем поля. В диапазон должны войти только числовые значения.
3. Результат анализа выводится на новый лист (как было задано).

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

Скачать пример факторного и дисперсионного анализа

скачать факторный анализ отклоненийскачать пример 2

Для фактора «Громкость»: 3,16

Для примера также прилагаем факторный анализ отклонений в маржинальном доходе.

Обозначения

Отдельные, заданные значения каждого фактора называются уровнями (
levels
) или испытаниями (
treatments
).

Уровни фактора А будем обозначать буквой j (j изменяется от 1 до
a
). Уровни фактора В будем обозначать буквой i (i изменяется от 1 до
b
). Каждой паре уровней факторов соответствует одна выборка, которая состоит из
m
измерений, каждое измерение будем обозначать буквой k (k от 1 до m). Таким образом, измеренные значения Y при уровне j фактора А и при уровне i фактора В будем обозначать y ijk . Всего выборок
a*b
.

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

Рассмотрим
двухфакторный дисперсионный анализ
при решении задачи.

Факторный анализ в Excel: пример

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

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

Рассмотрим на примере проведение факторного анализа. Допустим, нам известны продажи каких-либо товаров за последние 4 месяца. Необходимо проанализировать, какие наименования пользуются спросом, а какие нет.

1. Посмотрим, за счет, каких наименований произошел основной рост по итогам второго месяца. Если продажи какого-то товара выросли, положительная дельта – в столбец «Рост». Отрицательная – «Снижение». Формула в Excel для «роста»: =ЕСЛИ((C2-B2)>0;C2-B2;0), где С2-В2 – разница между 2 и 1 месяцем. Формула для «снижения»: =ЕСЛИ(J3=0;B2-C2;0), где J3 – ссылка на ячейку слева («Рост»). Во втором столбце – сумма предыдущего значения и предыдущего роста за вычетом текущего снижения.
2. Рассчитаем процент роста по каждому наименованию товара. Формула: =ЕСЛИ(J3/$I$11=0;-K3/$I$11;J3/$I$11). Где J3/$I$11 – отношение «роста» к итогу за 2 месяц, ;-K3/$I$11 – отношение «снижения» к итогу за 2 месяц.
3. Выделяем область данных для построения диаграммы. Переходим на вкладку «Вставка» – «Гистограмма».
4. Поработаем с подписями и цветами. Уберем накопительный итог через «Формат ряда данных» – «Заливка» («Нет заливки»). С помощью данного инструментария меняем цвет для «снижения» и «роста».

Теперь наглядно видно, продажи какого товара дают основной рост.

Однофакторный анализ

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

Однофакторный дисперсионный анализ может стать непараметрическим аналогом рангового метода Краскела-Уоллиса. Он разработан американскими математиком Уильямом Краскелом и экономистом Вильсоном Уоллисом в 1952 г. Этот критерий назначен для проверки нулевой гипотезы о равенстве эффектов влияния на исследуемые выборки с неизвестными, но равными средними величинами. При этом количество выборок должно быть больше двух.

Критерий Джонкхиера (Джонкхиера-Терпстра) был предложен независимо друг от друга нидерландским математиком Т. Дж. Терпстром в 1952 г. и британским психологом Е. Р. Джонкхиером в 1954 г. Его применяют тогда, когда заранее известно, что имеющиеся группы результатов упорядочены по росту влияния исследуемого фактора, который измеряют в порядковой шкале.

М — критерий Бартлетта, предложенный британским статистиком Маурисом Стивенсоном Бартлеттом в 1937 г., применяют для проверки нулевой гипотезы о равенстве дисперсий нескольких нормальных генеральных совокупностей, с которых взяты исследуемые выборки, в общем случае имеющие различные объемы (число каждой выборки должно быть не меньше четырех).

G — критерий Кохрена, который открыл американец Вильям Геммел Кохрен в 1941 г. Его используют для проверки нулевой гипотезы о равенстве дисперсий нормальных генеральных совокупностей по независимым выборкам равного объема.

Непараметрический критерий Левене, предложенный американским математиком Ховардом Левене в 1960 г., является альтернативой критерия Бартлетта в условиях, когда нет уверенности в том, что исследуемые выборки подчиняются нормальному распределению.

В 1974 г. американские статистики Мортон Б. Браун и Алан Б. Форсайт предложили тест (критерий Брауна-Форсайта), который несколько отличается от критерия Левене.

Двухфакторный анализ

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

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

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

Q — критерий Кохрена, предложенный В. Кохреном в 1937 г. Его используют в случаях, когда группы однородных субъектов подвергаются воздействиям, количество которых превышает два и для которых возможны два варианта отзывов — условно-отрицательный (0) и условно-положительный (1). Нулевая гипотеза состоит из равенства эффектов влияния. Двухфакторный дисперсионный анализ дает возможность определить существование эффектов обработки, однако не дает возможности установить, для каких именно столбцов существует этот эффект. При решении данной проблемы применяют метод множественных уравнений Шеффе для связанных выборок.

Как разработать шаблон для заполнения плановых данных

Проверь свои знания и приобрети новые

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

Как провести SWOT-анализ компании

Воспользуйтесь пошаговой инструкцией для проведения SWOT- анализа

2007–2019 ООО «Актион управление и финансы»

«Финансовый директор» — практический журнал по управлению финансами компании

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

Анализ чувствительности инвестиционного проекта в Excel

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

• ликвидационная стоимость – 0 руб.;
• отток денежных средств в каждом периоде – 20 тыс. руб. (со знаком «–»);
• прединвестиционные затраты – 400 тыс. руб. (в таблице нужно показать со знаком «минус»);
• приток денежных средств в каждом периоде – 90 тыс. руб.;
• ставка дисконтирования – 10 процентов.

Ниже приведем пошаговую инструкцию.

Шаг 1. Денежный поток

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

=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;;)

Формула для нашего примера (первый период):

=ЕСЛИ(СМЕЩ(A3;1;)=2;СУММ(C3:D3);СУММ(C3:D3)+’Исходные данные’!$B$5)

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

Шаг 2. Точка безубыточности (в денежном эквиваленте)

Чтобы узнать, при каком объеме производства доходы организации будут равны ее расходам, используем функцию «СУММЕСЛИ». Но формула подойдет только для нулевого периода:

=СУММЕСЛИ(E2:E12;»<0″)

Далее необходимо будет применить другую формулу. Например, для первого периода в нашем примере ее можно представить так:

=F2+ЕСЛИ(E3>0;E3;0)

Шаг 3. Срок окупаемости

Проект из нашего примера начнет приносить прибыль компании в шестом периоде. Установить это удалось с помощью функции «ЕСЛИ»:

=ЕСЛИ(F8>=0;$A8;»»)

Шаг 4. Рентабельность инвестиций (ROR)

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

=СУММ($F$2;F3)/-$F$2

Знак доллара в формуле нужен для того, чтобы сделать ссылку абсолютной.

Шаг 5. Коэффициент дисконтирования

Рассчитаем коэффициент без учета дат платежей:

 =1/(1+’Исходные данные’!$B$1)^A2, где:

• ‘Исходные данные’!$B$1 – ставка дисконтирования;
• А2 – период;

Шаг 6. Приведенная (дисконтированная) стоимость

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

=E2*I2 (для нулевого периода)

Шаг 7. Индекс рентабельности (PI)

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

=J3/-$E$2

Шаг 8. Внутренняя норма доходности (IRR)

Для вычисления будем использовать формулу «ВСД», так как не учитываем даты платежей. В противном случае мы бы воспользовались функцией «ЧИСТВНДОХ». Для расчета нужно знать лишь денежный поток.

=ВСД(E2:E12)

Шаг 9. Прочие показатели

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

=(C13+СУММ(D2:D12))/B15

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

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

=ЕСЛИ(J13<0;»не окупается»;(ПОИСКПОЗ(ВПР(0;F2:F12;1;ИСТИНА);F2:F12))-((ИНДЕКС(F2:G12;ПОИСКПОЗ(ВПР(0;F2:F12;1;ИСТИНА);F2:F12)+1;1))/((ABS(ВПР(0;F2:F12;1;ИСТИНА)))+ИНДЕКС(F2:F12;ПОИСКПОЗ(ВПР(0;F2:F12;1;ИСТИНА);F2:F12)+1;1))))

Шаг 10. Вывод

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

Таблица. Итоги анализа чувствительности проекта

Срок проекта	10 лет
Чистый дисконтированный доход	30 120 руб.
Дисконтированный индекс рентабельности	1,08
Рентабельность инвестиций	-43%
Внутренняя норма доходности	12%
Срок окупаемости	5,7 лет

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