ПРАКТИЧЕСКАЯ РАБОТА
АВТОМАТИЗАЦИЯ РАСЧЕТА ЗАРАБОТНОЙ ПЛАТЫ СРЕДСТВАМИ EXCEL
Цель работы : Автоматизировать рабочее место по созданию формы «Расчет заработной платы», на базе табличного редактора MS Excel .
Обеспечить старт приложения с главной странички «Диалог». На ней обеспечить доступ к основным таблицам, которые участвуют в расчете заработной платы, и предусмотреть возврат на главную страницу. Создать на первой странице диалогменю, с кнопками для управления доступом ко всем листам документа. Применить макросы для автоматизации работы приложения.
Создать приложение из 20 таблиц. В главной таблице «Сотрудники» произвольно ввести данные для пяти человек. Таблицы связать между собой ссылками по отношению к первой таблице «Сотрудники» для возможности ввода и изменений данных о сотрудниках, тарифной ставке, больничных листах, отпусках и других данных в расчетных таблицах. Автоматизировать расчет заработной платы, организовав связи с применением формул, для корректировки сумм, участвующих в расчете.
На отдельной странице составить структуру схемы «Выполнение расчета ЗП». На отдельной странице составить диаграмму «Изменение фонда заработной платы».
Ознакомиться с кодом программы.
Ход работы.
1. Загрузите среду MS Excel и создайте файл с названием «Зарплата».
2. Создайте в документе 20 листов, один из них назовете «Диалог», а остальные в соответствии с надписями расположенных на рис.1. Для этого станьте на Лист 1 и правой кнопкой мышки в диалоговом окне выберите - «Добавить лист», затем переименуйте его.
Предусмотрите кнопку для возврата с каждого листа на главную страницу (рис.1).
Рис. 1. Главный интерфейс страница «Диалог»
3. Для быстрого просмотра приложения по каждому из перечисленных пунктов установите кнопку управления, при нажатии на которую будет открыт лист, согласно его описанию или по нажатию запрограммированных клавиш, см. рис.2.
Рис. 2. Интерфейс главной таблицы
4. Организовать автоматический переход между главной страницей и другими листами возможно с помощью макросов, см. рис.3.
Сервис - макрос начать запись.
Рис. 3. Настройка макроса
После выбора «Начать запись» будет предложено окно, в котором следует назвать макрос и присвоить ему для дальнейшего запуска горячие клавиши (рис.4).
Рис. 4. Запись макроса
Затем следует с документом выполнить те действия, которые за вас в дальнейшем выполнит макрос (в нашем случае открыть нужный лист). По завершению работ с документом следует остановить макрос (рис.5).
Рис. 5. Остановка макроса
Кнопки управления на главной странице следует разместить, вызвав панель элементов управления (рис.6).
Рис. 6. Панель инструментов
Затем каждой кнопке, присвойте ранее созданные макросы (рис.7,8.). Для этого следует нажать правой кнопкой мыши по элементу управления и в появившемся меню выбрать Назначить макрос. В появившемся окне следует выбрать макрос, который вы хотите назначить этому объекту.
5. На отдельном листе нарисуйте блок - схему расчета заработной платы (рис.9).
Рис. 9. Блок-схема расчета ЗП
6. Создайте таблицы «Список сотрудников» (рис.10), «Календарные и рабочие» (рис.11) и «Таблица архива» (рис.12).
Рис. 10. Интерфейс «Список сотрудников»
Рис. 11 Интерфейс «Календарные и рабочие дни»
Рис. 12 Интерфейс «Таблица архива»
7. Таблицу «Список сотрудников» сделайте основной для ввода и корректировки данных, поэтому во всех таблицах, где применяются столбцы «Табельный номер», «ФИО» организовать ссылки, чтобы данные брались из таблицы «Сотрудники», применив формулы:
Согрудники!В4 (для столбца «Табельный номер»);
Сотрудники!С4 (для столбца «Сотрудники»).
В «Таблице архива» дайте полную справку о размере заработной платы любого из сотрудников. Представьте информацию, как по месяцам, так и в столбце «Среднемесячная», рассчитайте среднюю зарплату за последние 6 месяцев, применив формулу:
=(H 5+ I 5+ J 5+ K 5+ L 5+ M 5)/6.
В столбце с именем «Итого» произвести суммирование столбца пользуясь автосуммой:
СУММ(В5:М5).
В таблице «Рабочие и календарные дни» укажите, сколько рабочих, и сколько календарных дней содержится в каждом месяце.
8. Создайте таблицы «Данные о больничных листах» (рис.13) и «Данные об отпусках» (рис.14).
Рис. 13. Интерфейс «Данные о больничных листах»
Рис. 14. Интерфейс «Данные об отпусках»
Поставьте выборочно некоторым сотрудникам количество дней по болезни и процент оплаты по больничному листу, а 2-3 человека отправьте в отпуск.
9. Создайте таблицы «Ведомость начисления аванса» (рис.15) и «Ведомость начисления премии» (рис.16).
Произведите расчет аванса в размере 40% от оклада, применив формулу:
С4*0,4.
Начисление премии производится в процентном соотношении с окладом:
C 4* D 4/100.
Рис. 15. Интерфейс «Ведомость начисление аванса»
10. Создайте «Ведомость начисления больничных листов» (рис.17). Суммарная оплата по больничному листу составляет:
Среднемесячную зарплату следует разделить на количество календарных дней, умножить на количество дней по болезни и умножить на процент оплаты по больничному листу:
=(С7/"Календар_рабочие дни"!С10)* D 7*Е7/100.
Рис. 16. Интерфейс «Ведомость начисления премии»
Рис. 17. Интерфейс «Ведомость начисления больничных»
Создайте «Ведомость начисления сумм по отпускным» (рис.18), применив формулу:
C 6/ D 6* E 6,
где С6- ="Таблица архива"!"N7/12;
D 6- ="Данные об отпусках"!С6;
Е6- КРД по отпуску.
Рис. 18. Интерфейс «Начисление сумм по отпускным»
11. Создайте «Ведомость начисленных сумм» (рис.19).
12. Создайте «Ведомость расчета отчислений в пенсионный фонд» (рис.20).
Рис. 19. Интерфейс «Ведомость начисленных сумм»
Рис. 20. Интерфейс «Ведомость отчислений в пенсионный фонд»
13. Создайте «Ведомость расчета подоходного налога» (рис.21).
Рис. 21. Интерфейс «Ведомость расчета подоходного налога»
и «Ведомость сумм отчислений в фонд занятости» (рис.22).
Рис. 22. Интерфейс «Ведомость сумм отчислений в фонд занятости»
14. Создайте «Ведомость расчета сумм отчислений по алиментам» (рис. 23).
Рис. 23. Интерфейс «Ведомость расчета сумм отчислений по алиментам»
15. Создайте «Выборку сумм по больничным листам» (рис.24), используйте «Автофильтр» (рис.25).
Рис. 24. «Выборка сумм по больничным листам»
Рис. 25. Пользовательский автофильтр
16. Создайте «Расчетную ведомость заработной платы» (рис.26).
Рис. 26. Интерфейс «Расчетная ведомость заработной платы»
17. Создайте «Платежную ведомость» (рис.27).
Рис. 27. Интерфейс «Итоговая платежная ведомость».
20. С помощью «Мастера диаграмм» составьте диаграмму «Изменение фонда заработной платы», см. рис.31.
Рис. 31. Изменение фонда заработной платы
21. Запустите созданную программу и исследуйте ее работу. По фамилии отыщите сотрудника. Убедитесь, что его данные появились в текущих полях. Проверьте работоспособность кнопок. Выйдите из программы.
Программа Зарплата от Правконс является составной частью комплексной условно бесплатной программы Зарплата, Табель, Кадры. Простая, интуитивно понятная программа для расчета зарплаты со всеми необходимыми возможностями и с автоматическим формированием первичных и отчетных документов по зарплате. При необходимости программу Зарплата можно приобрести отдельно от иных функций комплексных программ. В программе Зарплата все первичные и отчетные документы выгружаются в Excel БЕСПЛАТНО.
ПРОГРАММА ЗАРПЛАТА РАСЧЕТ ЗАРПЛАТЫ И НАЛОГОВ, ДОКУМЕНТЫ И ОТЧЕТНОСТЬ ПРОГРАММА СДЕЛАЕТ ЗА ВАС!
Начать работать в программе проще, чем в Excel !Вот действия, которые позволяют в программе произвести расчет зарплаты, расчет всех налогов (НДФЛ и ЕСН) и получить все нужные документы:
1. Скачать и установить программу
2. В Сведениях об организации ввести название и ставку по страховым взносам по травматизму (все остальные данные можно завести потом)
3. В кадровых данных ввести Ф.И.О, дату рождения и стандартные вычеты, если они есть (остальные кадровые данные можно ввести позже)
4. В расчете зарплаты нажать на кнопку Начислить (по всем сотрудникам или по каждому).
В результате у Вас будут автоматически правильно рассчитаны все налоги с фонда оплаты труда и заполнены все необходимые документы по расчету зарплаты.
Вот некоторые документы
(со скриншотами)
, которые автоматически
делает расчет программа зарплата:
Приказ о поощрении работников
Приказ об изменении оклада
Приказ о командировке
Расчетная ведомость. Ведомость платежная
Служебное задание
Автоматический расчет зарплаты по кадровым данным и табелю
Конечно же, со временем приходит необходимость требовать от программы еще большей автоматизации. Удобство программы заключается в том, что при заведении в кадровых данных постоянных начислений, расчет зарплаты, удержаний, налогов с зарплаты производится в одно действие всему списку сотрудников (кнопка «Списком»). При этом, если начисление относится к оплате по окладу и пользователем ведется табель учета рабочего времени, то программа автоматически учитывает только отработанные дни. Если табель пользователь не ведется, то оклад начисляется в полном размере. Также по кадровым данным автоматически отслеживается начисление больничных и отпускных. Таким образом, при наличии исходной информации по расчету зарплаты вся процедура начисления оплаты труда и формирование необходимых документов производится в автоматическом режиме за несколько минут даже на крупных предприятиях.
При необходимости в программе имеется возможность производить индивидуальные начисления через лицевой счет работника.
Широкий выбор настроенных начислений для расчета зарплаты
Программа автоматически производит расчет практически всех известных начислений. По каждому начислению в программу уже произведены настройки, как именно с него должны считаться налоги. Вот некоторые виды начислений, предусмотренные в программе.
Работа с табелем рабочего времени
Порядок выплаты заработной платы
Перечисление на пластиковые карты
Оплата по окладу
Аванс
Оплата сдельная
Авторские вознаграждения
Вознаграждение по договору
Пособие по временной нетрудоспособности
Единовременное пособие при рождении ребенка
Пособие по уходу за ребенком до 1,5 лет
Дивиденды
Оплаты ежегодных (дополнительных) отпусков
Перерасчет отпускных и больничных листов
Доходы нерезидента
По всем начислениям произведены настройки по расчету соответствующих налогов и взносов.
При необходимости пользователь может добавить любое собственное начисление и произвести настройку расчета налогов.
Файл справок 2-НДФЛ
Одним из преимуществ программы «Бухсофт: Зарплата» является то, что в ней предусмотрено автоматическое формирования справок о доходов физических лиц (сотрудников) по форме 2-НДФЛ. В конце года, когда зарплата за все месяцы рассчитана, программа сформирует файл и скопирует его на дискету в полном соответствии с теми требованиями, которые предъявляют налоговые органы.
1. Создайте таблицу расчета заработной платы по образцу Введите исходные данные - Табельный номер, ФИО и Оклад, % Премии = 27 %, % Удержания = 13 %.
Примечание. Выделите отдельные ячейки для значений % Премии (D4) и % Удержания (F4).
2. Произведите расчеты во всех столбцах таблицы.
При расчете Премии используется формула Премия = Оклад х % Премии , в ячейке D5 наберите формулу = $D$4 * С5 (ячейка D4 используется в виде абсолютной адресации – для применения параметров адресации нажмите клавишу ) и скопируйте автозаполнением.
Формула для расчета «Всего начислено» = Оклад + Премия.
При расчете Удержания используется формула = Всего начислено * % Удержания,
для этого в ячейке F5 наберите формулу = $F$4 * Е5 .
Формула для расчета столбца «К выдаче» = Всего начислено – Удержания.
3. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Формулы/Вставить функцию/категория - Статистические функции ).
4. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши.
5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист (пр.клавиша мыши по листу/Переместить/Скопировать…или зажмите клавишу CTRL и перетащите лист правее). Не забудьте для копирования поставить галочку в окошке Создавать копию .
6. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение Премии на 32 %.
Убедитесь, что программа произвела пересчет формул.
7. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» и рассчитайте значение доплаты по формуле = Оклад х % Доплаты . Значение доплаты примите равным 5 %.
8. Измените формулу для расчета значений колонки «Всего начислено» = Оклад + Премия + Доплата.
9. Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Рецензирование/Создать примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид расчета заработной платы за ноябрь приведен на рисунке
10. Сохраните созданную электронную книгу под именем «Зарплата» в своей папке.
11. Построить круговую диаграмму начисленной суммы к выдаче всех сотрудников зa ноябрь месяц.
1. Откройте созданный в Занятии 1 файл «Зарплата».
2. Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книга. Не забудьте для копирования поставить галочку в окошке Создавать копию .
3. Присвойте скопированному листу название «Зарплата декабрь». Исправьте название месяца в ведомости на декабрь.
4. Измените значение Премии на 46%, Доплаты - на 8 %. Убедитесь, что программа произвела пересчет формул.
5. По данным таблицы «Зарплата декабрь» постройте гистограмму доходов сотрудников. В качестве подписей оси X выберите фамилии сотрудников. Проведите, форматирование диаграммы. Конечный вид гистограммы приведен на рисунке.
6. Перед расчетом итоговых данных за квартал проведите сортировку по фамилиям в алфавитном порядке (по возрастанию) в ведомостях начисления зарплаты за октябрь-декабрь.
7. Скопируйте содержимое листа «Зарплата октябрь» на новый лист. Не забудьте для копирования поставить галочку в окошке Создавать копию .
8. Присвойте скопированному листу название «Итоги за квартал». Измените название таблицы на «Ведомость начисления заработной платы за 4 квартал».
9. Отредактируйте лист «Итоги за квартал». Для этого удалите в основной таблицы колонки Оклада и Премии, а также строку 4 с численными значениями % Премии и % Удержания и строку 19 «Всего». Удалите также строки с расчетом максимального, минимального и среднего доходов под основной таблицей. Вставьте пустую третью строку.
10. Вставьте новый столбец «Подразделение» (Главная/Ячейки/Вставить столбец на лист) между столбцами «Фамилия» и «Всего начислено». Заполните столбец «Подразделение» данными по образцу
11. Произведите расчет квартальных начислений, удержаний и суммы к выдаче как сумму начислений за каждый месяц (данные по месяцам располагаются на разных листах электронной книги, поэтому к адресу ячейки добавится адрес листа).
В ячейке D5 для расчета квартальных начислений «Всего начислено» формула имеет вид:
= "Зарплата декабрь"!Р5 + "Зарплата ноябрь"!Р5 +
+ "Зарплата октябрь"!Е5.
Аналогично произведите квартальный расчет «Удержания» и «К выдаче».
Для расчета квартального начисления заработной платы для всех сотрудников скопируйте формулы в столбцах D , Е и F . Ваша электронная таблица примет вид, как на рисунке.
12. Для расчета промежуточных итогов проведите сортировку по подразделениям, а внутри подразделений - по фамилиям.
13. Подведите промежуточные итоги по подразделениям, используя формулу суммирования. Для этого выделите всю таблицу и выполните команду Данные/Промежуточные итоги. Задайте параметры подсчета промежуточных итогов:
при каждом изменении в - Подразделение ;
операция - Сумма ;
добавить итоги по : Всего начислено , Удержания , К выдаче .
Отметьте галочкой операции «Заменить текущие итоги» и «Итоги под данными».
Примерный вид итоговой таблицы представлен на рисунке.
14. Изучите полученную структуру и формулы подведения промежуточных итогов, устанавливая курсор на разные ячейки таблицы. Научитесь сворачивать и разворачивать структуру до разных уровней (кнопками «+» и «-»).
16. Сохраните файл «Зарплата» с произведенными изменениями.
Расчет сумм НДФЛ и взносов в социальные фонды с помощью программы Excel для ежемесячной уплаты налогов с зарплаты работников и использования для подготовки и сдачи отчетности. Скачать файл с примером.
Этим способом учета заработной платы и расчета сумм НДФЛ и взносов в Excel я пользовался с начала 2005 года по 1 квартал 2016 года вплоть до закрытия нашего предприятия. Он позволяет самостоятельно, без знания основ программирования, справиться с решением задач по учету заработной платы работников и уплатой НДФЛ и взносов, рассчитанных из нее.
Использовавшаяся на практике таблица для расчета НДФЛ и взносов в социальные фонды с измененными Ф.И.О. работников
Для такого учета на листе Excel создается таблица, в первой строке которой записываются названия колонок (граф). Строка заголовков закрепляется, чтобы всегда оставалась в поле зрения.
Каждый месяц на сотрудника заполняется одна строка с его начислениями и расчетом, которую условно можно разделить на четыре части. Я их вынес в названия первых четырех параграфов.
Период
Первую колонку называем «Год», а вторую «Месяц». Такое деление периода на две колонки необходимо для более удобного применения автофильтра. Формат ячеек во всех неденежных столбцах оставляем «Общий», в ячейках с денежными суммами устанавливаем формат «Числовой» с двумя знаками после запятой.
Учет заработной платы
- «За отр. дни» - начислено за отработанные дни или оклад;
- «Отпускные»;
- «3 дня за сч. пред.» - пособие по временной нетрудоспособности, начисленное за счет работодателя;
- «Пособ. по вр. нетр.» - пособие по временной нетрудоспособности, начисленное за счет ФСС;
- «Пособ. не обл. нал.» - государственные пособия, не облагаемые НДФЛ.
В названиях колонок можно указать коды доходов, которые будут служить подсказкой при подготовке отчетов по форме 2-НДФЛ.
Расчет НДФЛ
Для расчета НДФЛ нам необходимо определить базу налогообложения, для этого складываем все налогооблагаемые доходы (в нашем примере - это колонки 4, 5, 6 и 7) и вычитаем из них сумму стандартных налоговых вычетов. Чтобы рассчитать НДФЛ, добавляем еще три столбца:
- «Вычет детск.» - сумма стандартных налоговых вычетов на детей;
- «База» - налогооблагаемая база;
- «НДФЛ» - сумма исчисленного НДФЛ.
Раньше у меня была в таблице Excel еще одна колонка с вычетом общим (на скриншоте она под номером 9, в файле для скачивания ее нет), который по 2011 год предоставлялся всем работникам в размере 400 рублей. Вы можете добавить еще одну колонку с вычетами, если кому-то из ваших сотрудников предоставлены другие налоговые вычеты, или приплюсовать их к детским.
Сумму НДФЛ в размере 13% рассчитываем, умножив базу на 0,13. Округлять полученное значение в ячейке не нужно, так как начисленный НДФЛ округляется по каждому работнику за год. За каждый месяц, кроме декабря, общую сумму исчисленного НДФЛ при заполнении платежного поручения округляем до рублей, а при уплате за декабрь, сравниваем сумму уплаченного налога за 11 месяцев с суммой налога по всем отчетам 2-НДФЛ, и разницу между ними следует оплатить за декабрь. Обязательно сравните эту сумму с суммой налога, полученной за декабрь из таблицы Excel - разницы между ними или не будет, или будет очень небольшая.
Расчет взносов
Для расчета взносов в нашей таблице Excel используются следующие колонки:
- «Страх. ПФ» - взносы в ПФР на страховую часть пенсии;
- «ФФОМС» - взносы в Федеральный фонд обязательного медицинского страхования;
- «ФСС» - взносы в ФСС на страхование случаев временной нетрудоспособности и материнства;
- «НС и ПЗ» - взносы в ФСС на страхование несчастных случаев и профессиональных заболеваний.
Для расчета взносов в социальные фонды используется сумма начислений из колонок 4, 5 и 6, умноженная на соответствующий коэффициент.
Скачать пример
В примере для скачивания применены для расчета взносов в ПФР и ФОМС процентные ставки 2017 года (22% и 5,1% соответственно), НДФЛ в размере 13%, НС и ПЗ в размере 0,2%.
Для выборки данных за определенный период по конкретному сотруднику используйте автофильтр. Если у вас, как у меня на скриншоте, вдруг начисление окажется меньше предоставленного вычета, учтите его в следующем периоде, когда доход превысит вычет. В течение года неиспользованные вычеты накапливаются, а 31 декабря сгорают.
Похожие статьи
