Лабораторная работа 12

К оглавлению 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
34 35 36 37

Оптимизация портфеля продукции.

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

Краткое описание работы. Фармацевтическое предприятие производит три вида таблеток. Специалистами отдела маркетинга был проведен анализ рынка и получены параметры функций спроса для всех трех препаратов (см. табл.1). Была проанализирована себестоимость и выделены переменные расходы по каждому виду продукции (см. табл. 1). Постоянные издержки предприятия остаются одинаковыми при любой структуре производства. Для каждого вида продукции существуют ограничения по объему продаж в натуральном выражении. Максимальная производственная мощность предприятия составляет 100 тыс. упаковок в месяц. Минимальный объем продаж по видам продукции для поддержания достаточного ассортимента представлен в таблице 2. Учитывая внешние ограничения и внутренние ограничения следует отыскать оптимальное сочетание объемов продаж всех видов продукции таблеточного цеха, приносящее наибольшую прибыль.

Таблица 1

Таблица исходных данных

Наименование препарата	а1, грн. / тыс. уп.	а0, грн.	Переменные издержки, грн / тыс. уп.
Анальгин, N10	-0,5	395,2	340,0
Диклофенак, №10	-2,0	2752,0	2550,0
Фталазол, N10	-6,5	765,0	420,0

Таблица 2

Ограничения по объемам продаж, тыс. уп.

Наименование препарата	Минимальный объем продаж, тыс. упаковок
Анальгин, N10	30
Диклофенак, №10	20
Фталазол, N10	15
Всего	65

Работа выполняется с помощью программы Lab12.xls, с использованием надстройки Excel "Поиск решения".

В процессе выполнения работы слушатель должен:

изучить возможности надстройки Excel "Поиск решения";

с помощью программы Lab12.xls найти оптимальный портфель продукции предприятия;

проанализировать полученный результат.

Подготовительная часть работы. Изучить содержание программы Lab12.xls и ответить на следующие вопросы:

Каким образом, зная параметры функции спроса рассчитать значение цены для некоторого объема продаж?

Как рассчитывается общий вложенный доход конкретного вида продукции?

В каком случае возможно максимизировать прибыль посредством максимизации общего вложенного дохода?

Какие ограничения можно выделить в рамках внутренних ограничений производственной системы, а какие в рамках внешних ограничений;

Изменится ли процентное соотношение объемов продаж оптимального портфеля продукции при изменении ограничения на общий выпуск продукции?

Выполнение работы. Выполняя работу, необходимо придерживаться следующей инструкции:

Запустить программу Lab12.xls.

Этап 1.

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

Этап 2.

На втором этапе происходит работа с надстройкой EXCEL. Работа начинается с открытия окна "Поиск решения", которое вызывается через меню "сервис".

В окне "Поиск решения" следует проделать следующее:

Установить целевую ячейку: выбрать ячейку "Вложенный доход всего" (имеет красную заливку).

Равной: максимальному значению.

Изменяя ячейки: диапазон ячеек объемов продаж для трех видов продукции (имеют голубую заливку).

Ограничения: после нажатия кнопки "Добавить" в поле "Ссылка на ячейку" следует внести диапазон ячеек значений объемов продаж, в том числе и общий объем продаж. Установить значение ">=", затем в поле "Ограничение:" следует внести диапазон значений, ограничивающих объем продаж продукции таблеточного цеха. Данный диапазон состоит из четырех ячеек выделенных розовым цветом, находящихся в столбце "минимальный" таблицы ограничений на объем продаж. После ввода следует нажать кнопку "добавить" и также внести ограничения на максимальный объем продаж, выбирая соответствующие диапазоны ячеек и знак неравенства. После ввода следует нажать "ОК".

После того как заданы все ограничения, следует установить ограничение на неотрицательность варьируемых параметров, для чего следует нажать кнопку "Параметры" и установить подтверждение на неотрицательные значения. После нажатия "ОК" пользователь попадает обратно в главное окно "Поиск решения". Для начала процесса оптимизации следует нажать кнопку "Выполнить", после чего система производит ряд итераций и представляет оптимальное значение портфеля продукции, при этом производится запрос в виде диалогового окна "Результаты поиска решения" на сохранение результатов оптимизации. Имеется возможность построения отчетов по результатам оптимизации, помогающим анализировать оптимизационную модель.

Оценить полученный результат.