АІС КООПЕРАТИВНИМИ ОРГАНІЗАЦІЯМИ І ПІДПРИЕМСТВАМИ
ТЕМА 8
ІНФОРМАЦІЙНА ТЕХНОЛОГІЯ РОЗВ'ЯЗКУ ЗАДАЧ ПЛАНУВАННЯ
Мета. Проробити на персональному комп'ютері основні питання організації автоматизованої обробки даних на АРМ планувальника в умовах функціонування інтегрованої системи обробки економічної інформації на підприємствах споживчої кооперації; розвинути у студентів навики в застосуванні отриманих теоретичних знань при розв'язку на персональних комп'ютерах комплексу економічних завдань розглядуваного АРМ.
Контрольні питання
1.Яке призначення АРМу планувальника. Охарактеризуйте його основні підсистеми.
2. Перелічіть та стисло охарактеризуйте завдання АРМа планувальника.
3. Які види початкової інформації використовуються в процесі розв'язку задач за допомогою АРМа планувальника?
4. Опишіть постановку основних задач планування торговельної діяльності підприємств і організацій споживчої кооперації.
5. Поясніть алгоритми і технологію автоматизації розрахунків плану оптового товарообігу і його товарного забезпечення.
6. Висвітліть методику, алгоритми і технологію автоматизації контролю виконання плану товарообігу на гуртовій базі.
7. Яка методика розрахунку плану фонду заробітної плати працівників торговельного підприємства? Опишіть алгоритм та технологію автоматизації розрахунків.
8. Дайте характеристику пакетів прикладних програм, які використовуються для автоматизації розв'язку задач планування торговельно-господарської діяльності підприємств.
Завдання для лабораторних занять і самостійної роботи
ЗАВДАННЯ 1. Директор універмагу районної споживчої спілки повинен скласти штатний розпис, тобто визначити, скільки працівників, на яких посадах і з яким окладом він повинен прийняти на роботу. Загальний місячний фонд зарплати складає 30000 грн. Розробити постановку задачі і алгоритм її розв'язку, за допомогою програмних засобів MS Excel розв'язати задачу на персональному компютері і проаналізувати результати, результатну інформацію видати на друк.
Початкові дані. Для нормальної роботи універмагу необхідно 9 продавців, 7 старших продавців, 10 менеджерів для роботи з товарами, 3 завідувачі відділами, 1 менеджер з вивчення кон'юнктури ринку, 1 завідувач складом, 1 головний менеджер, 1 директор. Рада універмагу вирішила встановити продавцеві оклад в розмірі 450 грн., старший продавець повинен отримувати в 1,5 раза більше продавця, менеджер по роботі з товарами — в 3 раза більше від продавця, заввідділом — на 30 грн. більше, ніж менеджер по роботі з товарами, менеджер з вивчення кон'юнктури ринку — в 2 рази більше від продавця, завскладом на 40 грн. більше від продавця, головний менеджер — в 4 рази більше від продавця, директор універмагу — на 20 грн. Більше від головного менеджера.
ЗАВДАННЯ 2. Скласти оптимальний план завантаження обладнання, яке переробляє зерно в муку, за критерієм максимиму прибутку при наявності обмежень на ресурси. Розробити постановку задачі і алгоритму її розв'язку. За допомогою програмних засобів MS Excel розв'язати задачу на персональному комп'ютері проаналізувати результати і результуючу ознаку видати на друк.
Початкові дані. Підприємство споживчого товариства займається переробкою пшениці на борошно. Технологічне обладнання підприємства працює в дві зміни, тривалість якої 7 годин протягом 300 робочих днів в році. В районі діяльності підприємства можна закупити лише 3200 т пшениці за ціною 150 дол. за 1 тону. На підприємстві можна запровадити 3 варіанти завантаження технологічного обладнання. При першому варіанті за 1 годину можна переробити 1 т пшениці і вихід борошна складає: вищого гатунку 25%,
1 сорту — 20 %, 2 сорту — 45 %, висівок — 10 %. Застосування другого варіанту дозволяє переробити за 1 годину 0,6 т пшениці і вихід борошна складає: вищого гатунку 50%, 1 сорту — 27 %, 2 сорту — 13%, висівок — 10%. Застосування третього варіанту дозволяє переробити за 1 годину 0,3 т пшениці і вихід борошна складає: вищого ґатунку 63 %, 1 сорту — 18 %, 2 сорту — 9 %, висівок — 10 %. Ціна 1 т борошна вищого гатунку складає 320 дол., 1 сорту — 260 дол., 2 сорту — 220 дол., висівок — 60 $.
Затрати на складування і тару при переробці 1 т пшениці за годину складають 25 дол., інші змінні затрати переробки 1 т пшениці — 13 дол. Прибуток, отриманий за 1 годину роботи обладнання визначається як різниця між виручкою від реалізації борошна (його вартістю) і змінними затратами, які складаються із затрат на закупівлю зерна, складування і тару та інших змінних витрат (собівартістю борошна).
ЗАВДАННЯ 3. Скласти оптимальний план товарообігу універмагу за критерієм максимуму прибутку. Розробити постановку задачі і алгоритму її розв'язку. За допомогою програмних засобів MS Excel розв'язати задачу на персональному комп'ютері проаналізувати результати і результуючу ознаку видати на друк.
Початкові дані. Універмаг реалізує товари декількох груп: А, В, С. Відомі нормативи затрат ресурсів в розрахунку на одиницю товару по кожний групі (табл. 27) і відповідні величини ресурсів.
Таблиця 27
ЗАТРАТИ PFXYPCIB НА РЕАЛІЗАЦІЮ ТОВАРІВ
Методичні вказівки
На автоматизованому робочому місці планувальника в залежності від виду галузі споживчої кооперації розв'язуються наступні задачі і планування роздрібного товарообігу і його товарного забезпечення, планування виробництва і реалізації продукції промисловості, планування закупок сільськогосподарських продуктів і сировини, планування розвитку матеріально-технічної бази торгівлі, планування трудових ресурсів, планування господ-дарсько-фінансових результатів.
Кожна із вищевказаних задач складається із окремих підзадач визначення фонду заробітної плати працівників організацій і підприємств споживчої спілки, розрахунок чисельності працівників галузей діяльності споживчих спілок, розрахунок навантаження на одного працівника в галузі діяльності споживчих спілок, розрахунок навантаження на одного працівника в галузі діяльності споживчої спілки.
При розв'язку задачі 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, використовуючи ін- L І струмент автосумування. І І
5.2. Перемістіть значення суми в клітинки F12 і зробіть до неї підпис. «Сумарний місячний фонд заробітної плати».
5.3. Складіть штатний розпис. Внесіть зміни в зарплату продавця або міняйте кількість співробітників в клітинках ЕЗ:Е5 до того часу, поки отриманий сумарний місячний фонд заробітної плати не буде дорівнювати заданому 30000 грн. (в комірці F12 необхідно отримати значення и 30000).
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. Виведіть відредаговану таблицю на друк.
Підприємствам споживчої кооперації в перехідний період ринкової економіки все більше властиві функції переробки сільськогосподарської продукції, її заготівель і менше — функції торговельної діяльності. Це обумовлено тим, що ці галузі є високо-ліквідними, оскільки інвестиції вкладені в них швидко скуповуються. Тому розв'язок другого завдання є актуальним на даний момент часу.
Оптимальне планування полягає в пошуку найкращого варіанту плану із множини можливих. Для його реалізації виділяються ресурси, тому планування пов'язане з розподілом ресурсів. Найкращий розподіл ресурсів здійснюється при співставленні варіантів плану за вибраним критерієм оптимальності, за яким і визначається ступінь досягнення поставленої мети. Таким критерієм є прибуток. У зв'язку з цим оптимальніш вважається такий план, який забезпечує максимальний прибуток (розв'язок задачі на максимум).
Всі економічні показники і чинники можна розділити на неке-ровані(г,, z2, z3,..., zm) і керовані (х,, х2, х3,..., хп), оптимальне значення яких ми і повинні знайти. На цій підставі цільову функцію в загальному виді можна записати так:
y = F(xi,x2,xi...,xn;zl,z2,zi...,zm)->exti .
Завдання полягає у визначенні такого оптимального завантаження обладнання, що переробляє зерно в муку, яке б "забезпечило максимум прибутку підприємства згідно з цільовою функцією виду:
|
За умови
де і — індекс видів виробничих ресурсів (/=1,2,..., т);
j— індекс виробництва продукціїу'-го виду (муки, висівок); Xj — обсяг виробництва продукції /-го виду (муки, висівок);
bJk — наявність затраченого часу на виробництво одиниці продукції j-ro при к-ому варіанті технологічного процесу;
В — максимальне значення ресурсу часу роботи технологічного обладнання;
a j — кількість виробничих ресурсів, затрачених за годину переробки продукціїу'-го виду;
С j — ціна одиниці продукції у'-го виду.
Перша умова полягає в тому, що обсяг перероблюваного з допомогою трьох варіантів завантаження обладнання пшениці менше або дорівнює кількості пшениці, яка є на даний момент часу.
Друга умова вимагає, щоб загальна кількість годин при одному з трьох варіантів завантаження повинна бути меншою або рівною максимальному значенню ресурсу часу.
Третя умова вказує, що кількість тон пшениці, перероблюваної за допомогою кожного варіанту завантаження, повинна бути цілим числом.
За четвертою умовою кількість тон пшениці, перероблюваної з допомогою кожного варіанту завантаження, є невід'ємною величиною.
П'ята умова свідчить, що кількість тон пшениці, перероблюваної з допомогою кожного варіанту завантаження, повинна бути меншою або рівною максимально допустимому обсягу з врахуванням ресурсу часу і перероблюваній кількості пшениці за годину.
Розв'язуючи друге завдання, необхідно створити дві таблиці і заповнити їх даними. Першу таблицю розмістимо на окремому аркушу такої форми (табл. 29).
Таблиця 29
ЗАТРАТИ РЕСУРСІВ НА ПЕРЕРОБКУ ЗЕРНА
| А | В | С | Д | Е | ||
| Планування завантаження обладнання | ||||||
| Ціна 1 т борошна | ||||||
| Вищий ґатунок | 320,00 | |||||
| Перший сорт | 268,00 |
Закінчення табл. 29
| А | В | С | Д | Е | |
| Другий сорт | 220,00 | ||||
| Висівки | 60,00 | ||||
| Ціна Іт.зерна | 150,00 | ||||
| Кількість (тон) | |||||
| Ресурс (годин) | |||||
| Вартість складування та тари (на 1 тону зерна) | 25,00 | ||||
| Інші затрати (на 1 тону зерна) | 13,00 | ||||
1. Назвіть перший аркуш «Дані» — на ньому будуть розташовуватися ПОЧАТКОВІ ДАНІ.
2. В клітинку А1 введіть назву задачі.
3. В клітинку A3 введіть текст «Ціна однієї тони».
4. В клітинки діапазону А4:А7 введіть назви: «Вищий гатунок», «Перший сорт», «Другий сорт», «Висівки».
5. В клітинки діапазону В4:В7 послідовно введіть ціни всіх сортів муки і висівок. Сформуйте ці клітинки грошовим стилем.
6. Введіть клітинку А9 текст «Ціна 1 т. зерна», а в клітинку В9 —$150,00.
7. Введіть в клітинку А11 текст «Кількість (тон)», а в клітинку ВИ—3200.
8. Введіть в клітинку А13 текст «Ресурс (годин)», в клітинку ВІЗ —4200.
9. Введіть в клітинку А15 текст «Вартість складування та тари (на 1 тону зерна)», а в клітинку В16 — число 25,00.
10. Введіть в клітинку А17 текст «інші затрати (на 1 тону зерна)», а в клітинку В18 — число 13,00.
11. Відформатуйте дані, які ви надали в робочому аркуші «Дані» і на іншому аркуші створіть другу таблицю наступної форми (табл. 30).
Таблиця ЗО
|
ЗАТРАТИ НА ВИРОБНИЦТВО МУКИ
і розмножте їх на діапазон клітинок М6:М8 та N6:N8 відповідно.
Відмітивши діапазон клітинок L6:N6, скористайтеся інструментом автосумування. В результаті цього в клітинку Об система занесе формулу SUM(L6:N6). Скопіюйте цю формулу на весь діапазон 06:08.
В клітинку G6 занесіть формулу, яка визначає вартість муки вищого гатунку, одержаного від переробки зерна за 1 год: =Р6*В6*Дані! $В$4 і розмножте її на діапазон G6.G8. Аналогічно побудуйте 'формули для визначення вартості муки 1 сорту, 2 сорту і висівок в клітинки Н6,16 та J6:
Формули, побудовані для першого варіанту завантаження обладнання далі потрібно розмножити на діапазон клітинок G6:G8, Н6:Н8,16:18, та J6:J8 відповідно.
Відмітивши діапазон клітинок G6J6 і скориставшись інструментом автосумування, занесіть в клітинку К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 (Ограничения) діалогового вікна Solver. В це поле вводять наступні обмеження:
$Р$6<=Дані!$В$11, $0$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. Обмеження задайте таким чином:
Для третього варіанту цільова функція предсталена клітинкою Т8, а незалежний параметр — клітинкою Р8. Обмеження задайте таким чином:
В клітинку Т9 можна занести формулу =max(T6:T8), а в клітинку U6 — формулу =if(T6=T9, «опт», ««). Поширимо останню формулу на діапазон клітинок U6:U8. Тоді з варіантів завантаження обладнання, який забезпечує найбільший прибуток, буде відмічений справа в таблиці з поміткою «опт».
Планування товарообігу підприємствами споживчої кооперації є їх важливою функцією, оскільки від складеного плану товарообігу в багатьох випадках залежать результати торгово-господарської діяльності підприємств. За даними завдання 3 побудуємо математичну модель задачі:
З*, + 5х2 + 4х, -> шах;
|
Оптимальний план товарообігу є розв'язком цієї оптимізацій-ної задачі, оскільки він максимізує цільову функцію при заданих обмеженнях. Розв'язок цієї задачі теж можна отримати за допомогою програми 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.
g3«
ТЕМА 9