ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ В МЕНЕДЖМЕНТЕ

Лабораторная работа 4. РЕЖИМ ПОДБОРА ПАРАМЕТРОВ

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

Примеры.

Задание 1. Используя режим Подбора параметра (Данные/ анализ «что-если»/ подбор параметра), определите, при каком зна­чении процента премии общая сумма месячной заработной платы всех сотрудников фирмы, предназначенная к выдаче, будет равна 500 000 руб.

Решение. Создайте таблицу расчета заработной платы по исходным данным  (рисунок 21).  Произведите расчёты во всех столбцах таблицы, отмеченных знаком «?». При расчёте столбца "Премия" используйте формулу Премия = Оклад * % Премии, (=$D$5*С6), обратите внимание, что ячейка D5 используется в виде абсолютной адресации и строки и столбца. После набора формулу следует скопировать вниз по столбцу автозаполнением.

 Рисунок 21- Ведомость начисления заработной платы

Формула для расчёта столбца «Всего начислено»: Всего на­числено = Оклад + Премия.

Формула для расчёта столбца «Удержания»: Удержания = Всего начислено * % Удержаний, в ячейке F6 наберите =$F$5*Е6. Далее эта формула копируется вниз по столбцу F автозаполнением.

Формула для расчёта столбца «К выдаче»: К выдаче = Все­го начислено — Удержания.

           По столбцу К выдаче рассчитайте среднее значение, найдите максимальное значение и минимальное значение. Для этого воспользуйтесь функциями «МАКС», «СРЗНАЧ», «МИН» соответственно. Откройте окно Мастер функций, выберите категорию – СТАТИСТИЧЕСКИЕ, функцию «МАКС» (рисунок 22). В строку 1 введите диапазон - столбец К выдаче (без итоговой суммы). Аналогично работают функции «СРЗНАЧ», «МИН».

                                  Рисунок 22- Диалоговое окно Мастера функций.

Результаты работы и итоговый вид таблицы для расчёта за­работной платы представлены на рисунке 23.

  Рисунок 23- Ведомость начисления заработной платы

Из расчетов видно, что общая сумма к выдаче при указанных окладах и пре­мии в размере 27 % составляет 104 799,77 руб. Теперь необходимо осуществить подбор параметра командой, для чего установите курсор в ячейке об­щей суммы К выдаче и воспользуйтесь меню Данные/ анализ «что-если»/ подбор параметра. Откроется окно, как на рисунке 24.

Рисунок 24 -  Диалоговое окно Подбор параметра

Рисунок 25 -  Подбор параметра

В диалоговом окне «Подбор параметра» на первой строке Установить в ячейке в качестве подбираемого параметра должен нахо­диться адрес той ячейки, в которой необходимо получить заданное значение. В данном случае это общая итоговая сумма зарплаты к выдаче (ячей­ка G20, на которой установлен курсор). На второй строке у поля «Значение» диалогового окна «Подбор параметра» наберите желаемое значение параметра, в данном примере это число равно 250 000, на третьей строке в поле «Изменяя значение ячейки» укажите адрес подбираемого значения «% Премии» (ячейка D5), после чего нажмите кнопку ОК (рисунок 25).

Произойдёт почти моментальный пересчёт всей таблицы, и от­кроется окно «Результат подбора параметра» (рисунок 26), в котором дайте подтверждение подобранному параметру нажатием ОК.

Итак, произошёл обратный пересчёт «% Премии». Результа­ты подбора выглядят так: если сумма к выдаче равна 250 000 руб., то процент премии должен быть 203%.

 Рисунок 26 - Таблица расчета заработной платы

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

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

6 курьеров;

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

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

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

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

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

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

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

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

Решение. Каждый оклад является линейной функцией от оклада курье­ра, а именно: зарплата равна А * Z + В,

где Z — оклад курьера;

А и В — коэффициенты, показывающие соответственно, во сколь­ко раз превышается значение Z и на сколько превышается значе­ние Z.

Создайте таблицу штатного расписания фирмы по приведён­ному на рисунке 27 образцу. Введите исходные данные в рабочий лист электронной книги.

               Рисунок 27-   Штатное расписание фирмы (таблица исходных данных)

В столбце Коэф. В указаны надбавки к окладам. Выделите отдельную ячейку D16 для зарплаты курьера (пере­менная Z) и с учётом этого задайте все расчёты. В ячейку D16 вре­менно введите произвольное число, например 500.

В столбце Зарплата сотрудника введите формулу для расчёта заработной платы по каждой должности. Далее скопируйте формулу из ячейки E4 вниз по столбцу автозаполнением.

В столбце Суммарная зарплатпа определите заработную плату всех работающих в данной должности, путем умножения заработной платы работника на количество работающих в этой должности. В ячейке G12 вычислите суммарный фонд заработной платы фирмы, используя автосуммирование [1].

Произведите подбор зарплат сотрудников фирмы для сум­марной заработной платы в размере 100 000 руб. Для этого выберите  ПОДБОР ПАРАМЕТРА.

В поле Установить в ячейке появившегося окна введите ссылку на ячейку G12, содержащую формулу расчёта фонда зара­ботной платы; в поле Значение наберите искомый результат — 100000; в поле Изменяя значение ячейки введите ссылку на из­меняемую ячейку D16, в которой находится значение зарплаты курьера, и щелкните на кнопке ОК. Произойдёт обратный расчет зарплаты сотрудников по заданному условию при фонде зарпла­ты, равном 100 000 руб. (рисунок 28).

Рисунок 28 -  Штатное расписание фирмы

Итог обработанного пересчета таков: при фонде зарплаты, равном 100 000 руб., зарплата курьера должна составлять 1289,66 руб.

Следует заметить, что если возникает необходи­мость копировать результаты, полученные подбо­ром параметра, то следует производить копиро­вание полученных данных в виде значений с ис­пользованием СПЕЦИАЛЬНОЙ ВСТАВКИ.

Для этого необходимо выделить ко­пируемые данные, произвести запись в буфер па­мяти (ПРАВКА > КОПИРОВАТЬ), установить курсор в соответствующую ячейку, задать режим специ­альной вставки (ПРАВКА > СПЕЦИАЛЬНАЯ ВСТАВКА), отметив в качестве объекта вставки "значения" (ПРАВКА — СПЕЦИАЛЬНАЯ ВСТАВКА — ЗНАЧЕНИЯ) (рисунок 29).

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

Рисунок 29 – Диалоговое окно «Специальная вставка»

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

Ø   Для решения каких задач используется режим ПОДБОРА ПАРАМЕТРОВ?

Ø   Адрес какой ячейки должен находиться в диалоговом окне «Подбор параметра» в первой строке?

Ø  В каких случаях следует производить копиро­вание данных с ис­пользованием специальной вставки?