Анализ чувствительности проекта в Excel

Анализ чувствительности проекта в Excel

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

Рассмотрим элементарную экономическую модель в Excel по формированию прибыли от реализации продукции:

Условно выделим 3 варианта анализа чувствительности, доступные в Excel:

  1. Чувствительность 1 показателя к изменению 1 параметра

Просчитаем варианты изменения прибыли в зависимости от изменения цены. Для этого построим таблицу следующего вида, где в 10 строке укажем желаемые значения цены, а в ячейке B11 зададим ссылку на целевой показатель (прибыль):

Выделив указанный фрагмент запустим инструмент Таблицы данных на вкладке Данные

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

После нажатия ОК получаем сформированные варианты:

Т.е. при цене 5 руб прибыль составит 10 рублей, при цене 6 рублей — 20 рублей и так далее.. Самое время построить диаграмму:

2. Чувствительность нескольких показателей к изменению 1 параметра

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

В данном случае программа просчитает как варианты прибыли, так и варианты выручки при изменении цены:

3. Чувствительность 1 показателя к изменению 2 параметров

Таблицы данных могут также успешно применяться для 2 переменных. Например, определим зависимость прибыли от изменения одновременно цены и объема продаж. Для этого построим следующую таблицу:

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

Выделяем сформированную таблицу, запускаем Таблицы данных, задаем соответствующие параметры:

Получаем следующий результат. Здесь, кстати, хорошо подходит применение условного форматирования:

При цене 6 руб и объеме продаж 11 ед прибыль будет равна 22 руб, при цене 7 руб и объеме продаж 12 ед прибыль будет равна 36 руб и так далее.

С визуализацией здесь несколько сложнее, для отдельных случаев подойдет вид нескольких графиков:

Кстати, в последней версии нашей надстройки SubEx 3.7 для Excel включен мастер анализа чувствительности на основе Таблиц данных.

В надстройке вы найдете встроенные схемы и все необходимые подсказки, чтобы еще быстрее и удобнее использовать данный функционал Excel:

Источник:
http://vk.com/@excel_subex-analiz-chuvstvitelnosti-proekta-v-excel