Варіант 3. Розгляньте таку проблему

Розгляньте таку проблему. Ви працюєте менеджером невеликої акціонерної компанії з переробки сільськогосподарської продукції. Компанія має три молокозаводи. Собівартість виробництва 1 л молока та денні потужності цих заводів наведені в табл. 1.

Таблиця 1

Компанія має контракти на постачання своєї продукції до п'яти магазинів. Потреби цих магазинів у молоці наведені в табл. 2.

Таблиця 2

Дані про транспортні витрати на перевезення 1 л молока наведені в табл. 3.

Таблиця 3

Потрібно скласти оптимальний план перевезення продукції з молокозаводів у магазини, якщо за критерій оптимальності вибрати: а) мінімальність транспортних витрат; б) максимальність прибутку.

1. В області B3:G7 побудуйте таблицю проектних денних обсягів молока, що перевозитиметься з кожного молокозаводу в кожний магазин (тобто денних виробничих потужностей) у форматі табл. 3.

При цьому початкові проектні обсяги вважайте такими, що дорівнюють 1 л, і введіть їх в область C5:G7. У комірку В2 введіть заголовок таблиці.

10.До створеної таблиці додайте стовпчик "Разом" у графі Н і розрахуйте суми за рядками. Далі додайте рядок "Разом" у рядку 8 і обчисліть сумарні обсяги перевезення для кожного магазину.

11.В область J4:L7 введіть дані табл. 1.

12.В область B10:G12 введіть дані табл. 2.

13.В області B15:G19 побудуйте табл. 3. У комірку В14 введіть заголовок таблиці.

14.Сформуйте рядок "Витрати на перевезення" в області B21:G21, де для кожного магазину розрахуйте витрати на перевезення молока в магазин з усіх молокозаводів. При цьому використайте функцію СУMMПРОИЗВ() і процедуру автозаповнення.

15.У комірку В22 введіть текст "Сумарні транспортні витрати" і обчисліть їх у комірці D22.

16.Запустіть Поиск решения.

17.Заповніть параметри Поиск решения: цільову комірку D22, задачу мінімізації; змінні параметри "проектні обсяги перевезення" розміщуються в області C5:G7 (див. п.1). Створіть такі обмеження: проектні обсяги перевезення цілі й невід'ємні, сумарні обсяги перевезення з кожного молокозаводу (область Н5:Н7)не перевищують максимальні виробничі потужності, сумарні поставки в кожний магазин (область C8:G8) не менші від потреб магазинів.

15.Натисніть кнопку Параметры. Виберіть лінійну модель. Збережіть цю оптимізаційну модель в області В26:В32. Поверніться до основного вікна Поиск решения.

16.Розв'яжіть описану оптимізаційну задачу.

17.Для розв'язання задачі б) максимізації прибутку в області С23:G23розрахуйте прибутки від постачання молоком кожного магазину. При цьому від сумарних обсягів реалізації молока в магазин відніміть вартість перевезень і вартість виробництва реалізованого до магазину молока. Використайте функцію СУММПРОИЗВ() і процедуру автозаповнення.

13. У комірку В24 введіть текст "Сумарний прибуток" і обчисліть його в комірці D24. 14. Запустіть Поиск решения.

1.Заповніть параметри Поиск решения: цільову комірку D24, задачу максимізації. Змінні параметри задачі та обмеження залишіть такими самими, як у попередній задачі,

2.Збережіть модель в області F26:F32.

3.Розв'яжіть задачу максимізації прибутків. Створіть звіт Результаты для цієї задачі.

4.Порівняйте результати, отримані за двома оптимізаційними моделями, і зробіть висновки.