ИМИТАЦИОННОЕ МОДЕЛИРОВАНИЕ ИНСТРУМЕНТОМ

"ГЕНЕРАТОР СЛУЧАЙНЫХ ЧИСЕЛ";

Инструмент «Генератор случайных чисел» предназначен для автоматической генерации множества данных (генеральной совокупности) заданного объема, элементы которого характеризуются определенным распределением вероятностей. При этом могут быть использованы 7 типов распределений: равномерное, нормальное, Бернулли, Пуассона, биномиальное, модельное и дискретное. Применение инструмента «Генератор случайных чисел», как и большинства используемых в этой работе функций, требует установки специального дополнения «Пакет анализа», - меню: «Сервис», «Надстройки».

Для демонстрации техники применения инструмента «Генератор случайных чисел» изменим условия примера, рассмотренного на практическом занятии: «Имитационное моделирование с применением встроенных функций генерации случайных чисел ППП Excel»- практическое занятие №1, определив вероятности для каждого сценария развития событий следующим образом (табл.8). Будем также исходить из предположения о нормальном распределении ключевых переменных. Количество имитаций оставим прежним - 500.

 

Таблица 8.

Вероятностные сценарии реализации проекта

Показатели	Сценарий
Наихудший P = 0.25	Наилучший P = 0.25	Вероятный P = 0.5
Объем выпуска - Q
Цена за штуку - P
Переменные затраты - V

 

Приступим к формированию шаблона. Выделим в рабочей книге два листа: "Имитация" и "Результаты анализа".

Формирование шаблона целесообразно начать с листа "Результаты анализа" (рис.8.).

Рис. 8. Лист "Результаты анализа"

 

Как следует из рис.8 этот лист практически соответствует ранее разработанному для решения задачи на практическом занятии №1. Отличие составляют лишь формулы для расчета вероятностей, которые приведены в табл.9.

 

 

Используемые в листе собственные имена ячеек также взяты из аналогичного листа шаблона практического занятия №1. (см. табл. 7).

Перейдите к следующему листу и присвойте ему имя - "Имитация".

 

Приступаем к его формированию (рис.9).

Рис. 9. Лист "Имитация"

 

Первая часть этого листа (блок ячеек А1:Е10) предназначена для ввода исходных данных и расчета необходимых параметров их распределений. Напомним, что нормальное распределение случайной величины характеризуется двумя параметрами - математическим ожиданием (средним) и стандартным отклонением. Формулы расчета указанных параметров для ключевых переменных модели заданы в блоках ячеек В7:D7 и B8:D8 соответственно (табл.11). Для удобства определения формул и повышения их наглядности блоку ячеек Е3:Е5 присвоено имя "Вероятности"; (табл.10).

 

Таблица 10.

Имена ячеек листа "Имитация"

Адрес ячейки	Имя	Комментарии
Блок Е3:Е5	Вероятности	Вероятность значения параметра
Блок A13:A512	Перем.расх.	Переменные расходы
Блок B13:B512	Количество	Объем выпуска
Блок C13:C512	Цена	Цена изделия
Блок D13:D512	Поступления	Поступления от проекта NCF
Блок E13:E512	ЧСС	Чистая современная стоимость NPV

 

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

Ячейки D13:E13 содержат уже знакомые нам формулы для расчета величины потока платежей NCF и его чистой современной стоимости NPV.

Сформируйте элементы оформления листа "Имитация", определите необходимые имена для блоков ячеек (табл.10) и задайте требуемые формулы (табл.11). Сверьте полученную ЭТ с рис.9. Сохраните полученный шаблон в своей рабочей папке.

Введите исходные значения постоянных переменных (табл.2 практического занятия №1) в ячейки В2:В4 и D2:D4 листа "Результаты анализа". Перейдите к листу "Имитация". Введите значения ключевых переменных и соответствующие вероятности (табл.8). Полученная в результате ЭТ должна иметь вид рис.10.

Рис. 10. Лист "Имитация" после ввода исходных данных