Задачи оптимизации в маркетинге и их решение на основе ППП Excel

Составитель

 

Компьютерный набор: Семенов И.Т., Торина Н.А.

 

 

Подписано в печать с готового оригинал-макета.

Печать – ризограф. Бумага офсетная. Формат 60х84 1/16.

Физ.печ.л. 5, 75. Усл. печ. л. 5, 34. Уч.-изд. л. 5. Тираж 100 экз. Заказ №4

 

 

Отпечатано в типографии Хакасского государственного университета

им. Н.Ф.Катанова

655017, г. Абакан, пр. Ленина, 94

ЭКОНОМИКО-МАТЕМАТИЧЕСКИЕ МОДЕЛИ И МЕТОДЫ ПОЛУЧЕНИЯ ОПТИМАЛЬНЫХ РЕШЕНИЙ, АНАЛИЗА ЭКОНОМИЧЕСКИХ ПРОЦЕССОВ С ПРИМЕНЕНИЕМ ПК

 

Задачи оптимизации в маркетинге и их решение на основе ППП Excel

Задача 1. Фирма освоила производство столов и тумбочек для торговой сети из древесины первого и второго видов. Всего имеется 72 м³ древесины первого вида и 56 м³ древесины второго вида. При этом на производство одного стола (одной тумбочки) требуется 0, 18 (0, 09) м³ древесины первого вида и 0, 08 (0, 28) м³ древесины второго вида. От производства одного стола мастерская получает прибыль в размере 1, 1 д.е., а одной тумбочки – 0, 7 д.е. Сколько столов и тумбочек должна изготовить фирма из имеющейся древесины, чтобы получить наибольшую прибыль?

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

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

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

.

Ограничения по ресурсам и граничные условия имеют следующий вид:

Приведем подробное описание технологии получения решения приведенной задачи линейного программирования (ЗЛП) с помощью пакета прикладных программ Excel.

Обозначим: М1 – один щелчок левой кнопкой мыши; М2 – двойной щелчок левой кнопкой мыши.

Далее необходимо последовательно выполнить следующий алгоритм.

1⁰. Создать форму для ввода условий задачи. Запустить Excel, выбрав Microsoft Excel из подменю Программы главного меню Windows. Открывается чистый лист Excel.

Создать текстовую форму – таблицу для ввода условий задачи (рис. 1).

Рис.1 Текстовая форма для ввода условий ЗЛП

2⁰. Указать адрес ячеек, в которые будет помещен результат решения (изменяемые ячейки). Обозначим через количество продукции каждого вида. В нашей задачи оптимальные значения компонент вектора будут помещены в ячейках B3: C3, оптимальные значения целевой функции – в ячейке D4.

3⁰. Ввести исходные данные задачи в таблицу (рис. 2). Сохранить таблицу.

Рис. 2. Введение исходных данных задачи

4⁰. Ввести зависимость для целевой функции:

· курсор в ячейку D4.

· курсор на кнопку «Мастер функций», расположенную на панели инструментов.

· М1. На экране появляется диалоговое окно Мастер функций – шаг 1 и 2 (рис. 3).

· курсор в окно «Категории» на категорию Математические;

· М1;

· курсор в окно Функции на СУММПРОИЗВ;

· М1. На экране появляется диологовое окно СУММПРОИЗВ (рис. 4);

· в строку «Массив 1» ввести B$3: C$3;

· в строку «Массив 2» B4: C4;

· кнопка «ОК» на экране: в ячейку D4 введена функция (рис. 4).

Рис. 3. Диалоговое окно

Рис. 4. Диалоговое окно

 

5⁰. Ввести зависимости для ограничений (рис. 5):

· курсор в ячейку D4;

· щелчок правой кнопкой мыши -> Копировать -> М1;

· выделить ячейки D7 – D8;

· щелчок правой кнопкой мыши -> Вставить -> М1;

Рис. 5. Введение зависимостей для ограничений

В строке Меню указатель мыши на имя Сервис -> М1. в развернутом меню команда Поиск решения -> М1. Появляется диалоговое окно Solver Parameters (Поиск решения) (рис.6).

Рис. 6. Диалоговое окно

6⁰. Назначить целевую функцию (установить целевую ячейку):

· курсор в строку Set Target Cell (Установить целевую ячейку);

· ввести адрес ячейки $D$4;

· ввести направление целевой функции в зависимости от условия задачи. В нашем случае – Max (Максимальному значению);

· курсор в строку By Changing Cells (Изменяя ячейки);

· ввести адреса искомых переменный B$3: C$3.

7⁰. Ввести ограничения:

· указатель мышки на кнопку Add (Добавить) -> М1. Появляется диалоговое окно Add Constraint (Добавление ограничения) (рис. 7);

· в строке Cell Reference (Ссылка на ячейку) ввести адрес $D$7;

· ввести знак ограничения < =;

· в строке Constraint (Ограничение) ввести адрес $G$7;

· указатель мыши на кнопку Add (Добавить) -> М1. На экране вновь диалоговое окно Add Constraint (Добавление ограничения) (рис. 7).

· Ввести остальные ограничения задачи по вышеописанному алгоритму;

· после введения последнего ограничения кнопка ОК.

Рис. 7. Диалоговое окно

На экране появится диалоговое окно Solver Parameters (Поиск решения) с введенными условиями (рис. 8).

8⁰. Ввести параметры для решения ЗЛП: в диалоговом окне указатель мыши на кнопку Options (Параметры). На экране появляется диалоговое окно Solver Options (Параметры поиска решения) (рис. 9);

- установить флажки в окнах Assume Linear Model (Линейная модель) (это обеспечивает применение симплекс-метода) и Assume Non-Negative (Неотрицательные значения); указатель мыши на кнопку ОК.

На экране диалоговое окно Solver Parameters (Поиск решения);

- указатель мыши на кнопку Solver (Выполнить).

Рис. 8. Диалоговое окно

Рис. 9. Диалоговое окно

Спустя время появится диалоговое окно Solver Results (Результаты поиска решений) (рис. 10), таблица с заполненными ячейками В3: С3 для значений , ячейка D4 с максимумом значения целевой функции (рис. 11).

Рис. 10. Диалоговое окно

Рис. 11. Искомое решение ЗЛП

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

Проверка решения: max f(x) = 1, 1*350 + 0, 7*100 = 385 + 70 = 455 д.е. Оптимальный план ЗЛП при этом Х = (х₁ = 350; х₂ = 100; х₃ = 0; х₄ = 0), т.е. все имеющиеся в запасе ресурсы использованы в производстве полностью.

Задача 2. Фирма производит два вида изделий А и Б, рынок сбыта которых не ограничен. Каждое изделие должно пройти обработку на каждой из трех машин 1, 2 и 3. Время обработки (в часах) для каждого изделия А на машинах 1, 2 и 3 составляет 0, 5 ч, 0, 4 ч и 0, 2 ч соответственно, а для каждого из изделий Б время обработки на этих машинах равно соответственно 0, 25 ч, 0, 3 ч и 0, 4 ч. Ресурсы времени работы машин 1, 2 и 3 типов составляют 40; 36 и 36 ч в неделю соответственно. Прибыль от изделий А и Б равна соответственно 5 и 3 д.е. за одно изделие. Определить недельный план выпуска изделий А и Б, обеспечивающий максимизацию прибыли.

Составим экономико-математическую модель. Обозначим через . объемы производства соответствующего вида продукции. Тогда имеем:

max ; ограничения по ресурсам:

Решение. Подробное описание технологии получения решения приведенной ЗЛП дано выше, в задаче 1, поэтому приведем лишь результаты.

а)

б)

Рис. 1. Начальная (а) и конечная (б) формы решения ЗЛП

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

Задача 3. Из 505 м² ткани нужно сшить не более 150 женских платьев не более 100 детских платьев. На пошив одного женского и детского платья требуется соответственно 3 м² и 1 м² ткани. При реализации каждого женского платья получают 10 д.е. прибыли, а детского - 5 д.е. Сколько нужно сшить женских и детских платьев, чтобы получить наибольшую прибыль?

Решение. Экономико-математическая модель имеет вид:

max f(x) = 10х₁ + 5х₂; ограничения 3х₁ + х₂ ≤ 505;

х₁ ≤ 150;

х₂ ≤ 100;

х₁ ≥ 0, х₂ ≥ 0.

Рис. 1. Результат решения ЗЛП

Полученное решение означает, что максимальную прибыль 1850 д.е. фирма получит, если сошьет 135 женских и 100 детских платьев.

Задача 3. Можно закупить корм 1 и 2, при этом стоимость единиц корма вида 1 равна 2 ден. единицам, а вида 2 – 4 ден. единицы. В каждой единице корма 1 содержится одна единица витамина А, две единицы витамина В и нет витамина С, а в каждой единице корма 2 – две единицы А, одна В и одна единица С. Животному в сутки необходимо не менее 10 единиц витамина А, 10 единиц витамина В и 4 единицы витамина С. Составить наиболее дешевый рацион питания животного в расчете на сутки.

Экономико-математическая модель имеет вид:

Целевая функция - ;

Ограничения по ресурсам:

Рис. 1. Результат решения ЗЛП

Задача 4. Издательство «Геоцентр-Медиа» издает два журнала «Автомеханик» и «Инструмент», которые печатают в трех типографиях «Алмаз-Пресс», «Карелия-Принт» и «Hansaprint» (Финляндия). Время, отведенное для печати 1000 экземпляров, ограничено (табл. 1). Спрос на журнал «Инструменты» не превышает 7500 экземпляров, на журнал «Автомеханик» - 12000 экземпляров в месяц. Определить оптимальное количество журналов, обеспечивающее максимальную выручку от продажи.

Таблица 1. Исходные данные ЗЛП

Типография	Время печати 1000 экземпляров	Ресурс времени, отведенный типографией, ч
«Автомеханик»	«Инструмент»
Алмаз-Пресс
Карелия-Принт
Hansaprint
Оптовая цена, руб./шт.

 

Экономико-математическая модель данной задачи имеет вид:

при ограничениях: ;

Рис. 1. Результат решения ЗЛП