Методичні рекомендації

Пошук не може знайти відповідного розв'язку.

У процесі пошуку розв'язку не можна зробити ітерацію, яка задовольняла б усі обмеження при заданій точності. Ймовірно, обмеження суперечливі. Досліджуйте аркуш на предмет можливих помилок у формулах обмежень або у виборі обмежень.

Умови для лінійної моделі не задовольняються.

Встановлений прапорець Линейная модель, проте підсумковий перерахунок породжує такі значення, які не узгоджуються з лінійною мод елю. Це означає, що розв'язок недійсний для даних формул аркуша. Щоб перевірити лінійність задачі, встановіть прапорець Автоматическое масштабирование і повторно запустіть задачу. Якщо це повідомлення знову з'явиться на екрані, зніміть прапорець Линейная модель і знову запустіть задачу.

При пошуку розв'язку знайдено помилкове значення у цільовій комірки або в комірці обмеження.

Замало обсягу пам'яті для розв'язування задачі.

Система не змогла виділити обсяг пам'яті, необхідний для пошуку розв'язку. Закрийте деякі файли або додатки і спробуйте знову виконати процедуру пошуку розв'язку.

Завдання для лабораторних занять і самостійної роботи

ЗАВДАННЯ 1. Директор універмагу районної споживчої спілки повинен скласти штатний розпис, тобто визначити, скільки працівників, на яких посадах і з яким окладом він повинен прийняти на роботу. Загальний місячний фонд зарплати складає 30000 грн. Розробити постановку задачі і алгоритм її розв'язку, за допомогою програмних засобів MS Excel розв'язати задачу на персональному компютері і проаналізувати результати, результатну інформацію видати на друк.

141

PDF создан версией pdfFactory Pro для ознакомления www.pdffactory.com

ПОЧАТКОВІ ДАНІ. Дляя нормальної роботи універмагу необхідно 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

142

PDF создан версией pdfFactory Pro для ознакомления www.pdffactory.com

сорту — 9 %, висівок — 10 %. Ціна 1 т борошна вищого ґатунку складає 320 дол., 1 сорту — 260 дол., 2 сорту — 220 дол., висівок — 60 $.

Затрати на складування і тару при переробці 1 т пшениці за годину складають 25 дол., інші змінні затрати переробки 1 т пшениці — 13 дол. Прибуток, отриманий за 1 годину роботи обладнання визначається як різниця між виручкою від реалізації борошна (його вартістю) і змінними затратами, які складаються із затрат на закупівлю зерна, складування і тару та інших змінних витрат (собівартістю борошна).

ЗАВДАННЯ 3. Скласти оптимальний план товарообігу універмагу за критерієм максимуму прибутку. Розробити постановку задачі і алгоритму її розв'язку. За допомогою програмних засобів MS Excel розв'язати задачу на персональному комп'ютері проаналізувати результати і результуючу ознаку видати на друк.

ПОЧАТКОВІ ДАНІ. Універмаг реалізує товари декількох груп: А, В, С. Відомі нормативи затрат ресурсів в розрахунку на одиницю товару по кожний групі (табл. 28) і відповідні величини ресурсів.

Таблиця 28

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

Ресурси	Нормативи затрат ресурсів із			Обмежені
		продажу товарів		обсяги ре-
	А	В	с	сурсів, bi
Робочий час, люд.-год.	a11 =0,1	a12= 0,2	a13 =0,4	b1= 1100
Площа торгових залів, м2	a21 = 0,05	a22 = 0,02	a23=0,02	b2 =120
Витрати обігу, грн.	a31 = 3	a32 = 1	a33 =2	b3 =3000
Прибуток в розрахунку на	C1 = 3	C2 = 5	C3= 4
одиницю товару, грн.
План продажу товарів, од.	X1= ?	X2= ?	X3 = ?

143

PDF создан версией pdfFactory Pro для ознакомления www.pdffactory.com

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

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

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

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

А*П + В,

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

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

А= 1,5	В = 0
А = 3	В = 0
А = 3	В = 30
А = 2	В = 0
А = 1,5	В = 40
А = 4	В = 0

144

PDF создан версией pdfFactory Pro для ознакомления www.pdffactory.com

А = 4

В =20

Задавши кількість людей на кожну посаду, можна скласти рівняння: 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, створіть таблицю такої форми (табл. 30):

Таблиця 30

	A	B	C	D	E	F	G	H
1	КоефіцієнтА	КоефіцієнтB			Кількість працівників			Зарплата продавця
			Посада	Зарплата		Сумарна зарплата
2
3			Продавець					450,00
4			Ст. продавець
5			Менеджер по
			роботі з
			товарами
6			Менеджер
			вивчення
			кон’юктури
			ринку

145

PDF создан версией pdfFactory Pro для ознакомления www.pdffactory.com

	A	B	C	D	E	F	G	H
1	Коефіцієнт А	Коефіцієнт B			Кількість працівників			Зарплата продавця
			Посада	Зарплата		Сумарна зарплата
2
7			Зав.складом
8			Гол.менеджер
9			Зав.відділом
10			Директор
11
12		Сумарний місячний фонд зарплати

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

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

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

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

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

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

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

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

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

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

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

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

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

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

146

PDF создан версией pdfFactory Pro для ознакомления www.pdffactory.com

автосумування.

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

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

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

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

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

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

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

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

самі.

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

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

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

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

147

PDF создан версией pdfFactory Pro для ознакомления www.pdffactory.com

bj k -

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

Всі економічні показники і чинники можна розділити на некерованіі керованіоптимальне значення яких ми і повинні знайти. На цій підставі цільову функцію в загальному виді можна записати так:

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

де і — індекс видів виробничих ресурсів (і= 1, 2, т); j - індекс виробництва продукції - j -го виду (муки, висівок); xj— обсяг виробництва продукції ї-го виду (муки, висівок);

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

148

PDF создан версией pdfFactory Pro для ознакомления www.pdffactory.com

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

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

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

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

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

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

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

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

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

1. Перейменувати Лист1 на «Дані» та створити на ньому таблицю за наступним зразком:

149

PDF создан версией pdfFactory Pro для ознакомления www.pdffactory.com

2. На аркуші Лист2 створити наступну таблицю:

150

PDF создан версией pdfFactory Pro для ознакомления www.pdffactory.com