Оптимальные бизнес-планы, план по продукции, технология оптимизации

  ЛАБОРАТОРНАЯ  РАБОТА №1

  Оптимальные бизнес-планы, план по продукции,  технология оптимизации

1.1. Постановка задачи

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

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

Определение проблемы

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

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

  Перенастроили линию на серию индикаторов стадии наркоза. Выполнили две трети плана - не хватило дисплеев. Сгрузили "незавершенку" в проходы цеха.

Запустили энцефалографы - не хватило усилителей биопотенциалов и т. п.

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

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

Цель  работы

1. Научиться составлять наилучший (оптимальный) план производства продукции с учетом ограниченного обеспечения материальными ресурсами.
2. Освоить методику и технологию оптимизации планов в табличном процессоре Excel с помощью программы Поиск решения (Solver).

Выделение проблемной системы

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

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

  Возможные объемы производства зависят от обеспеченности тремя видами ресурсов: трудом, машинами и оборудованием, материалами и комплектующими.

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

Задача:

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

  Следует учитывать уменьшение удельной прибыли  при увеличении объемов производства в связи с дополнительными затратами на сбыт.

1.2. Лабораторная модель

Табличная модель

  Обычно  план по номенклатуре составляется в  виде таблицы. Вначале структура  документа вчерне составляется на бумаге или сразу в виде электронной таблицы. Пример приведен в табл. 5.1.

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

  Наименование  продукции расположено в строке 8 листа Excel (телевизор, стереосистема, акустическая система). В строке 9 расположены ячейки искомого плана. Мы должны назначить количество изделий в плане производства.

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

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

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

  Исходными данными для расчетов являются нормы  расхода комплектующих на производство одного изделия. Они задаются в матрице диапазона Dl1:F15 и готовятся технологами-нормировщиками.

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

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

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

Математическая модель

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

Плановую табл. 5.1 экономист составил почти интуитивно.

  Посмотрим на таблицу в представлении значений и формул и составим математическую модель.

Введем обозначения:

i - номер строки, ресурса;

 j - номер столбца, продукта; 

Xj - искомое плановое количество j-го продукта;

Pj - прибыль (profit) на единицу j-ro продукта;

Bi - ограниченный (boundary = граница) запас i-ro ресурса на складе;

Rij - норма расхода i-ro ресурса на единицу j-ro продукта;

Ci - плановая сумма расхода i-ro ресурса по всем продуктам;

Ci = ∑ Rij*Xj.

  В общем виде наша модель экономико-математической постановки задачи будет выглядеть следующим образом:

Целевая функция (максимизировать прибыль)

P =  ∑   Pj *Xj >max

      при ограничениях Ci <= Bi и неотрицательных количествах продуктов Xj >= 0.

Формулы табличной модели

  

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

  Переключение  листа в режим представления  формул или значений (результатов вычисления) производится в меню Сервис>Параметры>Параметры окна>формулы.

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

  В строке 17 (Прибыль по видам изделий) числа 75, 50 и 35 означают прибыль на единицу продукции, которая умножается на количество изделий по плану и корректируется возведением в степень коэффициентом уменьшения прибыли из ячейки HI5.

В строке 18 суммируется  прибыль по всей продукции из строки 17.

1.3. Программа оптимизации Поиск решения (Solver)

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

Установка программы Поиск  решения

В меню Сервис выберите команду Надстройки.

  В диалоговом окне Надстройки установите флажок Поиск решения. Если диалоговое окно Надстройки не содержит команду Поиск решения, нажмите кнопку Обзор и укажите диск и папку, в которых содержится файл надстройки Solver.xla (как правило, это папка Library\Solver folder), или запустите программу Setup, если найти файл не удается.

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

Настройка экономико-математической модели

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

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

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

  Поле  Изменяя ячейки служит для указания ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанной в поле Установить целевую ячейку. В поле Изменяя ячейки вводятся имена или адреса изменяемых ячеек, разделяя их запятыми. В нашем примере введен диапазон ячеек D9:F9, содержащий искомые величины плана производства продукции. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается установка до 200 изменяемых ячеек.

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

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

  Команда Добавить служит для отображения  диалогового окна Добавить ограничение.

  Команда Изменить служит для отображения диалогового окна Изменение ограничения.

Команда Удалить  служит для снятия указанного курсором ограничения.

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

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

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