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

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

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

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

Обзор

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

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

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

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

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

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

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

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

1. Выделите свободную ячейку подальше от края листа и напишите в ней формулу: = Ячейка общего дохода. Например, если в ячейке С14 записан доход (указано случайное обозначение), нужно написать так: =S14.
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: Г17;»<0″). Диапазон ячеек — это столбец денежного потока. Для дальнейших периодов применим следующую формулу: =Начальный период+ЕСЛИ(Первый электронный поток>0; Первый электронный поток;0). Проект выйдет на точку безубыточности через 4 года.

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

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

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

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

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

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

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

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

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