Лабораторная работа 7. Расчеты и выбор решения по результатам

Цель занятия: изучение технологии подбора параметра при об­ратных расчетах. Поиск решения.

Задание 1. Используя режим подбора параметра, определить штатное расписания фирмы. Исходные данные приведены на рисунке.

Известно, что в штате фирмы состоит:

• 6 курьеров;

• 8 младших менеджеров

• 10 менеджеров

• 3 заведующих отделами;

• 1 главный бухгалтер;

• 1 программист;

• 1 системный аналитик;

• 1 генеральный директор фирмы.

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

Каждый оклад является линейной функцией от оклада курьера

1.Создайте таблицу штатного расписания фирмы по приведен­ному образцу.

2.Выделите отдельную ячейку D3 для зарплаты курьера введите произвольное число.

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

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

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

6.Произведите подбор зарплат сотрудников фирмы для суммар­ной заработной платы, равной 100 000 р. (Сервис/Подбор параметра)

7. В поле Установить в ячейке появившегося окна введите ссылку на ячейку F14, содержащую формулу расчета фонда заработной плати; В поле Значение наберите искомый результат 100 000;в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3. в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100000р.

8. Присвоите рабочему листу имя «Штатное расписание 1». Со­храните созданную электронную книгу под именем «Штатное рас­писание» в своей папке.

Задание 2. Минимизация фонда заработной платы фирмы.

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

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

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

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

Поскольку необходимо минимизировать общий месячный фонд

зарплаты, активизируйте кнопку равный — Минимальному значению.

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

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

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

$D$3 >=1400

$E$6 >=5

$E$6 <=7

$E$7 >=8

$E$7 >=10

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

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

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