Как сделать расчет зарплаты в excel?
Расчёт зарплаты в MS Excel — Представленная здесь электронная форма разработана для автоматизации процесса расчёта заработной платы по окладам. Теперь Вам не нужно выполнять рутинную монотонную работу, расчитывая заработную плату и налоги на неё, используя калькулятор.
Используя электронную форму «Расчёт зарплаты в MS Excel» Вам достаточно один раз внести список работников Вашей организации, оклады, должности и табельные номера. Расчёт зарплаты будет произведён автоматически.
Электронная форма «Расчёт зарплаты в MS Excel» разработана таким образом, что подоходный налог, единый социальный налог (пенсионное страхование, социальное страхование, медицинское страхование) расчитываются автоматически.
Автоматический расчёт налогов на зарплату (ПФ, соц. страх., мед. страх., п/н), автоматический расчёт накопительной и страховой частей трудовой пенсии индивидуально по каждому работнику и итогово (для отчётности) за любой отчётный период.
Абсолютно безопасно для вашего компьютера, вирусов и макросов нет.
Расчёт зарплаты в MS Excel для Windows
Как рассчитать зарплату онлайн и в экселе: формула и методы
Пусть требуется определить зависимость годового подоходного налога от величины заработной платы, если она одинакова в каждом месяце. В этой зависимости одна независимая («входная») переменная – заработная плата за месяц. Зависимая («выходная») переменная – величина подоходного налога. Эта зависимость была определена в предыдущей лабораторной работе.
Excel
Д i – доля от общей суммы заработной платы, выплачиваемая i -му работнику.
Таким образом, даже если у двух работников квалификация и затраченное время одинаковы, КТУ учитывает такие факторы, как важность вклада работника в общее дело, его ценные предложения, добросовестное отношение к труду.
Пусть согласно договору на всю бригаду начисляется сумма заработной платы 10 000 р. Заработная плата каждого работника зависит от его квалификации (разряда), фактически отработанного времени и КТУ.
Расчет заработной платы с использованием КТУ производится с помощью таблицы вида
Во-первых, из нее берутся исходные данные для расчетов налоговых отчислений и выдаваемых на руки сумм.
Во-вторых, она служит для того, чтобы показать каждому работнику весь ход расчетов и убедить его в правильности начисленной ему суммы. Каждая цифра должна быть проверяемой, то есть получаться при расчетах вручную или с помощью калькулятора. Каждый работник должен иметь возможность убедиться в том, что распределение произведено согласно договоренности, а расчеты сделаны правильно.
В третьих, общий размер начисленной суммы используется в бухгалтерском учете заработной платы.
1. Корректной. В частности, при одинаковых КТУ и других параметрах рассчитанные суммы должны быть одинаковыми или отличаться не более чем на 1 коп.
2. Проверяемой. Если взять данные из таблицы и произвести расчет вручную или на калькуляторе, то результат будет совпадать с имеющимся в таблице.
3. Автоматизируемой. Если на вход расчетной программы подать общую сумму заработной платы бригады, то всегда должен получаться правильный результат. В противном случае может возникнуть несоответствие между суммой, перечисленной на заработную плату (верхняя строка таблицы), и выданной в кассе (нижняя строка).
Совпадение должно быть полным, с точностью до 1 коп.
4. Правильно оформленной. Все шаги по начислению заработной платы (и любым бухгалтерским действиям) должны быть оформлены документально, согласно соответствующим формам, и иметь необходимые подписи.
Правила оформления документации Вы узнаете при изучении других дисциплин, поэтому пока внимание будет уделяться только первым трем требованиям.
Простая проверка расчетов таблицы с помощью калькулятора показывает, что эти требования не удовлетворяются. Значит, таблица требует доработок.
Создать таблицу расчета заработной платы с учетом КТУ.
С помощью созданной таблицы разделить 20000 рублей поровну между тремя работниками с выполнением первых трех из указанных выше требований.
Постройте таблицу, аналогичную приведенной в разделе Общие сведения. В ячейки D, α, КТУ введите числа, а в ячейки Доля и Заработная плата – формулы. Установите значения D, α, КТУ так, чтобы 20 000 рублей распределялись поровну между тремя работниками.
Измените точность представления чисел, увеличив разрядность до 20 знаков после десятичной запятой. Это можно сделать, выбрав ме-
Задайте Числовые форматы: Числовой; Число десятичных знаков: 20.
Если число не помещается в ячейку (вся ячейка заполнена символами ##### ), то увеличьте ее ширину.
Уменьшите число десятичных знаков до 0, затем оставьте 2 знака после запятой. Сделайте вывод о том, как хранятся и представляются числа в Excel.
Скопируйте полученную таблицу на другой лист или на свободное место на том же листе и в ячейки Доля занесите вручную числа, отображаемые в первой таблице.
Объясните причину несовпадения результатов в двух таблицах. Для любой свободной клетки выберите меню Формат Ячей-
ки… Число и задайте Числовые форматы: Дробный; Тип: Про-
стыми дробями. Введите в эту ячейку последовательно следующие данные: =2/3, затем 0,666666666666666, затем 0,6. Определите минимальное и максимальное десятичное число, которое представляется в данном формате как 2/3. Сделайте заключение о возможности применения данного формата в таблице.
Постройте таблицу, удовлетворяющую поставленным требованиям. Для этого существует несколько путей. Вам необходимо найти хотя бы один из них. Полученный результат следует обосновать.
При построении возможно добавление новых столбцов для расчетов. Возможно, потребуется использование функций округления. Для
их вызова нажмите кнопку, помеченную f x . Выберите среди математических функций функции округления. Для получения подробных сведений вызовите помощь (кнопка, помеченная знаком ? ).
Определите необходимую точность представления доли.
При решении поставленной задачи предлагается руководствоваться следующими принципами.
Отображаться и использоваться в формулах должно одно и то же значение. Это можно реализовать с помощью функций округления. Чтобы сумма точно равнялась заданному числу, в данном случае можно рассчитать все значения, кроме последнего, а последнее
значение определить как разницу нужной суммы и всех предыдущих значений.
Количество десятичных знаков в представлении доли должно соответствовать количеству цифр результата, рассчитываемого с помощью этого коэффициента.
Рассмотрите применимость полученной таблицы к следующим исходным данным:
Другой общей сумме, например, в 100 раз большей, в 101 раз большей.
Другому числу работников при равных КТУ. Другому числу работников при различных КТУ,
Укажите, какие изменения потребует таблица для каждого из приведенных случаев.
Таблица, удовлетворяющая всем сформулированным требованиям. Ответы на поставленные вопросы.
5. Разработка электронной таблицы расчета сумм налоговых отчислений
Цель работы – комплексная проверка умения работать с формулами и графиками
ются даже по убывающей шкале (чем больше фонд оплаты труда, тем меньше процент единого социального налога).
Данная работа посвящена изучению различных шкал исчисления налогов. В качестве примера будет рассмотрен подоходный налог с физических лиц. Главное в данной работе – понять, как может рассчитываться подоходный налог, какие трудности при этом возникают.
В алгоритмах расчета сделаны некоторые упрощения. Однако создаваемая программа расчета должна обладать достаточной гибкостью.
В 2000 году совокупный годовой облагаемый доход Д о определялся по формуле (приводится упрощенный вариант)
где З – заработная плата по основному месту работы; Д дог – доходы не по основному месту работы;
П – отчисления в пенсионный фонд ( 1% от оплаты труда) работника);
Отчисления в пенсионный фонд вычитаются из облагаемого дохода, чтобы избежать двойного налогообложения.
Размер облагаемого совокупного дохода, Ставка налога
Главный принцип расчета налога по прогрессивной шкале состоит в том, что он не увеличивается резко при росте дохода. При росте заработной платы сумма, остающаяся после уплаты налогов, должна монотонно возрастать.
Пусть налог взимался с суммы 49999 рублей. Налог с суммы 50000 рублей будет на 12 копеек больше. Налог с суммы 50001 рубль будет еще на 20 копеек больше. Иными словами, человек, заработавший 50001 рубль, должен получать на руки больше, чем человек, получивший 50000 рублей.
Предприятие, на котором работал работник, ежемесячно рассчитывало и удерживало с него подоходный налог.
Часть 1. Расчет в течение года в бухгалтерии по месту основной работы
Второй столбец – суммы заработной платы работника помесячно за год. Ее расчет не входит в данную работу, поэтому она вводится как число.
Третий столбец – отчисления в пенсионный фонд. Они составляют 1% от суммы, находящейся во втором столбце.
12 Подсказка. Формул должно бытьдве: для первогомесяца идля остальных.
Пятый столбец содержит величину льгот. Их размер зависит от нарастающего итога начисленной заработной платы. Здесь необходимо использовать функцию ЕСЛИ .
Шестой столбец содержит размер облагаемого дохода, полученного в данном месяце. Из полученного дохода за месяц вычитаются отчисления в пенсионный фонд и льгота. Здесь проблема состоит в том, что заработная плата может оказаться меньше льгот. В этом случае облагаемый доход должен быть равным нулю, а не отрицательному числу. Воспользуйтесь функцией ЕСЛИ.
Седьмой столбец – облагаемый доход нарастающим итогом. Восьмой столбец – подоходный налог нарастающим итогом. Он
рассчитывается согласно приведенной таблице, с использованием вложенной функции ЕСЛИ.
Девятый столбец – подоходный налог за месяц. Для его расчета применяется операция, обратная получению нарастающего итога.
Десятый столбец – выдаваемая на руки сумма. Это заработная плата за вычетом удержанных сумм.
Не все столбцы таблицы допускают суммирование. Некоторые суммы не имеют смысла. Не вводите суммирование для них.
Часть 2. Расчет в течение года в бухгалтерии по дополнительному месту работы
Физическое лицо может получать заработную плату и по другому месту работы. Однако там у него нет льгот по подоходному налогу. Налогооблагаемая сумма определяется как разница между начисленной и уплаченной в пенсионный фонд.
Для выполнения этих расчетов проще всего скопировать все содержимое таблицы на новый лист и установить величину льгот, равную нулю.
В конце года сведения со всех мест работы поступают в налоговую инспекцию. Там вычисляются суммы по всем местам работы: начис-
лений, уплаченного в пенсионный фонд, налогооблагаемой базы, уплаченного подоходного налога, оставшейся на руках сум-
мы. После этого производится расчет подоходного налога с общей
суммы налогооблагаемой базы. Эта сумма должна быть не меньше уплаченного подоходного налога. Разницу физическое лицо должно доплатить. В конце рассчитывается сумма, оставшаяся на руках после доплаты.
Для выполнения этой части работы следует использовать третий лист электронной таблицы. В него копируется таблица расчетов по основному месту работы. Во второй столбец этой таблицы заносится формула суммы начисленной зарплаты по всем местам работы.
Далее создается таблица, которая показывает итоги года: общий доход и его распределение: платежи в пенсионный фонд, уплата подоходного налога в течение года, доплата подоходного налога в конце года, оставшаяся на руках после окончательного расчета сумма.
Часть 4. Графическое представление структуры доходов и расходов
Для наглядного представления распределения начисленной суммы удобно построить круговую диаграмму. Размер ее сегментов («долек») показывает относительный размер составляющих.
Эту часть работы можно выполнить на третьем листе или использовать четвертый.
1. Готовится таблица для диаграммы. Она должна включать только данные, используемые для построения диаграммы. Это составляющие дохода, использованные по различному назначению: в пенсионный фонд, подоходный налог в течение года, доплата, остаток на руках.
Таблица должна содержать названия строк и столбцов.
2. Созданная таблица, включая названия строк и столбцов, выделяется. С помощью Мастера диаграмм строится круговая диаграмма. Лучше создать диаграмму на отдельном листе.
Часть 5. Расчет подоходного налога по равномерной шкале 13
С 2001 года расчет производится по-другому. Ниже приводятся краткие выдержки из Налогового кодекса РФ.
13 Эта часть может служить в качестве контрольного задания по разделу «Таблицы и формулы».
При определении налоговой базы учитываются все доходы. Налоговым периодом является календарный год.
Налоговая база определяется как денежное выражение этих доходов, уменьшенное на величину вычетов.
Если величина вычетов превышает величину доходов, то налоговая база принимается равной нулю.
Стандартные налоговые вычеты в размере 400 рублей за каждый месяц налогового периода для налогоплательщика и 300 рублей на каждого ребенка действуют до месяца, в котором доход налогоплательщика, исчисленный нарастающим итогом с начала налогового периода работодателем, превысит 20000 рублей.
Вычеты предоставляются одним из работотдателей по выбору налогоплательщика 14 .
Сумма налога исчисляется как соответствующая налоговой ставке процентная доля налоговой базы.
Исчисление сумм налога производится нарастающим итогом с начала налогового периода по итогам каждого месяца с зачетом удержанной в предыдущие месяцы текущего налогового периода суммы налога без учета доходов, полученных в других местах работы, и удержанных там налогов.
В налоговой декларации физические лица указывают все полученные ими в налоговом периоде доходы, удержанные суммы налога, суммы налога, подлежащие уплате иливозврату по итогам налогового периода.
Задание состоит в том, чтобы создать таблицы расчета налога и выплат по основному и дополнительному месту работы, аналогичные рассмотренным в частях 1 и 2 данной работы, а также таблицу расчета налоговой декларации. Завершить работу должна круговая диаграмма распределения доходов физического лица.
Готовая к сдаче работа должна удовлетворять следующим требованиям.
14 По письменному заявлению и соответствующим документам.
15 В данной работе рассматривается только заработная плата.
Файл с результатами должен находиться в личной папке. Формулы в таблице должны быть корректными и работоспособными при любых положительных значениях начисленных сумм. Формы таблиц должны обеспечивать ясность и удобство их восприятия. В основном это касается точности заголовков.
В графе Количество детей могут содержаться числа 0, 1 или 2. Диаграмма должна быть снабжена поясняющими надписями. При защите требуется знать:
Смысл каждого выполненного действия. Способы построения ссылок и формул. Сущность и порядок форматирования ячеек.
Процесс построения диаграмм; информацию, которая отображается в круговой диаграмме.
Цель работы – изучение средств построения зависимостей
Для того, чтобы исследовать зависимость между переменными, удобно построить ее график по точкам. Однако обычные таблицы не всегда достаточны для этого.
Пусть требуется определить зависимость годового подоходного налога от величины заработной платы, если она одинакова в каждом месяце. В этой зависимости одна независимая («входная») переменная – заработная плата за месяц. Зависимая («выходная») переменная – величина подоходного налога. Эта зависимость была определена в предыдущей лабораторной работе.
Для построения графика этой зависимости требуется подставлять значения заработной платы, считывать значение величины подоходного налога и записывать данные в новую таблицу. Конечно, можно построить общую формулу для расчета данной зависимости, но эта формула окажется слишком сложной 16 .
16 Excel не очень хорошо приспособлен для создания и, главное, проверки сложных формул. В этом Вы, наверное, убедились, выполняя предыдущие работы. Поэтому рекомендуется создавать дополнительные столбцы или строки, производить вычисления по шагам. Тогда их будет несложно проверить.
Как сделать расчет зарплаты в excel?
Некоторым работникам полагаются налоговые вычеты. Они представляют собой определенную льготу, которую правительство предоставляет своим гражданам. По сути, это конкретная сумма оклада, которая может не облагаться подоходным налогом.
MS EXCEL в расчетных задачах
• выполнить учебные примеры по вводу чисел, текста, формул;
• выполнить учебный пример по созданию и форматированию таблицы;
Пример 1. Набор текста и простейшие расчеты в MS Excel
1. Ввод текста . Введите в ячейку A1 текст: Выполнение простейших расчетов в Excel. После этого просмотрите содержимое ячеек A1, B1,C1 в строке формул. Весь текст относится к ячейке A1. В дальнейшем при вводе текста следует применять принцип помещения связанного текста в одну ячейку. Это упростит форматирование таблицы.
2. Ввод чисел. Как правило, в расчетах используются целые и десятичные числа. Для разделения целой и дробной части используется запятая. В ячейки A5 и B5 введите числа 2,5 и 0,6 .
Проанализируйте полученные результаты. Для исходных величин используйте обозначения x, y , которые введите в вышележащую строку. В соответствии с расчетами введите обозначения и для результатов. Примерный вид таблицы показан на рис. 1.5.
Рис.1.5. Фрагмент рабочего листа при выполнении примера 1
Сохраните таблицу в личной папке в файле с именем Tаble1.xls. Для этого выполните команду Сохранить (кнопка Office) . При выполнении команды появится диалоговое окно команды Сохранение документа (рис. 1.6).
Рис.1.6. Диалоговое окно при первом сохранении документа
В поле Папка следует перейти к личной папке, в поле Имя файла набрать имя: Tаble1.xls. В поле Тип файла следует выбрать: Книга Excel . При внесении в файл изменений и повторном сохранении файла по команде Сохранить , диалоговое окно не появляется.
Закройте приложение по команде Выход из Excel (кнопка Office) . . Откройте папку, в которой должен быть сохранен файл, и снова
откройте его из окна папки, используя двойной щелчок. Убедитесь, что рабочая книга сохраняется со всеми рабочими листами.
Замечание. Рабочую книгу можно сохранить под новым именем или в другом формате по команде Сохранить как… (кнопка Office) . .
Пример 2. Создание и форматирование таблицы Переименуйте листы 1, 2 и 3 рабочей книги в следующие:
Перейдите на лист Расходы . Следуя указаниям, создайте и отформатируйте таблицу, вид которой показан на рис. 1.7.
1. Введите в ячейку A1 заголовок таблицы, в ячейки A2:D2 заголовки столбцов, в ячейки A3:A7 заголовки строк, в ячейки B3:C7 цену (только числа) и количество товара.
2. В ячейках D3:D7 вычислите стоимость товара. Для этого в ячейку D3 введите формулу: =B3*C3, а затем скопируйте ее в ячейки D4:D7 с помощью Маркера заполнения. Для этого наведите указатель мыши на маркер (в
нижний правый угол ячейки с формулой) и протащите его вдоль ячеек
3. В ячейке D8 вычислите сумму столбца D с помощью кнопки
Автосуммирование (группа Редактирование , вкладка Главная ).
Форматирование заголовка. Выделите ячейки A1:D1 и назначьте шрифт Times New Roman ,14 пунктов, полужирный с помощью кнопок на панели инструментов, вкладка Главная . В окне команды Выравнивание
установите параметр Выравнивания по горизонтали — По центру
Форматирование остальной части таблицы. Выделите другую часть таблицы, в ней используйте шрифт Times New Roman, размер 12 пт., в ячейках с ценами и стоимостью назначьте денежный формат (группа Число , вкладка Главная ) , формат Денежный . Увеличьте ширину столбцов, чтобы заголовки помещались в ячейках.
5. Сохраните таблицу в личной папке в том же самом файле. Убедитесь
7. Изучите все элементы форматирования, в частности, форматы данных , перенос по словам, выравнивание по горизонтали и по вертикали
Замечание. Перенос внутри слов в MS Excel можно выполнить только вручную.
8. Откройте новую рабочую книгу, выполните задание по варианту, сохраните его и завершите работу приложения MS Excel.
2. Создайте таблицу заданного варианта, выполните необходимые расчеты и оформление таблицы по образцу.
3. Сохраните таблицу в личном каталоге в файле с расширением .xls.
Вычислить средний размер подоходного налога (в тысячах рублей) по одной декларации за каждый год.
Вычислить четвертый столбец как отношение элементов второго
Добавить четвертый столбец, в котором вычислить цены предыдущей недели по формуле C old = C new (1 + /100) , где C old — старая цена, − изменение цены по отношению к прошлой неделе.
Добавить четвертый столбец, в котором вычислить стоимость хранения 1 Гигабайта данных для разных типов дисков.
Добавить четвертый столбец, в котором вычислить цены предыдущей недели (см. указания вар.3).
Добавить четвертый столбец, в котором вычислить цены предыдущей недели (указания вар. 3).
Добавить столбец, в котором вычислить ожидаемый прирост населения в процентах.
Добавить пятый столбец, в котором вычислить продолжительность разводки моста в часах и минутах.
Величина прожиточного минимума в РФ (в среднем на душу населения; рублей в месяц)
Вычислить относительный прирост прожиточного минимума по всему населению и по социальным группам.
Учитывая средние оптовые цены, определить суммарную стоимость товара.
2. ВЫЧИСЛЕНИЯ ПО ФОРМУЛАМ С ИСПОЛЬЗОВАНИЕМ ВСТРОЕННЫХ МАТЕМАТИЧЕСКИХ ФУНКЦИЙ MS EXCEL
Формула задает правило для вычисления нового значения через исходные значения. Формула должна подчиняться определенным правилам записи, т.е. синтаксису . В MS Excel запись формулы всегда начинают со знака равенства. Часть формулы, следующая за знаком равенства, называется
Формулой в MS Excel называется последовательность, содержащая следующие элементы:
операнды (числа, текст, ссылки на ячейки, ссылки на массивы ячеек, встроенные функции);
круглые скобки, причем число открывающих скобок должно быть равно числу закрывающих.
Встроенные функции MS Excel – это функции, вычисление которых выполняется по определенным алгоритмам, содержащимся в приложении MS Excel. Вызов встроенной функции происходит при вычислении по формуле, содержащей эту функцию.
Запись функции в формуле MS Excel аналогична записи функций в математике. Она имеет вид f ( x 1 ; x 2 ; K ; x n ) ,
В общем случае аргументами функций могут быть данные любого вида, но для конкретной функции возможные аргументы определяются ее синтаксисом. Аргументы отделяются друг от друга точкой с запятой. Существуют встроенные функции, не содержащие аргументов, например, число π вычисляется с помощью функции ПИ().
Встроенные функции MS Excel разбиты на категории. Каждая категория функций предназначена для определенных целей, например, имеются математические, логические, статистические функции и т.д. В таблице 2.1 перечислены математические функции, причем только те, которые соответствуют элементарным функциям в математике.
При наборе формул с клавиатуры безразлично, набираются строчные или прописные буквы, но нужно соблюдать соответствие языка имени функции. Ссылки на ячейки записываются только латинскими буквами. При указании типа аргумента не рассматриваются ограничения на область определения функций, но, разумеется, их нужно соблюдать.
Как вычислить налог в excel
Как мы уже выяснили, расчет зависит от 3 параметров: исходной суммы (для которой будем производить расчет), размера ставки (18%, 10% или 0%) и типа расчета (выделение, вычитание или прибавление).
Поэтому в качестве аргументов функции и зададим указанные параметры.
