3.2.1. Одномерный анализ чувствительности Лабораторная модель №1
Цели работы:
1) освоить технологию решения задач «Что будет, если?» и «Как сделать, чтобы?»;
2) уметь проводить анализ чувствительности по одному параметру.
Содержание работы. Рассмотрим построение одномерной таблицы чувствительности на следующем примере. Предположим, что заработная плата коммерческого агента определяется установленным процентом вознаграждения. Для определения абсолютного размера вознаграждения в зависимости от объема продаж построим таблицу чувствительности. В любой столбец (например, в D) введем интересующие нас значения продаж. В ячейках E2 и F2 записываются формулы расчета зарплаты с указанным процентом вознаграждения 12 и 15 в следующем виде: =D2*0,12 и D2*0,15. Каждая формула ссылается на так называемую ячейку ввода столбца D2 содержащую первое значение объема продаж.
Исходные данные, необходимые для построения таблицы анализа чувствительности, представлены в таблице 3.2.
Таблица 3.2
После того, как подготовка к получению таблицы чувствительности закончена, необходимо выполнить следующие действия:
выделите интервал, содержащий список исходных значений, и область с формулами (в нашем случае это D2:F12);
выберите команду Данные – Таблица подстановки;
в появившемся окне укажите ячейку ввода строки (в нашем случае – $D$2).
Если таблица исходных данных ориентирована не по столбцам, а строкам, адрес ячейки, содержащий заменяемый компонент формулы, указывается в поле «Ячейка ввода строки».
В результате выполненных действий Excel автоматически строит требуемую таблицу чувствительности и выводит ее на экран в указанный в блок (табл.3.3).
Таблица 3.3
Содержательно данная таблица показывает абсолютный уровень вознаграждения, зависящий от размера продаж и заданного процента вознаграждения. Полученные данные позволяют определить приемлемый процент вознаграждения, который фирма согласна выплачивать своим коммерческим агентам, и величину этого вознаграждения в зависимости от суммы продаж.
При более внимательном изучении полученного результата можно увидеть, что для построения таблицы чувствительности была использована следующая функция:
=ТАБЛИЦА(;D2)
В качестве аргумента выступает ячейка D2. В нее подставляются значения из представленного списка.
Задание. Постройте табличную модель выбора компьютера и проведите анализ чувствительности с одним управляемым параметром.
3.2.2. Двухмерный анализ чувствительности Лабораторная модель №2
Цели работы:
1) освоить технологию решения задач «Что будет, если?» и «Как сделать, чтобы?»;
2) уметь проводить анализ чувствительности по двум параметрам;
3) иметь представление выработки альтернативы решения на основе сценарного подхода.
Двухмерная таблица чувствительности может использоваться для выявления одновременного влияния двух переменных на определенный показатель. При этом можно использовать только одну формулу.
Содержание работы. Рассмотрим следующий пример. Предположим, что фирма, оказывающая медицинские услуги (страховая медицинская компания), вводит новый способ определения годового взноса по следующей формуле:
ВЗНОС=(ГОДОВОЙ ДОХОД СЕМЬИ – (КОЛИЧЕСТВО ЧЛЕНОВ СЕМЬИ*1000))/100
В этом случае таблица чувствительности должна показать влияние двух факторов (годового дохода семьи и количество ее членов) на размер ежегодного взноса.
Для получения таблицы чувствительности необходимо предварительно ввести в электронную таблицу данные, определяющие, при каких именно значениях годового дохода и количества членов семьи мы хотим рассчитать значения взноса.
Это можно сделать следующим образом:
1) введите переменные задачи, их значения и формулу связи;
2) в один из столбцов электронной таблицы (например, В) введите интересующие значение годового семейного дохода. Верхняя ячейка столбца (в данном случае B5) должна оставаться пустой;
3) в соответствующие ячейки пятой строки введите значения количества членов семьи, например, от 1 до 6 (табл.3.4);
в оставленную пустой верхнюю левую ячейку столбца значений годового семейного дохода (B5) введите вычисления годового взноса:
=(C1-(C2*1000))/100
или ссылку на ячейку, где она находится (в нашем случае в ячейке С3). В режиме ввода ссылки в ячейке B5 мы будем видеть ссылку C3, а после ее ввода – значение 170;
выделите интервал ячеек для построения таблицы чувствительности. В этот интервал необходимо включить колонку и строку исходных данных. В нашем случае – указать интервал B5:H22;
используя команду Данные – Таблица подстановки в открывшемся окне укажите местонахождение ячейки ввода столбца (С2) и ячейки ввода строки (С1).
Таблица 3.4
После нажатия «Ок» автоматически будет построена таблица чувствительности (табл. 3.5), которая даст возможность определить годовой взнос в зависимости от годового семейного дохода и количества членов семьи.
При построении данной таблицы используется формула
=ТАБЛИЦА (С1;C2)
Первый аргумент этой формулы представляет собой ссылку на ячейку, в которую подставляются значение из первой строки, выделенной области. Вторым аргументом служит ячейка, в которую подставлены значения из левого столбца этой области.
Построенная таблица чувствительности позволяет фирме определить дифференцируемый уровень ежегодного взноса в зависимости от семейного годового дохода и численности семьи.
Таблица 3.5
