ЗАТРАТИ РЕСУРСІВ НА РЕАЛІЗАЦІЮ ТОВАРІВ

Методичні вказівки

На автоматизованому робочому місці планувальника в залеж­ності від виду галузі споживчої кооперації розв'язуються наступ­ні задачі і планування роздрібного товарообігу і його товарного забезпечення, планування виробництва і реалізації продукції промисловості, планування закупок сільськогосподарських про­дуктів і сировини, планування розвитку матеріально-технічної бази торгівлі, планування трудових ресурсів, планування господ-дарсько-фінансових результатів.

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

При розв'язку задачі 1 необхідно врахувати, що за основу бе­реться оклад продавця, а всі інші обчислюються через нього — у скільки разів або на скільки більше, тобто кожний оклад є ліній­ною функцією від окладу продавця:

А*П + В,

де П — оклад продавця,

А і В — коефіцієнти, які для кожної посади визначаються рі­шенням ради трудового колективу.

Так, із умови завдання видно, що коефіцієнти А і В прийма­ють наступні значення:

Задавши кількість людей на кожну посаду, можна скласти рі­вняння:

N1 • (А1 • П + В1) + N2 • (А2 • П + В2) + ... + N8 • (А8 • П + В8),

де N1 — кількість продавців,

N2 — кількість старших продавців,

А1...А8 і В1...В8 — коефіцієнти для кожної посади.

В цьому рівнянні нам відомі величини А1...А8 і В1...В8, а не ві­домі — П і N1...N8. Розв'язати (тобто знайти всі його розв'язки) та­ке рівняння відомими методами неможливо, але деякі його розв'язки можна знайти за допомогою програм Goal Seek (Підбір параметра) та Solver (Пошук рішень) MS Excel. В ході розв'язку цієї задачі необхідно створити розрахункову таблицю сценарій та звіт.

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

1. Для розв'язку задачі, сформульованої в завданні 1, створіть таблицю такої форми (табл. 28)

Таблиця 28

РОЗРАХУНОК ФОНДУ ЗАРПЛАТИ ПРАЦІВНИКІВ УНІВЕРМАГУ

1.1. Заповніть шапку таблиці.

1.2. Відведіть для кожної посади один рядок і занесіть в стов­пчик С назви посад.

1.3. В стовпчиках А і В вкажіть коефіцієнти А і В, які відпові­дають кожній посаді.

1.4. В клітинку НЗ занесіть значення заробітної плати про­давця (450) і встановіть для неї формат 0,00 — два знаки після коми.

2. В стовчику D обчисліть заробітну плату для кожної посади.

2.1. В клітинку D3 занесіть формулу =АЗ*$Н$3+В3&

2.2. Скопіюйте формулу із клітинки D3 на діапазон D4:D10.

3. В стовпчику Е вкажіть кількість працівників на посадах.

4. В стовпчику F обчисліть заробітну плату всіх працівників, які займають дану посаду.

4.1. В клітинку F3 занесіть формулу =D3*E3 (зарплата * кіль­кість працівників).

4.2. Скопіюйте формулу із клітинки F3 на діапазон F4:F10.

4.3. Встановіть для даних в стовпчиках D і F формат 0,00 — два знаки після коми.

5. Визначіть сумарний місячний фонд заробітної плати.

5.1. Просумуйте дані в стовпчику F, використовуючи ін- струмент автосумування.

5.2. Перемістіть значення суми в клітинки F12 і зробіть до неї підпис. «Сумарний місячний фонд заробітної плати».

5.3. Складіть штатний розпис. Внесіть зміни в зарплату про­давця або міняйте кількість співробітників в клітинках ЕЗ:Е5 до того часу, поки отриманий сумарний місячний фонд заробітної плати не буде дорівнювати заданому 30000 грн. (в комірці F12 необхідно отримати значення « ЗОООО).

6. Складіть штатний розпис з використанням операції автома­
тизації розрахунків Goal Seek (Подбор параметра).

6.1. Виберіть з меню Tools (Сервис) кнопку Goal Seek.

6.2. Вкажіть в полі Set Cell (Установить в ячейке) адресу ці­льової клітинки $F$12.

6.3. Вкажіть в полі То value (Значение) — 30 000.

6.4. Вкажіть в полі By changing cell (Изменяя ячейку) адресу клітинки із заробітною платою продавця $Н$3 і натисніть на діа­логову клавішу ОК.

7. Збережіть таблицю в особистому каталозі під іменем яке задаєте самі.

8. Підготуйте таблицю до друку.

9. Виведіть відредаговану таблицю на друк.

Підприємствам споживчої кооперації в перехідний період ри­нкової економіки все більше властиві функції переробки сільсь­когосподарської продукції, її заготівель і менше — функції тор­говельної діяльності. Це обумовлено тим, що ці галузі є високо-ліквідними, оскільки інвестиції вкладені в них швидко скупову­ються. Тому розв'язок другого завдання є актуальним на даний момент часу.

Оптимальне планування полягає в пошуку найкращого варіан­ту плану із множини можливих. Для його реалізації виділяються ресурси, тому планування пов'язане з розподілом ресурсів. Най­кращий розподіл ресурсів здійснюється при співставленні варіан­тів плану за вибраним критерієм оптимальності, за яким і визна­чається ступінь досягнення поставленої мети. Таким критерієм є прибуток. У зв'язку з цим оптимальним вважається такий план, який забезпечує максимальний прибуток (розв'язок задачі на ма­ксимум).

Всі економічні показники і чинники можна розділити на неке-ровані(z₁, z₂, z₃, ..., z_m) і керовані (х₁, х₂, х₃,..., х_n), оптималь­не значення яких ми і повинні знайти. На цій підставі цільову функцію в загальному виді можна записати так:

Завдання полягає у визначенні такого оптимального заван­таження обладнання, що переробляє зерно в муку, яке б забез­печило максимум прибутку підприємства згідно з цільовою фун­кцією виду:

За умови

(1)

(2)

(3) (4) (5)

де і — індекс видів виробничих ресурсів (/ = 1, 2, ..., т);

І — індекс виробництва продукціїу'-го виду (муки, висівок); х — обсяг виробництва продукції г'-го виду (муки, висівок);

Ь._к — наявність затраченого часу на виробництво одиниці продукції j-ro при к-ому варіанті технологічного процесу;

В — максимальне значення ресурсу часу роботи технологіч­ного обладнання;

а. — кількість виробничих ресурсів, затрачених за годину пе­реробки продукції,/-го виду;

С, — ціна одиниці продукціїу'-го виду.

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

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

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

За четвертою умовою кількість тон пшениці, перероблюваної з допомогою кожного варіанту завантаження, є невід'ємною ве­личиною.

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

Розв'язуючи друге завдання, необхідно створити дві таблиці і заповнити їх даними. Першу таблицю розмістимо на окремому аркушу такої форми (табл. 29).

Таблиця 29

ЗАТРАТИ РЕСУРСІВ НА ПЕРЕРОБКУ ЗЕРНА

Закінчення табл. 29

1. Назвіть перший аркуш «Дані» — на ньому будуть розташо­вуватися ПОЧАТКОВІ ДАНІ.

2. В клітинку А1 введіть назву задачі.

3. В клітинку A3 введіть текст «Ціна однієї тони».

4. В клітинки діапазону А4:А7 введіть назви: «Вищий ґату­нок», «Перший сорт», «Другий сорт», «Висівки».

5. В клітинки діапазону В4.В7 послідовно введіть ціни всіх сортів муки і висівок. Сформуйте ці клітинки грошовим стилем.

6. Введіть клітинку А9 текст «Ціна 1 т. зерна», а в клітинку В9 —$150,00.

7. Введіть в клітинку All текст «Кількість (тон)», а в клітинку ВИ—3200.

8. Введіть в клітинку А13 текст «Ресурс (годин)», в клітинку ВІЗ—4200.

9. Введіть в клітинку А15 текст «Вартість складування та тари (на 1 тону зерна)», а в клітинку В16 — число 25,00.

10. Введіть в клітинку А17 текст «інші затрати (на 1 тону зер­на)», а в клітинку В18 — число 13,00.

11. Відформатуйте дані, які ви надали в робочому аркуші «Да­ні» і на іншому аркуші створіть другу таблицю наступної форми (табл.30).

Таблиця ЗО

ЗАТРАТИ НА ВИРОБНИЦТВО МУКИ

Продовження табл. ЗО

Продовоюення таб. ЗО

Виберіть команду меню Tools —» Solver (Сервис —> Поиск ре-шений).

У полі Set Target Cell (Установить целевую ячейку) вкажіть клітинку $Т$6.

У полі By changing cell (Изменяя ячейки) вкажіть діапазон клі­тинок, значення яких програма змінює для побудови оптималь­ного значення (клітинки $Р$6). Після активізації цього поля за допомогою миші необхідно виділити клітинку Р6 або з клавіату­ри ввести її адресу.

В клітинку L6 занесіть формулу для обчислення вартості зер­на, яке переробляється за 1 годину (при першому варіанті заван­таження обладнання): F6*Дані!$В$9. Цю формулу слід поширити на діапазон клітинок L6:L8 для двох інших варіантів завантажен­ня обладнання.

Аналогічно в клітинки М6 і N6 занесіть формули:

=F6*Дані!$В$16 =F6*Дані!$В$18

і розмножте їх на діапазон клітинок М6:М8 та N6:N8 відповідно.

Відмітивши діапазон клітинок L6:N6, скористайтеся інстру­ментом автосумування. В результаті цього в клітинку О6 система занесе формулу SUM(L6:N6). Скопіюйте цю формулу на весь ді­апазон 06:08.

В клітинку G6 занесіть формулу, яка визначає вартість муки вищого гатунку, одержаного від переробки зерна за 1 год: =F6*В6*Дані! $В$4 і розмножте її на діапазон G6:G8. Аналогічно побудуйте 'формули для визначення вартості муки 1 сорту, 2 сор­ту і висівок в клітинки Н6,16 та J6:

=F6*С6*Дані!$В$5 =F6*D6*Дані!$B$6 =Р6*Е6*Дані!$В$7

Формули, побудовані для першого варіанту завантаження об­ладнання далі потрібно розмножити на діапазон клітинок G6:G8, Н6:Н8,16:18, та J6:J8 відповідно.

Відмітивши діапазон клітинок G6:J6 і скориставшись інстру­ментом автосумування, занесіть в клітинку К6 сумарну вартість муки, одержаної від переробки зерна за 1 годину при першому варіанті завантаження обладнання. Розмножте формулу з клітин­ки К6 на діапазон К6:К8.

В клітинку Q6 занесіть формулу =P6/F6 і розмножте її на діа­пазон клітинок Q6:Q8.

В клітинку R6 занесіть формулу К6-06, а в клітинку S6 — фо­рмулу R6/F6. Ці формули далі розмножте на діапазони клітинок R6:R8 та S6:S8 відповідно.

В клітинку Т6 занесіть формулу =P6*S6 і розмножте її на діа­пазон клітинок Т6:Т8. Таким чином, прибуток (клітинки Т6:Т8) залежить лише від одного параметра — кількості зерна, яка пере­робляється за тим чи іншим варіантом завантаження обладнання (клітинки Р6:Р8).

Обмеження задачі задайте в полі Subjects to constaints (Orpa-ничения) діалогового вікна Solver. В це поле вводять наступні обмеження:

$Р$6<=Дані!$В$11, $Q$6<=Дані!$В$13, $P$6=int, $Р$6>=0.

Щоб задати обмеження, скористайтеся кнопкою Add (Доба­вить), після чого з'явиться наступне діалогове вікно — Add Constraint (Добавление ограничений) з трьома полями.

У лівому полі, Cell reference (Ссьілка на ячейку), цього вікна вказується адреса клітинки, вміст якої повинен відповідати од­ному із заданих типів обмежень. Тип обмежень задається в сере­дньому полі.

Праве поле, Constraint (Ограничение), цього вікна призначене для введення значення обмеження або у вигляді константи, або у вигляді адреси клітинки, яка містить це значення. Діалогова кла­віша Add використовується для переходу до введення наступного обмеження.

Закінчивши введення всіх параметрів задачі, натисніть на діа­логову клавішу ОК.

Для внесення змін і вилучення обмежень використайте, відпо­відно, кнопки Change (Изменить) та Delete (Удалить) діалогового вікна програми Solver (Поиск решения). При натисненні у вікні Solver кнопки Options (Параметри поиска решения) можна задати параметри програми Solver.

Кнопкою Solve (Исполнить) запустіть процес пошуку розв'язку. Якщо оптимальне рішення буде знайдено, то обчисле­ні значення будуть вставлені в таблицю і на екрані з'явиться діа­логове вікно Solver Results (Результати поиска решений) з інфор­мацією про закінчення оптимізаційного процесу.

Однак, оптимізаційна задача не завжди має розв'язок. У та­кому випадку в діалоговому вікні замість повідомлень «Solver

found a solution», «All constraints and optimality conditions are satisfied» (Решения найдено, Все ограничения и условия опти-мальности вьіполненьї) буде повідомлення «Solver could not find a feasible solution» (Поиск не может найти оптимальное решение).

Якщо рішення знайдено, то виберіть один із режимів Keep Solver Solution (Сохранить найденное решение) або Restore Original Values (Восстановить исходное значение) і задайте вида­чу звітів, які потрібні для проведення аналізу оптимального рі­шення. Можна задати видачу звітів трьох типів: Answer (Резуль­тати), Sensitivity (Устойчивость), Limits (Предельї).

Збережіть таблицю в особистому каталозі під тим же іменем.

Повторіть процес оптимізації для другого та третього варіан­тів завантаження обладнання.

В другому варіанті цільова функція буде задаватися в клітинці Т7, а незалежний параметр — в клітинці Р7. Обмеження задайте таким чином:

$Р$7<=Дані!$В$11, $Q$6<=Дані!$В$13, $P$7=int, $Р$7>=0.

Для третього варіанту цільова функція предсталена клітинкою Т8, а незалежний параметр — клітинкою Р8. Обмеження задайте таким чином:

$Р$8<=Дані!$В$11, $Q$8<=Дані!$B$13, $P$8=int, $Р$8>=0.

В клітинку Т9 можна занести формулу =тах(Т6:Т8), а в клі­тинку U6 — формулу =if(T6=T9, «опт», ««). Поширимо останню формулу на діапазон клітинок U6:U8. Тоді з варіантів заванта­ження обладнання, який забезпечує найбільший прибуток, буде відмічений справа в таблиці з поміткою «опт».

Планування товарообігу підприємствами споживчої коопера­ції є їх важливою функцією, оскільки від складеного плану това­рообігу в багатьох випадках залежать результати торгово-господарської діяльності підприємств. За даними завдання 3 по­будуємо математичну модель задачі:

Зх₁ + 5х₂ + 4х₃ —> max;

Оптимальний план товарообігу є розв'язком цієї оптимізацій-ної задачі, оскільки він максимізує цільову функцію при заданих обмеженнях. Розв'язок цієї задачі теж можна отримати за допо­могою програми Solver.

Для застосування програми Solver побудуйте розрахункову схему, яка задає обмеження та цільову функцію.

1. Модель оптимізації товарообігу можна представити у ви­гляді трьох секцій (табл.31):

1) секції управляючих змінних;

2) секції цільової функції;

3) Секції функціональних обмежень.

Таблиця 31

МОДЕЛЬ ОПТИМІЗАЦІЇ ТОВАРООБІГУ

Змінні

Функціональні обмеження

Клітинка стовпчика В секції цільової функції називається ці­льовою клітинкою. Це клітинка, у якій відображається значення

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

2. Запустіть програму Solver.

3. У полі Set Target Cell вкажіть адресу клітинки з стовпчика В секції цільової функції.

4. У полі By Changing Cells вкажіть діапазон клітинок стовп­чика В сек.ції змінних, значення яких програма змінює для побу­дови оптимального розв'язку.

5. В подальшому виконуйте вимоги, які описані в пунктах 15—17 та 26—27 рекомендацій до виконання попереднього зав­дання.

Висновки

1. Планування — складний і трудомісткий процес, в якому приймають участь фахівці і керівники. Склад­ність планування пояснюється необхідністю ув'язки багатьох напрямків життєзабезпечення підприємства, параметрів його функціонування та поведінки його працівників.

2. Функція планування створює модель поточного і майбутнього функціонування підприємства. Ефектив­ність функціонування підприємства в значній мірі ви­значається моделлю планування.

3. В залежності від повноти інформаційного за­безпечення задач планування при розробці планів господарської діяльності підприємства використо­вуються методи дослідно-статистичний, норматив­ний, балансовий, економіко-математичного моделю­вання.

4. Розв'язок задач планування торговельно-госпо­дарської діяльності підприємства можна здійснювати за допомогою команди Goal Seek та надбудови Solver пакету програм MS Excel, а також засобів пакету про­грам Project Expert.

ТЕМА 9.