Серкебаева Л.Т.

Оптимизация производственных моделей в MS Excel

Костанайский государственный университет имени А.Байтурсынова, Республика  Казахстан

Оптимизация производственных моделей в MS Excel

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

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

- константы нормативных затрат материалов, труда и финансов;

- переменные решения;

- целевую функцию;

- параметры спроса на продукцию.

Оптимизация модели производственного плана состоит в поиске максимума (для прибыли) или минимума (для затрат) целевой функции при ограничении на спрос и ресурсы.

Рассмотрим следующую задачу:

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

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

- сумка женская – 150 шт. при оптовой цене 3000 тенге;

- сумка дорожная – 50 шт. при оптовой цене 2000 тенге;

- сумка спортивная – 30 шт. при оптовой цене 1200 тенге.

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

Основная формула математической модели будет иметь следующий вид:

«Количество продукции» * «Нормативный расход материалов» = «Затраты материалов» <= «Ресурс (запасы)»

Оптимальному решению задачи отвечает максимальное значение целевой функции при следующих условиях и ограничениях:

Оформим исходные данные задачи в электронной таблице Microsoft Excel.

Рисунок 1 – Оформление задачи в Microsoft Excel.

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

Рисунок 2 – окно «Поиск решения»

После выполнения команды «Выполнить»  будет выдан результат расчета: значения искомых переменных  и соответствующий расход материалов (рисунок 3).

Рисунок 3 – Результат решения

Таким образом, мы нашли, что максимально возможная выручка может составить 677500 тенге. Для этого сверх договорных поставок требуется изготовить 35 мужских сумок и 9 дорожных. При этом на складе останется запас материалов, кроме кожи и кожзаменителя, которые будут израсходованы полностью.  

Литература:

1. Минько А.А Принятие решений с помощью Excel. Просто, как дважды два М.: Эксмо, 2007

2. Козлов, А. Ю., Пакет анализа MS Excel в экономико-статистических расчетах: учеб. пособие для вузов / А. Ю. Козлов, В. Ф. Шишов ; под ред. проф. В. С. Мхитаряна. – М.: ЮНИТИ-ДАНА, 2003.