Изучение технологии подбора параметра при обратных расчетах

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

Задание: Используя режим подбора параметра, определить, при каком значении % Премии общая сумма заработной платы за октябрь будет равна 250000 р. (на основании файла «Зарплата», созданного ранее).
Краткая справка. К исходным данным этой таблицы относятся значения Оклада и % Премии, одинакового для всех сотрудников. Результатом вычислений являются ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов расчетов. Использование операции «Подбор параметра» в MS EXCEL позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра, и по этому значению подбирается некоторое удовлетворяющее заданным условиям, значение исходного параметра расчета.
Порядок работы:
1. Запустите редактор электронных таблиц MS EXCEL и откройте созданный ранее файл «Зарплата».
2. Скопируйте содержимое листа «Зарплата октябрь» на новый лист электронной книги.
3. Осуществите подбор параметра командой Сервис/Подбор параметра.
В диалоговом окне Подбор параметра на первой строке в качестве



подбираемого параметра укажите адрес общей итоговой суммы зарплаты (ячейка G19), на второй строке наберите заданное значение 250000, на третьей строке укажите адрес подбираемого значения % Премии (ячейка D4), затем нажмите кнопку ОК. В окне Результат подбор параметра дайте подтверждение подобранному параметру нажатием кнопки ОК.
Произойдет обратный перерасчет % Премии, если сумма к выдаче 25000 р., то % Премии должен быть 203%.



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


Краткая справка. Известно, что в штате фирмы состоит:
• 6 курьеров;
• 8 младших менеджеров;
• 10 менеджеров;
• 3 заведующих отделами;
• 1 главный бухгалтер;
• 1 программист;
• 1 системный аналитик
• 1 генеральный директор фирмы.
Общий месячный фонд зарплаты составляет 100000 р. Необходимо определить, какими должны быть оклады сотрудников фирмы.
Каждый оклад является линейной функцией от оклада курьера, а именно: зарплата=А1*х+В1 , где х - оклад курьера; А1 и В1 – коэффициенты, показывающие: А1 – во сколько раз превышается значение х; В1 – на сколько раз превышается значение х.
Порядок работы:
1. Запустите редактор электронных таблиц MS EXCEL.
2. Создайте таблицу штатного расписания фирмы по приведенному выше образцу. Введите исходные данные в рабочий лист электронной книги.
3. Выделите отдельную ячейку D3 для зарплаты курьера (переменная «х») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число.
4. В столбце В введите формулу для расчета заработной платы по каждой должности. Например, для ячейки D6 формула расчета имеет следующий вид: =B6*$D$3+C6.
В столбце F задайте формулу для расчета заработной платы всех работающих в данной должности. Суммарная зарплата = Зарплата сотрудника * Кол-во сотрудников.
5. Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы, равной 100000 р.

6. Присвойте рабочему листу имя «Штатное расписание 1». Сохраните созданную электронную книгу под именем «Штатное расписание» в своей папке.
Задание 2. Используя режим подбора параметра и таблицу расчета штатного расписания (см. задание 1), определить заработные платы сотрудников фирмы для ряда заданных значений фонда заработной платы.
Порядок работы:
1. Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2». Выберите коэффициенты уравнений для расчета согласно таблице (один из пяти вариантов расчетов по согласованию с преподавателем).

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