- •Лабораторная работа №2 Оптимальные бизнес-планы, план по продукции, технология оптимизации
- •1.1. Постановка задачи
- •1.2. Лабораторная модель
- •1.3. Программа оптимизации Поиск решения (Solver)
- •1.4. Практическая работа
- •1.5. Задание 1. Ручной поиск оптимального плана
- •1.6. Задание 2. Компьютерный поиск оптимального плана
- •Анализ результатов и решения менеджера
1.2. Лабораторная модель
Табличная модель
Обычно план по номенклатуре составляется в виде таблицы. Вначале структура документа вчерне составляется на бумаге или сразу в виде электронной таблицы. Пример приведен в табл. 5.1.
Плановики, разрабатывая план производства продукции, располагают номенклатуру в первом столбце, как это принято в прайс-листах. Но, следуя фирменному примеру, имеющемуся в ваших компьютерах и составленному явно не экономистами, а математиками или программистами, примем расположение плановой номенклатуры в строке.
Наименование продукции расположено в строке 8 листа Excel (телевизор, стереосистема, акустическая система). В строке 9 расположены ячейки искомого плана. Мы должны назначить количество изделий в плане производства.
Цель производства - максимально возможная прибыль вычисляется в ячейке D18.
Искомые величины: плановое количество продукции и прибыль - окружены сплошной жирной рамкой. Необходимые для расчета плана исходные данные окружены пунктирными рамками. Промежуточные результаты расчетов не выделяются рамками.
В колонке А приводится наименование комплектующих изделий, необходимых для производства продукции. Рядом, в колонке В, задан как исходные данные запас комплектующих на складе. Это можно также представить как ежемесячная, квартальная или годовая мощность (возможность) поставщиков комплектующих.
Исходными данными для расчетов являются нормы расхода комплектующих на производство одного изделия. Они задаются в матрице диапазона Dl1:F15 и готовятся технологами-нормировщиками.
Плановые затраты комплектующих на производство всех типов изделий не должны превышать запасов на складе. Они вычисляются в колонке С как сумма произведений планового количества продуктов на удельные нормы затрат комплектующих.
Прибыль по каждому типу изделий вычисляется в строке 17.
Исходным данным является коэффициент уменьшения отдачи. Он отражает убывающую эффективность роста продаж за счет роста затрат на рекламу и другие затраты в системе маркетинга и сбыта.
Математическая модель
Математики сделали очень многое для развития экономической теории и инструментария экономистов. Но определение проблемы и экономическую постановку задачи для математиков и программистов дают экономисты. Хорошо, если экономисты могут на основе своих расчетных таблиц составить экономико-математическую постановку задачи для математиков и программистов.
Плановую табл. 5.1 экономист составил почти интуитивно.
Посмотрим на таблицу в представлении значений и формул и составим математическую модель.
Введем обозначения:
i - номер строки, ресурса;
j - номер столбца, продукта;
Xj - искомое плановое количество j-го продукта;
Pj - прибыль (profit) на единицу j-ro продукта;
Bi - ограниченный (boundary = граница) запас i-ro ресурса на складе;
Rij - норма расхода i-ro ресурса на единицу j-ro продукта;
Ci - плановая сумма расхода i-ro ресурса по всем продуктам;
Ci = ∑ Rij*Xj.
В общем виде наша модель экономико-математической постановки задачи будет выглядеть следующим образом:
Целевая функция (максимизировать прибыль)
P = ∑ Pj *Xj >max
при ограничениях Ci <= Bi и неотрицательных количествах продуктов Xj >= 0.
Формулы табличной модели
После составления плановой таблицы необходимо связать показатели формулами для вычислений. Представление формул и чисел исходных данных дано в табл. 5.2.
Переключение листа в режим представления формул или значений (результатов вычисления) производится в меню Сервис>Параметры>Параметры окна>формулы.
В колонке С (Плановый расход комплектующих) введены формулы вычисления суммы произведений норм расхода ресурсов на плановое количество продукции.
В строке 17 (Прибыль по видам изделий) числа 75, 50 и 35 означают прибыль на единицу продукции, которая умножается на количество изделий по плану и корректируется возведением в степень коэффициентом уменьшения прибыли из ячейки HI5.
В строке 18 суммируется прибыль по всей продукции из строки 17.