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

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

 

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

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

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

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

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

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

а

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

 

 

два

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

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

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

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

 

 

3

1. Находим во вкладке «Данные» пункт «Что если анализ» и нажимаем на него; в открывшемся меню выберите опцию «Таблица данных».

 

 

4

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

 

 

пять

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

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

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

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

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

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

1. Пишите варианты цен одной строкой со ссылкой на доход — по одной ячейке на каждую цену.
2. Выберите диапазон ячеек.

 

 

6

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

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

 

 

7

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

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

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

 

 

8

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

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

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

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

 

 

9

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

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

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

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

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

Формула

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

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

1. Вам нужно знать результат при стандартных условиях.
2. Заменяем одну из переменных и следим за изменением результата.
3. Рассчитывали процентное изменение обоих параметров по отношению к установленным условиям.
4. Подставляем полученные проценты в формулу и определяем чувствительность.

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

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

 

 

10

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

 

 

11

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

 

 

12

1. Определяем период, в течение которого проект будет приносить плоды. Для начального периода используйте следующую формулу: =СУММЕСЛИ(G7:G17;»0; Первый поток d.;0). Проект выходит на безубыточность через 4 года.

 

 

13

1. Мы создаем столбец для номеров тех периодов, когда проект стоит.

 

 

четырнадцать

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

 

 

15

1. Мы определяем коэффициент дисконтирования по следующей формуле: =1/(1+Ставка диска.%) ^Число.

 

 

шестнадцать

1. Мы рассчитываем текущую стоимость путем умножения: денежный поток умножается на коэффициент дисконтирования.

 

 

17

1. Рассчитаем PI (индекс рентабельности). Текущая стоимость с течением времени делится на инвестиции в начале проекта.

 

 

18

1. Определим внутреннюю норму доходности с помощью функции IRR: = IRR (диапазон денежного потока).

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

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

Факторный и дисперсионный анализ в 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, чтобы каждый пользователь мог понять методы его применения.

Оцените качество статьи. Ваше мнение важно для нас: