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

Исходные данные приведены на рис. 5.4.

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

· 6 курьеров;

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

· 10 менеджеров;

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

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

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

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

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

Общий месячный фонд зарплаты составляет 100 000 руб. Необходимо определить, какими должны быть оклады сотрудников фирмы.
Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата = Ai * х+Вi, где х - оклад курьера; Аi и Вi - коэффициенты, показывающие:

· Ai – во сколько раз превышается значение x;

· Bi – на сколько превышается значение x.

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

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

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

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

Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы в сумме 100000 руб. Активизируйте функцию подбор параметра введите значения согласно рис. 5.5.

Рис. 5.5. Подбор параметра

Нажмите кнопку OK и проследите изменения в таблице. Примерный вид таблицы представлен на рис. 5.6.

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

Анализ задач показывает, что с помощью MS Excel можно решать линейные уравнения. Задания 1 и 2 показывают, что поиск значения параметра формулы – это не что иное, как численное решение уравнений.
Другими словами, используя возможности программы MS Excel, можно решать любые уравнения с одной переменной.

Рис. 5.6. Итоговая таблица

Задание 3.

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

Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2». Выберите коэффициенты уравнений для расчета согласно рис. 5.7. (один из пяти вариантов расчетов).

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

Методом подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100000, 150000, 200000, 250000, 300000, 350000, 400000 р. Результаты подбора значений зарплат скопируйте в таблицу, представленную на рис. 5.8 в виде специальной вставки.

Рис. 5.8. Таблица для заполнения

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

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