Решение оптимизационных задач

Лабораторная работа № 4. Решение оптимизационных задач

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

Постановка задачи. Рассматривается типичная модель сбыта продукции, отражающая зависимость прибыли от объёма продаж, зарплаты персонала, расходов на рекламу и т.д. Требуется максимизировать общую прибыль от операции, варьируя какие-либо параметры, например расходы на рекламу.

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

,	(2.4.1)

где V₀ – нормирующий коэффициент;

K_s – коэффициент сезонности;

С = 3000 – постоянная;

R – расходы на рекламу.

Данная модель отражает нелинейную зависимость эффективности рекламы. В соответствии с этой моделью вложения 5000 руб. в рекламу в первом квартале принесут увеличение объёмов продаж на 1092 единицы, тогда как такие же вложения во втором квартале увеличат объём продаж только на 775 единиц.

Накладные расчёты исчисляются из расчёта 15 % от объёмов реализации. Затраты на сбыт определяются удельными затратами на одно изделие (25 руб.)

Порядок решения задачи

Подготовка исходных данных

1.1. Введите на рабочем листе Excel следующие исходные данные (рис. 2.4.1).

Рис. 2.4.1. Исходные данные для лабораторной работы № 4

1.2. Выполните ввод формул в столбце В (табл. 2.4.1).

Таблица 2.4.1

Расчётные формулы для лабораторной работы № 4

Окончание табл. 2.4.1

1.3. Введите аналогичные формулы в столбцах C, D, E.

1.4. В столбце F введите формулы для суммирования содержимого столбцов B, C, D, E.

Решение оптимизационных задач

2.1. Решение задачи однопараметрической безусловной оптимизации.

Цель оптимизации – максимизировать прибыль в первом квартале за счёт изменения затрат на рекламу в этом квартале. Последовательность операций в MS Excel для решения задачи следующая:

- вызовите диалоговое окно Сервис®Поиск Решения;

- в открывшемся окне задайте целевую ячейку B15 и изменяемую ячейку В11;

- запустите процесс поиска. Результатом решения должна стать квартальная прибыль, равная 17093 руб., соответствующая затратам на рекламу в первом квартале 15093 руб;

- после ознакомления с результатами закройте диалоговое окно.

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

Цель оптимизации: распределить расходы на рекламу поквартально таким образом, чтобы годовая прибыль от продажи продукции была максимальна. При этом затраты на рекламу ничем не ограничены. Особенности решения данной задачи по сравнению с задачей п. 2.1 следующие. В диалоговом окне Поиск решения целевой должна быть установлена ячейка F15, а диапазон изменяемых ячеек B11:E11 (выделяется мышью).

Результатом решения должна стать годовая прибыль, равная 79706 руб., соответствующая годовым затратам на рекламу 89706 руб.

2.3. Решение задачи многопараметрической оптимизации с учётом ограничения - равенства.

Цель оптимизации: распределить фиксированный годовой бюджет расходов на рекламу поквартально таким образом, чтобы годовая прибыль была максимальна. Годовой бюджет расходов на рекламу требуется израсходовать без остатка. Особенности решения данной задачи по сравнению с задачей п. 2.2 следующие. В диалоговом окне Поиск решения выбирается кнопка Добавить (ограничение). В открывшемся диалоговом окне в левом поле устанавливается ссылка на ячейку F11, в среднем – отношение =, а в правом – ограничение расходов на рекламу 40000.

Результатом решения должна стать годовая прибыль, равная 71447 руб., соответствующая годовым затратам на рекламу 40000 руб.

2.4. Решение задачи многопараметрической оптимизации с ограничениями-неравенствами.

Основное отличие цели оптимизации от задачи п. 2.3 – средства, заложенные на рекламу, не требуется израсходовать в полном объёме. При этом в ограничении задачи устанавливается знак <= вместо =. Бюджет расходов установите равным 50000.

Результатом решения должна стать прибыль, равная 74817 руб., соответствующая затратам годовым затратам на рекламу 50000 руб.

2.5. Самостоятельно решите рассмотренную задачу с двумя ограничениями. Первое устанавливает минимальные расходы на рекламу 50000, второе – максимальные затраты на рекламу 100000.

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