Приемы решения задач

Фирма «Фасад» производит двери для продажи местным строительным компаниям. Репутация фирмы позволяет ей продавать всю производимую продукцию. На фирме работает 10 рабочих в одну смену (8 рабочих часов), 5 дней в неделю, что дает 400 часов в неделю.

Оптимально ли распределение рабочего времени между двумя технологическими процессами (производство и конечная обработка)? Как изменится прибыль, если распределить рабочее время между этими процессами оптимально? c.

На предстоящей неделе «Фасад» должен выполнить контракт на поставку 280 стандартных, 120 полированных и 100 резных дверей. Для выполнения заказа «Фасад» может закупить некоторое количество полуфабрикатов дверей у внешнего поставщика. Эти полуфабрикаты «Фасад» может использовать только для производства стандартных и полированных, но не резных дверей. При этом изготовление стандартной двери требует лишь 6 мин процесса обработки, а полированной - 30 мин обработки (процесс собственно производства для этих полуфабрикатов не требуется). Полученная таким образом стандартная дверь приносит $15 прибыли, а полированная - $50. Предполагая, что по-прежнему 250 часов в неделю отведено под производство и 150 под обработку, определите сколько и каких дверей «Фасад» должен произвести самостоятельно, и сколько полуфабрикатов закупить для изготовления стандартных и полированных дверей?

ё.

Решение задачи. a.

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

Эти соображения приводят нас к выводу, что в качестве переменных задачи следует выбрать количества дверей каждого типа, которые следует произвести. Значит в задаче будет 3 переменных: Xi - количество стандартных дверей, Х2 - количество полированных и Х3 - количество резных дверей. При этом целевая функция запишется, очевидно, следующим образом:

P = Xi*45 + X2*90 + Хз*120 ($).

Лучше всего организовать данные на листе MS Excel следующим образом (Рис. 1): A B C D E F 1 Фи )ма «Фасад» 2 Время на производство (мин) Время на обработку (мин) Прибыль,

$ Переменные 3 Стандартные 30 15 45 0 X1 4 Полированные 30 30 90 0 X2 5 Резные 60 30 120 0 X3 6 Целевая функция 7 =СУММПРОИЗВ

(E3:E5;D3:D5) 8 1 Рис. 1

Удобно выделить ячейки, в которых будут располагаться переменные цветом, (в данном случае серым), т. к. начальные значения переменных неизвестны, а ссылаться на переменные при вычислениях необходимо. Целевая функция задана с помощью стандартной функции MS Excel =СУММПРОИЗВ( ) (или SUMPRODUCT() в английской версии), которая и вычисляет приведенное выше выражение для P.

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

Итак, следует подсчитать, сколько времени на каждой стадии потребуется для реализации произвольного плана производства дверей. Для стадии производства это время будет равно ti=Xi*30+X2*30+X3*60 (мин), а для стадии обработки

t2=X1*15+X2*30+Xз*30 (мин),

По условию

^<=250*60 (мин), а ^<=150*60 (мин).

Добавим эти формулы на лист с данными задачи (Рис. 2): A B C D E F 1 Фирма «Фасад» 2 Время на производство (мин) Время на обработку (мин) Прибыль,

$ Переменные 3 Стандартные 30 15 45 0 X1 4 Полированные 30 30 90 0 X2 5 Резные 60 30 120 0 X3 6 Целевая функция 7 =СУММПРОИЗВ(

$E$3:$E$5;B3:B5) =СУММПРОИЗВ(

$E$3:$E$5;C3:C5) =СУММПРОИЗВ

(E3:E5;D3:D5) 8 Ограничения =250*60 =400*60-B8 Рис. 2

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

В строке меню находим пункт Сервис (Tools), а внутри выпадающего меню пункт Поиск решения (в английской версии программы Solver).

Вызов надстройки «Поиск решения» приводит к появлению следующего диалогового окна (Рис. 3):

Рис. 3

В нем и следует задать параметры поиска.

В окошке Установить целевую ячейку указываем ячейку, содержащую целевую функцию (нашем примере, как видно из Рис. 2, это ячейка E7). Переключатель оставляем в позиции Равной максимальному значению. В окошке Изменяя ячейки нужно указать ячейки, содержащие переменные решения - в нашем случае это Е3:Е5. Чтобы указать несколько ячеек, просто выделяем диапазон, как обычно это делается в Excel (в случае разрозненных ячеек удерживая клавишу Ctrl на клавиатуре).

Для того, чтобы добавить что-либо в окно Ограничения, следует нажать кнопку Добавить и в выпадающем окне (Рис.

?1х

Добавление огран... Ссылка на ячейку: |$В$7:$С$7

Ограничение:

Т | ||В|8:$С$8

[

<=

Отмена

ОК

Добавить | Справка | Рис. 4

В данном случае записано, что число в ячейке В7 меньше или равно числа в ячейке В8, и число в ячейке С7 меньше или равно числа в ячейке С8.

Результат всех этих действий показан на рисунке (Рис. 5).

Рис. 5

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

Параметры поиска ре...

Максимальное время: Предельное число итераций: Относительная погрешность: Допустимое отклонение: Сходимость: |ЕШ секунд ОК |юо Отмена |0.,000001 Загрузить модель,,, |5 % Сохранить модель... 10,0001 Справка 1^ Линейная модель Г" Автоматическое масштабирование

Неотрицательные значения Г" Показывать результаты итераций Оценки Разности Метод поиска (* линейная (* прямые (• Ньютона С квадратичная С центральные С сопряженных [радиентов Рис. 6

Больше никаких изменений здесь делать не нужно. Нажав ОК возвращаемся в панель Поиск решения.

Теперь можно нажимать кнопку Выполнить, после чего и будет найдено решение, о чем и сообщит панель Результаты поиска решения (Рис. 7).

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

(*

Сохранить найденное решение

Восстановить исходные значения

Сохранить сценарий,

ОК

Отмена

Тип отчета Результаты d Устойчивость Пределы zi Справка

Рис. 7

Нажав ОК Вы сохраните найденное решение на листе MS Excel, содержащем условия задачи. A B C D E F 1 Фи )ма «Фасад» 2 Время на производство (мин) Время на обработку (мин) Прибыль,

$ Переменные 3 Стандартные 30 15 45 0 X1 4 Полированные 30 30 90 100 X2 5 Резные 60 30 120 200 X3 6 Целевая функция 7 15000 9000 33000 8 Ограничения 15000 9000 Рис.

Проверьте, что получился следующий результат (Рис. 8).

В данном случае оказывается, что максимально возможная прибыль равна 33000 $ и получена она будет, если производить за неделю 100 полированных дверей и 200 резных. Это и есть оптимальный план производства для базовой задачи (пункт а). b.

В первой части задачи мы полагали, что суммарное рабочее время по каким-то причинам (не упоминаемым в условии задачи) жестко разбито на 250 часов производства и 150 часов обработки. Возможно, что это связано со специализацией рабочих.

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

Сначала взглянем на отчет об устойчивости. Чтобы получить его для предыдущего решения задачи, нужно в итоговом окне Результаты поиска решения (Рис. 7), прежде чем нажать клавишу ОК, отметить пункт Тип отчета - Устойчивость. При этом к книге MS Excel добавится лист Отчет по устойчивости 1 (Рис. 9). Подробнее об анализе устойчивости задачи линейной Изменяемые ячейки Результ.

значение Нормир.

стоимость Целевой

Коэффициент Допустимое

Увеличение Допустимое

Уменьшение Ячейка Имя $E$3 Стандартные

Переменные 0 -15 45 15 1E+30 $E$4 Полированные

Переменные 100 0 90 30 30 $E$5 Резные

Переменные 200 0 120 60 30 Ограничения Результ. Теневая Ограничение Допустимое Допустимое Ячейка Имя значение Цена Правая часть Увеличение Уменьшение $B$7 Время на

производство

(мин) 15000 1 15000 3000 6000 $C$7 Время на обработку (мин) 9000 2 9000 6000 1500 Рис. 9

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

Чтобы модифицировать задачу в соответствии с изменившимися условиями, достаточно отказаться от ограничения по рабочему времени каждой из стадий и потребовать, чтобы суммарное рабочее время не превышало = 400*60 (мин).

Оставим действующим решение задачи (а), и для модифицированной задачи создадим новый лист.

Для изменения условий добавим в ячейки Б7 и Б8 формулы:

=Б7+Б8 и =400*60,

соответственно. После этого нужно немного модифицировать задание надстройке «Поиск решения». Вызвав надстройку, удалим из ограничений условие $Б$7:$С$7 <= $Б$8:$С$8, и добавим вместо него условие Б7 <= Б8. Получим следующее решение (Рис. 10) А В С Ь Е р 1 Фи )ма «Фасад» 2 Время на производство (мин) Время на обработку (мин) Прибыль,

$ Переменные 3 Стандартные 30 15 45 0 Х1 4 Полированные 30 30 90 400 Х2 5 Резные 60 30 120 0 Х3 6 Целевая функция 7 12000 12000 24000 36000 8 Ограничения 15000 9000 24000 Рис. 10

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

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

Поэтому имеет смысл посмотреть, что меняется, если потребовать выпускать все двери. Конечно, здесь нужно задать некоторое конкретное число, которое мы вынуждены «взять с потолка». Положим, что следует выпускать не менее 50 штук дверей каждого типа. Введем в ячейки 03:05 число 50 и добавим в надстройку «Поиск решения» ограничение Е3:Е5 <= 03:05. Получим новое решение задачи (снова лучше создать сначала копию листа) (Рис. 11 а).

Введенное ограничение, как любое новое ограничение задачи, уменьшает итоговую прибыль. Тем не менее, она оказывается выше, чем прибыль в базовом решении (а). Кроме того, ведь в базовом решении тоже не предполагалась к выпуску стандартная дверь. Если и в базовом решении потребовать выпускать не менее 50 дверей каждого типа, то общая прибыль снизится от 33000$ до 32250$ (Рис. 11 б).

Конечно, только что проведенное исследование задачи не требуется по условию, но зачастую такой анализ («что будет если...») очень интересен и полезен для принятия разумного управленческого решения при использовании той или иной математической модели. 1

Переменные 1

Переменные 50 X1 50 50 X1 50 287.5 X2 50 100 X2 50 i 50 X3 50 175 X3 50 1 Целевая функция Целевая функция 34125 а) 32250 б) 1 Рис. 11 c.

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

Организация данных на листе MS Excel в этом случае представлена на Рис.

12. Фирма «Фасад» Время на производ ство Время на обработку (мин) Прибыль, $ Переменные Всего, шт. Заказ Стандартные 30 15 45 0 X1 280 280 Полированные 30 30 90 120 X2 120 120 Резные 60 30 120 124 X3 124 100 Стандартные П 0 6 15 280 X4 Полированные П 0 30 50 0 X5 Полное время Целевая функция 11040 9000 20040 29880 Ограничения 15000 9000 24000 Рис. 12

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

Часть d. Для решения этой задачи нужно изменить только одно условие - так же как мы делали при анализе части Ь задачи, ограничим только суммарное время двух стадий. Результат представлен на Рис. 13. Фирма «Фасад» Время на производство (мин) Время на обработку (мин) я ?

д §

Пб Переменные Всего Заказ Стандартные 30 15 45 0 Х1 1900 280 Полированные 30 30 90 0 Х2 120 120 Резные 60 30 120 100 Х3 100 100 Стандартные П 6 15 1900 Х4 Полированные П 30 50 120 Х5 Полное время Целевая функция 6 000 18 000 24 000 46 500 Ограничения 24 000 Рис. 13

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

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

Если продать 1900 стандартных дверей невозможно (а возможно, допустим, 600), то, при добавлении соответствующего ограничения, возрастет производство дверей других типов. А сколько их можно продавать за неделю? -

А нельзя ли увеличить сбыт, сбросив отпускные цены (и уменьшив тем самым прибыльность)? Принесет ли это дополнительные деньги?

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

1.П-2. Компания “Черные каски”

Горнопромышленная компания “Черные каски” собирается работать в некоторой области в течение следующих пяти лет. У нее имеется 4 шахты, для каждой из которых есть технический верхний предел на количество руды, которая может быть выдана «на гора» за год. Эти верхние пределы составляют: шахта Койот - 2 млн. тонн, шахта Мокрая - 2.5 млн. тонн, шахта Елизавета - 1.3 млн. тонн и шахта Ореховый лог - 3 млн. тонн.

Стоимость извлечения руды на разных шахтах различная, вследствие отличающихся глубины и геологических условий. Эти стоимости составляют (включая последующую обработку): шахта Койот - 6 $/тонна, шахта Мокрая - 5.5

$/тонна, шахта Елизавета - 7 $/тонна и шахта Ореховый лог - 5 $/тонна.

При этом руда из различных шахт имеет и разное содержание извлекаемого компонента. Для упомянутых выше шахт содержание извлекаемого компонента равно: 10%, 7%, 15% и 5% соответственно. Каждая руда перерабатывается по одному и тому же технологическому процессу, а затем смешивается, чтобы получить более-менее однородную руду с заданным и фиксированным содержанием извлекаемого компонента, так как технологический процесс на металлургическом предприятии подстроен под определенное содержание соединений металла в руде.

Так как руды с течением времени становятся беднее, металлургическое предприятие, на которое компания поставляет руду, собирается провести постепенный переход на обработку более бедных руд. Если в первый год предприятие ожидает 5 млн. тонн руды с содержанием извлекаемого компонента 9%, то во второй и третий годы - 5.63 млн. тонн руды с содержанием 8%, а в четвертый и пятый годы - 6.43 млн. тонн 7%-ной руды.

Соответственно понизится и стоимость руды. Если в первый год руда покупается по $10 за тонну, то 8%-ная руда будет стоить $8.9 за тонну, а 7%-ная - $7.8 за тонну.

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

Представьте, что владелец горнорудной компании получил предложение о продаже. По оценке экспертов покупатель предлагает цену, превышающую стоимость имущества компании на $70 млн. Однако владелец считает, что за пять лет он заработает большую сумму. Стоит ли в действительности продавать компанию? При оценке стоимости компании примите ставку дисконтирования равной 10% в год.

Решение задачи.

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

При тех условиях, которые описаны в задаче, единственное что мы можем варьировать, это количество руды, добываемой на каждой из шахт. Причем из-за изменения условий размер добычи может меняться из года в год. Следовательно, нам необходимо подобрать размер добычи для 4 шахт в каждом году, на пять следующих лет. Таким образом, в задаче должно быть 4*5=20 переменных.

Если у нас будет информация о том, сколько руды добывается на каждой из шахт, мы сможем рассчитать издержки по добыче. Зная цену, по которой металлургический комбинат будет принимать руду в последующие пять лет, и планируемый объем закупок, мы сможем определить полный доход компании за пять лет - целевую функцию задачи. А B C D E F G 1 шахта предел

выработк

и содержани е ИК себест.

руды 2 Койот 2 10% 6 3 Мокрая 2.5 7% 5.5 4 Елизавета 1.3 15% 7 5 Ореховый

лог 3 5% 5 6 7 шахта 1 год 2 год 3 год 4 год 5 год 8 Койот 9 Мокрая 10 Елизавета 11 Ореховый л. 12 задан. % 9% 8% 8% 7% 7% 13 1 2 3 4 5 14 средний % =СУММПРОИЗВ(B8:B11;$D$2:$D$5)/B18 ? 15 кол-во руды ^УММ^ВП) - ? Млн. $ 16 доход =B15*B20-СУММПРОИЗВ(B8:B11;$E$2:$E$5)

? =СУММ(Б16Л

6) 17 ... с

дисконтом =B16/$A$18AB13 ^ =СУММ(Б17Л

7) 18 1.1 5.00 5.63 5.63 6.43 6.43 19 20 цена руды 10.0 8.9 8.9 7.8 7.8 Рис. 14

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

Один из вариантов организации данных представлен на Рис. 14.

В ячейках B8:F11 приготовлено место для переменных задачи - количества руды, добываемой в разные годы на каждой шахте. Для удобства вычислений в ячейках сверху для этих шахт в том же порядке перечислены данные задачи: в ячейках B2:B5 - предельная годовая выработка руды на шахтах в млн. тонн, в ячейках D2:D5 - содержание извлекаемого компонента в руде в % от массы, а в ячейках E2:E5 - себестоимость извлечения 1 тонны руды в долларах.

В строке B12:F12 записаны заданные проценты содержания извлекаемого компонента в сырье, поставляемом металлургическому комбинату. В строке B18:F18 - плановый объем закупок сырья комбинатом в млн. тонн, а в строке B20:F20 - цена покупки тонны сырья.

Так как нужный процент извлекаемого компонента в сырье для металлургов добывающая компания получает путем смешивания различных руд, то вся добытая руда в конечном итоге будет продана комбинату по закупочной цене. Общее количество добытой руды мы подсчитываем в строке B15:F15 просто складывая добычу на отдельных шахтах с помощью функции Excel вида ^УММ^ББ^П). Для этого вводим эту формулу в ячейку B15 и протягиваем вправо до ячейки F15. В задании для поиска решения нужно будет потребовать, чтобы значения ячеек B15:F15 в точности равнялись плановой продаже в эти же годы B18:F18.

Произведение добычи за год на цену продажи даст нам доход за любой год. Однако для получения чистой прибыли нужно из этой суммы вычесть собственные расходы (будем полагать, что все прочие издержки и налоги расписаны на себестоимость). Величина расходов может быть найдена перемножением размеров добычи на издержки за тонну. Для расчета опять удобно использовать функцию =СУММПРОИЗВ( ). Издержки в первый год в этом случае будут вычисляться по формуле =СУММПРОИЗВ(B8:B11;$E$2:$E$5). Знаки $ здесь добавлены, чтобы формулу удобно было протягивать, распространяя вычисления на все годы добычи.

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

=B15*B20-СУММПРОИЗВ(B8:B11;$E$2:$E$5). Далее формула протянута вправо до ячейки F16. Соответственно, формула ^УММ^^Е^), записанная в ячейке G16, дает полную прибыль за пять лет.

Однако, знать полную прибыль - недостаточно. Ведь нам нужно знать, сколько стоит эта будущая прибыль сегодня. Для этого нужно дисконтировать все годовые доходы к нулевому году, т.е. к текущему моменту. Коэффициент дисконта равен 1.1 (10% в год), значит прибыль первого года нужно поделить на 1.1. Прибыль второго года - на 1.12 и т.д. Эти расчеты выполнены в строке B17:F17 (в Excel символ “Л” обозначает возведение в степень, например 23 = 2Л3). И, как итог, в ячейке G17 эти дисконтированные прибыли просуммированы. Таким образом целевую функцию мы задали.

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

Подумаем теперь об ограничениях. Об одном ограничении - суммарной добыче за каждый год - мы уже позаботились (значения ячеек B15:F15 строго равняются плановой продаже в эти же годы B18:F18).

Второе очевидное ограничение - на предельную выработку для каждой шахты - задать очень просто, так как все необходимые данные для сравнения у нас уже есть. Правда придется задать в Поиске решения не одно, а пять ограничений, для каждого года отдельно. Для первого года ограничение будет выглядеть следующим образом: B8:B11 <= B2:B5. Для второго C8:C11 <= B2:B5 и т. д.

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

Итоговый процент =

ДШ, *Р1 + ДШ 2 *Р2 + ДШ 3 *Р3 + ДШ 4 *Р4 + ДШ 5 *Р5 Общая годовая добыча

Где ДШ; - размеры годовой добычи для каждой шахты, а Р; - процентное содержание извлекаемого компонента для руд каждой из шахт. На Рис. 14 для первого года эта формула записана так:

=СУММПРОИЗВ(B8:B11;$D$2:$D$5)/B18. Протягиванием получим реальный процент содержания для каждого года. Для Поиска решения ограничение на содержание извлекаемого компонента в сырье нужно записать как строгое равенство: Б14:Б14 = Б12:Б12.

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

Если вы не допустили ошибок при вводе формул, то после запуска надстройки Поиск решения на выполнение получите следующее решение (Рис.

15): . . шахта 1 год 2 год 3 год 4 год 5 год Койот 2.00 2.00 2.00 2.00 2.00 Мокрая 0.00 0.00 0.00 1.43 1.43 Елизавета 1.00 0.69 0.69 0.00 0.00 Ореховый

лог 2.00 2.94 2.94 3.00 3.00 задан. % 9% 8% 8% 7% 7% 1 2 3 4 5 средний % 9.0% 8.0% 8.0% 7.0% 7.0% кол-во руды 5.00 5.63 5.63 6.43 6.43 $ млн. доход 21.0 18.5 18.5 15.1 15.1 88.29 ... с

дисконтом 19.09 15.29 13.90 10.34 9.40 68.02 Рис. 15

Общая номинальная прибыль за 5 лет составит $88.29 млн., но эти будущие доходы следует оценить сегодня в сумму $68 млн. Следовательно предложение $70млн. оказывается справедливым и даже выгодным для компании “Черные каски”, если эта сумма будет выплачена немедленно.

1.П-3. Сталепрокатный завод

Сталепрокатный завод производит стальные листы трех различных размеров: 100 дюймов, 80 дюймов и 55 дюймов. Поступил заказ на стальные листы размером 45, 30 и 18 дюймов в количестве 150, 200 и 185 штук соответственно. a.

Каким образом компания должна разрезать стальные листы, чтобы минимизировать отходы? Учтите, что желательно также при раскрое не получать слишком много лишних листов с размерами, заданными данным заказчиком. b.

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

Решение задачи.

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

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

В данной задаче переформулировать условие оказывается несложно.

Из листов каждого из размеров (100, 80 и 55) можно выкроить по нескольку различных наборов заказанных листов. Например из листа размера 55 дюймов можно получить 1 лист размером 45 дюймов (10 дюймов - в обрезки), или 1 лист в 30 дюймов и 1 в 18 дюймов (7 - в обрезки), или 3 листа в 18 дюймов (1 дюйм - в обрезки). Если перебрать все возможные варианты раскроя, их окажется не так уж много. Так как для каждого варианта известно и количество полученных листов и количество обрезков, то выбрав в качестве переменных количество листов раскроенных по каждому из описанных вариантов, можно построить задачу линейной оптимизации. Целевой функцией будет общее количество остатков. Цель - минимизация остатков при условии исполнения заказа.

Пример организации таблицы для расчета всех нужных для решения задачи величин приведен ниже на Рис. 16.

Задание для Поиска решения в данном случае будет выглядеть очень просто: целевая ячейка - Н19, цель - минимум, изменяемые ячейки - 03:017. По смыслу задачи следует потребовать, чтобы переменные были целыми числами (03:017 = целое). Как обычно во вкладке параметры отмечаем, что задача линейная и переменные неотрицательны.

Условие выполнения заказа может быть записано по-разному. Можно потребовать точного выполнения заказа (С19:Е19 = С20:Е20), что, очевидно, соответствует недопустимости получения лишних листов заказанных размеров. Можно использовать более мягкое условие: количество полученных листов не менее заказанного (С19:Е19 >= С20:Е20), что допустимо в случае, когда оставшиеся листы могут быть проданы другому заказчику.

При ответе на вопрос a разумно потребовать точного выполнения заказа. При этом общее количество остатков равно 670 дюймам. Для выполнения заказа придется разрезать 44 листа по 3-ему варианту, 106 листов по 8-му, 47 - по 10-му и 2 листа по 15-му варианту.

Если не требовать точного соответствия результатов раскроя заказу, общее количество остатков значительно уменьшится и составит 350 дюймов. Однако при этом будет получено 550 листов размеров 18 дюймов, что в 3 раза больше, чем было заказано.

Рис. 16

Для того, чтобы получить более разумный план раскроя, можно потребовать дополнительно, чтобы количество полученных листов не превышало заказанное на некоторое предельное число, скажем 10%. Как вы можете убедиться, при этом общее количество обрезков увеличится до 650 дюймов. Что практически совпадает с вариантом точного выполнения заказа. 1.

П-4. На кондитерской фабрике. (Кейс)

Действие 1-е. (Борьба научного подхода и эмпирики.

Сырье Запасы, кг

Маленькая кондитерская фабрика должна закрыться на реконструкцию. Необходимо реализовать оставшиеся запасы сырья, для производства продуктов из ассортимента фабрики, получив максимальную прибыль. Запасы и расход каждого вида сырья для производства единицы продукции каждого вида, а также нормы прибыли для каждого продукта (прибыль на 1 пакет), представлены в таблице.

Продукты, расход сырья, кг Ореховый

звон Райский

вкус Батончик Белка Ромашка Темный

шоколад 1411 0.8 0.5 1 2 1.1 Светлый

шоколад 149 0.2 0.1 0.1 0.1 0.2 Сахар 815.5 0.3 0.4 0.6 1.3 0.05 Карамель 466 0.2 0.3 0.3 0.7 0.5 Орехи 1080 0.7 0.1 0.9 1.5 0 Прибыль/пакет у.е. 1 0.7 1.1 2 0.6 В разговоре с владельцем фабрики мастер, используя свой 20-летний опыт, предлагает «на глазок» выпустить по 200 пакетов каждого продукта, утверждая, что ресурсов «должно хватить», а прибыль получится, очевидно, 1080 у.е.

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

Анализ Действия 1-го.

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

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

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

Организуем данные на листе MS Excel так, как это показано на рисунке (Рис. 17) «На кондитерской фабрике». A B C D E F в 1 На кондитерской фабрике 2 Продукты 3 Сырье Запасы Ореховый

звон Райский вкус Батончик Белка Ромашка 4 Темный шок. 1411 0,8 0,5 1 2 1,1 5 Светлый шок. 149 0,2 0,1 0,1 0,1 0,2 6 Сахар 815,5 0,3 0,4 0,6 1,3 0,05 7 Карамель 466 0,2 0,3 0,3 0,7 0,5 8 Орехи 1080 0,7 0,1 0,9 1,5 0 9 Прибыль 1 0,7 1,1 2 0,6 10 11 12 Ореховый

звон Райский вкус Батончик Белка Ромашка 13 Переменные 454,48 58,78 0,00 503,99 9,13 14 Цель 15 Расход P = =СУММПРОИЗВ(С13:в13;С9:в9) 16 Темный шок. =СУММПРОИЗВ($C$13:$G$13;C4:G4) 17 Светлый шок. =СУММПРОИЗВ($C$13:$G$13;C5:G5) 18 Сахар =СУММПРОИЗВ($C$13:$G$13;C6:G6) 19 Карамель =СУММПРОИЗВ($C$13:$G$13;C7:G7) 20 Орехи =СУММПРОИЗВ($C$13:$G$13;C8:G8) Рис. 17

В ячейку Б16 введена целевая функция, представляющая собой сумму произведений прибылей от продажи одного пакета каждого продукта (строка 9) на произведенное количество каждого продукта (строка 13). В ячейках 013:013 - содержатся переменные

В ячейках Б16:Б20- введены формулы, отражающие расход ресурсов на весь производственный план.

Остается сформировать задачу для надстройки Поиск решения. После того, как мы зададим целевую ячейку, цель (поиск максимума), изменяемые ячейки и отметим во вкладке «Параметры», что задача линейная и переменные неотрицательны, останется только задать ограничение. В данном случае оно только одно (если задавать его для группы ячеек): реальный расход ресурсов, рассчитанный в ячейках Б16:Б20, не должен превышать запасы на складе, записанные в ячейках Б4:Б8.

После команды «Выполнить» получим решение, приведенное на рисунке (Рис. 18). На кондитерской фабрике Продукты Сырье Запасы Ореховый

звон Райский вкус Батончик Белка Ромашка Темный шок. 1411 0,8 0,5 1 2 1,1 Светлый шок. 149 0,2 0,1 0,1 0,1 0,2 Сахар 815,5 0,3 0,4 0,6 1,3 0,05 Карамель 466 0,2 0,3 0,3 0,7 0,5 Орехи 1080 0,7 0,1 0,9 1,5 0 Прибыль 1 0,7 1,1 2 0,6 Ореховый

звон Райский вкус Батончик Белка Ромашка Переменные 454,48 58,78 0,00 503,99 9,13 Цель Расход Р= 1509,09 Темный шок. 1411,00 Светлый шок. 149,00 Сахар 815,50 Карамель 465,89 Орехи 1080,00 Рис. 18

Если аккуратно округлить значения переменных, соблюдая ограничения на ресурсы, получим реальный план производства конфет (Рис. 19). Как видим, общая прибыль составила примерно 1509 долл., т.е. прибавка к исходному плану достигает 429 долл. Ореховый

звон Райский вкус Батончик Белка Ромашка Переменные 454,00 59,00 0,00 504,00 9,00 Цель Расход Р= 1508,70 Рис. 19

В установках надстройки Поиск решения существует возможность потребовать целочисленности переменных решения. Для этого достаточно в левом поле этого окна указать ячейки, содержащие переменные решения, а из предлагаемых ограничений выбрать ограничение «цел». Добавление ограничения Ссылка на ячейку: Ограничение: |$С$ 13:^13 цел - (целое <=

>- ОК | Отмен; Завить | Справка | цел 1 Рис. 20

Вопреки тому, что можно было бы ожидать, получаемое целочисленное решение (производственный план) не совпадает с округленным оптимальным решением, полученным без условия целочисленности (Рис. 21) . Ореховый

звон Райский вкус Батончик Белка Ромашка Переменные 450,00 60,00 10,00 500,00 10,00 Цель Расход Р= 1509,00 Рис. 21

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

звон Райский вкус Батончик Белка Ромашка Переменные 450,00 60,00 10,00 500,00 10,00 Цель Расход Р= 1509,00 Рис. 22

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

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

Действие 2-е. Жаль..., ведь мы все так любим «Батончик»!

После решения задачи об оптимальном плане производства для родной кондитерской фабрики, юноша (сын владельца фабрики) испытал двойственное чувство. С одной стороны, прибыль, соответствующая найденному им производственному плану, почти на 430 у.е. больше, чем по плану мастера, т.е. он заработал более 400 баксов. Это здорово! С другой стороны, почему компьютер отказался от выпуска Батончика (его с раннего детства любимого лакомства)? Юноша был уверен, что «Батончик» - один из лучших продуктов, который выпускает фабрика его отца. Если его не окажется на прилавках, может пострадать имидж фабрики. Ведь не только он сам, но и все соседи в округе обожают эту конфету!

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

старый мастер не только план производства определяет на глазок, но и запасы сырья взвешивает кое-как? А что, если каких-то запасов не хватит для его оптимального плана? Он не доберет прибыли! Может быть тогда более прибыльным станет иной план? Какой?

И еще одна мысль. У него есть в кармане, что-то около 50 баксов. Может пустить их в дело? Докупить у знакомого оптовика какого-нибудь сырья, потихоньку подложить на склад (чтоб мастер не заметил), как будто, так и было. Тогда можно получить дополнительную прибыль (и премию от отца). Только вот какого сырья докупать? И сколько? И на сколько от этого возрастет прибыль? Итак, ответьте на следующие вопросы. a.

Как надо изменить норму прибыли для любимого продукта сына хозяина фабрики (Батончика), чтобы он вошел в оптимальный план (ответьте, не решая задачу, анализируя лишь отчет об устойчивости)? b.

Введите это изменение в данные и решите задачу заново. Как изменился оптимальный план? c.

Какой ресурс является наиболее дефицитным (т.е. максимально влияет на прибыль)?

ё. Можете ли Вы сказать (не решая задачу снова) как изменится прибыль от производства, если количество этого ресурса оценено а) с избытком в 10 весовых единиц; б) с недостатком в 5 единиц?

е. Есть ли другой способ добиться производства «Батончика» (кроме изменения нормы прибыли)?

Анализ Действия 2-го.

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

Изменяемые ячейки Ячейка Имя Результ.

значение Нормир.

стоимость Целевой

Коэффициент Допустимое

Увеличение Допустимое

Уменьшение $С$13 Переменные Ореховый звон 454,48 0,0000 1 0,052299 0,019488 $Э$13 Переменные Райский вкус 58,78 0,0000 0,7 0,043961 0,345734 $Е$13 Переменные Батончик 0,00 -0,0087 1,1 0,008737 1,00Е+30 $Р$13 Переменные Белка 503,99 0,0000 2 0,956405 0,021902 $в$13 Переменные Ромашка 9,13 0,0000 0,6 0,100575 0,039565

Ограничения Ячейка Имя Результ.

значение Теневая

Цена Ограничение Правая часть Допустимое

Увеличение Допустимое

Уменьшение $В$16 Темный шок. Расход 1411,00 0,0454 1411 0,262411 7,952174 $В$17 Светлый шок. Расход 149,00 2,4973 149 1,042254 11,868952 $В$18 Сахар Расход 815,50 1,0115 815,5 0,392226 20,092150 $В$19 Карамель Расход 465,89 0,0000 466 1,00Е+30 0,110834 $В$20 Орехи Расход 1080,00 0,2297 1080 16,043860 0,318052 Рис. 23

Согласно отчету об устойчивости, нормированная стоимость конфеты «Батончик», не вошедшей в оптимальный план составляет 0,00874 у.е. Абсолютная величина этого числа показывает, на сколько нужно увеличить прибыль от производства одного пакетика этих конфет, чтобы «Батончик» вошел в оптимальный план. С точки зрения анализа ситуации, малость этого числа (менее 0,8% от нормы прибыли) свидетельствует о том, что если мы «насильно» заставим Поиск решения запланировать выпуск «Батончика» (введя условие Е13>= 100, например), большого уменьшения прибыли не произойдет.

Давайте проверим это умозаключение и потребуем, чтобы количество произведенных пакетиков «Батончика» было бы не менее 100 (Рис. 24). Ореховый

звон Райский вкус Батончик Белка Ромашка Переменные 411,70 73,40 100,00 462,98 15,11 Цель Расход Р= 1508,11 Рис. 24

Прибыль уменьшилась менее, чем на 1 у.е. Потребуем, чтобы количество произведенных пакетиков «Батончика» было бы не менее 200, 300 .... Во всех этих случаях мы получим другие оптимальные решения, а прибыль будет отличаться от оптимальной (для исходного варианта постановки задачи) не более чем на 1%.

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

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

В этом случае прибыль на единицу этого продукта станет равной 1,11 у.е. Еще раз запустим Поиск решения. Результат представлен на следующем рисунке (Рис. 25). Ореховый

звон Райский вкус Батончик Белка Ромашка Переменные 0,00 217,50 1067,50 65,00 70,00 Цель Расход Р= 1509,17 Рис. 25

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

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

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

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

Однако, в данном случае, неустойчивость решения не создает никаких проблем: ведь прибыль-то в обоих случаях почти одинакова! Попробуйте вернуть прежнее значение прибыли для Батончика (1.1 у.е.) - прибыль уменьшится до 1498,5

у.е. Это менее чем на 1% ниже оптимальной.

Таким образом, в нашем распоряжении оказывается множество альтернативных решений, сильно различающихся по значениям переменных, но очень близких по прибыли. Это - не плохо. Это - очень хорошо!

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

Смириться с отсутствием Батончика в оптимальном плане?

Повысить цену?

Ввести ограничение на минимальное количество пакетиков Батончика?

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

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

звон Райский вкус Батончик Белка Ромашка Переменные 212,86 212,86 212,86 212,86 212,86 Цель Расход Р= 1149,43 Рис. 26

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

Или, например, мы вводили требование выпустить не меньше чем 100, 200, 300 пакетов «Батончика» и результат почти не менялся. А если бы народу захотелось, чтобы было много «Ромашки»? В базовом плане ее всего 9 пакетов. Давайте добавим ограничение, что «Ромашки» должно быть не менее 300 пакетов (Рис. 27)! Ореховый

звон Райский вкус Батончик Белка Ромашка Переменные 0,00 0,00 767,50 122,50 300,00 Цель Расход Р= 1269,25 Рис. 27

Этот результат в комментариях не нуждается.

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

Вернемся к полученному нами ранее отчету об устойчивости (Рис. 23). Из нижней таблицы, «рассказывающей» о ресурсах, следует, что наибольшей теневой ценой обладает ресурс №2 - «Светлый шоколад». Это и есть наиболее дефицитный ресурс. Правда интервал устойчивости, соответствующий этой цене (2.4973 у.е.) очень узок. Если запас светлого шоколада оценен с избытком в 10 единиц (то есть, на самом деле, его запас не 149, а 139), то реальная прибыль будет ниже на

ЛРпах = ЛЪ2 X 72 = - 10Х2.5 = - 25 >>.е.

Формулу для оценки уменьшения прибыли можно использовать, поскольку ЛЪ2 = -10 попадает в интервал устойчивости (допустимое уменьшение 11,

868952). Вместе с тем, если запас этого ресурса оценен с недостатком в 5 единиц (то есть, на самом деле, его запас не 149, а 154), предсказать увеличение прибыли нельзя, т.к. ЛЪ2 = +5 выходит за границы интервала устойчивости (допустимое увеличение 1,042254).

Ответить на последний вопрос (Есть ли другой способ добиться производства «Батончика», кроме изменения нормы прибыли или введения дополнительных ограничений на минимальное количество пакетов Батончика в плане?) не так просто.

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

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

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

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

устойчивости не дает. Однако, ориентиром в таком исследовании может служить, например, теневая цена ресурса

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

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

Ну, что ж, молодой человек, замечательно! Будем реализовывать! Только учти, что по технологии до (или после) производства конфеты Белка (особенно в таком количестве как ты рекомендуешь), надо остановить производственную линию и тщательно ее вычистить, а то будет брак! А стоит такая очистка 400 у.е.! Так что с премией своей можешь попрощаться.

Вот это удар!

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

Анализ Действия 3-его.

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

р = схX1 + с2X2 + ... + спХп .

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

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

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

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

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

Заметим, что встречающаяся в бухгалтерском учете практика «размазывания» постоянной издержки по всей партии выпущенных изделий и увеличение таким образом величины издержек на одно изделие, совершенно неприменима при решения ЛП-задачи об оптимальном плане. В этой задаче количество выпущенных изделий данного типа - это переменная X, подлежащая определению (т.е. заранее неясно на какое количество изделий нужно «размазать» постоянную издержку), а издержка (или прибыль) на одно произведенное изделие Cj должна быть постоянной (т.е. независящей от количества выпущенных изделий).

Вернемся теперь к анализу ситуации на кондитерской фабрике. Введем в рассмотрение величину постоянных издержек 400 у.е., связанную с производством конфеты «Белка» (Рис. 28).

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

В этих условиях целевую функцию - прибыль, можно записать «по ЕхсеГевски» следующим образом: =СУММПР0ИЗВ(С13:013;С9:09)- ЕСЛИ(Е13>0;Б10;0). A B С D E F G 1 На кондитерской фабрике 2 Продукты 3 Сырье Запасы Ореховый

звон Райский вкус Батончик Белка Ромашка 4 Темный шок. =1411+14 0,8 0,5 1 2 1,1 5 Светлый шок. = 149+15 0,2 0,1 0,1 0,1 0,2 6 Сахар =815,5+16 0,3 0,4 0,6 1,3 0,05 7 Карамель =466+17 0,2 0,3 0,3 0,7 0,5 8 Орехи = 1080+18 0,7 0,1 0,9 1,5 0 9 Прибыль 1 0,7 1,1 2 0,6 10 Постоянная издержка 400 11 Есть\Нет Y = 0 12 Ореховый

звон Райский вкус Батончик Белка Ромашка 13 Переменные 0,00 0,00 0,00 0,00 0,00 14 Цель 15 Расход P = =СУММПРОИЗВ(С13^13;C9:G9)-F10*F1 16 Темный шок. =СУММПРОИЗВ($С$13:$G$13;C4:G4) 17 Светлый шок. =СУММПРО1 18 Сахар =СУММПРО1 Вместо функции =если() =F13-10000*F11 19 Карамель =СУММПРО1 20 Орехи =СУММПРОИЗВ($С$13:$G$13;C8:G8) 1

Рис. 28

Однако, такой вид функции («ступенька») совершенно не соответствует принципам линейной модели. Более того, если убрать флажок в окне «Линейная модель», задача все равно не будет решаться. Функция ЕСЛИ - это «смерть» любого алгоритма оптимизации: он обязательно «застрянет» возле этой ступеньки и оптимального решения не найдет.

Для подобных случаев, существует специальный метод, позволяющий явно не использовать функцию =ЕСЛИ(..).

Для этого вместо каждой такой функции вводят одну дополнительную переменную и одно дополнительное ограничение.

Запишем в ячейке Б10 величину постоянной издержки (400) для конфеты «Белка», а в ячейку Б11 поместим новую переменную У, показывающую, выпускается «Белка» или нет. Чтобы показывать нам это переменная У будет принимать всего два значения: 0 и 1.

При этом для корректного расчета прибыли нужно написать: =СУММПР0ИЗВ(С13:013;С9:09)-Р11*Е10.

Если «Белка» выпускается, то переменная У=1, и из прибыли вычитаются 400 у.е. постоянной издержки очистки линии. Если «Белка» не выпускается, то переменная У=0 и из прибыли не вычитается ничего.

Разумеется, без дополнительного ограничения Поиск решения заведомо не станет присваивать переменной У значение 1, ибо это невыгодно. Поэтому запишем формулу =Е13-10000*Б11 , т.е. объем выпуска «Белки» - 10000 умноженное на переменную У - и, затем, потребуем в установках Поиска решения, чтобы это выражение было не больше 0!

В этом случае, если объем выпуска «Белки» хоть как-нибудь отличается от нуля, Поиск решения сможет удовлетворить заданное ограничение, только если задаст У=1. И 10000 здесь, это просто произвольное большое число, превышающее любой возможный (при данных ресурсах) объем выпуска конфет. В первоначальных решениях мы видели, что выпускается от 1000 до 1500 пакетов, значит, даже если будет выпускаться только одна «Белка», условие выполнится только при У=1. Если «Белка» не выпускается и значение ячейки Б13 равно нулю, то Поиск решения волен выбрать в качестве значения переменной У и ноль, и единицу. Но при выборе в качестве цели максимума прибыли, алгоритм, конечно, и теперь уже совершенно правомерно, оставит переменную У равной нулю.

Фактически, речь идет о том, что если оптимизационный алгоритм «согласен» положить У = 1 и уменьшить прибыль Р на величину 400 у.е., то ограничений на производство «Белки» нет. Если же, алгоритм «желает» положить У = 0, то ему придется отказаться от производства «Белки».

Чтобы переменная У принимала только значения 1 и 0 добавим соответствующее ограничение - «Б11=двоичное». Не забудьте только перед вводом этого ограничения добавить ячейку Б11 в список переменных.

Замечание.

Чтобы указать в качестве переменных несвязанные ячейки или диапазоны, нужно сначала выделить один диапазон, затем нажать на клавиатуре кнопку С1х1

и, удерживая ее, выделить второй диапазон, третий и т.д.

Итак, к нашему исходному групповому ограничению добавится еще два: новая переменная двоичная и конструкция =Е13-10000*Б11 в ячейке Б18 меньше или равна нулю. Если вы все сделали правильно запуск Поиска решения на выполнение принесет следующий результат (Рис. 29). Продукты Сырье Запасы Ореховый

звон Райский вкус Батончик Белка Ромашка Темный шок. 1411 0,8 0,5 1 2 1,1 Светлый шок. 149 0,2 0,1 0,1 0,1 0,2 Сахар 815,5 0,3 0,4 0,6 1,3 0,05 Карамель 466 0,2 0,3 0,3 0,7 0,5 Орехи 1080 0,7 0,1 0,9 1,5 0 Прибыль 1 0,7 1,1 2 0,6 Постоянная издержка 400 Есть\Нет У= 0 Ореховый

звон Райский вкус Батончик Белка Ромашка Переменные 0,00 283,66 1168,48 0,00 18,93 Цель Расход Р= 1495,25 Темный шок. 1331,1 Светлый шок. 149,0 Сахар 815,5 Вместо функции =если() 0,00 <=0 Карамель 445,1 Орехи 1080,0 Рис. 29

Кроме очевидных изменений в оптимальном плане, следует отметить главное - целевая функция уменьшилась по сравнению с прежним результатом всего на 14 у.е.! Ну а если вспомнить план, в котором тоже было много «Батончика», то и вообще только на 3 у.е.

Мало этого, можно посоветовать молодому человеку напомнить отцу, что в исходном плане старого мастера так же предусматривался выпуск «Белки», стало быть прибыль была бы не 1080 у.е., а всего 680! Так что парень честно отыграл еще 400 у.е.

Возвращаясь к хитрому приему, который позволил нам обойти использование функции =ЕСЛИ(..), следует проверить, что алгоритм вообще захочет, хоть при каких-нибудь условиях включить «Белку» в план производства. Очевидно, что при достаточной прибыльности «Белки» это должно оказаться выгодным. Вот только мы теперь не имеем инструмента в виде отчета об устойчивости, который нам мог бы подсказать, сколько именно прибыльности не хватает «Белке», чтобы войти в оптимальный план. Ведь при использовании целочисленных ограничений такой отчет создать невозможно.

Придется действовать методом подбора. В первоначальном плане «Белка» производилась в количестве 504 пакетов. Значит, чтобы вернуться к этому плану, окупив постоянную издержку в 400 у.е., одной дополнительной единицы прибыльности должно хватить. И действительно, при изменении прибыльности «Белки» до 3 у.е. оптимальное решение включает эту конфету в оптимальный план почти в прежнем объеме (Рис. 30). Прибыль 1 0,7 1,1 3 0,6 Постоянная издержка 400 Есть\Нет У= 1 Ореховый

звон Райский вкус Батончик Белка Ромашка Переменные 396,47 0,00 0,00 534,98 21,69 Цель Расход Р= 1614,43 Темный шок. 1411,0 Светлый шок. 137,1 Сахар 815,5 Вместо функции =если() -9465,02 <=0 Карамель 464,6 Орехи 1080,0 Рис. 30

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

1.П-5.

Действие 1-е. Оптимальный план.

На рисунке (Рис. 31) представлена схема движения материалов, частей, узлов и агрегатов, проходящих трансформацию от сырья к готовой продукции на заводе «Прогресс». Завод

производит

3

Рис. 31

А,

Б,

Б.

продукта

и Продукт Г 40 шт $180 і КЕБ 3 (10) > к К1 ?Б 4 ( 7) КЕБ 1 (14 ) ) к КЕБ 4 ( 20) к КЕБ 3 (12 ) ) к КЕБ 2 (15 ) ) к Сырье ГЯМ $65 КЕБ 1 ( 28 ) і КЕБ 4 (18 ) I к К! ?Б 3 ( 9 ) К Сырье ЕЯМ $30 Схема показывает последовательность операций на имеющихся у завода универсальных станках, которым необходимо подвергнуть сначала сырье, а затем полуфабрикаты, для производства готовых продуктов, и время (в минутах), необходимое для каждой операции. Это время указано на схеме в скобках рядом с именем соответствующего станка.

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

Например, два имеющихся одинаковых станка, обозначенных как Ресурс- 2,

требуются для выполнения 4-х операций (см. рисунок). Для осуществления

каждой из этих операций нужно некоторое время для перенастройки станка (setup time). В случае Ресурс- 2 необходимо 120 минут для перенастройки на любую из 4-х требуемых операций.

На схеме также показан максимальный рыночный спрос на каждый из продуктов фабрики (кол-во шт./неделю).

Для производства одной единицы продукта A требуется по одной единице сырья ARM и CRM. Одна единица продукта D требует по одной единице сырья ARM, CRM и ERM. Одна единица продукта F требует только одну единицу сырья FRM.

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

станков Ресурс- 1 15 1 Ресурс-2 120 2 Ресурс-3 60 2 Ресурс-4 20 2 Ресурс-5 0 1 Завод работает 5 дней в неделю, по 8 часов в день. Сверхурочная работа не допускается. Завод не имеет больших собственных складов и не может, поэтому, произвести за неделю больше, чем потребляет рынок.

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

станков Зарплата

$ Накладные расходы $ Всего$ Ресурс- 1 500 1500 2000 Ресурс-2 1000 1000 2000 Ресурс-3 1000 1800 2800 Ресурс-4 1000 2000 3000 Ресурс-5 500 700 1200 Итого 4000 7000 11000 Первый шаг анализа

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

Способен ли завод удовлетворить этот спрос?

Найдите оптимальный план производства продуктов А, Б и Б за неделю, который обеспечит заводу максимальную прибыль. Какова эта реальная прибыль?

Второй шаг анализа (Предложение добросовестного рабочего)

Недавно на заводе прошло общее собрание персонала, на котором выступал директор и призывал всех работать более эффективно, добиваться большей производительности.

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

(Определите, сколько процентов рабочего времени станок Ресурс-2 простаивает).

Мастер подсчитал, сколько необходимых полуфабрикатов для продуктов Л,Б и Б может произвести его станок. Он также подсчитал, какую прибыль мог бы получить завод, если бы он произвел и продал такое количество продуктов Л, Б и Б (Подсчитайте и Вы).

Мастер подготовил предложение о немедленном увеличении снабжения его станка сырьем и материалами с целью гигантского увеличения объема производства. «Сумасшедшие деньги просто валяются у нас под ногами, а мы не хотим их подобрать из-за нашего разгильдяйства и неумения работать!» - лейтмотив его предложения.

Принять ли предложение мастера или отклонить (и мягко успокоить добросовестного работника)?

Третий шаг анализа (Предложение ненормального инженера- технолога)

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

И вот написал ...рационализаторское предложение: переоборудовать станок Ресурс-2 так, чтобы тот смог выполнять часть работы станка Ресурс-1. При этом все операции, в которых участвует станок Ресурс-1, сократятся на 1 мин., зато все операции станка Ресурс-2 увеличатся на 3 мин. На переоборудование 2-х станков Ресурс-2 нужно $15000.

Директор не поленился и подсчитал, что в результате при производстве по 1 шт. продуктов Л, Б и Б на станке Ресурс-1 будет выиграно только 3 мин, а на станках Ресурс-2 проиграно 18 мин. Таким образом, длительность производственного цикла увеличится на 15 мин!

(Подсчитайте и Вы, по схеме технологического процесса на рис. 1 ).

«И за это $15000? Да он и правда ненормальный!»

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

Остановить ли директора или, правильно, пусть выгоняет дурака?

Четвертый шаг анализа

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

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

«Рынок полностью потребляет все производимые нами продукты типа А и Б. Если бы мы могли найти для них новые рынки сбыта, мы смогли бы производить их больше и получать больше прибыли!» Все восприняли замечание зам. директора как очень правильное. (Согласны ли Вы с ним?) Зам. директора по маркетингу сказал также, что он слышал о том, что в Монголии есть спрос на продукты, которые производит завод. Он готов съездить в командировку в Монголию и разобраться на месте. Разумеется, предложение было одобрено.

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

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

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

Пятый шаг анализа

После долгих колебаний директор решается выйти на собрание акционеров с предложением купить еще один станок Ресурс-1 за $300,000. Это потребует удвоить количество рабочих, занятых на обслуживание и в операциях со станком Ресурс-1. Соответственно удвоятся операционные расходы. Акционеры потребуют информацию о том, когда окупятся инвестиции, и какую прибыль сможет приносить завод после этого Найдите новый оптимальный план производства продуктов А, Б и Б за неделю, который обеспечит заводу максимальную прибыль. Какова теперь эта прибыль?

За сколько времени окупятся инвестиции? (Найдите не дисконтированный период окупаемости).

Сделайте расчет в двух вариантах:

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

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

Какое решение относительно целесообразности покупки второго станка Ресурс-1, приняли бы Вы в каждом из вариантов? Анализ Действия 1-го.

Шаг 1.

На первом шаге анализа необходимо сформулировать и решить задачу линейной оптимизации плана производства завода. Для того, чтобы сделать это, удобно организовать данные, приведенные в тексте в следующую таблицу. А в с D Е F G н 1 1 Шаг 1-3 2 Оптимальный план 3 Продукты 4 Станки Запас

времени А D F Время

обработки % использования оборудования Кол-во

переналадок Время

переналадки 5 РЕСУРС-1

РЕСУРС-2

РЕСУРС-3

РЕСУРС-4

РЕСУРС-5 2400 0 34 14 =СУММПРОИЗВ($С$11: $Е$11 С5:Е5)+Н5‘15 15 6 4800 24 9 15 =F6/B6 4 120 7 4800 33 15 22 6 60 В 4800 20 18 27 4 20 Э 2400 8 17 0 і г г 2 0 10 Прибыль 115 145 115 11 Рыночный спрос 40 80 40 12 Макс. Прибыль =СУММПРОИЗВ(С11 :Е11 ;С10:Е10)-11000 13 14 А D F Время

обработки % использования оборудования 15 Переменные 0 0 0 РЕСУРС-1 =СУММПР0ИЗВ($В$15:$0$15;С5:Е5)+Н5‘15 16 РЕСУРС-2 =F16/В6 17 Цель РЕСУРС-3 18 Прибыль =СУММПРОИЗВ(В15:015;С10:Е10)-11000 19 РЕСУРС-5 і г 1 г Рис. 32

Заполнение пустой таблицы начнем с внесения информации о рыночном спросе в ячейки С11:Е11 (для продуктов A,D,F - это 40, 80, 40 штук соответственно). Затем, заполним строчку «Прибыль» (ячейки С10:Е10). Для этого взглянем на схему технологического процесса и определим, какое сырье требуется для производства 1 шт. продукта А. Двигаясь сверху вниз по схеме (квадратика с именем продукта к квадратикам с именем сырья) найдем, что для производства 1 шт. продукт А требуется 1 порция сырья ARM и 1 порция сырья CRM. Вычитая из отпускной цены продукта А стоимость сырья ARM и CRM, найдем условную прибыль при производстве продукта А, равную $115. Аналогично, прибыль от производства 1 шт. продуктов D и А равны $145 и $115 соответственно.

При расчете этой условной прибыли мы приняли предположение, что переменная часть издержек связана только с затратой сырья. Все остальные издержки (включая зарплату рабочих, обслуживающих универсальные станки), включены в постоянную издержку, связанную с функционированием завода - $11000 (как следует из таблицы Рис. 32).

Взяв сумму произведений рыночного спроса на условную прибыль от 1 шт. каждого продукта и вычтя постоянную издержку в $11000, найдем максимальную прибыль. которую может заработать завод за неделю, если удовлетворит этот рыночный спрос (формула в ячейке С12). Результат - $9800 в неделю.

По-видимому, завод не может заработать эту прибыль, поскольку не может удовлетворить рыночный спрос из-за недостатка производственных ресурсов. Такими производственными ресурсами, очевидно, является время обработки на каждом универсальном станке, которым располагает завод в неделю. Поскольку завод работает в одну смену, 5 дней в неделю - это 40 часов на каждом станке. Выразим это время в минутах (т.к. расход времени каждого ресурса на каждую технологическую операцию, задан на схеме в минутах). Так как Ресурс-1 присутствует на заводе в одном экземпляре (Рис. 32), время обработки различных полуфабрикатов на нем составляет 2400 мин. в неделю. Станки Ресурс-2, Ресурс-3 и Ресурс-4 присутствуют в 2 экземплярах (Рис. 32), поэтому время обработки на каждом из них - по 4800 мин. в неделю. На станке Ресурс-5 (так же как на Ресурс- 1) имеется 2400 мин. в неделю.

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

Взглянем опять на схему технологического процесса и определим, сколько времени станка Ресурс-1 требует производство 1 шт. продукта А? Следуя по схеме сверху вниз (от продукта А к сырью), видим, что в этой части схемы Ресурс-1 вообще не встречается. Следовательно, для производства продукта А он не нужен. Т.е. продукт А требует 0 мин. от Ресурса-1. Аналогично найдем, что производство продукта Б требует от станка Ресурс-1 34 мин., а производство продукта Б - 14 мин.

Действуя аналогично заполним строчку С6-Е6 (сколько времени каждый из продуктов А, Б и Б требуют от Ресурса-2), и оставшиеся строчки С7:Е9 (нормы временных затрат Ресурсов 3-5 на производство А,Б и Б).

После этого, сосчитаем, сколько всего времени требуется от каждого ресурса, чтобы выполнить рыночный спрос (т.е. произвести 40 шт. А, 80 шт. Б и 40 шт. Б). Для этого очевидно необходимо найти сумму произведений строчки норм затрат данного ресурса на единицу каждого продукта на требуемое количество каждого продукта в соответствие с рыночным спросом. Введенная в ячейку Б5 формула отражает это действие (ее, разумеется, следует протянуть на ячейки Б6:Б9).

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

С одной стороны, чем меньше переналадок мы делаем, тем меньше времени Ресурс простаивает, тем больше продукции мы можем произвести. Минимальное количество переналадок Ресурса 1, необходимое чтобы обеспечить недельный цикл (т.е., чтобы следующую неделю можно было бы начать, имея Ресурс 1, настроенный на ту же технологическую операцию, что и в начале прошлой недели), очевидно, должно быть равно 3. Аналогично, для Ресурса 2 количество переналадок будет 4 (несмотря на то, что количество станков равно 2). Примем, для простоты, что вообще, минимальное количество переналадок в неделю равно количеству технологических операций, в которых участвует данный Ресурс (в скольких бы экземплярах станок не существовал, и в скольких бы операциях не участвовал). Количество переналадок и времени каждой переналадки для каждого станка введены в ячейках Н5:Н9 и 15:19 соответственно.

С другой стороны, минимальное количество переналадок, которые мы собираемся делать, означает большой размер партии продукции, который мы «прогоняем» через каждый Ресурс, настроенный на данную технологическую операцию. Это означает, что на полу в цехах завода (или на специальных промежуточных складах) будет лежать большой объем различных полуфабрикатов - незавершенной продукции, в которой заморожены средства, затраченные на сырье, труд и пр. С этим связаны специфические издержки хранения, которые мы не учитываем сейчас, при анализе кейса, но которые в реальности могут заставить изменить наше решение о минимальном количестве переналадок (подробнее об издержках хранения см. учебные пособия [1,2] и задачи соответствующего раздела в настоящем сборнике).

После введения формул в ячейки Б5:Б9 и формул, показывающих процент использования оборудования (т.е. отношение требуемого времени на обработку и переналадку каждого Ресурса для производство продукции в количестве, равном рыночному спросу, к реально имеющемуся времени), мы можем видеть, что Ресурс-1 должен быть загружен на 139%, в то время как все остальные ресурсы недогружены (Рис. 33). Таким образом. Ресурс-1 является узким местом («бутылочным горлышком») нашего технологического процесса, и не позволяет заводу удовлетворить рыночный спрос полностью и заработать максимально возможную прибыль (поскольку использование сверхурочных не предусматривается).

Чтобы рассчитать реальную прибыль, которую может заработать завод, нужно решить задачу линейной оптимизации. В качестве переменных решения (ячейки Б15:Б15) выберем реальные количества продуктов А, Б и Б, которые может произвести завод, чтобы максимизировать прибыль - целевую функцию (ячейка Б18). При этом в ячейках Б15:Б19 вычислим сколько времени на обработку и переналадку каждого Ресурса при этом требуется, а в ячейках 015:019 - каков при этом будет процент использования оборудования. Разумеется, в ограничениях для «Поиска решения» необходимо потребовать, чтобы этот процент не превышал 100%. Кроме того, необходимо потребовать, чтобы количество произведенного продукта каждого типа (А, Б, Б) не превышало рыночный спрос. В результате решения этой задачи получим следующий результат Рис. 33. А в С 0 Е Р е н I 1 Решение 2 Оптимальный план 3 Продукты 4 Станки Запас

времени А 0 Р Время

обработки */• использования оборудования Кол-во

переналадок Время

переналадки 5 гев1 2400,00 0,00 34,00 14,00 3325,00 139% 3,00 15,00 6 геэ2 4800,00 24,00 9,00 15,00 2760,00 58% 4,00 120,00 7 геэЗ 4800,00 33,00 15,00 22,00 3760,00 78% 6,00 60,00 8 геэ4 4800,00 20,00 18,00 27,00 3400,00 71% 4,00 20,00 Э геэ5 2400,00 8,00 17,00 0,00 1680,00 70% 0,00 10 Прибыль 115,00 145,00 115,00 11 Рыночный спрос 40,00 80,00 40,00 12 Макс. Прибыль 9800,00 13 14 А 0 Р Время

обработки */• использования оборудования 15 Переменные 40,00 52,79 40,00 геэ1 2400,00 100% 16 геэ2 2515,15 52% 17 Цель геэЗ 3351,91 70% 1В Прибыль 5855,15 геэ4 2910,29 61% 13 геэ5 1217,50 51% Рис. 33

Таким образом, прибыль завода почти на $4000 ниже максимальной. Это и является «завязкой» сюжета кейса: как улучшить производительность цеха и добиться большей прибыли?

Шаг 2.

Разумеется, ответ на вопрос, сформулированный на этом шаге - отрицательный. Предложение добросовестного рабочего не проходит. Поток произведенной продукции завода определяется его узким местом - Ресурсом-1, и сколько бы полуфабрикатов не произвел мастер на станке Ресурс-2, эта продукция будет не более чем мусор, поскольку станок Ресурс-1 не позволит переработать ее всю в конечную продукцию. Вместе с тем интересно узнать, какую все-таки прибыль завода мог «насчитать» наш мастер, если бы он игнорировал все ограничения (производственные мощности других Ресурсов, ограничения по рыночному спросу), кроме ограничения на производительность своего Ресурса-2. Ответ поразителен: прибыль увеличилась бы в 10 раз. При этом, производить нужно было бы только продукт D в количестве в 8 раз превышающем рыночный спрос.

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

Шаг 3.

Прежде всего, следует понять, откуда директор получил увеличение времени производственного цикла (т.е. времени, необходимого для производства 1 шт. A, 1 шт. D и 1 шт. F). Взглянем на схемы технологического процесса.

Видно, что Ресурс-1 не используется при производстве A. Таким образом, выигрыш от снижения времени операций на станке Ресурс-1 нет. При производстве D, Ресурс-1 используется дважды. На каждой операции выигрыш составит по 1 мин. При производстве F, Ресурс-1 используется один раз - выигрыш 1 мин. Итого, выигрыш 3 мин. Однако, при производстве продукта А Ресурс-2 используется трижды. На каждой операции проигрыш составляет по 3 мин. (итого -9 мин.). При производстве продукта D Ресурс-2 используется дважды. На каждой операции проигрыш составляет по 3 мин. (итого - 6 мин.). Наконец, при производстве продукта F Ресурс-2 используется один раз - проигрыш 3 мин. В сумме на увеличении времени операций станка Ресурс-2 мы теряем 18 мин. Эффект от внедрения этого рацпредложения - увеличение времени производственного цикла на 15 мин. По мнению директора это недопустимо (по-видимому, это время фигурировало в отчетных документах завода).

На самом деле, конечно, в предложении молодого технолога есть смысл. Ведь увеличение времени обработки на станке Ресурс-2 означает лишь уменьшение его простоев, в то время как, пусть и небольшое, но уменьшение времени обработки на станке Ресурс-1, означает расширение узкого места и реальное увеличение выпуска конечной продукции. Вопрос лишь в том, насколько быстро это рационализаторское предложение окупится? Это нужно сосчитать.

Скопируйте лист с полученным на шаге 1 решением исходной задачи (как описано в примере решения задачи о фирме «Фасад»- при этом скопируются и установки «Поиска решений») и замените данные о нормах расхода времени Ресурсов 1-2 после внедрения предложения технолога. Измененный фрагмент таблицы Ms Excel представлен на Рис. 34 3 Продукты 4 Станки Запас

времени А D F 5 Ресурс -1 ?400 0 32 13 6 Ресурс -2 4800 33 15 18 Рис. 34

После использования «Поиска решения» получим новый оптимальный план (Рис. 35). >

ш

о

о Е F н I 1 "Ненормальный" инженер-технолог 2 Оптимальный план 3 Продукты 4 Станки Запас

времени А D F Время

обработки % использования оборудования Кол-во

переналадок Время

переналадки 5 Ресурс -1 2400 0 32 13 3125 130% 3 15 6 Ресурс -2 4800 33 15 18 3720 78% 4 120 7 Ресурс -3 4800 33 15 22 3760 78% 6 60 8 Ресурс -4 4800 20 18 27 3400 71% 4 20 9 Ресурс -5 2400 8 17 0 1680 70% 0 10 Прибыль 115 145 115 11 Рыночный спрос 40 80 40 12 Макс. Прибыль 9800 13 14 А D F Бремя

обработки % использования оборудования 15 Переменные 40 57.344 40 Ресурс -1 2400,00 100% 16 Ресурс -2 3380,16 70% 17 Цель Ресурс-3 3420,16 71% 18 Прибыль 6514.84 Ресурс -4 2992,19 62% 19 Дельта 659.70 Ресурс -5 1294,84 54% 20 Период

окупаемости 22,7 нед. Рис. 35

Видно, что после внедрения предложения инженера-технолога еженедельная прибыль возросла на $659,7. Это означает, что примерно через 23 недели (меньше чем за полгода) инвестиции в усовершенствование производственного процесса окупятся, и прибыль завода возрастет на 11%.

Шаг 4.

Прежде всего, согласимся с директором по маркетингу, что отечественный рынок ограничивает производство продуктов A и F. И в оптимальном плане для исходной ситуации (Рис. 33), и после внедрения предложения технолога (Рис. 33) продукты A и F производятся по максимуму, который может быть потреблен рынком.

Разумеется, и производственная мощность Ресурса-1 также используется полностью. Поэтому трудно ожидать прироста выпуска продуктов A и F (если бы емкость рынка увеличилась), без уменьшения выпуска продукта D. Но при этом валовая прибыль завода, конечно, может возрасти.

Для проверки этого предложения директора по маркетингу нам понадобится новая таблица MS Excel (Рис. 36) А в с D Е F G н I J к :т 1 Шаг 4-5 2 Оптимальный план 3 Продукты 4 Запас

времени А D F DM FM Время

обработки */• использования оборудования Кол-во

переналадок Время

переналадки 5 Ресурс -1 2400 0 32 13 32 13 3645 152% 3 15 6 Ресурс -2 4300 33 15 18 15 18 3120 65% 4 120 7 Ресурс -3 4300 33 15 22 15 22 3320 69% 6 60 В Ресурс -4 4300 20 13 27 18 27 3680 77% 4 20 9 Ресурс -5 2400 8 17 0 17 0 1360 57% 0 10 Прибыль 115 145 115 65 55 11 Рыночный спрос 40 30 40 35 25 12 Макс. Прибыль 13450 13 14 А D F DM FM Время

обработки */• использовлння оборудования 15 Переменные 0 0 0 0 0 Ресурс =CyMMnPOH3B($B$15:$F$15;C5:G5)+J3‘K3 16 Ресурс -2 =Н16/В6 17 Цель Ресурс -3 1В Прибыль =CyMMnPOH3B(B15:F15;C10:G10)-11000 19 Ресурс -5 Рис. 36

Здесь мы рассматриваем продукты, которые завод будет выпускать для монголов, как новые продукты в ассортименте. Они требуют таких же норм затрат Ресурсов на их производств как и продукты D и F, производимые на отечественный рынок, но приносят меньшую прибыль (прибыль рассчитана как 2/3 от нормальной отпускной цены продуктов D и F минус те же издержки) и имеют другие рыночные ограничения.

Таким образом, теперь в нашей задаче 5 переменных. Соответственно поправлены формулы в ячейках B18 и H15:H19 (для расчета общего расхода времени Ресурсов на данный производственный план).

Результат расчета показан на Рис. 37 13 А D F DM FM Время

обработки % использования оборудования 14 Переменные 40 57,344 40 0 0 Ресурс -1 2400 100% 15 Ресурс -2 3140,1563 65% 1Є Цель Ресурс -3 3240,1563 68% 17 Прибыль 6514,84 Ресурс 4 2952,1875 62% 1В Ресурс -5 1294,8438 54% Рис. 37

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

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

Изменяемые ячейки Результ. Нормир. Целевой Допустимое Допустимое Ячейка Имя значение стоимость Коэффициент Увеличение Уменьшение $В$15 Переменные А 40 115 115 1Е+30 115 $С$15 Переменные D 57,34 0 145 138,08 0,62 $D$15 Переменные F 40 56,00 115 1Е+30 56,00 $Е$15 Переменные DM 0 -80,00 65 80.00 1Е+30 $F$15 Переменные FM 0 -3,91 55 3.91 1Е+30 граничения Результ. Теневая Ограничение Допустимое Допустимое Ячейка Имя значение Цена Правая часть Увеличение Уменьшение $Н$15 Ресурс -1 Время обработк 2400 4,53 2400 725 1835 $Н$16 Ресурс -2 Время обработк 3140,16 0 4800 1Е+30 1650,84 $Н$17 Ресурс -3 Время обработк 3240,16 0 4800 1Е+30 1550,84 $Н$18 Ресурс 4 Время обработк 2052,10 0 4800 1Е+30 1847,81 $Н$19 Ресурс -5 Время обработк 1204,84 0 2400 1Е+30 1105,16 Рис. 38

Ключом к анализу решения в данном случае является столбик «Нормир. стоимость» таблицы «Изменяемые ячейки» этого отчета. Нормированная стоимость (если она отрицательна) показывает, сколько не хватает данному продукту по норме прибыли, чтобы войти в оптимальный план (подробнее о смысле нормированной стоимости читайте в учебном пособии [1]). Для переменной DM (количество продукта D для монголов)_нормированная стоимость равна -$80. Ровно настолько прибыль от предполагаемой продажи D монголам ($65) ниже, чем от продажи D на отечественном рынке.

Этот результат совершенно понятен. Ведь мы не может удовлетворить спрос на D на отечественном рынке при прибыли $145 за 1 штуку D. Понятно, что при этом продавать D монголам с потерей $80 за штуку будет слишком щедро. Да ведь и сам директор по маркетингу в своем выступлении на собрании руководителей говорил о необходимости расширения рынка для продуктов A и F, а не D (по-видимому, за время своего путешествия по Монголии он просто забыл об этих «несущественных» деталях).

Гораздо интереснее результат для продукта FM (фактически - продукт F, предназначенный для монголов). Его нормированная стоимость составляет только -$3,91. Это значит, что если директору по маркетингу удастся «подвинуть» цену на этот продукт в переговорах с монголами всего на $5 за штуку (что при отпускной цене $120 вполне реальная задача), производство F для монголов станет выгодным!

Допустим, это удалось, и проверим, какой будет теперь новый оптимальный план. Для этого просто изменим в нашей таблице MS Excel прибыль для FM с $55 до $60 и запустим «Поиск решения». Результат представлен во фрагменте Рис. 39 . Ресурс -5 2400 8 17 0 17 0 Прибыль 115 145 115 65 60 Рыночный спрос 40 80 40 35 25 Макс. Прибыль 13575 А D F DM FM Вре

обраЕ Переменные 40 47,19 40 0 25 Ресурс -1 Ресурс -2

3437

Цель

16 Прибыль

6542,19

Ресурс -3

3637

Ресурс 4

344

14 14

1G 17

Рис. 39

Теперь нужно производить 25 шт. FМ для монголов, за счет сокращения выпуска D на отечественный рынок с 57 до 47 штук в неделю. При этом прибыль слегка возрастает (с $6514 до $6542) за счет того, что мы превысили пороговую прибыльность в расчете на единицу продукта FM на $1.09 ($5 - $3.91).

Если по какой-то причине даже такой минимальный сдвиг цены невозможен, можно, в конце концов, согласиться на этот план и при прежней цене на FM. При этом прибыль завода снизится с $6514 до $6417 ($6514 - $3.91*25), что составит всего 1,5%. Ради стратегической перспективы, такую жертву, наверное, можно перетерпеть. Используя полученную таблицу MS Excel, можно рассмотреть и другие компромиссные планы и/или ограничения.

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

Шаг 5.

Для расчета нового оптимального плана в случае приобретения второго станка Ресурс-1 достаточно внесение минимальных изменений в одну из рассмотренных ранее таблиц MS Excel.

Если мы предположим, что в нашем распоряжении есть только отечественный рынок, то модифицировать нужно таблицу на Рис. 33. При этом необходимо запас времени для обработки и переналадки Ресурса-1 увеличить вдвое (записать в ячейку B5 4800 вместо 2400), а также учесть увеличение операционных расходов на $2000, связанных с обслуживание второго станка Ресурс-1 (см. условие задачи), т.е. вычесть 2000 из формул в ячейках С12 и В18. А в

к Ресурс-1. Ш

О

о <3 н I 1 Втором стано Голько отечественным рынок. 2 Оптимальным план 3 Продукты 4 Станкм Запас

времени А 0 Р Время

обработки '/• использования оборудования Кол-во

переналадок Время

переналадки 5 Ресурс -1 4800 0 32 13 3125 65% 3 15 6 Ресурс -2 4800 33 15 18 3720 78% 4 120 7 Ресурс -3 4800 33 15 22 3760 78% 6 60 8 Ресурс -4 4800 20 18 27 3400 71 % 4 20 9 Ресурс -5 2400 8 17 0 1680 70% 0 10 Прибыль 115 145 115 11 Рыночным спрос 40 80 40 12 Макс. Прибыль 7800 13 14 А Б Р Время

обработки % использования оборудования 15 Переменные 40 80 40 Ресурс -1 3125,00 65% 16 Ресурс -2 3720,00 78% 17 Цель Ресурс-3 3760,00 78% 18 Прибыль 7800,00 Ресурс -4 3400,00 71 % 19 Дельта 1285.16 Ресурс -5 1680,00 70% Период 20 окупаемости 233,43 нед. Рис. 40

Как видно из полученной таблицы (Рис. 40), теперь завод зарабатывает максимально возможную прибыль (правда, по сравнению с первоначальным вариантом, она уменьшилась на $2000). Дополнительная прибыль, по сравнению с первоначальным вариантом увеличилась примерно на $1285 в неделю (если предложение технолога, рассмотренное на шаге 3, на заводе внедрено). Окупятся вложенные инвестиции ($300000) за 4,5 года. При этом, загрузка всех Ресурсов (включая и Ресурс -1, теперь - в количестве 2 станков) не будет превышать 70%- 80%.

Если вдруг за это время рыночный спрос изменится, так что потребуется выпускать, скажем, 80 шт. А, 40 шт. Б и 80 шт. Б в неделю, то, как видно из фрагмента нашей таблицы (Рис. 41), лимитирующими окажутся Ресурсы 2-3, в то время как недавно купленный новый станок Ресурс-1 окажется совершенно не загруженным. Продукты Станки Запас

времени А й Р Время

обработки % использования оборудования ПЄ| Ресурс -1 4800 0 32 13 2365 49% Ресурс -2 4800 33 15 18 5160 108% Ресурс -3 4800 33 15 22 5360 112% Ресурс -4 4800 20 18 27 4560 95% Ресурс -5 2400 8 17 0 1320 55% Прибыль 115 145 115 Рыночным спрос 80 40 80 Макс. Прибыль 11200 Так покупать или не покупать новый станок Ресурс-1? Ведь это серьезное инвестиционное решение для завода. Но модели не принимают решений! Это дело менеджеров. Думается, однако, что рассмотренная модель дает менеджеру немало информации к размышлению, весьма полезной для принятия рационального управленческого решения. ь_

ш

о

о

ш

< о Н ' Л К 1 Второй станок Ресурс-1. Монгольским рынок открыт. 2 Оптимальный план 3 Продукты 4 Запас

времени А 0 Р ОМ РМ Бремя

обработки % использования оборудования Кол-во

переналадок Время

переналадки 5 Ресурс -1 4800 0 32 13 32 13 4570

СП 3 15 6 Ресурс -2 4800 33 15 18 15 18 46Э5 98% 4 120 7 Ресурс -3 4800 33 15 22 15 22 4835 101% 6 60 8 Ресурс -4 4800 20 18 27 18 27 4705 98% 4 20 9 Ресурс -5 2400 8 17 0 17 0 2275 95% 0 10 Прибыль 115 145 115 65 55 11 Рыночный спрос 40 80 40 35 25 12 Макс. Прибыль 11450 13 14 А 0 Р ? М РМ Бремя

обработки % использования оборудования 15 Переменные 40 80 40 35 23,41 Ресурс-1 4549.31818 95% 16 Ресурс-2 4666.36364 97% 17 Цель Ресурс-3 4800 100% 18 Прибыль 11362.50 Ресурс-4 4662.04545 97% 19 Дельта 4847,66 Ресурс-5 2275 95% 20 Период

окупаемости 61,89 нед. Рис. 42

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

Видно, что теперь завод полностью справляется с рыночным спросом на отечественном рынке и почти закрывает потребности монгольского рынка, зарабатывая при этом прибыль $11363, очень близкую к максимально возможной $11450. Выигрыш по сравнению с первоначальным вариантом составляет $4848, и инвестиции ($300000) окупаются чуть больше, чем за 1 год и 2 месяца.

Разумеется, в этом случае инвестиция выглядит гораздо более привлекательной, чем если бы монгольский рынок не был открыт. Особенно, по- видимому, греет душу финансового директора то, что производственная линия завода оказывается сбалансированной: все ресурсы загружены примерно одинаково, причем процент использования оборудования от 95%-100%. Никто не простаивает!

Однако, мечта финансового директора, скорее всего, обернется кошмаром для менеджеров производственного отдела. Отсутствие ярко выраженного «узкого места» сразу резко усложнит процесс составления производственных графиков и планов закупки сырья и материалов, а неизбежные вариации длительностей обработки, сроков поставки и т. п. будут приводить либо к простоям, либо к длинным очередям полуфабрикатов то на одной то на другой операции, провоцируя авралы с неизбежными потерями качества продукции (подробнее об этом см. [16]). Однако, это уже совершенно другая тема, выходящая далеко за рамки линейной оптимизации производственного плана. 1. П-6. Аренда с предоплатой

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

Требующиеся площади: 30, 20, 40, 10, 50 и 20 тыс.м2 в январе, феврале, ..., июне месяце соответственно. Стоимость аренды 1 м на 1, 2, 3, 4, 5 и 6 месяцев: 7; 12.8; 18.6; 23.6; 27.5 и 31.2 $ соответственно, оплата вперед за весь срок в пределах 6 мес.

Учтите, что в январе расходы на аренду не должны превышать $400 тыс., а в феврале и в марте по $200 тыс. a.

Составьте план аренды, минимизирующий затраты. b.

Сравните с оптимальным планом различные варианты аренды, которые можно было бы предложить не решая задачу (скажем те, что были упомянуты в условии задачи). c.

Представьте, что никаких финансовых ограничений нет, сколько денег можно было бы сэкономить на соответствующем этому случаю плане аренды?

ё. Рассмотрите вопрос о кредите, который можно взять в январе под 5% в месяц, чтобы реализовать этот лучший план. Помните, что в реальности вы можете выплатить в первые три месяца только 400, 200 и 200 тыс. соответственно, а в следующие 3 мес. ваши финансовые возможности не ограничены. Стоит ли взять кредит?

Решение задачи.

Вначале давайте определимся с выбором переменных задачи. Так как по смыслу задачи нам необходимо решить для каждого месяца, сколько квадратных метров (точнее, десятков тысяч. м2) складской площади нанимать и на какой срок, имеет смысл выбрать 36 переменных - 6 сроков найма для каждого из шести месяцев. Понятно, что примерно половина переменных должны оставаться нулями, так как мы не можем нанимать площади в начале июня, например, на срок больше месяца. А на все шесть возможных сроков складские площади можно арендовать только в январе. Но с этими подробностями можно разобраться позже.

Целевая функция задачи - общая сумма арендной платы. Имея план найма по месяцам подсчитать ее не сложно.

На Рис. 43 показан вариант организации данных на листе Excel. В строке C3:H3 собраны данные о стоимости аренды на сроки от 1 до 6 месяцев. Так как платить нужно сразу за весь срок найма, данные о стоимости аренды в расчете на один месяц нам не понадобятся. В табличке C7:H12 будут располагаться переменные. При этом число в ячейке Е8, например, будет означать, сколько тыс. А В С Ь Е Г б Н I К 1 Стоимость аренды 2 На сколько месяцев на 1 на 2 на 3 на 4 на 5 на 6 3 7 12.8 19 24 28 31 1 5 Возможный план Переменные: сколько тыс. м2 арендовать и на сколько месяцев 6 аренды в: на 1 на 2 на 3 на 4 на 5 на 6 Ограничения: 7 январь =СУММ(С7:Н7) 30 8 февраль =СУММ(С8:С8;07:Н7) 20 9 март =СУММ(С9:Р9;08:С8;Е7:Н7) 40 10 апрель =СУММ(С10:Е10;09:Р9;Е8:С8;Р7:Н7) 10 11 май =СУММ(С11:011;010:Е10;Е9:Р9;Р8:С8;С7:Н7) 50 12 июнь =СУММ(С12;011;Е10;Р9;С8;Н7) 20 Рис. 43

В столбце 17:Л2 будем подсчитывать, сколько тыс. м2 площади имеется у нас в аренде в каждом из шести месяцев полугодия. На рисунке показаны формулы для расчета. Эти формулы не так просты, как можно было бы ожидать, потому что почти каждый раз нужно учитывать не только те площади, которые мы наняли в текущем месяце, но и нанятые ранее на срок больше месяца.

Для января, конечно, все просто, так как нанятых ранее площадей нет. Значит, простая сумма нанятых в январе площадей и есть полная арендованная площадь.

Для расчета суммарного количества складских площадей в феврале нужно сложить все площади, нанятые в феврале, и добавить площади, нанятые в январе на срок два месяца и более (формула =СУММ(С8:08;Б7:Н7)).

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

Эти количества имеющихся в каждом месяце площадей должны быть не меньше плановой потребности (ячейки К7:К12). А В С ь Е ? б н I 1 К 1 Стоимость аренды 2 На сколько месяцев на 1 на 2 на 3 на 4 на 5 на 6 3 7 12.8 19 24 28 31 I 5 Возможный план Переменные: сколько тыс. м2 арендовать и на сколько месяцев 6 аренды в: на 1 на 2 на 3 на 4 на 5 на 6 Ограничения: 7 январь =СУММ^7^7) 30 8 февраль =СУММ^8^8^7^7) 20 9 март 40 10 апрель 10 11 май 50 12 июнь 20 13 январь =СУММПРОИЗВ($C$3:$H$3;C7:H7) 400 тыс. 15 февраль =СУММПРОИЗВ($C$3:$H$3;C8:H8) 200 тыс. 16 март =СУММПРОИЗВ($C$3:$H$3;C9:H9) 200 тыс. 17 апрель О

О

3;

$

X

ее

3:

$

О

($

В(

З

И

О

Р

П

М

М

У

С

= И10) 18 май =СУММПРОИЗВ($C$3:$H$3;C11 И11) 19 июнь сЧ

О

3;

$

X

ее

3:

$

О

($

В(

З

И

О

Р

П

М

М

У

С

= Ж2) 20 С мин.= ^УММ^М:^) Рис. 44

В ячейках С14:С19 (Рис. 44) подсчитаем сколько денег будет затрачено на аренду в каждом месяце. При принятой схеме оплаты для этого нужно просто умножить нанятые в данном месяце площади на цены аренды и сложить.

В последней ячейке столбца (С20) все месячные выплаты просуммированы. Эта сумма и будет целевой функцией задачи оптимизации.

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

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

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

Попробуем первое очевидное решение - нанимать каждый месяц ровно столько, сколько нужно в этом месяце на срок в один месяц (Рис. 45). А В С ь Е Г б Н I 1 К 5 Возможный план Переменные: сколько тыс. м2 арендовать и на сколько месяцев 6 аренды в: на 1 на 2 на 3 на 4 на 5 на 6 Ограничения: 7 январь 30.00 30 30 8 февраль 20.00 20 20 9 март 40.00 40 40 10 апрель 10.00 10 10 11 май 50.00 50 50 12 июнь 20.00 20 20 13 14 январь 210.0 тыс. Плата за аренду 400 тыс. 15 февраль 140.0 тыс. 200 тыс. 16 март 280.0 тыс. 200 тыс. 17 апрель 70.0 тыс. 18 май 350.0 тыс. 19 июнь 140.0 тыс. 20 С мин.= 1 190.0 Целевая функция: Рис. 45

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

Можно даже не пробовать аренду 50 тыс. м2 на шесть месяцев в январе. Очевидно, что расходы превысят 1500 тыс., притом, что лимит расходов в январе 400 тыс.

Так как плата за месяц аренды при больших сроках меньше, очевидно, что следует максимально использовать такую скидку. Наименьшие потребности в площадях составляют 10 тыс. м2 (в апреле). В связи с этим можно нанять в январе 10 тыс. м на шесть месяцев и еще 20 на один месяц. А в остальные месяцы до- нанимать к имеющимся 10 тыс. м столько, сколько не хватает до плановой потребности. А В С ь Е Г б Н I 1 К 5 Возможный план Переменные: сколько тыс. м2 арендовать и на сколько месяцев 6 аренды в: на 1 на 2 на 3 на 4 на 5 на 6 Ограничения: 7 январь 20.00 10.00 30 30 8 февраль 10.00 20 20 9 март 30.00 40 40 10 апрель 0.00 10 10 11 май 40.00 50 50 12 июнь 10.00 20 20 13 14 январь 452.0 тыс. Плата за аренду 400 тыс. 15 февраль 70.0 тыс. 200 тыс. 16 март 210.0 тыс. 200 тыс. 17 апрель - тыс. 18 май 280.0 тыс. 19 июнь 70.0 тыс. 20 С мин.= 1 082.0 Целевая функция: Такой план приведен на Рис. 46. Расходы снизились до 1082 тыс. Однако есть перерасход средств в январе и марте.

Если не задаваться целью не иметь лишних площадей, можно обратить внимание на то, что в феврале и июне нужно 20 тыс. м2, Таким образом, сняв в январе 20 тыс. м2 на все шесть месяцев можно полностью покрыть потребности этих месяцев и заодно сильно сократить затраты на ежемесячный найм площадей. Правда в апреле 10 тыс. м2 будут простаивать, но проверить такой план не мешает. Результат расчета приведен на следующем рисунке (Рис. 47). А В С ь Е ? б Н I 1 K 5 Возможный план Переменные: сколько тыс. м2 арендовать и на сколько месяцев 6 аренды в: на 1 на 2 на 3 на 4 на 5 на 6 Ограничения: 7 январь 10.00 20.00 30 30 8 февраль 20 20 9 март 20.00 40 40 10 апрель 20 10 11 май 30.00 50 50 12 июнь 20 20 13 14 январь 694.0 тыс. Плата за аренду 400 тыс. 15 февраль - тыс. 200 тыс. 16 март 140.0 тыс. 200 тыс. 17 апрель - тыс. 18 май 210.0 тыс. 19 июнь - тыс. 20 С мин.= 1 044.0 Целевая функция: Рис. 47

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

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

Перечислим сначала все требующиеся установки Поиска решения.

Целевая ячейка - С20, суммарные затраты.

Цель - минимум расходов.

Изменяемые ячейки - С7:Н12. Можно, конечно, выделить шесть диапазонов ячеек: С7:Н7, С8:08, С9:Б9, С10:Е10, С11:Б11 и С12 (для этого при указании ячеек в Поиске решения надо удерживать нажатой клавишу СТКЬ), но это необязательно. Во-первых, экономия переменных тут не требуется. А во- вторых, Поиск решения и сам, без дополнительных ограничений оставит лишние ячейки нулевыми. Ведь при подсчете снятой площади (ячейки 17:Л2) лишние ячейки не используются, зато они учитываются при расчете оплаты (ячейки С14:С19). Так что при минимизации расходов лишние переменные автоматически обнулятся.

Кроме обычных ограничений линейности модели и не отрицательности переменных (вкладка Параметры) нужно добавить только два групповых ограничения. 1. Суммарные арендуемые площади не меньше, чем ежемесячные потребности - 17:Л2>=К7:К12. 2.

Суммарные затраты в первые три месяца не должны превышать 400, 200 и 200 тыс. соответственно - С14:С16<=Н14:Н16.

Все, можно делать расчет.

Результат оптимизации не слишком нас удивил (Рис. 48). A B C D E F G H I J K 5 Возможный план Переменные: сколько тыс. м2 арендовать и на сколько месяцев 6 аренды в: на 1 на 2 на 3 на 4 на 5 на 6 Ограничения: 7 январь 22.15 0.00 0.00 0.00 0.00 7.85 30 30 8 февраль 0.00 10.00 0.00 0.00 2.15 0.00 20 20 9 март 20.00 0.00 0.00 0.00 0.00 0.00 40 40 10 апрель 0.00 0.00 0.00 0.00 0.00 0.00 10 10 11 май 30.00 10.00 0.00 0.00 0.00 0.00 50 50 12 июнь 0.00 0.00 0.00 0.00 0.00 0.00 20 20 13 14 январь 400.0 тыс. Плата за аренду 400 тыс. 15 февраль 187.1 тыс. 200 тыс. 16 март 140.0 тыс. 200 тыс. 17 апрель - тыс. 18 май 338.0 тыс. 19 июнь - тыс. 20 С мин.= 1 065.1 Целевая функция: Рис. 48

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

Здесь кстати проверить, каковы будут издержки, если не учитывать лимит средств. Для этого можно убрать соответствующее ограничение из Поиска решения, а лучше просто заменить верхние границы расходов большими числами (Рис. 49) A B C D E F G H I J K 6 аренды в: на 1 на 2 на 3 на 4 на 5 на 6 Ограничения: 7 январь 10.00 0.00 0.00 0.00 0.00 20.00 30 30 8 февраль 0.00 0.00 0.00 0.00 0.00 0.00 20 20 9 март 20.00 0.00 0.00 0.00 0.00 0.00 40 40 10 апрель 0.00 0.00 0.00 0.00 0.00 0.00 20 10 11 май 30.00 0.00 0.00 0.00 0.00 0.00 50 50 12 июнь 0.00 0.00 0.00 0.00 0.00 0.00 20 20 13 14 январь 694.0 тыс. Плата за аренду 1 000 тыс. 15 февраль - тыс. 1 000 тыс. 16 март 140.0 тыс. 1 000 тыс. 17 апрель - тыс. 18 май 210.0 тыс. 19 июнь - тыс. 20 С мин.= 1 044.0 Целевая функция: Как вы можете убедиться, этот план мы нашли раньше (Рис. 47) и отметили его, как очень хороший, но не укладывающийся в лимит расходов. Видимо, именно этот план имеет смысл реализовывать за счет кредита.

Давайте рассчитаем финансовые потоки при взятом кредите (Рис. 50).

Понятно, что нам необходимо взять 294 тыс. (694-400). Тогда в январе долг составит 294 тыс. и за месяц набежит 14.7 тыс. долларов по процентам (5%*294).

В феврале у нас расходов нет, но зато есть 200 тыс., которые мы можем направить на погашение кредита. Т.о. наш долг в феврале уменьшится до 108.7 тыс. (294+14.7-200). Однако на эту сумму снова будут начислены проценты и она А В С ь Е Г & Н I 1 13 Долг Проценты 14 январь 694.0 тыс. Плата

за

аренду 294.0 14.7 1 000 тыс. 15 февраль - тыс. 108.7 5.4 1 000 тыс. 16 март 140.0 тыс. 54.1 2.7 1 000 тыс. 17 апрель - тыс. - 18 май 210.0 тыс. 19 июнь - тыс. 20 С мин.= 1 066.8 Целевая функция: 22.8 Рис. 50

В марте наши расходы на аренду составляют 140 тыс. при лимите 200 тыс. Таким образом, 60 тыс. мы можем направить на погашение кредита. После этого мы останемся должны 54.1 тыс. (108.7+5.4-60).

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

Итого по взятому кредиту нужно выплатить 22.8 тыс. Эту сумму нужно добавить к расходам по найденному оптимальному плану - 1044 тыс., что в итоге даст 1066.8 тыс. долларов. Это, к сожалению, чуть хуже найденного ранее оптимального плана (Рис. 48), при котором мы без всяких проблем укладываемся в лимиты.

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

Разумеется, можно.

Но задачу придется несколько усложнить. Добавим к задаче еще три переменных: сколько денег занять в январе и сколько долга оставить в феврале и марте. Очевидно, что, так или иначе, в апреле мы погасим все долги. Мы добавили новые переменные в ячейки Е14:Е16 (Рис. 51). В ячейках 014:016 на сумму оставшегося долга начисляются проценты. А В С ь Е ? б Н I 1 К 13 Долг Проценты 14 январь - тыс. 168 =С14-Н14 =Е14*$К$14 400 тыс. 5% 15 февраль - тыс. 0 =П4+014-

Н15+С15 =Е15*$К$14 200 тыс. 16 март - тыс. 0 =П5+015-

Н16+С16 =Е16*$К$14 200 тыс. 17 апрель - тыс. 18 май - тыс. 19 июнь - тыс. 20 С мин.= =СУММ(С14:С19)+020 =СУММ(014:016) Рис. 51

В ячейках Е14:Б16 подсчитывается размер кредита (Р14), а затем и остаток долга. Три переменных нам нужны для того, чтобы не оперировать отрицательными значениями кредита и долга. В установках Поиска решения мы потребуем, чтобы ячейки Е14:Е16 были больше, чем Е14:Б16. При этом по условию на переменные они еще и больше нуля. Таким образом, если долг по кредиту положителен, соответствующая переменная будет равна ему, а если отрицателен (кредит погашен), переменная будет равна нулю.

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

Кроме сделанных исправлений уберем из списка ограничений ограничение на расходы в январе, феврале и марте. На следующем рисунке приведен результат оптимизации (Рис. 52). А В С ь Е ? б Н I 1 К 5 Возможный план Переменные: сколько тыс. м арендовать и на сколько месяцев 6 аренды в: на 1 на 2 на 3 на 4 на 5 на 6 Ограничения: 7 январь 10.00 0.00 10.00 0.00 0.00 10.00 30 30 8 февраль 0.00 0.00 0.00 0.00 0.00 0.00 20 20 9 март 20.00 0.00 0.00 0.00 0.00 0.00 40 40 10 апрель 0.00 0.00 0.00 0.00 0.00 0.00 10 10 11 май 30.00 10.00 0.00 0.00 0.00 0.00 50 50 12 июнь 0.00 0.00 0.00 0.00 0.00 0.00 20 20 13 Долг Проценты 14 январь 568.0 тыс. 168 168.0 8.4 400 тыс. 5% 15 февраль - тыс. 0 -23.6 0.0 200 тыс. 16 март 140.0 тыс. 0 -83.6 0.0 200 тыс. 17 апрель - тыс. 18 май 338.0 тыс. 19 июнь - тыс. 20 С мин.= 1 054.4 8.4 Рис. 52

Как мы видим, кредит предусмотрен. Оказывается, как это часто бывает, невыгоден не сам кредит - невыгоден слишком большой кредит! Если же взять только 168 тыс. (соответствующим образом изменив план аренды, конечно), расходы удается уменьшить примерно на 12 тыс. 1.П-7. Большой портфель

Некий бизнесмен, удалясь от дел, решает вложить часть своих накоплений в размере $1 млн. в акции известных компаний. Его помощник собрал данные о доходности 15 компаний за последние 11 лет. Эти данные приведены в таблице. Компания Доход по акциям компании, % ‘9G ‘91 ‘92 ‘93 ‘94 ‘95 ‘96 ‘9l ‘98 ‘99 ‘GG APPLE 13 36 13 -46 15 4 -33 -29 92 2G2 -6l BOEING 1G G -22 8 19 63 33 11 -25 4 61 BP AMOCO 2G -12 -28 4G 35 3G 46 23 14 4G -22 DEBEERS -1 68 -59 64 11 33 9 -29 -26 83 2 DOW CHEM -24 14 15 13 13 16 22 22 G 3G -15 DU PONT 1 3G 12 1 14 32 46 31 -4 6 -2l EXXON 8 16 1 5 -4 28 22 29 23 l 14 FIAT -39 -16 -23 24 62 -1l -5 16 4 -8 -1G FORD -36 -11 l5 4l -14 l 13 36 31 -13 -15 GE -12 21 25 2G -l 5G 5G 43 23 48 14 G. MOTORS -l -11 9 68 -28 35 21 1G 2l 25 -28 INTEL -3 11 l4 l2 G 95 1G8 28 41 33 -1G LOCKHEED -21 45 1l 35 -2 l6 22 8 9 -62 56 MICROSOFT 58 1G6 38 -12 54 38 83 82 8G 44 -39 PEPSICO 34 18 33 -2 -12 5l 9 26 9 -16 23 Бизнесмен желает обеспечить доход не менее 18% в год при наименьшем риске. Он слышал, что портфель с наименьшим риском следует формировать по методу Марковица.

Суть этого подхода состоит в том, что дисперсия доходности (т.е. риск) портфеля из двух, например, видов акций, может быть меньше, чем дисперсия любой из этих акций, в случае, когда доходность по акциям меняется в противофазе. Т.е. в то время, когда доходность по одной из акций падает, по другой она обычно растет. Это видно из стандартной формулы для расчета дисперсии суммы двух случайных величин. Если в первую акцию (дисперсия о12) вложено р % денег, а во вторую (дисперсия а22) Я % денег, то дисперсию портфеля можно рассчитать по формуле:

2 2 2 2 2 г\

Т портфеля — Р ^ ^ Т2 2Р\2

рохцо2

В этой формуле через р12 обозначен коэффициент корреляции между доходностями двух акций. Дисперсия такого пакета будет меньше наименьшей из двух акций, если только коэффициент корреляции не слишком близок к единице и если распределение средств по акциям не слишком ассиметрично. Разумеется, наиболее сильно дисперсия уменьшается, если коэффициент корреляции отрицателен. Увеличение числа акций в пакете снижает его дисперсию еще больше. Этот эффект известен в финансах как диверсификация портфеля.

N N

Для N видов акций эта формула имеет вид 011ддбйё^ — II ** СОУ(Я1 , ),

2—1 ]—1

где СОУ(Я2 , Я}) - ковариации доходности для всех пар видов акций, а х2 - доли капитала, вложенные в каждый вид акций. a.

Постройте таблицу Excel, позволяющую рассчитать риск портфеля и его средний доход. Для расчета взаимных и собственных дисперсий различных акций используйте функцию Excel =КОВАР( ). b.

Каковы риск (корень из дисперсии портфеля) и ожидаемый доход при вложении одинаковой суммы во все акции? c.

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

Каков будет доход портфеля, если добиваться наименьшего возможного риска? Как возрастет риск, если потребовать не менее 25% дохода?

Решение задачи.

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

Разумеется, в реальных условиях имело бы смысл выбирать не из десятка видов акций, а из тысяч, по крайней мере. И в этом случае стандартная надстройка к Excel не смогла бы помочь, так как допускает использование не более 200 переменных. Однако, кроме стандартного Поиска решения существует продвинутая программа под названием Premium Solver. Эту программу, также оформленную как надстройка к Excel с очень похожим интерфейсом, можно найти на сайте компании-создателя этого инструмента FrontLine System www.solver.com. Собственно, стандартная надстройка к Excel лицензирована компанией Майкрософт у этой же компании. Надстройку Premium Solver можно скачать бесплатно и пользоваться ею в течение двухнедельного пробного срока.

Главный модуль надстройки позволяет решать задачи с тысячами переменных и ограничений. Кроме этого, в коммерческой версии Поиска решения используется более совершенный алгоритм решения задач. Задачи, квадратичные по переменным, решаются одним модулем с задачами линейной оптимизации (Standard LP/Quadratic), в то время как все остальные нелинейные задачи решаются с помощью другого модуля - GRG Nonlinear Solver - менее эффективными по скорости и результатам методами.

Для решения задачи введем на страницу MS Excel заданную таблицу доходностей по годам (Рис. 53). Для удобства дальнейшей работы исходная таблица повернута (транспонирована). В строке B14:P14 с помощью функции Excel =СРЗНАЧ( ) сосчитана средняя доходность каждой акции за 11 лет в процентах. Эти данные необходимы для расчета ожидаемой доходности. Фактически мы при этом полагаем, что средняя доходность по акциям каждой компании не изменится в ближайшем будущем. Так как ожидаемая доходность - величина случайная, то мы можем утверждать, что для следующего года ожидаемую доходность можно рассчитать как случайную величину с нормальным распределением, с математическим ожиданием, равным среднему значению, и

стандартным отклонением, равным стандартному отклонению, рассчитанному по прошлым значениям доходности.

Чтобы сформировать портфель акций нужно решить, какую часть денег потратить на покупку пакетов каждой из акций. Если мы решим этот вопрос, то ожидаемая доходность портфеля в целом будет равна сумме произведений долей акций в портфеле на их доходность. Таким образом, максимально возможная доходность портфеля акций равна доходности самой прибыльной из акций (в нашем случае МБ - 48%), а минимально возможная доходность портфеля - доходности самой непривлекательной акции (в данном случае Б1). В этих крайних случаях портфель акций будет содержать акции только одной компании.

В этой задаче, поэтому, не имело бы смысла максимизировать доходность портфеля - она и так известна. Наша задача - составить портфель акций так, чтобы при заданной средней доходности портфеля ее стандартное отклонение для A B C D E F G H I J K L M N O P 1 Доход по акциям компании, % 2 AP БО BP DB DO DP EX FI FO GE GM IN LM MS PEP 3 1990 13 10 20 -1 -24 1 8 -39 -36 -12 -7 -3 -21 58 34 4 1991 36 0 -12 68 14 30 16 -16 -11 21 -11 11 45 106 18 5 1992 13 -22 -28 -59 15 12 1 -23 75 25 9 74 17 38 33 6 1993 -46 8 40 64 13 1 5 24 47 20 68 72 35 -12 -2 7 1994 15 19 35 11 13 14 -4 62 -14 -7 -28 0 -2 54 -12 8 1995 4 63 30 33 16 32 28 -17 7 50 35 95 76 38 57 9 1996 -33 33 46 9 22 46 22 -5 13 50 21 108 22 83 9 10 1997 -29 11 23 -29 22 31 29 16 36 43 10 28 8 82 26 11 1998 92 -25 14 -26 0 -4 23 4 31 23 27 41 9 80 9 12 1999 202 4 40 83 30 6 7 -8 -13 48 25 33 -62 44 -16 13 2000 -67 61 -22 2 -15 -27 14 -10 -15 14 -28 -10 56 -39 23 14 Средняя

доходность,

% =СРЗНАЧ(Б3:Б13) 14 10 13 14 -1 11 25 11 41 17 48 16 Рис. 53

Продолжим построение таблицы и для этого добавим в нее часть, позволяющую рассчитывать дисперсии доходности для каждой из акций и их взаимные дисперсии (так называемые ковариации). Чтобы подсчитать ковариации доходностей всех пар для 15 акций нужно, конечно, иметь таблицу размером 15х15 ячеек. Для удобства добавим вертикальный столбец с названиями компаний (A16:A30) (Рис. 54). В каждой из 225 ячеек должно содержаться значение ковариации доходностей соответствующей пары компаний. Скажем в ячейке B17, соответствующей паре компаний Apple-Boeing (столбец - строка), должна быть формула =КОВАР($С$3:$С$13;В$3:В$13), где столбец $C$3:$C$13 показывает доходность акций Boeing, а столбец В$3:В$13 - доходность акций Apple. Так как эту формулу нужно протягивать, то адреса ячеек частично фиксированы. При протягивании формулы вправо должны вычисляться ковариации доходностей всех других компаний с доходностью Boeing, поэтому столбец полностью фиксирован. Мы не будем отдельно вычислять дисперсию доходности Boeing, так как выражение вида =КОВАР($С$3:$С$13; С$3:С$13) и так вычисляет эту дисперсию.

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

Поэтому придется сначала протянуть формулу вверх и вниз на оставшиеся компании, потом скорректировать ссылки на столбец доходности для каждой компании, а после этого протягивать полученные формулы вправо. A B C D E F G H I J K L M N O P 15 Взаимная дисперсия (ковариация) 16 APPLE =КОВАР($B$3:$B$13;B$3:B$13) -106 -208 -423 280 223 -277 -1773 968 -599 17 BOEING =КОВАР($C$3:$C$13;B$3:B$13) 86 -13 -377 96 -119 60 501 -484 184 18 BP =КОВАР($D$3:$D$13;B$3:B$13) 27 270 -132 131 334 327 -301 165 -208 19 DEBEERS 999 =КОВАР($E$3:$E$13;C$3:C$13) 76 -599 142 313 -35 -98 -150 -350 20 DOW CH 347 -54 149 193 242 199 18 135 194 232 174 327 -70 202 -102 21 DU PONT -73 26 184 61 199 390 82 16 89 213 98 425 86 558 71 22 EXXON -106 86 27 -46 18 82 112 -71 20 134 66 126 152 144 106 23 FIAT -208 -13 270 76 135 16 -71 678 92 -86 -7 -114 -62 -53 -330 24 FORD -423 -377 -132 -599 194 89 20 92 1001 231 466 736 233 -67 70 25 GE 280 96 131 142 232 213 134 -86 231 424 297 552 93 140 49 26 GM 5095 -119 334 313 174 98 66 -7 466 297 748 783 50 -106 -24 27 INTEL -277 60 327 -35 327 425 126 -114 736 552 783 1528 404 111 165 28 LM -1773 501 -301 -98 -70 86 152 -62 233 93 50 404 1302 -364 430 29 MS 968 -484 165 -150 202 558 144 -53 -67 140 -106 111 -364 1663 2 30 PEPSICO -599 184 -208 -350 -102 71 106 -330 70 49 -24 165 430 2 425 31 32 Инвестици 0.0% 0.0% 0.0% 50% 0.0% 0.0% 0.0% 5.2% 8.2% 0.0% 0.0% 0.0% 0.0% 4.0% 33% 33 =СУММ( B AP BO BP DB DO DP EX FI FO GE GM IN LM MS PEP 34 =B32*B14/100 0.07 0 0 0 -0 0.01 0 0 0 0 0.02 0.05 35 Вариация =B32*СУММПРОИЗВ($B$32 :$P$32;B16:P16) =J32*СУММПРОИЗВ($B$32:$P$32;B24: 36 Целевая функция Мин. допустимый средний доход 37 =СУММ(В35 :P35) =CyMMnPOroB(B32:P32;B14:P14)/100 38 =A37Л0.5 15% Рис. 54

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

15 15

^ портфеля =ее хгх1Ковар(Д.,), где Ковар(Д.,) - рассчитанные нами

г=1 1 =1

ковариации доходности для всех пар компаний.

Ранее мы уже выяснили, что доли капитала, потраченные на покупку каждого из пакетов акций, должны быть переменными задачи. Выделим строку В32:Р32 под такие переменные. Так как сумма всех переменных х1, х2, ... х15 - должна равняться единице или 100% капитала (ячейка А33), то при постановке задачи потребуем, чтобы А33=1.

Для расчета дисперсии портфеля удобно переписать формулу для Ъпортфеля

б б В портфеля = Е хг тЕ х1 КоваР(Кг , ) [ Ч

в более удобном для расчетов виде “Т 11=1 I . Часть

формулы в фигурных скобках - это сумма произведений долей на

соответствующие ковариации, значит ее можно записать с помощью функции =СУММПРОИЗВ( ). Тогда для Арр1е, например, можно вычислить значение формулы

выражения

Х1і

с

помощью

xj Ковар(^, R і);

=Б32*СУММПРОИЗВ($Б$32:$Р$32;Б16:Р16). Запишем эту формулу в ячейку B35. К сожалению эту формулу так же неудобно протягивать. Поэтому после того, как вы все же протянете ее вправо до конца, исправьте в ней то, что необходимо. Для ориентира в ячейке J35 приведена правильная формула.

Суммирование всех ячеек строки Б35:Р35 соответствует первому символу суммы в формуле для Бпортфеля. Таким образом в ячейке A37 (Рис. 54) мы вычисляем ту самую дисперсию портфеля, которая нам необходима для решения задачи. Это и есть целевая ячейка.

Так как для сравнения удобнее использовать стандартное отклонение, вычислим в ячейке A38 корень из дисперсии (=КОРЕНЬ(А37)).

Мы уже обсуждали здесь, как вычислить ожидаемый доход портфеля. Для этого запишем в ячейке I37 формулу =СУММПРОИЗВ(Б32:Р32;Б14:Р14). Вычисляемую тут доходность портфеля при минимизации следует удерживать на уровне не ниже заданного. Зададим минимально допустимый доход в ячейке I38.

Теперь можно ставить задачу надстройке Поиск решения. Пройдемся еще раз по необходимым установкам. Целевая функция в ячейке А37, цель - поиск минимума функции. Изменяемые ячейки Б32:Р32. Вид модуля для решения задачи - Standard ЬР/Quadratic (для продвинутого Solver^) . Опции - подразумеваются неотрицательные значения переменных (Assume Non-Negative) и не следует отмечать, что задача линейная, если используется встроенный «Поиск решения» . Ограничений всего два - сумма долей капитала, вложенных во все пакеты, равна 100% (А33=1) и средний ожидаемый доход должен быть не менее заданного I37>=I38. A B C D E F G H I J K L M N O P 32 Инвестиция 0.0% 0.0% 0.0% 50.4% 0.0% 0.0% 0.0% 0.0% 8.5% 0.0% 0.0% 0.0% 0.0% 10.2% 30.9% 33 100.00% АР БО БР ББ DO БР EX FI FO GE GM IN LM MS РЕР 34 0 0 0 0.071 0 0 0 0 0.009 0 0 0 0 0.049 0.05 35 Вариация 0 0 0 51.89 0 0 0 0 -17.2 0 0 0 0 9.15 -12 36 Целевая функция Мин. допустимый средний доход 37 31.83 18% 38 5.64 18% Рис. 55

Оказывается, что деньги будут вложены в 4 пакета акций. При этом ожидается доход 18%, а риск портфеля составит 5.64%. Учитывая, что речь идет о нормальном распределении для такой случайной величины, как доход, можно сказать, что с вероятностью 95% реальная величина дохода составит от ~7% (18%-1.96*5.64%) до ~29% (18%+1.96*5.64%).

Что изменится, если мы попытаемся составить более доходный портфель акций? Зададим минимальный доход на уровне 25% и снова найдем минимум дисперсии. А В С 0 Е р б н I J К Ь м N о Р 32 Инвестиция 0.0% 19.2% 0.0% 25.2% 0.0% 0.0% 0.0% 0.0% 8.6% 0.0% 0.0% 7.4% 0.0% 25.6% 14.0% 33 100.00% АР ВО ВР БВ БО БР ЕХ ш БО ОЕ ОМ Ш ЬМ МЯ РЕР 34 0 0.0283 0 0.036 0 0 0 0 0.009 0 0 0.03 0 0.124 0.023 35 Вариация 0 15.922 0 29.31 0 0 0 0 -7.79 0 0 16.88 0 76.39 3.547 36 Целевая функция Мин. допустимый средний доход 37 134.26 25% 38 11.59 25% Рис. 56

В этом случае получается, что наименьшее стандартное отклонение портфеля составляет 11.6%. Поэтому с вероятностью 95% реальная величина дохода составит от 2.3% (25%-1.96*11.6%) до 47.7% (25%+1.96*11.6%). С точки зрения минимальной прибыли разницы практически нет, но средняя прибыль существенно выше. Так что более правильным будет выбрать второй портфель. В нем, как вы видите, содержится 6 пакетов акций.

Если у вас есть настроение, попробуйте определить при каком уровне дохода нижняя граница 95%-ного доверительного интервала станет отрицательной.

Разумеется, сами по себе полученные числа мало что значат. Все дело в том, на каких условиях вы готовы вложить капитал. Если, например, вы хотите, чтобы нижняя граница не опускалась ниже 8%, то из предложенных акций вообще невозможно составить нужный пакет. Придется расширить область поиска. Убедиться в этом можно заменив условие на минимальный доход условием на нижнюю границу доходности (например 137-1.96*Б38/100>=8%).

Если вообще снять условие на доход и оставить только требование А33=1, то мы найдем минимально возможный риск для портфеля, состоящего из предложенных акций. Он равен 3.78%, как несложно убедиться.

Мы пропустили вопрос о равном распределении денег по всем пакетам акций. Давайте запишем в ячейке В32 выражение =1/15 и протянем его на все остальные переменные. Получим следующий результат (Рис. 57) А В С 0 Е р б н I т К Ь м N о Р 32 Инвестиция 6.7% 6.7% 6.7% 6.7% 6.7% 6.7% 6.7% 6.7% 6.7% 6.7% 6.7% 6.7% 6.7% 6.7% 6.7% 33 100.00% АР ВО ВР БВ БО БР ЕХ Ш БО ОЕ ОМ Ш ЬМ МЯ РЕР 34 0.012 0.0098 0.011 0.009 0.006 0.009 0.009 -0 0.007 0.017 0.007 0.027 0.011 0.032 0.011 35 Вариация 17.21 0.5106 10.82 9.717 9.717 10.78 3.772 1.445 6.815 13.01 36.3 22.74 2.601 12.13 -0.49 36 Целевая функция Мин. допустимый средний доход 37 157.08 18% 38 12.53 18% Рис. 57

Как мы видим, средний ожидаемый доход в этом случае равен 18%, как при первом расчете портфеля! Если у вас возникло впечатление, что, может быть, никакой минимизации и не требовалось, попробуйте оценить доверительный интервал для полученного значения риска. Вы убедитесь, что нижняя граница интервала достигла значения -6.7%, при верхней границе + 42.4%. Таким образом, этот портфель акций является довольно рискованным вложением! Во всяком случае, если вам вдруг понадобится крупная сумма наличных и вы вынуждены будете продавать акции в неблагоприятной ситуации, то ваши инвестиции принесут немалый убыток.

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