Требования к теоретическому и практическому заданиям

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

- Дрогобыцкий И.Н. Системный анализ в экономике. — М.: ЮНИТИ-ДАНА, 2011

Также рекомендуется прочитать:

1. О'Коннор Дж., Макдермотт И. Искусство системного мышления: Необходимые знания о системах и творческом подходе к решению проблем: пер. с англ. — М.: Альпина Бизнес Букс, 2006.

2. Медоуз Д. Азбука системного мышления. — М.: БИНОМ: Лаборатория знаний, 2010.

Раскрытие вопросов по теоретической части дисциплины предполагает краткий ответ (1-2 страницы) на теоретический вопрос.

 

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

Количественные методы предоставляют мощные инструменты анализа в области:

- планирования производства

- плана закупок

- оптимального состава

- транспортных задач

- задач о назначениях (частный случай транспортной: рабочие – «поставщики», работы – «потребители»)

- оптимального управления запасами.

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

Задание выполняется с использованием пакета Поиск решения в MS Excel и служит для проведения анализа деятельности компании.

 

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

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

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

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

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

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

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

· ограниченностью ресурсов, находящихся в нашем распоряжении (денежных, временных, материальных),

· установленными «правилами игры» (рыночные ограничения, нормативные акты, лимитирующие ту или иную характеристику или любые требования субъекта, принимающего решения).

 

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

c₁х₁ + с₂х₂ + … + с_nx_n

Примеры выполнения задач по планированию производства с помощью средств MS Excel

2.1. Фирма «Фасад»

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

«Фасад» производит 3 типа дверей: стандартные, полированные и резные. В таблице приведены временные затраты и прибыль от продажи одной двери каждого типа.

Время на производство (мин)   Время на обработку (мин)   Прибыль Стандартные     $ 45 Полированные     $ 90 Резные     $120

 

 

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

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

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

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

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

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

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

P = X1*45 + X2*90 + X3*120 ($).

Лучше всего организовать данные на листе MS Excel следующим образом:

 

	A	B	C	D	E	F
	Фирма «Фасад»
		Время на производство (мин)	Время на обработку (мин)	Прибыль, $	Переменные
	Стандартные					X1
	Полированные					X2
	Резные					X3
					Целевая функция
					=СУММПРОИЗВ(E3:E5;D3:D5)

 

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

На следующем этапе решения следует выяснить, при каких ограничениях нужно найти максимальную прибыль. В данном случае из условия следует, что можно затратить на производственную стадию не больше 250 часов в неделю, а на обработку не больше 150 часов. Других существенных ограничений в задаче нет. Так как в надстройке «Поиск решения» нельзя задавать ограничения в виде формул, все необходимые расчеты для задания ограничений следует сделать на листе MS-Excel.

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

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

Добавим эти формулы на лист с данными задачи (Рис. 2):

 

	A	B	C	D	E	F
	Фирма «Фасад»
		Время на производство (мин)	Время на обработку (мин)	Прибыль $	Переменные
	Стандартные					X1
	Полированные					X2
	Резные					X3
					Целевая функция
		=СУММПРОИЗВ ($E$3:$E$5;B3:B5)	=СУММПРОИЗВ ($E$3:$E$5;C3:C5)		=СУММПРОИЗВ (E3:E5;D3:D5)
	Ограничения	=250*60	=400*60-B8

 

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

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

 

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

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

 

Нажав ОК, Вы сохраните найденное решение на листе MS Excel, содержащем условия задачи. Проверьте, что получился следующий результат.

	A	B	C	D	E	F
	Фирма «Фасад»
		Время на производство (мин)	Время на обработку (мин)	Прибыль, $	Переменные
	Стандартные					X1
	Полированные					X2
	Резные					X3
					Целевая функция
	Ограничения

 

 

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

 

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

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

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

Изменяемые ячейки
		Результ. значение	Нормир. стоимость	Целевой коэффициент	Допустимое увеличение	Допустимое уменьшение
Ячейка	Имя
$E$3	Стандартные переменные		-15			1E+30
$E$4	Полированные переменные
$E$5	Резные переменные
Ограничения
		Результат	Теневая	Ограничение	Допустимое	Допустимое
Ячейка	Имя	значение	Цена	Правая часть	увеличение	уменьшение
$B$7	Время на производство (мин)
$C$7	Время на обработку (мин)

 

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

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

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

Для изменения условий добавим в ячейки D7 и D8 формулы: =B7+B8 и =400*60, соответственно. После этого нужно немного модифицировать задание надстройке «Поиск решения». Вызвав надстройку, удалим из ограничений условие $B$7:$C$7 <= $B$8:$C$8, и добавим вместо него условие D7 <= D8.

 

 

Получим следующее решение:

 

	A	B	C	D	E	F
	Фирма «Фасад»
		Время на производство (мин)	Время на обработку (мин)	Прибыль, $	Переменные
	Стандартные					X1
	Полированные					X2
	Резные					X3
					Целевая функция
	Ограничения

 

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

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

Поэтому имеет смысл посмотреть, что меняется, если потребовать выпускать все двери. Конечно, здесь нужно задать некоторое конкретное число, которое мы вынуждены «взять с потолка». Положим, что следует выпускать не менее 50 штук дверей каждого типа. Введем в ячейки G3:G5 число 50 и добавим в надстройку «Поиск решения» ограничение E3:E5 <= G3:G5. Получим новое решение задачи (снова лучше создать сначала копию листа).

 

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

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

 

	Переменные			Переменные
		X1			X1
	287.5	X2			X2
		X3			X3
	Целевая функция		Целевая функция
		а)		б)

 

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

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

 

Фирма «Фасад»
	Время на производство (мин)	Время на обработку (мин)	Прибыль, $	Переменные	Всего, шт.	Заказ
Стандартные					X1
Полированные					X2
Резные					X3
Стандартные П					X4
Полированные П					X5
		Полное время	Целевая функция
Ограничения

 

Часть d. Для решения этой задачи нужно изменить только одно условие – так же как мы делали при анализе части b задачи, ограничим только суммарное время двух стадий. Результат представлен на Рис.

 

Фирма «Фасад»
	Время на производство (мин)	Время на обработку (мин)	Прибыль$	Переменные	Всего	Заказ
Стандартные					X1
Полированные					X2
Резные					X3
Стандартные П					X4
Полированные П					X5
	Полное время		Целевая функция
	6 000	18 000	24 000	46 500
Ограничения			24 000

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

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

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

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

 

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

2.2. «Сталепрокатный завод»

Сталепрокатный завод производит стальные листы трех различных размеров: 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 дюйм – в обрезки). Если перебрать все возможные варианты раскроя, их окажется не так уж много. Так как для каждого варианта известно и количество полученных листов и количество обрезков, то выбрав в качестве переменных количество листов раскроенных по каждому из описанных вариантов, можно построить задачу линейной оптимизации. Целевой функцией будет общее количество остатков. Цель – минимизация остатков при условии исполнения заказа.

 

	A	B	C	D	E	F	G	H	I	J
	Вариант раскроя	Лист проката	Размер листа, дюймов		Число листов	Остаток
								=B3-СУММПРОИЗВ ($C$2:$E$2;C3:E3)
								Целевая функция
		Получено листов					Всего	=СУММПРОИЗВ (H3:H17;G3:G17)
		Заказ
Получено листов –	=СУММПРОИЗВ(C3:C17;$G$3:$G$17)

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

Задание для Поиска решения в данном случае будет выглядеть очень просто: целевая ячейка – H19, цель – минимум, изменяемые ячейки – G3:G17. По смыслу задачи следует потребовать, чтобы переменные были целыми числами (G3:G17 = целое). Как обычно во вкладке параметры отмечаем, что задача линейная и переменные неотрицательны.

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

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

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

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

2.3. «Компания “Черные каски”»

Горнопромышленная компания “Черные каски” собирается работать в некоторой области в течение следующих пяти лет. У нее имеется 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 переменных.

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

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

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

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

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

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

 

	A	B	C	D	E	F	G
	Шахта	Предел выработки		Содер-жание ИК	себест. руды
	Койот			10%
	Мокрая	2.5		7%	5.5
	Елизавета	1.3		15%
	Ореховый лог			5%
	шахта	1 год	2 год	3 год	4 год	5 год
	Койот
	Мокрая
	Елизавета
	Ореховый лог.
	задан. %	9%	8%	8%	7%	7%
		1	2	3	4	5
14	средний %	=СУММПРОИЗВ(B8:B11;$D$2:$D$5)/B18
15	кол-во руды	=СУММ(B8:B11)				Млн. $
	доход	=B15*B20-СУММПРОИЗВ(B8:B11;$E$2:$E$5)	=СУММ(B16:F16)
	… с дисконтом	=B16/$A$18^B13	=СУММ(B17:F17)
	1.1	5.00	5.63	5.63	6.43	6.43
	цена руды	10.0	8.9	8.9	7.8	7.8

 

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

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

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

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

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

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

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

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

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

 

ДШ1 * p1 + ДШ 2 * p 2 + ДШ 3 * p 3 + ДШ 4 * p 4 + ДШ 5 * p 5,

Общая годовая добыча

 

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

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

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

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

шахта 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

 

 

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