Анализ чувствительности в Excel (пример таблицы данных)

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

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

Возможности таблиц данных представляют собой элементы анализа «что если» – его нередко проводят через Microsoft Excel. Это второе название анализа чувствительности.

Общие сведения

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

Базовые сведения о таблицах данных

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

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

Создание таблицы данных с одной переменной

Рассмотрим метод анализа на примере небольшого книжного магазина, где всего 100 книг в наличии. Часть из них можно продать дороже (50$), остальные обойдутся покупателям дешевле (20$). Рассчитан общий доход с продажи всех товаров – владелец решил, что продаст по высокой цене 60% книг. Необходимо выяснить, как вырастет выручка, если повысить цену на больший объем товара – 70% и так далее.

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

1. Выбираем свободную ячейку в отдалении от края листа и записываем в ней формулу: =Ячейка общей выручки. Например, если доход записан в ячейке С14 (указано случайное обозначение), необходимо написать так: =С14.
2. Записываем проценты объема товара в столбец слева от этой ячейки – не под ней, это очень важно.
3. Выделяем диапазон ячеек, где располагается столбец процентов и ссылка на общий доход.

4. Находим на вкладке «Данные» пункт «Анализ «что если»» и кликаем по нему – в открывшемся меню нужно выбрать опцию «Таблица данных».

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

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

Добавление формулы в таблицу данных с одной переменной

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

1. Вновь выделяем диапазон ячеек, но теперь он должен включать новую формулу.
2. Открываем меню анализа «что если» и выбираем «Таблицу данных».
3. Добавляем новую формулу в соответствующее поле по строчкам или по столбикам в зависимости от ориентации таблички.

Создание таблицы данных с двумя переменными

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

1. Записать варианты цены в одну строку со ссылкой на доход – каждой цене одна ячейка.
2. Выделить диапазон ячеек.

3. Открыть окно таблицы данных, как при составлении таблички с одной переменной – через вкладку «Данные» на панели инструментов.
4. Подставить в графу «Подставлять значения по столбцам в…» ячейку с начальной высокой ценой.
5. Добавить в графу «Подставлять значения по строкам в…» ячейку с начальным процентом продаж дорогих книг и нажать «ОК».

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

Ускорение вычислений для листов, содержащих таблицы данных

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

1. Открываем окно параметров, выбираем пункт «Формулы» в меню справа.
2. Выбираем пункт «Автоматически, кроме таблиц данных» в разделе «Вычисления в книге».

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

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

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

1. Функция «Подбор параметра» подойдет, если известен нужный результат, и требуется узнать входное значение переменной для получения такого результата.
2. «Поиск решения» – это надстройка для решения задач. Необходимо установить ограничения и указать на них, после чего система найдет ответ. Решение определяется путем изменения значений.
3. Анализ чувствительности можно провести с помощью диспетчера сценариев. Этот инструмент находится в меню анализа «что если» на вкладке «Данные». Он подставляет значения в несколько ячеек – количество может достигать 32-х. Диспетчер сравнивает эти значения, и пользователю не придется менять их вручную. Пример применения диспетчера сценариев:

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

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

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

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

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

Формула

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Анализ чувствительности инвестиций при помощи таблицы данных

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

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

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

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

Цель подобного анализа – разделить изменчивость величины на три компонента:

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

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

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

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

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

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

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

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

2. Подсчитаем рост спроса на товары в процентах: =ЕСЛИ(Рост/Итог 2 =0;Снижение/Итог 2; Рост/Итог 2).

3. Составим диаграмму для наглядности – выделяем диапазон ячеек и создаем гистограмму через вкладку «Вставка». В настройках нужно убрать заливку, это можно сделать через инструмент «Формат ряда данных».

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

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

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

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

Заключение

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