4. Звіт з лабораторної роботи повинен містити

• вихідні дані;

• результати розрахунків оптимальних витрат ресурсів за двома варіантами умов роботи підприємства;

• висновки.

5. Приклад оптимізації витрат ресурсів

Є наступна інформація про виробництво на меблевій фабриці:

1. освоєно виробництво чотирьох видів виробів (столи письмові, шафи книжкові, шафи посудні, шафи платтяні);

2. для виробництва продукції підприємство має у своєму розпорядженні основні ресурси:

1) деревина – 5800 м³;

2) трудові ресурси – 9800 чол.-год.;

3) фінансові ресурси – 12000 грн;

3. витрата ресурсів на одиницю виробів:

1) стіл письмовий – 1,8 м³ деревини, 3,5 чол.-год. трудових ресурсів, 7,3 грн фінансових ресурсів;

2) шафа книжкова – 2,4 м³ деревини, 3 чол.-год. трудових ресурсів, 6,5 грн фінансових ресурсів;

3) шафа посудна – 2,8 м³ деревини, 7 чол.-год. трудових ресурсів, 5,8 грн фінансових ресурсів;

4) шафа платтяна – 2,5 м³ деревини, 6 чол.-год. трудових ресурсів, 5,4 грн фінансових ресурсів;

4. прибуток від реалізації одиниці продукції:

1) стіл письмовий – 29,8 грн;

2) шафа книжкова – 26,5 грн;

3) шафа посудна – 27,5 грн;

4) шафа платтяна – 24,6 грн.

Для максимізації прибутку необхідно оптимізувати витрати наявних ресурсів, виходячи з наступних умов на ринку збуту, виявлених службою маркетингу:

– варіант 1: продукція підприємства в будь-яких кількостях затребувана покупцями;

– варіант 2:

1) попит на шафи книжкові і шафи посудні буде обмежений 800 і 950 одиницями виробів відповідно;

2) у силу вже укладених договорів столів письмових і шаф платтяних необхідно випустити не менш 10 і 20 одиниць відповідно, а в цілому прогнозується необмежений попит на ці види виробів.

1. Складаємо допоміжні таблиці:

• матриця витрати ресурсів на одиницю продукції ( ) і їх загальний обсяг

Ресурси	Стіл письмовий	Шафа книжкова	Шафа посудна	Шафа платтяна	Загальний обсяг ресурсів,
Деревина, м3	1,8	2,4	2,8	2,5	5800
Трудові ресурси, чол.-год.	3,5	3	7	6	9800
Фінансові ресурси, грн	7,3	6,5	5,8	5,4	12000

• коефіцієнти цільової функциії

  Прибуток від реалізації одного продукції, грн
  	29,8	26,5	27,5	24,6

• шукані невідомі

  Плановий обсяг виробництва, од.

• цільова функція (у комірку заносимо формулу цільової функції, використовуючи убудовану функцію СУММПРОИЗВ(...) з категорії «Математичні» майстра функцій; як масив 1 вказуємо дані планового обсягу виробництва, а як масив 2 вказуємо дані про прибуток від реалізації одниниці продукції)

  Прибуток, грн

• витрати ресурсів (у таблицю заносимо формули планових витрат ресурсів, для чого використовуємо функцію СУММПРОИЗВ(...) з категорії «Математичні» майстри функцій; як масив 1 вказуємо плановий обсяг виробництва, а як масив 2 – питомі витрати відповідних ресурсів)

Ресурси	Планові витрати ресурсів	Умова	Загальний обсяг ресурсів
Деревина, м3		…	…
Трудові ресурси, чол.-год.		…	…
Фінансові ресурси, грн		…	…

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

Варіант 1. Для цього:

1) викликаємо з пункту головного меню «Сервіс» вкладку «Пошук рішення»;

2) заповнюємо поля вікна, що з'явилось:

– як цільову функцію вказуємо комірку таблиці «Цільова функція» (на момент занесення містить формулу та дорівнює 0);

– указуємо, що функцію необхідно максимізувати;

– як змінювані комірки вказуємо комірки таблиці «Шукані невідомі» (на момент занесення порожні);

– заносимо обмеження по витратам ресурсів, для чого натискаємо кнопку «Додати» і вказуємо:

= у полі «посилання на комірку» вказуємо комірки «Планові витрати виробництва» таблиці «Витрати ресурсів» (на момент занесення містять формули та дорівнюють 0);

• указуємо умову « »;

• у полі «обмеження» вказуємо комірки «Загальний обсяг ресурсів» таблиці «Витрати ресурсів», натискаємо «ОК»;

  • –заносимо умову невід’ємності, для чого натискаємо кнопку «Додати» і вказуємо:

• у полі «посилання на комірку» вказуємо комірки «Плановий обсяг виробництва» з таблиці «Шукані невідомі»;

• указуємо умову « »,

= у полі «обмеження» вказуємо 0, натискаємо «ОК».

3. натискаємо кнопку «виконати» і отримуємо наступні результати:

   • шукані невідомі

Плановий обсяг виробництва, од.
	4	961	984	2

• цільова функція

  Прибуток, грн

  52694,9

• витрати ресурсів

Ресурси
Деревина, м3	5073,8
Трудові ресурси, чол.-год.	9797
Фінансові ресурси, грн	11993,7

4) виконуємо аналіз розв’язку, для чого визначаємо «тіньові» ціни ресурсів:

– деревина витрачена нецілком, отже, її тіньова ціна дорівнює 0;

– збільшуємо на 1 чол.-год. обсяг наявних трудових ресурсів (тобто 9801 чол.-год.) і в «Пошуку рішень» натискаємо кнопку «виконати». Одержуємо цільову комірку рівну 52704,4 грн, що на 9,5 грн більше прибутку при наявному обсязі ресурсів;

– збільшуємо на 1 грн обсяг наявних фінансових ресурсів (тобто 12001 грн) і в «Пошуку рішень» натискаємо кнопку «виконати». Одержуємо цільову комірку рівну 52714 грн, що на 19,1 грн більше прибутку при наявному обсязі ресурсів.

Висновок 1: підприємству необхідно випускати продукцію в наступній кількості: столи письмові – 4 одиниці; шафи книжкові, посудні і платтяні відповідно 961, 984 і 2 одиниці. Дотримання такої структури виробництва дозволить дістати максимальний прибуток у сумі 52694,9 грн. Стримуючими факторами для росту обсягів виробництва і прибутку є обмежені трудові і фінансові ресурси, при цьому «цінність» фінансових ресурсів приблизно в 2 рази вище, ніж трудових, і свідчить про необхідність залучення інвестицій в розвиток виробництва.

Вариант 2. Для врахування ринкових умов в «Пошуку рішень» до вже введених обмежень додаємо наступні:

1) заносимо умову за нижніми межами обсягу виробництва письмових столів, для чого натискаємо кнопку «Додати» і вказуємо:

• -у полі «посилання на комірку» вказуємо комірку таблиці «Шукані невідомі»;

• вказуємо умову 10, натискаємо «ОК»;

• аналогічним чином заносимо умови за нижніми і верхніми межами обсягів виробництва інших видів продукції ( – для шаф книжкових; – для шаф посудних; – для шаф платтяних);

  2. натискаємо кнопку «виконати» і отримуємо наступні результати:

     • шукані невідомі

Плановий обсяг виробництва, од.
	163	783	950	38

• цільова функція

  Прибуток, грн

  52666,7

• витрати ресурсів

Ресурси
Деревина, м3	4927,6
Трудові ресурси, чол.-год.	9797,5
Фінансові ресурси, грн	11994,6

3) виконуємо аналіз розв’язку, для чого визначаємо «тіньові» ціни ресурсів:

– деревина витрачена нецілком, отже її тіньова ціна дорівнює 0;

– збільшуємо на 1 чол.-год. обсяг наявних трудових ресурсів (тобто 9801 чол.-год.) і в «Пошуку рішень» натискаємо кнопку «виконати». Одержуємо цільову комірку рівну 52687,1 грн, що на 20,4 грн більше прибутку при наявному обсязі ресурсів;

– збільшуємо на 1 грн обсяг наявних фінансових ресурсів (тобто 12001 грн) і в «Пошуку рішень» натискаємо кнопку «виконати». Одержуємо цільову комірку рівну 52680,0 грн, що на 13,3 грн більше прибутку при наявному обсязі ресурсів.

Висновок 2: підприємству необхідно випускати продукцію в наступній кількості: столи письмові – 163 одиниці; шафи книжкові, посудні і платтяні відповідно 783, 950 і 38 одиниць. Дотримання такої структури виробництва дозволить дістати максимальний прибуток у сумі 52666,7 грн. Стримуючими факторами для росту обсягів виробництва і прибутку є обмежені трудові і фінансові ресурси, при цьому «цінність» трудових ресурсів приблизно в 1,3 рази вище, ніж фінансових. Це свідчить про необхідність збільшення штату робітників, а вже потім необхідно розглядати питання залучення інвестицій в розвиток виробництва.