Оглавление

Работа № 1. Оптимальные бизнес-планы, план по продукции, технология оптимизации 2

Работа № 2. Выбор поставщиков, план перевозок, транспортная задача 7

Работа № 3. Расчет резерва по вкладам 12

Работа № 4. Оптимальная ставка налога, имитационное моделирование 18

Работа № 5. Разработка АИС для расчета амортизационных отчислений 22

Работа № 1. Оптимальные бизнес-планы, план по продукции, технология оптимизации

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

Цель работы

1. Освоить методику и технологию оптимизации планов в табличном процессоре MS Excel с помощью программы Поиск решения (Slover).

2. Научиться составлять наилучший (оптимальный) план производства продукции с учетом ограниченного обеспечения материальными ресурсами.

Постановка задачи

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

Следует учитывать уменьшение удельной прибыли при увеличении объемов производства в связи с дополнительными затратами на сбыт.

Порядок выполнения работы

Исходные данные приведены в Таблице 1.1. Наименование продукции расположено в строке 2, в строке 3 расположены ячейки искомого плана. В колонке А приводится наименование комплектующих изделий, необходимых для производства продукции. В колонке B задан запас комплектующих на складе. Нормы расхода комплектующих на производство одного изделия задаются в матрице диапазона D5:F9. Плановые затраты комплектующих на производство всех типов изделий вычисляются в колонке С и не должны превышать запасов на складе. Прибыль по каждому типу изделий вычисляется в строке 17, числа 75, 50 и 35 означают прибыль на единицу продукции, которая умножается на количество изделий по плану и корректируется возведением в степень коэффициентом уменьшения прибыли из ячейки G9. Коэффициент уменьшения отдачи отражает убывающую эффективность роста продаж за счет роста затрат на рекламу и другие затраты в системе маркетинга и сбыта. Необходимо назначить количество изделий в плане производства в строке 9 и получить максимально возможную прибыль в ячейке D12.

Математическая модель поиска оптимального плана будет выглядеть следующим образом:

i — номер строки, ресурса;

j — номер столбца, продукта;

X_j — искомое плановое количество j-го продукта;

P_j — прибыль на единицу j-го продукта;

B_i — ограниченный запас i-го ресурса на складе;

R_ij — норма расхода i-го ресурса на единицу j-го продукта;

C_i — плановая сумма расхода 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. Написать формулы модели для оптимального планирования выпуска продукции.

6. Предложения по модификации и расширению модели.

7. Выводы обо всей проделанной работе.