ХОД РАБОТЫ. 1. Выполнение первого задания:

1. Выполнение первого задания:

Пусть известно, что для нормальной работы фирмы требуется 5...7 курьеров, 8... 10 младших менеджеров, 10 менеджеров, 3 за­ведующих отделами, главный бухгалтер, программист, системный аналитик, генеральный директор фирмы.

Общий месячный фонд зарплаты должен быть минимален. Не­обходимо определить, какими должны быть оклады сотрудников фирмы, при условии, что оклад курьера не должен быть меньше 1400 р.

В качестве модели решения этой задачи возьмем линейную мо­дель. Тогда условие задачи имеет вид

N 1 * А1 * х + N₂ * (А₂ * х + В₂) +... + N₈ * (А₈ * х + В₈) = Минимум,

где N, - количество работников данной специальности; х — зарп­лата курьера; А, и В, — коэффициенты заработной платы сотруд­ников фирмы.

· Запустите редактор электронных таблиц Microsoft Excel и от­кройте созданный в Практической работе 11 файл «Штатное рас­писание».

Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2».

· В меню Сервис активизируйте команду Поиск решения.

· В окне Установить целевую ячейку укажите ячейку F14, содер­жащую модель — суммарный фонд заработной платы.

Поскольку необходимо минимизировать общий месячный фонд зарплаты, активизируйте кнопку равный — Минимальному значе­нию.

· В окне Изменяя ячейки укажите адреса ячеек, в которых будет отражено количество курьеров и младших менеджеров, а также зарплата курьера — $E$6: $E$7: $D$3 (при задании ячеек Е6, Е7 и D3 держите нажатой клавишу [Ctrl]).

· Используя кнопку Добавить в окнах Поиск решения и Добавле­ние ограничений, опишите все ограничения задачи: количество ку­рьеров изменяется от 5 до 7, младших менеджеров от 8 до 10, а зарплата курьера > 1400.

· Ограничения наберите в виде

$D$3 > = 1400

$Е$6 > = 5

$Е$6 < = 7

$Е$7 > = 8

$Е$7 < = 10.

Активизировав кнопку Параметры, введите параметры поиска, как показано ниже.

Окончательный вид окна Поиск решения.

Запустите процесс поиска решения нажатием кнопки Выпол­нить. В открывшемся диалоговом окне Результаты поиска решения задайте опцию Сохранить найденное решение.

Решение задачи приведено ниже. Оно тривиально: чем меньше сотрудников и чем меньше их оклад, тем меньше месяч­ный фонд заработной платы.

2. Выполнение первого задания:

Фирма производит несколько видов продукции из одного и того же сырья — А, В и С. Реализация продукции А дает прибыль 10 р., В — 15 р. и С — 20 р. на единицу изделия.

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

Нормы расхода сырья на производство продукции каждого вида приведены в таблице.

· Запустите редактор электронных таблиц Microsoft Excel и со­здайте новую электронную книгу.

· Создайте расчетную таблицу как ниже.

Введите исход­ные данные и формулы в электронную таблицу. Расчетные форму­лы имеют такой вид:

Расход сырья 1 = (количество сырья 1) * (норма расхода сырья А) + (количество сырья 1) * (норма расхода сырья В) + (количес­тво сырья 1) * (норма расхода сырья С).

Значит, в ячейку F5 нужно ввести формулу = В5 * $В$9 + C5 * * $С$9 + D5 * $D$9.

Обратите внимание, что значения количества сырья каждого вида пока не известны и будут подобраны в процессе решения задания (ячейки B9: D9 пока пустые).

(Общая прибыль по А) = (прибыль на ед. изделий А) * (количе­ство А), следовательно в ячейку В10 следует ввести формулу = В8 * В9.

Итоговая общая прибыль = (Общая прибыль по А) + (Общая прибыль по В) + (Общая прибыль по С), значит в ячейку ЕЮ следует ввести формулу = СУММ(В10: О10).

В меню Сервис активизируйте команду Поиск решенияш введи­те параметры поиска, как указано ниже.

В качестве целевой ячейки укажите ячейку «Итоговая общая прибыль» (ЕЮ), в качестве изменяемых ячеек — ячейки количе­ства сырья — (B9: D9).

Не забудьте задать максимальное значение суммарной прибыли и указать ограничения на запас сырья:

расход сырья 1 < = 350; расход сырья 2 < = 200; расход сырья 3 < = 100, а также положительные значения количества сырья А,

В, С > = 0.

Установите параметры поиска решения. Для этого кнопкой Параметры откройте диалогового окно Параметры поиска решения, установите параметры по образцу, задайте линейную модель расчета (Линейность модели).

Кнопкой Выполнить запустите Поиск решения.

· Сохраните созданный документ под именем «План производ­ства».

Выводы. Из решения видно, что оптимальный план выпуска предусматривает изготовление 5, 56 кг продукции В и 22, 22 кг про­дукции С. Продукцию А производить не стоит. Полученная при­быль при этом составит 527, 78 р.

3. Выполнение третьего задания:

Используя файл «План производства» (см. задание 2), оп­ределить план выгодного производства, т.е. какой продукции и сколько необходимо произвести, чтобы общая прибыль от реа­лизации была максимальной.

Выберите нормы расхода сырья на производство продукции каждого вида и ограничения по запасам сырья из таблицы соот­ветствующего варианта (5 вариантов):

Вариант 1

Вариант 2

Вариант 3

Вариант 4

Вариант 5

4. Сделать вывод о проделанной работе.

Контрольные вопросы

1. Как осуществлять поиск решения?
2. Как добавить ограничение в поиск решения?
3. Как задать параметр поиска?

ЛИТЕРАТУРА

1. Михеева Е.В. Практикум по информационным технологиям в профессиональной деятельности: учеб. пособие для студ. сред. проф. образования / Е.В. Михеева. – 9-е изд., стер. – М.: Издательский центр «Академия», 2010. – 256 с.