Инструкция по использованию Microsoft Excel. Две конкурирующие коммерческие организации а и в выпускают продукцию одного вида

Две конкурирующие коммерческие организации А и В выпускают продукцию одного вида. Каждая организация пла­нирует проведение рекламной акции, причем маркетологи каждой компании предложили четыре сценария ее проведения A 1, A2, A3,A4 - для компании А и B 1, B2, B3, B4 - для компа­нии В. Ожидаемая прибыль для кампании А при каждой ее стратегии A и ответе Bj представлена в платежной матрице:

Ai \ Bj	B1 70 60 20 50	B2 30 50 60 70	B3 20 40 80 30	B4 50 80 60 50
A1
A2
A3
A4

Необходимо найти оптимальные стратегии для обоих игроков А и В в предположении, что чем больше выигрыш одного игрока, тем он меньше для другого. Определить среднюю прибыль А.

Данную задачу нельзя свести к задаче меньшей размерности, так как ее каждая строка не меньше, чем другая строка, а каждый столбец не больше другого столбца. Построим задачу линейного про­граммирования. Рассмотрим задачу со стороны игрока А. Введем па­раметры, пропорциональные вероятностям чистых стратегий, которые равны х₁, х₂, х₃, х₄. Тогда нужно составить задачу линейного про­граммирования, то есть необходимо найти минимум функции при ог­раничениях:

Для решения данной ЗЛП на ЭВМ также используют над­стройку EXCEL «Поиск решения» (Solver Add - in).

Подготовим предварительно в электронной таблице данные. Запускаем программу MS Excel, вводим в ячейку А1 открывшейся электронной таблицы подпись «Переменные», а в следующие ячейки В1-Е1, произвольные значения переменных х₁, х₂, х₃, х ₄. Это внача­ле могут быть произвольные числа, например единицы. Вводим в ячейки В1-Е1 в каждую цифры 1.

Далее, в ячейку А2 вводим подпись «Целевая», а в соседнюю
ячейку В2 значение целевой функции (переключившись в английский
режим набора текста): «=B1+С1+D1+Е1», что означает формулу
_{Х 1} + _{Х 2} + _{Х 3} + _{Х 4}. В третьей строке вводятся левые части системы огра­
ничений. Для этого переводим курсор в ячейку А3 и вводим в ней
текст «Ограничения», а в ячейку В3 формулу
«=70*В1+60*C1+20*D1+50*E1», которая соответствует левой части
первого ограничения системы 70х₁ + 60х₂ + 20х₃ + 50х₄ > 1. Три ос­
тальных ограничения вводим в ячейки С3-В3, а именно, в ячейку С3:
«=30*В1+50*C1+60*D1+70*E1», в D3:

«=20*В1+40*C1+80*D1+30*E1», в ячейку Е3:

«=50*В1+80*C1+60*D1+50*E1». После этого вызываем надстройку Сервис/Поиск решения (Solver…), в поле «Установить целевую ячейку» (Set Target Cell) даем ссылку на В2. Ниже, в области «Равной», поставить переключатель на минимальное значение (Equal to … Max … Value of:). Ставим курсор в поле «Изменяя ячейки» (By Changing Cell), и даем ссылки на переменные, обводя мышью ячейки В1-Е1. Далее, переводим курсор в поле «Ограничения» (Subject to the Con­straints), и вводим ограничения. Для этого, нажимаем на кнопку «До­бавить» (Add) слева от поля и в появившемся окне в поле «Ссылка на ячейку» (Cell Reference) даем ссылку на ячейку, содержащую левую часть первого ограничения 70*₁ + 60х₂ + 20х₃ + 50х₄ > 1, которая хра­нится в ячейке В3 (то есть переводим курсор в поле «Ссылка на ячей­ку» (Cell Reference) и щелкаем мышью по ячейке В3). В центральном поле выбираем знак неравенства - ограничения: «≥», в поле «Ограни­чение» (Constraints) вводим единицу. Нажимаем «ОК». Вводим второе ограничение, нажимая «Добавить» (Add), вводим в поля: ссылку на «С3», «≥», «1», нажимаем «ОК», далее «Добавить» (Add), ссылку на «D3», «≥», «1», «ОК», «Добавить», ссылку на «Е3», «≥», «1», «ОК». Для ввода дополнительных ограничений х ₁ > 0; х ₂ > 0; х ₃ > 0; х ₄ > 0 нажимаем «Добавить», в поле «Ссылка на ячейку» (Constraints) ставим курсор и обводим ячейки В1-Е1, выводим в центральное поле «≥», ог­раничение «0», нажимаем «ОК». Далее запускаем программу, нажимая «Выполнить» (Solve). Результат: х ₁ = 0, х ₂ = 0,015, х ₃ = 0,05, х ₄ = 0, что видно из ячеек В1-Е1. Вводим в А5 подпись «Цена игры», а в со­седнюю В5 формулу (переключаясь на английский язык) «=1/(В1+С1+D1+Е1)». Результат: 50. Это средняя вероятность выиг­рыша для игрока А. Находим вероятности чистых стратегий в смешан­ной стратегии р. Для этого вводим в А6 подпись «Р1=», а в соседнюю В6 формулу «=В5*В1», вводим в А7: «Р2=», а в В7 формулу «=В5*С1», в А8: «Р3=», а в В8: «=В5*D1», в А9: «Р4=», в В9: «=В5*Е1». Данные показатели и есть решение задачи.

Рассмотрим теперь решение относительно игрока В.

Для него вводим переменные, пропорциональные вероятно­стям чистых стратегий у, у₂, у₃, у₄. ЗЛП для игрока В имеет вид:

Переходим на «Лист2» электронной таблицы, щелкнув на со­
ответствующей закладке внизу таблицы. Вводим в ячейки открывшей­
ся чистой электронной таблицы в ячейку А1 надпись «Переменные», а
в следующие ячейки, произвольные значения переменных, например,
вводим в ячейки В1-Е1 в каждую цифры 1. В ячейку А2 вводим под­
пись «Целевая». Вводим в ячейку В2 значение целевой функции (пе­
реключившись в английский режим набора текста): «=B1+С1+D1+Е1»,
что означает формулу у ₁ + у ₂ + _{У 3} + у ₄. В третьей строке вводятся ле­
вые части системы ограничений. Для этого переводим курсор в ячейку
А3 и вводим в ней текст «Ограничения». Переключившись в англий­
ский режим клавиатуры, вводим в ячейку В3 формулу
«=70*В1+30*C1+20*D1+50*E1», которая соответствует левой части
первого ограничения системы 70* +30х₂ +20х₃ + 50х₄ < 1. Вводим в
ячейку С3: «=60*В1+50*C1+40*D1+80*E1», в D3:

«=20*В1+60*C1+80*D1+60*E1», в ячейку Е3:

«=50*В1+70*C1+30*D1+50*E1». После этого вызываем надстройку в меню «сервис» и подменю «Поиск решений» (Solver…), открывается окно надстройки. В поле «Установить целевую ячейку» (Set Target Cell) даем ссылку на В2. Ниже, в области «Равной», поставить пере­ключатель на максимальное значение (Equal to … Max … Value of:). Ставим курсор в поле «Изменяя ячейки» (By Changing Cell), и даем ссылки на переменные, обводя мышью ячейки В1-Е1. Далее, перево­дим курсор в поле «Ограничения» (Subject to the Constraints), и вводим ограничения. Для этого, нажимаем на кнопку «Добавить» (Add) и да­лее в поле «Ссылка на ячейку» (Cell Reference) даем ссылку на ячейку В3, в центральном поле выбираем знак неравенства - ограничения: «≤», в поле «Ограничение» (Constraints) вводим единицу. Нажимаем «ОК». Вводим второе ограничение, нажимая «Добавить» (Add), вво­дим в поля: «С3», «≤», «1», нажимаем «ОК», далее «Добавить» (Add), ссылку на «D3», «≤», «1», «ОК», «Добавить» (Add), ссылку на «Е3», «≤», «1», «ОК». Для ввода дополнительных ограничений *> 0; *> 0; *> 0; *> 0 нажимаем «Добавить» (Add), в поле

«Ссылка на ячейку» (Cell Reference) ставим курсор и обводим ячейки В1-Е1, выводим в центральное поле «≥», ограничение «0», нажимаем «ОК». Далее запускаем программу, нажимая «Выполнить» (Solve). Результат решения ЗЛП в ячейках В1-Е1. Вводим в А5 подпись «Цена игры», а в соседнюю В5 формулу (переключаясь на английский язык) «=1/(В1+С1+D1+Е1)». Находим вероятности чистых стратегий q в смешанной стратегии игрока В. Для этого вводим в А6 подпись «q1=», а в соседнюю В6 формулу «=В5*В1», вводим в А7: «q2=», а в В7 формулу «=В5*С1», в А8: «q3=», а в В8: «=В5*D1», в А9: «q4=», в В9:«=В5*Е1». Данные показатели и есть решение задачи для игрока В.

ПРИМЕРНЫЕ ВОПРОСЫ НА ЗАЩИТЕ РАБОТЫ

1. Как называются методы принятия управленческих решений в ус­ловиях конфликта, когда в ситуации участвуют две стороны?

2.Как задачу свести к задаче меньшей размерности?

3. По какому плану решаются задачи методами теории игр?