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

Анализ чувствительности является развитием задачи типа «Что будет, если?», дает возможность путем подстановки в формулу различных значений переменных, представить зависимость результатов вычислений по формуле от значений входящих в нее переменных. Этот режим реализуется в Excel при помощи команды Д анные – Таблица подстановки. Создавать таблицы можно на основе одной или двух переменных (одномерный или двумерный анализ). Значения переменных задаются в виде списков. При создании таблицы данных с одной переменной список исходных значений задается либо в виде строки, либо в виде столбца. При использовании двух переменных значений одной из них располагаются в столбце, значения другой – в строке, а результаты вычислений – на пересечении соответствующей строки и столбца. Для случая с одной переменной в формуле должна быть указана ссылка на одну ячейку, в которой при создании таблицы будут подставляться значения из списка, для таблицы с двумя переменными – ссылки на две ячейки.

Одномерный анализ чувствительности

Лабораторная модель №1

Цели работы:

1) освоить технологию решения задач «Что будет, если?» и «Как сделать, чтобы?»;

2) уметь проводить анализ чувствительности по одному параметру.

Содержание работы. Рассмотрим построение одномерной таблицы чувствительности на следующем примере. Предположим, что заработная плата коммерческого агента определяется установленным процентом вознаграждения. Для определения абсолютного размера вознаграждения в зависимости от объема продаж построим таблицу чувствительности. В любой столбец (например, в D) введем интересующие нас значения продаж. В ячейках E2 и F2 записываются формулы расчета зарплаты с указанным процентом вознаграждения 12 и 15 в следующем виде: =D2*0, 12 и D2*0, 15. Каждая формула ссылается на так называемую ячейку ввода столбца D2 содержащую первое значение объема продаж.

Исходные данные, необходимые для построения таблицы анализа чувствительности, представлены в таблице 3.2.

Таблица 3.2

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

1) выделите интервал, содержащий список исходных значений, и область с формулами (в нашем случае это D2: F12);

2) выберите команду Д анные – Таблица подстановки;

3) в появившемся окне укажите ячейку ввода строки (в нашем случае – $D$2).

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

В результате выполненных действий Excel автоматически строит требуемую таблицу чувствительности и выводит ее на экран в указанный в блок (табл.3.3).

Таблица 3.3

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

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

=ТАБЛИЦА(; D2)

В качестве аргумента выступает ячейка D2. В нее подставляются значения из представленного списка.

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