Алгоритм расчета

Оплата труда агентов складывается из двух источников: комиссионное вознаграждение и премия.

1. Комиссионное вознаграждение за заказ рассчитывается по следующему правилу: если сумма заказа свыше 250000 размер комиссионных составит 5% от суммы заказа, в противном случае – 3%.

2. Премия за выполнение заказа в заявленный срок исчисляется в размере 0,5 % от суммы заказа. Для определения срока, за который заказ выполнен, находится разность между Датой выполнения заказаиДатой заключения договора.

Методические указания

1. Размер комиссионныхиПремия(таблица 1) рассчитываются с помощью логической функции ЕСЛИ.

2. Количество сделок, заключенных каждым агентом (таблица 2) рассчитывается с помощью функции СЧЕТЕСЛИ.

3. Общий объем сделок, заключенных каждым агентом иОплата за выполненную работукаждому агенту за месяц (таблица 2) определяется с помощью функции СУММЕСЛИ.

4. Средняя величина заказаиСредняя зарплата агента по фирме рассчитывается с использованием функции СРЗНАЧ.

2.2 Задание для самостоятельной работы №1

Задача «Расчет штрафов за непредставление налоговой декларации»

Цель задания:приобретение навыков решения реальной задачи в области налогообложения, автоматизирующей расчет штрафов, с использованием встроенных функций MS Excel.

Порядок выполнения задания. Работа состоит из двух частей:

1. домашнее задание (осмысление задачи, конструирование расчетных формул и представление преподавателю в письменном виде результатов выполнения домашнего задания);

2. решение задачи в компьютерном классе и защита работы.

Постановка задачи

В соответствии со статьёй 119 части IНалогового кодекса РФ (ред. 24.03.2001г.) «непредставление налогоплательщиком в установленный законодательством о налогах и сборах срок налоговой декларации в налоговый орган по месту учёта … влечёт взыскание штрафа в размере 5 процентов суммы налога, подлежащей уплате (доплате) на основе этой декларации, за каждый полный или неполный месяц со дня, установленного для её представления, но не более 30 процентов указанной суммы и не менее 100 рублей. … Непредставление налогоплательщиком налоговой декларации в налоговый орган в течение более 180 дней по истечении установленного законодательством о налогах срока представления такой декларации влечёт взыскание штрафа в размере 10 процентов суммы налога, подлежащей уплате на основе этой декларации, за каждый полный или неполный месяц, начиная со 181-го дня».

Требуется средствами MSExcelпо известным суммам налога, а также по установленному сроку представления и датам фактической подачи налоговых деклараций автоматизировать расчёт штрафов за несвоевременную их подачу.

Методические указания.

1. Создайте в MSExcelзаготовку таблицы по образцу таблицы 3. Занесите данные в ячейки А2:Е7. Ячейки Е2:J7 таблицы заполняются расчётными формулами.

2. В столбце Fрассчитайте число дней просрочки с учётом того, что, если декларация подана в срок, число дней просрочки должно быть равно 0. Для этого следует воспользоваться оператором ЕСЛИ. Для ячейкиF2 формула принимает вид:

(1)

3. В столбце Gрассчитайте количество полных или неполных месяцев просрочки. Для этого необходимо число дней просрочки разделить на 30 и полученный результат округлить до ближайшего большего целого числа. С помощью кнопкиf_xна панели инструментов вызовите встроенную функцию (в разделе математических) «ОКРВВЕРХ». Эта функция требует указания в скобках двух параметров: аргумента, т.е. собственно округляемого числа, и разрядность , т.е. кратного до которого требуется округлить. Более подробную информацию об этой встроенной функции можно найти в справочной системе. Для ячейкиG2формула имеет вид:

(2)

4. В столбце Hрассчитайте величину штрафа в процентах с учётом следующих трёх возможных случаев:

5. если декларация подана в срок (G2<=0), штраф отсутствует (равен 0);

6. если количество полных или неполных месяцев находится в диапазоне от 0 до 6 (включая 6), то величина штрафа в процентах определяется произведением числа этих месяцев на 5%;

7. если количество полных или неполных месяцев превосходит число 6, то величина штрафа в процентах определяется как сумма 30% (часть штрафа за первые 6 месяцев) и произведения 10% на число оставшихся месяцев (часть штрафа за период, начиная со 181-го дня). С учётом этого рабочая формула для ячейки H2 принимает вид:

(3)

8. В столбце Iрассчитайте величину штрафа в рублях с учётом того, что в случае своевременной подачи декларации величина штрафа равняется 0, а в случае, когда величина штрафа, вычисленного по рассчитанным процентам, составляет менее 100 рублей, окончательное значение принимается равным 100 рублям. При этом формула в ячейкеI2 принимает вид:

(4)

9. В столбце Jсамостоятельно рассчитайте суммарное значение налога по декларации и штрафа.

Таблица 3 – Сведения о представлении налоговых жеклараций

A	B	C	D	E	F	G	H	I	J
№ п/п	Фамилия И.О. налогоплательщика	Сумма налога, подлежащего уплате	Установленный срок представления налоговой декларации	Дата фактической подачи налоговой декларации	Число дней просрочки	Количество полных или неполных месяцев просрочки	Величина штрафа в %	Величина штрафа в рублях	Сумма налога со штрафом
1	Авдеев А.С.	7 835,00р.	03.05.06	18.05.06
2	Бондарев В.К.	1 900,00р.		25.04.06
3	Иванов И.П.	16 350,00р.		23.09.06
4	Карцев П.Р.	410,00р.		15.05.06
5	Сидоров П.С.	2 117,00р.		30.12.06
6	Якушев А.Р.	5 490,00р.		27.10.06

3. Создание сводных многоуровневых таблиц

Структура позволяет управлять степенью детализации отображаемых данных, что особенно важно для больших таблиц. При использовании структуризации часть данных в любой момент может быть скрыта или раскрыта. Это позволяет легко ориентироваться в больших таблицах. Допустимы восемь вложенных уровней структуры.

Если необходимо создать многоуровневую таблицу с вычислением промежуточных итогов, Excelпредоставляет функцию автоматического вычисления итогов. Причем, имеется возможность «вложения» итогов для групп, находящихся внутри больших групп. Например, если требуется подсчитать объемы продаж по регионам, а внутри этих групп по видам продукции.

Для применения этого инструмента данные должны быть представлены в виде списка, предварительно отсортированного по ключевым реквизитам.

Технология подведения промежуточных итогов:

1. Отсортируйте список по столбцу, для которого необходимо подвести промежуточный итог.

2. Укажите ячейку в этом списке.

3. Выберите команду ИтогименюДанные. Появится окно диалога (рис. 2).

Рис. 2 - Вид окна Промежуточные итоги

4. В поле списка При каждом изменении вукажите столбец, содержащий группы, по которым необходимо подвести итоги. Это должен быть тот столбец, по которому проводилась сортировка списка на шаге 1.

5. Выберите функцию, необходимую для подведения итогов, из списка Операция (по умолчанию – СУММ).

В поле списка Добавить итоги повыберите столбцы, содержащие значения, по которым необходимо подвести итоги, (например, Количество, Сумма).

В результате выполнения команды таблица будет дополнена строками с итогами по каждому виду продукции отдельно.

Чтобы «вложить» или вставить итог для групп, находящихся внутри уже существующих групп выполните следующие действия:

1. Отсортируйте список по двум или более столбцам, для которых необходимо подвести итог. Например, чтобы определить объем продаж по регионам и по видам продукции внутри каждого региона, в первую очередь следует отсортировать список по столбцу «регионы», а затем – по столбцу «вид продукции». При подведении промежуточных итогов, значения итогов по видам продукции вкладываются в итоги по регионам.

2. Вставьте автоматические итоги для первого столбца, содержащего группы суммируемых данных. Этот столбец при сортировке списка должен быть указан в поле Сортировать по.

3. После создания автоматических итогов в первом столбце, повторите процедуру для следующего столбца.

4. Выделите ячейку в списке.

5. Выберите команду Итоги в менюДанные. Затем укажите из спискаПри каждом изменении в столбец, по которому необходимо подвести итоги. Снимите флажок«Заменить текущие итоги»и нажмите кнопку OK.