Практическое занятие 1

Построение простейшей имитационной модели

1 Цель занятия:

1.1 Изучение технологии реализации имитационного моделирования с помощью табличного процессора.

1.2 Формирование навыков работы по организации расчетно-аналитичес-кой работы.

2 Литература:

2.1 Строгалев В.П., Толкачева И.О. Имитационное моделирование. - МГТУ им. Баумана, 2008.

2.2 Боев В.Д., Сыпченко Р.П.. Компьютерное моделирование. - ИНТУИТ.РУ, 2010.

2.3 Афонин В.В., Федосин С.А. Моделирование систем БИНОМ: Лаборатория знаний, Интернет-университет информационных технологий - ИНТУИТ.ру, 2010.

3 Теоретические сведения:

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

Стохастические переменные - переменные, точные значения которых не могут быть точно установлены или предсказаны. Так, стохастическими переменными при моделировании обычно считают цены сырья, арендную плату, потребности в рабочей силе, спрос на различные виды товаров и услуг. Если одна или несколько входных независимых переменных нашей модели являются стохастическими, есть все основания считать такими же стохастическими переменными и выходные зависимые переменные модели. Такая образом, если одна или несколько независимых переменных модели вводит в процесс принятия решений (поддерживаемый этой моделью), то существует элемент риска. (Имеется в виду, что когда такое решение будет принято, будет существовать вероятность того, что оно приведет к иным, чем ожидалось, результатам. Эта вероятность и составляет элемент риска при принятии решений). Термином «Риск» часто выражают потенциальные потери. Однако, факт того, что последствия принятого решения носят случайный характер, еще не означает безусловной рискованности данного решения.

Имитационное моделирование представляет собой технику оценки параметров выходной переменной модели, когда несколько или все ее входные переменные точно не определены. Если несколько или все входные переменные модели считать стохастическими, то их композиция - выходная переменная модели (Y в выражении 1) также будет стохастической.

Целью имитационного моделирования является описание распределения и характеристик возможных значений выходной стохастической переменной Y при случайном изменении входных стохастических переменных X, (i=1,2,…k).

В основе имитационного моделирования лежит метод многократного решения задач “Что будет, если?” Отличие заключается в том, что определение и запись в ячейки входных переменных стохастических переменных автоматизирована. Используя технологию имитационного моделирования, появляется возможность непрерывно и случайным образом генерировать значения каждой входной переменной X, (1=1,2,…к) модели и затем рассчитывать значения каждой выходной переменной Y. Затем, используя полученные значения переменной Y, можно оценивать закон распределения и его параметры для этой стохастической переменной. Например, изменяя случайным образом входные параметры, можно получить некоторое количество значений выходной переменной (выборку), затем построить частотное распределение встречающихся в выборке значений, определить пределы изменения выходной переменной, оценить среднее значение и дисперсию полученного распределения и, наконец, оценить вероятность того, что фактическое значение выходной переменной не будет выше (меньше), чем заданная величина. Все эти параметры дают возможность менеджеру точнее оценить риск, связанный с принимаемым им решением.

4 Задание:

4.1 Задача.

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

Пусть в компании в настоящий момент работает 18533 человека, каждый из которых отчисляет в месяц на медицинское страхование $125. При этом компания в год имеет большую текучесть кадров, поэтому количество работающих в ней постоянно меняется. Среднемесячная величина затрат на медицинское обслуживание одного работника в месяц составила $250. Поскольку исходные данные меняются в течение года, примем следующие допущения: количество служащих, пользующихся медицинской страховкой, будет увеличиваться на 2% каждый месяц, при росте индивидуальных потребностей в медицинском обслуживании на 1% а месяц.

На рисунке 1.1 приведена модель рассмотренной ситуации.

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

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 суммируются месячные доплаты компании, и определяется годовая потребность компании в средствах для покрытия медицинских расходов.

Рисунок 1.1 - Исходные данные

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

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

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

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

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

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

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

1) выделите блок А3:B302;

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

3) в открывшемся диалоговом окне в качестве ячейки ввода строки укажите ячейку А1;

4) нажмите ОК.

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

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

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

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

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

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

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

Анализ данных.

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

1) войдите с меню Сервис, выберите режим Дополнения и пометьте среди дополнительных возможностей пакет анализа;

2) выберите режим Анализ данных меню Сервис/дополнения. В открывшемся диалоговом окне необходимо выбрать режим Описательная статистика;

3) введите данные в открывшееся диалоговое окно. В качестве входного интервала укажите диапазон $В$B2:$B$302, метки на первой строке, уровень надежности - 95%, выходной интервал $D$2, итоговая статистика.

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

1) выберите режим Анализ Данных в меню Сервис;

2) в открывшемся диалоговом окне выберите режим Гистограмма;

3) заполните соответствующими данными диалоговое окно Гистограмма по колонке Доплата компании.

В результате на новом листе под именем гистограмма появится частотное распределение случайной величины «Доплата компании» и иллюстрирующий его график.

Столбец «Частота» содержит количество повторений значений, попавших в «Карман» (находящихся в выборке значений). Столбец «Интегральный %» показывает процент, который соответствует количеству значений в выборке меньших или равных величинам, указанным в столбце «Карман».

По показателю интегрального процента можно сделать следующие выводы по определению бюджета компании. Так, если допустить, что только 10% вероятности того, что денег на медицинскую страховку не хватит до конца года, в ячейке, где интегральный процент около или чуть больше 90% таблицы построения частотного распределения и гистограммы находятся данные для определения потребной для этого суммы. Пусть, например, этому значению соответствует 38900567, следовательно, только 10% из 300 значений будут превышать эту сумму. Таким образом, если выбрать бюджет компании, используемый для покрытия расходов по медицинскому страхованию работников, равный 38900567, будет существовать около 10 % вероятность того, что эта сумма будет превышена.

Нужно принимать во внимание при этом, что расчеты по определению параметров распределений случайных величин, проводимые на основе исследования некоторой выборки ее значений (как в рамках имитационного моделирования, так и вне его), всегда содержат в себе элемент неопределенности (ошибки). Так, на самом деле мы определяем не среднее значение случайной величины «Доплата компании», а лишь пытаемся получить оценку этого параметра, используя некоторую выборку значений. Эта оценка будет зависеть от значений, попавших в выборку, и от размера последней. Чем больше размер выборки, тем точнее получаемый результат.

5 Содержание отчета:

5.1 Наименование и цель работы.

5.2 Выполненное задание.

5.3 Ответы на контрольные вопросы.

6 Контрольные вопросы:

6.1 Что называется имитационным моделированием?

6.2 Указать цель имитационного моделирования.

6.3 Как вычислить доплату компании?

6.4 Как стабилизировать изменяющиеся переменные?

6.5 Как скопировать данные с одного листа на другой, сохраняя изменяемость и зависимость от данных на исходном листе.