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

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

 

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

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

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

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

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

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

 

 

1

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

 

 

2

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

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

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

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

 

 

3

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

 

 

4

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

 

 

5

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

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

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

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

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

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

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

 

 

6

1. Откройте таблицу данных, как это делается для таблицы с одной переменной - используйте вкладку "Данные" на панели инструментов.
2. Вставьте ячейку с начальной высокой ценой в столбец "Заменить значения столбцом в...".
3. Добавьте в ячейку "Заменить значения строкой в..." процент книг с начальной высокой ценой и нажмите "OK".

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

 

 

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. Рассчитайте денежный поток с помощью функции MESH. Первоначально денежный поток равен инвестициям. Затем примените формулу: = IF(SMESH(Number;1;)=2;SUMM(Inflow 1:Outflow 1);SUMM(Inflow 1:Outflow 1)+$B$5)
   Имена ячеек в формуле могут быть разными, в зависимости от макета таблицы. Наконец, вы добавляете остаточную стоимость из исходных данных.

 

 

12

1. Определите, сколько времени потребуется проекту, чтобы окупиться. Используйте эту формулу для начального периода: =SUM IF(G7:G17;"0; First d.flow;0). Проект выходит на безубыточность через 4 года.

 

 

13

1. Создайте колонку для номеров тех периодов, в которых проект безубыточен.

 

 

14

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

 

 

15

1. Используйте эту формулу для определения коэффициента дисконтирования: =1/(1+Диск.%) ^Число.

 

 

16

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

 

 

17

1. Рассчитайте PI (индекс прибыльности). Приведенная стоимость за временной горизонт делится на инвестиции в начале разработки проекта.

 

 

18

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

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

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

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

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

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

Цель этого вида анализа - разложить изменчивость переменной на три составляющие:

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

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

 

 

19

1. Перейдите на вкладку "Данные" инструмента "Анализ данных" и откройте его окно. Выберите из списка односторонний дисперсионный анализ.

 

 

20

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

 

 

21

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

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

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

 

 

22

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

 

 

23

1. Рассчитайте процентное увеличение спроса на товары: = ЕСЛИ(Рост/Промышленность 2 =0;Спад/спрос 2;Рост/Промышленность 2).

 

 

24

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

 

 

25

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

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

 

 

26

1. Откройте Анализ данных и найдите в списке двухфакторный дисперсионный анализ без репликации.
2. Входной интервал - это ячейки, содержащие данные (без заголовка). Распечатайте результаты на новом листе и нажмите OK.

 

 

27

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

 

 

28

Заключение

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