Вариант 13
В организации ведется журнал расчета подоходного налога с зарплат сотрудников в разрезе подразделений. Виды подразделений представлены на рис. 13.1. При этом работает следующее правило.
Все вычеты предоставляются согласно таблице рис. 13.2 только работникам «основного» места работы, остальные работники платят налог с общей суммы.
Построить таблицы по приведенным ниже данным (рис. 13.1-13.3).
Организовать межтабличные связи для автоматического заполнения графы журнала расчета налога на доходы с физических лиц (НДФЛ) (рис. 13.3): «Наименование подразделения», «НДФЛ».
Настроить проверку в поле «Вид места работы» на вводимые значения с выводом сообщения об ошибке.
Определить помесячную сумму уплаченного сотрудником налога (за несколько месяцев).
Определить общую сумму НДФЛ по каждому подразделению.
Определить общую перечисляемую организацией сумму НДФЛ за месяц.
Построить гистограмму по данным сводной таблицы.
Код подразделения
Наименование подразделения

1
АХО

2
1 цех

3
2 цех

4
Бухгалтерия

5
Склад

Рис. 13.1. Список подразделений организации
НДФЛ, %
Стандартный вычет на сотрудника
Вычет на одного ребенка
Вычет по инвалидности

13
400
300
400

Рис. 13.2. Ставки льгот и налогов
Дата начислений
Таб. номер
ФИО сотрудника
Код подразделения
Наименование подразделения
Начислена зарплата
Вид места работы
Количество детей
Льгота по инвалидности
НДФЛ

30.11.2005
101
Иванов С.М.
1
 
8254
основное
1
 
 

30.11.2005
102
Воробьева В.С.
4
 
7456
не основное
2
 
 

30.11.2005
103
Сидоров В.С.
2
 
6385
основное
 
 
 

30.11.2005
104
Васильев В.И.
3
 
7214
основное
 
инвалид
 

30.11.2005
105
Емельянов И.П.
2
 
8023
основное
3
 
 

30.11.2005
106
Петров П.В.
3
 
6595
основное
1
 
 

30.11.2005
107
Семенова И.О.
4
 
8645
основное
1
инвалид
 

30.11.2005
108
Сомова В.С.
5
 
4550
основное
 
 
 

30.11.2005
109
Печкина С.И.
1
 
6224
не основное
1
 
 

30.11.2005
110
Яшин С.Н.
1
 
10364
основное
 
 
 

31.12.2005
101
Иванов С.М.
1
 
8254
основное
1
 
 

31.12.2005
102
Воробьева В.С.
4
 
7456
не основное
2
 
 

31.12.2005
103
Сидоров В.С.
2
 
6385
основное
 
 
 

31.12.2005
104
Васильев В.И.
3
 
7214
основное
 
инвалид
 

31.12.2005
105
Емельянов И.П.
2
 
8023
основное
3
 
 

31.12.2005
106
Петров П.В.
3
 
6595
основное
1
 
 

31.12.2005
107
Семенова И.О.
4
 
8645
основное
1
инвалид
 

31.12.2005
108
Сомова В.С.
5
 
4550
основное
 
 
 

31.12.2005
109
Печкина С.И.
1
 
6224
не основное
1
 
 

31.12.2005
110
Яшин С.Н.
1
 
10364
основное
 
 
 

Рис. 13.3. Журнал расчета налога на доходы с физических лиц
Практическая часть
1. Общая характеристика задачи
Наименование экономической задачи: на предприятии бухгалтерия осуществляет деятельность, связанную с ведением журнала расчета подоходного налога с зарплат сотрудников в разрезе подразделений.
Цель:
Построить таблицы.
Организовать автоматический расчет и заполнение графы журнала на доходы с физических лиц (НДФЛ): «Наименование подразделения», «НДФЛ».
Настроить проверку в поле «Вид места работы» на вводимые значения с выводом сообщения об ошибке.
Определить помесячную сумму уплаченного сотрудником налога (за несколько месяцев).
Определить общую сумму НДФЛ по каждому подразделению.
Определить общую перечисляемую организацией сумму НДФЛ за месяц.
Построить гистограмму по данным сводной таблицы
Место решения: данная задача будет решена с помощью Microsoft Excel.
2. Описание алгоритма решения задачи
Исходные данные:
Таблица «Подразделения»
Код подразделения
Наименование подразделения

1
АХО

2
1 цех

3
2 цех

4
Бухгалтерия

5
Склад

Таблица «Ставки льгот и налогов»
НДФЛ,
%
Стандартный вычет на сотрудника
Вычет на одного ребенка
Вычет по инвалидности

13
400
300
400


Таблица «Журнал расчета»
Дата начислений
Таб. номер
ФИО сотрудника
Код подразделения
Наименование подразделения
Начислена зарплата
Вид места работы
Количество детей
Льгота по инвалидности
НДФЛ

30.11.05
101
Иванов С.М.
1

8254
основное
1
 


30.11.05
102
Воробьева B.C.
4

7456
не основное
2
 


30.11.05
103
Сидоров B.C.
2

6385
основное
 
 


30.11.05
104
Васильев В.И.
3

7214
основное
 
инвалид


30.11.05
105
Емельянов И.П.
2

8023
основное
3
 


30.11.05
106
Петров П.В.
3

6595
основное
1
 


30.11.05
107
Семенова И.О.
4

8645
основное
1
инвалид


30.11.05
108
Сомова B.C.
5

4550
основное
 
 


30.11.05
109
Печкина С.И.
1

6224
не основное
1
 


30.11.05
110
Яшин С.Н.
1

10 364
основное
 
 


31.12.05
101
Иванов С.М.
1

8254
основное
1
 


31.12.05
102
Воробьева B.C.
4

7456
не основное
2
 


31.12.05
103
Сидоров B.C.
2

6385
основное
 
 


31.12.05
104
Васильев В.И.
3

7214
основное
 
инвалид


31.12.05
105
Емельянов И.П.
2

8023
основное
3
 


31.12.05
106
Петров П.В.
3

6595
основное
1
 


31.12.05
107
Семенова И.О.
4

8645
основное
1
инвалид


31.12.05
108
Сомова B.C.
5

4550
основное
 
 


31.12.05
109
Печкина С.И.
1

6224
не основное
1
 


31.12.05
110
Яшин С.Н.
1

10 364
основное
 
 



Решение:
Запустить MS Excel
Добавим еще 3 листа: нажав правой кнопкой мыши на ярлыке листа 1 и выбрав в меню «Добавить» затем «Лист». Повторим процедуру еще два раза (Рис. 7)

Рис. 7
На листе 1, начиная с ячейки А1 введем следующие исходные значения из таблицы «Подразделения».
Два раза нажмет по имени листа «Лист 1» и введем новое имя «Подразделения».
На листе 2, начиная с ячейки А1 введем следующие исходные значения из таблицы «Ставки льгот и налогов».
Два раза нажмет по имени листа «Лист 2» и введем новое имя «Ставки льгот и налогов».
На листе 3, начиная с ячейки А1 введем следующие исходные значения из таблицы «Журнал расчета».
Два раза нажмет по имени листа «Лист 3» и введем новое имя «Журнал расчета».
Нажмем на кнопку сохранить и в появившемся окне введем имя файла «НДФЛ».
Расчет данных в таблице «Журнал расчета»
Перейдем на лист «Журнал расчета»
В ячейку Е2 введем формулу (Рис. 8):
=ПРОСМОТР(D2;Подразделения!$A$2:$A$6;Подразделения!$B$2:$B$6)

Рис. 8
Скопируем ее в ячейки с Е3 по Е21. Должно выглядеть так (Рис. 9):

Рис. 9
В ячейку J2 введем следующую формулу:
=ЕСЛИ(G2="основное";(F2-(400 + ЕСЛИ (H2>0;H2*300;0) + ЕСЛИ (I2 = "инвалид"; 400;0))) *13%;F2*13%)

Рис. 10
Скопируем ее в ячейки с J3 по J21. Должно выглядеть так (Рис. 10, Рис. 11)

Рис. 11
Проверка ввода.
Для этого сделаем следующее:
В ячейки N2 и N3 соответственно введем «основное» «не основное».
Выделим ячейки с G2 по G21.
На панели инструментов выберем «Данные», затем «Проверка» (Рис. 12)

Рис. 12
Выберем значения как показано на рисунке 13

Рис. 13
Перейдем ко вкладке «Сообщение об ошибке».

Рис. 14
Выберем значения как показано на рисунке 14
Кнопка «ОК»
Определение помесячной суммы уплаченного сотрудником налога (за несколько месяцев)
Лист 4 переименуем в «НДФЛ по месяцам».
На панели инструментов выберем меню «Данные» затем «Сводная таблица» (Рис. 15).

Рис. 15
Нажмем кнопку «Далее».
Укажем следующий диапазон (Рис. 16): 'Журнал расчета'!$A$1:$J$21.

Рис. 16
Кнопка «Далее».
Выберем «Существующий лист» и нажмем кнопку «Макет».
Расположим поля как показано на рисунке 17. Кнопка «Готово».

Рис. 17
Аналогично определим общую сумму НДФЛ по каждому подразделению и общую сумму перечисляемую организацией НДФЛ за месяц, только макеты разные рисунок 18 и 19 соответственно.

Рис. 18

Рис. 19
Для того чтобы построить гистограмму необходимо сделать следующее:
Перейдем на лист «НДФЛ по подразделениям».
Выделим ячейки с А2 по В10 и нажмем на панели инструментов кнопку «Матер диаграмм» .
Гистограмма готова.
Расчетные данные:
Таблица «Журнал расчета»
Дата
начислений
Таб. номер
ФИО сотрудника
Код подразделения
Наименование подразделения
Начислена зарплата
Вид места работы
Количество детей
Льгота по инвалидности
НДФЛ

30.11.05
101
Иванов С.М.
1
АХО
8254
основное
1
 
982,02

30.11.05
102
Воробьева B.C.
4
Бухгалтерия
7456
не основное
2
 
969,28

30.11.05
103
Сидоров B.C.
2
1 цех
6385
основное
 
 
778,05

30.11.05
104
Васильев В.И.
3
2 цех
7214
основное
 
инвалид
833,82

30.11.05
105
Емельянов И.П.
2
1 цех
8023
основное
3
 
873,99

30.11.05
106
Петров П.В.
3
2 цех
6595
основное
1
 
766,35

30.11.05
107
Семенова И.О.
4
Бухгалтерия
8645
основное
1
инвалид
980,85

30.11.05
108
Сомова B.C.
5
Склад
4550
основное
 
 
539,50

30.11.05
109
Печкина С.И.
1
АХО
6224
не основное
1
 
809,12

30.11.05
110
Яшин С.Н.
1
АХО
10 364
основное
 
 
1295,32

31.12.05
101
Иванов С.М.
1
АХО
8254
основное
1
 
982,02

31.12.05
102
Воробьева B.C.
4
Бухгалтерия
7456
не основное
2
 
969,28

31.12.05
103
Сидоров B.C.
2
1 цех
6385
основное
 
 
778,05

31.12.05
104
Васильев В.И.
3
2 цех
7214
основное
 
инвалид
833,82

31.12.05
105
Емельянов И.П.
2
1 цех
8023
основное
3
 
873,99

31.12.05
106
Петров П.В.
3
2 цех
6595
основное
1
 
766,35

31.12.05
107
Семенова И.О.
4
Бухгалтерия
8645
основное
1
инвалид
980,85

31.12.05
108
Сомова B.C.
5
Склад
4550
основное
 
 
539,50

31.12.05
109
Печкина С