Контрольная работа

Цель: освоение методики и технологии оптимизации планов в табличном процессоре Excel с помощью программы «Поиск решения» (Solver).

Задачи:

• составление наилучших (оптимальных) планов выпуска продукции;
• определение оптимального количества сотрудников в штате и т.д.

Многие проблемы прогнозирования, проектирования и производства сводятся к широкому классу задач оптимизации. Такими задачами являются, например:

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

Надстройка MS Excel «Поиск решений» позволяет решать широкий круг задач на оптимизацию.

«Поиск решений» в Excel позволяет без временных затрат находить оптимальные решения достаточно сложных моделей, не только линейных, без знания алгоритмов и длительных рутинных итераций. «Поиск решений» является частью блока задач, который иногда называют анализ "что-если". Процедура поиска решения позволяет найти оптимальное значение формулы содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки.

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

1. Решить задачу оптимального плана выпуска объективов согласно варианту:

Предприятие решило производить два вида объективов А и В. Объектив вида А состоит из 3-х линзовых компонентов, вид В - из 4-х. За неделю можно изготовить не более определенного количества линз. На сборку объектива каждого вида требуется определенное количество времени. Рабочая неделя для 4 сотрудников составляет 160 часов. Сколько объективов А и В надо изготовить, чтобы получить максимальную прибыль?

2. Решить задачу определения оптимального количества сотрудников в штате согласно варианту:

Штат научно-исследовательской лаборатории (НИЛ) должен состоять из лаборантов, инженеров, младших научных сотрудников (м.н.с.), старших научных сотрудников (с.н.с.), ведущих научных сотрудников, и заведующего НИЛ.

Необходимо определить, учитывая общий месячный фонд зарплаты, какими должны быть оклады сотрудников НИЛ при условии, что оклад лаборанта не должен быть меньше прожиточного минимума 6 тыс.руб. Инженер получает в 1,8 раза и на 600 руб. больше лаборанта. Младший научный сотрудник получает в 1,5 раза больше лаборанта и на 1100 руб. больше инженера. Старший научный сотрудник получает в 3,5 раза больше лаборанта. Ведущий научный сотрудник получает в 3,2 раза больше лаборанта, на 3200 руб. больше м.н.с.

3. Решить задачу проектирования емкости согласно варианту:

Спроектировать емкость - параллелепипед с объемом V = abh = 3000 (длина, ширина, высота). Емкость должна иметь минимальную площадь стенок S = ab+ab+bh+bh+ah+ah.

4. Решить задачу проектирования площади участка минимального периметра согласно варианту:

Участок имеет площадь S = аb = 800 м. При какой длине (а) и ширине (b) периметр будет минимальным.

Требуется для ТулГУ