Штриховые обозначения

Представлена типичная модель сбыта, отражающая увеличение числа продаж от заданной величины (обусловленной, например, затратами на персонал) при увеличении затрат на рекламу и уменьшении прибыли. Так, первые 5000 р. затраченные на рекламу в первом квартале приводит к увеличению числа продаж на 1092 единицы, а следующие 5000 р. - только на 775 единиц.

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

Нахождение значения, при котором заданная величина максимальна

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

Для этого в меню Сервис выполните команду Поиск решения. Задайте B15 в качестве результирующей ячейки (прибыль за первый квартал) на листе Excel. Выберите поиск наибольшего значения и укажите в качестве изменяемой ячейки B11 (расходы на рекламу в первом квартале). Запустите процесс поиска решения. В процессе решения задачи в строке состояния будут отображаться сообщения. Через некоторое время появится сообщение о том, что решение найдено. В соответствии с найденным решением, затратив 17093 р. на рекламу в первом квартале можно получить наибольшую прибыль, которая составит 15093 р.

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

Нахождение значения за счет изменения нескольких величин

Имеется возможность поиска наибольшего или наименьшего значения для заданной величины, одновременно изменяя несколько других величин. Например, можно определить бюджет на рекламу в каждом квартале, соответствующий наибольшей годовой прибыли. Поскольку задаваемая в 3 строке сезонная поправка входит в расчет числа продаж (строка 5) в качестве сомножителя, целесообразно увеличить затраты на рекламу в 4 квартале, когда прибыль от продаж наибольшая и уменьшить, соответственно, в 3 квартале. Поиск решения позволит найти наилучшее распределение затрат на рекламу по кварталам.

В меню Сервис выполните команду Поиск решения. Задайте F15 (общая прибыль за год) в качестве результирующей ячейки. Выберите поиск наибольшего значения и задайте в качестве изменяемых ячеек B11:E11 (расходы на рекламу в каждом квартале). Запустите процесс поиска решения. После ознакомления с результатами выберите параметр Восстановить исходные значения и нажмите кнопку OK для восстановления исходных значений ячеек.

Рассмотренная задача является нелинейной задачей оптимизации средней степени сложности; то есть поиск значения уравнения с четырьмя неизвестными в ячейках с B11 по E11. (Нелинейность уравнения связана с операцией возведения в степень в формуле строки 5). Результат этой оптимизации без ограничений говорит о возможности увеличения годовой прибыли до 79706 р. при годовых затратах на рекламу 89706 р.

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

Добавление ограничения

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

Предположим, расходы на рекламу за четыре квартала не должны превышать 40000 р. Добавим в рассмотренную задачу соответствующее ограничение. В меню Сервис выполните команду Поиск решения и нажмите кнопку Добавить. Задайте ссылку на ячейку ограничения F11 (общие расходы на рекламу) листа Excel. Содержимое этой ячейки не должно превышать 40000 р. Установленное по умолчанию отношение <= (меньше или равно) требуется. В поле, расположенном справа, введите число 40000. Нажмите кнопку OK, а затем - Выполнить. После ознакомления с результатами восстановите первоначальные значения ячеек.

В соответствии с найденным решением на рекламу будет выделено 5117р. в 3 квартале и 15263р. - в 4. Прибыль увеличится с 69662р. до 71447р. без увеличения бюджета на рекламу.

Изменение ограничения

Поиск решения позволяет экспериментировать с различными параметрами задачи, для определения наилучшего варианта решения. Например, изменив ограничения, можно оценить изменение результата. Попробуйте на листе примера изменить ограничение на рекламный бюджет с 40000р. до 50000р. и посмотреть, как изменится при этом общая прибыль. Для этого в меню Сервис выберите пункт Поиск решения. В списке Ограничения: уже задано ограничение $F$11<= 40000. Нажмите кнопку Изменить. Измените в поле значения 40000 на 50000. Нажмите кнопку OK, а затем - Выполнить. Выберите параметр Сохранить найденное решение в диалоговом окне Результаты поиска решения и нажмите кнопку OK, чтобы сохранить результаты.

Найденное решение соответствует прибыли 74817р., что на 3370 р. больше прежнего значения 71447 р. Для большинства предприятий увеличение капиталовложений на 10000 р., приносящее 3370 р. (т.е. 33,7 % возврат вложений) является оправданным. Прибыль при таком решении будет на 4889 р. меньше, по сравнению с задачей без ограничений, однако при этом требуется и на 39706 р. капиталовложений меньше.

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

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

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

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

Для загрузки сохраненных параметров нажмите кнопку Загрузить модель... в диалоговом окне Параметры поиска решения, после чего задайте ячейки H15:H18 в поле области модели или выделите эти ячейки на листе Excel. Нажмите кнопку OK. Подтвердите сброс текущих значений параметров задачи и замену их на новые.

Пример 1: Структура производства с уменьшением нормы прибыли.

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

Параметры задачи

В формулу прибыли на изделие в ячейках D17:F17 входит коэффициент ^H15, учитывающий уменьшение прибыли с ростом объема. В H15 содержится 0,9, что делает задачу нелинейной. Измените H15 на 1,0 (если прибыль не зависит от объема производства) и повторно запустите процесс поиска решения, найденное теперь оптимальное решение будет другим. Внесенное изменение делает задачу линейной.

Пример 2: Задача перевозки грузов.

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

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

Параметры задачи

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

Пример 3: График занятости персонала Парка отдыха.

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

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

Параметры задачи

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

Пример 4: Управление оборотным капиталом.

Требуется с наибольшей доходностью разместить дополнительные средства в 1-, 2- и 6-месячных депозитах, учитывая собственные потребности в средствах (и гарантийный резерв).

Одной из задач сотрудника или управляющего финансового отдела является управление средствами и краткосрочные вложения с максимальной прибылью при сохранении достаточного резерва для покрытия расходов. Более доходными могут оказаться долгосрочные депозиты, однако краткосрочные депозиты предоставляют более гибкие возможности управления финансовыми средствами. В данной модели конечная сумма рассчитывается, исходя из начальной (из прошлого месяца), плюс погашаемые депозиты, минус новые депозиты и с учетом ежемесячных потребностей самого предприятия. Необходимо определить девять сумм: ежемесячные суммы для 1-месячных депозитов; суммы депозитов 1 и 4 месяца для квартальных депозитов; сумму шестимесячного депозита в 1 месяце.

Параметры задачи

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

Предположим, что необходимо обеспечить достаточные средства для оплаты оборудования в 5 месяце. Это накладывает ограничение на средний срок действия депозита в 1 месяце, который не должен превышать четырех месяцев. Формула в ячейке B20 вычисляет общие суммы вложений в 1 месяце (B14, B15 и B16), умноженные на сроки действия (1, 3 и 6 месяцев) и вычитает общую сумму депозита, умноженную на 4. Если получено отрицательное число, средний срок погашения не превышает 4 месяцев.

Чтобы добавить это ограничение, восстановите исходные значения и выберите пункт Поиск решения в меню Сервис. Выберите Добавить. ЗадайтеB20в поле Ссылка на ячейку, 0 - в поле Ограничение и нажмите кнопку OK. Чтобы найти решение задачи, нажмите кнопку Выполнить.

В соответствии с заданным ограничением средства помещаются в основном на трехмесячные депозиты. Средний срок возврата депозита составляет 4 месяца, после чего средства снова помещаются на трехмесячный депозит. Если требуются свободные средства, они могут не помещаться на депозит. Возвращаемой в 4 месяце суммы 56896 р. достаточно для оплаты оборудования в 5 месяце. Данная возможность приобретается потерей около 460 р. дохода по процентам.

Пример 5: Портфель ценных бумаг.

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

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

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

Поиск решения позволяет рассмотреть различные варианты размещения средств для получения наибольшего оборота при заданном уровне риска или минимального риска при заданном уровне оборота. При равном 20 процентном вложении оборот составит 16,4, а изменение - 7,1 процента.

Параметры задачи

В ячейках D21:D29 содержатся данные для задачи минимизации риска для заданного оборота 16,4 процента. Чтобы учесть эти данные в поиске решения, выберите пункт Поиск решения в меню Сервис, выполните команду Параметры, нажмите кнопку Загрузить модель.., выделите на листе Excel ячейки D21:D29 и нажимайте кнопку OK, пока не отобразится диалоговое окно Поиск решения. Нажмите кнопку Выполнить. В результате будет найдено распределение средств, отличающееся от равномерного.

Можно добиться более высокого оборота (17,1 процента) при том же риске или уменьшить риск без снижения оборота. Оба распределения будут соответствовать эффективному портфелю. В ячейках A21:A29 описана исходная модель. Чтобы повторно загрузить задачу, выберите пункт Поиск решения в меню Сервис, выполните команду Параметры, нажмите кнопку Загрузить модель.., выделите ячейки A21:A29 и нажмите кнопку OK. Подтвердите сброс текущих значений параметров на параметры загружаемой модели.

Пример 6: Планирование штатного расписания

Авиакомпании требуется определить, сколько стюардесс следует принять на работу в течение шести месяцев при условии, что любая из них должна пройти предварительную подготовку. Потребности в количестве человеко-часов летного времени для стюардесс известны: в январе - 8000, в феврале - 9000, в марте - 8000, в апреле - 10000, в мае - 90000 и в июне - 12000. Подготовка стюардессы занимает один месяц. В течение этого месяца каждая стюардесса проходит 100-часовую полетную практику (освобождается 100 человеко-часов, отведенного для уже обученных стюардесс). Каждая обученная стюардесса может иметь налет до 150 часов. В начале января авиакомпания уже имеет 60 опытных стюардесс. Установлено, что 10% обучаемых стюардесс по окончании обучения увольняются по каким-то обстоятельствам. Опытная стюардесса обходится компании в 800$, а обучаемая - в 400$ в месяц. Необходимо спланировать штат авиакомпании таким образом, чтобы минимизировать издержки за отчетные шесть месяцев.

Для решения задачи необходимо разработать математическую модель, воплотить в электронную таблицу и проанализировать ситуацию в развернутом и наглядном виде. Рекомендуется использовать инструмент Поиск решения из меню Сервис, при этом наложить соответствующие ограничения $B$3:$B$8=целое, $B$3:$B$8>=0, $Е$3:$Е$8>=$C$3:$C$8. Вид таблицы после поиска оптимального решения, приведен ниже. Данные в затененном диапазоне соответствуют минимальным издержкам и говорят о том, что для этого авиакомпания в последний месяц отчетного периода должна принять на обучение 17 новых стюардесс, а в апреле пополнить штат 10-ю единицами.

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

Вид таблицы в режиме показа формул

Пример 7: Задача о назначениях.

Пусть имеется n видов работ и столько же претендентов на них. Работники имеют различный уровень навыков по каждому из видов работ, который оценивается по пятибалльной шкале. Требуется оптимально распределить кадры по видам работ для получения максимального суммарного показателя качества. Число возможных вариантов n! Без применения компьютера поиск оптимального варианта для случая n=10 занял бы годы, а так - секунды. Но во избежание излишней громоздкости решения мы в качестве примера возьмем n=3.

Параметры задачи

В ячейке F6 содержится формула =СУМПРОИЗВ(B3:D5;B8:D10), а в ячейках E3:E5 формулы: =СУММ(B3:D3)...=СУММ(B5:D5). В ячейках B6:D6 - формулы: =СУММ(B3:B5)...=СУММ(D3:D5). Задача решается аналогично предыдущим, с привлечением инструмента Поиск решения электронных таблиц Excel.