Решения задачи средствами Microsoft Excel

Вызовите Microsoft Excel.

Введение математической модели в электронную таблицу Excel

Введите математическую модель в ячейки электронной таблицы Excel, так как показано на рисунке 1.

	A	B	C	D	E	F	G
		Ограничения		Правая часть	Формула ограничений
		x1	x2
		=15*B7+0,1*B7^2	=18*C7+0,05*C7^2			=СУММ(B3;C3)
		=12*B7+0,2*B7^2	=16*C7+0,2*C7^2			=СУММ(B4;C4)
		=17*B7+0,1*B7^2	=14*C7+0,15*C7^2			=СУММ(B5;C5)
	Целевая функция	=100*B7-0,08*B7^2	=120*C7-0,1*C7^2			=СУММ(B6;C6)
	Переменные

Рисунок 1 – Задание математической модели

В ячейки B3:B6 занесены формулы, отражающие слагаемые ограничений в левых частях и в целевой функции, содержащие переменные x1 и x2.

Для изменяемых переменных, т.е. переменных х1 и х2, которые необходимо определить, отведены ячейки B7, C7.

Поясним суть выражения в ячейке B3. В первом ограничении два первых слагаемых имеют вид 15x₁+0,1x₁². Под значение изменяемой переменной x1 отведена ячейка B7, поэтому в ячейку B3 занесено выражение 15*B7+0,1*B7^2. Аналогично занесены выражения и в другие ячейки.

В ячейках F3:F6 представлены формулы для подсчета расхода ресурсов на производство продукции в объемах x1 и x2. Так как на производство продукции первого вида в объеме х1 расходуется первого ресурса 15*B7+0,1*B7^2, а на производство продукции второго вида в объеме х2 расходуется того же ресурса 18*C7+0,05*C7^2 и эти величины находятся в ячейках B3 и C3, то суммарный расход первого ресурса занесен в ячейку F3, что отражено формулой = СУММ(B3;C3). Аналогично занесены формулы в ячейки F4 и F5. В ячейку F6 занесена суммарная прибыль от производства продукции (целевая функция).

В ячейки D3:D5 занесены запасы ресурсов.

Определение оптимального решения с помощью

Надстройки Поиск решения

Поставить курсор мыши на формулу для расчета целевой функции, которая содержится в ячейке F6.

В меню Сервис командой Поиск решения открываем диалоговое окно Поиск решения и заносим в него (рисунок 2):

- адрес ячейки целевой функции F6,

- отмечаем пункт максимизировать,

- адреса изменяемых переменных в ячейках B7, C7,

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

Рисунок 2 – Диалоговое окно Поиск решения

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

Рисунок 3 – Диалоговое окно Параметры поиск решения

В ячейках B7 и C7 представлены искомые объемы производства продукции х1 = 32 и х2= 35. Суммарная максимальная прибыль равная 7195,58 представлена в ячейке F6. В ячейках F3:F5 находится информация о суммарном расходе ресурсов при производстве оптимального количества продукции. В ячейках В3:В5 и С3:С5 находится информация о расходе ресурсов затрачиваемых на производство продукции первого и второго вида соответственно.

Рисунок 4 – Результаты поиска оптимального решения задачи

В окне Результаты поиска решения содержится тип отчета: Результаты, Устойчивость, Пределы. Для получения всех видов отчетов надо щелкнуть кнопкой мыши на каждом из них – соответствующие строчки будут закрашены – а затем на ОК. Отчеты отображаются в нижней строке Листа на экране Excel. Для их вызова необходимо щелкнуть на соответствующем отчете.

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

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

В отчете по пределам (рисунок 9) показаны нижние и верхние пределы изменения неизвестных и значения целевой функции при этих изменениях.

Рисунок 7 – Отчет по результатам

Рисунок 8 – Отчет по устойчивости

Рисунок 9 – Отчет по пределам

Индивидуальные задания

Задание 1. Небольшая фабрика изготовляет два вида красок: для наружных (№1) и внутренних (№2) работ. Продукция обоих видов поступает в оптовую продажу. Для производства красок используются два исходных продукта – А и В. Максимально возможные суточные запасы этих продуктов составляют 6 и 8 т соответственно. Расходы А и В на 1т соответствующих красок приведены в табл.

Изучение рынка сбыта показало, что суточный спрос на краску для внутренних работ (№2) никогда не превышает спрос на краску для наружных работ (№1) более чем на 1 т. Кроме того, установлено, что спрос на краску № 2 никогда не превышает 2 т в сутки.

Прибыль от реализации одной тонны красок № 1 равна 3 тыс. денежных единиц, а для краски № 2 – 2 тыс. ден. ед.

Исходный продукт	Расход исходных продуктов (в тоннах) на тонну краски	Максимально возможный запас, т
	Краска № 1	Краска № 2
А
В

Какое количество краски каждого вида должна производить фабрика, чтобы доход от реализации продукции был максимальным?

Задание 2. Пошивочное предприятие намечает выпуск двух видов костюмов – мужских и женских. На женский костюм требуется 1 м шерсти, 2м лавсана и 1чел/день трудозатрат. На мужской костюм требуется 3,5 м шерсти, 0,5 м лавсана и 1чел/день трудозатрат. Всего имеется 350 м шерсти, 240 м лавсана и 150 чел/день трудозатрат. Определить сколько костюмов каждого вида необходимо сшить, чтобы обеспечить максимальную прибыль, если прибыль от реализации женского костюма составляет 10 денежных единиц, от мужского – 20 денежных единиц. При этом следует иметь в виду, что необходимо сшить не менее 60 мужских костюмов.

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

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

Вид сырья	Нормы расхода сырья (кг) на одно изделие	Общее количество сырья (кг)
А	В
Прибыль от реализации одного изделия (руб)

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

Ресурсы	Нормы затрат ресурсов на одно изделие	Общее количество ресурсов
стол	шкаф
Древесина (м3):
1 вида	0,2	0,1
2 вида	0,1	0,3
Трудоемкость (человеко-час)	1,2	1,5	371,4
Прибыль от реализации одного изделия (руб)

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

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

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

Тип оборудования	Затраты времени (оборуд.-час) на обработку одного изделия	Общий фонд полезного рабочего времени оборудования (ч)
А	В
Фрезерное
Токарное
Шлифовальное
Прибыль от реализации одного изделия (руб)

Задание 6.На мебельной фабрике из стандартных листов фанеры необходимо вырезать заготовки трех видов в количествах, соответственно равных 24, 31 и 18 шт. Каждый лист фанеры может быть разрезан на заготовки двумя способами. Количество получаемых заготовок при данном способе раскроя приведено в табл. В ней же указана величина отходов, которые получаются при данном способе раскроя одного листа фанеры.

Вид заготовки	Количество заготовок (шт) при раскрое по способу
Величина отходов (см2)

Определить, сколько листов фанеры и по какому способу следует раскроить так, чтобы было получено не меньше нужного количества заготовок при минимальных отходах.

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

Вид корма	Количество единиц корма, которое ежедневно должны получать	Общее количество корма
Лисица	Песец
Прибыль от реализации одной шкурки (руб)

Определить, сколько лисиц и песцов следует выращивать на звероферме, чтобы прибыль от реализации их шкурок была максимальной.

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

Задача 9. Небольшая фирма производит два вида продукции: столы и стулья. Для изготовления одного стула требуется 3 фута древесины, а для изготовления одного стола – 7 футов. На изготовление одного стула уходит 2 часа рабочего времени, а на изготовление стола – 8 часов. Каждый стул приносит 1 долл. прибыли, а каждый стол – 3 долл. Сколько стульев и сколько столов должна изготовить эта фирма, если она располагает 420 футами древесины и 400 часами рабочего времени и хочет получить максимальную прибыль?

Задача 10. Некоторая фирма выпускает два набора удобрений для газонов: обычный и улучшенный. В обычный набор входит 3 фунта азот- ных, 4 фунта фосфорных и 1 фунт калийных удобрений, а в улучшенный – 2 фунта азотных, 6 фунтов фосфорных и 3 фунта калийных удобрений. Известно, что для некоторого газона требуется по меньшей мере 10 фунтов азотных, 20 фунтов фосфорных и 7 фунтов калийных удобрений. Обычный набор стоит 3 долл., а улучшенный – 4 долл. Какие и сколько наборов удобрений нужно купить, чтобы обеспечить эффективное питание почвы и минимизировать стоимость?

Задача 11. На имеющихся у фермера 400 акрах земли он планирует посеять кукурузу и сою. Сев и уборка кукурузы требует на каждый акр 200 долл. затрат, а сои – 100 долл. На покрытие расходов, связанных с севом и уборкой, фермер получил ссуду в 60 тыс. долл. Каждый акр, засеянный кукурузой, приносит 40 бушелей, а каждый акр, засеянный соей, – 80 бушелей. Фермер заключил договор на продажу, по которому каждый бушель кукурузы принесет ему 3 долл., а каждый бушель сои – 1 долл. Однако, согласно этому договору, фермер обязан хранить убранное зерно в течение нескольких месяцев на складе, максимальная вместимость которого равна 21 тыс. бушелей. Фермеру хотелось бы знать, сколько акров нужно засеять каждой из этих культур, с тем чтобы получить максимальную прибыль.

Задача 12. На заводе используется сталь трех марок: А, В, С, запасы которых равны соответственно 10, 16 и 12 ед. Завод выпускает два вида изделий. Для изделия 1 требуется по одной единице стали всех марок. Для изделия 2 требуется 2 единицы стали марки В, одна – марки С и не требуется сталь марки А. От реализации единицы изделия вида 1 завод получает 300 руб. прибыли, а вида 2 – 200 руб. Составить план выпуска продукции, дающий наибольшую прибыль

Задача 13. Производитель безалкогольных напитков располагает двумя разливочными машинами А и В по 4 шт. каждой. Машина А спроектирована для пол-литровых бутылок, а машина В – для литровых. Машина А может выпускать до 50 пол-литровых бутылок в 1 мин, а машина В – до 30 литровых бутылок в 1 мин. Каждая из машин работает ежедневно по 6 час, при пятидневной рабочей неделе. Прибыль от продажи одной пол-литровой бутылки составляет 4 цента, а одной литровой бутылки – 10 центов. Недельная продукция не может превосходить 259200 л; рынок за неделю принимает не более 288000 пол-литровых бутылок и не более 180000 литровых бутылок. Сколько бутылок пол-литровых и литровых должна выпускать каждая машина А и В за 1 мин., чтобы максимизировать недельную прибыль производителя от продажи безалкогольных напитков, при имеющихся средствах.

Задание 14. Предприятие выпускает два вида продукции. На изготовление продукции затрачивается два вида ресурсов. Запасы ресурсов 1-го вида составляют 160 ед., 2-го вида 210 ед. Нормы расхода 1-го ресурса, идущего на изготовление единицы продукции, равны 2 ед. для продукции 1-го вида и 2,67 ед. – для продукции 2-го вида; нормы расхода 2-го ресурса составляют 3 ед. для продукции 1-го вида и 2 ед. – для продукции 2-го вида. Суммарный объем выпуска должен быть не менее 40 ед.

Затраты на изготовление единицы продукции определяются выражениями _,, где – искомый объем производства продукции j-го вида (j = 1, 2); – себестоимость продукции j-го вида; – коэффициент снижения затрат с ростом объема производства. = 100 ден. ед., = 140 ден. ед., .

Составить математическую модель задачи и найти объемы производства продукции 1 и 2 вида, при которых суммарные затраты при производстве минимальны.

Задание 15. Предприятие может изготовить 200 изделий двумя технологическими способами производства. При производстве одного изделия первым способом себестоимость производства равна , а вторым способом , где – объемы производства продукции по 1-му и 2-му способам.

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

Задание 16. Предприятие производит продукцию по двум технологическим способам производства. Для производства продукции используется сырье двух видов, объемы которых у предприятия составляют = 186 ед., = 210 ед.

Оптовая цена единицы продукции по 1-му и 2-му способам производства составляют Р₁ = 52 ден.ед. и Р₂ = 68 ден.ед.

Себестоимость производства по 1-му и 2-му способам определяется выражениями , j = 1, 2, где .

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

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

Задание 17. Предприятие может изготовить 141 изделие и для этого использует две технологические линии. При производстве одного изделия на первой линии себестоимость производства равна , а на второй линии – равна , где – объемы производства продукции на 1-ой и 2-ой линиях.

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

Задание 18. По плану производства продукции предприятию необходимо изготовить 180 изделий. Эти изделия могут быть изготовлены двумя способами. При производстве изделий способом 1 затраты определяются выражением руб, а при изготовлении изделий способом 2 затраты определяются выражением руб.

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

Задание 19. Фирма реализует автомобили через магазин и торговых агентов.

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

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

Задание 20. Средние ежедневные расходы ресторана на рекламу составляют 100$, которые затрачиваются на рекламные объявления в газете и по радио. Введем обозначения: – сумма, затрачиваемая в день на рекламу в газете и – на рекламу на радио.

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

.

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

Задание 21. Компания производит две марки телевизоров “Рекорд“ и “Cosmo“. Производственные мощности компании таковы, что объем производства телевизоров “Рекорд“ составляет не более 70 телевизоров в день, а для телевизоров “Cosmo“ – не более 50 телевизоров в день.

На производство одной электронно-лучевой трубки для телевизоров затрачивается время в количестве 1 час для телевизоров “Рекорд“ и 2 час – для телевизоров “Cosmo“, причем производству трубок для обоих телевизоров может быть уделено не более 120 часов рабочего времени в день.

Для производства одного корпуса для телевизоров “Рекорд“ и “Cosmo“ требуется по одному часу, причем на производство корпусов обоих телевизоров может быть затрачено не более 90 часов рабочего времени в день.

Цены продаж одного телевизора описываются выражениями

- телевизора “Рекорд“ ;

- телевизора “Cosmo“ ,

где – ежедневный выпуск телевизоров “Рекорд“ и “Cosmo“.

Известно также, что затраты на производство одного телевизора “Рекорд“ и “Cosmo“ составляют 210$ и 230$ соответственно.

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

Задача 22. Инвестор, имеющий Р = 1000$, может вложить их в два вида ценных бумаг.

Ожидаемый годовой доход от каждого вида ценных бумаг 1 и 2 составляет R1 = 0,06 и R2 = 0,02 соответственно; верхние границы инвестиций в ценные бумаги 1 и 2 равны S1 = 0,75 и S2 = 0,9 соответственно; нижняя граница ожидаемого годового дохода от всех инвестиций равна b = 0,03.

Дисперсии годового дохода от ценных бумаг 1 и 2 равны 0,09 и 0,06, ковариация годового дохода от ценных бумаг 1 и 2 равна .

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

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

Задача 23. Пусть заданы рыночные цены трех товаров p1 = 2$, p2 = 2,8$ и p3 = 2,8$ и личный бюджет некоего субъекта, в количестве I = 250$.

Полезность потребительского набора, которую субъект извлекает из потребления единиц товара 1, единиц товара 2 и единиц товара 3, измеряется функцией полезности

.

Составить математическую модель и определить потребительскую корзину, которая позволит субъекту получить максимальную пользу при соблюдении бюджетного ограничения субъекта.

Задача 24. Пусть заданы рыночные цены трех товаров p1 = 1,2$, p2 = 4,5$ и p3 = 2,3$ и личный бюджет некоего субъекта, в количестве I = 1450$.

Полезность потребительского набора, которую субъект извлекает из потребления единиц товара 1, единиц товара 2 и единиц товара 3, измеряется функцией полезности

.

Составить математическую модель и определить потребительскую корзину, которая позволит субъекту получить максимальную пользу при соблюдении бюджетного ограничения субъекта.

Задача 25. Личный бюджет Иванова И.И. составляет 1900$. Основные продукты его ежедневного потребления 1, 2 и 3 имеют цены на рынке равные p1 = 1,2$, p2 = 2,5$ и p3 = 0,8$. Минимальные ежедневные потребляемые количества товаров каждого вида составляют a1 = 3 ед., a2 = 6 ед., a3 = 8 ед. соответственно.

Полезность потребительского набора, которую субъект извлекает из потребления единиц товара 1, единиц товара 2 и единиц товара 3, измеряется функцией полезности Стоуна

.

Составить математическую модель и определить потребительскую корзину, которая позволит субъекту получить максимальную пользу при соблюдении бюджетного ограничения субъекта.

Литература

1. Костевич Л.С. Математическое программирования. – Минск: Новое знание, 2003.

2. Орлова И.В. Экономико-математическое моделирование: Практическое пособие по решению задач. – М.: Вузовский учебник, 2004.

3. Замков О.О., Толстопятенко А.В., Черемных Ю.Н. Математические методы в экономике. – М.: ДИС, 1997.

4. Мур Дж., Уэдерфорд Л. и др. Экономическое моделирование в Microsoft Excel. – М.: Вильямс, 2004.

5. Таха Х. Введение в исследование операций, в 2-х книгах. Пер. с англ.. – М.: Мир, 1985

6. Акулич И.Л. Математическое программирование в примерах и задачах. – М.: Высш. Шк., 1986

7. Шикин Е.В., Чхартишвили А.Г. Математические методы и модели в управлении. – М., Дело, 2002

8. Банди Б. Основы линейного программирования. Пер. с англ. – М.: Радио и связь, 1989.