Международный экономический форум 2013

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

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

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

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

-->

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выражение

Знак отношения

Ресурс

Примечание

Х1

>=

150

(выполнение договорных поставок)

Сумки женские

Х2

>=

70

Сумки мужские

Х3

>=

50

Сумки дорожные

Х4

>=

30

Сумки спортивные

Х1,Х2,Х3,Х4

Целые

Доли сумок не выпускаются

0,5Х1

<=

75

(ограничение на расход материалов)

Кожа

0,3Х2+1,5Х3+Х4

<=

150

Кожзаменитель

0,6Х1+0,4Х2+1,7Х3+1,5Х4

<=

300

Подкладочная ткань

20Х1+10Х2+30Х3+25Х4

<=

8000

Нитки

4Х1+5Х2+3Х3+6Х4

<=

1500

Фурнитура-молнии

2Х1+2Х2+2Х3+6Х4

<=

800

Фурнитура-пряжки

2Х1+2Х2+4Х3+6Х4

<=

1000

Фурнитура-разная

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

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

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

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

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

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

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

Литература:

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

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