Лабораторная работа № 4. Решение оптимизационных задач
Цель работы: изучение технологии решения задач условной оптимизации функций нескольких переменных в среде MS Excel.
Постановка задачи. Рассматривается типичная модель сбыта продукции, отражающая зависимость прибыли от объёма продаж, зарплаты персонала, расходов на рекламу и т.д. Требуется максимизировать общую прибыль от операции, варьируя какие-либо параметры, например расходы на рекламу.
Предполагается, что в результате маркетинговых исследований построена следующая модель зависимости уровня объёмов продаж от расходов на рекламу для данного вида продукции:
, | (2.4.1) |
где V0 – нормирующий коэффициент;
Ks – коэффициент сезонности;
С = 3000 – постоянная;
R – расходы на рекламу.
Данная модель отражает нелинейную зависимость эффективности рекламы. В соответствии с этой моделью вложения 5000 руб. в рекламу в первом квартале принесут увеличение объёмов продаж на 1092 единицы, тогда как такие же вложения во втором квартале увеличат объём продаж только на 775 единиц.
Накладные расчёты исчисляются из расчёта 15 % от объёмов реализации. Затраты на сбыт определяются удельными затратами на одно изделие (25 руб.)
Порядок решения задачи
Подготовка исходных данных
1.1. Введите на рабочем листе Excel следующие исходные данные (рис. 2.4.1).
A | B | C | D | E | F | |
Лабораторная работа № 4 | ||||||
Квартал | Всего | |||||
Сезонность | 0.9 | 1.1 | 0.8 | 1.2 | ||
Объём продаж | ||||||
Выручка от продажи | ||||||
Затраты на сбыт | ||||||
Валовая прибыль | ||||||
Расходы на персонал | ||||||
Расходы на рекламу | ||||||
Накладные расходы | ||||||
Суммарные расходы | ||||||
Прибыль | ||||||
Норма прибыли | ||||||
Цена изделия | ||||||
Затраты на сбыт |
Рис. 2.4.1. Исходные данные для лабораторной работы № 4
1.2. Выполните ввод формул в столбце В (табл. 2.4.1).
Таблица 2.4.1
Расчётные формулы для лабораторной работы № 4
Яче-йка | Формула | Описание |
B5 | =35*B3*(B11+3000)^0.5 | Ожидаемое количество продаж, ед. |
Окончание табл. 2.4.1
B6 | =B5*$B$18 | Выручка от реализации, руб. |
B7 | =B5*$B$19 | Затраты на сбыт, руб. |
B8 | =B6-B7 | Валовая прибыль, руб. |
B12 | =0.15*B6 | Накладные расходы (15 % выручки), руб. |
B13 | =СУММ(B10:B12) | Суммарные расходы (персонал, реклама, накладные), руб. |
B15 | =B8-B13 | Производственная прибыль, руб. |
B16 | =B15/B6 | Норма прибыли - отношение прибыли к выручке |
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.
Результаты выполнения работы представьте преподавателю. Прокомментируйте получившийся результат оптимизации расходов.