Лабораторная работа №3. Решение экономических задач с помощью функции подбора параметров (Составление штатного расписания хозрасчетной больницы)
Цель работы: на примере составления штатного расписания хозрасчетной больницы научиться решать экономические задачи с помощью функции подбора параметров
Постановка задачи:
Заведующий хозрасчетной больницы должен составить штатное расписание, то есть определить, сколько сотрудников, на каких должностях и с каким окладом он должен принять на работу. Общий месячный фонд зарплаты составляет 100 000 руб.
Исходные данные: заведующий знает, что для нормальной работы больницы требуется 5-7 санитарок, 8-10 медсестер, 10-12 врачей, 1 заведующий аптекой, 1 заведующий больницей. На некоторых должностях число людей может меняться. Например, зная, что найти санитарок сложно, руководитель может принять решение о сокращении числа санитарок, чтобы увеличить оклад каждой из них.
Заведующий берет за основу следующую модель. За основу берется оклад санитарки, а все остальные вычисляются исходя из него: A * C + B, где C – оклад санитарки, A, B – коэффициенты, которые определяются для каждой должности решением трудового коллектива.
Допустим, что совет решил:
Медсестра должна получать в 1,5 раза больше санитарки, то есть A = 1.5, B = 0;
Врач – в 3 раза больше санитарки (A = 3, B = 0);
Заведующий отделением – на 300 руб. больше, чем врач (A = 3; B = 300);
Заведующий аптекой – в 2 раза больше санитарки (A = 2; B = 0);
Заведующий хозяйством – на 400 руб. больше медсестры (A = 1.5; B = 400);
Главный врач – в 4 раза больше санитарки (A = 4; B = 0);
Заведующий больницей – на 200 руб. больше главного врача (A = 4; B = 200).
Ход выполнения работы:
Заполнение таблицы. Введем данные о коэффициентах A и B в ячейки B6:C13 в соответствии с представленными выше рекомендациями. Далее для каждой должности введем формулу, определяющую зарплату сотрудника =B6*$H$5+C6 - для санитарки (ячейка D6). Продлим формулу на ячейки D7:D13. Заметим, что используется ссылка на ячейку H5, которая представляет собой зарплату санитарки.
Задав количество сотрудников для каждой должности в соответствии с представленными выше рекомендациями, можно вычислить суммарную зарплату для каждой должности – так для всех санитарок будем использовать формулу =D6*E6. Продлим формулу на ячейки D7:D13.
В ячейке F15 представлен суммарный месячный фонд зарплаты всех сотрудников больницы =СУММ(F6:F13).
Теперь изменяя величину заработной платы санитарки (значение в ячейке H5), мы будем получать различные значения месячного фонда зарплаты. Нужно подобрать такое значение H5, при котором мы не превысим предоставленных нам 100 000 руб. Также мы можем изменять число сотрудников!
Сделать вручную это очень сложно, поэтому мы воспользуемся функционалом Excel – Поиск решения. Введем следующие параметры:
То есть мы хотим найти такое значение H5, при котором значение F5 составит 100 000. После нажатия кнопки Выполнить, мы получим следующее решение: 1611.38 – заработная плата санитарки – см. штатное расписание в таблице 3.1.
Таблица 3.1 – Штатное расписание №1
|
ШТАТНОЕ РАСПИСАНИЕ ХОЗРАСЧЕТНОЙ БОЛЬНИЦЫ | |||||
|
зав. больницей Петров И.С. | |||||
|
должность |
коэф. |
коэф. |
зарплата |
кол-во |
суммарная |
|
|
A |
B |
сотрудника |
сотрудников |
зарплата |
|
|
|
|
|
|
|
|
санитарка |
1 |
0 |
1611,38 |
5 |
8056,91 |
|
медсестра |
1,5 |
0 |
2417,07 |
8 |
19336,59 |
|
врач |
3 |
0 |
4834,15 |
10 |
48341,46 |
|
зав. отделением |
3 |
300 |
5134,15 |
1 |
5134,15 |
|
зав. Аптекой |
2 |
0 |
3222,76 |
1 |
3222,76 |
|
завхоз |
1,5 |
400 |
2817,07 |
1 |
2817,07 |
|
главврач |
4 |
0 |
6445,53 |
1 |
6445,53 |
|
зав. Больницей |
4 |
200 |
6645,53 |
1 |
6645,53 |
|
|
|
|
|
|
|
|
|
|
|
|
месячный |
100000,00 |
|
|
|
|
|
фонд |
|
|
|
|
|
|
зарплаты |
|
Если мы изменим число санитарок, медсестер и врачей, то мы можем получить новое штатное расписание и новые данные о заработной плате на каждой должности – см. таблицу 3.2, зарплата санитарки 1753,98 руб. Для таблицы 3.2 произведем Автоформатирование.
Таблица 3.2 – Штатное расписание №2
|
ШТАТНОЕ РАСПИСАНИЕ ХОЗРАСЧЕТНОЙ БОЛЬНИЦЫ | |||||
|
зав. больницей Петров И.С. | |||||
|
должность |
коэф. |
коэф. |
зарплата |
кол-во |
суммарная |
|
|
A |
B |
сотрудника |
сотрудников |
зарплата |
|
|
|
|
|
|
|
|
санитарка |
1 |
0 |
1753,98 |
3 |
5261,95 |
|
медсестра |
1,5 |
0 |
2630,97 |
8 |
21047,79 |
|
врач |
3 |
0 |
5261,95 |
9 |
47357,52 |
|
зав. отделением |
3 |
300 |
5561,95 |
1 |
5561,95 |
|
зав. Аптекой |
2 |
0 |
3507,96 |
1 |
3507,96 |
|
завхоз |
1,5 |
400 |
3030,97 |
1 |
3030,97 |
|
главврач |
4 |
0 |
7015,93 |
1 |
7015,93 |
|
зав. Больницей |
4 |
200 |
7215,93 |
1 |
7215,93 |
|
|
|
|
|
|
|
|
|
|
|
|
месячный |
100000,00 |
|
|
|
|
|
фонд |
|
|
|
|
|
|
зарплаты |
|
Выводы: в ходе выполнения работы мы узнали, что ЭТ Excel позволяют решать сложные экономические задачи с помощью Поиска решения.