Работа № 1. Оптимальные бизнес-планы, план по продукции, технология оптимизации
Проблема: менеджеры и планировщики разрабатывали план производства продукции без учета ресурсов, т. е. запасов материалов и комплектующих на складах и возможностей поставщиков.
Цель работы
Освоить методику и технологию оптимизации планов в табличном процессоре MS Excel с помощью программы Поиск решения (Slover).
Научиться составлять наилучший (оптимальный) план производства продукции с учетом ограниченного обеспечения материальными ресурсами.
Постановка задачи
Предприятие выпускает телевизоры, стерео и акустические системы, используя общий склад комплектующих. Каждому типу изделий соответствует своя норма прибыли. Запас комплектующих на складе ограничен. Задача сводится к определению количества каждого вида изделий для получения наибольшей прибыли, т. е. оптимальное соотношение объемов выпуска разных типов изделий в плане.
Следует учитывать уменьшение удельной прибыли при увеличении объемов производства в связи с дополнительными затратами на сбыт.
Порядок выполнения работы
Исходные данные приведены в Таблице 1.1. Наименование продукции расположено в строке 2, в строке 3 расположены ячейки искомого плана. В колонке А приводится наименование комплектующих изделий, необходимых для производства продукции. В колонке B задан запас комплектующих на складе. Нормы расхода комплектующих на производство одного изделия задаются в матрице диапазона D5:F9. Плановые затраты комплектующих на производство всех типов изделий вычисляются в колонке С и не должны превышать запасов на складе. Прибыль по каждому типу изделий вычисляется в строке 17, числа 75, 50 и 35 означают прибыль на единицу продукции, которая умножается на количество изделий по плану и корректируется возведением в степень коэффициентом уменьшения прибыли из ячейки G9. Коэффициент уменьшения отдачи отражает убывающую эффективность роста продаж за счет роста затрат на рекламу и другие затраты в системе маркетинга и сбыта. Необходимо назначить количество изделий в плане производства в строке 9 и получить максимально возможную прибыль в ячейке D12.
Математическая модель поиска оптимального плана будет выглядеть следующим образом:
i — номер строки, ресурса;
j — номер столбца, продукта;
Xj — искомое плановое количество j-го продукта;
Pj — прибыль на единицу j-го продукта;
Bi — ограниченный запас i-го ресурса на складе;
Rij — норма расхода i-го ресурса на единицу j-го продукта;
Ci — плановая сумма расхода i-го ресурса по всем продуктам;
Нам необходимо максимизировать прибыль
при ограничениях и неотрицательных количествах продуктов.
Таблицу 1.1 необходимо набрать в Excel. Для ввода формул удобнее воспользоваться режимом представления формул, для этого нужно установить галочку в меню Сервис>Параметры>Параметры окна>формулы.
Таблица 1.1.
| A | B | C | D | E | F | G |
1 |
|
|
|
|
|
|
|
2 | Наименование продукции: | Телевизор | Стерео система | Акустическая система |
| ||
3 | План производства, шт. |
|
|
|
| ||
4 | Наименование комплектующих | Запас на складе, шт. | Расход по плану, шт. | Нормы расхода ресурсов |
| ||
5 | Шасси | 450 | =$D$3*D5+$E$3*E5+$F$3*F5 | 1 | 1 | 0 |
|
6 | Кинескоп | 250 | =$D$3*D6+$E$3*E6+$F$3*F6 | 1 | 0 | 0 | Уменьшение коэффициента отдачи |
7 | Динамик | 800 | =$D$3*D7+$E$3*E7+$F$3*F7 | 2 | 2 | 1 | |
8 | Блок пит. | 450 | =$D$3*D8+$E$3*E8+$F$3*F8 | 1 | 1 | 0 | |
9 | Печатн. плата | 600 | =$D$3*D9+$E$3*E9+$F$3*F9 | 2 | 1 | 1 | 0,9 |
10 |
|
|
|
|
|
|
|
11 | Прибыль по видам изделий: | =75*МАКС(D3;0)^$G$9 | =50*МАКС(E3;0)^$G$9 | =35*МАКС(F3;0)^$G$9 |
| ||
12 | Прибыль всего: | =СУММ(D11:F11) |
|
|
|
Задание № 1. Ручной поиск оптимального плана
Изменяя количество продукции в строке 3 попытаться получить максимальную прибыль в ячейке D12. При этом необходимо визуально контролировать расход комплектующих в колонке С. Расход не должен превышать запасов на складе (колонка В).
Задание № 2. Настройка экономико-математической модели
Для обработки таблицы Excel оптимизатором необходимо вызвать его диалоговое окно Сервис>Поиск решения и настроить экономико-математическую модель. Поле Установить целевую ячейку служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. В нашем случае это максимальное значение ячейки $D$12. Поле Изменяя ячейки служит для указания ячеек, значения которых изменяются в процессе поиска решения. В нашем примере это диапазон $D$3:$F$3. Поля Ограничения служат для отображения списка граничных условий поставленной задачи. В нашем случае величины диапазона расхода комплектующих C5:C9 не должны превышать запаса на складе D5:D9 ($C$5:$C$9<=$B$5:$B$9). Количество выпускаемых изделий не может быть отрицательным ($D$3:$F$3>=0) и должно быть целым ($D$5:$D$9=целое). Так как в формулу прибыли на изделие в ячейках D11:F11 входит показатель степени G9 и его значение отлично от 1, то наша задача нелинейная. Необходимо вызвать окно настройки параметров модели и снять флажок линейной модели. Сохранить модель в ячейке B15.
Задание № 3. Компьютерный поиск оптимального плана
Вызвать команду Сервис>Поиск решения>Параметры>Загрузить модель указать область где сохранена модель, нажать OK. Проверить настройку модели затем нажать кнопку Выполнить в окне Поиск решения. Вывести отчет по результатам.
Оформление отчета
Отчет должен содержать:
Определение проблемы.
Плановую таблицу с результатами оптимального плана.
Краткую характеристику программы оптимизации Поиск решения.
Анализ оптимального плана и решения менеджера.
Написать формулы модели для оптимального планирования выпуска продукции.
Предложения по модификации и расширению модели.
Выводы обо всей проделанной работе.
- Оглавление
- Работа № 1. Оптимальные бизнес-планы, план по продукции, технология оптимизации
- Работа № 2. Выбор поставщиков, план перевозок, транспортная задача
- Работа № 3. Расчет резерва по вкладам
- Характеристика вкладов
- Справочник открытых вкладов
- Расчет резерва по счетам в месяце: ……….
- Расчет резерва по видам вкладов в месяце: ……….
- Работа № 4. Оптимальная ставка налога, имитационное моделирование
- Работа № 5. Разработка аис для расчета амортизационных отчислений
- Справочник сроков и способов расчета амортизации Вх.Ф.№ 1
- Расчет годовой суммы амортизации Пром.Ф.№ 1
- Работа № 6. Разработка автоматизированной системы по начислению заработной платы
- Учетные сведения о сотрудниках отделения
- Количество дней невыхода на работу без причины
- Количество дней нахождения в административном отпуске
- Количество дней по больничным листам
- Фактическое количество отработанных дней в текущем месяце
- Справочные данные
- Разряды единой тарифной сетки
- Работа № 7. Создание локальных реляционных баз данных
- Работа № 8. Обработка данных в локальных реляционных базах данных
- Работа № 9. Нормализация реляционной бд
- Работа № 10. Создание er-модели и ее нормализация