Как остановить поиск решений в excel

Примечание: Отношения int, бин и раз можно использовать только в ограничениях для ячеек, в которых находятся переменные решения.

Необходимые действия

Принять ограничение и добавить другое

Нажмите кнопку Добавить.

Принять ограничение и вернуться в диалоговое окно Параметры поиска решения

Нажмите кнопку ОК.

1. Чтобы прервать поиск решения, нажмите клавишу ESC. Лист Excel будет пересчитан с учетом последних найденных значений для ячеек переменных.
2. Чтобы создать отчет, основанный на найденном решении, выберите тип отчета в поле Отчеты и нажмите кнопку ОК. Отчет будет помещен на новый лист книги. Если решение не найдено, отчет не будет доступен.
3. Чтобы сохранить значения ячейки переменной решения в качестве сценария, который можно будет отобразить позже, нажмите кнопку Сохранить сценарий в диалоговом окне Результаты поиска решения, а затем введите имя этого сценария в поле Название сценария.

Просмотр промежуточных результатов поиска решения

1. В Excel 2016 для Mac: выберите пункты Данные >Поиск решения. В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите Поиск решения.
2. После постановки задачи нажмите кнопку Параметры в диалоговом окне Параметры поиска решения.
3. Чтобы просмотреть значения всех предварительных решений, установите флажок Показывать результаты итераций и нажмите кнопку ОК.
4. В диалоговом окне Параметры поиска решения нажмите кнопку Найти решение.
5. В диалоговом окне Показать предварительное решение выполните одно из следующих действий:

Изменение способа поиска решения

1. В Excel 2016 для Mac: выберите пункты Данные >Поиск решения. В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите Поиск решения.
2. Нажмите кнопку Параметры, а затем в диалоговом окне Параметры или Поиск решения выберите один или несколько из следующих вариантов:

Примечание: Если будет достигнуто максимальное время поиска решения или количество итераций, а решение еще не будет найдено, средство «Поиск решения» выведет диалоговое окно Показать предварительное решение.

Сохранение или загрузка модели задачи

1. В Excel 2016 для Mac: выберите пункты Данные >Поиск решения. В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите Поиск решения.
2. Щелкните Загрузить/сохранить, укажите диапазон ячеек для области модели и нажмите кнопку Сохранить или Загрузить. При сохранении модели введите ссылку на первую ячейку вертикального диапазона пустых ячеек, в котором следует разместить модель оптимизации. При загрузке модели введите ссылку на весь диапазон ячеек, содержащий модель оптимизации.

Совет: Чтобы сохранить последние параметры, настроенные в диалоговом окне Параметры поиска решения, вместе с листом, сохраните книгу. Каждый лист в книге может иметь свои параметры надстройки «Поиск решения», и все они сохраняются. Кроме того, для листа можно определить более одной задачи, если нажимать кнопку Загрузить/сохранить для сохранения задач по отдельности.

Методы поиска решения

1. В Excel 2016 для Mac: выберите пункты Данные >Поиск решения. В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите Поиск решения.
2. Во всплывающем меню Выберите метод решения выберите одно из следующих значений:

Метод решения

Нелинейный метод обобщенного понижающего градиента (ОПГ)

Используется по умолчанию для моделей со всеми функциями Excel, кроме ЕСЛИ, ВЫБОР, ПРОСМОТР и другие ступенчатые функции.

Поиск решения линейных задач симплекс-методом

Используйте этот метод для задач линейного программирования. В формулах модели, которые зависят от ячеек переменных, должны использоваться функции СУММ, СУММПРОИЗВ, +, — и *.

Эволюционный поиск решения

Этот метод, основанный на генетических алгоритмах, лучше всего подходит в том случае, если в модели используются функции ЕСЛИ, ВЫБОР и ПРОСМОТР с аргументами, которые зависят от ячеек переменных.

Примечание: Авторские права на части программного кода надстройки «Поиск решения» версий 1990–2010 принадлежат компании Frontline Systems, Inc. Авторские права на части версии 1989 принадлежат компании Optimal Methods, Inc.

Поскольку надстройки не поддерживаются в Excel в Интернете, вы не сможете использовать надстройку «Поиск решения» для анализа данных «что если», чтобы найти оптимальные решения.

Если у вас есть Excel, вы можете нажать кнопку Открыть в Excel, чтобы открыть книгу для использования надстройки «Поиск решения».

Дополнительная справка по надстройке «Поиск решения»

За дополнительной справкой по надстройке «Поиск решения» обращайтесь по этим адресам:

Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288
(775) 831-0300
Веб-сайт: http://www.solver.com
Электронная почта: info@solver.com
«Решение» на www.solver.com.

Авторские права на части программного кода надстройки «Поиск решения» версий 1990-2009 принадлежат компании Frontline Systems, Inc. Авторские права на части версии 1989 принадлежат компании Optimal Methods, Inc.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Как остановить поиск решений в excel

Функция Поиск решения в Excel. Включение, пример использования со скриншотами

«Поиск решения» является надстройкой Эксель, посредством которой возможно подобрать лучшее решение задач на основе указанных ограничений. Функция дает возможность составлять график работников, распределять расходы либо инвестиционные вложения. Знание принципа работы такой функции поможет сэкономить время и силы.

Что такое Поиск решений

В комплексе с различными другими опциями в Excel существует одна менее популярная, однако крайне нужная функция «Поиск решения». Невзирая на то, что отыскать ее бывает нелегко, ознакомление с ней и использование помогает в разрешении множества задач. Опция обрабатывает данные и выдает оптимальное решение из допустимых. В статье описывается, как непосредственно функционирует «Поиск решения».

Как включить функцию «Поиск решения»

Невзирая на эффективность, рассматриваемая опция не находится на видном месте панели инструментов либо контекстного меню. Большинство юзеров, которые работают в Эксель, не знают о ее наличии. По умолчанию такая функция выключена, для ее отображения следует произвести такие действия:

1. Открываем «Файл», нажимая на соответствующее название.
2. Кликаем на раздел «Параметры».
3. Затем выбираем подраздел «Надстройки». Тут будут отображены все надстройки программы, внизу появится надпись «Управление». С правой стороны от нее будет всплывающее меню, где следует выбрать «Надстройки Excel». Потом нажимаем «Перейти». 1
4. На мониторе высветится дополнительное окно «Надстройки». Устанавливаем флажок возле искомой функции и кликаем «ОК».
5. Нужная функция появится на ленте справа от раздела «Данные».

О моделях

Данная информация будет крайне полезна тем, кто лишь ознакамливается с понятием «оптимизационная модель». До того, как воспользоваться «Поиском решения», рекомендуется исследовать материалы о методах построения моделей:

• рассматриваемая опция даст возможность выявить оптимальный метод, чтобы осуществить выделение средств на вложения, загрузку помещения, поставку товаров либо иные действия, где необходимо отыскать оптимальный вариант решения.
• «Оптимальный метод» в такой ситуации будет означать: увеличение доходов, снижение трат, улучшение качества и др.

Типовые задачи по оптимизации:

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

Важно! Чтобы формализовать поставленную задачу, необходимо создать модель, отражавшую основные параметры предметной области. В Эксель модель является комплексом формул, использующих переменные. Рассматриваемая опция подыскивает такие показатели, чтобы целевая функция была больше (меньше) либо равнялась указанному значению.

Подготовительный этап

Перед тем как разместить функцию на ленте, необходимо изучить принцип функционирования опции. К примеру, есть сведения по реализации товаров, указанные в таблице. Задачей является назначение для каждого наименования скидки, которая составляла бы 4.5 млн. рублей. Параметр отображается внутри ячейки, именуемой целевой. Отталкиваясь от нее, рассчитываются прочие параметры.

Нашей задачей станет вычисление скидки, на которую умножаются суммы по реализации различной продукции. Эти 2 элемента связываются формулой, прописываемой так: =D13*$G$2. Где в D13 прописывается суммарное количество по реализации, а $G$2 – адрес искомого элемента.

Применение функции и ее настройка

Когда формула будет готова, необходимо использовать непосредственно саму функцию:

1. Нужно переключиться в раздел «Данные» и нажать «Поиск решения».

1. Откроются «Параметры», где задаются требуемые настройки. В строке «Оптимизировать целевую функцию:» следует указать ячейку, где выводится сумма по скидкам. Есть возможность прописать координаты самостоятельно или выбрать из документа.

1. Далее нужно перейти к настройкам прочих параметров. В разделе «До:» есть возможность задать максимальную и минимальную границу либо точное число.

1. Потом заполняется поле «Изменяя значения переменных:». Здесь вносятся данные искомой ячейки, которая содержит конкретное значение. Координаты прописываются самостоятельно или кликается соответствующая ячейка в документе.

1. Затем редактируется вкладка «В соответствии с ограничениями:», где задаются ограничения применяемых данных. К примеру, исключаются десятичные дроби либо отрицательные числа.

1. После открывается окно, которое позволяет добавлять ограничения при расчетах. В начальной строке указываются координаты ячейки либо целого диапазона. Следуя условиям задачи, указываются данные искомой ячейки, где выводится показатель скидки. Затем определяется знак сравнения. Устанавливается «больше либо равно», чтобы конечное значение не было со знаком «минус». «Ограничение», устанавливаемое в 3 строке, в такой ситуации равняется 0. Возможно выставить также ограничение посредством «Добавить». Последующие действия аналогичны.

1. Когда выполнены вышеописанные действия, в самой большой строке появляется установленное ограничение. Перечень бывает большим и будет зависеть от сложности расчетов, однако в конкретной ситуации достаточно 1 условия.

1. Кроме того, возможно выбирать другие дополнительные настройки. Внизу с правой стороны присутствует опция «Параметры», которая позволяет это сделать.

1. В настройках можно выставить «Точность ограничения» и «Пределы решения». В нашей ситуации использовать эти опции нет нужды.

1. Когда настройки завершены, запускается сама функция – нажимается «Найти решение».

1. После программа проводит требуемые расчеты и выдает конечные расчеты в необходимых ячейках. Потом открывается окно с результатами, где сохраняются/отменяются итоги либо настраиваются параметры поиска по новой. Когда данные соответствуют требованиям, то найденное решение сохраняется. Если заранее установить отметку «Вернуться в диалоговое окно параметров поиска решения», будет открыто окно с настройками функции.

1. Есть вероятность, что расчеты оказались ошибочными или есть необходимость в изменении исходных данных в целях получения других показателей. В такой ситуации требуется вновь открыть окно с настройками и перепроверить сведения.
2. Когда данные точны, можно воспользоваться альтернативным методом. В этих целях нужно нажать на текущий вариант и из появившегося списка выбрать самый подходящий способ:

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

Внимание! Когда вышеназванные варианты не смогли справиться с задачей, следует осуществить проверку данных в настройках снова, так как это зачастую бывает основной ошибкой в таких задачах.

1. Когда получена искомая скидка, остается ее применить для подсчета суммы скидок по каждому наименованию. В этих целях выделяется начальный элемент столбика «Сумма скидки», прописывается формула «=D2*$G$2» и жмется «Enter». Значки доллара проставляются, чтобы во время растягивания формулы на смежные строчки G2 не изменялась.

1. Теперь будет получена сумма скидки для начального наименования. Затем следует навести курсор на угол ячейки, когда он станет «плюсом», зажимается ЛКМ и формула растягивается на необходимые строки.
2. После этого таблица будет окончательно готова.

Загрузить/сохранить параметры Поиска решений

Данная опция полезна при применении различных вариантов ограничений.

1. В меню «Параметры поиска решения» следует нажать «Загрузить/сохранить».
2. Вводится диапазон для области модели и нажимается «Сохранить или Загрузить».

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

Важно! Для сохранения последних настроек в меню «Параметры поиска решения» сохраняется книга. Каждый лист в ней имеет собственные параметры надстройки «Поиск решения». Помимо того, для листа возможно выставить больше 1 задачи при нажатии кнопки «Загрузить или сохранить» в целях сохранения отдельных задач.

Простой пример использования Поиска решения

Нужно провести загрузку контейнера тарой, чтобы его масса была максимальной. Емкость обладает объемом в 32 куб. м. Наполненная коробка имеет вес в 20 кг, ее объем равен 0,15 куб. м. Ящик – 80 кг и 0,5 куб. м. Требуется, чтобы общее число тары составляло не менее 110 шт. Данные организовываются так:

Переменные модели отметим зеленым. Целевая функция выделяется красным. Ограничения: по наименьшему количеству тары (больше либо равно 110) и по массе (=СУММПРОИЗВ(B8:C8;B6:C6) – суммарный вес тары, находящейся в контейнере.

По аналогии считаем общий объем: =СУММПРОИЗВ(B7:C7;B8:C8). Такая формула необходима, чтобы выставить ограничение на суммарный объем тары. Потом посредством «Поиск решения» вводятся ссылки на элементы с переменными, формулами и самими показателями (либо ссылки на конкретные ячейки). Разумеется, что количество тары – целое число (также является ограничением). Нажимаем «Найти решение», в результате чего находится такое число тары, когда общая масса максимальна и учтены все ограничения.

Поиску решения не удалось найти решения

Такое уведомление выскакивает, когда рассматриваемая функция не нашла сочетаний показателей переменных, удовлетворяющих каждому ограничению. При использовании Симплекс-метода вполне возможно, что решения нет.

Когда используется способ решения нелинейных задач, во всех случаях начинающийся с начальных показателей переменных, это свидетельствует о том, что возможное решение далеко от таких параметров. Если запустить функцию с прочими начальными показателями переменных, то, вероятно, решение найдется.

К примеру, во время использования нелинейного способа, элементы таблицы с переменными не заполнялись, и функция не нашла решений. Это не значит, что решения нет. Теперь, с учетом результатов определенной оценки, в элементы с переменными вводятся другие данные, близкие к получаемым.

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

В вышеуказанном примере показатель максимального объема указан 16 куб. м вместо 32, потому такое ограничение противоречит показателям по минимальным количествам мест, поскольку ему будет соответствовать число 16,5 куб. м.

Заключение

Исходя из этого, опция «Поиск решения» в Excel поможет в разрешении конкретных задач, которые довольно трудно либо невозможно решить обычными способами. Сложность в применении такого метода состоит в том, что изначально эта опция скрыта, ввиду чего большинство пользователей не знают о ее наличии. Кроме того, функция достаточно сложна в изучении и использовании, однако при надлежащем исследовании, она принесет большую пользу и облегчит расчеты.

Поиск решений в Excel
для экономиста

Поиск решения — это надстройка программы Excel, по умолчанию она не установлена, поэтому, если Вы никогда ранее ее не использовали, ее нужно настроить.

На одном листе Excel будут расположены все исходные данные, формулы, взаимосвязи и ограничения: это называется математическая модель . В составе модели пять типов данных:

Попробуйте простыми расчетами решить такую задачу:

Компания занимается производством шкатулок ручной работы. В штате есть 4 мастера-надомника. Производительность мастеров в день представлена в таблице:

Мастер 1 — 3 шкатулки в день
Мастер 2 — 1,5 шкатулки в день
Мастер 3 — 2 шкатулки в день
Мастер 4 — 2,5 шкатулки в день

Эту задачу можно решить простыми расчетами, без использования поиска решений. Для начала так и поступим:

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

3+1,5+2+2,5 = 9 шкатулок.

Теперь 100 шкатулок разделим на 9 шкатулок в день и получим 11,11 дней. Соответственно, сообщаем заказчику, что заказ будет готов за 12 дней

Распределим заготовки между мастерами с использованием округления:

Мастер 1: 3 х 11,11 = 33,33 Выдаем 34 заготовки
Мастер 2: 1,5 х 11,11 = 16,66 Выдаем 17 заготовок
Мастер 3: 2 х 11,11 = 22,22 Выдаем 21 заготовку
Мастер 4: 2,5 х 11,11 = 27,77 Выдаем 28 заготовок

А теперь дополним условие и введем индивидуальные тарифные ставки для мастеров за изготовление каждой шкатулки:

Мастер 1 — 1 500 руб
Мастер 2 — 950 руб
Мастер 3 — 1 100 руб
Мастер 4 — 1 150 руб

Рассчитаем для клиента, сколько стоит изготовить 100 шкатулок

34 х 1 500 = 51 000
17 х 950 = 16 150
21 х 1 100 = 23 100
28 х 1 150 = 32 200
Итого: 122 450

А теперь клиент задает нам вопрос, а если бы заказ был не срочный, во сколько минимально он мог бы обойтись? Как Вы думаете?

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

Минимальный срок изготовления: 11,11 дней,
стоимость 122 450

Максимальный срок изготовления: 66,66 дней,
стоимость 95 000

Разница в сроке составляет 55,55 дней, а в сумме 27 450.

Вполне логично предположить, что клиент может задать вопрос:
А если я дам Вам срок 20 дней или месяц, как изменится стоимость?

Создайте новый файл в программе Excel.
Проверьте, есть ли кнопка «Поиск решения» в закладке «Данные», если нет, то здесь инструкция, как ее установить.

Наша цель: как распределить заготовки, чтобы заказ был выполнен за 20 дней и стоимость была минимальной? Сколько будет стоить в этом случае выполнение заказа?

Попытайтесь сами ответить на вопросы и записать ответы:
1. Какие у нас есть константы?
2. Что будет переменными?
3. Целевая функция
4. Ограничения

1. Устанавливаем целевую ячейку E13. Это целевая функция , которая равна общей стоимости заказа. Переключаем цель, чтобы она была равной минимальному значению.

2. Вносим диапазон переменных D9:D12 в поле «Изменяя ячейки».

3. Вносим ограничения:
D13 (общее число заготовок) = 100 (внесем не значение 100, а ячейку D3, чтобы в дальнейшем можно было изменить количество шкатулок в задаче)
Диапазон переменных D9:D12 = целые
Диапазон переменных D9:D12 >= 0 (неотрицательные)
F13 ( срок выполнения заказа : в эту ячейку внесена формула, которая выбирает максимальное значение из сроков по каждому мастеру, она выглядит так =МАКС(F9:F12)) (внесем не значение 20, а ячейку D6, чтобы можно было изменять)

Задание: Определить оптимальный план производства, максимизирующий прибыль.

Колонка «Значение»
По строкам 1 и 2 в будет внесено оптимальное количество продуктов. Сейчас их оставляем пустыми, функция сама их заполнит. На эти ячейки ссылаются все последующие формулы.
По строке 3 «Целевая функция» — вносим формулу со ссылкой на ячейки Х и У выше. Пока она равна нулю, ПОИСК РЕШЕНИЯ будет ее максимизировать и заполнит значение.
Далее по строкам 4-10 вносим ограничения в виде формул. Например, 3х+2у, также со ссылками на ячейки Х и У.

Колонка «Ограничение»
Ограничения можно внести как в ячейки на листе, так и непосредственно в окне «Поиск решения». В данном случае они внесены в таблицу, чтобы можно было заполнить формулу в следующей колонке (которая называется «Излишек»), т.к. она участвует в финальной матрице.

Колонка «Излишек»
Здесь вносим формулу: Ограничение — Значение. После того, как функция рассчитает все значения, мы сразу сможем видеть ограниченные ресурсы и излишки.

Итак, таблица готова и выглядит, как приведено выше.

Максимальное значение прибыли 62 625 долл , достигается при выпуске оптимального ассортимента: Продукт Е — 375 ед, Продукт R — 750 ед.

Смотрим колонку «Избыток»
Имеется избыток Материала А и материала В в размере 2 375 ед и 1 650 ед, соответственно.
На продукт У имеется нереализованный спрос 750 ед.

Оборудование и труд являются ограничивающими ресурсами. Это значит, что если бы у нас имелись дополнительные единицы этих ресурсов, мы могли бы произвести еще продукт и получить дополнительную прибыль.

Поиск решения позволяет проводить углубленный анализ модели и рассчитывает » теневую цену » — сложнейшее для понимания экономистов понятие.
Надбавка к номинальной цене за единицу ограниченного ресурса, которую имело бы смысл заплатить, чтобы получить еще одну дополнительную единицу ограниченного ресурса, называется теневая цена.

Теневая цена ограниченного ресурса -это дополнительная маржинальная прибыль, которая возникла бы, если бы имелась одна дополнительная единица ограниченного ресурса, либо потерянная маржинальная прибыль, которая возникла бы, если бы объем ограниченного ресурса был на единицу меньше.

Теневую цену смотрим из отчета по устойчивости:

Теперь объясню, что это значит на цифрах.

Еще одна дополнительная единица продукта, которую мы могли бы произвести, при наличии еще одной единицы ограниченного ресурса, представляет собой условную единицу. Это не одна единица E или R, а условная единица, в состав которой входят и Е, и R в той пропорции, в которой определен оптимальный план

Продукты E и R входят в оптимальный план в пропорции 375:750 или 1:2. Давайте рассмотрим минимальный набор оптимального плана, который включает 3 единицы: 1 единицу продукта Е и 2 единицы продукта R.

Вернемся к таблице с расчетом маржинальной прибыли:

Если мы приобретем все ресурсы, которые входят в состав по тем же ценам, что и прежде, то получим маржинальную прибыль 1 х 35 + 2 х 66 = 167$

Однако ресурс работы оборудования и рабочее время рабочих исчерпаны, а все остальные ресурсы есть. Значит, если мы можем арендовать дополнительную единицу оборудования и нанять еще рабочих, можно было бы доплатить. Сейчас оборудование стоит 7$ в час, труд – 10$ в час и мы и имеем маржинальную прибыль. Теневая цена показывает, что можно доплатить за 1 час работы оборудования — 10,75 (тогда он будет стоить 17,75), а за 1 час труда — 6,75 (будет стоить 16,75) — это максимальные суммы, при которых маржинальная прибыль будет равна нулю:

Есть группа из 3 компаний. ООО «Крона» закупает телефоны в Китае, ООО «Стрим» продает телефоны мелкооптовыми партиями по России, ООО «Маркет» торгует телефонами через розничные точки.

Стрим и Маркет закупают телефоны у Кроны. Учитывая, что все 3 юридических лица входят в группу и имеют одного собственника, их можно рассматривать как подразделения одной компании. Перед финансовым директором стоит задача запланировать оптимальные трансфертные цены.

Маркет несет дополнительные расходы в виде 30% процентов от маржинальной прибыли по сделкам, это премия управляющему директору. Сумма премии при выполнении плана реализации в 1000 штук в квартал, не может быть меньше 150 000 руб.

Ограничения:
В целях минимизации налоговых рисков в задаче установлены следующие ограничения:

Цена
Минимальная трансфертная цена не может быть ниже себестоимости, увеличенной на 5%.
Максимальная трансфертная цена для компании не может быть больше чем средняя продажная цена покупателям уменьшенная на 5%.
Цены не должны отличаться между собой не более, чем на 20%.

Прибыль
Прибыль после уплаты налогов по каждой компании должна составить не менее 1% от выручки.

Оптимальные трансфертные цены должны удовлетворять следующим условиям:

1) распределять налоговую нагрузку внутри компании с целью минимизации налога на прибыль

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

3) обеспечить справедливую оценку деятельности подразделений

Выручка по Кроне рассчитывается по формуле:
количество единиц, проданных со Стрима, умноженное на трансфертную цену + количество единиц проданных с Маркета, умноженное на трансфертную цену
Выручка по Стриму и Маркету нам известна: умножаем продажи на цену

Себестоимость по Кроне нам известна: умножаем общую сумму продаж со Стрима и Маркета на покупную цену.
По Стриму и Маркету в расчете себестоимости участвуют трансфертные цены, поэтому заполним формулы, но значения пока равны нулю.

Маржинальная прибыль рассчитывается по формуле: выручка минус себестоимость

Налог. Для Кроны и Стрима используется формула маржинальная прибыль на ставку налога, у Маркета другая формула: выручка на 6%.

Премия управляющему : вносим только для Маркета: маржинальная прибыль, умноженная на 30%

Прибыль после уплаты налога по формуле: маржинальная прибыль минус премия минус налог

Целевая функция. Сумма прибыли после уплаты налога по всем компаниям

Рядом с премией управляющему сразу установим ограничение >= 150 000

Минимальное ограничение цены: 4 400 х 1,05 = 4 620
Максимальная возможная цена на Стрим: 15 200 х 0,95 = 14 440
Максимальная возможная цена на Маркет: 17 860 х 0,95 = 17 860

Для того, чтобы задать ограничение по диапазону удобно использовать формулу отношение одной цены к другой. Предварительно требуется прикинуть, какая из цен будет стремиться в большую сторону. В данном случае я сделала оценку, что цена на Маркет чем больше, тем лучше, потому что ставка премии управляющему директору выше, чем ставка налога в Кроне, а цена на Стрим, наоборот, чем меньше, чем лучше, потому что ставка налога в Стриме ниже. Можно не делать предварительную оценку, а запустить поиск решения без учета этого ограничения: выяснить максимальную цену, а затем задать ограничение. Ограничение диапазона в пределах 20% будет выглядеть так:
Значение цена на Маркет/цена на Стрим