В таблице приведена модель рассмотренной ситуации.

Модель начинается с исходных данных и вводимых допущений.

1.Используя введенные параметры допущений разработайте формулу для расчета количества служащих по месяцам (чтобы поместить их в ячейки В8:В19)и индивидуальной месячной потребности в медицинском обслуживании (чтобы поместить их в ячейки D8:D19). Для этого рекомендуется воспользоваться функцией СЛЧИСЛ() - генератор случайных чисел. Эта функция генерирует одинаковым образом распределенные числа, находящиеся в интервале от 0,0 до 1,0. Функция ЦЕЛОЕ позволяет брать целую часть от числа.

2.Для расчета индивидуальной месячной потребности в медицинском обслуживании (чтобы поместить их в ячейки D8:D19) используется нормальное распределение. Этот закон распределения задается двумя параметрами: средним значением μ, соответствующее максимальной вероятности и среднеквадратическим отклонением σ. Формула для вычисления индивидуальной месячной потребности будет иметь вид:

=НОРМОБР(СЛЧИСЛ(),$D$3*(1+$F$3)^A8,$H$3).

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

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

5.В ячейке G20 суммируются месячные доплаты компании и определяется годовая потребность компании в средствах для покрытия медицинских расходов.

Таблица.

Имитационное исследование модели.

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

Для получения 300 реализаций (решений)модели, сохранив при этом полученные результаты на специальном рабочем листе, который назовем «Имитация». Для этого необходимо проделать следующие операции:

1.В меню Вставка выберите Рабочий лист. EXCEL вставит в рабочую книгу новый рабочий лист.

2.В меню Формат выберите режим Лист –Переименовать, введите название нового листа «Имитация».

Для 300 пересчетов модели, необходимо поместить номера с 1…300 в первый столбе, начиная с ячейки А3. Затем перенесите значения суммарных доплат компании из ячейки G20 модели, находящейся на листе 1, в ячейку В3 листа «Имитация» следующую формулу =Лист1!G20.

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

1.Выделите блок А3:B302

2.Выберите режим таблица в меню Данные.

3.В открывшемся диалоговом окне в качестве ячейки ввода строки укажите ячейку А1. Нажмите ОК.

После выполнения команды получится список из 300 возможных значений случайной переменной «Доплата компании».Поскольку все ячейки столбца В, начиная с В4, содержат формулу =;ТАБЛИЦА(;А1), каждый раз при пересчете таблицы получаемые значения будут изменяться, и будут получаться 300 новых возможных значений случайной переменной «Доплата компании». Если необходимо сохранить от изменений, полученный в столбце В набор значений, следует заменить содержащиеся в его ячейках формулы их значениями следующим образом:

1. выделить блок В3-В302.

2. Выбрать режим копировать в меню Правка.

3. Выбрать режим Специальная вставка в меню Правка.

4. В открывшемся диалоговом окне выбрать режим Вставить/Значения.

5. Нажать на клавишу ввода.

 

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