Как сделать анализ чувствительности в Microsoft Excel

Одномерная таблица данных для анализа изменения одного параметра

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

1. Допустим, у вас есть финансовая модель, в которой рассчитываются основные показатели бизнеса: выручка, затраты и чистая прибыль. Для примера возьмем простую модель с исходными параметрами в ячейках B4:B8 (цена товара, объем продаж, себестоимость, постоянные и переменные затраты), а расчетные показатели находятся в ячейках B11:B15. Чистая прибыль рассчитывается в ячейке B15 по формуле =B11-B14.

2. Предположим, вам нужно понять, как изменится прибыль при различных ценах на рынке. Это важно, поскольку цена может колебаться в зависимости от конкурентной ситуации. Перейдите к свободной области под вашей таблицей, например в ячейку A35, и введите заголовок «Анализ чувствительности к цене».

3. В ячейке A37 введите текст «Цена», а начиная с ячейки A38 создайте список значений цены для анализа. Например, введите 1200, 1300, 1400, 1500, 1600, 1700, 1800 в ячейки A38:A44. Эти значения покрывают диапазон от минимальной до максимальной возможной цены на вашем рынке.

4. В ячейке B37 введите заголовок «Чистая прибыль». Затем в ячейке B37 (непосредственно справа от заголовка «Цена» и на одну строку выше первого входного значения) введите формулу =B15, которая ссылается на результирующую ячейку с чистой прибылью из вашей основной модели.

5. Выделите диапазон A37:B44, который включает ячейку с формулой и все входные значения цены. Перейдите на вкладку «Данные», затем в группе «Прогноз» раскройте меню «Анализ «что если»» и выберите пункт «Таблица данных».

6. В открывшемся диалоговом окне поле «Подставлять значения по столбцам в» оставьте пустым, поскольку наши входные значения расположены в столбце. В поле «Подставлять значения по строкам в» укажите ссылку на ячейку B4 — именно там в вашей модели находится исходное значение цены товара, которое будет автоматически подменяться. Нажмите «ОК».

7. Excel автоматически заполнит столбец B значениями прибыли для каждой цены, создав формулу массива {=ТАБЛИЦА(;B4)} во всех ячейках результатов. Обратите внимание на фигурные скобки — они указывают, что это формула массива, которую нельзя редактировать в отдельных ячейках.

8. Примените к результатам числовой формат для удобства восприятия. Выделите диапазон B38:B44, щелкните правой кнопкой мыши, выберите «Формат ячеек» и установите формат «Числовой» с разделителем разрядов. Теперь вы можете сразу увидеть, при какой цене прибыль становится отрицательной (убыток) или достигает максимума.

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

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

1. Допустим, вам необходимо проанализировать, как одновременное изменение цены и объема продаж повлияет на чистую прибыль. Это важный анализ, поскольку при снижении цены обычно растет объем продаж, и наоборот. Для примера в нашей таблице уже подготовлена структура с заголовком «ТАБЛИЦА ПОДСТАНОВКИ (для двумерного анализа)», которую мы будем использовать для наглядности.

2. Обратите внимание на готовую структуру: в ячейке A20 находится заголовок «Цена / Объем», который обозначает, что по вертикали будут располагаться цены, а по горизонтали — объемы продаж. В строке 20 в ячейках B20:F20 уже введены различные значения объема продаж: 80, 90, 100, 110, 120 — эти значения показывают возможные колебания спроса от минимального до максимального.
3. В столбце A начиная с ячейки A21 и до A25 перечислены значения цены: 1200, 1350, 1500, 1650, 1800. Эти цены охватывают реалистичный диапазон от минимальной (при высокой конкуренции) до максимальной (при благоприятной рыночной ситуации). Центральное значение 1500 соответствует базовой цене из вашей основной модели в ячейке B4.

4. Поскольку заголовок A20 существует только для демонстрации структуры данных, заменим его на значение для нашего двумерного анализа. Вместо заголовка введите формулу =B15, которая ссылается на ячейку с чистой прибылью из вашей основной финансовой модели. Эта формула станет основой для расчета всех комбинаций.

5. Выделите весь диапазон A20:F25, который включает ячейку с формулой =B15, все значения объема в верхней строке и все значения цены в левом столбце. Перейдите на вкладку Данные, затем в группе Работа с данными откройте меню Анализ «что если» и выберите пункт Таблица данных.

6. В открывшемся диалоговом окне настройте параметры подстановки: в поле Подставлять значения по столбцам в укажите ссылку $B$5 — это ячейка, где в основной модели находится объем продаж (значения из верхней строки будут подставляться сюда). В поле Подставлять значения по строкам в укажите ссылку $B$4 — это ячейка с ценой товара (значения из левого столбца будут подставляться сюда). Нажмите ОК.

7. Excel создаст формулу массива {=ТАБЛИЦА(B4;B5)} и автоматически рассчитает прибыль для каждой комбинации цены и объема продаж, заполнив диапазон B21:F25 результатами. Вы увидите, что при низких ценах и малых объемах прибыль отрицательная (убыток), а при высоких ценах и больших объемах достигается максимальная прибыль.

8. Для наглядности примените условное форматирование к диапазону результатов B21:F25. Выделите этот диапазон, перейдите на вкладку Главная, раскройте меню Условное форматирование и выберите Цветовые шкалы — Зеленая-желтая-красная цветовая шкала. Зеленым будут выделены наиболее прибыльные комбинации (например, цена 1800 и объем 120), красным — убыточные (цена 1200 и объем 80).

Визуализация результатов двумерного анализа

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

1. Выделите диапазон с результатами B21:F25 (без заголовков и входных значений — только рассчитанные данные). Перейдите на вкладку Вставка, затем в группе Диаграммы выберите Поверхностная — Объемная поверхностная для объемного представления данных.

2. Настройте диаграмму, добавив названия осей и заголовок. Щелкните по диаграмме, затем на появившейся вкладке Конструктор диаграмм нажмите Добавить элемент диаграммы и последовательно добавьте Название диаграммы («Анализ чувствительности прибыли») и Названия осей («Цена», «Объем», «Прибыль»).

3. Измените угол обзора для лучшей читаемости: щелкните правой кнопкой мыши по области диаграммы, выберите Поворот объемной фигуры и настройте углы поворота по осям X и Y до тех пор, пока диаграмма не станет максимально наглядной. Полученная поверхность четко покажет «холмы» прибыли и «впадины» убытков.

Использование Диспетчера сценариев для сравнения различных вариантов развития

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

1. Предположим, вам нужно подготовить три варианта бизнес-плана для презентации инвесторам: пессимистичный (при неблагоприятной рыночной ситуации), базовый (при текущих условиях) и оптимистичный (при благоприятном развитии событий). Мы подготовили демонстрационные данные для сценариев в строках 28-33: в столбце A перечислены названия сценариев, а в столбцах B, C и D указаны соответствующие значения цены, объема и себестоимости для каждого варианта.

2. Выделите диапазон изменяемых ячеек B4:B6 в основной модели — это ячейки с ценой товара, объемом продаж и себестоимостью единицы. Именно эти параметры зависят от рыночной ситуации и будут меняться в каждом сценарии. Перейдите на вкладку Данные, в группе Работа с данными откройте меню Анализ «что если» и выберите Диспетчер сценариев.

3. В открывшемся окне Диспетчер сценариев нажмите кнопку Добавить для создания первого сценария.

4. В поле Название сценария введите «Пессимистичный» — это вариант при экономическом спаде. В поле Изменяемые ячейки должен быть указан диапазон $B$4:$B$6, после чего нажмите ОК.

5. В диалоговом окне Значения ячеек сценария введите значения из вашей таблицы данных. Если отталкиваться от нашего примера, в поле $B$4 (цена) укажите 1200 (низкая цена из-за конкуренции), в поле $B$5 (объем) введите 80 (снижение спроса на 20%), в поле $B$6 (себестоимость) укажите 950 (рост затрат на производство). Нажмите кнопку Добавить для создания следующего сценария.

6. Создайте сценарий «Базовый» с параметрами из строки 32 таблицы: цена 1500, объем 100, себестоимость 900 — это текущие условия работы. Затем добавьте сценарий «Оптимистичный» со значениями из строки 33: цена 1800 (можно повысить из-за высокого спроса), объем 120 (рост продаж на 20%), себестоимость 850 (оптимизация производства снизила затраты). После ввода последнего сценария нажмите ОК вместо Добавить.

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

8. Для создания сводного отчета, который можно показать инвесторам, нажмите кнопку Отчет в окне диспетчера. В диалоговом окне Отчет по сценарию выберите тип отчета Структура (более наглядный формат) и в поле Ячейки результата укажите диапазон B11:B15, где находятся ключевые показатели: выручка, общие переменные затраты, общая себестоимость, общие затраты и чистая прибыль. Нажмите ОК.

9. Excel создаст новый лист с именем «Структура сценария», содержащий таблицу со сравнением всех трех вариантов развития. В этом отчете вы сразу увидите, что в пессимистичном сценарии бизнес становится убыточным (отрицательная чистая прибыль), базовый вариант дает умеренную прибыль 15 000, а оптимистичный показывает максимальную доходность с прибылью около 65 000.

Анализ влияния одного параметра на несколько показателей одновременно

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

1. Допустим, вам нужно понять, как изменение цены повлияет не только на прибыль, но и на выручку и общие затраты. Это важно для комплексной оценки: высокая цена дает больше прибыли на единицу, но может снизить общую выручку из-за падения спроса. Перейдите к свободной области под вашей таблицей, например к ячейке A35, и создайте заголовок «Комплексный анализ влияния цены».

2. В ячейке A37 введите текст «Цена» — это будет заголовок столбца с входными значениями. В ячейках B37, C37 и D37 будем вычислять следующие параметры: «Выручка», «Общие затраты» и «Чистая прибыль» соответственно. Эти три показателя дают полное представление о финансовом состоянии при разных ценах.
3. Начиная с ячейки A38 создайте список значений цены для анализа. Введите следующие значения в ячейки A38:A44: 1200, 1300, 1400, 1500, 1600, 1700, 1800. Эти же значения цены вы использовали ранее в первом разделе материала, что позволит сравнить результаты разных методов анализа.

4. В строке 37 (на одну строку выше первого входного значения цены) разместите формулы для отслеживаемых показателей: в ячейке B37 введите =B11 — это ссылка на выручку из вашей основной модели, в C37 введите =B14 — это общие затраты, в D37 введите =B15 — это чистая прибыль. Каждая из этих формул будет рассчитана для всех значений цены.

5. Выделите диапазон A37:D44, который включает все три формулы в верхней строке и все входные значения цены в левом столбце. Откройте меню Данные — Работа с данными — Анализ «что если» — Таблица данных.

6. В диалоговом окне таблицы данных в поле Подставлять значения по строкам в укажите ссылку $B$4, где в основной модели находится цена товара. Поле Подставлять значения по столбцам в оставьте пустым, поскольку входные значения расположены в столбце (вертикально). Нажмите ОК.

7. Excel заполнит столбцы B, C и D рассчитанными значениями для каждой цены, создав формулу массива {=ТАБЛИЦА(;B4)} в каждом столбце результатов. Теперь вы получили три столбца с данными: в столбце B видно, как выручка растет с увеличением цены (от 120 000 при цене 1200 до 180 000 при цене 1800), в столбце C затраты остаются стабильными на уровне 135 000, а в столбце D прибыль изменяется от убытка -15 000 до прибыли 45 000.

8. Постройте комбинированную диаграмму для визуализации результатов: выделите диапазон A37:D44 (включая заголовки), перейдите на вкладку Вставка и выберите тип диаграммы График с маркерами. На графике будут отображены три линии разных цветов, показывающие динамику каждого показателя при изменении цены.

9. Настройте диаграмму для лучшей читаемости: добавьте название диаграммы «Влияние цены на ключевые показатели», подпишите вертикальную ось как «Сумма» и горизонтальную как «Цена товара». По графику вы сможете быстро определить точку безубыточности (где линия прибыли пересекает ноль) и увидеть, что при низких ценах бизнес убыточен, несмотря на стабильные затраты.

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

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

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

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

• Невозможно редактировать ячейки таблицы данных. После создания таблицы данных Excel автоматически защищает ячейки с результатами, поэтому вы не можете изменить отдельные значения в созданной таблице. Это ограничение связано с тем, что результаты вычисляются через формулу массива. Чтобы изменить таблицу, необходимо сначала удалить весь диапазон с формулой массива (выделить все ячейки с результатами и нажать Del), а затем создать таблицу заново с нужными параметрами.

• Диспетчер сценариев не отображает изменения. Если после применения сценария значения в ячейках не изменились, убедитесь, что в настройках сценария правильно указаны изменяемые ячейки. Откройте Диспетчер сценариев, выберите проблемный сценарий и нажмите Изменить, затем проверьте диапазон изменяемых ячеек и их значения. Также убедитесь, что изменяемые ячейки не защищены и не содержат формулы.

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

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