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

Цели работы:

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

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

3) иметь представление выработки альтернативы решения на основе сценарного подхода.

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

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

ВЗНОС=(ГОДОВОЙ ДОХОД СЕМЬИ – (КОЛИЧЕСТВО ЧЛЕНОВ СЕМЬИ*1000))/100

В этом случае таблица чувствительности должна показать влияние двух факторов (годового дохода семьи и количество ее членов) на размер ежегодного взноса.

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

Это можно сделать следующим образом:

1) введите переменные задачи, их значения и формулу связи;

2) в один из столбцов электронной таблицы (например, В) введите интересующие значение годового семейного дохода. Верхняя ячейка столбца (в данном случае B5) должна оставаться пустой;

3) в соответствующие ячейки пятой строки введите значения количества членов семьи, например, от 1 до 6 (табл.3.4);

4) в оставленную пустой верхнюю левую ячейку столбца значений годового семейного дохода (B5) введите вычисления годового взноса:

=(C1-(C2*1000))/100

или ссылку на ячейку, где она находится (в нашем случае в ячейке С3). В режиме ввода ссылки в ячейке B5 мы будем видеть ссылку C3, а после ее ввода – значение 170;

5) выделите интервал ячеек для построения таблицы чувствительности. В этот интервал необходимо включить колонку и строку исходных данных. В нашем случае – указать интервал B5: H22;

6) используя команду Д анные – Таблица подстановки в открывшемся окне укажите местонахождение ячейки ввода столбца (С2) и ячейки ввода строки (С1).

Таблица 3.4

После нажатия «Ок» автоматически будет построена таблица чувствительности (табл. 3.5), которая даст возможность определить годовой взнос в зависимости от годового семейного дохода и количества членов семьи.

При построении данной таблицы используется формула

=ТАБЛИЦА (С1; C2)

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

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

Таблица 3.5