МУ ИСЭ ЭК 2013
.pdf72
|
Ведомость начислений |
Таблица 16 |
||
|
|
|||
Табельный номер |
|
Начислено |
|
|
По окладу |
Премия |
Всего |
||
|
||||
|
|
|
|
|
|
|
|
|
|
|
Ведомость удержаний |
|
Таблица 17 |
|||
|
|
|
|
|||
Табельный номер |
|
Удержано |
|
|
|
|
Подоходный |
Пенсионный налог |
|
Исполнительные |
|
Всего |
|
|
налог |
|
листы |
|
||
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Для расчетов используются следующие формулы: |
|||
|
начисленная заработная плата |
ЗП = ЗПокл + ПР; |
||
|
начисленная оплата по окладу |
ЗПокл ОКЛ |
ФТ |
; |
|
||||
|
|
|
Т |
|
|
размер премии |
ПР = ЗПокл %ПР; |
||
|
общее удержание из зарплаты |
У = Упн + Упф + Уил; |
||
|
удержание подоходного налога |
Упн = (ЗП МЗП Л) 0,12; |
||
|
удержание пенсионного налога |
Упф = ЗП 0,01; |
||
|
удержание по исполнительным |
|
|
|
|
листам |
Уил = (ЗП Упн) %ИЛ; |
||
|
заработная плата к выдаче |
ЗПВ = ЗП – У, |
||
где ОКЛ – оклад работника в соответствии с его разрядом; ФT – фактически отработанное время в расчетном месяце (дн.); Т – количество рабочих дней в месяце; %ПР – процент премии в расчетном месяце; МЗП – минимальная зарплата; Л – количество льгот; %ИЛ – процент удержания по исполнительным листам.
Оклад работника также зависит от его квалификации (разряда). Разрядная сетка является справочником и представлена в виде табл. 18 (разряды от 1 до 18, оклады от 10000 руб. до 18500 руб.).
Таблица 18
|
Разрядная сетка |
|
Разряд |
|
Оклад, руб. |
|
|
|
1 |
|
10000 |
… |
|
… |
18 |
|
18500 |
Размер удержания по исполнительным листам работника зависит от процента удержания. Сведения о работниках, с которых необходимо удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 19).
|
73 |
|
Таблица 19 |
Справочник по исполнительным листам |
|
Табельный номер |
Процент удержаний |
102 |
25 |
105 |
20 |
107 |
5 |
В процессе решения задачи задайте размер минимальной оплаты труда и количество рабочих дней в месяце, процент премии в зависимости от выслуги лет и размер прожиточного минимума.
Решение:
1.Переименуйте Лист1 в Плановый фонд ЗП.
2.Начиная с ячейки А1 введите табл. 14 (рис. 33).
Рис. 33. Макет данных ведомости Личный счет
3. Таблицу Разрядная сетка наберите с ячейки B11 (рис. 34).
Рис. 34. Макет заголовков таблиц
Заполните таблицу Разрядная сетка, используя функцию автозаполнения. Для этого:
введите 1 в ячейку B13;
установите указатель в ячейку B13 на маркер в правом нижнем углу. Указатель мыши примет форму ;
удерживая клавишу Ctrl и левую клавишу мыши, протащите указатель по диапазону B14:B30. Диапазон ячеек заполнился числами от 1 до 18;
введите 10000 в ячейку C13 и 10500 в ячейку С14;
выделите ячейки С13:С14, используя автозаполнение заполните диапазон С13:С30.
4. Таблицу Справочник по исп. листам наберите с ячейки E11. Заполните
Справочник по исп. листам согласно табл. 19.
5. Создайте Справочник работников, осуществив ввод данных с ячейки
L1 (рис. 35).
74
Рис. 35. Таблица Справочник работников
6. Начиная с ячейки A34, сформируйте Ведомость начислений (рис. 36).
Введите с ячейки A42 – Ведомость удержаний (рис. 37).
Рис. 36. Таблица Ведомость начислений
Рис. 37. Таблица Ведомость удержаний
7. Заполним таблицу Лицевой счет, используя данные справочников. Установите курсор в клетку B3 и введите формулу заполнения фамилии на основании данных Справочника работников (ссылки на ячейки и диапазоны ячеек вводите, выделяя ячейки мышью, для ввода знаков $ нажимайте F4 после ввода каждого диапазона или ссылки, по окончании
ввода формулы нажмите Enter):
=ВПР(А3;$L$3:$P$9;2;0).
Знак $ фиксирует координаты ячеек и диапазонов (при копировании формул они не изменяются). В случае возникновения ошибки определите источник возникновения ошибки. Для этого установите
75
указатель в ячейку с формулой и нажмите на вкладке Формулы в группе
Зависимости формул кнопку группы Проверка наличия ошибок 
и
выберите нужный пункт.
Скопируйте формулу определения фамилии в диапазон ячеек B4:B9. 8. Аналогично заполните диапазоны ячеек D3:D9 и E3:E9 (столбцы
Должность и Отдел) на основании данных Справочника работников.
9. Введите дополнительную информацию (рис. 38). Обратите внимание на адреса ячеек ввода данных.
Рис. 38. Таблица дополнительной информации
10. В ячейке B34 введите формулу расчета начислений по окладу (текст формулы вводите без переноса в одну строку):
=ВПР(ВПР(A34;$A$2:$J$9;3;0);$B$13:$C$30;2;0)*ВПР(A34;$A$3:$J$9;7;0)/$F$21
Скопируйте формулу начисления заработной платы по окладу в диапазон В35:В40.
11. В ячейку С34 введите формулу расчета премии. Размер премии зависит от выслуги лет, определяемой как разность между текущей датой и датой поступления на работу. Соответственно формула для расчета премии:
=ЕСЛИ((СЕГОДНЯ()-ВПР(A34;$L$3:$P$9;5;0))/365<5;B34*$F$23; ЕСЛИ((СЕГОДНЯ()-ВПР(A34;$L$3:$P$9;5;0))/365<10;B34*$G$23;B34*$H$23))
Скопируйте формулу в ячейки С35:С40.
12. В ячейку D34 введите формулу расчета общих начислений заработной платы:
=B34+C34
Скопируйте формулу в ячейки D35:D40.
13. В ячейку H3 введите формулу для начисленной оплаты из таблицы Ведомость начислений (=D34). Скопируйте формулу в ячейки
H4:H9.
14. В ячейку B44 введите формулу расчета подоходного налога в
таблице Ведомость удержаний:
=(ВПР(A44;$A$3:$J$9;8;0)-$F$21*ВПР(A44;$A$3:$J$9;6;0))*0,12
Скопируйте формулу в ячейки B45:B50.
15. В ячейку С44 введите формулу расчета пенсионного налога:
=ВПР(A44;$A$3:$J$9;8;0)*0,01
Скопируйте формулу в ячейки С45:С50.
76
16. В ячейку D44 введите формулу расчета удержания по исполнительным листам:
=ЕСЛИ(ЕНД(ВПР(A44;$E$13:$F$15;2;0));0;(ВПР(A44;$A$3:$J$9;8;0)-B44)* *ВПР(A44;$E$13:$F$15;2;0)/100)
Скопируйте формулу в ячейки D45:D50.
17. В ячейку Е44 введите формулу расчета общей суммы удержания:
=B44+C44+D44.
Скопируйте формулу в ячейки E45:E50.
18.В ячейку I3 введите формулу для нахождения общей суммы удержания (=E44) из таблицы Ведомость удержаний. Скопируйте формулу
вячейки I4:I9.
19.Вычислите сумму к выдаче с помощью формулы массива {=H3:H9–I3:I9}. Для этого выделите блок ячеек J3:J9, нажмите клавишу «=», выделите блок H3:H9, нажмите клавишу «-», выделите блок I3:I9,
нажмите клавиши Ctrl +Shift+ Enter.
20.Используя автосуммирование, рассчитайте итоги в таблице Лицевой счет. Для этого в ячейку А10 введите текст Итого, установите указатель в ячейку H10 и выполните команду
Формулы/Библиотека функций/Автосумма.
Если выбранный командой блок окажется верным – H3:H9, нажмите Enter. В противном случае выделите блок H3:H9 и нажмите Enter.
Аналогичные действия выполните для ячеек I10, J10.
Получение итоговых данных
21. Чтобы выполнить расчет суммы начисленной заработной платы по отделу 1, в ячейку I12 введите Итого по отделу 1. В ячейку J12 введите формулу:
=СУММЕСЛИ(E3:E9;1;J3:J9)
22.Самостоятельно введите в ячейку J13 формулу для расчета суммарной начисленной заработной платы по отделу 2.
23.Рассчитайте количество работников отдела 1. Для этого в ячейку I14 введите Кол-во сотрудников 1 отдела. В ячейку J14 введите формулу:
=СЧЕТЕСЛИ(Е3:E9;1)
24.Самостоятельно введите в ячейку J15 формулу для расчета количества работников отдела 2.
25.Аналогично можно выполнить расчет начисленной заработной платы и количество работников по каждой должности.
77
Задания
Вариант 1
Для планового отдела необходимо рассчитать плановый фонд заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 20-21 (пустые ячейки заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 16-17).
|
|
|
|
|
Справочник работников |
|
|
Таблица 20 |
|||||||||
|
|
|
|
|
|
|
|
|
|||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Таб. номер |
Фамилия |
|
Должность |
|
|
Отдел |
|
Дата поступления на работу |
||||||||
1001 |
|
Краснов А.А. |
|
Экономист |
|
|
3 |
15.10.2003 |
|
||||||||
1002 |
|
Самохов В.П. |
|
Инженер |
|
|
4 |
14.01.1994 |
|
||||||||
1003 |
|
Кривоносов А.Ф. |
|
Бухгалтер |
|
|
3 |
10.03.2010 |
|
||||||||
|
… |
|
… |
|
… |
|
|
|
|
|
… |
|
… |
|
|
||
1007 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Лицевой счет |
|
|
|
|
Таблица 21 |
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||
|
Таб. |
Фамилия |
Разряд |
|
Долж- |
Отдел |
|
Кол- |
|
Факт. |
|
Начис- |
Удер- |
З/П |
|||
|
номер |
|
ность |
|
во |
|
время |
|
лено |
жано |
к вы- |
||||||
|
|
|
|
|
|
|
льгот |
|
(дн.) |
|
з/п |
даче |
|||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||
|
1001 |
|
|
5 |
|
|
|
|
|
0 |
|
17 |
|
|
|
|
|
|
1002 |
|
|
11 |
|
|
|
|
|
1 |
|
19 |
|
|
|
|
|
|
1003 |
|
|
12 |
|
|
|
|
|
1 |
|
22 |
|
|
|
|
|
|
… |
|
|
… |
|
|
|
|
|
… |
|
… |
|
|
|
|
|
|
1007 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Оклад работника также зависит от его квалификации (разряда). Разрядная сетка является справочником и представлена в виде табл. 22 (разряды от 1 до 16).
Таблица 22
|
Разрядная сетка |
|
Разряд |
|
Оклад, руб. |
|
|
|
1 |
|
15000 |
2 |
|
15250 |
… |
|
… |
Размер удержания по исполнительным листам работника зависит от процента удержания. Сведения о работниках, с которых необходимо удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 23).
Таблица 23
Справочник по исполнительным листам
Табельный номер |
Процент удержаний |
1002 |
5 |
1005 |
15 |
1006 |
22 |
78
В процессе решения задачи задайте размер минимальной оплаты труда (5500 руб.) и количество рабочих дней в месяце (22 дня), процент премии в зависимости от выслуги лет (табл. 24).
|
Процент премии |
Таблица 24 |
|
|
|
||
Стаж |
меньше 10 лет |
от 10 до 15 |
больше 15 лет |
Процент премии |
5% |
25% |
50% |
Вариант 2
Для планового отдела необходимо рассчитать плановый фонд заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 25-26 (пустые ячейки заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 16-17).
|
|
Справочник работников |
Таблица 25 |
||
|
|
|
|||
|
|
|
|
|
|
Таб. номер |
Фамилия |
|
Должность |
Отдел |
Дата поступления на работу |
101 |
Соколов А.Р. |
|
Инженер |
3 |
11.06.2007 |
102 |
Романов А.А. |
|
Инженер |
2 |
13.12.2000 |
103 |
Иванов П.В. |
|
Программист |
2 |
10.08.1995 |
… |
… |
|
… |
… |
… |
107 |
|
|
|
|
|
Таблица 26
Лицевой счет
Таб. |
|
|
Долж- |
|
Кол- |
Факт. |
Начис- |
Удер- |
З/П |
номер |
Фамилия |
Разряд |
ность |
Отдел |
во |
время |
лено |
жано |
к вы- |
|
|
|
|
|
льгот |
(дн.) |
з/п |
|
даче |
101 |
|
6 |
|
|
0 |
22 |
|
|
|
102 |
|
11 |
|
|
1 |
23 |
|
|
|
103 |
|
10 |
|
|
2 |
21 |
|
|
|
… |
|
… |
|
|
… |
… |
|
|
|
107 |
|
|
|
|
|
|
|
|
|
Оклад работника также зависит от его квалификации (разряда). Разрядная сетка является справочником и представлена в виде табл. 27 (разряды от 1 до 14).
Таблица 27
|
Разрядная сетка |
|
Разряд |
|
Оклад, руб. |
|
|
|
1 |
|
16000 |
2 |
|
16500 |
… |
|
… |
Размер удержания по исполнительным листам работника зависит от процента удержания. Сведения о работниках, с которых необходимо
79
удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 28).
Таблица 28
Справочник по исполнительным листам
Табельный номер |
Процент удержаний |
102 |
25 |
103 |
15 |
107 |
10 |
В процессе решения задачи задайте размер минимальной оплаты труда (6500 руб.) и количество рабочих дней в месяце (24 дня), процент премии в зависимости от выслуги лет (табл. 29).
|
Процент премии |
Таблица 29 |
|
|
|
||
Стаж |
меньше 10 лет |
от 10 до 15 |
больше 15 лет |
Процент премии |
5% |
25% |
50% |
Вариант 3
Для планового отдела необходимо рассчитать плановый фонд заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 30-31 (пустые ячейки заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 16-17).
|
|
Справочник работников |
Таблица 30 |
||
|
|
|
|||
|
|
|
|
|
|
Таб. номер |
Фамилия |
|
Должность |
Отдел |
Дата поступления на работу |
11 |
Павлов П.А. |
|
Экономист |
1 |
15.02.1998 |
12 |
Иванов И.И. |
|
Бухгалтер |
3 |
22.03.2000 |
13 |
Краснов А.А. |
|
Программист |
1 |
26.01.2010 |
… |
… |
|
… |
… |
… |
17 |
|
|
|
|
|
Таблица 31
Лицевой счет
Таб. |
|
|
Долж- |
|
Кол- |
Факт. |
Начис- |
Удер- |
З/П |
номер |
Фамилия |
Разряд |
ность |
Отдел |
во |
время |
лено |
жано |
к вы- |
|
|
|
|
|
льгот |
(дн.) |
з/п |
|
даче |
11 |
|
11 |
|
|
0 |
22 |
|
|
|
12 |
|
11 |
|
|
1 |
23 |
|
|
|
13 |
|
10 |
|
|
2 |
21 |
|
|
|
… |
|
… |
|
|
… |
… |
|
|
|
17 |
|
|
|
|
|
|
|
|
|
Оклад работника также зависит от его квалификации (разряда). Разрядная сетка является справочником и представлена в виде табл. 32 (разряды от 1 до 14).
|
80 |
|
|
|
Таблица 32 |
|
Разрядная сетка |
|
Разряд |
|
Оклад, руб. |
1 |
|
17000 |
2 |
|
17250 |
… |
|
… |
Размер удержания по исполнительным листам работника зависит от процента удержания. Сведения о работниках, с которых необходимо удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 33).
Таблица 33
Справочник по исполнительным листам
Табельный номер |
Процент удержаний |
11 |
35 |
14 |
22 |
17 |
6 |
В процессе решения задачи задайте размер минимальной оплаты труда (5000 руб.) и количество рабочих дней в месяце (24 дня), процент премии в зависимости от выслуги лет (табл. 34).
|
Процент премии |
Таблица 34 |
|
|
|
||
Стаж |
меньше 6 лет |
от 6 до 16 |
больше 16 лет |
Процент премии |
5% |
25% |
35% |
Вариант 4
Для планового отдела необходимо рассчитать плановый фонд заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 35-36 (пустые ячейки заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 16-17).
|
|
Справочник работников |
Таблица 35 |
||
|
|
|
|||
|
|
|
|
|
|
Таб. номер |
Фамилия |
|
Должность |
Отдел |
Дата поступления на работу |
201 |
Комаров П.А. |
|
Нач. отдела |
3 |
12.03.2000 |
202 |
Романов П.Р. |
|
Экономист |
4 |
11.06.2008 |
203 |
Маслов П.В. |
|
Экономист |
3 |
10.03.1998 |
… |
… |
|
… |
… |
… |
207 |
|
|
|
|
|
Оклад работника также зависит от его квалификации (разряда). Разрядная сетка является справочником и представлена в виде табл. 37 (разряды от 1 до 14).
81
Таблица 36
Лицевой счет
Таб. |
|
|
Долж- |
|
Кол- |
Факт. |
Начис- |
Удер- |
З/П |
номер |
Фамилия |
Разряд |
ность |
Отдел |
во |
время |
лено |
жано |
к вы- |
|
|
|
|
|
льгот |
(дн.) |
з/п |
|
даче |
201 |
|
11 |
|
|
0 |
22 |
|
|
|
202 |
|
11 |
|
|
1 |
23 |
|
|
|
203 |
|
10 |
|
|
2 |
21 |
|
|
|
… |
|
… |
|
|
… |
… |
|
|
|
207 |
|
|
|
|
|
|
|
|
|
Таблица 37
|
Разрядная сетка |
|
Разряд |
|
Оклад, руб. |
1 |
|
12000 |
2 |
|
12850 |
… |
|
… |
Размер удержания по исполнительным листам работника зависит от процента удержания. Сведения о работниках, с которых необходимо удерживать по исполнительным листам, и размере процента удержания
представлены в Справочнике по исполнительным листам (табл. 38).
Таблица 38
Справочник по исполнительным листам
Табельный номер |
Процент удержаний |
203 |
35 |
205 |
12 |
206 |
18 |
В процессе решения задачи задайте размер минимальной оплаты труда (5500 руб.) и количество рабочих дней в месяце (23 дня), процент премии в зависимости от выслуги лет (табл. 39).
|
Процент премии |
Таблица 39 |
|
|
|
||
Стаж |
меньше 5 лет |
от 5 до 20 |
больше 20 лет |
Процент премии |
5% |
25% |
35% |
Вариант 5
Для планового отдела необходимо рассчитать плановый фонд заработной платы. Форма оплаты труда работников – повременная.
Входная информация представлена в табл. 40-41 (пустые ячейки заполните произвольными данными). Организовать заполнение данных
ведомости Лицевой счет, с использованием Справочника работников.
Результирующую информацию необходимо оформить в виде форм
Начисления и Удержания (табл. 16-17).
Оклад работника также зависит от его квалификации (разряда). Разрядная сетка является справочником и представлена в виде табл. 42 (разряды от 1 до 16).