Как провести анализ чувствительности в Excel: пошаговое руководство

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

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

Что такое анализ чувствительности

Анализ чувствительности показывает, насколько сильно меняется результат при изменении одного допущения. Он отвечает на вопросы типа «Что произойдет с прибылью, если цена упадет на 10%?»

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

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

Microsoft перечисляет три встроенных инструмента: «Сценарии, Подбор параметра и Таблицы данных». У каждого своя задача:

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

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

Настройте модель перед началом

Инструменты Excel работают только в том случае, если модель построена чисто. Сначала проверьте эти пункты.

Поместите каждый входной параметр в отдельную ячейку. Количество, цена, затраты и ставки — каждому нужна своя ячейка. Не вводите числа внутри формул.

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

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

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

Зафиксируйте базовый вариант. Запишите начальные значения. Каждый результат в анализе сравнивается с этой базой.

Вот простая модель прибыли для примера. Это вымышленный магазин велосипедов Northgate Cycles:

Ячейка	Метка	Значение
B1	Продано единиц	2,400
B2	Цена за единицу	$850
B3	Переменные затраты на единицу	$510
B4	Постоянные затраты	$520,000
B5	Прибыль	=B1*(B2-B3)-B4

При этих базовых значениях прибыль составляет $296,000.

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

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

Вот как протестировать цену в примере модели:

1. В ячейках с D2 по D6 введите цены для теста: $800, $825, $850, $875 и $900.
2. В ячейке E1, на одну строку выше и на один столбец правее первой цены, введите =B5.
3. Выделите диапазон D1:E6.
4. На вкладке Данные выберите Анализ «что-если» > Таблица данных.
5. В поле Подставлять значения по столбцам в введите B2 (ячейка цены). Поле Подставлять значения по строкам в оставьте пустым.
6. Нажмите ОК.

Excel заполнит E2:E6 значениями прибыли для каждой цены. Расположение формулы имеет значение. Microsoft рекомендует вводить ее «на одну строку выше и на одну ячейку правее столбца значений».

Результаты для Northgate Cycles:

Цена	Прибыль
$800	$176,000
$825	$236,000
$850	$296,000
$875	$356,000
$900	$416,000

Каждое изменение цены на $25 меняет прибыль на $60,000. Это и есть чувствительность прибыли к цене.

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

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

Вот как протестировать цену и количество единиц вместе:

1. В пустой ячейке, например D10, введите =B5.
2. Ниже, в ячейках с D11 по D13, введите объемы продаж: 2,000, 2,400 и 2,800.
3. Справа, в ячейках с E10 по G10, введите цены: $800, $850 и $900.
4. Выделите диапазон D10:G13.
5. Выберите Анализ «что-если» > Таблица данных.
6. В поле Подставлять значения по строкам в введите B2. В поле Подставлять значения по столбцам в введите B1.
7. Нажмите ОК.

Результатом будет сетка значений прибыли:

Единицы \ Цена	$800	$850	$900
2,000	$60,000	$160,000	$260,000
2,400	$176,000	$296,000	$416,000
2,800	$292,000	$432,000	$572,000

Сетка показывает, что магазин остается прибыльным при любой протестированной комбинации. Самый слабый вариант (2,000 единиц по $800) все равно приносит $60,000.

Есть одно жесткое ограничение. Microsoft заявляет: «Таблица данных не может содержать более двух переменных». Для большего количества параметров используйте Диспетчер сценариев.

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

Как сравнивать полные кейсы с помощью Диспетчера сценариев

Диспетчер сценариев сохраняет полные наборы входных данных и подставляет их. Microsoft описывает сценарий как «набор значений, которые Excel сохраняет и может автоматически подставлять в ваш лист».

Вот как настроить три кейса:

1. На вкладке Данные выберите Анализ «что-если» > Диспетчер сценариев.
2. Нажмите Добавить, назовите сценарий «Базовый» и укажите B1:B4 как изменяемые ячейки. Сохраните текущие значения.
3. Добавьте «Худший кейс»: 2,000 единиц, цена $825, переменные затраты $530 и постоянные затраты $540,000.
4. Добавьте «Лучший кейс»: 2,800 единиц, цена $875, переменные затраты $495 и постоянные затраты $510,000.
5. Выберите сценарий и нажмите Вывести, чтобы загрузить его значения.
6. Нажмите Отчет и выберите B5 в качестве ячейки результата, чтобы создать отчет.

Для Northgate Cycles сводка покажет три значения прибыли. Худший кейс приносит $50,000, базовый — $296,000, а лучший — $554,000.

Microsoft отмечает несколько ограничений. Каждый сценарий может содержать до 32 изменяемых значений. Сводный отчет не является автоматическим: «Отчеты по сценариям не пересчитываются автоматически». Если вы измените сценарий, создайте новую сводку.

Как найти точку безубыточности с помощью Подбора параметра

Анализ чувствительности показывает, насколько меняется результат. Подбор параметра отвечает на обратный вопрос: какое входное значение дает целевой результат?

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

Вот как найти цену безубыточности для Northgate Cycles:

1. На вкладке Данные выберите Анализ «что-если» > Подбор параметра.
2. В поле Установить в ячейке введите B5 (ячейка прибыли).
3. В поле Значение введите 0.
4. В поле Изменяя ячейку введите B2 (ячейка цены).
5. Нажмите ОК.

Excel будет менять цену до тех пор, пока прибыль не станет равной нулю. При 2,400 единицах и базовых затратах цена безубыточности составляет около $726.67. Это примерно на 15% ниже текущей цены в $850.

У Подбора параметра есть одно ограничение. Microsoft заявляет, что он «работает только с одним изменяемым входным значением». Для работы с несколькими параметрами Microsoft рекомендует надстройку Поиск решения (Solver).

Как построить диаграмму «торнадо»

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

Вот как ее построить:

1. Выберите диапазон тестирования, например, изменение каждого параметра на 10% в меньшую и большую сторону.
2. Изменяйте по одному параметру за раз и фиксируйте минимальный и максимальный результат. Остальные параметры оставляйте базовыми.
3. Поместите результаты в таблицу (одна строка на каждый параметр).
4. Отсортируйте строки так, чтобы параметр с самым широким диапазоном был сверху.
5. Вставьте гистограмму с накоплением или линейчатую диаграмму для минимальных и максимальных результатов.

Для Northgate Cycles при изменении каждого параметра на 10% вверх и вниз:

Параметр	Прибыль при −10%	Прибыль при +10%	Диапазон
Цена	$92,000	$500,000	$408,000
Переменные затраты	$418,400	$173,600	$244,800
Продано единиц	$214,400	$377,600	$163,200
Постоянные затраты	$348,000	$244,000	$104,000

Цена имеет наибольшее значение с большим отрывом. Снижение цены на 10% сокращает прибыль с $296,000 до $92,000. Постоянные затраты влияют меньше всего. Это подсказывает владельцу, на проверку каких допущений стоит тратить время.

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

Как провести анализ чувствительности с помощью AI

Создание таблиц данных, сценариев и диаграмм «торнадо» для большой модели требует времени. Инструмент AI может выполнить те же тесты на основе вашей книги.

Вот метод в три шага с использованием Powerdrill Bloom.

Шаг 1: Загрузите вашу модель

Начните с электронной таблицы, в которой находится ваша модель. AI Sensitivity Analysis Generator предложит вам загрузить «DCF, бизнес-кейс, операционный план или любую модель в электронной таблице».

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

Шаг 2: Выберите вид анализа на естественном языке

Опишите тест, который вы хотите провести. Например: «Покажи диаграмму торнадо для прибыли при изменении каждого параметра на 10% вверх и вниз. Добавь таблицу с двумя переменными для цены и объема продаж».

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

Шаг 3: Проверьте результаты и экспортируйте анализ

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

Затем выполните экспорт. На странице инструмента указаны «книга Excel, брендированный PDF или ссылка на холст». Для презентации инструмент Excel to PPT превратит тот же файл в слайды. Страница AI Financial Modeling посвящена созданию самой модели.

Как читать и представлять результаты

Анализ чувствительности полезен только в том случае, если на его основе принимаются решения. Эти советы помогут.

Начните с рейтинга. Откройте презентацию диаграммой «торнадо». Она на одной картинке показывает, какие допущения определяют результат.

Переводите диапазоны в решения. «Прибыль упадет до $92,000, если цена снизится на 10%» — это факт. «Нам следует протестировать цену, прежде чем подписывать договор аренды» — это решение.

Покажите точку безубыточности. Дополните анализ значением, при котором результат становится нулевым. Для Northgate Cycles безубыточность наступает примерно при 1,530 единицах при базовой цене и затратах. Наше руководство по анализу безубыточности в Excel описывает этот расчет.

Используйте реалистичные диапазоны. Изменение на 10% подходит для стабильных показателей. Объем продаж нового продукта может потребовать гораздо более широкого диапазона.

Указывайте, что оставалось неизменным. Каждый тест «по одному параметру» предполагает, что остальные входные данные остаются базовыми. Укажите это на слайде или в сноске. Тогда читатели поймут, что изменение цены на 10% предполагает тот же объем, переменные и постоянные затраты. Эта одна строка предотвращает большинство уточняющих вопросов о методе.

Вернитесь к анализу после получения результатов. Когда появятся фактические цифры, сравните их с вашими диапазонами. Отчет «план-факт» является логичным продолжением.

Распространенные ошибки анализа чувствительности

Эти ошибки встречаются часто, и их легко избежать.

Жестко заданные числа в формулах. Если формула содержит 850 вместо ссылки на B2, таблица данных не сможет их изменить. Держите каждый входной параметр в отдельной ячейке.

Тестирование слишком большого количества параметров одновременно. Таблица данных справляется с одним или двумя. Для большего количества переходите к Диспетчеру сценариев или диаграмме «торнадо».

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

Игнорирование связей между параметрами. Более низкая цена может увеличить объем продаж. Тест «по одному параметру» упускает это, поэтому добавьте сценарий, в котором меняются оба показателя.

Устаревшие сводки сценариев. Отчеты по сценариям не обновляются сами по себе. Перестраивайте их после любого изменения.

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

Когда вы будете готовы провести стресс-тест своей следующей модели, попробуйте Powerdrill Bloom с той книгой, которую вы используете для планирования.

Часто задаваемые вопросы

Что такое анализ чувствительности в Excel?

Это способ проверить, как меняется результат при изменении входных данных. В Excel вы используете инструменты анализа «что-если», в основном таблицы данных и Диспетчер сценариев, чтобы подставлять новые входные значения и фиксировать результаты.

Как сделать анализ чувствительности с одной переменной в Excel?

Перечислите входные значения в столбце. Поместите формулу, ссылающуюся на результат, на одну строку выше и на один столбец правее. Выделите диапазон, выберите Анализ «что-если» > Таблица данных и укажите ячейку ввода в поле «Подставлять значения по столбцам в».

В чем разница между таблицей данных и Диспетчером сценариев?

Таблица данных тестирует одну или две переменные для множества значений и показывает все результаты в одной сетке. Диспетчер сценариев сохраняет полные наборы входных данных (до 32 изменяемых значений в каждом) и подставляет их по очереди.

Как сделать диаграмму «торнадо» в Excel?

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

Может ли AI провести анализ чувствительности?

Да. Инструмент AI может прочитать вашу модель в электронной таблице, выполнить тесты «торнадо» и двухфакторные тесты, а затем экспортировать результаты в виде книги или отчета. Вам все равно нужно будет проверить базовый вариант и выбрать реалистичные диапазоны.

Источники: Microsoft Support, Введение в анализ «что-если» · Microsoft Support, Расчет нескольких результатов с помощью таблицы данных · Microsoft Support, Переключение между различными наборами значений с помощью сценариев.