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

Оглавление

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

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

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

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

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

Работа № 6. Разработка автоматизированной системы по начислению заработной платы... 35

работа № 7. Создание локальных реляционных баз данных 43

работа № 8. Обработка данных в локальных реляционных базах данных.. 53

работа № 9. Нормализация реляционной БД.. 61

работа № 10. Создание ER-модели и ее нормализация.. 70

Работа № 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
	Наименование продукции:	Телевизор	Стерео система	Акустическая система
	План производства, шт.
	Наименование комплектующих	Запас на складе, шт.	Расход по плану, шт.	Нормы расхода ресурсов
	Шасси		=$D$3*D5+$E$3*E5+$F$3*F5
	Кинескоп		=$D$3*D6+$E$3*E6+$F$3*F6				Уменьшение коэффициента отдачи
	Динамик		=$D$3*D7+$E$3*E7+$F$3*F7
	Блок пит.		=$D$3*D8+$E$3*E8+$F$3*F8
	Печатн. плата		=$D$3*D9+$E$3*E9+$F$3*F9				0,9
	Прибыль по видам изделий:	=75*МАКС(D3;0)^$G$9	=50*МАКС(E3;0)^$G$9	=35*МАКС(F3;0)^$G$9
	Прибыль всего:	=СУММ(D11:F11)

Задание № 1. Ручной поиск оптимального плана

Изменяя количество продукции в строке 3 попытаться получить максимальную прибыль в ячейке D12. При этом необходимо визуально контролировать расход комплектующих в колонке С. Расход не должен превышать запасов на складе (колонка В).