Решение задач с помощью оптимизационного моделирования

· Прибыль от единицы этого  изделия составляет 18 денежных единиц

· Затраты сырья вида I – 3;

· Затраты сырья вида II – 3;

· Затраты сырья вида III - 3.

Рассчитываем нормированную  стоимость:

3*0+1,25*3+3*2=9,75 -затраты на одно изделие

18-9,75=8,25 - нормированная стоимость.

Следовательно, выпускать  изделие Д предприятию выгодно.

Три последних столбца определяют допустимый диапазон прибыли от единицы изделия. Целевая функция – это прибыль от единицы изделия. В данном случае она равна 3647,5 единицам.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Задача 2.

Выполнение второго задания предполагает определение штатного расписания организации, что включает в себя следующие действия:

1. построение математической модели решения задачи;
2. создание штатного расписания в Excel;
3. создание базы данных в Access «Штатное расписание»;
4. Экспорт штатного расписания из Excel в СУБД Access;
5. Создание нескольких вариантов запросов и организация на их основе отчетов.

    Условие ЗАДАЧИ 2:

Составить  штатное расписание завода, для обеспечения нормальной работы которой требуются 2-3 уборщицы, 4 гардеробщицы, 12-14 рабочих, 3-4 технологов, 1 начальника цеха, 1 заместителя директора, 1 директора завода. Фонд заработной платы составляет 1320 тысяч рублей. Зарплата рассчитывается, исходя из минимальной зарплаты, согласно выражению: k*x+a+b, где x- минимальная зарплата, равная 5,5 тысяч рублей, коэффициент a – постоянная надбавка, равная 25% от минимальной зарплаты, коэффициенты k и b устанавливаются руководством школы.

Решение:

Построение математической модели задачи.

За основу возьмем оклад  уборщицы, а остальные оклады будем вычислять, исходя из него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада уборщицы: k_i*x+a_i+b_i, где x - оклад уборщицы; k_i и b_i - коэффициенты, которые для каждой должности устанавливаются руководством школы, а коэффициент a – постоянная надбавка, равная 25% от минимальной зарплаты:

• гардеробщица получает 18,38 тыс.рублей (a₂=1,4; b₂=6; k₂=2);
• рабочий получает 46,88 тыс.рублей (a₃=1,4; b₃=7; k₃=7);
• технолог получает 80,73 тыс.рублей(a₄=1,4; b₄=9,5; k₄=12,7);
• начальник цех 101,58 тыс.рублей (a₅=1,4; b₅=10; k₅=16,4);
• заместитель директора 133,03 тыс.рублей(a₆=1,4; b₆=20; k₆=20,3);
• директор получает 196.38 тыс.рублей (a₅=1,4; b₅=30; k₅=30);

Зная предполагаемое количество человек на каждой должности, нашу модель можно записать как уравнение:

{N₁*(k₁*x+a₁+b₁)}+ {N₂*(k₂*x+a₂+b₂)} +...+{N₇*(k₇*x+a₇+b₇)} = 1320000,

где N₁ - число уборщиц, N₂ - число гардеробщиц и т.д.

В этом уравнении нам известны x, a₁...a₈, b₁...b₈, k₁... k₈, а N неизвестно.

Анализ уравнения показывает, что задача составления расписания свелась к решению линейного  уравнения относительно N. Решим его.

Для решения задачи составим таблицу:

В режиме просмотра формул эта таблица имеет вид:

При заполнении столбцов Зарплата, Суммарная зарплата и Месячный фонд заработной платы следует обратить внимание на смысл вводимых формул.

Затем нужно вызвать команду Поиск решения. В появившемся диалоговом окне Поиск решения: в качестве целевой ячейки необходимо установить адрес $H$2, где находится итоговое значение зарплаты по школе:

Установим значение целевой  ячейки равное числу 1320. Изменяемыми  ячейками, то есть ячейками результата, являются ячейки, содержащие зарплату уборщицы ($G$2) и количества сотрудников  каждой должности ($E$2:$E$8).Затем нужно  установить ограничения. В качестве ограничений нужно установить допустимый диапазон варьирования изменяемых ячеек. Например, количество уборщиц по условию  задачи должно быть больше 2, но меньше 3. При этом оно должно быть целым  значением Ограничения устанавливаются  с помощью кнопки Добавить.

В левой части окна указывается  адрес изменяемой ячейки, например адрес $E$2, содержащий количество уборщиц. Затем указывается нужный знак, например, <=, в правой части вводится предельно  допустимое значение, по условию задачи равное числу 6. Таким же образом, устанавливаются  условия: $E$2>=3 и $E$2=целое. Аналогично заполняются остальные ограничения. Путем нажатия кнопки Выполнить получим решение задачи:

Далее выводим Отчет по результатам:

 

 

 

 

Следующим шагом является создание базы данных в Access «Штатное расписание» и экспорт штатного расписания из Excel в СУБД Access:к

Далее следует создание нескольких запросов.

Первый  запрос идет на выборку. Простой запрос на выборку служит для отбора данных по какому-либо признаку или комбинации признаков. В данном случае выбираем должности. С зарплатой меньше 45,00р.:

 

Второй запрос идет с параметром, то есть при вызывании запроса  будет появляться диалоговое окно, где нужно будет выбрать должность, у которой нужно посмотреть зарплату, количество работников, коэффициенты a, b и k. При создании запроса в режиме конструктора вводим в строку «Условие отбора»: [Введите должность]. Далее сохраняем и смотрим, что получилось: диалоговое окно:

далее – результат:

Следующий запрос - итоговый. Итоговые запросы предназначены для вычисления суммы значений или среднего значения по всем ячейкам поля, также может выбираться максимальное или минимальное значение данных или выполняться какая-либо другая функция. Здесь посчитаем сумму коэффициентов b и k:

На основе трех запросов делаем 3 отчета соответственно:

 

 

 

 

Задача 3.

Выполнение третьего задания предполагает решение транспортной задачи, включающее:

1. построение математической модели
2. непосредственное решение задачи.

    Условие ЗАДАЧИ 3:

Пункты назначения											Наличие
B1		B2	B3		B4			B5
Пункты отправления	А1					98			105			150		196			201		750
	А2					64			74			184		39			39		400
	А3					40			11			16		53			80		200
Потребность				150			200			350			500		350

 

Решение:

В общем виде формулировка транспортной задачи осуществляется следующим  образом: требуется перевезти определенное количество однородного груза из m пунктов отправления в n пунктов  назначения. Известны расходы на перевозку  единицы груза из каждого пункта отправления в каждый пункт назначения.

Требуется составить такой  план прикрепления потребителей к поставщикам, т.е. план перевозок, при котором  весь груз от поставщиков вывозится, каждый потребитель получает требуемое  количество груза, и вместе с тем, общая  величина транспортных издержек минимальна.

Для составления экономико-математической модели задачи введем обозначения:

 n число пунктов отправления;

m число пунктов назначения;

ai общее количество груза в I-м пункте отправления;

bj общее количество груза, необходимое в j-м пункте назначения;

cij затраты на транспортировку единицы груза из I-го пункта

отправления в j-й пункт  назначения;

Z совокупные затраты на перевозку всего груза;

xij исходно неизвестное количество груза, которое перевозится из

i-го пункта отправления  в j-й пункт назначения.

Экономико-математическая модель задачи:

 

              n       m

 Z= å å ® c_y* x_{y        min  (1)}

  i=1     j=1

 

 

   m

å = x_y = a_i,    i= 1,n   (2)

 

   n

å = x_y = b_j,    j= 1,n    (3)

I=1           I

x_y ³ 0, _,    i= 1,n, _,    j= 1,n  (4)

В рассмотренной модели транспортной задачи предполагается, что суммарные  запасы поставщиков равны суммарынм  запросам потребителей, т.е.:

m                      n

å a_i  = åb_j

 i=1                        j=1

Такая задача называется задачей  с правильным балансом, а модель задачи закрытая

Математическая формулировка транспортной задачи такова: найти переменные задачи X=(x_ij), i=1,2,...,m; j=1,2,...,n, удовлетворяющие системе ограничений (цифра 2 на математической модели) , (3), условиям неотрицательности (4) и обеспечивающие минимум целевой функции (1).                               Целевая функция минимизирует совокупные затраты на транспортировку всех партий грузов из всех пунктов отправления во все пункты назначения. Система ограничений (2)говорит о том, что весь груз из каждого пункта его сосредоточения должен быть вывезен. Система ограничений (3) говорит о том, что потребность в грузе в каждом пункте назначения должна быть удовлетворена. Система ограничений (4) говорит о том, что по любому маршруту некоторое количество груза либо перевозится, либо нет.

Теперь составим модель транспортной задачи для данной задачи:

	Матрица исходных данных
Пункты отправления	Пункты назначения
	B1	B2	B3	B4	B5
	202	190	350	288	320
750	98	105	150	196	201
400	64	74	184	39	39
200	40	11	16	53	80

 

Вводим переменные задачи (матрицу перевозок):

        x₁₁   x₁₂   x₁₃   x₁₄   x₁₅

Х=   x₂₁   x₂₂   x₂₃   x₂₄   x₂₅

           x₃₁ x₃₂  x₃₃   x₃₄  x₃₅

 

 Записываем матрицу  стоимостей:

           98  105  150  196  201

C=      64   74    184  39   39

           40   11    16    53   80

 

Целевая функция  задачи равняется сумме произведений всех соответствующих элементов  матриц C и X.

Z(X)= 98x₁₁ + 105 x₁₂ + 150 x₁₃ + 196 x₁₄ + 201x₁₅ + 64x₂₁ + 74x₂₂ + 184x₂₃ +39x₂₄+    + 39x₂₅ + 40x₃₁ + 11x₃₂ + 16x₃₃ + 53x₃₄ + 80x₃₅

Составим систему ограничений  задачи. Сумма всех перевозок, стоящих в первой строке матрицы X, должна равняться запасам первого поставщика, а сумма перевозок во второй строке матрицы X равняться запасам второго поставщика:

    x₁₁ + x₁₂ +  x₁₃ + x₁₄  + x₁₅ = 750

    x₂₁ + x₂₂ +  x₂₃  + x₂₄  + x₂₅ = 400

       x₃₁ + x₃₂ + x₃₃  + x₃₄ +  x₃₅ = 200

 

Это означает, что запасы поставщиков  вывозятся полностью.

Суммы перевозок, стоящих в каждом столбце матрицы X, должны быть равны  запросам соответствующих потребителей: