Пример 3. Необходимо определить оптимальный объем заказа четырех товаров, для каждого из них известны цены для покупки, спрос, затраты на поставку и хранение единицы продукции, а также ограничение на сумму финансирования покупки. Период времени спроса на продукцию τ = 90 дней. Исходные данные приведены в табл. 6.
Таблица 6. Исходные данные
Наименование |
Затраты на хранение единицы товара, у.е. (с2) |
Спрос (N) |
Затраты на поставку единицы товара, у.е. (с1) |
Цена за единицу продукции, у.е. (z) |
Бюджет на покупку, у.е. (V) |
Товар 1 |
25 |
200 |
50 |
200 |
30000 |
Товар 2 |
20 |
325 |
50 |
300 |
|
Товар 3 |
30 |
400 |
50 |
275 |
|
Товар 4 |
15 |
150 |
50 |
400 |
|
1.Введем обозначения для решения задачи:
– оптимальный объем заказа i-го товара, вычисляемый по формуле (7)
c1i – затраты на поставку i-го товара;
C1 – общие затраты на поставку;
с2i – затраты на хранение i-го товара;
С2 – общие затраты на хранение;
Ni – спрос на i-й товар;
ni – реальный объем заказа i-го товара;
V – общая стоимость покупки.
Математическая модель включает:
1) Целевую функцию – общие затраты, включающие затраты на поставку и затраты на хранение товаров:
2) Расчетные формулы:
– общие затраты на поставку
– общие затраты на хранение
3) Ограничения по стоимости покупки (не должны превышать бюджета на покупку):
2.Создаем таблицу с исходными данными в Excel (рис. 9).
Рис. 9. Форма с исходными данными для расчета
3.Для решения создадим форму и введем расчетные формулы из математической модели, которые связывают план, ограничения и целевую функцию (общие затраты) (см. видеозапись "Ввод зависимостей"):
– в ячейки В9–В12 запишем начальные значения размера заказа ni=1;
– в ячейки С9–С12 введем расчет оптимального объема заказа для каждого товара по формуле (7);
– в ячейках D9–D12 рассчитаем стоимость покупки каждого товара, а в ячейке D13 их сумму для вычисления общей стоимости покупки V (формула (12));
– в ячейках Е9–Е12 рассчитаем затраты на поставку каждого товара, а в ячейке Е13 их сумму для вычисления общей стоимости поставки С1 (формула (14));
– в ячейках F9–F12 рассчитаем затраты на хранение каждого товара, а в ячейке F13 их сумму для вычисления общей стоимости хранения С2 (формула (15));
– в ячейках G9–G12 рассчитаем затраты на поставку и затраты на хранение каждого товара, а в ячейке G13 их сумму для вычисления общей стоимости С (формула (16)).
4.Получим таблицу начальных расчетов (рис.10).
Рис. 10. Окно начальных расчетов
В принципе в исходной модели уже получено оптимальное решение по формуле Уильсона, но фирма не имеет нужного количества денег (53795 у.е.), поэтому возникает необходимость получить оптимальное решение в пределах ограниченности денежных ресурсов (30000).
5.Настроим параметры модели в Поиск решения:
-целевая функция ячейка G13 (общие затраты) – решение на минимум.
-изменяемые ячейки B90:B12 (размер заказа).
-ограничения D13 ≤ F3 (суммарная стоимость поставок товаров не должна превышать 30000 у.е.)
(см. видеозапись "Поиск решения")
6.После нажатия кнопки Выполнить, получим результат решения задачи (рис. 11).
Рис. 11. Результаты работы
Анализ результата показывает, что объемы полученных заказов меньше оптимальных (ЭРЗ), поскольку они ограничены бюджетом финансирования покупки товаров.
На основе модели можно рассчитать периодичность поставок:
-количество оптимальных поставок k0 за промежуток τ (контрольный период) при размере заказа n
-оптимальный период поставок Т0:
Для этого создадим в Excel таблицу и введем расчетные зависимости , величины k0 следует округлить до целых значений (см. видеозапись "Расчет периодичности поставок").
Получим результат (рис. 12).
Рис. 12. Результаты расчета периодичности поставок
Таким образом, получили основные характеристики заказа товаров при ограниченной стоимости поставок.