В Таблиця 1 аріант з

Р

Таблиця 2

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

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

Д

Таблиця З

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

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

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

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

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

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

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

6. Сформуйте рядок «Витрати на перевезення» в області В21 :G21, де для кожного магазину розрахуйте витрати на перевезення молока в магазин з усіх молокозаводів. При цьому використайте функцію СУММПРОИЗВО і процедуру автозаповнення.

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

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

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

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

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

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

13. У комірку В24 введіть текст «Сумарний прибуток» і обчисліть його в комірці D24.

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

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

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

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

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

Варіант 4

Р

Таблиця. Товари

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

Дані про асортимент товарів, прибуток і поточний план виробництва наведено в таблиці «Товари».

Інформацію про наявні ресурси наведено в таблиці «Ресурси».

П

Таблиця. Ресурси

Таблиця. Технологічні обмеження

отреби в ресурсах для виробництва 1 л соку наведено в таблиці «Технологічні обмеження».

1

Таблиця. Планові затрати ресурсів

. Побудуйте таблицю «Товари» в області B3:F12. При цьому стовпчик «Проектний план виробництва» заповніть довільними числами.

2. Побудуйте таблиці «Ресурси» та «Технологічні обмеження» в області відповідно НЗ:К10 та M2:W10.

3. В області 114:К21 побудуйте таблицю «Планові затрати ресурсів». При цьому обчислення в стовпчиках «Поточні затрати» та «Проектні затрати» здійсніть так:

• Виділіть область Л 5:J21, введіть формулу =МУМНОЖ($О$4:$\¥$10;Е4:Е12) і натисніть комбінацію клавіш <Ctrl+Shift+Enter>;

• скопіюйте цю формулу в область К15:К21, використовуючи процедуру автозаповнення.

4. У комірці В23 наберіть текст «Прибуток від реалізації плану». Використовуючи функцію СУММПРОИЗВО, у комірках Е23 та F23 обчисліть прибутки від реалізації поточного та проектного планів (параметри функції – колонка значень прибутків від реалізації 1 л товарів та колонка значень відповідного плану).

5. Запустіть Поиск решения з такими параметрами:

• цільова комірка F23;

• задача максимізації значення цільової комірки;

• змінні параметри задачі – проектний план F4:F12;

• обмеження – значення проектного плану невід’ємні, проектні затрати ресурсів не перевищують запасів ресурсів.

6. Складіть звіт про стійкість розв’язку. Які ресурси потрібно купувати насамперед для збільшення обсягів виробництва за умови прийняття проектного плану?

7. У комірках В24, С24, С25 наберіть текст відповідно «Проектне збільшення прибутку», «абсолютне» та «відносне». Обчисліть абсолютне та відносне збільшення сумарного прибутку в разі заміни поточного плану на оптимальний у комірках F24, F25. Для цього використайте формули відповідно =F23-E23 та =F24/E23.

8. Зробіть висновок. Який план потрібно вибрати: поточний чи знайдений оптимальний?

Варіант 5

Розгляньте таку проблему. Ви працюєте менеджером на заводі з виробництва продуктів дитячого харчування. Потрібно проаналізувати поточний (на наступний день) план виробництва пюре для малят і, можливо, внести пропозиції щодо покращення цього плану.

Д

Таблиця. Товари

ані про асортимент товарів, прибуток і поточний план виробництва наведені в таблиці «Товари».

Інформацію про наявні ресурси наведено в таблиці «Ресурси».

П

Таблиця. Ресурси

Таблиця. Технологічні обмеження

отреби в ресурсах для виробництва 1 кг пюре наведені в таблиці «Технологічні обмеження».

1. Виконайте завдання 1-2 варіанта 4.

2. Виконайте завдання 3 варіанта 4, використавши таблицю «Планові затрати ресурсів».

3. Виконайте завдання 4-8 варіанта 4.

Варіант 6

Розгляньте таку проблему. Ви працюєте менеджером на заводі з виробництва продуктів консервації. Потрібно проаналізувати поточний (на наступний день) план виробництва консервації і, можливо, внести пропозиції щодо покращення цього плану.

Дані про асортимент товарів, прибуток і поточний план виробництва наведені в таблиці «Товари».

І

Таблиця. Товари

нформацію про наявні ресурси наведено в таблиці «Ресурси».

П

Таблиця. Ресурси

отреби в ресурсах для виробництва 1 кг консервації наведено в таблиці «Технологічні обмеження».

1

Таблиця. Технологічні обмеження

. Виконайте завдання 1-2 варіанта 4.

2. Виконайте завдання 3 варіанта 4, використавши таблицю «Планові затрати ресурсів».

3. Виконайте завдання 4-8 варіанта 4.