Всероссийский заочныйфинансово-экономический институт
Кафедра автоматизированной обработкиэкономической
информации
Лабораторная работа по дисциплине
«Экономико-математическоемоделирование»
Вариант №7
Выполнила:
специальность Б/У, А и А
Караченко Евгения Николаевна
Краснодар 2010
Задача 1
Фирма рекламирует своюпродукцию с использованием четырех средств: телевидения, радио, газет и афиш.Из различных рекламных экспериментов, которые проводились в прошлом, известно,что эти средства приводят к увеличению прибыли соответственно на 10, 3, 7 и 4у.е. в расчёте на 1 у.е., затраченную на рекламу.
Распределение рекламногобюджета по различным средствам подчинено следующим ограничениям:
а) полный бюджет недолжен превосходить 500 000 у.е.;
б) следует расходовать неболее 40% на телевидение и не более 20% бюджета на афиши;
в) вследствие привлекательностидля подростков радио на него следует расходовать, по крайней мере половину того,что планируется на телевидение.
Сформулируйте задачу распределениясредств по различным источникам как задачу линейного программирования и решите её.
Составим экономико-математическуюмодель
Обозначим через Х1, Х2, Х3,Х4 объёмы производства рекламных средств, используемых фирмой.
Целевая функция — это математическаязапись критерия оптимальности, т.е. выражение, которое необходимо максимизировать
f(x) = 10Х1+3 Х2+7Х3+4 Х4
Ограничения по бюджету:
Х1 + Х2 + Х3 + Х4 ≤500 000;
Х1 ≤ 200 000; (500000*40%= 200000)
Х2≥100 000;
Х4≤100 000;
Х1, Х2, Х3, Х4 ≥ 0.
Решение.
1. Открыть чистый лист Excel. Создать текстовую форму – таблицу для ввода условий задачи(рис.1.1)
/>
Рис.1.1
2. Указать адреса ячеек, вкоторые будет помещён результат решения (изменяемые ячейки). В данной задаче оптимальныезначения компонентов вектора Х = (Х1, Х2, Х3, Х4) будут помещены в ячейках В4: В7,коэффициенты целевой функции – С4: С7, коэффициенты ограничения D4:D7.
3. Ввести исходные данныезадачи в созданную форму – таблицу, представленную на рис.1.2.
4. Ввести зависимость для целевой функции:
• курсор в ячейку F10;
• курсор на кнопку «Мастерфункций», расположенную а панели инструментов;
• курсор в окно Функции наСУММ;
• в строку «Число 1» ввести F4:F7;
• кнопка «ОК». На экране: в ячейкуF10 введена функция (рис.1.3.).
• курсор в ячейку E4.
• курсор на кнопку «Мастерфункций», расположенную а панели инструментов;
• курсор в окно Функции наСУММПРОИЗВ;
• в строку «Массив 1»ввести $B$4:$B$7;
• в строку «Массив 2» ввестиD4:D7;
• кнопка «ОК». На экране:в ячейку Е4 введена функция.
Копировать формулу из Е4в ячейки Е5, Е6, Е7 (рис.1.3.).
5. Ввести зависимость дляфункции ограничений:
• курсор в ячейку F4;
• в ячейку F4 ввести формулуB4*D4.
Копировать формулу из F4 в ячейки F5,F6,F7 (рис.1.3.).
/>
Рис.1.2
6. В строке Меню указательмыши на имя Сервис. В развернутом меню команда Поиск решения. Появляетсядиалоговое окно Поиск решения.
• курсор в строку Установитьцелевую ячейку;
• ввести адрес ячейки $F$10;
• ввести направление целевойфункции равной – Максимальному значению;
• курсор в строку Изменяяячейки;
• ввести адреса искомых переменных$B$4:$B$7.
7. Ввести ограничения:
• указатель мышки на кнопкуДобавить.Появляется диалоговое окно Добавления ограничения;
• в строке Ссылка наячейку ввести адрес $B$4;
• ввести знак ограничения≤;
• в строке Ограничение вести200000;
• указатель мышки на кнопкуДобавить. На экране вновь откроется диалоговое окно Добавление ограничения;
• ввести остальные ограничениязадачи, по вышеописанному алгоритму;
• после введения последнегоограничения кнопка ОК.
На экране появится диалоговоеокно Поиск решения с веденными условиями.
8. Ввести параметры для решенияЗЛП:
• в диалоговом окне указательмышки на кнопку Параметры. На экране появится диалоговое окно Параметрыпоиска решения.
• установить флажки в окнахЛинейная модель и Неотрицательные значения;
• указатель мыши на кнопкуОК. На экране появится диалоговое окно Поиск решения;
• указатель мыши на кнопкуВыполнить (Рис.1.4.).
Полученное решение означает,что фирма может получить наибольшую прибыль, если распределит рекламный бюджет равный500000 у.е. следующим образом: 200000 – телевизионная реклама; 300000 – радиореклама.
/>
Рис.1.3
/>
Рис.1.4
Задача 2
В распоряжении некоторой компанииимеется 6 торговых точек и 5 продавцов. Из прошлого опыта известно, что эффективностьработы продавцов в различных точках неодинакова. Коммерческий директор компаниипроизвел оценку деятельности каждого продавца в каждой точке. Результаты этой оценкипредставлены в таблице.Продавец Объем продаж по торговым точкам, USD/тыс.шт. I II III IV V VI A 68 72 75 83 75 69 B 56 60 58 63 61 59 C 35 38 40 45 25 27 D 40 42 47 45 53 36 E 62 70 68 67 69 70
Как коммерческий директордолжен осуществить назначение продавцов по торговым точкам, чтобы достичь максимальногообъема продаж?
Решение
1. Создание формы для решениязадачи предполагает создание матрицы назначений по должностям.
Для этого необходимо выполнитьрезервирование изменяемых ячеек: в блок ячеек B3:G7 вводится «1».
Таким образом, резервируетсяместо, где после решения задачи будет находится распределение продавцов, обеспечивающеемаксимальную производительность труда.
2. Ввод граничных условий
Введение условия назначенияработника только на одну должность т.е.
m
∑xij =1,i=1,…m.
j=1
где xij – назначение i-го продавца на j-ую должность;
m – количество вакантных должностей.
Для этого необходимо выполнитьследующие операции:
— курсор в ячейку А3;
— щелкнуть знак Σ;
— выделить необходимые для суммированияячейки B3:G3
— нажать ENTER – подтверждение ввода формулы для суммирования.
Аналогичные действия выполнитьдля ячеек А4,А5,А6,А7 т.е. ввести условия назначенияпродавцов (для всех строк).
3. Ввод исходных данных
В данной задачи осуществляетсяввод продавцов на различных торговых точках (в ячейки А11: А15 вводится «1»), потребностив заполнении вакантной должности («1» — в B10:G10), вводпроизводительности труда конкретного продавца при проведении оценки деятельностикаждого продавца в каждой торговой точке ( блок B11:G15) (Рис.1.6.).
/>
Рис.1.5.
4. Назначение целевой функции
Для вычисления значения целевойфункции, соответствующей максимальной суммарного объёма продаж, необходимо зарезервироватьячейку и ввести формулу для её вычисления:
m m
F= ∑∑CijXij,
J=1 i=1
где Cij – производительность труда i-го работника при занятии j –ого торговой точки;
Xij – назначений i-го продавца на j-ую торговую точку.
Для этого:
— курсор в ячейку В19.В данную ячейку будет помещаться значение целевой функции после решения задачи;
— щёлкнуть Мастер функции
— в окне СУММПРОИЗВуказать адреса массивов элементы, которых обрабатываются этой функцией.
В задаче целевая функция представляетсобой произведение производительности труда продавцов (расположенных в блоке ячеекВ11:G15) и назначения продавцов на торговые точки (содержимоеячеек B3:G7). Для этого:
— в поле Массив 1 указатьадреса В11:G15;
— в поле Массив 2 указатьадреса B3:G7;
— ОК подтверждение окончания ввода адресовмассивов.,
В поле ячейки В19 появитсянекоторое числовое значение, равное произведению «1» на производительность каждогопродавца на конкретном торговом месте (число 1678 в данной задаче) (рис.1.7).
/>
Рис.1.6
5. Ввод зависимостей из математическоймодели. Для осуществления этого этапа необходимо выполнить следующий перечень операций:
• щёлкнуть Сервис –Поиск решения;
• курсор подвести в поле Установитьцелевую (ячейку);
• ввести адрес $B$19. Таким образом, производится указания ячейки, куда прирешении задачи помещаются значения целевой функции.
• установить направление измененияцелевой функции, равное «максимальному значению»;
• ввести адреса изменяемыхячеек B3:G7. Для этого:
— щёлкнуть в поле Изменяяячейки;
— ввести адреса $B$3:$G$7.
• ввести ограничения задач.В матрицу объёма продаж, содержащую исходные данные по задаче, необходимо ввестиусловия назначения продавца только на одно торговое место. Для этого:
— щёлкнуть Добавить ограничения;
— в поле Ссылка на ячейкуввести адреса $A$3:$A$7;
— в среднем поле установитьзнак «=». Для этого щёлкнуть спинер и выбрать необходимый знак «=».
— в поле Ограниченияустановить адреса $A$11:$A$15;
— щёлкнуть ОК.
Далее вводятся ограничение,которое реализует условия заполнения торгового места. Для этого:
— щёлкнуть Добавитьограничения;
— в поле Ссылка на ячейкуввести адреса $B$8:$G$8;
— в поле знака выбрать припомощи спинера знак «=»;
— в поле Ограниченияустановить адреса $B$10:$G$10;
— ОК (Рис.1.8.).
/>
Рис.1.7
6. Ввод параметров. Далеенеобходимо установить параметры для решение задачи. Для этого:
— щёлкнуть Параметры;
— установить Линейная модель;
— установить Неотрицательноезначение;
-ОК. После этого осуществитьпереход в поле Поиск решения;
— нажать Выполнить.
В Матрице назначений содержитсясхема распределения продавцов по торговым точкам (1- назначен, 0- не назначен),дающая максимальный суммарный объём продаж. Значение целевой функции содержитсяв ячейки В19 и для данной задачи равно 289 (Рис.1.9.).
/>
Рис.1.8
Вывод: максимальный объёмпродаж, равный 289 тыс.шт., будет достигнут при назначении:
— продавца I (содержимое ячейки E3 равно 1);
— продавца II («1» в ячейке D4);
— продавца III («1» в ячейкеC5);
— продавца IV («1» в ячейкеB6);
— продавца VI («1» в ячейкеG7).