Имитационное моделирование на примере киоска

Осуществим имитационное моделирование и анализ рисков розничной торговли на примере всё того же некоего ларька скоропортящихся товаров. Приступим сразу к созданию модели средствами электронных таблиц. Введём исходные данные, как показано на рисунке (диапазон А1:В6). В дальнейшем эти значения можно будет изменять, как, например, количество закупаемых товаров (решение).

Напомним, продавец может приобрести товар по 40 денежных единиц за штуку и реализовать его в течение дня по 75. А обобщённые потери в условиях превышения спроса составляют 50 единиц на каждого неудовлетворённого покупателя. Если указать спрос в ячейке А9, то дневной результат в ячейке В9 (прибыль или убыток) можно найти с использованием функции ЕСЛИ(), либо по следующей формуле:

=$B$2*МИН($B$6;A9)-$B$1*$B$6-$B$3*МАКС(A9-$B$6;0)

Применение абсолютных ссылок позволит «растянуть» эту формулу на весь диапазон, соответствующий возможным значениям спроса. Предположим, что спрос подчиняется равномерному закону распределения. В этом случае для генерации случайных значений спроса можно использовать встроенные математические функции СЛЧИС() или СЛУЧМЕЖДУ().

Функция СЛЧИС() не имеет аргументов и возвращает равномерно распределенное случайное вещественное число, которое не меньше 0 и меньше 1. Новое случайное вещественное число возвращается при каждом вычислении листа. Для получения целого случайного числа, находящегося в диапазоне между двумя заданными числами, используется функция =СЛУЧМЕЖДУ(нижн_граница; верхн_граница).

Введём в ячейку А9 формулу =СЛУЧМЕЖДУ($B$4;$B$5).

Для того, чтобы сымитировать проведение и представление результатов сразу пятисот экспериментов, нужно скопировать ячейки А9:В9 в диапазон А9:В508. Поясним, как это можно сделать достаточно быстро и для большего количества значений, воспользовавшись командой ленты Главная – Редактирование – Найти и выделить – Перейти (Ctrl+G).

Итак, скопируем ячейки А9:В9 в буфер обмена, выделив их и вызвав команду ленты Главная – Буфер обмена – Копировать (Ctrl+С). Затем щёлкнем ячейку А9, после чего командой Перейти (Ctrl+G) вызываем окно Переход, в поле Ссылка которого вводим адрес В508. Держа нажатой клавишу Shift, щёлкаем ОК (нажимаем Enter). В результате выделится диапазон А9:В508. Командой ленты Главная – Буфер обмена – Вставить (Ctrl+V) заполняем его скопированными ранее формулами.

Вот так, благодаря созданной имитационной модели, без лишних ожиданий и затрат мы получили (псевдо)экспериментальные данные за 500 дней торговли. Добавим ещё несколько формул для анализа. Узнаем, как часто проявляются возможные значения спроса с помощью функции СЧЁТЕСЛИ($A$9:$A$509;D2). Найдём также среднее, максимальное и минимальное значения результата.

Любое изменение исходных данных повлечёт за собой пересчёт листа и получение новых данных. Так, многократно меняя решение о количестве покупаемых товаров, можно убедиться в достаточно адекватном соответствии данной модели Таблице 1, скорректированной для случая равновероятных (р=0,25) событий. Закон больших чисел проявляется в том, что при увеличении числа независимых испытаний, экспериментально получаемые результаты приближаются к теоретическим значениям.

Однако если мы хотим анализировать влияние изменений некоторых исходных данных (например, потерь) на одной и той же случайной выборке, да ещё и при неравномерном распределении, следует воспользоваться возможностями пакета Анализ данных. Чтобы заполнить такими значениями столбец Потери, используем команду ленты Данные – Анализ – Анализ данных. В появившемся окне выберем инструмент Генерация случайных чисел, щёлкнем ОК, заполним поля соответствующего окна.

Приведем необходимые пояснения. Первым заполняемым аргументом диалогового окна "Генерация случайных чисел" является поле "Число переменных". Оно задает количество колонок, в которых будут размещаться сгенерированные в соответствии с заданным законом распределения случайные величины. В нашем примере оно должно содержать 1, так как столбец Потери единственный.

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

Необходимый вид распределения задается путем соответствующего выбора из списка "Распределения". Могут быть получены 7 наиболее распространенных в практическом анализе типов распределений, каждое из которых характеризуется собственными параметрами и определяет внешний вид диалогового окна. В рассматриваемом примере выбор типа распределения "Нормальное" повлек за собой появление дополнительных аргументов – его параметров "Среднее" и "Стандартное отклонение", (укажем 50 и 1). К сожалению, эти аргументы могут быть заданы только в виде констант. Использование адресов ячеек и собственных имен здесь не допускается.

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

Последний аргумент диалогового окна "Генерация случайных чисел" – "Параметры вывода" определяет место расположения полученных результатов (С9:С508). Переключателем задается один из трёх вариантов размещения:

•выходной блок ячеек на текущем листе – введите ссылку на левую верхнюю ячейку выходного диапазона, при этом его размер будет определен автоматически и в случае возможного наложения генерируемых значений на уже имеющиеся данные на экран будет выведено предупреждающее сообщение;

•новый рабочий лист – в рабочей книге будет открыт новый лист, содержащий результаты генерации случайных величин, начиная с ячейки A1;

•новая рабочая книга – будет открыта новая книга с результатами имитации на первом листе.

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

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

К недостаткам рассмотренного подхода следует отнести:

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

•при разработке реальных моделей может возникнуть необходимость привлечения специалистов или научных консультантов со стороны;

•относительную неточность полученных результатов, по сравнению с другими методами численного анализа и др.

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

ОСНОВНЫЕ ПОНЯТИЯ БАЗ ДАННЫХ