Решение обратной задачи «Как сделать, чтобы?» (подбор параметра)

Лабораторное работа-16

Применение MS Excel при решении задач оптимизации

Подбор оптимального графика работы персонала

Решить задачу оптимизации - подбор оптимального графика работы для работников с пятидневной рабочей неделей и двумя выходными подряд. График работы должен обеспечивать требуемый уровень обслуживания при наименьших затратах на оплату труда.

Цель - минимизация расходов на оплату труда.

Изменяемые данные - число работников в группе.

Ограничения:

- число работников не может быть отрицательным;

- число работников должно быть целым числом;

- число ежедневно занятых работников не должно быть меньше ежедневной потребности.

Создайте модель как на рис.1.

В ячейке D11 наберите формулу =СУММ(D3:D9), D15 – 400, в D16 - формула =D11*D15.

В диапазоне F3:L9 цифра 1 означает, что день рабочий, 0 – выходной.

В ячейке F11 наберите формулу

=$D$3*F3+$D$4*F4+$D$5*F5+$D$6*F6+$D$7*F7+$D$8*F8+$D$9*F9

и распространите ее на все дни недели до столбца L включительно.

В 13-й строке (Всего требуется) запишите значения правых частей ограничений: H13 - 15, I13 - 14, J13 - 16, K13 - 18, L13 – 23 (рис.1).

Рис.1.

Запустите режим Поиск решения. Целевая ячейка - $D$16 (рис.2), выбрать вариант минимальному значению (цель задачи - минимизация расходов на оплату).

Изменяемые ячейки: $D$3:$D$9 (изменяемые данные - число работников в группе).

Ограничения:

$D$3:$D$9>=0,

$D$3:$D$9=целое,

$F$11:$L$11>=$F$13:$L$13.

В Параметрах поиска решения задайте Линейную модель расчета. Далее ОК, Выполнить. Проанализируйте полученное решение максимизации итоговой прибыли при заданных ограничениях и сделайте выводы.

Решение обратной задачи «Как сделать, чтобы?» (подбор параметра)

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

Решите обратную задачу: определение штатного расписания фирмы с использованием режима «Подбор параметра».

Известно (рис.1), что в штате фирмы состоит: 6 курьеров; 8 младших менеджеров; 10 менеджеров; 3 заведующих отделами; 1 главный бухгалтер; 1 программист; 1 системный аналитик; 1 генеральный директор фирмы. Общий месячный фонд зарплаты составляет 250000 р.

Необходимо определить, какими должны быть оклады сотрудников фирмы. Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата =А_i*х+В_i, где х — оклад курьера; А_i, и B_i — коэффициенты, показывающие: A_i — во сколько раз превышается значение х; B_i — на сколько превышается значение х.

Рис. 1. Исходные данные

Порядок решения

1. Выделите отдельную ячейку D3 для зарплаты курьера (переменная «х»)и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.

2. В столбце D введите формулу для расчета заработной платы по каждой должности. Например, для ячейки D6: =В6*$D$3+С6.

3. В столбце F задайте формулу расчета заработной платы всех работающих в данной должности. Например, для ячейки F6: =D6*Е6.

В ячейке F14 вычислите суммарный фонд заработной платы фирмы.

4. Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы, равной 250000 р. (Сервис - Подбор параметра). Для этого (рис.2):

в поле Установить в ячейке введите ссылку на ячейку F14;

в поле Значение наберите искомый результат 250000;

в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3 (зарплата курьера), ОК.