Після закінчення виконання роботи студент повинен

ЗНАТИ: яким чином працює надбудова Поиск решения меню Сервис.

ВМІТИ: вирішувати конкретні економічні задачі стосовно діяльності різних підприємств по оптимізації планування витрат на виробництво та вантажопотоків за допомогою засобів MS Excel.

МАТИ УЯВЛЕННЯ: до яких задач можливе застосування надбудови Поиск решения.

ЗАВДАННЯ: Відомий обсяг виробництва на трьох підприємствах: a ₁ =360+N, a ₂ =340-N, a ₃ =200*N, продукція яких знаходить постійний попит у чотирьох замовників у відповідних обсягах: b ₁ =330-N, b ₂ =250+N, b ₃ =120+2N, b ₄ =180+N. Витрати на виготовлення продукції на кожнім підприємстві визначаються величинами: d ₁ =N, d ₂ =8+N, d ₃ =2*N (N – номер студента за списком). Транспортні витрати під час перевезення одиниці продукції від виробника до замовника задаються наступною матрицею:

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

Класична транспортна задача – задача про найбільш економний план перевезень однорідного продукту чи взаємозамінних продуктів з пунктів виробництва в пункти споживання. Такі задачі займають особливе місце серед задач лінійного програмування, що пояснюється актуальністю проблеми транспортних перевезень в економіці. Проте математична структура цієї задачі характерна для великого класу задач лінійного програмування. Їхній реальний зміст може бути найрізноманітнішим, зовсім не зв’язаним із перевезенням вантажів. Транспортна модель широко використовується для розв’язування задач розміщення виробництва, розподілу капіталовкладень, задачі оптимального призначення тощо.

Складемо математичну модель транспортної задачі:

Нехай:

n – кількість пунктів постачальників;

m – кількість пунктів споживачів;

a_i – кількість одиниць товару, яка є в i-му пункті постачання (обсяг виробництва);

b_j – кількість одиниць товару, що потребує j-й пункт споживання (потреби споживачів);

c_ij – транспортні витрати на перевезення одиниці товару з i-го пункту постачання в j-й пункт споживання;

x_ij – кількість одиниць товару, яку планується перевезти з i-го пункту постачання в j-й пункт споживання;

Перший етап. Уведення початкових даних. Подано на рис.3.1.

Рис.3.1. Екран уведення початкових даних

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

Другий етап. Складання оптимального плану перевезення з урахування мінімізації транспортних витрат.

На початку складаємо таблицю „ Кількість перевезеної продукції ”. Її заповнюємо „0”. У комірках F25-F27 підраховуємо суму, яку реалізував кожний виробник. Аналогічно у комірках B28-E28 підраховуємо суму, яку отримав кожний споживач.

Недовиконання – це різниця між обсягом потреби та кількістю наданої споживачу продукції (для кожного споживача - свій обсяг недовиконання).

Залишок – це різниця між обсягом виробництва на підприємстві та кількістю реалізованої продукції (для кожного виробництва підраховується окремо).

Наступна таблиця „ Витрати на транспортування продукції ” підраховується, як добуток кількості перевезеної продукції та витрат на транспортування. Для цього будемо використовувати формулу масиву, яка виконує кілька підрахунків, а потім повертає одне або групу значень:

- виділяємо діапазон комірок B33:E35;

- для вищеназваного діапазону в рядку формул прописуємо: =B17:E19*B25:E27, а потім нажимаємо CTRL+SHIFT+ENTER, формула обмежена {}.

Комірка, у якій підраховуємо сумарні витрати на транспортування – є цільовою. Вікно формування початкових даних надано на рис.3.2.

Рис.3.2.Формування початкових даних для розрахунку

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

Рис.3.3. Вікно надбудови Поиск решения із заданими обмеженнями

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

2. За допомогою перемикача Равной: визначити тип критерію оптимізації: забезпечення мінімального, максимального або певного значення. В останньому випадку слід увести це значення у відповідне поле.

3. У поле Изменяя ячейки: ввести адреси блока комірок, у яких програма має змінювати значення змінних для одержання оптимального результату, і помістити в них розв’язок - оптимальні значення змінних.

4. Для введення обмежень натиснути кнопку Добавить і в діалоговому вікні Добавление ограничения ввести адресу комірки (або діапазону комірок), вміст якої (яких) має задовольняти обмеження, величину та тип обмеження (=, < чи >). Далі натиснути кнопку Добавить для продовження введення обмежень або ОК для завершення.

5. Для зазначення параметрів пошуку розв’язку служить відповідна кнопка у вікні Поиск решения.

Для виконання розрахунку натиснути кнопку Выполнить діалогового вікна Поиск решения. Після завершення розрахунку одержані значення будуть надані в таблицю, і відкриється вікно з повідомленням про завершення пошуку розв’язку.

Після виконання всіх дій, отримуємо значення оптимального плану перевезень, який подано на рис. 3.4.

Рис.3.4. Вікно отриманого оптимального плану

Третій етап. Складання оптимального плану перевезення з урахування мінімізації сумарних витрат на виробництво та перевезення.

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

Рис.3.5.Матриця витрат на виробництво та транспортування продукції

Відповідно до випадку мінімізації витрат на транспортування, складаємо таблицю плану закріплення кількості перевезеної продукції від виробника до споживача. На початку визначаємо кількість перевезеної продукції, як 0. Залишок та недовиконання кількості поставок обчислюємо так само, як на другому етапі. Потім за допомогою формули масиву підрахуємо витрати на виробництво та транспортування всього обсягу продукції – це добуток витрат на одиницю продукції та кількості перевезеної продукції. Це формула =B50:E52*B42:E44. Яку також за допомогою CTRL+SHIFT+ENTER, обмежуємо {}.

Оптимальний план закріплення споживачів до виробництв з обліком мінімуму сумарних витрат на виробництво та перевезення підраховується аналогічно оптимальному плану, отриманому при мінімізації тільки витрат на транспортування. Після виконання всіх дій, отримуємо значення оптимального плану перевезень, який надано на рис.3.6.

Рис.3.6. Вікно отриманого оптимального плану

Зробити аналіз отриманих оптимальних планів перевезень. Порівняти їх. Змінилася чи ні схема взаємозв’язків Виробник-Споживач та чому? Якщо на виробництві існує залишок, що потрібно зробити підприємству? Прийняти правильне рішення по цьому завданню.