Разбираем оптимизационную задачу из книги Э.Голдратта "Синдром стога сена. Выуживание информации из океана данных" (англ. Eliyahu M. Goldratt "The Haystack Syndrome: Sifting Information Out of the Data Ocean") с использованием EXCEL. Решим задачу с помощью инструмента EXCEL "Поиск решения", а затем, чтобы лучше разобраться в подходе Голдратта, с помощью обычных формул.

Примечание: самая известная книга Э.Голдратта - роман Цель.

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

Задача

Задача состоит в максимизации прибыли производственной компании за период. Как видно из схемы ниже, все производственные и стоимостные параметры известны: 

• Компания производит только 2 Продукта (обозначим P и Q), цены их известны: 90долл и 100долл за штуку.
• Известен Объем рынка в шт (больше этого количества продать не можем, но меньше можем): 100 шт в неделю Продукта P и 50шт Продукта Q
• Для производства Продуктов используются различные Ресурсы (обозначим их А, В, С, D - считается, что это рабочие определенных специальностей) и Материалы (обозначим их Материал1, Материал2, Материал3 + покупная деталь), из которых изготавливаются Продукты P и Q.
• Стоимость использования Ресурсов (Операционные расходы) известна: 6000 долл в неделю (это мы платим за все Ресурсы сразу не разделяя рабочих и оборудование, эти расходы не зависят от объема выпуска)
• Известны производительности ресурсов (мин/шт) и стоимость Материалов. Значения указаны на схеме ниже

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

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

Поиск решения

Для тех, кто знаком с инструментом MS EXCEL Поиск Решения, может справиться с задачей, даже не особо вникая в суть теории Голдратта, т.к. это классическая задача по линейной оптимизации. Подобная задача решена в этой статье.

Составим модель, как показано в нашей вводной статье про Поиск решения. Модель приведена в файле примера (кнопка для скачивания вверху и внизу статьи).

1. Переменными модели будут, естественно, количество Продуктов P и товара Q, которые нужно произвести за неделю (зеленые ячейки В10:С10 на картинке выше). Инструмент Поиск решения будет по своему алгоритму подбирать их значения для максимизации прибыли

2. Введем ограничения модели, их 2 (выделены синим цветом заливки). Объем продаж ограничен объемом рынка (100 и 50 шт каждого Продукта), а время работы ресурсов - длительностью рабочей недели в минутах (у нас это 2400 мин).

3. Наконец займемся тем, что мы будем максимизировать - прибылью. Напишем формулу, чтобы вычислить прибыль (красная ячейка В49 на картинке выше). Определим прибыль как разницу между стоимостью проданных Продуктов (выручка всего) и затрат на их изготовление (Операционные расходы и Связанный капитал). Операционные расходы мы определили выше, а Связанным капиталом Э.Голдратт называет "деньги, инвестированные компанией в производство продукции, которую она собирается продать", т.е. стоимость всех Материалов, которые мы купили у других компаний для производства наших Продуктов (не забудем про покупную деталь). В файле примера формула для расчета Связанного капитала находится в ячейке F21. В терминах Поиска решения формула прибыли называется целевой функцией. Понятно, что в формулу целевой функции входят переменные модели.

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

После нажатия кнопки решение будет найдено - прибыль составит 300 долл в неделю (Продукт P нужно продать 100 шт, а продукт Q - 30шт). 

Конечно, сведя задачу из книги Э.Голдратта к обычной оптимизационной задаче, мы не поставили под сомнение ценность его теории. Теория ограничений (TOC - Theory of Constraints) - это способ  мышления, а не одна частная задача. В следующем разделе мы решим задачу путем логических рассуждений, подкрепленных формулами.

Решение с помощью формул (вникаем в суть задачи)

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

1. Начнем с естественного вопроса: покрывает ли цена каждого Продукта хотя бы затраты на приобретаемые для его изготовления материалы? Если нет, то компания заведомо будет в убытке, оптимизировать убытки как-то странно.  

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

2. Теперь проверим, что является для компании ограничением: рынок или время ресурсов? Действительно, если компания на 100% может закрыть рыночную потребность, то и оптимизировать план продах не требуется. 

Чтобы ответить на этот вопрос нужно посчитать сколько потребуется времени для удовлетворения на 100% запроса рынка.

Как видно из картинки выше, времени ресурса В не достаточно, чтобы произвести за неделю Продукта P 100 шт, а продукта Q - 50шт.

3. Ресурс В - узкое место в компании. Его также можно определить с помощью несложных формул (далее это пригодится в расчетах оптимального соотношения выпуска Продуктов).

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

Т.к. ресурс В (оборудование/персонал) ограничивает производительность всей компании, то он должен трудиться прежде всего над производством того продукта, который продавать максимально выгодно. Какой это продукт Р или Q?

Голдратт рассуждает так:

• для того чтобы изготовить продукт Р ресурсу В нужно потрудиться 15 мин (см. схему), а для продукта Q придется потрудиться 2 раза по 15 мин, т.е. всего 30 мин.
• на продукте Р компания зарабатывает 45 долл/шт (шаг 1 или ячейку Н10), а на другом продукте 60 долл/шт.

Примечание: Голдратт называет эти показатели (45долл и 60долл) "генерацией дохода", которые вычисляются путем вычитания из цены продажи только стоимости покупных материалов. Однако, зарплата рабочих сюда не входит, т.к. она относятся к "операционным расходам" в целом по компании. Хитрый Голдратт не хотел связываться с монстром по имени "себестоимость продукта" и не хотел распределять все понесенные компанией за период затраты по продуктам. Подробнее об этой весьма не простой теме рассказывается в главе 5 книги. Один из аргументов чтобы не распределять ЗП по продуктам звучит так: раз мы не увольняем и не нанимаем персонал каждый раз когда меняется производственная программа, то и распределять эти затраты на продукты бессмысленно. Это просто наши общие затраты на содержание компании в какой-то период.

• разделив генерацию дохода продукта на время работы узкого места по его изготовлению, мы получим скорость генерации дохода. Для продукта Р скорость составит 3 долл/мин, а для продукта Q 2 долл/мин. Следовательно, нужно максимально загружать узкое место работой по изготовлению продукта Р, а оставшееся время пусть оно изготавливает Q.

Затраты времени на наиболее выгодный для компании продукт P составят 1500 мин (15мин*100шт). Остается 2700мин-1500мин=900мин на продукт Q. Всего можно еще изготовить 30 шт Q (900мин/30мин).

5. Наконец, вычисляем выручку (100шт * 90долл+30шт*100долл)=12 тыс. долл. Вычитаем из нее все приобретенные у других компаний материалы (5700 долл) и операционные расходы (6000долл).

Естественно, прибыль также составит 300 долл.

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