Процессы в сфере финансов всегда взаимосвязаны – один фактор зависит от другого и меняется вместе с ним. Отследить эти изменения и понять, чего стоит ожидать в будущем, возможно с помощью функций Excel и табличных методов.
- Получение нескольких результатов с помощью таблицы данных
- Общие сведения
- Базовые сведения о таблицах данных
- Создание таблицы данных с одной переменной
- Добавление формулы в таблицу данных с одной переменной
- Создание таблицы данных с двумя переменными
- Ускорение вычислений для листов, содержащих таблицы данных
- Другие инструменты для выполнения анализа чувствительности
- Анализ чувствительности инвестиционного проекта в Excel
- Метод анализа чувствительности в сфере инвестиций
- Формула
- Пример анализа чувствительности инвестиционного проекта в Excel
- Анализ чувствительности инвестиций при помощи таблицы данных
- Факторный и дисперсионный анализ в Excel с автоматизацией подсчетов
- Дисперсионный анализ в Excel
- Факторный анализ в Excel: пример
- Двухфакторный дисперсионный анализ в Excel
- Заключение
Получение нескольких результатов с помощью таблицы данных
Возможности таблиц данных представляют собой элементы анализа «что если» – его нередко проводят через Microsoft Excel. Это второе название анализа чувствительности.
Общие сведения
Таблица данных – это тип диапазона ячеек, с помощью которого можно решать возникающие проблемы путем изменения значений в некоторых ячейках. Ее составляют, когда необходимо следить за изменениями компонентов формулы и получать обновления результатов, согласно этим изменениям. Выясним, как применять таблички данных в исследованиях, и каких видов они бывают.
Базовые сведения о таблицах данных
Существует два вида таблиц данных, они различаются по количеству компонентов. Составлять таблицу нужно с ориентацией на количество значений, которые нужно проверить с ее помощью.
Специалисты статистики применяют таблицу с одной переменной, когда в одном или нескольких выражениях есть только одна переменная, которая может повлиять на изменение их результата. К примеру, ее часто используют в связке с функцией ПЛТ. Формула предназначена для расчета суммы регулярного платежа и учитывает установленную в договоре процентную ставку. При подобных вычислениях переменные записывают в одну колонку, а результаты вычислений в другую. Пример таблички данных с 1 переменной:
Далее рассмотрим таблички с 2 переменными. Они применяются в тех случаях, когда на изменение какого-либо показателя влияют два фактора. Две переменные могут оказаться в другой таблице, связанной с займом, – с ее помощью можно выявить оптимальный срок выплаты и сумму ежемесячного платежа. В таком расчете тоже нужно использовать функцию ПЛТ. Пример таблички с 2 переменными:
Создание таблицы данных с одной переменной
Рассмотрим метод анализа на примере небольшого книжного магазина, где всего 100 книг в наличии. Часть из них можно продать дороже (50$), остальные обойдутся покупателям дешевле (20$). Рассчитан общий доход с продажи всех товаров – владелец решил, что продаст по высокой цене 60% книг. Необходимо выяснить, как вырастет выручка, если повысить цену на больший объем товара – 70% и так далее.
Обратите внимание! Общую выручку обязательно нужно считать с помощью формулы, иначе не получится составить таблицу данных.
- Выбираем свободную ячейку в отдалении от края листа и записываем в ней формулу: =Ячейка общей выручки. Например, если доход записан в ячейке С14 (указано случайное обозначение), необходимо написать так: =С14.
- Записываем проценты объема товара в столбец слева от этой ячейки – не под ней, это очень важно.
- Выделяем диапазон ячеек, где располагается столбец процентов и ссылка на общий доход.
- Находим на вкладке «Данные» пункт «Анализ «что если»» и кликаем по нему – в открывшемся меню нужно выбрать опцию «Таблица данных».
- Откроется небольшое окно, где необходимо указать ячейку с процентом изначально проданных по высокой цене книг в графе «Подставлять значения по строкам в…». Этот шаг делают для того, чтобы делать перерасчет общей выручки с учетом возрастающего процента.
После нажатия кнопки «ОК» в окне, где вводились данные для составления таблицы, в строках появятся результаты вычислений.
Добавление формулы в таблицу данных с одной переменной
Из таблицы, которая помогала рассчитывать действие только с одной переменной, можно сделать усложненный инструмент анализа, добавив дополнительную формулу. Ее необходимо вписать рядом с уже существующей формулой – например, если таблица ориентирована по строкам, вписываем выражение в ячейку справа от уже существующего. Когда установлена ориентация по столбцам, записываем новую формулу под старой. Далее следует действовать по алгоритму:
- Вновь выделяем диапазон ячеек, но теперь он должен включать новую формулу.
- Открываем меню анализа «что если» и выбираем «Таблицу данных».
- Добавляем новую формулу в соответствующее поле по строчкам или по столбикам в зависимости от ориентации таблички.
Создание таблицы данных с двумя переменными
Начало составления такой таблицы немного отличается – нужно поместить ссылку на общую выручку над значениями процентов. Далее выполняем эти шаги:
- Записать варианты цены в одну строку со ссылкой на доход – каждой цене одна ячейка.
- Выделить диапазон ячеек.
- Открыть окно таблицы данных, как при составлении таблички с одной переменной – через вкладку «Данные» на панели инструментов.
- Подставить в графу «Подставлять значения по столбцам в…» ячейку с начальной высокой ценой.
- Добавить в графу «Подставлять значения по строкам в…» ячейку с начальным процентом продаж дорогих книг и нажать «ОК».
В результате вся табличка заполнена суммами возможного дохода с разными условиями продажи товара.
Ускорение вычислений для листов, содержащих таблицы данных
Если требуются быстрые вычисления в табличке данных, не запускающие пересчет книги целиком, можно выполнить несколько действий для ускорения процесса.
- Открываем окно параметров, выбираем пункт «Формулы» в меню справа.
- Выбираем пункт «Автоматически, кроме таблиц данных» в разделе «Вычисления в книге».
- Выполним пересчет результатов в табличке вручную. Для этого нужно выделить формулы и нажать клавишу F
Другие инструменты для выполнения анализа чувствительности
В программе есть и другие инструменты, помогающие выполнять анализ чувствительности. Они автоматизируют некоторые действия, которые в ином случае пришлось бы выполнять вручную.
- Функция «Подбор параметра» подойдет, если известен нужный результат, и требуется узнать входное значение переменной для получения такого результата.
- «Поиск решения» – это надстройка для решения задач. Необходимо установить ограничения и указать на них, после чего система найдет ответ. Решение определяется путем изменения значений.
- Анализ чувствительности можно провести с помощью диспетчера сценариев. Этот инструмент находится в меню анализа «что если» на вкладке «Данные». Он подставляет значения в несколько ячеек – количество может достигать 32-х. Диспетчер сравнивает эти значения, и пользователю не придется менять их вручную. Пример применения диспетчера сценариев:
Анализ чувствительности инвестиционного проекта в Excel
Анализ «что если» оказывается особенно полезным в ситуациях, где требуется прогнозирование – например, в сфере инвестиций. Аналитики применяют этот метод, чтобы выяснить, как будет меняться стоимость акций компании в результате изменения некоторых факторов.
Метод анализа чувствительности в сфере инвестиций
При анализе «что если» используют перебор – ручной или автоматический. Известен диапазон значений, и они по очереди подставляются в формулу. В итоге получается набор значений. Из них выбирают подходящую цифру. Рассмотрим четыре показателя, по которым ведется анализ чувствительности в сфере финансов:
- Чистая приведенная стоимость – вычисляется путем вычитания размера вложения из объема доходов.
- Внутренняя норма доходности/прибыли – указывает, какую прибыль требуется получить с вложения за год.
- Коэффициент окупаемости – отношение всей прибыли к начальному вложению.
- Дисконтированный индекс прибыли – указывает на эффективность инвестиции.
Формула
Чувствительность вложения можно вычислить с помощью этой формулы: Изменение выходного параметра в % / Изменение входного параметра в %.
Выходным и входным параметром могут быть величины, описанные ранее.
- Необходимо узнать результат при стандартных условиях.
- Заменяем одну из переменных и следим за изменениями результата.
- Вычисляем процентное изменение обоих параметров относительно установленных условий.
- Вставляем полученные проценты в формулу и определяем чувствительность.
Пример анализа чувствительности инвестиционного проекта в Excel
Для лучшего понимания методики анализа необходим пример. Проанализируем проект с такими известными данными:
- Заполним таблицу, чтобы анализировать проект по ней.
- Вычисляем денежный поток с помощью функции СМЕЩ. На начальном этапе поток равен вложениям. Далее применяем формулу: =ЕСЛИ(СМЕЩ(Номер;1;)=2;СУММ(Приток 1:Отток 1); СУММ(Приток 1:Отток 1)+$B$5)
Обозначения ячеек в формуле могут быть другими, это зависит от размещения таблицы. В конце прибавляется значение из начальных данных – ликвидационная стоимость.
- Определяем срок, за который проект окупится. Для начального периода используем эту формулу: =СУММЕСЛИ(G7:G17;»<0″). Диапазон ячеек – это столбец денежного потока. На дальнейших периодах применим эту формулу: =Начальный период+ЕСЛИ(Первый д.поток>0; Первый д.поток;0). Проект оказывается в точке безубыточности за 4 года.
- Создаем столбец для номеров тех периодов, когда проект окупается.
- Вычисляем рентабельность вложений. Необходимо составить выражение, где прибыль в конкретном отрезке времени делится на начальные вложения.
- Определяем коэффициент дисконтирования по этой формуле: =1/(1+Ставка диск.%) ^Номер.
- Вычислим приведенную стоимость с помощью умножения – денежный поток умножается на коэффициент дисконтирования.
- Рассчитаем PI (индекс рентабельности). Приведенная стоимость в отрезке времени делится на вложения в начале развития проекта.
- Определим внутреннюю норму прибыли с помощью функции ВСД: =ВСД(Диапазон денежного потока).
Анализ чувствительности инвестиций при помощи таблицы данных
Для анализа проектов в сфере инвестирования лучше подходят другие способы, нежели таблица данных. У многих пользователей возникает путаница при составлении формулы. Чтобы выяснить зависимость одного фактора от изменений в других, нужно выбрать правильные ячейки ввода вычислений и для считывания данных.
Факторный и дисперсионный анализ в Excel с автоматизацией подсчетов
Еще одна типология анализа чувствительности – факторный и дисперсионный анализ. Первый тип определяет связь между числами, второй выявляет зависимость одной переменной от других.
Дисперсионный анализ в Excel
Цель подобного анализа – разделить изменчивость величины на три компонента:
- Изменчивость в результате влияния других значений.
- Изменения из-за взаимосвязи влияющих на него значений.
- Случайные изменения.
Выполним дисперсионный анализ через надстройку Excel «Анализ данных». Если она не включена, ее можно подключить в параметрах.
Начальная таблица должна соответствовать двум правилам: на каждую величину приходится один столбец, и данные в нем располагаются по возрастанию или по убыванию. Необходимо проверить влияние уровня образования на поведение в конфликте.
- Находим во вкладке «Данные» инструмент «Анализ данных» и открываем его окно. В списке нужно выбрать однофакторный дисперсионный анализ.
- Заполняем строки диалогового окна. Входной интервал – все ячейки без учета шапки и номеров. Группируем по столбцам. Выводим результаты на новый лист.
Поскольку значение в желтой ячейке больше единицы, можно считать предположение неверным – зависимости между образованием и поведением в конфликте нет.
Факторный анализ в Excel: пример
Проанализируем взаимосвязь данных в сфере продаж – необходимо выявить популярные и непопулярные товары. Начальная информация:
- Нужно выяснить, на какие товары больше всего вырос спрос в течение второго месяца. Составляем новую таблицу для определения роста и снижения спроса. Рост рассчитывается по этой формуле: =ЕСЛИ((Спрос 2-Спрос 1)>0; Спрос 2- Спрос 1;0). Формула снижения: =ЕСЛИ(Рост=0; Спрос 1- Спрос 2;0).
- Подсчитаем рост спроса на товары в процентах: =ЕСЛИ(Рост/Итог 2 =0;Снижение/Итог 2; Рост/Итог 2).
- Составим диаграмму для наглядности – выделяем диапазон ячеек и создаем гистограмму через вкладку «Вставка». В настройках нужно убрать заливку, это можно сделать через инструмент «Формат ряда данных».
Двухфакторный дисперсионный анализ в Excel
Дисперсионный анализ проводят с несколькими переменными. Рассмотрим это на примере: нужно выяснить, как быстро проявляется реакция на звук разной громкости у мужчин и женщин.
- Открываем «Анализ данных», в списке нужно найти двухфакторный дисперсионный анализ без повторений.
- Входной интервал – ячейки, где содержатся данные (без шапки). Выводим результаты на новый лист и жмем «ОК».
Показатель F больше, чем F-критическое – это означает, что пол влияет на скорость реакции на звук.
Заключение
В данной статье был подробно рассмотрен анализ чувствительности в табличном процессоре Excel, благодаря чему каждый пользователь сможет разобраться в методах его применения.