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

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

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

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

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

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

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

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

analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
1

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

analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
2

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

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

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

  1. Выбираем свободную ячейку в отдалении от края листа и записываем в ней формулу: =Ячейка общей выручки. Например, если доход записан в ячейке С14 (указано случайное обозначение), необходимо написать так: =С14.
  2. Записываем проценты объема товара в столбец слева от этой ячейки – не под ней, это очень важно.
  3. Выделяем диапазон ячеек, где располагается столбец процентов и ссылка на общий доход.
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
3
  1. Находим на вкладке «Данные» пункт «Анализ «что если»» и кликаем по нему – в открывшемся меню нужно выбрать опцию «Таблица данных».
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
4
  1. Откроется небольшое окно, где необходимо указать ячейку с процентом изначально проданных по высокой цене книг в графе «Подставлять значения по строкам в…». Этот шаг делают для того, чтобы делать перерасчет общей выручки с учетом возрастающего процента.
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
5

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

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

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

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

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

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

  1. Записать варианты цены в одну строку со ссылкой на доход – каждой цене одна ячейка.
  2. Выделить диапазон ячеек.
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
6
  1. Открыть окно таблицы данных, как при составлении таблички с одной переменной – через вкладку «Данные» на панели инструментов.
  2. Подставить в графу «Подставлять значения по столбцам в…» ячейку с начальной высокой ценой.
  3. Добавить в графу «Подставлять значения по строкам в…» ячейку с начальным процентом продаж дорогих книг и нажать «ОК».

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

analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
7

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

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

  1. Открываем окно параметров, выбираем пункт «Формулы» в меню справа.
  2. Выбираем пункт «Автоматически, кроме таблиц данных» в разделе «Вычисления в книге».
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
8
  1. Выполним пересчет результатов в табличке вручную. Для этого нужно выделить формулы и нажать клавишу F

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

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

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

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

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

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

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

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

Формула

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

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

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

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

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

analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
10
  1. Заполним таблицу, чтобы анализировать проект по ней.
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
11
  1. Вычисляем денежный поток с помощью функции СМЕЩ. На начальном этапе поток равен вложениям. Далее применяем формулу: =ЕСЛИ(СМЕЩ(Номер;1;)=2;СУММ(Приток 1:Отток 1); СУММ(Приток 1:Отток 1)+$B$5)
    Обозначения ячеек в формуле могут быть другими, это зависит от размещения таблицы. В конце прибавляется значение из начальных данных – ликвидационная стоимость.
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
12
  1. Определяем срок, за который проект окупится. Для начального периода используем эту формулу: =СУММЕСЛИ(G7:G17;”<0″). Диапазон ячеек – это столбец денежного потока. На дальнейших периодах применим эту формулу: =Начальный период+ЕСЛИ(Первый д.поток>0; Первый д.поток;0). Проект оказывается в точке безубыточности за 4 года.
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
13
  1. Создаем столбец для номеров тех периодов, когда проект окупается.
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
14
  1. Вычисляем рентабельность вложений. Необходимо составить выражение, где прибыль в конкретном отрезке времени делится на начальные вложения.
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
15
  1. Определяем коэффициент дисконтирования по этой формуле: =1/(1+Ставка диск.%) ^Номер.
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
16
  1. Вычислим приведенную стоимость с помощью умножения – денежный поток умножается на коэффициент дисконтирования.
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
17
  1. Рассчитаем PI (индекс рентабельности). Приведенная стоимость в отрезке времени делится на вложения в начале развития проекта.
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
18
  1. Определим внутреннюю норму прибыли с помощью функции ВСД: =ВСД(Диапазон денежного потока).

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

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

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

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

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

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

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

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

analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
19
  1. Находим во вкладке «Данные» инструмент «Анализ данных» и открываем его окно. В списке нужно выбрать однофакторный дисперсионный анализ.
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
20
  1. Заполняем строки диалогового окна. Входной интервал – все ячейки без учета шапки и номеров. Группируем по столбцам. Выводим результаты на новый лист.
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
21

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

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

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

analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
22
  1. Нужно выяснить, на какие товары больше всего вырос спрос в течение второго месяца. Составляем новую таблицу для определения роста и снижения спроса. Рост рассчитывается по этой формуле: =ЕСЛИ((Спрос 2-Спрос 1)>0; Спрос 2- Спрос 1;0). Формула снижения: =ЕСЛИ(Рост=0; Спрос 1- Спрос 2;0).
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
23
  1. Подсчитаем рост спроса на товары в процентах: =ЕСЛИ(Рост/Итог 2 =0;Снижение/Итог 2; Рост/Итог 2).
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
24
  1. Составим диаграмму для наглядности – выделяем диапазон ячеек и создаем гистограмму через вкладку «Вставка». В настройках нужно убрать заливку, это можно сделать через инструмент «Формат ряда данных».
analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
25

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

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

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

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

analiz-chuvstvitelnosti-v-excel-primer-tablicy-dannyh
28

Заключение

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

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

7 + 11 =

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

ehhu.ru w0o.ru 4-man.ru Adblock
detector