Введение
Контрольная работа №3 служит формой отчёта студента о самостоятельной работе, проделанной по изучению дисциплины «Экономические системы в управлении».
Методические указания содержат разобранные примеры по темам:
· Подбор параметра
· Поиск решения
Контрольная работа состоит из четырёх заданий.
Выполнить контрольную работу и сдать её на проверку необходимо до начала занятий по дисциплине «Экономические системы в управлении».
Форма защиты контрольной работы ─ устный зачет.
Подбор параметра
Часто при решении практических задач возникают ситуации, когда необходимо достичь какой-то конкретной цели. Например, необходимо чтобы себестоимость продукции составляла 20 рублей.
Специфика таких задач состоит в том, что в Вашем распоряжении есть математическая модель исследуемого процесса, например, закон ценообразования, но Вы не знаете, при каком значении входящего в нее параметра можно достичь поставленной цели.
Решение таких задач можно искать методом перебора. Однако в лучшем случае на это уходит много времени.
Можно предложить другие способы решения. В Excel они реализованы как поиск значения параметра формулы, удовлетворяющего ее конкретному значению.
Эту процедуру используют для поиска такого значения ячейки, при котором значение другой ячейки, вычисляемое по формуле, заранее задано. В формуле должна быть ссылка на ячейку, значения которой ищут. Ограничения на искомое значение ячейки не налагают.
Познакомимся с этой процедурой на примерах.
Пример 1.
Предприятие решило выбросить на рынок новый продукт в количестве 10 000 единиц, какова должна быть цена продукта, если предприятие намеренно получить прибыль в 100 тыс. рублей.
При изготовлении единицы продукта расходы фирмы составили:
¨ Затраты (материалы, экспл. Оборудования, трансп. и др.) = 0,1*Цена ед. продукта.
¨ Зарплата = 0,3*Цена ед. продукта
¨ Реклама = 0,05*Цена ед. продукта
¨ Пр. расходы (расходы связанные с продажей изделия) = 0,1*Цена ед. продукта
¨ Расходы = Затраты +Зарплата +Реклама + Прочие
¨ Прибыль = Количество * Цена - Расходы * Количество
| 1. Заполните ячейки A1:A8[1]. (Рис. 1) 2. Выделите диапазон A1:B8 выполните Вставка Þ Имя Þ Создать… выберите в столбце слева (Рис. 2) |  Рис. 1
| ||
| 3. Введите все необходимые формулы (Рис. 1) 4. Выполните команду Подбор параметра из меню Сервис; |  Рис. 2
| ||
| 5. В поле "Установить в ячейке" появившегося окна введите ссылку на ячейку B8, содержащую формулу; (Рис. 3) 6. В поле "Значение" наберите искомый результат 100 000; | 
Рис. 3
| ||
7. В поле "изменяя значение ячейки" введите ссылку на изменяемую ячейку B2 и щелкните на кнопке ОК.
8. Сохраните таблицу в личном каталоге.
Пример 2.
Нам необходимо найти корни уравнения
Х3-2,92*Х2+1,4355*Х+0,7911136=0, на интервале значений Х от-1,5 до 3
| 1. Для этого заполните значения Х на указанном диапазоне с шагом 0,5 Ø В ячейку A2 введите -1,5, в A3 -1 Ø Выделите обе ячейки (A2:A3) и с помощью маркера заполнения растяните до A11 | 
Рис. 4
|
2. В B2 введите формулу уравнения (Рис. 4) и скопируйте ее для всего диапазона Х.
3. Для наглядности построим график.
Рис. 5
На диаграмме (Рис. 5) мы видим, что график пересекает ось Х приблизительно в точках –0,3; 1,2; 2 для того чтобы определить точные значения воспользуемся «Подбором параметра».
4. В ячейки D2, D3, D4 введите -0,3; 1,2; 2 соответственно, в ячейки E2, E3, E4 скопируйте формулу уравнения.
5. Выполните команду Сервис Þ Подбор параметра
| 6. В поле "Установить в ячейке" появившегося окна (Рис. 6) введите ссылку на ячейку E2, содержащую формулу; 7. В поле "Значение" наберите искомый результат 0; | 
Рис. 6
|
8. В поле "Изменяя значение ячейки" введите ссылку на изменяемую ячейку D2 и щелкните на кнопке ОК.
9. Выполните п. 5,6,7,8 для двух других корней уравнения.
| Получили три корня уравнения (Рис. 7). 10. Сохраните таблицу в личном каталоге. | 
Рис. 7
|
Анализ задачи показывает, что с помощью Excel можно решать уравнения. Конечно, такое уравнение может решить любой школьник. Однако, благодаря этим простым примерам стало, очевидным, что поиск значения параметра формулы, удовлетворяющего ее конкретному значению, - это не что иное, как численное решение уравнений. Другими словами, используя Excel, можно решать любые уравнения с одной переменной.
Задание для самостоятельной работы 1.1.
Месячный план продажи изделий составляет 10 тыс. рублей
Месячный объем продаж составляет 10.5 тыс. рублей.
Продавец получает заработную плату в размере:
(Объем продаж - План продаж)* Процент выполнения плана
Определите, какими должны быть объем продаж и процент выполнения плана,
чтобы заработная плата продавца составила 3 тыс. руб.
Задание для самостоятельной работы 1.3.
Используя операцию Подбор параметра, найдите корни уравнения
0,01*X3 - 3*X2 + 2*X + 1.25 =0
на интервале значений Х от -2 до +2
Задание для самостоятельной работы 1.3.
Дана функция прибыли Q =2P+20, где Q – прибыль, а P – цена.
Используя операцию Подбор параметра, определите цену, при которой прибыль будет равна 100 000 рублей.
Задачи оптимизации
Ранее мы рассмотрели задачу поиска значения параметра, позволяющего достичь конкретной цели.
Решаемые задачи могут быть более сложными. Например, поиск нескольких параметров, обеспечивающих некоторый наперед заданный результат.
Кроме того, иногда интересует не конкретный результат, а минимально или максимально возможный. Например, как минимизировать затраты на содержание персонала или максимизировать прибыли от реализации продукции?
Такие задачи в Excel решают с помощью Поиска решения.
Получение максимальной прибыли при ограниченном ресурсе
Очень часто в быту и в производстве перед нами стоит проблема максимального удовлетворения потребности, соизмеряясь с ограниченными возможностями. Это планирование штата сотрудников, фонда зарплаты, составление оптимального плана производства, планирование рекламной компании по продвижению продукции на рынок… и оптимизация капиталовложений.
Несмотря на все многообразие таких задач, встречающихся в жизни и экономике на каждом шагу, Excel предлагает единый мощный инструмент их решения - средство поиска решения. Нужно только грамотно сформулировать для Excel задачу, а оптимальное решение будет найдено быстро и точно.
Рассмотрим эту задачу например планирования производства красок. Небольшая фабрика выпускает два типа красок для внутренних (I) и наружных (Е) работ. Продукция обоих видов поступает в оптовую продажу. Для производства красок используется два исходных продукта А и В. Максимально возможные суточные запасы этих продуктов составляют 6 и 8 тонн, соответственно. Расходы продуктов А и В на 1 тонну соответствующих красок (Рис. 11).
| Изучение рынка сбыта показало, что суточный спрос на краску I никогда не превышает спроса на краску Е более, чем на 1т. | 
Рис. 8
|
Кроме того, установлено, что спрос на краску I никогда не превышает 2 т в сутки. Оптовые цены одной тонны красок равны 3000 руб. для краски Е и 2000 руб. для краски I.
Какое количество краски каждого вида должна производить фабрика, чтобы доход от реализации продукции был максимальным?
Для решения этой задачи необходимо построить математическую модель. Начнем с ответа на 3 вопроса.
Для определения каких величин строится модель (каковы переменные модели)?
В чем состоит цель оптимизации модели?
Каким ограничениям должны удовлетворять неизвестные?
В нашем случае необходимо спланировать объем производства красок, поэтому переменными являются XI - суточный объем производства краски I и ХЕ - суточный объем производства краски Е.
Суммарная суточная прибыль от производства красок равна Z = 3000 ХЕ +2000 XI. Целью оптимизации модели фабрики является определение таких величин суточного производства каждой краски, которые максимизируют суммарную прибыль, то есть целевую функцию Z.
Перейдем к ограничениям, которые налагаются на ХЕ и XI.
Объем производства красок не может быть отрицательным, следовательно ХЕ, XI ³ 0.
Расход исходного продукта не может превосходить его максимально возможный запас, следовательно
Кроме того, ограничения на величину спроса на краски таковы:
Таким образом, мы составили математическую модель, состоящую из целевой функции, которую надо максимизировать и ограничений.
Заметьте, что если бы мы исходили из стоимости исходного продукта для производства красок, то целевую функцию надо было бы минимизировать.
Данная модель является линейной, так как все уравнения этой модели линейные.
Эта задача решается с помощью команды Сервис Þ Поиск решения. Если в меню Сервис команда Поиск решения отсутствует, для ее установки необходимо выполнить команду Сервис Þ Надстройки Þ Поиск решения.
1. Для упрощения Вашей работы при поиске решения сначала все исходные данные, целевую функцию и ограничения оформите в табличном виде (Рис. 12).
Рис. 9
2. В ячейки "Значение" (переменные ХI и ХЕ) введите пока нули, Это результаты поиска решения, на начальном этапе они могут быть пустыми, но их адреса должны входить в формулу целевой функции и ограничений, если это необходимо.
3. Целевую функцию запишите в ячейке F9 в виде формулы (Рис. 12).
4. Ограничения также запишите в табличном виде, где есть левая и правая части ограничения и знак между ними. Потом эти части легко вставляются в диалоговое окно "Поиск решения".
5. Подготовив все данные, выполните команду Сервис Þ Поискрешения. Появляется диалоговое окно "Поиск решения" (Рис. 13).
| · В строку "Установит целевую ячейку" установите курсор и щелкните мышью по ячейке F9. · Переключатель "Равной" устанавливаем в соответствующее задаче положение - "Максимальному значению". | 
Рис. 10
|
· В строку "Изменяя ячейки" укажите ячейки, которые должны изменяться в процессе поиска решения задачи, то есть ячейки, отведенные под переменные задачи (ХЕ и ХI) В данном случае это ячейки В4 и С4.
Ограничения вводятся в соответствующее поле в виде равенств, неравенств, также можно ввести требование целочисленности значения. Ограничения добавляются по одному. Для начала ввода ограничений щелкните по кнопке " Добавить ". Появляется диалоговое окно
· "Добавление ограничения", состоящее из трех частей (Рис. 14).
Рис. 11
| · "Ссылка на ячейку" - введите мышью адрес ячейки A10, где записаны условия левой части ограничения. В поле "Ограничение" введите мышью адрес ячейки С10, правая часть ограничения. Для установки знака ограничения щелкните по кнопке списка и выберите знак >=. |
· Далее можно нажать кнопку "Добавить" и анологичным способом вводить следующее ограничение (Рис. 12) После ввода всех ограничений нажать кнопку "ОК".
6. Теперь нажмите кнопку "Параметры" в диалоговом окне "Поиск решения" и ознакомьтесь с ним.
Рис. 12
| В диалоговом окне "Параметры поиска (Рис. 15) решения" можно изменять условия и варианты поиска решения исследуемой задачи, а также загружать и сохранять оптимизируемые модели. Большинство задач решаются при установке параметров по умолчанию. |
Поле Максимальное время ограничивает время решения задачи.
Поле Предельное число итераций ограничивает число промежуточных вычислений.
Поле Относительная погрешность и Допустимое отклонение задают точность решения задачи. Рекомендуется (особенно для задач с требованием целочисленности переменных) повторить вычисления с большей точностью и сравнить результаты.
Флажок Линейная модель служит для поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи. При несоответствии положения флажка решаемой задаче можно получить неверный результат.
Флажок Показывать результаты итераций приостанавливает поиск решения и позволяет просмотреть результаты отдельных итераций.
Флажок Автоматическое масштабирование автоматически нормализует входные и выходные значения, качественно различающиеся по величине, например, при максимизации прибыли в процентах по отношению к вложениям, исчисляемым в миллионах рублей.
Группа Оценка - выбор метода экстраполяции.
Группа Производные - выбор метода численного дифференцирования.
Группа Метод - выбор алгоритма оптимизации.
7. Закройте окно «Параметры поиска решения», нажав кнопку «ОК».
8. Нажмите кнопку «Выполнить».
В появившемся диалоговом окне «Результаты поиска решения» можно выбрать требуемый тип отчета Результаты, Устойчивость, Пределы, чтобы вывести отчет о результатах решения задачи
9. Ничего, не выбирая, нажмите кнопку «ОК».
Решение найдено. Все ограничения и условия выполнены. XI = 3.333 и XE = 1.333
Очень важно при составлении математической модели вообще и решения задачи с помощью средства Поиск решения в частности все параметры, на которые вводятся ограничения, должны входить в целевую функцию.
Планирование штатного расписания
Рассмотрим задачу оптимального размещения сотрудников по должностям (рабочим местам)
Часто в практике руководителя возникает проблема: как разместить сотрудников по разным рабочим местам, чтобы и сотрудник мог проявить свои творческие возможности, и предприятие повысило свою производительность.
Будем считать, что каждый сотрудник может выполнять все виды планируемых работ, но опыт, квалификация, а, соответственно и производительность различаются для разных видов деятельности.
Для каждого работника Ai известна его производительность Bj на каждом рабочем месте. Производительность может выражаться как во времени, необходимом для выполнения данной работы, так и по шкале экспертных оценок.
| 1. Составьте матрицу производительности труда всех претендентов при выполнении конкретных видов работы. При этом если работник Ai назначен на работу Bj, то переменная назначения Xij=1, или Xij=0, если он на эту работу не назначен (Рис. 16). | 
Рис. 13
|
2. В ячейках B8:E 8 и F4:F7 введите формулы суммы по столбцам и по строкам.
Если составить таблицу предварительного распределения сотрудников по видам работы (должностям), то из нее видно, что если сотрудник А1 назначен на выполнение работы В1 (B3 =1), то остальные ячейки строки и столбца имеют значение =0 (Рис 16).
Из этого следует, что сумма переменных любой строки или столбца должна быть равна 1.
Примем, что если работник Ai назначен на работу Bj, то его производительность Aij.
В качестве критерия оптимальности (целевой функции) выберем суммарную производительность работников на различных участках работы (должностях).
| 
Рис. 14
|
1. Заполните таблицу производительностей труда сотрудников на разных работах (рис 17).
2. В ячейку D9 рабочего листа введите формулу целевой функции, которая для нашего примера будет иметь вид:
=B4*B14+C4*C14+D4*D14+E4*E14+B5*B15+C5*C15+D5*D15+E5*E15+B6*B16+C6*C16+D6*D16+E6*E16+B7*B17+C7*C17+D7*D17+E7*E17
Это выражение проще ввести в ячейку целевой функции с использованием функции СУММПРОИЗВ, которая позволяет перемножать массивы данных.
=СУММПРОИЗВ(B4:E7;B14:E17)
3. Далее выполните команду СервисÞ Поиск решения и установите соответствующие параметры в диалоговом окне Поиск решения (Рис. 18).
| · Укажите целевую ячейку D9. · Установите флажок "Максимальному значению". · Укажите диапазон изменяемых ячеек В4:Е7. · Введите ограничения | 
Рис. 15
|
В4:Е7 =двоичное
F4:F7 =1
B8:E8 =1
· В диалоговом окне "Параметры поиска решения" укажите, что решаемая модель линейна Þ ОК.
4. Нажмите кнопку «Выполнить».
| Программа выдаст оптимальное размещение сотрудников по должностям (Рис. 18). Отметим, что данная задача сбалансирована, так как число сотрудников совпадает с числом работ. Если задача не сбалансирована, то перед началом решения ее необходимо сбалансировать, введя недостающее число фиктивных строчек или столбцов с нулевой производительностью. | 
Рис. 16
|
Транспортная задача
В ходе производственной деятельности достаточно часто приходится решать задачи оптимизации перевозок грузов. Груз может быть размещен на разных базах, доставка его должна производиться в разные адресаты. При этом нежелательны простои транспорта, порожние пробеги, встречные и нерациональные перевозки.
Для составления оптимального плана перевозок существует особый класс математических методов линейного программирования - транспортные задачи.
Допустим, на трех торговых базах сосредоточен однородный груз в количествах соответственно равных 600, 450 и 500 тонн. Этот груз необходимо перевезти в три торговые точки в количествах соответственно равных 260, 520 и 420 тонн. Стоимость перевозок 1 тонны груза с каждой базы в каждую торговую точку приведены в таблице (Рис. 14).
| Требуется составить план перевозок, обеспечивающих удовлетворение всех заявок торговых точек таким образом, чтобы затраты на осуществление перевозок были минимальными. 1. Составьте таблицу стоимость перевозок (Рис. 20.). | 
Рис. 17
|
| 2. Составьте таблицу плана перевозок грузов от баз к торговым точкам (Рис. 21). В ячейках В16:D18 проставим произвольные величины количества перевозимых грузов. | 
Рис. 18
|
3. В строку "Доставка" и столбец ""Кол-во перевезенного груза" запишите формулы, суммирующие соответствующие значения. В столбец "Остаток" также запишем формулу =E9-E16.
4. В ячейку D20 разместите формулу целевой функции, определяемую как сумму произведений стоимости перевозок и количества перевезенного груза.
5. Выполните команду Сервис Þ Поиск решения и в окне "Поиск решения" сделаем следующие установки:
Ø Укажите ячейку целевой функции D20.
Ø Установить флажок, минимизирующий расходы на перевозку.
Ø Укажите адрес диапазона изменяемых ячеек B16:D18.
6. Ведите ограничения:
· Количество перевезенного груза не может быть отрицательным числом (B16:D18 >=0).
· Заявки торговых точек должны быть удовлетворены (B12:D12=B19:D19).
· Количество груза, вывозимого с каждой базы, ограничено его запасом (E16:E18<=E9:E11).
7. Нажмите кнопку "Параметры" и укажем, что решаемая модель линейна Þ ОК.
8. Нажмите кнопку «Выполнить».
Программа выведет на экран оптимальный план перевозки грузов (Рис. 22).
Мы решали задачу с тремя торговыми точками и тремя базами, но их число может быть и неодинаковым. Естественно, таким же образом можно планировать вывоз продукции с нескольких предприятий разным потребителям или на склады.
| Если требуется перевозить два или более типов грузов, надо составлять две или более пары таблиц, в целевой функции суммировать затраты на перевозки всех грузов (=СУММПРОИЗВ1+ СУММПРОИЗВ2…) и вводить два или более комплектов аналогичных ограничений. | 
Рис. 19
|
