Использование надстройки «Поиск решения» для решения экономических задач

Практическое занятие № 4

Использование надстройки «Поиск решения» для решения экономических задач

Цель занятия:

Приобретение практических навыков работы с табличным процессором

Microsoft Excel

Задание 1. Подбор параметров

ПОСТАНОВКА ЗАДАЧИ

Определить величину ежемесячной выплаты займа в р., если он взят на 36 месяцев при процентной ставке, равной 50%.

РЕШЕНИЕ ЗАДАЧИ МЕТОДОМ ПОДБОРА ПАРАМЕТРОВ

1. Введите следующую таблицу. В ячейку В4 введите значение 0,5 и установите тип Процент, с помощью кнопки Процентный стиль панели инструментов Форматирование. В ячейку В5 — 36 (например).

Изучите по Справке назначение и формат функции ПЛТ.

2. В ячейку В6 введите формулу

=ПЛТ(В4/12;В5;-В3)

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

3. С помощью команды Сервис — Подбор параметра осуществите подбор такого количества ежемесячных выплат, при котором каждая выплата составит 1300 р.:

-  выделите ячейку Вб;

-  Сервис — Подбор параметра — появится диалоговое окно;

-  в поле Установить в ячейке введите $В$6; S в поле Значение введите 1300;

-  в поле Изменяя ячейку введите $В$5.

Нажав ОК, получите в ячейке В5 значение.

Задание 2. Поиск решения. Уменьшение затрат на перевозку грузов

ПОСТАНОВКА ЗАДАЧИ

Допустим, что фирма занимается переработкой мяса на нескольких заводах, расположенных в разных районах Москвы. Мясо поставляется объединениями фермеров со складов, расположенных в нескольких городах Московская обл." href="/text/category/moskovskaya_obl_/" rel="bookmark">Московской области. Стоимость мяса одинаковая, однако перевозка со склада на завод зависит от расстояния и отличается для каждого склада и завода. Потребность заводов в мясе различна, и запасы на каждом складе ограничены. Требуется определить: с какого склада, на какой завод поставлять, сколько мяса для минимизации общих затрат на перевозку.

ПОРЯДОК ВЫПОЛНЕНИЯ

Создайте на листе Транспортные расходы таблицу. С этой целью:

-  В ячейку А1 введите текст «Оптимизация транспортных потоков».

-  В ячейку В2 введите текст «Потребители->».

-  В ячейки C2:F2 введите названия мясоперерабатывающих заводов.

-  В ячейку A3 введите текст «Поставщики».

-  В ячейки А4:А8 названия складов.

-  Установите курсор в ячейку В4 и нажмите кнопку å, после чего выделите ячейки с С4 по Е4. В стоке формул появится формула =CУMM(C4:F4). Нажмите кнопку - J, расположенную справа в строке формул, и формула будет введена.

-  Скопируйте содержимое ячейки В4 в ячейки В5:В8.

-  Выделите ячейки с С4 до F8. Введите цифру 1 и нажмите кнопку ч/ Нажмите комбинацию клавиш Ctrl+D (автозаполнение

	А	В		С	D	F
1	Оптимизация транспортных потопов
2		Потребители >	Завод Лужники	Завод Сокол	Завод Изглайлово	Завод Юго-Запад
3	Поставщики
4	Склад Наро-Фоминск	4	1.00	1,00	1.00	1.00
5	Стад Солнечногорск	4	1.00	1.00	1,00	1.00
6	Склад Домодедово	4	1.00	1,00	1.00	1,00
7	Склад Балашиха	4	1.00	1,00	1.00	1.00
8	Склад Ногинск	4	1,00	1.00	1.00	1.00
9	Факт	5.00	5,00	5.00	5.00
10	Запросы	240.00	115,00	280,00	370,00
11	Склад Наро-Фоминск	300	47000,00	41500,00	45000.00	32650.00
12	Склад Солнечногорск		240	39000.00	32300,00	38000,00	41000,00
13	Склад Домодедово		170	23650,00	27300,00	21000,00	18000,00
14	Склад Балашиха		120	19500,00	19400,00	9000.00	24000.00
15	Склад Ногинск		320	39000.00	36000,00	27500,00	44000,00
16	Всего:		624800	168150	156500	140500	159650
17
18	Всего на перевозку требуется			0,6248 млн руб.

столбцов в выделенной области), а затем нажмите Ctrl+R (автозаполнение строк в выделенной области). Все выделенные ячейки будут заполнены единицами. Установите формат ячеек выделенной области Числовой. S В ячейку В9 введите текст «Факт->».

-  В ячейку С9 введите формулу =СУММ(С4:С8). Скопируйте формулу в ячейки D9:F9. Подготовка первой части таблицы закончена. Каждое значение в ячейках на пересечении столбца конкретного завода и строки склада означает количество тонн, поставляемых в месяц с этого склада на данный завод. В нижней строке суммируется общее количество мяса, поставляемого на определенный завод, во втором столбце суммируется общее количество закупленного у конкретного склада мяса.

Введите требуемые объемы поставок и цены поставок. С этой целью:

-  Введите в ячейку В10 текст «Запросы ->». В десятой строке вводятся значения потребляемого каждым из заводов мяса в тоннах.

-  В ячейки этой строки введите соответственно:

В11	300
В12	240
В13	170
В14	120
В15	320
СЮ	240
D10	115
ЕЮ	280
F10	370

-  Выделите ячейки с А4 по А8. Нажмите клавишу CTRL и, не отпуская ее, подведите курсор мыши к краю выделенного интервала, нажмите левую клавишу мыши и двигайте мышь. Появится серый прямоугольник размером с выделенную область. Расположите его в ячейки с А1 1 по А1 5, затем отпустите клавишу мыши и клавишу CTRL. Названия складов будут скопированы.

-  В ячейки второго столбца занесите объемы месячных запасов на различных складах в тоннах соответственно.

-  В ячейки с С11 по F15 занесите стоимость перевозки тонны мяса с

конкретного склада на конкретный завод. Для этого введите в ячейки с С11 по F15 следующие данные:

47000	41500	45000	32650
39000	32300	38000	41000
23650	27300	21000	18000
19500	19400	9000	24000
39000	36000	27500	44000

-  В ячейку А1 б введите текст «Всего».

-  В ячейку С16 введите формулу =С4*С11+С5*С12+С6*С13+ С7*С14+С8*С15.

В ячейке С4 находится количество мяса, перевозимого со склада в Наро-Фоминске на завод в Лужниках, а в ячейке С11 — цена перевозки тонны груза по этому маршруту. Соответственно, первое слагаемое в формуле означает полную стоимость перевозок поданному маршруту. Вся же формула вычисляет полную стоимость перевозок мяса на завод в Лужниках.

-  Скопируйте формулу из ячейки О б в ячейки D16:F1 б.

-  В ячейку В1 б введите формулу = СУММ(С1 6:F1 б). В данной

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

-  В ячейку А18 введите текст «Всего на перевозки требуется», а в

-  ячейку Е18 —«млн. руб.».

-  Для вычисления суммы в миллионах в ячейку введите формулу =В16/1000000.

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

Выполнить поиск решения (Сервис — Поиск решения) с целью определения минимальных затрат на перевозки при соблюдении следующих условий:

-  Объем поставок с конкретного склада должен быть меньше или равен запасам на складе.

-  Объем перевозок не должен быть отрицательным.

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

	А	В	C	D	E	F
1	Оптимизация транспортных потоков
2	Потребители->	Завод Лужники	Завод Сокол	Завод Измайлов	Завод Юго-Запад
3	Поставщики
4	Склад Наро-Фоминск	300	0,00	0,00	0,00	300,00
5	Склад Солнечногорск	135	20,00	115.00	0,00	0,00
6	Склад Домодедово	170	100,00	0,00	0,00	70,00
7	Склад Балашиха	120	120.00	0,00	0.00	0,00
8	Склад Ногинск	280	0,00	0.00	280.00	0,00
9	Факт->		240.00	115.00	280.00	370X30
10	Запросы-*		240,00	115,00	280,00	370,00
11	Склад Наро-Фоминск	300	47000.00	41500,00	45000,00	32650,00
12	Склад Солнечногорск	240	39000,00	32300,00	38000,00	41000,00
13	Склад Домодедово	170	23650,00	27300,00	21000,00	18000.00
14	Склад Балашиха	120	19500,00	19400,00	9000,00	24000,00
15	Склад Ногинск	320	39000,00	36000,00	27500,00	44000,00
16	всего:		5485000	3714500	7700000

Задача 1. Фирма производит две модели А и В сборных книжных полок. Их производство ограничено наличием сырья (досок) и временем машинной обработки. Для каждого изделия модели А требуется 3 м2 досок, а изделия модели В – 4 м2 . Фирма может получить от своих поставщиков до 1700 м2 досок в неделю. Для каждого изделия модели А требуется 12 минут машинного времени, а для изделия модели В – 30 минут. В неделю можно использовать 160 часов машинного времени. Сколько изделий каждой модели следует выпускать в неделю, если каждое изделие модели А приносит прибыль 20 рублей, а изделие модели В 40 рублей прибыли.

Задача № 2 Фирма производит три вида продукции (А, В, С), для выпуска каждого из ко­торых требуется определенное время обработки на всех четырех устройствах I, II, III, IV.

Вид продукции	Время обработки, ч	Прибыль, рубл.
	I	II	III	IV
А	1	3	1	2	30
В	6	1	3	3	60
С	3	3	2	4	40

Пусть время работы на устройствах - соответственно 84, 42, 21 и 42 ч. Опреде­лите, какую продукцию и, в каких количествах следует производить. (Можете предположить, что рынок сбыта для каждого продукта неограничен; временем, тре­буемым для переключения устройства в зависимости от вида продукции, можно пренебречь; рассмотрите только задачу максимизации прибыли.)

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

Модель радиатора	А	В	С	D	Ограничение
Необходимое количество рабочей силы, человеко-часы	0,5	1,5	2	1,5	500
Необходимое количество стального листа, м2	4	2	6	8	2500
Прибыль от продажи одного радиатора, рубл.	5	5	12,5	10

Задача № 4 Фирма занимается составлением диеты, содержащей, по крайней мере, 20 единиц белков, 30 единиц углеводов, 10 единиц жиров и 40 единиц витаминов. Как дешев­ле всего достичь этого при указанных в таблице ценах на 1 кг (или 1 л) пяти имеющихся продуктов?

	Хлеб	Соя	Сушеная рыба	Фрукты	Молоко
Белки	2	12	10	1	2
Углеводы	12	0	0	4	3
Жиры	1	8	3	0	4
Витамины	2	2	4	6	2
Цена	12	36	32	18	10

Задача № 5 Фабрика производит три основных типа товара. Изделию типа I требуется 3 единицы сырья А и единица сырья В; оно приносит прибыль в 3 единицы. Изделию типа
II требуется 4 единицы сырья А и 3 единицы сырья В; оно приносит прибыль в 6
единиц. Изделию типа III требуется единица сырья А и 2 единицы сырья В; оно приносит прибыль в 2 единицы. Найдите оптимальный план производства, если доступны всего 20 единиц сырья А и 10 единиц сырья В. Если окажется доступной еще одна
единица сырья А (или В), какую наибольшую цену следует за нее платить?

Задача № 6 Прибыль от изделий А, В, С составляет соответственно 3, 4, 5 единиц. Для каждого изделия требуется время использования станка I и II, которые доступны соответственно 1 2 и 15 ч в день:

	А	В	С
I	3	2	3
II	4	1	2

Найдите оптимальный план производства. Назначьте дополнительное время исполь­зования станка I (II).

Задача № 7 Фирма специализируется на производстве буфетов. Она может производить три типа буфетов А, В, С, что требует различных затрат труда на каждой стадии производства

	А	В	С
Лесопилка	1	2	4
Сборочный цех	2	4	2
Отделочный цех	1	1

В течение недели можно планировать работу на лесопилке на 360 чел-ч, в сбо­рочном цехе - на 520 чел-ч и в отделочном цехе - на 220 чел-ч. Прибыль от продажи каждого буфета типов А, В, С составляет соответственно 9, 11, 15 дол. Показать, что в оптимальном решении х1, =180, х2=40, х3=0.

Определите избыток чел-ч работы на лесопилке, в сборочном цехе, в отделочном цехе, воспользовавшись этим решением. Для выполнения обязательств по организации интерьера гостиниц необходимо производить по крайней мере 10 буфетов типа С еженедельно. Как это дополни­тельное требование повлияет на решение?

Задача № 8 Фирме требуется уголь с содержанием фосфора не более 0.03% и долей зольных примесей не более 3.25%. Три сорта угля А, В, С доступны по следующим ценам за 1 тонну.

Сорт угля	Содержание примеси фосфора, %	Содержание примеси золы, %	Цена, рубл
А	0.06	2	300
В	0.04	4	300
С	0.02	3	450

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

Сформулируйте эту задачу как задачу линейного программирования и решите её

Задача № 9 Намечается выпуск двух видов костюмов – мужских и женских. На женский костюм требуется 1 м шерсти, 2 метра лавсана и 1 человеко-день трудозатрат, на мужской костюм – 3.5 м шерсти, 0.5 метров лавсана и 1 человеко-день трудозатрат. Всего имеется 350 м шерсти, 240 метров лавсана и 150 человеко-дней трудозатрат. По плану предусматривается выпуск не менее 110 костюмов, причем необходимо обеспечить прибыль не менее 1400 руб. Требуется определить оптимальное число костюмов каждого вида обеспечивающее максимальную прибыль если прибыль от реализации женского костюма составляет 10 руб., а от мужского 20 рублей

Задача № 10 По нормам в дневном рационе при откорме животных должны содержаться питательные вещества в следующем количестве: кормовых единиц – не менее 1.6 кг, перевариваемого протеина не менее 200 г, каротина не менее 10 мг. При откорме используется ячмень и сенная мука. Содержание питательных веществ в 1 кг этих кормов и стоимости 1 кг корма приведены в таблице. Составить дневной рацион, удовлетворяющий данной питательности при минимальной.

Наименование питательного вещества	Количество единиц питательных веществ, содержащихся в 1 кг корма
Ячмень	Сенная мука
Кормовые единицы, кг Перевариваемый протеин, г Каротин, мг	1.2 80 5	0.8 240 100
Цена 1 кг корма в рубл.	3	5

Сформулируйте эту задачу как задачу линейного программирования и решите её

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

Тип подшипника	Время обработки, ч
Токарный станок	Шлифовальный станок	Сверлильный станок	Прибыль от продажи одного подшипника, рубл.
А	0.01	0.02	0.04	80
В	0.02	0.01	0.01	125
Полное возможное время работы в неделю, ч	160	120	150

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

Задача № 12 Фирма рекламирует свою продукцию с использованием четырех средств: телевизора, радио, газет и афиш. Из различных рекламных экспериментов, которые
проводились в прошлом, известно, что эти средства приводят к увеличению прибыли соответственно на 10, 3, 7 и 4 рубл. в расчете на 1 рубл., затраченный на рекламу.

Распределение рекламного бюджета по различным средствам, подчинено следую­щим ограничениям:

·  полный бюджет не должен превосходить рубл.;

·  следует расходовать не более 40 % бюджета на телевидение и не менее 20 %
бюджета на афиши;

·  вследствие привлекательности для подростков радио на него следует расходовать, по крайней мере, половину того, что планируется на телевидение.

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

«Использование отчета об устойчивости для анализа результатов решения задач линейного программирования»

Упражнение по использованию отчета об устойчивости: влияние изменений в ценовых коэффициентах

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

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

2.  Измените коэффициенты целевой функции (в ячейках С9, D9) и с помощью надстройки "Поиск решения" найдите, как из­менится решение (Х1 Х2) и значение целевой функции.

Результаты впишите в пустые рамки (см. ниже).

2.1.Увеличить норму прибыли при производстве шкафа на 100 у. е. тогда Х1= Х2= DР =

2.2. Увеличить норму прибыли при производстве шкафа на 160 у. е. тогда Х1= Х2= DР =

2.3. Уменьшить норму прибыли при производстве тумбы на 40 у. е. тогда Х1= Х2= DР =

2.4. Уменьшить норму прибыли при производстве тумбы на 50 у. е. тогда Х1= Х2= DР =

В некоторых случаях решение (Х1 Х2) не меняется, в других - изменяется.

Как это объяснить с помощью данных таблицы "Изменяе­мые ячейки" отчета Ехсеl об устойчивости?

3.  Для последнего случая (п. 2.4) сделайте новый отчет об устойчивости. Обратите внимание, что в колонке "Нормированная стоимость" таблицы "Изменяемые ячейки" для тумбы появи­лось отрицательное число. Попробуйте выяснить, что оно означает.

Для этого увеличьте прибыль от продажи тумбы на величину, слегка превышающую по модулю это отрицательное число, и ре­шите задачу еще раз. Что произошло? Каков смысл данных в ко­лонке "Нормированная стоимость" таблицы "Изменяемые ячей­ки" отчета Ехсеl об устойчивости?

Упражнение по использованию отчета об устойчивости: влияние изменений в правых частях ограничений

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

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

II. Переключитесь на вновь созданный лист отчета об устойчивости. Найдите в таблице "Ограничения" колонку "Теневые цены", рассчитайте изменение целевой функции при изменении ресурса bi по формуле DР=Уi×Dbi и занесите результат в третью колонку данной таблицы для перечисленных случаев изменения запаса ресурсов:

№	Изменение запаса ресурсов	DР=Уi×Dbi	DР(прямое вычисление)
1	Увеличить запас ДСП на 150 м
2.	Увеличить запас ДСП на 250 м
3.	Уменьшить запас стекла на 40 м
4.	Уменьшить ресурс труда на 40 человеко-дней
5.	Увеличить ресурс труда на 20 человеко-дней

III. Вновь переключитесь на лист, содержащий прямую задачу о продукции мебельного цеха. Изменяя лимиты ресурсов (в ячей­ках В6:В8) в соответствии с п. II, каждый раз вызывая "Поиск решения" и заново решая оптимизационную задачу, прямым рас­четом найдите изменение целевой функции и переменные реше­ния в случаях 1-5 п. II и запишите эти изменения в четвертую колонку таблицы.

В одних случаях результаты вашего предварительного рас­чета (третья колонка) совпадают с решением с помощью над­стройки "Поиск решения" (четвертая колонка), а в других - нет. Почему?

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

Ткацкий цех выпускает два вида тканей Т1 и Т2 на двух видах станков С1 и С2. Количество станков первого типа – 103, второго – 210. Станок С1 выпускает 54 м ткани Т1 или 72 м ткани Т2, а станок С2 выпускает 34 м ткани Т1 или 652 м ткани Т2 за смену.

Производство тканей ограничено ресурсами и складскими помещениями. За смену можно выпустить не более 6000 м ткани Т1 и не более 11000 м ткани Т2.

Доход от продажи ткани Т1 7.3 у. е. за 1 м, от продажи Т2 – 4.2 у. е. за 1 м.

Как распределить производство тканей Т1 и Т2 между станками С1 и С2 чтобы максимизировать прибыль?

1.  Заполните таблицу параметров

Тип станка	Количество	Производительность
Т1	Т2
С1
С2
Ограничения на производство
Прибыль от продажи 1 м ткани

2.  Заполните таблицу элементов модели

3.  Организуйте данные на MS - Excel и используйте «Поиск решения»

Переменные решения	Целевая функция
Ограничения

Оптимальный план размещения производственных заказов

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

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

	Предприятие
1	2	3	4	Всего, тыс
Переменные решения	Х1	Х2	Х3	Х4	300
Издержки на единицу продукции	83	89	95	98
Инвестиции на единицу продукции	120	80	50	40	18000

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

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

Указания: заполните таблицу элементов модели. Имеется ввиду, что 18000 тыс. руб. – это сумма, выделенная на капиталовложения, но не на покрытие ежемесячных издержек производства. Последние будут покрываться за счет дополнительных средств (сначала краткосрочные кредиты, затем отчисления от продаж). Считается что для обеспечения заданного объема производства нужно вложить тем больше средств, чем больше будет его мощность (количество производимых изделий в месяц).

Переменные решения	Целевая функция
Ограничения

Минимизация отходов лесопилки

Пилорама заготавливает 10 метровые бревна, которые в дальнейшем использует для строительства. Поступил заказ, для которого требуется 275 шт. 3 метровых бревен, 100 шт. 5 метровых и 250 шт. 7 метровых бревен. На складе 315 шт. 10 метровых бревен.

Распилить бревна так, чтобы выполнить заказ и минимизировать длину нестандартных обрезков.