Перейти к содержимому

Решение задачи линейного программирования в Excel

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

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

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

Задача. Николай Кузнецов управляет небольшим механическим заводом. В будущем месяце он планирует изготавливать два продукта (А и В), по которым удельная маржинальная прибыль оценивается в 2500 и 3500 руб., соответственно. Изготовление обоих продуктов требует затрат на машинную обработку, сырье и труд. На изготовление каждой единицы продукта А отводится 3 часа машинной обработки, 16 единиц сырья и 6 единиц труда. Соответствующие требования к единице продукта В составляют 10, 4 и 6. Николай прогнозирует, что в следующем месяце он может предоставить 330 часов машинной обработки, 400 единиц сырья и 240 единиц труда. Технология производственного процесса такова, что не менее 12 единиц продукта В необходимо изготавливать в каждый конкретный месяц. Необходимо определить количество единиц продуктов А и В, которые Николай доложен производить в следующем месяце для максимизации маржинальной прибыли.

Подробнее »Решение задачи линейного программирования в Excel

Решение оптимизационных задач управления методом линейного программирования

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

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

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

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

Принятие решений с учетом ограничивающих факторов

Ограничивающий фактор – любой фактор, предложение которого ограничено и это сдерживает дальнейшее развитие компании, то есть, ограничивает ее деятельность [1].

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

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

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

Подробнее »Принятие решений с учетом ограничивающих факторов

3.2. Введение в управленческий учет теории ограничений Голдратта

Настоящая заметка написана в рамках курса Современный управленческий учет. Ранее я описал, как традиционный подход к учету затрат трансформировался под влиянием современных тенденций в бизнесе. Среди подходов, наиболее адекватно отвечающих текущим вызовам в управлении затратами, можно выделить теорию ограничений (ТОС). Концепция была впервые сформулирована и развита Элияху Голдраттом в книге Цель, вышедшей в США в 1984 году. Теория была подхвачена и преобразована в систему учета в Великобритании, где стала известна под названием учета пропускной способности [1]. Голдратт развил теорию и инструменты, помогающие менеджерам повышать прибыль компаний, ориентируясь на системный подход. Этот подход подразумевает фокус на глобальные (для всей компании) параметры управленческого учета. В отличие от господствовавшего до последнего времени фокуса на измерение локальной эффективности отдельных цехов, участков, рабочих мест. ТОС сосредотачивает внимание на ограничениях («узких местах») организации, которые тормозят темпы производства. Основная идея ТОС заключается в том, чтобы максимально повысить темпы производства и продажи, то есть пропускную способность организации.

Важная концепция, лежащая в основе теории ограничений, заключается в том, что темп производства компании зависят от темпов работы в «узком месте» / бутылочном горлышке / ограничивающем ресурсе. Для достижения наилучших результатов, ТОС делает акцент на важности управления ограничениями. Если они не могут быть устранены, ими нужно грамотно управлять, чтобы максимально снизить влияние ограничений на суммарный объем производства.

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

Рис. 1. Графическое описание основного бизнес-процесса

Подробнее »3.2. Введение в управленческий учет теории ограничений Голдратта

Excel. Формула адреса ячейки с максимальным (минимальным) значением в диапазоне

Формулы массива с каждым днем нравятся мне всё больше и больше! 🙂 Недавно у меня возникла задача – найти адрес ячейки, содержащей максимальное значение в диапазоне. Для начала я создал тестовый диапазон А1:F10 (рис. 1), заполнив его случайными целыми числами от 1 до 100 с помощью функции =СЛУЧМЕЖДУ(1;100).

Рис. 1. Исходный диапазон

Подробнее »Excel. Формула адреса ячейки с максимальным (минимальным) значением в диапазоне

3.1. Системы учета затрат: традиционный и современный подходы

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

Современное производство существенным образом отличается от того, что было 30–50 лет назад. Значительно сократился жизненный цикл продуктов, что смещает акцент в конкурентной борьбе в сторону важности быстрого вывода новых продуктов на рынок. Расширились и ассортиментные линейки многих продуктов, что также изменяет фокус с партий большого объема, на мелкосерийное производство широкой номенклатуры товаров. Вслед за производственными системами развивается и управленческий учет и, в первую очередь, учет затрат, предоставляя менеджерам информацию, которая им нужна для принятия решений в современных условиях ведения бизнеса. [1]

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

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

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

Традиционную философию производства можно охарактеризовать следующими афоризмами:

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

Подробнее »3.1. Системы учета затрат: традиционный и современный подходы

Том Дэвенпорт и Джон Харрис. Аналитика как конкурентное преимущество

«Превратить данные в знания», «Заставить идеи работать» – всё это так и останется прекрасными лозунгами, если компания не осознает роль бизнес-аналитики в получении конкурентного преимущества. Сегодня, если воспользоваться терминологией Дарвина, происходит «резкое изменение среды обитания» и условий в мировой экономике. Сейчас неэффективным и медлительным компаниям, не готовым к переменам, просто не остаётся ни единого шанса на выживание. Компании должны эволюционировать, их важнейшими активами должны стать знания и информация.

Предлагаю вашему вниманию краткий конспект книги: Том Дэвенпорт и Джон Харрис. Аналитика как конкурентное преимущество. Новая наука побеждать. – СПб: BestBusinessBooks, 2010. – 264 с.

Подробнее »Том Дэвенпорт и Джон Харрис. Аналитика как конкурентное преимущество

Excel. Как найти циклическую ссылку

Если при открытии Excel-файла появляется предупреждение о циклической ссылке

нажмите «Отмена», и после открытия файла перейдите на закладку «Формулы» в область «Зависимости формул».

Подробнее »Excel. Как найти циклическую ссылку

Малые выборки в конкурентной разведке

Не так давно я прочитал замечательную книгу Дугласа Хаббарда «Как измерить всё, что угодно. Оценка стоимости нематериального в бизнесе». Несколько тем меня заинтересовали, и я решил вернуться к их более подробному изучению позже. Сегодня – одна из таких тем: определение количества элементов генеральной совокупности на основании наблюдения нескольких номеров выборки. Например, имея на руках несколько счетов (накладных) конкурента за определенный период, можно определить количество всех счетов, выписанных им за этот период. По номерам билетов метро (автобуса, электрички) можно оценить число перевозимых пассажиров.

Для начала фрагмент упомянутой книги Хаббарда.

* * *

Серийная выборка

В учебниках по статистике метод серийной выборки [1] обычно не освещается. Не обсуждали бы его и мы, называйся наша книга «Как измерить большинство вещей». Но в период Второй мировой войны данный подход широко использовался для сбора разведывательной информации [2]. Он может пригодиться и сегодня для решения некоторых бизнес-проблем. В годы Второй мировой разведчики союзников собирали сведения о производстве вражеской военной техники, в том числе немецких танков «Mark V».[3]

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

Подробнее »Малые выборки в конкурентной разведке

Excel. Суммирование по диапазону, содержащему ошибку, с помощью формулы массива

Ранее я описал, как использовать формулы массива:

Кто не знаком с формулами массива, предлагаю сначала почитать Введение в формулы массива. Рекомендую также Джон Уокенбах «Формулы в Microsoft Excel 2010», а именно часть IV. Формулы массивов (глава 14. Знакомство с массивами; глава 15. Магия формул массива).

Наверное, вы сталкивались с тем, что функция СУММ не работает, если ее применить к диапазону, в котором содержатся ошибки, например, #Н/Д, #ЗНАЧ! или #ДЕЛ/0!

Подробнее »Excel. Суммирование по диапазону, содержащему ошибку, с помощью формулы массива