ЛАБА 1 ИТЭ

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

Решение задач оптимизации с помощью Excel

Цель работы: овладение методами линейного программирования, нахождение экстремума линейных функций.

Задание к работе:

Работу выполнил:

Работу защитил:

Гунин К.Д. ЗЭУ(2)-111

Теоретическое введение:

Основные возможности обработки экономической информа­ции на основе табличного процессора Excel

Электронные таблицы Excel предназначены для упорядочения и обработки различных типов данных. Excel позволяет:

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

• осуществлять табличную обработку данных и представлять результаты расчетов в виде графиков и диаграмм;

• планировать и распределять ресурсы;

• составлять статистические сводки и калькуляции, проводить статистические финансовые расчеты.

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

В Excel реализованы методы математического программирования для решения задач линейного и нелинейного программирования.

Задачи линейного программирования относятся к числу наиболее час­то решаемых задач оптимизации.

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

Оптимизация - определение значений экономических показателей, при которых достигается оптимум, то есть оптимальное, наилучшее состояние системы. Чаще всего оптимуму соответствует достижение наивысшего результата при данных затратах ресурсов или достижение заданного результата при минимальных ресурсных затратах.

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

Методы, посредством которых решают задачи оптимизации, подразделяются на виды, соответствующие задачам, к которым они применяются:

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

• В противном случае имеют дело с задачей нелинейного программирования и применяют соответствующие методы.

Помимо того, оптимизационные методы делятся на следующие группы:

• аналитические методы;

• численные методы;

• графические методы.

Целевая функция в экстремальных задачах — функция, минимум или максимум которой требуется найти. Это ключевое понятие оптимального программирования. Найдя экстремум целевой функции и, следовательно, определив значения управляемых переменных, которые к нему приводят, мы тем самым находим оптимальное решение задачи. Таким образом, целевая функция выступает как критерий оптимальности решения задачи.

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

Задача №1

Для изготовления трех видов изделий А, В, С используется токарное, сварочное и шлифовальное оборудование. Затраты времени на обработку одного изделия для каждого из типов оборудования указаны в таблице 3. В ней же указан общий фонд рабочего времени каждого из типов используемого оборудования, а также прибыль от реализации одного изде­лия каждого вида.

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

Оборудование	Расход времени на 1 деталь			Норма времени	Расход времени
	А	В	С
Фрезерное	2	4	5	120	120
Токарное	1	8	6	280	168
Сварочное	7	4	5	240	240
Шлифовальное	4	6	7	360	204
Прибыль	10	14	12
Количество деталей:	24	18	0	Общая прибыль:	492

Microsoft Excel 11.0 Отчет по результатам
Рабочий лист: [зАДАЧА123.xls]Лист1
Отчет создан: 24.03.2012 8:43:22
Целевая ячейка (Максимум)
	Ячейка	Имя	Исходное значение	Результат
	$F$9	Общая прибыль: Расход времени	492	492
Изменяемые ячейки
	Ячейка	Имя	Исходное значение	Результат
	$B$9	Количество деталей: А	24	24
	$C$9	Количество деталей: В	18	18
	$D$9	Количество деталей: С	0	0
Ограничения
	Ячейка	Имя	Значение	Формула	Статус	Разница
	$F$3	Фезерное Расход времени	120	$F$3<=$E$3	связанное	0
	$F$4	Токарное Расход времени	168	$F$4<=$E$4	не связан.	112
	$F$5	Сварочное Расход времени	240	$F$5<=$E$5	связанное	0
	$F$6	Шлифовальное Расход времени	204	$F$6<=$E$6	не связан.	156
	$B$9	Количество деталей: А	24	$B$9>=0	не связан.	24
	$C$9	Количество деталей: В	18	$C$9>=0	не связан.	18
	$D$9	Количество деталей: С	0	$D$9>=0	связанное	0
	$B$9	Количество деталей: А	24	$B$9=целое	связанное	0
	$C$9	Количество деталей: В	18	$C$9=целое	связанное	0
	$D$9	Количество деталей: С	0	$D$9=целое	связанное	0

Предприятие будет получать максимальную прибыль при изготовлении 24 шт. деталей А, 18 шт. деталей B и отказалось от изготовления деталей С.

Задача №2

Компания производит полки для ванных комнат двух разме­ров - А и В. Агенты по продаже считают, что в неделю на рынке может быть реализовано до 550 полок. Для каждой полки типа А требуется 2 м материа­ла, а для полки типа В 3 м материала. Компания может получить до 1200 м материала в неделю. Для изготовления одной полки типа А требуется 12 мин машинного времени, а для одной полки типа В - 30 мин; ресурс машинного времени в неделю составляет 160 ч. Если прибыль от продажи полок типа А составляет 3$, а от типа В - 4$, то сколько полок каждого типа следует выпускать в неделю.

	Вид продукции:		Норма затрат:	Затраты:
Вид затрат:	А	В
Материал	2	3	1200	1200
Время	12	30	9600	8400
Прибыль:	3	4		1750
			Норма выпуска:
Выпуск каждой детали:	450	100	550
Общий выпуск:	550

Microsoft Excel 11.0 Отчет по результатам
Рабочий лист: [зАДАЧА123.xls]Лист2
Отчет создан: 24.03.2012 8:50:54
Целевая ячейка (Максимум)
	Ячейка	Имя	Исходное значение	Результат
	$E$9	Прибыль: Затраты:	1750	1750
Изменяемые ячейки
	Ячейка	Имя	Исходное значение	Результат
	$B$11	Выпуск каждой детали: А	450	450
	$C$11	Выпуск каждой детали: В	100	100
Ограничения
	Ячейка	Имя	Значение	Формула	Статус	Разница
	$B$12	Общий выпуск: А	550	$B$12<=$D$11	связанное	0
	$E$7	Материал Затраты:	1200	$E$7<=$D$7	связанное	0
	$E$8	Время Затраты:	8400	$E$8<=$D$8	не связан.	1200
	$C$11	Выпуск каждой детали: В	100	$C$11=целое	связанное	0
	$B$11	Выпуск каждой детали: А	450	$B$11=целое	связанное	0
	$B$11	Выпуск каждой детали: А	450	$B$11>=0	не связан.	450
	$C$11	Выпуск каждой детали: В	100	$C$11>=0	не связан.	100

Оптимальный объем выпуска полок за неделю предприятием типа А -450 шт, типа В - 100шт. в пределах недельной нормы 550 шт, при этом предприятие будет получать максимальную прибыль от продажи полок.

Задача №3

Автозавод выпускает две модели: «Каприз» и «Фиаско». На заводе работает 1 ООО неквалифицированных и 800 квалифицированных ра­бочих, каждому из которых оплачивается 40 часов в неделю. Для изготовле­ния модели «Каприз» требуется 30 ч неквалифицированного труда и 50 ч квалифицированного труда; для «Фиаско» соответственно 40 и 20 ч. Каждая модель «Фиаско» требует затрат в размере 500$ на сырье и комплектующие изделия, «Каприз» 1 500$; суммарные затраты не должны превосходить 900 000 $ в неделю. Рабочие, осуществляющие доставку, работают по 5 дней в неделю и могут забрать с завода не более 210 машин в день. Каждая мо­дель «Каприз» приносит фирме 1 000$ прибыли, «Фиаско» 500$. Какой объем выпуска каждой модели Вы бы порекомендовали?

	Каприз	Фиаско	Факт	Ресурс
затраты на сырье и комплетующие	1500	500	891000	900000
доставка			1050	1050
квал труд	50	20	31980	32000
невкал труд	30	40	38340	40000
прибыль	1000	500	708000
единицы	366	684

Microsoft Excel 11.0 Отчет по результатам
Рабочий лист: [Книга1]Лист3
Отчет создан: 24.03.2012 8:58:30
Целевая ячейка (Максимум)
	Ячейка	Имя	Исходное значение	Результат
	$D$6	прибыль Факт	4500	708000
Изменяемые ячейки
	Ячейка	Имя	Исходное значение	Результат
	$B$8	единицы Каприз	2	366
	$C$8	единицы Фиаско	5	684
Ограничения
	Ячейка	Имя	Значение	Формула	Статус	Разница
	$D$2	затраты на сырье и комплетующие Факт	891000	$D$2<=$E$2	не связан.	9000
	$D$3	доставка Факт	1050	$D$3<=$E$3	связанное	0
	$D$4	квал труд Факт	31980	$D$4<=$E$4	не связан.	20
	$D$5	невкал труд Факт	38340	$D$5<=$E$5	не связан.	1660
	$B$8	единицы Каприз	366	$B$8=целое	связанное	0
	$C$8	единицы Фиаско	684	$C$8=целое	связанное	0
	$B$8	единицы Каприз	366	$B$8>=0	не связан.	366
	$C$8	единицы Фиаско	684	$C$8>=0	не связан.	684

Исходя из возможностей автозавода (фонд рабочего времени за неделю, возможности затрат

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

марки Каприз составит 366 ед., марки Фигаро - 684 ед.