Линейная оптимизация

Содержание курса. Литература 3 Темы зачетных домашних заданий 5 Тема 1 Линейная оптимизация 7 Кейс: На кондитерской фабрике «Алиса». 8 Продолжение: Жаль…, ведь мы все так любим Батончик! 22 Продолжение 2: Рыночные ограничения. 26 Продолжение 3: Что если? 28 Продолжение 4: Факультативное. 32 Кейс «Инвестиционная компания» 42 Расчет финансовых характеристик. Памятка. 44 Самостоятельный разбор материала. 65 Мини-кейс: Компания «Холод» - оптовая торговля замороженными овощами 65 Задачи транспортного типа и задачи о назначениях 72 Мини-кейс: Формирование команд. Для самостоятельного разбора. 73 Мини-кейс: Ремонт автодорог. Для самостоятельного разбора. 75 Домашнее задание к лекции №7 77 Мини-кейс: Лов рыбы 78 Домашнее задание к лекции №8 81 Литература Основная литература. 1. Lawrence J.A., B.A. Pasternack, Applied Management Science (Computer Integrated Approach for Decision Making), J Willey&Sons, 2002 2. Ханк Д. Э., Уичерн Д.У., Райтс А. Дж., Бизнес-прогнозирование, 7-е издание: Пер. с англ. – М.: ИД «Вильямс», 2015. – 656 с. 3. Зайцев М.Г., Варюхин С.Е. Методы оптимизации управления и принятия решений: примеры, задачи, кейсы. - 3-е изд. М.: Дело, 2017. - 640 с. 4. Зайцев М.Г. Методы оптимизации управления для менеджеров: Компьютерно-ориентированный подход. М.: Дело, 2017. – 304 с. 5. Томас Р., Количественные методы анализа хозяйственной деятельности, «Дело и Сервис», Москва 1999. Дополнительная литература. 6. Доугерти К., Введение в эконометрику: Учебник. 3-е изд. — М.: ИНФРА-М, 2009. — XIV, 465 с. — (Университетский учебник). 7. Таха, Х. А., Исследование операций, 10-е изд.= М.: ИД ‘‘Вильямс’’, 2018. - 1056 с. 8. Чейз Р.Б., Эквилайн Н.Дж., Якобс Р.Ф., Производственный и операционный менеджмент, ИД “Вильямс”, Москва - Санкт-Петербург - Киев, 2017. 9. Кремер Н. Ш., Путко Б. А., Тришин И. М., Фридман М. Н., Исследование операций в экономике, Юрайт, Москва, 2012 10. Фомин Г.П., Математические методы и модели в коммерческой деятельности, “Финансы и Статистика”, Москва, 2005. 11. Красс М.С., Чупрынов Б.П., Математика для экономистов, Спб., «Питер», 2005. - 454 с. 12. Макарова Н.В., Трофимец В.Я. Статистика в Excel: Учеб. Пособие- М.: Финансы и статистика, 2003.- 386 с. Интернет источники. 13. Рабочий сайт автора курса http://www.hcxl.net 14. http://www.informs.org - сайт Institute for Operations Research in the Management Science, посвященный практическим приложениям в управлении количественных методов. Сайты содержащие обширную бесплатную экономическую статистику и аналитику. 15. http://www.gks.ru, http://www.fedstat.ru - Российская официальная статистика 16. https://www.census.gov - Официальная статистика США 17. http://www.imf.org/external/index.htm - МВФ 18. http://unstats.un.org/unsd/databases.htm - Статистика ООН 19. https://www.destatis.de/EN/Homepage.html - Статбюро Германии 20. http://sophist.hse.ru/rstat/ - Статистика от ВШЭ 21. http://finance.yahoo.com - Финансовая статистика портала Yahoo.com 22. http://wsj.com - Известнейший финансовый журнал и портал 23. http://valueline.com - Аналитическая компания, предоставляющая и бесплатные данные 24. https://ru.investing.com - Аналитическая компания, предоставляющая и бесплатные данные 25. http://www.kitco.com/charts/historicalgold.html - Цена золота и других драгметаллов 26. http://www.indexmundi.com/commodities/?commodity=gold&months=360 - Золото и драгметаллы 27. https://www.eia.gov/totalenergy/data/monthly/index.php#electricity - Статистика энергетики США 28. damodaran.com - Финансовая статистика и аналитика от А. Дамодарана 29. https://www.ofx.com/en-au/forex-news/historical-exchange-rates/ - Исторические данные по курсам валют 30. Nyse.com, Nasdaq.com, www.londonstockexchange.com - сайты крупнейших фондовых бирж NYSE, Nasdaq и LSE Темы зачетных домашних заданий Домашнее задание содержит 3 задачи. 1. Построение прогноза по представленному временному ряду. 2. Задача на поиск оптимального решения. 3. Задача на статистическое моделирование. Задания для каждого студента персонально будут вывешены на рабочем сайте преподавателя – www.hcxl.net/kmpur2020.html. Нормальный срок сдачи работ – за одну неделю до экзамена. Задержка со сдачей работы влечет назначение до 10 штрафных баллов. Требования к содержанию отчетов и оформлению домашних заданий Отчет к каждой задаче домашних заданий должен содержать следующую информацию: 1. Полученное Вами задание (например, прикрепленный файл с заданием). 2. Листы MS Excel с решением или решениями, графиками и проч., подтверждающие ответы. Отчет к домашнему заданию (сводный документ MS Word или MS Excel) должен содержать четкие ответы на вопросы, поставленные в заданиях. Работу нужно прислать на электронную почту преподавателя. С Вашей стороны было бы разумно назвать файлы своей фамилией и внутри указать свою группу, программу и институт, в противном случае Ваша работа может затеряться среди сотен других. Большая просьба, для лучшей фильтрации ваших работ в моей почте в поле «Тема» (Subject) указать индекс – kmpur2020. Я обязательно отвечу, что получил вашу работу. Если такого ответа в течение 1-2 дней после отсылки вы не видите, может оказаться, что вы сделали что-то не так или какая-то система фильтрации спама отсеяла ваше письмо. В этом случае попробуйте повторить отправку через одного их ваших более удачливых :) однокурсников. Правильное, но «голое» решение без пояснений и надлежащего оформления задачи в Excel, может быть оценено ниже максимальной оценки на 2-5 баллов, ввиду невозможности установить, понимает ли студент, что у него получилось или нет. Одни ответы не оцениваются вообще. Работа с лекционными материалами Данный файл лекционных материалов не защищен и может использоваться слушателями по своему усмотрению. Делать свои замечания удобнее всего через вставку меню Вставка\блок Иллюстрации – Фигуры, Выноски. Выноски не смещают исходный текст и не сбивают форматирование документа. И в то же время хорошо заметны. Шаблоны файлов Excel внедрены в тело документа Word в начале каждого кейса и открываются двойным щелчком по значку внедренного файла. Сохраняется файл Excel автоматически вместе с документом Word или отдельно через меню «Сохранить как» в отдельный файл. Если данный документ не сохранен, то и изменения в файлах Excel не сохранятся. В конце каждого кейса имеются внедренные файлы Excel с уже выполненными заданиями. Они отличаются от создаваемых на лекциях, но чаще всего незначительно, ввиду различия обсуждений, возникших в ходе лекций. Они предназначены для самопроверки при самостоятельном разборе заданий. Тема 1 Линейная оптимизация Объем: 12 часов аудиторной работы и 8 часов самостоятельной работы. • Оптимизация как универсальный инструмент разработки операционной стратегии. Линейная оптимизация. Компьютерные средства линейной оптимизации. • Ключевые показатели эффективности как целевые функции оптимизации и ее ограничения. Ситуативный выбор ключевых показателей. • Выбор операционных стратегий в характерных проблемах логистики, закупок, управления финансами и человеческими ресурсами. • Методы разработки операционных стратегий развития компании. Отчет об устойчивости оптимального плана. Теневые цены. Чувствительность оптимального плана к изменениям условий. Методы улучшения оптимального решения. • Эффективный выбор линейки продуктов, товаров и услуг. Целочисленная оптимизация и бинарные переменные. • Совершенствование операций в логистике. Транспортные задачи и задачи о назначениях. Кейс: На кондитерской фабрике «Алиса». Кондитерская фабрика «Алиса» должна закрыть цех шоколадных конфет на 2-3 месяца для замены оборудования. Так как текущий средний дневной план производства конфет: «Ореховый звон» - 500 кг, «Райский вкус» - 1300 кг, «Золотой батончик» - 1200 кг, «Сибирская белочка» - 700 кг, «Поздний вечер» - 850 кг и «Первый фант» - 1250 кг, в сумме дает 5800 кг шоколадных конфет в сутки, то имеющихся запасов сырья по грубой оценке должно хватить примерно на 25 суток работы. Сырье Темный шоколад Светлый шоколад Сахар Карамель Орехи Себестоимость, руб/кг 1000 600 50 150 900 Запасы, кг 58 200 7 450 19 000 22 800 39 000 После обсуждения плана действий владелец решает: нового пищевого сырья не закупать, но имеющееся сырье, по возможности, израсходовать. Расчет по дневному плану и технологической карте: Продукты, расход основного сырья на 1 кг продукта, кг Сырье Ореховый звон Райский вкус Золотой батончик Сибирская белочка Поздний вечер Первый фант Темный шоколад 0,36 0,36 0,34 0,35 0,53 0,59 Светлый шоколад 0,09 0,07 0,04 0,03 0,00 0,11 Сахар 0,14 0,29 0,21 0,23 0,00 0,03 Карамель 0,09 0,21 0,10 0,12 0,33 0,27 Орехи 0,32 0,07 0,31 0,27 0,14 0,00 показывает, что израсходовть все сырье не получится. Директор по производству утверждает, что после полного исчерпания одного из видов сырья останется в сумме примерно 30,5 тонн прочих его видов. Такой объем остатков сырья приемлем, но не желателен и владелец решает перекроить текущий план, который, разумеется, соответствует спросу, но в ожидании двухмесячного простоя может быть значительно изменен. Владелец фабрики хотел бы перед закрытием выпустить продукцию, которая принесет максимальную суммарную прибыль и, скорее всего, израсходует максимальное количество запасов сырья: темного и светлого шоколада, сахара, карамели и орехов. В таблице для всех шести конфет приведены отпускные цены и получаемая при этом удельная валовая прибыль (за вычетом себестоимости сырья и операционных расходов). Цена и прибыль Ореховый звон Райский вкус Золотой батончик Сибирская белочка Поздний вечер Первый фант Отпускная цена, руб/кг 1020,0 765,0 900,0 895,0 945,0 1000,0 Удельная прибыль, руб/кг 257,5 214,0 191,5 214,5 199,5 262,0 Используя свой многолетний опыт и простейший расчет, директор по производству предлагает до закрытия выпустить по 20 тонн каждого вида конфет плюс еще 1,5 тонны для конфеты «Первый фант». При этом прибыль ожидается примерно на полтора миллиона рублей выше, чем в текущем плане, а остатки сырья в сумме уменьшатся на 5,5 тонн. (Для запуска подготовленного шаблона задачи кликните двойным щелчком внедренный файл Excel в начале кейса.) Задание 1. 2. Как вы думаете, почему в реальных условиях могли возникнуть излишки сырья в объеме около 20% от всех запасов, если сырье, по идее, закупается под текущий план производства? 3. Оцените корректность расчетов директора по производству, сравните с текущим планом производства. 4. Нельзя ли улучшить план директора по производству? Проверка плана директора по производству. Формулы и результаты расчета. Фактически достигнуто решение задачи-минимум – построена модель ситуации, позволяющая рассчитать результаты и потребности в ресурсах для любого плана производства. Проверьте наличие улучшений в сравнении с текущим планом. Попробуйте предложить лучший, чем у директора по производству, план. Проверка текущего плана производства. Формулировка математической модели для принятия решения и принятая терминология 1. Выбор факторов, количественно характеризующих систему (или состояние системы) с точки зрения проблемы, требующей решения. Это - параметры системы. 2. Выбор среди параметров системы количественных факторов, зависящих от субъекта, принимающего решение, и влияющих на результат работы системы. Это - переменные решения Xi. 3. Расчет ключевых показателей системы, количественно характеризующих результат работы системы и зависящих от переменных решения Xi, и выбор наиболее актуального, значимого среди них с вашей точки зрения (прибыль, доход, издержки…). Это - целевая функция. 4. Формулировка уравнений и неравенств, включающих ключевые показатели, переменные решения и параметры системы, ограничивающих возможные изменения переменных, устанавливающих необходимые связи между ними. Это - ограничения. Непосредственные задачи оптимизации Программа-минимум: Мгновенно вычислять значение целевой функции P и других ключевых показателей при любых интересующих значениях переменных решения (параметров управления) x1, x2, x3... (Вычисления типа “что, если”) Программа-минимум предусматривает, в частности, дополнительную проверку соответствия имеющейся модели операций и предоставленных данных. Если данные (или модель операций) не точны, то их не удастся корректно связать формулами. Программа-максимум: Быстро находить такие значения параметров управления (переменных) x1, x2, x3..., при которых целевая функция P оптимальна, т.е. достигает максимума или минимума (смотря, что интереснее) и все ограничения (требования) удовлетворены. (Задача оптимизации). Оптимизация позволяет найти план наиболее эффективного (в смысле выбранного ключевого показателя) использования имеющихся ресурсов: финансовых, человеческих, сырьевых, материальных, оборудования и проч. Плоды просвещения. Сын владельца фабрики – молодой BBA (Бакалавр делового администрирования) – случившийся при разговоре владельца с директором по производству, высокомерно замечает, что такие проблемы надо решать не на калькуляторе, а на ноутбуке методом линейной оптимизации. Отец, в первый раз заметивший «плоды просвещения», перемигнувшись со своим специалистом, просит сына сделать свой расчет и обещает ему 20% прибыли, полученный сверх объявленной опытным управленцем. Задание 2. a. Поставьте «Поиску решения» задачу линейной оптимизации для расчета максимальной возможной прибыли в рамках имеющихся запасов сырья. 5. Какова дополнительная прибыль в сравнении с планом директора по производству? Каков бонус консультанта? 6. Как изменились остатки сырья? Надстройка «Поиск решения» ( Solver ) и ее диалоговое окно. «Поиск решений является частью блока задач, который иногда называют анализ "что-если". Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки». «Процедуру поиска решения можно использовать для определения значения влияющей ячейки, которое соответствует экстремуму зависимой ячейки — например можно изменить объем планируемого бюджета рекламы и увидеть, как это повлияет на проектируемую сумму расходов». Встроенный инструмент Excel имеет ограничения. По числу переменных: Nv <=200 и по числу ограничений: Nc<=100. Получить подробные сведения об используемых алгоритмах оптимального поиска, другие сведения об инструменте и скачать более мощные (но платные) его версии можно на сайте разработчиков надстройки: http://www.solver.com. Пробные установки более мощных инструментов ограничены по времени (около 2 недель), но дают возможность получить реальный результат оптимизации для задач с большим числом переменных и ограничений. Имеются аналоги надстройки для различных инструментов, включая базы данных. Удобный бесплатный аналог Решатель включен в бесплатный аналог Excel – Calc – свободного бесплатного кроссплатформенного LibreOffice (https://www.libreoffice.org/). Решатель – эффективный инструмент, в некоторых отношениях дающий фору штатному Поиску решения. Решатель не имеет ограничений ни по числу переменных, ни по числу ограничений и зачастую выполняет целочисленную оптимизацию эффективнее Поиска решения из состава Excel. Дополнительные варианты инструментов оптимизации можно посмотреть на: www.maximalsoftware.com www.cplex.com Подключение надстройки «Поиск решения». Надстройка имеется во всех версиях Excel. Для ее подключения нужно: В Windows В Office 2010 – 2019, 365 вызвать меню Файл (верхний левый угол) → слева Параметры → слева Надстройки → снизу Управление: Надстройки Excel, кнопка Перейти. → В появившемся окне Надстройки отметить галочкой «Поиск решения», щелкнуть ОК. Надстройка появится в меню Данные: (справа) Анализ – Поиск решения. В Mac OS X В Microsoft Excel для Mac: вызвать верхнее меню Сервис\Надстройки Excel → В появившемся окне Надстройки отметить галочкой «Solver Add-In», щелкнуть ОК. Надстройка появится в меню Сервис: Поиск решения. В кроссплатформенном LibreOffice Calc Надстройка Решатель установлена по умолчанию и находится в меню Сервис: Решатель. Интерфейс надстройки «Поиск решения» Microsoft Office 2019 for Windows Microsoft Office 2019 for MacOS X Оптимизировать целевую функцию — Указывает целевую ячейку, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. Эта ячейка должна содержать формулу. До — выбор варианта оптимизации значения целевой ячейки (максимизация, минимизация или подбор заданного числа). Чтобы установить число, введите его в поле. Изменяя ячейки переменных — выбор ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанной в поле Оптимизировать целевую функцию. В соответствии с ограничениями — Отображает список граничных условий поставленной задачи. Добавить, Изменить, Удалить — Служат для отображения диалоговых окон Добавить ограничение, Изменить ограничение и для удаления выделенного ограничения. Выберите метод решения — здесь можно выбрать линейную или нелинейную задачу следует решать. Параметры — Служит для отображения диалогового окна Параметры поиска решения, в котором можно подобрать параметры оптимизации. Сбросить — Служит для полной очистки полей окна диалога. Постановка задачи оптимизации для надстройки «Поиск решения» ( Solver ). Оптимальное решение – результат расчета «Поиском решения». Оптимальное решение в целых числах – результат оптимизации. Историческая справка Канторович Леонид Витальевич (6 (19) января 1912, Санкт-Петербург — 7 апреля 1986, Москва). В 1926 году в возрасте четырнадцати лет поступил в Ленинградский университет. Окончил математический факультет (1930), учился в аспирантуре университета, c 1932 года преподаватель, в 1934 стал профессором, в 1935 году ему присвоена ученая степень доктора физико-математических наук без защиты диссертации. В 1938 году консультировал фанерный трест по проблеме эффективного использования лущильных станков. Канторович понял, что дело сводится к задаче максимизации линейной формы многих переменных при наличии большого числа ограничений в форме линейных равенств и неравенств. Он модифицировал метод разрешающих множителей Лагранжа для ее решения и понял, что к такого рода задачам сводится колоссальное количество проблем экономики. В 1939 году опубликовал работу «Математические методы организации и планирования производства», в которой описал задачи экономики, поддающиеся открытому им математическому методу и, тем самым, заложил основы линейного программирования. В 1949 году стал лауреатом Сталинской премии «за работы по функциональному анализу». 28 марта 1958 года избран членом-корреспондентом АН СССР (экономика и статистика). С 1958 года возглавляет кафедру вычислительной математики. Одновременно возглавлял отдел приближенных вычислений Математического института им. Стеклова Ленинградского отделения АН СССР. В середине 1948 года по распоряжению И. В. Сталина, расчетная группа Канторовича была подключена к разработке ядерного оружия. С 1960 года жил в Новосибирске, где создал и возглавил Математико-экономическое отделение Института математики СО АН СССР и кафедру вычислительной математики Новосибирского университета. 26 июня 1964 года избран академиком АН СССР (математика). За разработку метода линейного программирования и экономических моделей удостоен в 1965 году вместе с академиком В. С. Немчиновым и профессором В. В. Новожиловым Ленинской премии. С 1971 года работал в Москве, в Институте управления народным хозяйством Государственного комитета Совета Министров СССР по науке и технике. 1975 год — Нобелевская премия по экономике (совместно с Т. Купмансом «за вклад в теорию оптимального распределения ресурсов»). С 1976 работал во ВНИИ системных исследований Госплана СССР и АН СССР. Тьяллинг Купманс, Джордж Данциг, Леонид Канторович, 1975 год Джордж Бернард Данциг (англ. George Bernard Dantzig; 8 ноября 1914 — 13 мая 2005) — математик, который разработал симплексный алгоритм (симплекс-метод) и считается «отцом линейного программирования» (наряду с советским математиком Л. В. Канторовичем). Ему были присуждены: Национальная Медаль Науки (National Medal of Science) в 1975, Приз Джона фон Неймана (John von Neumann Theory Prize) в 1974. Он был членом Национальной Академии Наук (National Academy of Sciences), Национальной технической Академии (National Academy of Engineering), и американской Академии Искусств и Наук (American Academy of Arts and Sciences). Он получил степень бакалавра по математике и физике в Университете Мэриленд (University of Maryland) в 1936, степень магистра математики в Университете Мичиган (University of Michigan) и доктора философии в Беркли (UC Berkeley) в 1946. Отец Данцига, Тобиас Данциг, был российским (латвийским) математиком, который учился у Анри Пуанкаре (Henri Poincaré) в Париже, затем эмигрировал в Соединенные Штаты. На кондитерской фабрике «Алиса». Продолжение: Жаль…, ведь мы все так любим Батончик! После решения задачи об оптимальном плане производства для родной кондитерской фабрики, юноша (сын владельца фабрики) испытал двойственное чувство. С одной стороны, прибыль, соответствующая найденному им производственному плану, почти на 6,5 млн рублей больше, чем по плану Михалыча (директора по производству), т.е. он заработал почти 1 миллион 285 тысяч рублей. Это здорово! С другой стороны, почему компьютер отказался от выпуска «Батончика», можно сказать концептуальной (с его точки зрения) конфеты? Юноша был уверен, что «Золотой батончик» – один из лучших продуктов, который выпускает фабрика его отца. Если его не окажется на прилавках, может пострадать имидж фабрики. Ведь не только он сам, но и вся его тусовка очень любят эту конфету! Кроме того, он вспомнил, что на занятиях по количественным методам в менеджменте, преподаватель все время твердил об анализе полученного оптимального решения на чувствительность к изменениям: малые изменения величины запасов могут привести к радикальному изменению плана почти без изменения целевого показателя! А что, если каких-то запасов не хватит для его оптимального плана? Он не доберет прибыли! Может быть тогда более прибыльным станет иной план? Но какой?.. Задание 3. a. Попробуйте применить различные разумные требования к плану производства и оцените изменения плана и целевого показателя. Попробуйте прямо потребовать включения в план «Золотого батончика». b. Получите отчет об устойчивости (Sensitivity Report) для первоначального оптимального плана без дополнительных требований. Как надо изменить удельную прибыль для любимого продукта сына хозяина фабрики («Золотого батончика»), чтобы он вошел в оптимальный план (ответьте, не решая задачу, анализируя лишь отчет по устойчивости)? c. Как изменится прибыль, если выполнять новый оптимальный план, полученный при более высокой прибыльности «Золотого батончика», но оставить прибыльность конфеты на прежнем уровне? d. Включение в план производства (с помощью ограничения Хi >= 5 000-10 000) какой из отсутствующих в базовом плане конфет наиболее радикально скажется на общей прибыли? Подсчитайте по отчету об устойчивости ожидаемые потери прибыли при включении в план каждой из отсутствующих в нем конфет, а также всех трех продуктов сразу. Проверьте расчеты прямой оптимизацией с добавлением соответствующих ограничений. Комментарии к отчету по устойчивости MS Excel Переключитесь на лист книги Excel, содержащий задачу. Вызовите Поиск решения и заставьте его еще раз решить эту задачу. После нахождения оптимального решения выбрасывается окно “Результаты поиска решения”. Прежде чем нажать на клавишу OK, отметьте тип отчета - «Устойчивость». Excel добавит в рабочую книгу новый лист «Отчет об устойчивости 1». Переключитесь на вновь созданный лист отчета. Отчет Excel об устойчивости включает две таблицы: таблицу «Ячейки переменных» (сверху) и таблицу «Ограничения» (снизу). Внимание! Такой вид отчета можно получить только в линейной модели (симплекс-метод). Если вид отчета получается более коротким, проверьте модель в окне «Выберите метод решения» и установите «Поиск решения лин. задач симплекс-методом». Влияние изменений в коэффициентах целевой функции Таблица «Изменяемые ячейки». 1. Изменение коэффициентов целевой функции не изменяет оптимального плана (максимальное значение целевой функции при этом, конечно, меняется), пока они остаются в границах “Допустимое увеличение” и “Допустимое уменьшение” коэффициентов целевой функции. 2. При выходе значений коэффициентов за эти пределы решение скачком изменяется на другое решение, возможно отличающееся от прежнего очень сильно. Если переменная Xj > 0 (продукт входит в оптимальный план), то имеется как верхний так и нижний предел для изменения соответствующего коэффициента целевой функции, кроме случая, когда на переменную наложено прямое ограничение: Xj < a или Xj > b. Неограниченный верхний и нижний пределы обозначаются огромным числом 1Е+30 (единица с 30 нулями). Можно смело мысленно заменить это число на знак ∞, но для различных расчетов по верхнему и нижнему пределам удобнее иметь в соответствующих ячейках просто очень большое число. Если же Xj = 0, то: 1. При установке «оптимизировать до максимума» “Допустимое уменьшение” может быть как угодно велико - продукт все равно не войдет в оптимальный план. При этом верхний предел - “Допустимое увеличение”, показывает, насколько нужно увеличить соответствующий целевой коэффициент (прибыль по смыслу), чтобы продукт вошел в оптимальный план. 2. При установке «оптимизировать до минимума» “Допустимое увеличение” может быть как угодно велико - продукт все равно не войдет в оптимальный план. При этом нижний предел - “Допустимое уменьшение”, показывает, насколько нужно снизить соответствующий целевой коэффициент (издержку по смыслу), чтобы продукт вошел в оптимальный план. 3. При установке «оптимизировать до [конкретного числа]» и “Допустимое уменьшение”, и “Допустимое увеличение» теряют смысл. Величина противоположная допустимому увеличению (или уменьшению при минимизации) называется Приведенная (Нормированная) стоимость, и показывает, насколько нынешняя цена продукта ниже минимальной цены (или издержки выше максимальных), при которой продукт выгодно включить в оптимальный план. На кондитерской фабрике «Алиса». Продолжение 2: Рыночные ограничения. Включением в план «Золотого батончика» в объеме 5000 кг дело не завершилось. (см. рисунок) Оказалось, что продать около 70 тонн конфеты «Ореховый звон» невозможно за планируемый период. Максимум – 50 тонн, по утверждению директора по маркетингу. Задание 4. Найдите решение, устраивающее и отдел маркетинга, и молодого человека. Ограничение по «Золотому батончику» следует сохранить. Решение. Т.е. потери в бонусе удручающие. Но пренебрегать рыночными ограничениями неразумно, ведь план производства и расчет прибыли по нему получаются обоснованными только если эксперты признают соответствующий ему план продаж реалистичным. На кондитерской фабрике «Алиса». Продолжение 3: Что если? Получение хорошего оптимального плана, устраивающего все заинтересованные стороны, натолкнуло молодого человека на размышления о других возможностях повышения своей премии. Задание 5. Можно ли к имеющимся в последнем плане ограничениям добавить еще какое-то ограничение (или несколько ограничений) так, чтобы его бонус вырос? Попробуйте разные варианты. Объясните результат. Допустим, что у молодого человека имеется что-то около 50 000 рублей, на которые он может докупить любого сырья по тем же ценам и по секрету добавить к имеющимся запасам. Очевидно, что такое расширение ресурсов должно увеличить прибыль и, соответственно, его бонус. Задание 6. Какого сырья (или несколько видов сырья) докупать? Сколько докупать? Как сильно от этого возрастет прибыль? (Воспользуйтесь новым отчетом об устойчивости для последнего варианта плана.) Влияние изменения правых частей ограничений - bi (в нашем случае - запасов ресурсов). Таблица «Ограничения» содержит колонку «Теневая цена» (Shadow price). Теневые цены - Yi - показывают, как меняется целевая функция при не большом изменении количества ресурсов bi: P = Yi  * bi Эти оценки верны только в пределах постоянства теневой цены (при этом численные значения переменных решения Xj, конечно изменяются). Пределы изменения bi, в которых теневая цена остается одинаковой, также даны в таблице «Ограничения» («Допустимое увеличение» и «Допустимое уменьшение» количества ресурса). Причем, если ресурс используется полностью (дефицитный ресурс), существует как верхний, так и нижний предел. Если же ресурс используется не полностью, верхний предел устойчивости равен бесконечности (Excel пишет 1Е+30, что означает 10+30, для программы – это практическая бесконечность). Когда требуется оценить, на расширение какого из ресурсов (ограничений) следует направить финансовые ресурсы, следует рассчитать удельную добавку целевой функции на затраченный рубль, т.е. рассчитать отношение теневой цены к себестоимости добавки ресурса. Решение 6. Легко видеть, что отношение теневой цены к себестоимости сырья выше всего у сахара: 10,37. Его и имеет смысл добавлять в первую очередь. В соответствии с формулой ΔP = Yi  * Δbi можно и при Δbi = 1000 кг (1000 кг это 50000 руб/50 руб/кг) можно ожидать ΔP = 518,5 * 1000 = 518,5 тыс. руб. Или иначе ΔP = 10,37 * 50000 = 518,5 тыс. руб. На кондитерской фабрике «Алиса». Продолжение 4: Факультативное. Еще несколько практически значимых вопросов. Задание 7. В вопросе о до-закупке сырья выяснилось, что твердо рекомендовать вложение денег в ослабление какого-то из ограничений (расширение бутылочного горлышка) можно только когда увеличение не превышает границ постоянства теневой цены. А что делать, если денег существенно больше? Как тогда выяснить оптимальное направление инвестиций? Задание 8. В разборе кейса упоминалось, что в ходе оптимизации, при необходимости, можно получить и целочисленное решение проблемы (целочисленный план). Но с практической точки зрения интереснее не целое решение, а решение, кратное заданным числам, например величине упаковки или величине стандартной партии на производстве. Можно ли это сделать? Задание 9. Мы отмечали, что при разных решениях остатки склада сильно варьируют. При том, что сырье дорогостоящее, может оказаться, что прибыль в каком-то решении меньше, но стоимость сырья гораздо выше. Не значит ли это, что стоимость сырья следует тоже учитывать и добавить ее к целевой функции, предположив, что сырье можно продать? Это наверняка изменило бы наши выводы о лучшем решении! Решение 7. В сущности, проблему можно переформулировать так: сколько каждого вида сырья нужно закупить и сколько конфет разного вида произвести, чтобы максимизировать прибыль. В этом случае очевидно, что требуется добавить переменных в задачу. А именно, 5 переменных, по числу ресурсов, которые показывают, сколько каждого ресурса закупить. Ну и следует добавить закупленные ресурсы к запасам. В столбец L скопируем изначальные запасы из I4:I8. В М4:М8 запланируем новые переменные. Чтобы закупка не оказалась бесконечно большой, подсчитаем стоимость закупленного сырья в М9, а в М10 запишем предельную сумму, например 100 тыс. В ячейках I4:I8 вместо фиксированных значений запишем формулу =L4+M4 и протянем до I8. Теперь запасы из ограничений задачи будут меняться с закупкой. Ну и, учитывая канву задачи, пересчитаем бонус с учетом расходов на закупку M9: = A15-M9. Чтобы добавить в список переменных несвязанные диапазоны ячеек, уберем старые переменные, еще раз выделим C13:H13, зажмем кнопку CTRL и выделим M4:M8. Этот диапазон добавится через точку с запятой. Дополнительное ограничение, очевидно: M9<=M10. Все. Теперь можно оптимизировать закупки при любых доступных суммах М10. Причем, как несложно убедиться буквально с нескольких попыток, бонус при инвестициях в районе 1 млн руб. оказывается явно меньше, чем при инвестициях 50-100 тыс. Это наталкивает на идею поварьировать в качестве переменной и максимальную сумму инвестиций в ресурсы M10! Правда, просто добавить ячейку М10 к переменным недостаточно. Раз мы желаем максимального бонуса, то в качестве ключевого KPI выберем именно ячейку B15 – бонус с учетом расходов. На листе ничего изменять не нужно. В результате получается, что максимальный бонус 1 166 282 руб достигается при инвестициях в закупку около 255 тыс. На графике, построенном по данным максимизации без переменной М10, это выглядит так. Видно, что бонус сначала растет, а затем начинает уменьшаться. При инвестициях 2 млн руб бонус уходит в минус, кроме сахара закупается и светлый шоколад, но это уже мало интересно. Решение 8. Идея поиска планов, кратных каким-то заданным значениям, проста: давайте в качестве переменных выберем количество упаковок и именно его сделаем целым. Опыт проведем на базовом решении с ограничениями только на ресурсы, сделав копию страницы. После этого введем новые переменные в строке 17 и запишем туда какой-нибудь вариант плана. Например, всех конфет по 100 упаковок. В строке 15 зададим размер упаковок в килограммах. Вес упаковок задан произвольно, но так, чтобы были сильно отличающиеся варианты. После этого вместо старых переменных C13:H13 запишем формулы вида: =С15*С17, чтобы в этих ячейках по-прежнему фигурировали количества продукции в кг и остальные формулы не пришлось корректировать. Осталось вызвать «Поиск решения» и заменить переменные на С17:H17, а также добавить целочисленные ограничения С17:H17=целые. Решение не будет сильно отличаться от исходного, но план станет кратным заданным в 15-й строке числам. Решение 9. На самом деле попытка приплюсовать к прибыли деньги от продажи остатков сырья - заблуждение. Потому что прибыль вычисляется как выручка минус расходы. И для неиспользованного сырья, которое не с неба упало, должно быть так же. И тут уж очевидно, что продажа сырья в реальных условиях принесет скорее убыток и прибыль уменьшится (кроме особой ситуации, когда мы купили сырье, а цены на него взлетели). Но все-таки, можно ли как-то учесть стоимость остатков сырья? Можно. Но тогда нужно менять целевую функцию. Потому что полную стоимость сырья имеет смысл сопоставлять только с выручкой. Выручка в данной ситуации может быть и более разумным целевым показателем, поскольку деньги на закупку ресурсов уже потрачены и максимизация прибыли – это максимизация чисто формального показателя. В то время как выручка как раз показывает, сколько денег мы можем извлечь из уже имеющихся ресурсов. Подсчитать выручку не сложно, нужно план перемножить не на прибыль, а на отпускные цены (B13) =СУММПРОИЗВ(C9:H9;C13:H13). Если теперь в качестве целевой ячейки выбрать B13 и выполнить оптимизацию, выручка подрастет, а прибыль уменьшится. Правда, незначительно. И вот к этой выручке можно теперь добавить стоимость сырья. Сосчитаем в K11 стоимость сырья =СУММПРОИЗВ(K4:K8;B4:B8), а в K13 сумму выручки и стоимость сырья =K11+B13. Теперь можно выбрать в качестве целевой ячейки K13 и учесть явным образом стоимость остатков сырья. Но результат оптимизации получается довольно интересный. А именно: точно такой же, как при максимизации прибыли. Ожидаемо, если задуматься, правда? Итоговый файл кейса «На кондитерской фабрике «Алиса». Домашнее задание к лекции №5 Фирма «Фасад» производит двери для продажи местным строительным компаниям. Репутация фирмы позволяет ей продавать всю производимую продукцию. На фирме работает 10 рабочих в одну смену (8 рабочих часов), 5 дней в неделю, что дает 400 часов в неделю. Рабочее время поделено между двумя существенно различными технологическими процессами: собственно производством и конечной обработкой дверей. Из 400 рабочих часов в неделю 250 отведены под собственно производство и 150 под конечную обработку. «Фасад» производит 3 типа дверей: стандартные, полированные и резные. В таблице приведены временные затраты и прибыль от продажи одной двери каждого типа.       Время на производство (мин) Время на обработку (мин) Прибыль Стандартные 30 15 $ 45 Полированные 30 30 $ 90 Резные 60 30 $120 Задание. a. Сколько дверей различных типов нужно производить, чтобы максимизировать прибыль? b. На предстоящей неделе «Фасад» должен выполнить контракт на поставку 280 стандартных, 120 полированных и 100 резных дверей. Для выполнения заказа «Фасад» может закупить некоторое количество полуфабрикатов дверей у внешнего поставщика. Эти полуфабрикаты «Фасад» может использовать только для производства стандартных и полированных, но не резных дверей. При этом изготовление стандартной двери требует лишь 6 мин процесса обработки, а полированной – 30 мин обработки (процесс собственно производства для этих полуфабрикатов не требуется). Полученная таким образом стандартная дверь приносит $15 прибыли, а полированная - $50. Предполагая, что по-прежнему 250 часов в неделю отведено под производство и 150 под обработку, определите сколько и каких дверей «Фасад» должен произвести самостоятельно, и сколько полуфабрикатов закупить для изготовления стандартных и полированных дверей? Кейс «Инвестиционная компания» Инвестиционная компания «Карло и сыновья» рассматривает одиннадцать инвестиционных проектов, закодированных в таблице буквами греческого алфавита. Любой проект требует инвестиций сейчас и еще в течение от 1 до 3 лет (в таблице отрицательные значения финансовых потоков) и после определенного срока приносит доход (положительные или иначе свободные финансовые потоки). Горизонт планирования - 10 лет. Для 10-го года финансовые потоки включают не только, собственно, cash flow 10-го года, но и терминальную стоимость проекта. Финансовый поток (ФП) по годам, млрд. руб. Проект 1 2 3 4 5 6 7 8 9 10 Альфа -2,80 -1,90 1,16 1,19 1,23 1,27 1,31 1,34 1,39 1,43 8,40 Бета -0,90 -0,90 -0,60 -0,50 1,04 1,08 1,11 1,14 1,18 1,21 7,13 Гамма -1,00 -1,00 -0,95 0,00 0,87 0,90 0,92 0,95 0,98 1,01 5,94 Дельта -1,90 0,10 -1,50 1,00 1,16 1,19 1,23 1,27 1,31 1,34 7,92 Эпсилон -0,50 0,20 -1,30 0,60 0,46 0,48 0,49 0,51 0,52 0,54 3,17 Дзета -3,30 -4,05 -1,70 1,30 3,48 3,58 3,69 3,80 3,92 4,03 23,75 Эта -0,40 -0,40 -0,40 -0,30 0,50 0,58 0,60 0,62 0,63 0,65 3,84 Тета -0,70 -0,85 -1,70 -1,10 1,62 1,67 1,72 1,77 1,83 1,88 11,08 Иота -0,90 -1,45 -3,00 -2,50 2,90 2,99 3,08 3,17 3,26 3,36 19,79 Каппа -1,00 0,50 -3,20 1,20 0,99 1,02 1,05 1,08 1,11 1,14 6,73 Ламбда -0,60 -2,00 0,40 0,90 0,90 0,93 0,95 0,98 1,01 1,04 6,14 Компания желает вложить капитал в наиболее выгодные проекты. Ставка дисконтирования для отрасли - 23% годовых. В ближайшем будущем компания может инвестировать не более 9,5 млрд. руб. В следующем году - до 6,2 млрд. руб. и еще через год – 5,7 млрд. руб. Далее эта группа проектов может финансироваться только за счет внутренних ресурсов, т.е. суммарный номинальный финансовый поток должен быть положительным. Предполагается, что любой проект либо финансируется полностью, либо не финансируется совсем. * В кейсе предполагаются коэффициент инфляции 3% в год и скорость устойчивого роста 2%. Задание 1. a. Выберите проекты, которые следует финансировать. Все ли финансовые средства могут быть инвестированы? Что будет, если стремиться инвестировать как можно больше имеющихся средств? b. Как можно увеличить суммарный NPV для данной группы проектов? Расчет финансовых характеристик. Памятка. k – ставка дисконтирования, обычно принимается равной средневзвешенной стоимости капитала WACC ( Weighted Average Cost of Capital). CFm – финансовый поток (Cash Flow) суммарный поток денег за период номер m, положительный или отрицательный. I0 – начальная инвестиция (может обозначаться и как CF0, но часто специально выделяется в формулах и расчетах) f – ставка реинвестирования, может совпадать с k, может отличаться от k в случае, если полученные в проекте деньги реинвестируются с меньшей доходностью, чем k. g – скорость устойчивого роста. tax - ставка налога на прибыль; EBITDA (Earnings before interest, taxes, depreciation and amortization, валовая прибыль) = Revenue (Выручка) – ( COGS + S&GA ) (операционные, рекламные и административные расходы) EBIT (прибыль до уплаты процентов по кредитам и налогов) = EBITDA – Depreciation (амортизация) NOPAT (чистая прибыль на все активы) = EBIT – Налоги = EBIT*(1-tax) CF (cash flow, финансовый поток) Финансовый поток (Сash Flow) вычисляется как: CFn = EBITn * (1 - tax) + Амортизацияn – CAPEXn – ΔNWCn  где CAPEX - капитальные затраты (Capital Expenditure); ΔNWC - увеличение Чистого Оборотного Капитала (Net Working Capital); NPV (ЧПС) Чистая Приведенная Стоимость инвестиций (Net Present Value) рассчитывается либо с помощью стандартной функции MS Excel: NPV = ЧПС(k; CF1… CFn) + I0, либо прямо по формуле: NPV = I0 + CF1/(1+k)1 + CF2/(1+k)2 + CF3/(1+k)3 + CF4/(1+k)4 + … + CFn/(1+k)n IRR (ВСД) Внутренняя Ставка Доходности инвестиций (Internal Rate of Return) рассчитывается как ставка дисконтирования, при которой NPV обращается в ноль. Т.е. IRR вычисляется из уравнения: 0 = I0 + CF1/(1+ IRR)1 + CF2/(1+ IRR)2 + CF3/(1+ IRR)3 + CF4/(1+ IRR)4 + … + CFn/(1+ IRR)n . Функция Excel: IRR = ВСД(CF0… CFn; [предполагаемое IRR, очень приблизительно]) MIRR (МВСД) Модифицированная Внутренняя Ставка Доходности инвестиций (Modified Internal Rate of Return) рассчитывается путем уравнивания всех инвестиций (отрицательных CF), дисконтированных по ставке дисконтирования k к начальному моменту (PV), и всех доходов (положительных CF), приведенных к конечному периоду, по ставке реинвестирования f ( Net Terminal Value), деленных на (1+MIRR)n (n – номер последнего финансового периода в проекте). PV = = I0 + I1/(1+k)1 + I2/(1+k)2 +…+ In/(1+k)n NTV = = (CF1*(1+f)n-1 + CF2*(1+f)n-2 + …+ CFn-1*(1+f)1 + CFn) MIRR = (-NTV/PV)1/n – 1 Т.е. ставку дисконта, которая уравновешивает настоящую стоимость инвестиций PV с их чистой терминальной стоимостью NTV, называют MIRR. Функция Excel: MIRR = МВСД(CF0… CFn; k; f) MIRR призвана устранить два недостатка критерия IRR: неоднозначность вычисления и подразумеваемое реинвестирование денежных потоков по ставке k. TV (терминальная стоимость проекта) Так как расчет NPV часто довольно волевым методом ограничивают заданным горизонтом планирования n, то ее величина оказывается оцененной «по произволу». Так как по факту бизнес не заканчивается на горизонте планирования, скажем, 5 лет, то имеет смысл учесть и последующие годы, сколько бы их не было. Очевидно, что бизнес редко продолжается вечность… Но в предположении, что срок работы инвестиций неопределенно долог, NPV должна бы считаться по формуле: NPV = I0 + CF1/(1+k)1 + CF2/(1+k)2 + CF3/(1+k)3 + CF4/(1+k)4 + … + CFi/(1+k)i + …… + CF∞/(1+k)∞ В действительности никакой необходимости в суммировании бесконечного числа слагаемых нет, так как можно вычислить сумму геометрической прогрессии по стандартной формуле S = b1/(1-q). Правда, сами финансовые потоки в этой перспективе тоже не определены. Их оценка обычно принимается равной финансовому потоку на горизонте планирования, умноженному на (1+g)m (где g так называемая скорость устойчивого роста (из Dividend Discount Model) отрасли или экономики страны в целом, а m - степень номера года после горизонта планирования). В результате терминальную стоимость проекта (Terminal Value, TV), отнесенную ко времени после n-го года, можно рассчитать по формулам, как: NPV = I0 + CF1/(1+k)1 + CF2/(1+k)2 + CF3/(1+k)3 + … + CFn/(1+k)n + CFn*(1+g)/(1+k)n+1 + CFn*(1+g)2/(1+k)n+2 + CFn*(1+g)3/(1+k)n+3 + … или NPV = I0 + CF1/(1+k)1 + CF2/(1+k)2 + CF3/(1+k)3 + … + CFn/(1+k)n + CFn/(1+k)n *(1+g)/(1+k) * [1 + (1+g)/(1+k) + {(1+g) /(1+k)}2+ {(1+g)/(1+k)}3+ …] отсюда TVn = CFn *(1+g)/(1+k) * [1 + (1+g)/(1+k) + {(1+g) /(1+k)}2+ {(1+g)/(1+k)}3+ …] = CFn * (1+g) / (k – g) или, аккуратнее TV n = CFn * (1+g) / (WACC – g) Здесь g – скорость устойчивого роста за пределами прогнозируемого периода, k – ставка дисконтирования, WACC – средневзвешенная стоимость капитала. Тогда для NPV получится: NPV = I0 + CF1/(1+k)1 + CF2/(1+k)2 + CF3/(1+k)3 + CF4/(1+k)4 + … + CFn/(1+k)n + TVn/(1+k)n Так как терминальная стоимость проекта TVn принято определять как TV n = CFn * (1+g) / (k – g), то оказывается, что коэффициент дисконтирования ее соответствует последнему году прогнозируемого периода n, а не n+1, например. Из-за этого в перечне финансовых потоков последний финансовый поток обычно объединяют с терминальной стоимостью NPV = I0 + CF1/(1+k)1 + CF2/(1+k)2 + CF3/(1+k)3 + CF4/(1+k)4 + … + (CFn+ TVn)/(1+k)n Или NPV = I0 + CF1/(1+k)1 + CF2/(1+k)2 + CF3/(1+k)3 + CF4/(1+k)4 + … + {CFn*(1 + k) / (k – g)} /(1+k)n ФП0 + ФП1 + ФП2 + ФП3 + ФП4 + … + ФП10 A -> 7,0 0,5 0,5 0,5 0,5 0,5 0,5 Б -> 0,5 0,5 0,5 0,5 0,5 0,5 7,0 WACC -> k – ставка дисконтирования IRRпр > k -> NPV > 0 -> можно инвестировать IRRпр < k -> NPV < 0 -> не инвестировать! Формулы и установки «Поиска решения» Решение для задания 1 «a». Проставим произвольно выбор проектов: Дзета, Тета и Иота, например, и запишем формулы (C17:M17) для вычисления номинальных финансовых потоков (потому что на них должно действовать ограничение по доступным финансам). В качестве целевой функции выберем суммарную NPV выбранной группы проектов (O17). Результат оптимизации: К расчетам добавлены еще два финансовых показателя: внутренняя ставка доходности IRR и рентабельность инвестиций ROI. Видно, что ни один из них не является окончательным критерием выбора проектов. Решение для задания 1 «b». Тут, безусловно, возможны различные варианты. Наиболее очевидные технически (но не факт, что доступные в конкретной ситуации): 1. Перенос неиспользуемых остатков средств (а только в начальном периоде их почти 2 млрд.: 9,5-7,6 = 1,9) на следующие периоды. 2. Перенос начала некоторых ресурсоемких проектов на более позднее время (1-й, 2-й, 3-й год). 3. Заимствование денег извне в те периоды, когда они требуются для финансирования дополнительных проектов. Задание 2. a. Рассмотрите возможность переноса остатков бюджетных средств на следующий год. Изменится ли выбор проектов для финансирования? Как изменится максимальная суммарная чистая приведенная стоимость? b. Рассмотрите возможность сдвига проектов во времени начала. Есть ли выигрыш по NPV? c. Рассмотрите возможность использования кредита по ставке 30% в объеме до примерно 6 млрд. для поддержки проекта Дзета. Как рассчитать необходимые заемные средства и график выплаты долга с процентами? d. Рассмотрите возможность использования кредита по ставке 30% в объеме до 10 млрд на произвольные проекты. Как изменится максимальная суммарная чистая приведенная стоимость? Каков оптимальный план инвестиций и заимствования? Решение для задания 2 «a». В задаче можно (как один из вариантов анализа «что-если») запланировать перенос остатков денежных средств на следующий год. При этом имеется ввиду, что недорасходованные средства не могут лежать без движения, а тоже приносят доход. Доходность может быть разной, в зависимости от условий. Но без конкретизации условий обычно предполагают, что эти деньги будут в течение года работать под 23% годовых. Поэтому строку со старыми бюджетными ограничениями B18:M18 скопируем ниже, в строку 19, а вместо нее будем вычислять новый бюджет с учетом переноса денежных средств. В С18 – нулевом периоде – переносить нечего, поэтому пишем =С19. А в D18 напишем =D19 + (C18-C17)*(1+$O$3), т.е. то, что может выделить компания в текущем периоде плюс остаток с предыдущего периода, умноженный на годовой рост. Будем считать, что эта формула справедлива для всех остальных лет, поскольку начиная с 4-го года проектам все равно инвестиции не требуются. Задание «Поиску решения» менять не требуется. После оптимизации оказывается, что будет поддержано на один проект меньше - 5. Но суммарная NPV группы проектов вырастет до 6,66 млрд. Еще раз отметим, что существенное улучшение результата, это, конечно, аргумент в пользу переноса денежных средств. Но не факт, что в компании такое предложение будет поддержано. Решение для задания 2 «b». Идея решения о сдвиге проектов показана на рисунке. Здесь приведен вариант со сдвигом на 1 и 2 года. При этом набор проектов дублируется и сдвигается на 1-й – 11-й годы (голубые проекты), и еще раз дублируется и сдвигается на 2-й – 12-й годы (зеленые проекты. Теперь выбор проектов будет проводиться среди 33 проектов, а не среди 11. Собственно, все остальное не меняется. Так как при сдвиге в будущее NPV проектов уменьшается (сравните, например, NPV проекта Альфа в обычном, голубом и зеленом вариантах), то максимизация NPV группы проектов будет приводить к преимущественному выбору более раннего варианта проектов. Дополнительная необходимая поправка модели нужна для того, чтобы запретить выбор нескольких вариантов одного и того же проекта. Поэтому в оранжевой области справа считаются сумма переменных для каждого проекта по трем вариантам ( =Q5+Q16+Q27 для проекта Альфа и т. д.). В «Поиске решения» потребуем, чтобы оранжевые ячейки X5:X15 были меньше или равны 1. В этом решении тоже получается более высокий суммарный NPV=6.3 и поддерживаются 6 проектов, но 2 проекта: Тета и Иота предложено начать через 2 года. Решение для задания 2 «с». Для учета возможного кредита добавим в начальный вариант задачи в строке 16 комментарий «Кредит», а в столбцы «C-M» будем писать заимствованные и отданные деньги. Для удобства расчета модифицируем формулу расчета номинального финансового потока в строке 17. Нужно учесть в общем потоке кредитные средства. В ячейке С17 допишем в формулу =СУММПРОИЗВ($O$5:$O$15;C5:C15) финансовый поток по кредиту: =СУММПРОИЗВ($O$5:$O$15;C5:C15)+С16 и протянем формулу до M17. Заменим «вручную» переменную O10 проекта Дзета на 1 – решаем финансировать проект. Очевидно, что в первом году до баланса не хватает 1,4 млрд., во втором 3,80, а в третьем 0,84 млрд. Добавим и эти числа в строку «Кредит». Обратите внимание, что дефицит исчез и наши финансовые потоки сбалансировались с бюджетными ограничениями. Дальше кредит нужно возвращать. Очевидно, что свободные 1,99 млрд. 3-его года нужно сразу отдать. Но сколько отдать в следующем году? Как убедиться, что кредит возвращен и проценты по нему полностью уплачены? Сколько же денег нужно отдать? Ответ на этот вопрос на самом деле не слишком сложен. Надо только вспомнить, что «Кредит», по сути, такой же проект, как всякий другой и так же характеризуется финансовыми потоками. Поэтому ничто не мешает, как и для всех остальных проектов, подсчитать IRR кредита. Для этого просто потянем формулу в столбце «S» еще на строку вниз. В ячейке S16 возникнет ошибка, так как мы не вернули даже тех денег, которые взяли. Подберите число в ячейке G16 так, чтобы IRR кредита оказалась равной 30%. Получим результат, показанный на картинке. Т.е. заняв 6,04 млрд руб., мы должны будем вернуть банку 13,16 млрд. Выгодно ли это? На самом деле, пока это неизвестно. Потому что после выбора проекта Дзета к суммарной NPV≈4,92 прибавилось 2,1 млрд и… все. А кредит? Как учесть расходы по обслуживанию кредита в целевой функции? Фактически нужно добавить к целевой функции величину NPV кредита. Т.е. сначала протянем формулу NPV в столбце R до R16, а затем добавим ее к целевой функции О17: =СУММПРОИЗВ(O5:O15;R5:R15)+R16. Вот теперь все учтено и все равно суммарная NPV=6,115 млрд, существенно выше, чем была без проекта Дзета и кредита. Если имеются сомнения в осмысленности операции вычисления NPV для проекта «Кредит», можно вычислить NPV для всей группы проектов, финансовую сводку потоков которой содержит строка 17 «Номинальные ФП». В самом деле, мы в этой строке аккуратно учли все финансовые потоки, которыми оперируем, управляя данной группой проектов, включая финансовые потоки по кредиту. Так что, протянув формулу NPV столбца R до ячейки R17, получим сразу NPV суперпроекта из 7 обычных проектов и кредитного проекта. Видно, что это все те же 6,115 млрд, но вычисленные более очевидным способом. Решение для задания «d». Кажется, что усложнить задачу для автоматического подбора и поддерживаемых проектов O5:O15, и занимаемых и выплачиваемых сумм С16:M16 – очень легко. Ведь, по существу, все есть – есть живая книга Excel, которая пересчитывает все необходимые параметры при изменении плана. Однако именно в данном случае имеется проблема. Функции ЧПС() и ВСД(), которые мы используем при расчете изменяющегося NPV кредита и выплаченных процентов, не годятся для оптимизации, так как не являются математическими и выдают ошибки при некорректных параметрах финансовых потоков. Их надо как-то заменить. Для их замены придется ввести прямой расчет суммарной NPV группы проектов + кредит по ставке 23% (строка 20) и прямой расчет NPV кредита по ставке 30% (строка 21). Ячейка O20 будет новой целевой функцией, а ячейка O21 должна оставаться равной нулю, чтобы процент по кредиту составил заданные 30%, так как IRR – это ставка дисконтирования, при которой NPV обращается в ноль. И если финансовые потоки по кредиту удастся подобрать так, чтобы O21 стала нулем, то это и будет означать использование кредита по заданной ставке B22. Старый расчет с помощью функций ЧПС() и ВСД() в этом случае можно не убирать, поскольку он в оптимизации больше не используется. Результат получается довольно впечатляющим, но странным: Попробуйте объяснить его, интерпретируя финансовые потоки по кредиту. Учитывая, что отрицательный финансовый поток означает движение денег от нас, первые три потока по кредиту: -7.3, -1.9, -1.4 демонстрируют план одолжить кому-то деньги, а не занять. На самом деле вполне ожидаемый результат. Ведь без специальных оговорок возможность занять деньги под 30% годовых показывает возможность одолжить деньги под 30%. А у нас 8 проектов из 11 имеют более низкую доходность. Так что «Поиск решения» советует поддержать 3 проекта с доходностью 30% и выше, а оставшиеся деньги отдать в рост. Что выглядит разумно. В этой ситуации «Поиску решения» надо как-то запретить одалживать деньги. Т.е., во-первых, требуем, чтобы ячейки G16:M16<=0 – с четвертого года нам точно не нужны деньги для инвестиций, так что мы можем их только отдавать. И, во-вторых, имеет смысл потребовать, чтобы в первые два-три периода времени ФП по кредиту были больше или равны нулю – можем занимать, но не давать в кредит свободные средства. Тогда получим требуемое решение с суммарным кредитом 8,45 млрд. Итоговые файлы. Домашнее задание к лекции №6 (условия задания №5 с дополнительными обстоятельствами) Фирма «Фасад» производит двери для продажи местным строительным компаниям. Репутация фирмы позволяет ей продавать всю производимую продукцию. На фирме работает 10 рабочих в одну смену (8 рабочих часов), 5 дней в неделю, что дает 400 часов в неделю. Рабочее время поделено между двумя существенно различными технологическими процессами: собственно производством и конечной обработкой дверей. Из 400 рабочих часов в неделю 250 отведены под собственно производство и 150 под конечную обработку. «Фасад» производит 3 типа дверей: стандартные, полированные и резные. В таблице приведены временные затраты и прибыль от продажи одной двери каждого типа.       Время на производство (мин) Время на обработку (мин) Прибыль Стандартные 30 15 $ 45 Полированные 30 30 $ 90 Резные 60 30 $120 Задание. b. На предстоящей неделе «Фасад» должен выполнить контракт на поставку 280 стандартных, 120 полированных и 100 резных дверей. Для выполнения заказа «Фасад» может закупить некоторое количество полуфабрикатов дверей у внешнего поставщика. Эти полуфабрикаты «Фасад» может использовать только для производства стандартных и полированных, но не резных дверей. При этом изготовление стандартной двери требует лишь 6 мин процесса обработки, а полированной – 30 мин обработки (процесс собственно производства для этих полуфабрикатов не требуется). Полученная таким образом стандартная дверь приносит $15 прибыли, а полированная - $50. Предполагая, что по-прежнему 250 часов в неделю отведено под производство и 150 под обработку, определите сколько и каких дверей «Фасад» должен произвести самостоятельно, и сколько полуфабрикатов закупить для изготовления стандартных и полированных дверей?                     Задачу выслать на адрес преподавателя varyukhin@gmail.com к следующему занятию. Самостоятельный разбор материала. Мини-кейс: Компания «Холод» - оптовая торговля замороженными овощами Компания «Холод» занимается поставкой 15-ти типов замороженных овощей в овощные и продовольственные магазины. Замороженные овощи приходят от поставщиков в стандартных картонных коробках, объем которых представлен в таблице (в литрах). Недельный запас замороженных овощей прибывает на склад в понедельник утром. В конце недели, практически всегда, склад компании пустеет. Объем склада 160 м3. Компания «Холод» пользуется кредитной линией, допускающей еженедельный расход в 900 000$ на покупку недельного запаса овощей (оплата может быть произведена единовременно в понедельник утром). Компания прогнозирует объем продаж на каждую неделю в терминах минимального и максимального количества коробок для каждого типа овощей, которые будут проданы на следующей неделе. Минимальное количество определяется контрактами, которые компания «Холод» заключила с небольшим количеством овощных магазинов. Максимальное количество – это пессимистический прогноз рыночного спроса на предстоящей неделе. Цены, по которым продукция закупается у поставщика и отпускается потребителям, приведены в таблице, вместе с минимальными и максимальными объемами продаж каждого продукта на следующей неделе. Определите объем закупок каждого типа овощей, максимизирующий прибыль компании. Решение задачи. Это простая задача на максимизацию прибыли в условиях ограниченных ресурсов. Переменные задачи – количество закупаемых коробок замороженных овощей (всего 15 переменных). Организация данных и расчетные формулы. В качестве количества коробок для «затравки» и проверки формул укажем минимально допустимое количество (можно записать в G5 формулу =D5 и протянуть вниз до G19). Допустим, мы сначала не будем ограничивать количество коробок по каждой позиции, а зададим только ограничения на объем холодильных мощностей и доступные средства. Как понять полученное решение (рисунок справа)? Почему предлагается закупить только кукурузу? Как вы видите, кукуруза вовсе не имеет самую высокую маржу. Почему же выбрана именно она? Вычислим еще два показателя: отношение маржи к объему коробки и отношение маржи к закупочной цене. Как вы можете видеть, самое высокое у кукурузы только отношение маржи к себестоимости. Наблюдение, которое мы сделали сейчас, имеет прямое отношение к так называемой теории ограничений (см. книгу Э. Гольдратт, «Цель»). Которая утверждает в частности, что самый выгодный продукт не тот, у которого самая высокая маржа, а тот, который приносит наивысший эффект в расчете на единицу дефицитного ресурса. Целевая функция – самый важный ключевой показатель - в нашем случае действительно прибыль, т.е. величину эффекта определяет маржа. А дефицитный ресурс это объем кредита, так как в полученном решении полностью израсходованы именно деньги, а не место на складе. Так что кукуруза приносит максимальный эффект на вложенный рубль. Добавим ограничения на минимальные и максимальные закупки в виде G5:G19<=E5:E19 и G5:G19>=D5:D19. Теперь получается более сложное решение. Однако ввиду того, что мы имеем дело с единственным лимитирующим (дефицитным) ресурсом – объемом кредита, решение практически можно угадать. Нетрудно заметить, что от зеленого горошка до гомбо все предлагается закупить по минимуму. От высшей в рейтинге маржа\себестоимость кукурузы до зеленных бобов – по максимуму. И единственную позицию перуанские бобы в промежуточном между минимумом и максимумом количестве. Решение задачи в подобных случаях можно вычислить без оптимизации по простому алгоритму. 1. Отсортируем продукты по убыванию фактора маржа\себестоимость. 2. Назначим все закупки по возможному минимуму в соответствии с ограничениями. 3. Будем закупать самые высокорейтинговые продукты по максимуму до тех пор, пока не кончатся деньги. 4. Продукт, на котором деньги закончились закупим на весь остаток денежных средств. Все. Наши вкладки несложно проверить. Допустим, нам удалось нарастить кредитную линию до 1,2 млн USD. Получим новое решение. Теперь первым исчерпался склад, а деньги еще есть. Значит объем склада – дефицитный ресурс и управлять оптимальным решением должен фактор маржа\объем коробки. Отсортируем таблицу по столбцу I. Опять не сложно установить, что все продукты от Брокколи до перуанских бобов закупаются по максимуму, от кабачков до гомбо – по минимуму, а единственная позиция с промежуточным объемом закупки – зеленные бобы. Т.е. снова оптимальное решение можно предвычислить, если знать дефицитный ресурс. На практике, в реальном деле специалист практически всегда может и сам указать дефицитный ресурс, потому что с ним больше всего проблем, так что его не обязательно извлекать из результатов оптимизации. В отношении задач оптимизации эти наблюдения позволяют сделать вывод о том, что во многих случаях оптимизация даст довольно очевидный результат. Однако не следует думать, что без такого инструмента, как «Поиск решения», можно и вовсе обойтись. Даже в нашей задаче могла сложиться ситуация, когда теория ограничений не поможет. Представьте себе, что часть холодильных мощностей вышла из строя и на некоторое время нам доступен объем только в 120 кубометров. Кредитная линия остается 900 тыс. USD. Легко убедиться, что в этом случае оба ресурса полностью исчерпаны. Можете проверить, что никакой алгоритм составления решения «вручную» не позволяет найти данное оптимальное решение. Так получается всегда, когда дефицитных ресурсов больше чем один. Поэтому, например, в кейсе «Кондитерская фабрика», где дефицитных ресурсов было три, мы даже не пробовали получить решение без оптимизации. Итоговый файл. Задачи транспортного типа и задачи о назначениях Переход к целочисленным ограничениям в задачах линейной оптимизации приводит к изменению алгоритма решения задачи – вместо очень эффективного симплекс-метода используется медленный и не очень надежный метод ветвей и границ. Это приводит к катастрофическому увеличению времени расчета и к необходимости специального исследования корректности решения, что чаще всего обесценивает метод линейной оптимизации в конкретном случае с точки зрения практического менеджера. В некоторых случаях задачу, требующую использования целых или двоичных ограничений, удается сформулировать так, что решение заведомо получается целочисленным даже при отсутствии соответствующих ограничений. Разумеется, задача в этих случаях решается очень быстро и при большом числе переменных, так как для решения по-прежнему используется алгоритм симплекс-метода. Такие задачи называют транспортными задачами и задачами о назначениях (по причинам сугубо историческим). Транспортные задачи обычно решают проблему перевозок от нескольких поставщиков нескольким потребителям с минимальными затратами. Задачи о назначениях решают проблему назначений одних объектов в пару к другим (людей – людям, людей – работам, складов – потребителям и т.д.) в соответствии с оптимальным значением выбранного показателя. Кроме задач собственно транспортных и задач о назначениях такими полезными свойствами обладают, например, задачи о кратчайшем маршруте в сети дорог (используются в системах глобального позиционирования GPS для прокладывания маршрутов) и некоторые другие. Мини-кейс: Формирование команд. Для самостоятельного разбора. Фирма, занимающаяся продажей оборудования для компьютерных сетей, наняла 10 новых специалистов по продажам и 11 новых техников-программистов, которых необходимо объединить в пары (техник + менеджер по продажам) — команды по продаже оборудования, соответствующего нуждам конкретного клиента. Менеджер по работе с персоналом провел среди них тест Майер-Бриггс и определил индекс взаимной совместимости для каждой возможной пары техник - продавец. Индекс может принимать значения в интервале от 1 до 16. Значение индекса 1 показывает, что команда обладает наилучшей совместимостью. Индекс, равный 16, свидетельствует о практической невозможности какого бы то ни было сотрудничества. Более подробные характеристики смотрите в дополнительных файлах. Результаты тестов представлены в таблице. Необходимо провести формальный Team-building, т.е. по результатам тестирования наилучшим образом составить команды «техник + продавец» для работы с потенциальными клиентами. b. Сформулируйте целевую функцию для данной проблемы. Определите переменные задачи и организуйте данные для вычислений. Поставьте задачу для «Поиска решения» и получите оптимальное решение, не вводя ограничений целочисленности или бинарности. c. Есть ли среди созданных команд пара сомнительного качества? Введите дополнительное ограничение, чтобы все команды имели бы индекс не хуже, чем 10. Каково минимальное возможное значение индекса худшей команды? Организация данных. Результат оптимизации в отсутствие ограничений на индексы. d. Специалисты по HR утверждают, что нехорошо объединять в команды друзей. Попробуйте добавить ограничение, что индекс команд должен быть не лучше 2 или 3. Итоговый файл. Мини-кейс: Ремонт автодорог. Для самостоятельного разбора. С восьми асфальтобетонных заводов должен вывозиться асфальт для ремонта пяти больших участков автодорог области. Транспортные издержки при перевозках в общем различны (см. таблицу). Транспортные издержки даны в таблице. Заказы дорожно-строительных бригад на завтра: Заводы в состоянии предоставить завтра: Менеджер подрядной организации хочет минимизировать транспортные расходы для данных условий. a. Каковы наименьшие транспортные издержки? b. Какие участки недополучат заказанный ими асфальт и в каком количестве? c. Найдите разницу между наилучшим и наихудшим планом перевозок? d. Выяснилось, что из-за аварийного состояния моста перевозка асфальта с АБЗ 20 на участок D по прямому маршруту невозможна. Объездной маршрут увеличивает стоимость рейса на 1000 рублей. Как из-за этого возрастут транспортные расходы? Формулы для расчетов в транспортной задаче. Установки «Поиска решения». Итоговый файл. Домашнее задание к лекции №7 Необходимо составить 7 команд по два человека из метеоролога и специалиста по связи для вахтовой работы на базовых метеостанциях. Пары составляются из сотрудников, среди которых проведен специальный психологический тест на взаимную совместимость. Индекс совместимости варьирует от 1 (выраженная враждебность) до 15 (возможность дружеских отношений), и для каждой потенциальной пары приведен в таблице. Определите такое распределение по парам, которое обращает в максимум суммарный индекс совместимости.   Р 1 Р 2 Р 3 Р 4 Р 5 Р 6 Р 7 M 1 10 13 7 1 11 6 6 M 2 7 3 5 1 8 2 13 M 3 1 6 4 12 11 4 9 M 4 4 1 13 5 11 4 3 M 5 7 4 5 1 7 3 12 M 6 8 11 13 5 8 1 9 M 7 6 13 2 13 9 5 2 Задание. a. Какова величина суммарного индекса? Каков наихудший индекс в отобранных парах? b. Есть ли у задачи альтернативные решения? Какое из решений лучше? Почему?                     Задачу выслать на адрес преподавателя varyukhin@gmail.com к следующему занятию. Мини-кейс: Лов рыбы Фирма, занимающаяся промышленным ловом рыбы, нуждается в закупках горючего. Ежемесячные потребности рыболовецкой флотилии в дизельном топливе (в тоннах) представлены в таблице. Месяц Янв Фев Мар Апр Май Июн Июл Авг Сен Окт Ноя Дек Спрос 100 500 800 1000 600 1000 1000 800 700 500 400 400 Стоимость тонны горючего - $200, а издержки хранения, рассчитанные по внутренней норме доходности фирмы, составляют $15 в месяц на каждую тонну. Новый заказ на поставку горючего влечет за собой издержки в размере $20000, не зависящие, при тех масштабах закупок, которые осуществляет фирма, от объема поставки. a. Сформулируйте задачу линейной оптимизации. b. Составьте план закупок горючего на год так, чтобы минимизировать общие издержки хранения и запуска. Какова будет сумма издержек? c. Сравните оптимальные издержки с вариантами закупки всего годового запаса горючего либо сразу (в январе), либо ежемесячно. d. Финансовый отдел требует не производить закупки горючего в августе, в связи с приходящимися на этот месяц большими выплатами. Изменит ли это требование план закупок? Как изменятся общие издержки? Указание: если при запуске «Поиска решения» появится сообщение «Условия линейной модели не удовлетворяются», ответьте ОК и запустите «Поиск решения» еще раз. Решение задачи. Эта задача принадлежит к типу задач о выборе размера лота, и относится, таким образом, к теме управление запасами. Более точно можно сказать, что это задача на управление запасами в условиях, когда спрос в предстоящий период времени является детерминированным (т.е. не случайным), но существенно переменным. Как вы можете видеть из соответствующего раздела учебников, методы решения подобных задач в теории управления запасами имеются, но, к сожалению, они не достаточно эффективны. В то же время по своей постановке задача выглядит как типичная задача линейной целочисленной оптимизации. Для предварительного расчета издержек при различных вариантах времени и размеров заказов, о чем спрашивается в вопросе c, можно составить следующую таблицу. Организация данных и расчетные формулы. В этой таблице в ячейках C8-C19 нужно записывать количества горючего, закупаемого в каждом из месяцев. Найдем теперь оптимальный план закупок, соответствующий минимальным возможным издержкам. Здесь следует отметить, что задача нами уже почти построена: целевая функция – общие издержки хранения и заказов, переменные – значения лотов для каждого месяца, ограничение – отсутствие дефицита. Одна неувязка – функцию =ЕСЛИ() в задаче линейной оптимизации использовать нельзя, она нелинейная (в математике ее график представляют прямоугольной ступенькой и называют функцией Хевисайда). Такая функция обычно заведет в тупик и алгоритм нелинейной оптимизации. Если в надстройке Поиск решения снять условие линейной модели и попробовать минимизировать целевую функцию в таблице с отмеченными переменными решениями и ограничениями, программа не возразит, но и приемлемого результата не даст. Поэтому придется использовать прием, служащий в математике для замены функции =ЕСЛИ(). Для этого в тех ячейках, в которых были записаны эти функции, разместим дополнительные переменные двоичного типа. Теперь переменных у нас будет не 12, а 24 – 12 размеров лотов и 12 указателей на то, сделан заказ или нет. Так как схема расчета издержек, построенная нами ранее предполагает, что в ячейках D8-D19 записаны нули и единицы, показывающие, был заказ или нет, то никаких исправлений в других формулах не потребуется. Вызовем надстройку Поиск решения и зададим параметры задачи: целевая ячейка – I21, цель – минимум, переменные – C8-D19, ограничения – F8-F19>=0, D8-D19 = двоичные, линейная модель, неотрицательные значения переменных. Запуск Поиска решения на выполнение принесет неприятный результат – хотя заказы были сделаны, значения двоичных переменных остались равными 0. Этого и следовало ожидать, ведь никакой связи между заказами и двоичными переменными мы для Поиска решения не указали, поэтому он выбрал «наилучшие» значения. Чтобы ввести такую связь запишем в ячейки E8-E19 линейные выражения вида = C8 - 100000*D8 (об использовании этого дополнительного условия, типичного для задач с целочисленными ограничениями, читайте в учебном пособии). Добавим в параметрах поиска решения новое ограничение: E8-E19 <= 0. Домашнее задание к лекции №8 Прогноз спроса на сырье для производства тангриза на следующий год по результатам продаж предыдущих лет (с учетом слабого возрастающего тренда и сезонности), представлен в таблице. Месяц 1 2 3 4 5 6 7 8 9 10 11 12 Заказ, мешков 134 136 138 140 140 144 288 420 340 200 160 160 Стоимость одного мешка сырья 5 у.е. При оценке издержек хранения сырья на складе учитывается стоимость замороженного капитала в размере 2% от стоимости сырья в месяц. Сырье поставляется на предприятие в железнодорожных вагонах. В каждом вагоне можно привезти не более 600 мешков сырья. Стоимость доставки одного вагона сырья - 50 у.е. Задание. a. Составьте план поставок сырья на склад завода, минимизирующий суммарные издержки хранения и доставки. Учтите два варианта работы с поставками: i. Поставки идут только полными вагонами; ii. Вагоны могут идти с неполной загрузкой. b. Какое количество вагонов потребуется в том и другом случаях для выполнения всех поставок в течение года? c. Какое количество вагонов будет оптимальным при стоимости доставки 80 у.е. и неполной загрузке вагона? d. При какой минимальной стоимости доставки (с точностью до 5 единиц) оказывается выгодным грузить вагоны полностью?