ЛАБОРАТОРНА РОБОТА №6.

 

Тема. Використання пакету MS Excel для прийняття управлінських рішень при розв’язанні оптимізаційних задач.

 

Мета: Закріплення теоретичного матеріалу за темою «Використання пакету MS Excel для прийняття управлінських рішень при розв’язанні оптимізаційних задач». Набуття практичних навичок розв’язання задач оптимізації з використанням табличного процесора MS Excel.

 

Завдання:

1. Оптимізувати виробничу програму підприємства з використанням процедури «Пошук рішень» пакету MS Excel.

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

2. Зробити висновки.

 

Хід роботи.

1.     Вибрати варіант з додатка 5, який відповідає порядковому номеру здобувача в списку групи.

2.     На виготовлення двох видів продукції А та В витрачається три види ресурсів І, ІІ, ІІІ. Запаси ресурсів, норми їх витрат, прибуток від реалізації одиниці продукції задані в таблиці. Визначити такий план виробництва, який забезпечував би найбільший прибуток при заданих ресурсах.

3.     Скопіювати на лист MS Excel таблицю з вихідними даними (таблиця 6.1).

Таблиця 6.1

Вихідні дані для виконання лабораторної роботи

Ресурси

Норми витрат ресурсів на виготовлення одного виробу

Наявність ресурсів

А

В

І

13

7

361

ІІ

17

16

520

ІІІ

4

9

248

Прибуток від реалізації одного виробу, грн.

11

8

-

4.     Записати математичну модель задачі:

F(x)=11∙х1 +8∙х2 → max

13∙х1 +7∙х2 ≤ 361

17∙х1 +16∙х2 ≤ 520

4∙х1 +9∙х2 ≤ 248

х1 ≥ 0, х2 ≥ 0.

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

6.     Початкові значення змінних задати 0.

7.     В комірку для визначення цільової функції ввести формулу з використанням вбудованої функції MS Excel категорії «математичні» SUMPRODUCT, яка використовується для розрахунку суми добутків відповідних елементів масивів, в даному випадку горизонтальних масивів значень змінних х1, х2 і прибутку від реалізації кожного з виробів (11∙х1+8∙х2).

8.     Ввести формулу для розрахунку лівої частини кожної з нерівностей системи обмежень з використанням функції SUMPRODUCT.

9.     Викликати програму «Пошук рішення» і отримати розв’язок задачі.

10. Зробити висновки про оптимальний план виробництва, рівень використання ресурсів і значення цільової функції при оптимальному плані виробництва.

 

Література: [13; 29; 31].

 

 

Додаток 5

Дані для виконання лабораторної роботи №6

Варіант

Норми витрат

Обсяг ресурсів

Прибуток

І

ІІ

ІІІ

І

ІІ

ІІІ

А

В

А

В

А

В

А

В

1

13

7

17

16

4

9

361

520

248

11

8

2

1

1

4

7

1

4

18

93

48

24

36

3

3

2

2

3

1

1

101

99

37

27

24

4

4

13

5

6

11

5

379

197

335

25

12

5

3

1

9

4

3

4

45

144

96

9

8

6

14

15

1

2

9

5

400

49

220

21

18

7

11

6

1

2

15

14

324

60

500

10

7

8

2

1

1

5

4

15

48

100

325

12

9

9

3

8

7

2

1

1

187

143

29

10

6

10

2

7

1

1

6

1

126

30

120

20

15

11

9

4

3

2

2

2

175

65

60

15

10

12

2

3

2

2

3

2

80

58

75

10

12

13

5

2

2

3

1

8

125

83

152

12

10

14

3

2

4

1

7

8

65

70

235

30

20

15

2

2

7

2

3

8

58

143

197

15

21

16

1

1

12

5

1

4

37

360

100

12

9

17

2

1

2

5

3

4

34

105

91

9

7

18

4

7

5

14

2

1

196

350

68

15

30

19

14

15

2

1

6

11

500

60

324

14

10

20

14

3

2

2

2

13

280

62

260

15

18

21

3

2

2

2

2

3

75

58

80

15

18

22

5

2

4

3

3

6

98

84

91

18

10

23

1

2

4

1

2

15

51

120

300

6

9

24

2

5

4

3

2

4

80

91

68

15

12

25

18

15

5

11

13

4

591

335

379

12

22

26

13

7

17

16

4

9

400

49

220

12

10

27

1

1

4

7

1

4

324

60

500

30

20

28

3

2

2

3

1

1

48

100

325

15

21

29

4

13

5

6

11

5

187

143

29

12

9

30

3

1

9

4

3

4

126

30

120

9

7