Поиск решений как сделать в excel 2007

Включение функции

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

Для того, чтобы произвести активацию Поиска решений в программе Microsoft Excel 2010 года, и более поздних версий, переходим во вкладку «Файл». Для версии 2007 года, следует нажать на кнопку Microsoft Office в левом верхнем углу окна. В открывшемся окне, переходим в раздел «Параметры».

В окне параметров кликаем по пункту «Надстройки». После перехода, в нижней части окна, напротив параметра «Управление» выбираем значение «Надстройки Excel», и кликаем по кнопке «Перейти».

Открывается окно с надстройками. Ставим галочку напротив наименования нужной нам надстройки – «Поиск решения». Жмем на кнопку «OK».

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

Подготовка таблицы

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

Целевая и искомая ячейка должны быть связанны друг с другом с помощью формулы. В нашем конкретном случае, формула располагается в целевой ячейке, и имеет следующий вид: «=C10*$G$3», где $G$3 – абсолютный адрес искомой ячейки, а «C10» — общая сумма заработной платы, от которой производится расчет премии работникам предприятия.

Запуск инструмента Поиск решения

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

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

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

Ниже расположено поле «Изменяя ячейки переменных». Тут нужно указать адрес искомой ячейки, где, как мы помним, находится коэффициент, умножением на который основной заработной платы будет рассчитана величина премии. Адрес можно прописать теми же способами, как мы это делали для целевой ячейки.

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

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

Дополнительные настройки можно задать, кликнув по кнопке «Параметры».

Здесь можно установить точность ограничения и пределы решения. Когда нужные данные введены, жмите на кнопку «OK». Но, для нашего случая, изменять эти параметры не нужно.

После того, как все настройки установлены, жмем на кнопку «Найти решение».

Мы рады, что смогли помочь Вам в решении проблемы.

Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

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

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

Если версия Microsoft Office является оригинальной, тогда проблем с установкой не возникнет. Пользователю нужно сделать несколько переходов:

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

Зачем нужен Решатель?

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

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

Как работает Решатель?

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

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

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

Настройка параметров Поиска решений

Чтобы функция Поиска решений в Excel 2007 работала так, как необходимо пользователю, нужно ввести правильные параметры. Обычно они ограничиваются 1-3 характеристиками, но с более сложными задачами потребуется глобальная настройка.

Параметры в Поиске решений программы Office Excel 2007 могут быть следующими:

• Максимальное время – количество секунд, которые пользователь выделяет программе на решение. Оно зависит от сложности задачи.
• Максимальное число интеграций. Это количество ходов, которые делает программа на пути к решению задачи. Если оно увеличивается, то ответ не будет получен.
• Погрешность или точность, чаще всего применяется при решении десятичных дробей (к примеру, до 0,0001).
• Допустимое отклонение. Используется при работе с процентами.
• Неотрицательные значения. Применяется тогда, когда решается функция с двумя правильными ответами (например, +/-X).
• Показ результатов интеграций. Такая настройка указывается в случае, если важен не только результат решений, но и их ход.
• Способ поиска – выбор оптимизационного алгоритма. Обычно применяется «метод Ньютона».

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

Параметры задачи в функции Поиска решений

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

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

Формула в Excel

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

1. Функция. Это стандартная формула, где присутствует определенный и конкретный порядок действий, поменять который не удастся.
2. Ссылка. Она указывает на количество клеток, которые нужно решить. При этом ячейки могут располагаться хаотично или в определенном порядке.
3. Оператор. Это символ, который задает тип вычисления (+ – сложение, * – умножение и т.д.).
4. Константа. Постоянное значение, которое никогда не меняется. Также для его получения не нужно производить вычисления.

Решение формул осуществляется слева направо при соблюдении всех математических правил.

Создание формулы

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

Вычисление начинается со знака равенства. К примеру, если в ячейке указывается «=КОРЕНЬ(номер клетки)», то будет использована соответствующая функция.

После того как была напечатана основная формула со знаком «=», нужно указать на данные, с которыми она будет взаимодействовать. Это может быть одна или несколько ячеек. Если формула подходит для 2-3 клеток, то объединить их можно, используя знак «+».

Чтобы найти нужную информацию, можно воспользоваться функцией поиска. Например, если нужна формула с буквой «A», то ее и надо указывать. Тогда пользователю будут предложены все данные, ее в себя включающие.

Заключение

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

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

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

Краткое описание надстройки

Для получения заданного результата по формуле, процедура изменяет значения во влияющих ячейках. Для уменьшения интервала значений, используемых в модели, используются ограничения значений. Надстройка поиск решений является стандартной надстройкой Microsoft Office Excel и доступна сразу при установке Microsoft Office в целом или Microsoft Excel в частности.

Как установить надстройку?

Итак, запускаем установочный диск с пакетом приложений MS Office 2010 и выбираем опцию «Добавить или удалить компоненты».

Далее, нажимаем кнопку «Продолжить», в параметрах установки находим приложение Microsoft Excel, в компонентах этого приложения находим раздел «Надстройки», выбираем надстройку «Поиск решения» и устанавливаем параметр «Запускать с моего компьютера».

Опять жмем кнопку «Продолжить» и ожидаем пока надстройка установится.

Как подключить надстройку?

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

Где найти надстройку «Поиск решения» в Excel 2003/2007/2010?

После установки и подключения надстройки в Excel 2007/2010 на вкладке «Данные» появляется группа «Анализ» с новой командой «Поиск Решения». В Excel 2003 — появляется новый пункт меню «Сервис» с одноименным названием. Поиск решения — стандартная надстройка, существуют также и другие надстройки для Excel, служащие для добавления в MS Excel различных специальных возможностей.

Чтобы познакомиться с мощным инструментом Excel Поиск решения, рассмотрим и решим с вами задачу.

Необходимо найти оптимальные объемы выпуска трех видов продукции для получения максимальной прибыли от их продажи.

При решении данной задачи должны быть учтены следующие ограничения:

• общий объем производства – всего 300 изделий;
• должно быть произведено не менее 50 изделий А;
• должно быть произведено не менее 40 изделий В;
• должно быть произведено не более 40 изделий С.

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

2. Запустить задачу поиска решений. Для этого: выполнить команду в Excel 2003 Сервис | Поиск решений … (В Excel 2007 и 2010 необходимо зайти в раздел Данные | Поиск решения)

[expert_bq id=»1570″]Когда я писала эту статью, мне очень хотелось придумать задачу на Поиск решения, которая была бы полезна в реальной практике. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Еще одна дополнительная единица продукта, которую мы могли бы произвести, при наличии еще одной единицы ограниченного ресурса, представляет собой условную единицу. Это не одна единица E или R, а условная единица, в состав которой входят и Е, и R в той пропорции, в которой определен оптимальный план

Поиск решения в excel – инструкция

• Максимальное время – количество секунд, которые пользователь выделяет программе на решение. Оно зависит от сложности задачи.
• Максимальное число интеграций. Это количество ходов, которые делает программа на пути к решению задачи. Если оно увеличивается, то ответ не будет получен.
• Погрешность или точность, чаще всего применяется при решении десятичных дробей (к примеру, до 0,0001).
• Допустимое отклонение. Используется при работе с процентами.
• Неотрицательные значения. Применяется тогда, когда решается функция с двумя правильными ответами (например, +/-X).
• Показ результатов интеграций. Такая настройка указывается в случае, если важен не только результат решений, но и их ход.
• Способ поиска – выбор оптимизационного алгоритма. Обычно применяется «метод Ньютона».

После установки и подключения надстройки в Excel 2007/2010 на вкладке «Данные» появляется группа «Анализ» с новой командой «Поиск Решения». В Excel 2003 — появляется новый пункт меню «Сервис» с одноименным названием. Поиск решения — стандартная надстройка, существуют также и другие надстройки для Excel, служащие для добавления в MS Excel различных специальных возможностей.

Где находится «Поиск решения» в Excel и как им пользоваться

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

Обзор и возможности функции

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

Включение возможности

Функция поиска решений является надстройкой Excel. Для версий Excel ниже 2010 её потребуется запускать следующим образом:

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

Подготовка таблицы

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

В случае соответствия группы пункту она получает «вес» равный «1». В противном случае – «0». Это потребуется для дальнейших операций.

Теперь таблицу можно считать сведённой и подготовленной. Требуется только завершить работу с функцией и настроить формулы для её эффективного срабатывания. Предположим, что нас интересует результат, при котором разница между затратами групп будет минимизирована или сравняется с нулём (ячейка «Разница»).

Запускаем функцию подбора решений и начинаем задавать ей необходимые переменные и значения:

Получаем следующее заполнение:

Получаем решение в течении нескольких секунд. Для сравнения, по самым оптимистичным прикидкам, человек потратил бы на аналогичное действие около 5 минут.

Конкретные примеры использования

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

Изготовление йогурта

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

В результате вычислений (с учётом дробного остатка, поскольку условие работы только с целыми числами добавлено не было), получилось, что эффективнее всего производить 1 и 3 йогурты, а второй полностью игнорировать.

Затраты на рекламу

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

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

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

Оптимизация игрового процесса

В условиях имеем: требуется получить максимальную выгоду при лимите времени. Следовательно, программа определяет на каком комплекте сфокусировать внимание. Результат предсказуем: самый дорогой комплект достоин 100% временных затрат.

Установка ограничений

[expert_bq id=»1570″]Задание 2 самостоятельно найти решение СЛУ методом Крамера Формулы Крамера и матричный метод решения систем линейных уравнений не имеют серьезного практического применения, так как связаны с громоздкими выкладками. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Полученная цифра не удовлетворяет условию в 12000 после пятилетнего периода. Теперь необходимо воспользоваться инструментом Поиск решения. При этом изменяемыми параметрами будет процент и первоначальная сумма. Заполняете диалоговое окно построчно.

Учимся программировать

И перед нами стоит задача – назначить каждому товару скидку таким образом, чтобы сумма по всем скидкам составила 4,5 млн. рублей. Она должна отобразиться в отдельной ячейке, которая называется целевой. Ориентируясь на нее мы должны рассчитать остальные значения.

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

Чтобы это сработало, нужно уметь правильно сформулировать условия, это мы и будем учиться делать в настоящей статье.

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

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

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

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

Дополнительные формулы — в модель может быть внесено любое количество дополнительных формул, которые не влияют на целевую функцию и ограничения, а несут справочную информацию по проекту.

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

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

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

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

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

Теперь 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

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

Мы видим, что мастер 2, который работает медленнее всех — получает меньше всех. Значит, если мы не ограничены во времени и отдадим весь заказ ему, то вся работа будет стоить всего 95 000 (950 руб х 100 шкатулок). Но сколько это займет времени? 100/1,5 = 66,66 дней.

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

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

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

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

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

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

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

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

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

После того, как Вы внесли все исходные данные, запускаем «Поиск решения» (вкладка «Данные»). Я покажу на примере Excel 2007 (Excel 2010 немножко отличается, но сам подход аналогичен).

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

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

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

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

У Вас получилось? Если что-то непонятно или есть вопросы, скачайте мой файл с решением и проверьте в нем:

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

Молодцы, кто решил! С принципом работы надстройки Поиск решения мы разобрались, а теперь идем дальше — там еще интереснее!

Приведенную здесь задачу я взяла из курса подготовки к CIMA Кузьмина Михаила Юрьевича, который проходила в 2016 году. Было очень интересно, рекомендую и вам!

THS производит два продукта из различных комбинаций одних и тех же ресурсов. Ниже приведена информация о продуктах:

THS готовит план производства на следующий месяц. Максимально доступные (за месяц) объемы ресурсов приведены в таблице:
Материал А — 5 000 кг
Материал В — 5 400 кг
Работа оборудования — 3 000 часов
Квалифицированный труд — 4 500 часов

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

Маржинальная прибыль от всего объема выпуска будет рассчитана по формуле:
35x + 66y
Наша цель: найти такой объем выпуска, который даст максимальное значение

Ограничения:
Материал А: 3х+2уМатериал В: 4x+3уРабота оборудования: 2х+3уКвалифицированный труд: 2х+5уОбъем спроса: yНеотрицательность: х>=0, y>=0

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

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

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

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

Максимальное значение прибыли 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$

Итак, мы можем вести переговоры о приобретении дополнительных ресурсов с надбавкой к текущей стоимости в пределах теневой цены и нам это будет выгодно.

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

А у Вас есть идея,
как применить Поиск решения на практике?
Пишите мне на почту или в комментариях.
Если идея будет рабочая, я составлю задачу и опубликую ее здесь!

За идею задачи про трансфертные цены, которую я привожу в этом разделе, благодарю очень талантливого экономиста Алексея Д.!

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

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

Трансфертная цена – это цена, по которой товары или услуги передаются между подразделениями одной и той же компании.

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

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

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

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

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

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

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

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

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

Теперь подготовим поля для переменных. Это 2 ячейки: цена с Кроны на Стрим и цена с Кроны на Маркет. Пока они остаются пустыми, Поиск решений сам их заполнит.

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

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

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

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

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

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

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

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

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

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

И последнее ограничение: минимальная сумма прибыли после уплаты налогов. Здесь значение будем задавать через формулу: выручка, умноженная на 1%, потому что на Кроне в зависимости от изменения трансфертных цен выручка будет изменяться.

[expert_bq id=»1570″]Ограничения Материал А 3х 2у Материал В 4x 3у Работа оборудования 2х 3у Квалифицированный труд 2х 5у Объем спроса y Неотрицательность х 0, y 0. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] После того, как Вы внесли все исходные данные, запускаем «Поиск решения» (вкладка «Данные»). Я покажу на примере Excel 2007 (Excel 2010 немножко отличается, но сам подход аналогичен).

Поиск решения» в Excel 2007, 2010, 2013, 2016 (и других) – где находится функция, как включить подбор решений, возможности надстройки

И последнее ограничение: минимальная сумма прибыли после уплаты налогов. Здесь значение будем задавать через формулу: выручка, умноженная на 1%, потому что на Кроне в зависимости от изменения трансфертных цен выручка будет изменяться.