Алгоритм решения задачи. Создайте в Excel рабочую таблицу согласно образцу из Приложения 3

Создайте в Excel рабочую таблицу согласно образцу из Приложения 3.

1. Будем имитировать работу ООО "Глобус" в течение 100 дней. В первом столбце таблицы проставлены порядковые номера от 1 до 100. Начальный размер запасов установим равным 20 (ячейка C6). Для всех остальных дней, начиная со второго величина запаса при открытии будет равна остатку запаса на конец предыдущего дня. В ячейку С7 введите "=J6" (остаток на конец первого дня) и скопируйте до конца таблицы.

2. Далее необходимо сгенерировать значения спроса на ближайшие 100 работы предприятия. На отдельном листе создайте вспомогательную таблицу (см. рисунок 7). В таблице должно быть 100 строк. В ячейку С3 введите формулу:

=ЦЕЛОЕ(НОРМОБР(СЛЧИС();$G$10;$G$11)).

Функция НОРМОБР() возвращает значение случайной величины, имеющей нормальный закон распределения по известному значению вероятности. В качестве значений вероятностей используются случайные равномерно распределенные числа, которые генерирует функция СЛЧИС() (она является одним из параметров функции НОРМОБР()). В ячейках G10 и G11 находятся значения математического ожидания и среднеквадратического отклонения величины спроса. Функция НОРМОБР() возвращает вещественное число, а внешняя функция ЦЕЛОЕ() округляет его до ближайшего целого значения, т.к. количество проданных за день глобусов должно быть целым числом.

Рисунок 7. Генерация величины спроса.

Растяните формулу до конца таблицы, получим выборку из 100 реализаций случайной величины спроса. Скопируйте эти данные в столбец №3 основной таблицы. При копировании используйте функцию Excel Специальная вставка (надо скопировать только значения, а не формулу).

3. Сгенерируем случайные значения времени доставки заказов. На отдельном листе рабочей книги Excel создайте еще одну вспомогательную таблицу (см. рисунок 8).

Рис. 8. Генерация времени доставки

Время доставки – это случайная величина с дискретным законом распределения (таблица 6). Запустите инструмент Анализ данных / Генерация случайных чисел, в списке доступных распределений выберите Дискретное и укажите блок ячеек на рабочем листе Excel, в которых указаны все возможные значения случайной величины и соответствующие им вероятности. На рис. 6 эти ячейки выделены зеленым цветом.

В поле Число случайных чисел поставьте значение "20". Это значение выбрано из следующих соображений: ранее мы определили, что при постоянном спросе за период дням будет сделано равно 10 заказов. В том случае, когда спрос является случайной величиной, количество заказов за период времени может отличаться от 10. Но, в любом случае, количество заказов вряд ли будет больше 20.

В поле Выходной интервал укажите блок ячеек $C$3:$C$22 (на рисунке 6 эти ячейки выделены серым цветом). После нажатия на клавишу "ОК", Excel сгенеррирует 20 случайных значений времени доставки заказа (см. рисунок 7).

Рис. 9. Случайная реализация времени доставки заказов.

4. В ячейку J6 введите формулу:

=ЕСЛИ(C6-D6+I6>0;C6-D6+I6;0).

Эта формула вычисляет уровень запасов на конец дня. При этом, если остаток запасов на конец дня больше нуля, то этот остаток отображается в ячейке. В противном случае, отображается 0 (запасы кончились).

5. В ячейку K6 введите формулу:

=ЕСЛИ(J6=0;ABS(C6-D6+I6);"").

Здесь проверяется условие: если спрос на товар в течение дня был больше наличного остатка товара, то в ячейке отображается величина неудовлетворенного спроса (дефицит).

6. В столбце 9 вычислим стоимость хранения запасов за текущий день. Для этого остаток запаса на конец дня умножим на стоимость хранения одной единицы запасов ( ). В ячейку L6 введите формулу:

=J6*0,2.

Скопируйте формулы в ячейках J6, K6, L6 до конца таблицы.

7. Оставшиеся столбцы №4, 5, 6 заполняются вручную. Как только уровень запасов становится ниже (точка заказа), делается заказ очередной партии товара в объеме . Процесс оформления заказов, их поступления и динамику остатка товара на складе можно проследить по таблице 7.

Таблица 7. Табличная имитационная модель работы склада ( ).

№ дня	Величина запасов при открытии	Спрос	Разме-щенные заказы	Время поставки	Полученные заказы	Уровень запасов при закрытии	Дефицит	Стоимость хранения
								3,4
								2,4
			заказ					1,4
								0,6
								8,6
								7,2
								6,6
								6,2
								5,8
								5,4
								2,6
			заказ					2,2
								9,2
								8,6
								7,8
								6,8
								6,2
								4,8
								3,8
								2,6
			заказ					2,6
								1,6
								0,8
								0,2
								9,4
								8,4
								7,8
								7,4
								6,6
								6,2
								5,4
								4,8
								4,2
								3,8
								1,8
			заказ					1,4
								10,6
								9,2
		-2						9,6
								8,2
								7,8
								7,4
								6,6
								4,4
								3,4
								2,2
			заказ					1,2
								8,8
								8,2
								7,4
								6,6
								5,6
								4,6
								3,8
								3,4
								2,8
			заказ					1,8
								0,6
								6,8
								6,2
								5,4
								4,6
								3,8
								3,4
								2,4
			заказ					1,4
								0,6
								9,2
								6,8
								5,2
								4,4
								3,8
								2,4
			заказ					1,2
								0,6
								0,2
								9,4
								8,6
								7,4
		-1						7,6
								6,4
								5,2

Сравним результаты имитации с решением, полученным по модели Уилсона. За период времени было сделано 8 заказов (в детерминированной модели ). Средняя длина цикла дней (в модели Уилсона ). В имитационной модели мы учитывали такую категорию затрат, как потери от неудовлетворенного спроса (дефицит). Пусть функция потерь имеет вид:

, (28)

где - остаток товара на складе в конце дня t; - неудовлетворенный спрос (дефицит) в день t; - прибыль от реализации 1 глобуса.

Суммарные затраты на хранение остатков товара (сумма в столбце №9 таблицы 7). Неудовлетворенный спрос за период времени составляет единиц (сумма значений в столбце №8). Предположим, что прибыль от реализации 1 глобуса составляет 150 руб. Тогда величина общих затрат за 100 дней по (28):

рублей.

Наибольший вклад в величину общих затрат вносят потери от дефицита (150*16=2400 рублей). Значит, чтобы снизить суммарные затраты, необходимо избегать ситуаций, когда на складе нет достаточного количества товара. Добиться этого можно, например, увеличив значение (точка заказа). При этом средняя величина товарных остатков вырастет и это приведет к некоторому росту издержек на хранение запасов, но сокращение потерь от дефицита должно в итоге снизить величину суммарных затрат .

Пусть точка заказа , т.е. если товарный остаток становится меньше или равен 20, оформляется новый заказ на поставку в объеме глобусов. Сгенерируем в Excel новые значения величины спроса и времени доставки и заполним рабочую таблицу. Результаты эксперимента приведены а таблице 8.

Таблица 8. Табличная имитационная модель работы склада ( ).

№ дня	Величина запасов при открытии	Спрос	Разме-щенные заказы	Время поставки	Полученные заказы	Уровень запасов при закрытии	Дефицит	Стоимость хранения
			заказ					2,4
								11,8
								10,2
								9,2
								8,4
								7,6
								5,8
								5,2
			заказ					2,8
								1,8
								0,8
								9,2
								5,8
								5,8
								5,2
								4,4
								3,8
			заказ					2,2
								0,8
								9,2
								6,4
								5,2
								3,6
			заказ
								1,6
								10,6
								9,4
								8,6
								7,4
								6,2
								5,2
								5,2
								4,6
								3,6
			заказ					2,8
								2,4
								1,6
								10,6
								10,6
								9,6
								7,8
								5,8
								5,2
								4,6
								4,4
								3,2
			заказ
								0,8
								9,8
								8,4
								7,6
								6,2
								4,6
								3,8
			заказ					3,4
								2,8
								11,6
								10,8
								9,8
								8,8
								7,2
								6,6
								5,4
								4,4
		-1						4,6
								3,4
			заказ					2,4
								0,8
								7,8
								7,2
								6,2
								5,8
								4,6
								3,4
			заказ					2,8
								1,8
								10,2
								8,8
								7,8
								6,6
								5,6
								4,4
			заказ					2,2

Как видно из таблицы 8, за 100 дней дефицит товара возник только один раз (23-й день работы). Было сделано 10 заказов, при этом последний заказ пришелся на последний день работы и не был доставлен. Суммарные издержки на хранение товаров составили рубля. Общие затраты составили рубля, что гораздо меньше, чем в предыдущем примере.