Пример 1. Определение оптимального заказа

Компания, занимающаяся продажей обуви, собирается заказать производителю партию мужских зимних ботинок. Необходимо определить оптимальный объём заказа З. Положим, стоимость пары ботинок: Ст=1 500 р. Спрос (Сп) на обувь точно предсказать невозможно. Анализ спроса в предыдущие годы показывает, что он может колебаться от минимального значения − 25 000 пар до максимального – 40 000 пар. Наиболее вероятное значение – 30 000 пар за сезон. Вероятность того или иного значения Сп может быть приблизительно описана треугольной функцией распределения. Цена (Ц), которая может быть установлена за пару ботинок, зависит от нескольких факторов: моды, конкуренции, инфляции и др. Допустим, что цена может принимать значения в диапазоне от 1 800 до 2 100 р. за пару с равной вероятностью. Залежавшуюся обувь можно будет распродать по цене распродажи (ЦР) 1 000 р. за пару. Доход (Д), полученный после продажи, можно определить как:

Д=(З-О)Ц+О·ЦР-ЗСт, (2.8)

г

Рис. 2.4. Расчет величины дохода

На рис. 2.4 представлена модель, составленная при помощи электронной таблицы Excel. В ячейке B3 записан предполагаемый объём заказа, в ячейке B4 – стоимость одой пары ботинок. В ячейку B6 занесена формула для определения спроса:

=ЦЕЛОЕ(ЕСЛИ(СЛЧИС()<=5000/15000;25000+5000

КОРЕНЬ(СЛЧИС());40000-10000КОРЕНЬ(СЛЧИС()))). (2.9)

В ячейке B7 вычисляется количество нераспроданных пар ботинок по формуле

=ЕСЛИ(B3>B6;B3-B6;0). (2.10)

В ячейке В8 вычисляется вторая случайная величина – цена за пару ботинок по формуле:

=1800+ЦЕЛОЕ(301СЛЧИС()). (2.11)

В ячейку В9 занесена цена за пару ботинок при распродаже и, наконец, в ячейке В10 вычисляется общий доход в соответствии с формулой (2.8)

=(B3-В7)B8+B7B9-B3B4. (2.12)

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

Пока мы можем наблюдать значение величины дохода при двух значениях случайных величин – спроса и цены. Этого нам явно недостаточно. Чтобы использовать статистические методы обработки данных, надо выполнить некоторое число прогонов модели и записать результаты. Делается это следующим образом. Откроем новый лист Excel (рис. 2.5) и пронумеруем ячейки от А4 до А23 включительно цифрами от 1 до 20. Эти цифры будут означать номер прогона модели или репликации.

Рис. 2.5. Результаты расчетов и статистического анализа

В ячейку B4 скопируем содержимое ячейки В6 первого листа таблицы. Сделать это можно, записав в ячейку В4 =Лист!В6 или воспользовавшись командами Копировать и Специальная вставка меню Правка. В открывшемся диалоговом окне Специальная вставка надо нажать кнопку Вставить связь (ОК). Аналогично, в ячейки С4 и D4 второго листа скопируем содержимое ячеек В8 и В10 первого листа. Далее, выделим ячейки А4:D23. В меню Данные выполним команду: Таблица подстановки, в появившемся диалоговом окне в графе Подставлять значения по строкам в: запишем адрес любой свободной ячейки и нажмём OK.

В

Рис. 2.6. Диалоговое окно функции статистической обработки данных