Тема- Технологія розв~язання економічних задач методами лінійного програмування з використанням Microsoft Excel

Работа добавлена на сайт samzan.net:

абораторна робота №3(1)  Дисципліна “Інформаційні системи в менеджменті” Ст. викладач Волик О.Ф.

ЛАБОРАТОРНА РОБОТА №3 (частина 1)

Тема: Технологія розв’язання економічних задач методами лінійного програмування з використанням Microsoft Excel

Мета: придбання навичок побудови математичних моделей для економічних задач і розв’язання їх в Microsoft Excel

Теоретичні відомості

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

Для розв’язання таких задач використовуються методи математичного програмування.

Для побудови математичної моделі необхідно відповісти на наступні три питання:

1.  Що є шуканими величинами, тобто змінними цієї задачі?
2.  У чому полягає мета, для досягнення якої із всіх допустимих значень змінних потрібно вибрати ті, які відповідатимуть найкращому, тобто оптимальному, розв’язку?
3.  Які обмеження повинні бути накладені на змінні, щоб виконувалися умови, описані в задачі?

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

Завдання до лабораторної роботи

1.  Постанова задачі:

Меблевий комбінат випускає книжні полиці А із натурального дерева із склом, полиці B1 із полірованої ДСП (деревно–стружкової плити) без скла і полиці B2 із полірованої ДСП із склом. Габарити полиць А, B1 і В2 такі: довжина 1100 (d) мм, ширина 250 (w) мм, висота 300 (h) мм (рис. 1). Розмір листу ДСП 23 м.

Під час виготовлення полиць А виконуються такі роботи: столярні, покриття лаком, сушка, різання скла, упаковка. Всі операції, вироблювані в ході столярних робіт і упаковки, виконуються вручну. Полиці B1 і В2 поставляються в торгівельну мережу в розібраному вигляді. За винятком операції упаковки, вся решта операцій (виробництво комплектуючих полиці, різання скла) при виготовленні полиць B1 і В2, виконуються на спеціалізованих автоматах.

Трудомісткість столярних робіт по випуску однієї полиці А складає 4 (Тр1) год. Продуктивність автомата, що покриває полиці А лаком – 10 (Пр1) полиць в годину, автомата, що ріже стекло – 100 (Пp2) стекол в годину. Змінний фонд часу автомата для покриття лаком – 7 (ФВ1) год., автомата для різання скла – 7,5 (ФВ2) год. Сушка полиць, покритих лаком, відбувається протягом доби в спеціальних сушильнях, що вміщують 50 (V1) полиць. На упаковку полиці А потрібно 4 (Тр2) хвилини. У виробництві полиць зайняті 40 (Р1) столярів і 14 (Р2) пакувальників.

Продуктивність автомата, що виробляє комплектуючі полиць B1 і В2, рівна 3 (Пр3) полиці в годину, а його змінний фонд часу рівний 7,4 (ФВ3) год., трудомісткість пакувальних робіт складає 8 (Тр3) хв. для полиці В1 і 10 (Тр4) хв. для полиці В2.

Від постачальників комбінат отримує в місяць 400 (Z1) листів полірованої ДСП, 230 (Z2) листів ДВП (деревно-волокнистої плити), а також 260 (Z3) листів скла. Із кожного листу ДВП можна викроїти 14 (К1) задніх стінок полиць B1 і В2, а із кожного листу скла – 10 (К2) стекол для полиць А і В2.

Склад готової продукції може розмістити не більш 350 (V2) полиць і комплектів полиць, причому щодня в торгівельну мережу вивозиться в середньому 40 (N) полиць і комплектів. На початок поточного місяця на складі залишилося 100 (Ост) полиць, вироблених раніше. Собівартість полиці А рівна 205 (C1) грн., полиці В без скла – 142 (C2) грн., із склом – 160 (С3) грн.

Маркетингові дослідження показали, що частка продажів полиць обох видів із склом складає не менше 60% (Д) в загальному об’ємі продажів, а ємність ринку полиць вироблюваного типу складає близько 5300 (V3) штук в місяць. Меблевий комбінат уклав договір на поставку замовнику 50 (З) полиць типа В2 в поточному місяці.

Необхідно скласти план виробництва полиць на поточний місяць. Відомі ціни реалізації полиць: полиця А – 295 (Ц1) грн., полиця В без скла – 182 (Ц2) грн., полиця В із склом – 220 (Ц3) грн.

Побудова моделі

1.  Визначити шукані величини, тобто змінні поставленої задачі.

У задачі шуканими невідомими величинами є кількість полиць кожного виду, які будуть вироблені в поточному місяці. Таким чином, xА – кількість полиць А (шт./міс.); xB1 – кількість полиць В1 (шт./міс.); xB2 – кількість полиць В2 (шт./міс.).

1.  Визначити мету розв’язання задачі, тобто побудувати цільову функцію.

В даному випадку мета – це максимізація прибутку, одержаного від продажу полиць всіх видів протягом місяця. Оскільки в цій задачі прибуток може бути визначений як різниця між ціною (Ц1, Ц2, Ц3) і собівартістю (С1, С2, С3), то ЦФ має вигляд

L (X )=(295 -205 )xA + (182 -142 )xB1+(220-160) xB2→max

 =

(1)

1.  Сформувати обмеження по фонду часу (з використанням трудомісткості робіт)

Ліва частина обмежень по фонду часу є часом, що витрачається на виробництво полиць протягом місяця в кількості xА, xB1, xB2 штук. Права частина обмеження — це фонд робочого часу виконавця роботи (робітника або автомата) за зміну. Нерівність (2) описує обмеження по фонду часу на виконання столярних робіт. Коефіцієнт 4 ч/шт. (Тр1) – це час, що витрачається на столярні роботи при виробництві однієї полиці типу А (трудомісткість); 40 чол. (Р1) – це кількість столярів, що беруть участь у виробництві; 8 ч/(чол.зм.) – кількість годин роботи однієї людини протягом зміни; 1 зм./д. – кількість змін в одному робочому дні; 22 д./міс . – кількість робочих днів в місяці (табл. 2.1):

4xA ≤ 408122	≤	(2)
 ≤ чол

Примітка. Важливим моментом перевірки правильності складання обмежень є перевірка збігу одиниць вимірювання лівої і правої частин обмеження. У обмеженні (2) ліва і права частини вимірюються в годинах, витрачених на випуск продукції протягом місяця.

Аналогічно записується обмеження (3) по фонду часу на пакувальні роботи, в якому 14 чол. (Р2) – це кількість пакувальників:

xA + xB1 + xB2 ≤ 148122	≤	(3)
 ≤ чол

1.  Сформувати обмеження по фонду часу (з використанням продуктивності робіт)

Нерівність (4) описує обмеження по фонду часу на покриття лаком полиць типа А. Відмінність обмежень, що враховують дані про продуктивності робіт, від обмежень, що враховують дані про трудомісткість робіт, полягає у тому, що продуктивність необхідно перетворити в трудомісткість. Трудомісткість є величиною, зворотною до продуктивності. Коефіцієнт  () при xA в (4) – це кількість годин, що відводиться на покриття лаком однієї полиці типа А. Під час запису правої частини обмеження враховуємо, що автомат, який виконує покриття лаком, працює не повну зміну (8 ч), а протягом змінного фонду часів 7 ч (ФВ1). Це пов’язано з необхідністю підготовки автомата до роботи і обслуговуванням його після закінчення роботи.

xA ≤ 7122	≤	(4)
 ≤

Нерівність (5) описує обмеження по фонду часу на різання скла для полиць типу А і В2:

xA + xB2 ≤ 7,5122	≤	(5)
 ≤

Нерівність (6) описує обмеження по фонду часу на виробництво комплектуючих полиць типа В1 і В2:

xB1 + xB2 ≤ 7,4122	≤	(6)
 ≤

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

Нерівність (7) описує обмеження по запасу листів ДСП, що поставляються на комбінат щомісячно. При цьому слід врахувати, що з листу ДСП треба викроювати комплекти (верхню і нижню сторони полиць, 2 бічні сторони) для виробництва полиць. Тому при завданні обмеження має сенс орієнтуватися не на кількість листів ДСП, а на кількість комплектів для полиць [права частина (7)], які можна отримати з наявного запасу ДСП. Але оскільки листи ДСП можна розкроювати різними способами і отримувати при цьому різну кількість деталей і комплектів, то позначимо місячний запас комплектів в правій частині (7) як Yкомпл і розглянемо спосіб його чисельного визначення пізніше. У лівій частині обмеження (7) задається кількість комплектів (по одинці на полицю), необхідних на виробництво полиць протягом місяця в обсязі xB1 , xB2:

1xB1 + 1xB2 ≤ Yкомпл	≤	(7)
 ≤

Аналогічно обмеженню по ДСП нерівність (8) – це обмеження по запасу задніх стінок з ДВП для полиць В1 і В2, а нерівність (9) – обмеження по запасу стекол для полиць А і В2. На відміну від ДСП листи ДВП і листи скла крояться стандартним способом, і з кожного листу ДВП виходить 14 (К1) задніх стінок полиць, а з кожного листу скла виходить 10 (К2) стекол. Щомісячний запас листів ДВП і скла складає відповідно 230 (Z2) і 260 (Z3). При складанні лівих частин обмежень (8) і (9) слід врахувати, що на кожну полицю В1 і В2 припадає по одній задній стінці, а на кожну полицю А і В2 – по 2 стекла:

1xB1 + 1xB2 ≤ 23014	≤	(8)
 ≤

2xA + 2xB2 ≤ 26010	≤	(9)
 ≤

1.  Задати обмеження по місткості допоміжних приміщень і ринку

Нерівність (10) є обмеженням по кількості полиць А, які може вміщати сушильня. У правій частині (10) представлена кількість полиць, які можуть бути просушені протягом місяця (у день може бути просушено 50 (V1) полиць):

xA ≤ 5022	≤	(10)
≤

Нерівність (11) описує обмеження по кількості полиць всіх видів, які може вміщати склад готової продукції. При цьому права частина (11) враховує, що загальна місткість складу зменшена на 100 (Ост) полиць, які залишилися невивезеними з минулого місяця. Крім того, протягом місяця кожен день звільнятиметься по 40 (N) місць для полиць:

xA + xB1 + xB2 ≤ 350 – 100 + 4022	≤	(11)
≤ – + 

Нерівність (12) описує обмеження по приблизній ємності ринку, рівної 5300 (V3) полицям всіх видів:

xA + xB1 + xB2 ≤ 5300

≤

(12)

1.  Сформувати обмеження по гарантованому замовленню

Нерівність (13) показує, що необхідно виробити як мінімум 50 (З) замовлених полиць В2, а можливо, і більше, але вже для вільного продажу:

xB2  50



(13)

Нерівність (14) показує, що частка полиць А і В2 в загальному об’ємі полиць, вироблюваних для вільного продажу, повинна складати не менше 60% (Д). До такого висновку приводять результати маркетингових досліджень. Оскільки із всіх полиць В2 у вільний продаж поступить лише (xB2 – 50), то це враховується при складанні обмеження (14), яке після перетворень алгебри приймає вигляд (15).

xA + (xB2 – 50)  0,6[xA + xB1 + (xB2 – 50)]



(14)

0,4xA – 0,6xB1 + 0,4xB2  20

(15)

1.  Визначити кількості комплектів для полиць В1 і В2

Розглянемо детально питання визначення максимально можливої кількості комплектів для полиць В1 і В2, яке можна виробити з щомісячного запасу ДСП. Залежно від розмірів листів ДСП (2000×3000 мм) і габаритів полиць (1100×250×300 мм) деталі полиць В1 і В2 можна викроїти різними способами. Розглянемо три можливі варіанти такого розкрою, представлені на рис. 2 (затемнені ділянки – це невикористана площа ДСП).

Згідно 1-му варіанту з одного листу ДСП для полиць В1 і В2 можна викроїти 19 деталей верхньої або нижньої стінок, а також 9 деталей бокових стінок. По 2-му варіанту розкрою отримаємо 12 деталей верхньої або нижньої стінок і 36 деталей бокових стінок. По 3-му варіанту розкрою отримаємо 16 деталей верхньої або нижньої стінок і 18 деталей бокових стінок. Позначимо кількість листів ДСП, розкроєних протягом місяця:
по 1-му варіанту через y1 (лист./міс.); по 2-му варіанту – y2 (лист./міс.);
по 3-му варіанту – y3 (лист./міс.). При виробництві полиць нам вигідно прагнути до такого розкрою листів ДСП, при якому з отриманих деталей можна укомплектувати максимальну кількість полиць. Кількість комплектів, отриманих з розкроєних деталей, ми раніше позначили через Yкомпл . Таким чином, наша ціль описується цільовою функцією

L(Y) = Yкомпл → max

компл./міс.

Кількість всіх розкроєних листів ДСП не повинна перевищувати 400 (Z1), тобто щомісячний запас їх на складі:

y1 + y2 + y3 ≤ 400

лист./міс.

При цьому, оскільки в кожен комплект входить одна верхня і одна нижня стінки, кількість нижніх і верхніх стінок, отримуваних при розкрої всіх листів ДСП [ліва частина (16)], повинна бути не менше ніж 2Yкомпл:

19y1 + 12y2 + 16y3  2Yкомпл		(16)
 

Аналогічне значення має обмеження (17), яке задає нижню межу кількості бокових стінок полиць:

9y1 + 36y2 + 18y3  2Yкомпл



(17)

Після перетворення описаних нерівностей отримаємо модель задачі (18), що дозволяє розкроїти максимальну кількість комплектів:

L(Y) = Yкомпл → max;

 (18)

Таким чином, при розв’язанні задачі (18) симплекс–методом (наприклад, в MS Excel) змінна Yкомпл безпосередньо визначає значення ЦФ, а змінні y1, y2 і y3 впливають на зміну значення ЦФ непрямим чином, через обмеження.

1.  Розв’язати задачу (18) симплекс–методом в MS Excel та визначити максимально можливу кількість комплектів для полиць В1 і В2, яку можна виробити з щомісячного запасу ДСП.

Можливий варіант екранної форми для введення початкових даних, умов задачі та залежностей з математичної моделі наведений на рис. 2.

Розв’язавши задачу (18), отримаємо значення правої частини обмеження (7) Y=3387 комплектів.

Рис. 2.Екранна форма розв’язку задачі про визначення максимально можливої кількості комплектів для полиць В1 і В2, яку можна виробити з щомісячного запасу ДСП.

1.  Засобами MS Excel розв’язати початкову задачу щодо складання плану виробництва полиць на поточний місяць, модель якої має вигляд:

L(X) = 90xA+40xB1+60xB2→max;

(19)

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

Розв’язавши задачу (19), отримаємо

xA = 1100 шт./міс, xB1 =0 шт./міс, xB2 =120 шт./міс,

L(X)=106 200 грн./міс, (20)

Тобто в поточному місяці необхідно виробити 1100 полиць А і 120 полиць В2, а виробництво полиць В1 недоцільне. Після реалізації всіх вироблених полиць комбінат отримає прибуток у розмірі 106 200 гривень.

Рис. 3. Екранна форма для розв’язку задачі щодо складання плану виробництва полиць на поточний місяць

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

У власній робочій книзі на новому робочому листі, використовуючи засоби MS Excel, сформувати екранну форму та знайти розв’язок оптимізаційної задачі для моделі ЛП, відповідної до номеру Вашого варіанту (таблиця 1).

Примітка . Розрахунок числових даних, які безпосередньо не задані в умові задачі, виконуйте безпосередньо в комірках екранної форми. Наприклад, для введення коефіцієнта  при xA в лівій частині (3) в відповідну комірку треба ввести вираз =4/60, після чого в комірці відобразиться результат обчислення, тобто 0,066666667. Для введення правої частині обмеження (3) у відповідну комірку треба ввести вираз =14*8*1*22, при цьому в комірці відобразиться число 2464. Цей спосіб дозволяє чітко уявляти шлях отримання числових даних в комірках екранної форми, уникати помилок при розрахунку параметрів задачі, а також забезпечує високу точність розрахунків.

Контрольні запитання

1.  Що таке розподільна задача, загальна розподільна задача?
2.  Що таке математичне і лінійне програмування?
3.  Яка загальна форма запису моделі ЛП?
4.  Що таке допустимий і оптимальний розв’язок?
5.  Які основні етапи побудови математичної моделі ЛП?
6.  Яке економічне значення і математичний вид ЦФ задачі про виробництво полиць?
7.  Як можна класифікувати обмеження задачі про полиці по їх економічному значенню?
8.  Чим відрізняється побудова обмежень, що використовують дані про трудомісткість і продуктивність робіт?
9.  Поясніть спосіб побудови кожного конкретного обмеження задачі про полиці.
10.  Яким чином розв’язується задача оптимального розкрою листів ДСП?
11.  Яким чином одиниці вимірювання параметрів задачі використовуються для виявлення помилок побудови обмежень?

Таблиця 1

Початкові дані варіантів задач до лабораторної роботи №2

№ вар.	0	1	2	3	4	5	6	7	8	9	10	11	12
d	1100	1070	1140	1030	1180	990	1220	950	1260	910	1300	870	1340
w	250	240	260	230	270	240	260	230	270	240	260	230	270
h	300	290	280	270	260	250	240	310	320	330	340	350	360
Tp1	4	4,4	3,6	4,8	3,2	5,2	2,8	5,6	2,4	6	2	6,4	1,6
Tp2	4	10	5	9	6	8	7	5	8	6	9	7	10
Tp3	8	15	10	13	9	13	10	8	11	10	15	14	16
Tp4	10	16	12	14	10	14	11	9	14	13	18	16	20
P1	40	22	19	6	27	16	9	25	11	8	30	14	7
P2	14	16	12	11	7	5	13	3	6	8	10	2	9
Пр1	10	4	9	5	2	6	4	7	4	3	5	8	6
Пр2	100	150	170	250	180	130	190	120	200	110	210	140	220
Пр3	3	4	5	6	7	8	9	10	11	12	13	14	15
ФВ1	7	7,1	7,2	7,3	7,4	7,5	7,6	7,7	7,1	7,2	7,0	7,3	7,4
ФВ2	7,5	7,6	7,7	7,0	7,1	7,2	7,3	7,4	7,5	7,6	7,7	7,1	7,2
ФВ3	7,4	7,5	7,6	7,7	7,8	7,4	7,5	7,6	7,7	7,8	7,4	7,5	7,6
Z1	400	390	365	380	415	370	405	350	395	410	385	420	375
Z2	230	240	235	220	215	200	195	180	205	160	175	140	155
Z3	260	200	250	190	240	180	230	290	220	230	210	270	200
K1	14	15	5	16	6	17	7	12	8	13	18	11	9
K2	10	11	12	5	13	6	14	7	15	8	16	9	17
V1	50	20	65	40	55	75	45	60	35	70	25	30	80
V2	350	400	360	300	370	310	380	320	390	330	410	340	420
V3	5300	2000	3700	3000	1100	4000	2500	1500	1400	2700	4300	3100	1900
N	40	45	67	50	72	55	44	60	38	65	30	70	35
Oct	100	110	90	170	80	160	70	150	60	140	50	120	40

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

№ вар.	0	1	2	3	4	5	6	7	8	9	10	11	12
Д	60(А,В2)	15А	10В1	15(В1,В2)	43(А,В1)	72А	12В2	16(В1,В2)	23(А,В2)	46А	59В1	13(В1,В2)	9(А,В1)
3	50В2	30А	15В1	10А, 18В1	5А, 12В2	40В1, ЗВ2	60В2	24А	80В1	14А, 21В1	38 А, 62В2	23В1, 20В2	84В2
С1	205	210	145	200	150	215	170	220	165	225	180	230	195
С2	142	150	125	164	120	187	125	176	129	195	143	207	126
С3	160	170	133	178	134	205	148	197	142	210	162	214	146
Ц1	295	256	213	284	192	243	198	274	203	281	224	276	249
Ц2	182	202	149	190	154	230	175	246	194	263	214	287	186
Ц3	220	224	158	206	147	243	180	242	167	267	202	246	187
3 варіанти розкрою листів ДСП; 8 ч в зміні; робота в 1 зміну; 22 робочих дня в місяці

PAGE  6

1. Тема- Патофізіологія системи крові До клініки поступив чоловік віком 40 років якого укусила гадюка
2. PgeRnk- начала анализа
3. тематик и естествоиспытатель основоположник прагматизма и семиотики
4. Проектирование дискретной системы передачи данных
5. Ярославський іконопис XVII століття
6. Вплив анархістських доктрин на світогляд українських істориків
7. Решение проблем в маркетинговой деятельности торговой фирмы Одеон
8. В вопросительных предложениях английского языка содержится определенный вопрос для получения необходимой
9. Сокрытые сокровища Партнерам Миссии Кеннета Коупленда
10. Бронхиальная астма и ее лечение
11. Оформление паспорта гражданина Республики Казахстан 1
12. IV в.н.э.. Уйсуни. Обитали в Семиречье на землях тиграхауда
13. Культурная унификация в сфере питания как отражение функционализации телесного опыт
14. Учет выбытия основных средст
15. і Та це не зламало дух Франка і він все одно залишається прибічником своїх поглядів це призвело до другого ар
16. От того каким будет это решение зависит вся деятельность организации зависит и то будет ли достигнута пос
17. реферату- Операції з торгівлі іноземною валютою на міжбанківському валютному ринку УкраїниРозділ- Банківськ
18. Колумбия увы немногочисленны- аромат крепкого кофе волшебный слог Габриэля Гарсиа Маркеса и конечно мог
19. мотивы диверсификации производства Можно выделить три процесса мотива диверсификации
20.  Карта личности- Диагностика энергетики и потенциалов человека- 20 уе

Материалы собраны группой SamZan и находятся в свободном доступе