Анализ чувствительности в Excel: как проверить ключевые допущения модели
Анализ чувствительности показывает, как один результат модели меняется при разных значениях ключевых допущений. Для одного или двух факторов в Excel удобно использовать Таблицу данных: формула результата остаётся одной, а программа последовательно подставляет значения цены, объёма или другого входа.
Что именно проверяет анализ чувствительности
Он отвечает на вопрос «что будет с результатом, если изменить допущение», но не говорит, насколько вероятен сценарий. Например, таблица покажет прибыль при разных ценах и объёмах, однако не докажет, что покупатели примут новую цену или что рынок обеспечит нужный объём.
До запуска сформулируйте решение: ищем границу убытка, проверяем запас по цене или определяем самый влиятельный драйвер. Если сама модель ещё не построена, сначала полезно разобраться, как устроена финансовая модель.
Подготовим проверяемую модель
В учебном примере прибыль рассчитывается так:
Прибыль = объём × (цена − переменные затраты на единицу) − постоянные затраты.
| Ячейка | Показатель | Значение |
|---|---|---|
| B2 | Цена | 1 200 ₽ |
| B3 | Объём | 1 000 единиц |
| B4 | Переменные затраты | 700 ₽ |
| B5 | Постоянные затраты | 300 000 ₽ |
| B6 | Прибыль | =B3*(B2-B4)-B5 |
Базовая прибыль равна 1 000 × (1 200 − 700) − 300 000 = 200 000 ₽. Входы вынесены отдельно, а формула ссылается на них. Если цена зашита внутрь формулы числом, Таблица данных не сможет корректно проверять этот фактор.
Таблица данных для одной переменной
- В отдельном столбце запишите цены: 1 000, 1 100, 1 200, 1 300 и 1 400 ₽.
- Над соседним столбцом поставьте ссылку на результат:
=$B$6. - Выделите значения вместе с формулой результата.
- Откройте «Данные» → «Анализ “что если”» → «Таблица данных».
- Поскольку значения расположены столбцом, укажите B2 как входную ячейку столбца.
| Цена | Прибыль |
|---|---|
| 1 000 ₽ | 0 ₽ |
| 1 100 ₽ | 100 000 ₽ |
| 1 200 ₽ | 200 000 ₽ |
| 1 300 ₽ | 300 000 ₽ |
| 1 400 ₽ | 400 000 ₽ |
Проверка угловой точки обязательна: при цене 1 000 ₽ маржа на единицу равна 300 ₽, а 1 000 × 300 − 300 000 = 0. Это граница безубыточности при заданном объёме.
Таблица для двух переменных
Пусть цены 1 100, 1 200 и 1 300 ₽ стоят по верхней строке D2:F2, а объёмы 800, 1 000 и 1 200 — в C3:C5. В C2 поставьте =$B$6. Выделите C2:F5 и снова откройте Таблицу данных. Входная ячейка строки — B2, потому что по строке меняется цена; входная ячейка столбца — B3, потому что в столбце меняется объём.
| Объём / цена | 1 100 ₽ | 1 200 ₽ | 1 300 ₽ |
|---|---|---|---|
| 800 | 20 000 ₽ | 100 000 ₽ | 180 000 ₽ |
| 1 000 | 100 000 ₽ | 200 000 ₽ | 300 000 ₽ |
| 1 200 | 180 000 ₽ | 300 000 ₽ | 420 000 ₽ |
Контроль: при цене 1 100 ₽ и объёме 800 прибыль равна 800 × (1 100 − 700) − 300 000 = 20 000 ₽. Если ручной расчёт не совпадает, проверьте адреса входных ячеек.
Как читать матрицу
- Граница риска. Отметьте комбинации с нулём и убытком.
- Рабочий диапазон. Не оценивайте экстремальные значения, которые бизнес не может реализовать.
- Наклон результата. Сравните изменение прибыли на одинаковом шаге цены и объёма.
- Решение. Укажите, какое допущение нужно подтвердить исследованием, договором или операционным планом.
Цветовая шкала помогает увидеть зоны, но не заменяет подписи и числа. Два фактора также не объясняют весь риск: переменные затраты, постоянные расходы и ассортимент в реальности могут меняться одновременно.
Чем Таблица данных отличается от других инструментов
Официальная справка Microsoft относит к анализу «что если» Сценарии, Подбор параметра и Таблицы данных. Таблица данных перебирает одну или две переменные. Сценарии сохраняют несколько наборов допущений. Подбор параметра идёт от нужного результата к одному входу. Поиск решения оптимизирует целевую ячейку с ограничениями; его отдельно разбирает материал про Поиск решения в Excel.
Точная схема одно- и двухфакторных таблиц приведена в официальной инструкции Microsoft. Названия команд могут немного отличаться между версиями, поэтому для статьи и рабочей инструкции используйте скриншоты реального актуального Excel.
Почему таблица не работает
- Результат не зависит от указанной входной ячейки.
- Перепутаны ячейки строки и столбца.
- Процент введён как 10, а модель ожидает 10% или 0,1.
- Смешаны рубли, тысячи и миллионы рублей.
- В книге выбран режим пересчёта, при котором таблицы данных не обновляются автоматически.
- Все варианты дают одинаковое число из-за абсолютной ссылки не на тот вход.
Главное
Вынесите допущения в отдельные ячейки, определите один результат и сначала проверьте базовый расчёт. Затем создайте одно- или двухфакторную Таблицу данных, вручную пересчитайте хотя бы две точки и интерпретируйте не только максимум, но и границу риска.
Чтобы анализ чувствительности стал частью рабочей модели, нужно связать выручку, затраты, отчётность и сценарии. Такая практика входит в программу «Финансовый аналитик».
Хотите строить не отдельную таблицу, а связанную финансовую модель?
Программа «Финансовый аналитик» — профессиональная переподготовка с дипломом. На ней отчётность, модели в Excel, бюджетирование и сценарный анализ складываются в единую практическую систему.
Вопросы и ответы
- Сколько переменных поддерживает Таблица данных Excel?
Одну или две. Для нескольких наборов допущений используйте Сценарии, а для оптимизации результата с ограничениями — Поиск решения.
- Почему результаты Таблицы данных одинаковые?
Чаще всего итоговая формула не зависит от выбранной входной ячейки, указана не та ячейка строки или столбца либо в книге изменён режим пересчёта.
- Анализ чувствительности показывает вероятность сценария?
Нет. Он показывает условный результат при заданных значениях. Вероятность и реалистичность допущений нужно оценивать отдельно.