Лабораторная работа 1 создать таблицу представляющую собой табель учета рабочего времени

Единственный в мире Музей Смайликов Самая яркая достопримечательность Крыма

Скачать 200.61 Kb. Название Лабораторная работа 1 Создать таблицу, представляющую собой табель учета рабочего времени. Выполнение работы 1 Дата 05.11.2022 Размер 200.61 Kb. Формат файла Имя файла praktikum_ms_excel_1_kurs-1-2.pdf Тип Лабораторная работа #770893

---

Лабораторная работа 

СОЗДАНИЕ ТАБЕЛЯ РАБОЧЕГО ВРЕМЕНИ 

С ИСПОЛЬЗОВАНИЕМ EXCEL

Цели работы:

1.        
Получение навыков в создании и использовании шаблона.

2.        
Работа со встроенными функциями СУМ, СЧЕТ, СЧЕТЕСЛИ.

3.        
Работа с закреплѐнными областями.

ПОСТАНОВКА ЗАДАЧИ

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

Работа по
созданию таблицы делится на этапы:

• Создание бланка- шаблона
таблицы Учета рабочего времени.  

• Заполнение таблицы Учета
рабочего времени исходными данными.

• Выполнение расчетных
операций (подсчет дней явок, количество отработанных часов, дней отпуска, дней
пропущенных по болезни или из-за прогулов) с помощью формул.

• Построение диаграммы в
EXCEL.

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

В каждом варианте указываются исходные данные для
заполнения таблицы:

q 
Название месяца, год

q 
Количество сотрудников в подразделении.

q 
Количество дней отпуска в подразделении.

q 
Количество дней прогулов.

q 
Количество дней, пропущенных по болезни.

Формулы и
методика расчетных операций приведены ниже.

Построение диаграммы в EXCEL по графам-дни фактических
явок, отпуск, прогул, болезнь (см. лабораторную работу № 2).

ТЕОРЕТИЧЕСКИЙ МАТЕРИАЛ

Создание бланка-шаблона

1.     
Оставьте в рабочей книге только один лист.

2.     
Сформатируйте заголовок табеля учета рабочего времени за текущий месяц и
подготовьте таблицу-бланк по образцу, приведенному на рис. 1.

Воспользуйтесь всеми известными вам приемами
форматирования. Сформатируйте заголовок, применив различные способы выравнивания
текста.

 

Рис. 1.
Таблица-бланк

Введите числа месяца с 1-го по 31-е. Для столбцов,
содержащих даты, установите ширину столбца, равную 2.

Если на вашем предприятии постоянный состав
сотрудников, внесите в шаблон фамилии и профессии.

3.     
Для сохранения подготовленного файла в качестве шаблона:

•  
выполните команду Сохранить как… меню Файл;

•  
введите имя сохраняемого файла в поле ввода Имя файла: Табель;

•  
в списке типов файлов выберите Шаблон, расширение файла
сменится на .xlt;

•  
нажмите ОК и  закройте файл.

Применение шаблона

Для создания нового файла с применением шаблона
выполните следующие действия:

• В
меню Файл выберите Создать.

• В
списке Общие диалогового окна <Создание документа> выделите
шаблон, на основе которого хотите создать новую рабочую книгу (рис. 2.).

• Выберите
кнопку ОК. Таким образом, вы получите рабочую копию шаблона.

 

Рис. 2.
Создание шаблона

Заполнение табеля

1.        
Введите название текущего месяца в заголовок табеля.

2.        
Сразу же выделите цветом столбцы, соответствующие нерабочим дням недели
(чтобы случайно не ошибиться при заполнении табеля).

3.        
Проставьте для каждого сотрудника:

Ø    
количество часов, отработанных за день, или Ø
о, если он находится в отпуске, или

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

Закрепление столбца, строки таблицы

Имея широкую таблицу, можно столкнуться с неудобствами
при заполнений. Дело в том, что, перемещаясь вправо для заполнения таблицы, вы
теряете из вида столбец с фамилиями и становится трудно определить, кому из
сотрудников проставляете рабочие часы.

Microsoft Excel позволяет зафиксировать заголовок на
странице, чтобы при перемещении нужные вам столбцы (или строки) оставались на
своем месте. Для того чтобы зафиксировать столбец «Фамилия»:

•      выделите
столбец справа от столбца «Фамилия»

(«Профессия»);

•      в
меню Окно выберите команду Закрепить области;

•      далее
свободно пользуйтесь горизонтальной полосой прокрутки, фамилии ваших
сотрудников не исчезнут с экрана.

Работая с большими таблицами, можете пользоваться
следующими возможностями фиксации заголовков.

•      Чтобы
зафиксировать горизонтальные заголовки, выделите строку ниже заголовков.

•      Чтобы
зафиксировать вертикальные заголовки, выделите столбец справа от заголовков.

•      Чтобы
зафиксировать и вертикальные, и горизонтальные заголовки выделите ячейку, по
которой хотите зафиксировать заголовки.

В меню Окно выберите команду Закрепить
области. Все строки выше выделенной строки (ячейки) будут зафиксированы и
все столбцы слева от выделенного столбца (ячейки) будут зафиксированы.

Чтобы отменить фиксацию заголовков в меню Окно
выберите команду Снять закрепление областей.

Ввод формул для подсчета дней явок, неявок и
отработанных часов

4.     
Самостоятельно вставьте формулу суммирования соответствующих ячеек
строки для подсчета отработанных часов. Заполните формулу вниз. 

5.     
Для подсчета дней явок необходимо в каждой строке (для каждого
сотрудника) подсчитать количество ячеек, содержащих числа (не суммируя эти
числа). 

Для этого:

• выделите ячейку таблицы, в
которую нужно разместить формулу (для первого сотрудника);

• выполните команду Вставка_ Функция…;  

• в списке Имя функции
окна диалога <Мастер функций> выберите функцию СЧЕТ. Если вы не
знаете, к какой категории относится искомая функция, выберите категорию Полный
алфавитный перечень и дальше ищите по алфавиту. Нажмите кнопку Ок. 

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

• Нажмите кнопку Ок.  Заполните формулу вниз.

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

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

Аналогично пунктам 6, 7 надо заполнить столбцы для
дней по болезни (критерий-русская буква б), выходных дней
(критерий-русская буква в),  дней прогулов, если таковые имеются
(критерий-русская буква п).

ЗАДАНИЕ

Создать табель рабочего времени по изложенной выше
методике согласно своему варианту.

Варианты заданий

Вариант 1

 

Вариант 2

 

Вариант 3

 

Вариант 5

 

Вариант 6

Вариант 7

 

Вариант 9

 

Вариант 10

 

Вариант 11

 

Вариант 12

 

Вариант 13

Вариант 14

 

Вариант 15

 

Вариант 16

 

Лабораторная работа

Задание №1. На листе ТАБЕЛЬ
оформить таблицу «Табель учета рабочего времени» по образцу (см. приложение№1).

Ø
Табельный номер, ФИО,
должность  оформить через ссылку на
лист ОТДЕЛ КАДРОВ

Ø
Отработано дней = 31 – количество выходных (функция СЧЕТЕСЛИ)

Ø
Отработано часов = сумма чисел (функция СУММ)

Ø
Отпуск  = количество букв «О» (функция СЧЕТЕСЛИ)

Ø
Больничный  = количество букв «Б» (функция СЧЕТЕСЛИ)

Ø
Выходные дни выделить серым
цветом (УСЛОВНОЕ ФОРМАТИРОВАНИЕ)

Задание №2. На листе РАСЧЕТ
оформить таблицу «???» по образцу (см. приложение№2).

Ø
Табельный номер, ФИО,
должность, оклад  оформить через
ссылку на лист ОТДЕЛ КАДРОВ

Ø
Отработано дней, отпускные дни оформить через ссылку на лист ТАБЕЛЬ

Ø
Повременно = Если форма оплаты ДНИ, то оклад / норму (дней)
* отработано дней, иначе оклад / норму (час) * отработано часов

Ø
Отпускные сумма  = сумма за три предшествующих месяца (лист ОТДЕЛ КАДРОВ)
/ 3 / 25,25 * количество дней отпуска

Ø
Премиальные = 10% от повременно

Ø
Больничный лист = сумма за 2 предшествующих месяца / 40 * количество
больничных дней * коэффициент

Ø
Районный коэффициент = 15% от (повременно + премиальные + больничный)

Ø
Всего начислено = повременно + премиальные + районный коэффициент +
отпускные + больничный

Ø
Вычеты = Если сумма валового дохода < 20000, то количество
детей * 300 + 400, иначе 0

Ø
НДФЛ = 13% от (всего начислено – вычеты)

Ø
Аванс = 40% от оклада

Ø
Всего удержано = НДФЛ + аванс

Ø
К выдаче = всего начислено – всего удержано

Сохранить файл под именем
МАРТ.

 Табель учета рабочего
времени                        

                                                                                      ПРИЛОЖЕНИЕ
1

Таб.№	Фамилия, имя, отчество	Должность	Март	Отработано	Количество дней
1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	Дней	Часов	Отпуска	Больничных

Норма рабочих дней в марте 20       Норма
рабочих часов в марте 160

ПРИЛОЖЕНИЕ 2

Таб.№

Фамилия, имя, отчество

Должность

Оклад

Отработано дней

Начисления

Всего начислено

Удержания

Всего удержано

К выдаче

Повременно

Отпускные

Премиальные

Больничные

Районный коэффициент

Вычеты

НДФЛ

Аванс

Дни

Сумма

©
Клинцовский
текстильный техникум.
Лабораторные работы по Microsoft
Excel.
Лабораторная работа №5. www.debryansk.ru/~ktt

Упражнение 1

Создание шаблона.
Работа с шаблонами документов. Совместное
использование Word
и Excel.

Представьте себя работником Отдела
кадров, которому еже­месячно предстоит
заполнять Табель учета рабочего времени
на сотрудников предприятия. Разумеется,
хотелось бы максимально автоматизировать
эту операцию. Удобно создать шаблон
заготов­ки бланка и применить
специальные функции.

Создание бланка-шаблона

1. Оставьте в рабочей книге только один
лист.

2.
Сформатируйте заголовок табеля учета
рабочего времени за текущий месяц и
подготовьте таблицу-бланк по образцу,
приве­денному на рис. 1

Воспользуйтесь всеми известными вам
приемами форматиро­вания. Сформатируйте
заголовок, применив различные способы
выравнивания текста.

Введите числа месяца с 1-го по 31-е. Для
столбцов, содержа­щих даты, установите
ширину столбца, равную 2.

Если на вашем предприятии постоянный
состав сотрудников, внесите в шаблон
фамилии и профессии.

3. Для сохранения подготовленного файла
в качестве шаблона:

• выполните команду Сохранить как…меню Файл;

• введите имя сохраняемого файла в поле
ввода Имя файла: Табель;

• в списке типов файлов выберите
Шаблон,расширение файла сменится на
.xlt;

• нажмите ОК;

• закройте файл.

Применение шаблона

Для создания нового файла с применением
шаблона выпол­ните следующие действия:

• В меню Файлвыберите Создать.

• В списке Общиедиалогового окна
<Создание документа> выделите шаблон,
на основе которого хотите создать новую
рабочую книгу (рис.2).

•Выберите кнопку ОК.

Таким образом, вы получите рабочую копию
шаблона.

1. Введите название текущего месяца в
заголовок табеля.

2. Сразу же выделите цветом столбцы,
соответствующие не­рабочим дням
недели (чтобы случайно не ошибиться при
запол­нении табеля).

3. Проставьте для каждого сотрудника:

• количество часов, отработанных за
день, или

• о,если он находится в отпуске,
или

• б,если в этот день сотрудник
болеет, или

• п,если прогуливает.

о, б, п —русские буквы, проставляются
без кавычек.

Имея такую широкую таблицу, как ваша,
можно столкнуться с неудобствами при
заполнений. Дело в том, что, перемещаясь
вправо для заполнения таблицы, вы теряете
из вида столбец с фамилиями и становится
трудно определить, кому из сотрудни­ков
проставляете рабочие часы.

Помните, в MicrosoftWordсуществовала возможность зафик­сировать
заголовок таблицы, чтобы он автоматически
появлялся на каждой новой странице?

MicrosoftExcelпозволяет зафиксировать заголовок на
страни­це, чтобы при перемещении
нужные вам столбцы (или строки) оставались
на своем месте. Для того, чтобы зафиксировать
стол­бец «Фамилия»:

• выделите столбец справа от столбца
«Фамилия» («Профессия»);

• в меню Окновыберите команду
Закрепить области;

• далее свободно пользуйтесь горизонтальной
полосой про­крутки, фамилии ваших
сотрудников не исчезнут с экрана.

Работая с большими таблицами, можете
пользоваться сле­дующими возможностями
фиксации заголовков.

• Чтобы зафиксировать горизонтальные
заголовки, выделите строку ниже
заголовков.

• Чтобы зафиксировать вертикальные
заголовки, выделите столбец справа от
заголовков.

•Чтобы зафиксировать и вертикальные, и
горизонтальные заголовки выделите
ячейку, по которой хотите зафиксировать
заголовки.

В меню Окновыберите команду
Закрепить области.Все строки выше
выделенной строки (ячейки) будут
зафиксированы и все столбцы слева от
выделенного столбца (ячейки) будут
за­фиксированы.

Чтобы отменить фиксацию заголовков в
меню Окновыберите команду Снять
закрепление областей.

Хотелось бы ввести формулы для подсчета
дней явок, неявок и отработанных часов.

4. Самостоятельно вставьте формулу
суммирования соответст­вующих ячеек
строки для подсчета отработанных часов.
Запол­ните формулу вниз.

5. Для подсчета дней явок необходимо в
каждой строке (для каждого сотрудника)
подсчитать количество ячеек, содержащих
числа (не суммируя эти числа). Для этого:

• выделите ячейку таблицы, в которую
нужно разместить формулу (для первого
сотрудника);

• выполните команду Вставка
Функция…;

• в списке Имя функцииокна диалога
<Мастер функций> выберите функцию
СЧЕТ (рис. 3). Если вы не знаете, к какой
категории относится искомая функция,
выберите категориюПолный алфавитный
перечень и дальше ищите по алфавиту.
Нажмите кнопкуОк.

• В следующем окне нужно указать диапазон
значений.

Нет необходимости вводить адреса ячеек
с клавиатуры.

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

Нажмите кнопку Ок.

• Заполните формулу вниз.

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

Заполните формулу вниз по столбцу.

В результате вы получите приблизительно
следующее.

Упражнение 2

Совместное
использование Word
и Excel.

MicrosoftExcel—
это мощный инструмент анализа данных,
позволяющий создавать электронные
таблицы, диаграммы и другие формы
представления информа­ции. В свою
очередь,MicrosoftWord,
как вы уже знаете, — это мощный инструмент
для создания профессионально выглядящих
документов. В этой работе вы узнаете,
какWordиExcelмогут работать вместе и какие возможности
предоставляет это сотрудничество.

Соседние файлы в папке labex

• #
• #
• #
• #
• #
• #
• #

Перейти к содержимому

Модуль ПМ.02 Организация деятельности коллектива исполнителей

МДК.02.01. Управление коллективом исполнителей

Урок 66

Тема: ПЗ № 22 Составление табеля учета использования рабочего времени.

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

Краткие теоретические сведения по теме:

Табель учета рабочего времени и расчета оплаты труда (форма N Т-12) и табель учета рабочего времени (форма N Т-13) применяются для учета времени, фактически отработанного и (или) неотработанного каждым работником организации, для контроля за соблюдением работниками установленного режима рабочего времени, для получения данных об отработанном времени, расчета оплаты труда, а также для составления статистической отчетности по труду. При раздельном ведении учета рабочего времени и расчета с персоналом по оплате труда допускается применение раздела I. «Учет рабочего времени» табеля по форме N Т-12 в качестве самостоятельного документа без заполнения раздела 2. «Расчет с персоналом по оплате труда». Форма N Т-13 применяется для учета рабочего времени.

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

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

Для отражения ежедневных затрат рабочего времени за месяц на каждого работника в табеле отведено:

в форме N Т-12 (графы 4, 6) — две строки;

в форме N Т-13 (графа 4) — четыре строки (по две на каждую половину месяца) и соответствующее число граф (15 и 16).

В формах N Т-12 и N Т-13 (в графах 4, 6) верхняя строка применяется для отметки условных обозначений (кодов) затрат рабочего времени, а нижняя — для записи продолжительности отработанного или неотработанного времени (в часах, минутах) по соответствующим кодам затрат рабочего времени на каждую дату.

При заполнении граф 5 и 7 табеля по форме N Т-12 в верхних строках проставляется количество отработанных дней, в нижних строках — количество часов, отработанных каждым работником за учетный период.

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

Форма N Т-13 «Табель учета рабочего времени» применяется при автоматизированной обработке учетных данных. При составлении табеля по форме N Т-13:

— при записи учетных данных для начисления заработной платы только по одному виду оплаты и корреспондирующему счету, общим для всех работников, включенных в Табель, заполняются реквизиты «код вида оплаты», «корреспондирующий счет» над таблицей с графами с 7-9 и графа 9 без заполнения граф 7 и 8.

— при записи учетных данных для начисления заработной платы по нескольким (от двух до четырех) видам оплаты и корреспондирующих счетов заполняются графы 7-9. Дополнительный блок с идентичными номерами граф предусмотрен для заполнения данных по видам оплаты, если их количество превышает четыре.

Бланки табеля по форме N Т-13 с частично заполненными реквизитами могут быть изготовлены с применением средств вычислительной техники. К таким реквизитам относятся: структурное подразделение, фамилия, имя, отчество, должность (специальность, профессия), табельный номер и т.п. — то есть данные, содержащиеся в справочниках условно-постоянной информации организации. В этом случае форма табеля изменяется в соответствии с принятой технологией обработки учетных данных.

Условные обозначения отработанного и неотработанного времени, представленные на титульном листе формы N Т-12, применяются и при заполнении табеля по форме N Т-13.

Практические задания по теме

Задание № 1

Изучите структуру и основные разделы табеля учета рабочего времени и расчета оплаты труда (форма N Т-12) и табеля учета рабочего времени (форма N Т-13).

Задание № 2

Изучите указания по заполнению табеля учета рабочего времени и расчета оплаты труда (форма N Т-12) и табеля учета рабочего времени (форма N Т-13), приведенные в кратких теоретических сведениях.

Задание № 3

Заполните табель (форма N Т-12) на 2-х работников выбранной Вами должности отработавших полный месяц.

Задание № 4

Заполните табель (форма N Т-12) на работника выбранной Вами должности отработавшего не полный месяц и убывшим в очередной ежегодный оплачиваемый отпуск.

Задание № 5

Заполните табель (форма N Т-12) на работника выбранной Вами должности отработавшего не полный месяц и убывшим в командировку.

Задание № 6

Заполните табель (форма N Т-12) на работника выбранной Вами должности отработавшего полный месяц посменно (день-ночь).

Задание № 7

Выполните задания № 3 — № 6 заполнив табель формы N Т-13.

Задание № 8

Дайте ответы и пояснения на следующие вопросы:

1. Как организуется учет отработанного рабочего времени?

2. Какая существует первичная документация по учету выработки?

3. Каковы основные задачи бухгалтерского учета труда и заработной платы?

4. Как должна оплачиваться работа в сверхурочное время?

5. Как организуется учет оплаты труда за непроработанное время, подлежащее оплате в соответствии с действующим законодательством?

Работа добавлена на сайт samzan.net: 2016-03-13

нформационные системы в экономике

Лабораторные работы

Лабораторная работа № 1

Тема: Электронный табель учета рабочего времени (2 часа)

Табель – именной список сотрудников подразделения, в котором учитывается отработанное каждым сотрудником время.

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

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

Рис. 1. Табель учёта времени

1.  Для первого работника рассчитайте сумму отработанных часов в ячейке AG4 при помощи функции СУММ.
2.  В ячейке AH4 вычислите количество отработанных дней при помощи функции СЧЕТЕCЛИ (категория Статистические).

Функция СЧЕТЕСЛИ производит подсчет количества ячеек внутри интервала, значения в которых удовлетворяют заданному критерию. Критерием могут служить как цифры, так и символы. Например, =СЧЁТЕСЛИ(C4:AF4;»>0″) – подсчет количества ячеек, в которых значение больше нуля в диапазоне C4:AF4.

1.  Проведите аналогичные вычисления для остальных работников (с помощью Автозаполнения).

Лабораторная работа № 2

Тема: Учет и налогообложение доходов физических лиц (4 часа)

Доходы физических лиц в виде материальной выгоды

Задание 1. Организация одному из своих работников выдала 20 февраля 2007 года заем в сумме 50000 руб. на три месяца. Процентная ставка за пользование заемными средствами по договору была определена в размере 10% годовых. Заем с процентами возвращен 21 мая 2007 года. В качестве процентов уплачено 1233 руб. Расчет приведен в строке 5 таблицы, представленной на рис. 2

Задание 2. Работнику организации 5 января 2007 года выдан заем в сумме 9000 руб. на три месяца. Оплата процентов в размере 5% годовых производится ежемесячно одновременно с частичным погашением займа. Ставка рефинансирования на дату выдачи займа составляет 10,5%. Погашение займа производится частями – 1 февраля 2007 года, 5 марта 2007 года, 9 апреля 2007 года в размере 3000, 2000, 4000 руб. соответственно. В это же время выплачивались проценты.

Налоговые вычеты

Социальные налоговые вычеты предоставляются налогоплательщику, если он тратил собственные средства на благотворительность (не более 25% полученного дохода), на лечение (не более 25000 руб., не учитывая расходов на дорогостоящие лекарства), на обучение (не более 25000 руб. на обучение налогоплательщика и 25000 руб. на обучение каждого его ребенка).

Задание 3. Рассчитать социальный налоговый вычет на благотворительные цели. Исходные данные: полученный доход 1000000 руб., перечисленная сумма на благотворительность 26000 руб.

Задание 4. Рассчитать социальный налоговый вычет при оплате лечения, если на лечение было потрачено 44000 руб., из них 21000 руб. – дорогостоящее лечение.

Имущественные налоговые вычеты распространяются на две статьи доходов: от реализации недвижимости и имущества, от реализации ценных бумаг.

Налогоплательщик имеет право на получение следующих имущественных налоговых вычетов:

•  В сумме, полученной в налоговом периоде от продажи жилых домов, квартир, дач, садовых участков, находившихся в собственности менее трёх лет, но не превышающих в целом 1000000 руб., а также в сумме, полученной в налоговом периоде от продажи иного имущества, находившегося в собственности менее трех лет, но не превышающей 125000 руб. При продажи жилых домов, дач, квартир, земельных участков, находившихся три года и более, а также иного имущества, находившегося в собственности три года и более, имущественный налоговый вычет предоставляется в сумме, полученной от продажи указанного имущества.
•  В сумме, израсходованной на новое строительство либо приобретение на территории РФ жилого дома или квартиры, в размере фактически произведенных расходов, а также в сумме, направленной на погашение процентов по ипотечным кредитам, полученным в банках РФ и фактически израсходованным на новое строительство либо приобретение на территории РФ жилого дома или квартиры.
•  При реализации ценных бумаг вместо использования права на имущественный налоговый вычет налогоплательщик может уменьшить сумму общего дохода от их реализации на совокупную сумму фактически произведенных и документально подтвержденных расходов на приобретение указанных ценных бумаг.

Имущественные налоговые вычеты от приобретения (строительства) жилого дома или квартиры могут уменьшать налоговую базу, облагаемую по ставке 13%, и переноситься на следующие налоговые периоды, если не использованы полностью в текущем налоговом периоде:

•  В сумме, израсходованной на новое строительство либо приобретение на территории РФ жилого дома или квартиры, в размере фактически произведенных расходов (но не более 600000 руб.).
•  В сумме, направленной на погашение процентов по ипотечным кредитам, полученным в банках РФ и фактически израсходованным на новое строительство либо приобретение на территории РФ жилого дома или квартиры.

Задание 6. Рассчитать имущественный налоговый вычет от продажи квартиры, если сумма, полученная от продажи, составила 2000000 руб. Продавец вступил в право собственности 04.05.2001 г. Договор купли-продажи данной квартиры заключен 12.08.2004 г. (рис. 3).

Задание 7. Рассчитать имущественный налоговый вычет от продажи иного имущества, если сумма, полученная от продажи, составила 235000 руб. Продавец вступил в право собственности 14.10.2002 г. Договор купли-продажи указанного имущества заключен 22.05.2004 г.

Учет стандартных вычетов

При определении размера налоговой базы налогоплательщик имеет право на получение следующих стандартных налоговых вычетов:

•  налоговый вычет в размере 400 рублей за каждый месяц налогового периода действует до месяца, в котором их доход, исчисленный нарастающим итогом с начала налогового периода превысил 20 000 рублей. Начиная с месяца, в котором указанный доход превысил 20 000 рублей, налоговый вычет, предусмотренный настоящим подпунктом, не применяется;
•  налоговый вычет в размере 600 рублей за каждый месяц налогового периода распространяется на:

каждого ребенка у налогоплательщиков, на обеспечении которых находится ребенок, и которые являются родителями или супругами родителей;

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

Указанный налоговый вычет действует до месяца, в котором доход налогоплательщиков, исчисленный нарастающим итогом с начала налогового периода превысил 40 000 рублей. Начиная с месяца, в котором указанный доход превысил 40 000 рублей, налоговый вычет, предусмотренный настоящим подпунктом, не применяется.

Налоговый вычет, установленный настоящим подпунктом, производится на каждого ребенка в возрасте до 18 лет, а также на каждого учащегося очной формы обучения, аспиранта, ординатора, студента, курсанта в возрасте до 24 лет у родителей и (или) супругов родителей, опекунов или попечителей, приемных родителей.

Указанный налоговый вычет удваивается в случае, если ребенок в возрасте до 18 лет является ребенком-инвалидом, а также в случае, если учащийся очной формы обучения, аспирант, ординатор, студент в возрасте до 24 лет является инвалидом I или II группы.

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

Под одинокими родителями для целей настоящей главы понимается один из родителей, не состоящий в зарегистрированном браке.

Задание 8. Определите сумму налога на доходы сотрудницы организации, не состоящей в браке и имеющей двоих детей в возрасте до 18 лет, ежемесячно начисляется заработная плата в размере 10500 руб. При определении размера налоговой базы работница имеет право на получение следующих стандартных вычетов за каждый месяц налогового периода до месяца, в котором ее доход, исчисленный нарастающим итогом с начала налогового периода, превысит 20000 руб.:

•  в размере 400 руб.;
•  в размере 1200 руб. на каждого ребенка.

Лабораторная работа №3

Тема: Учет доходов и расходов в быту и бизнесе (4 часа)

Система учета семейных расходов будет состоять из двух компонентов:

•  таблицы с журналом регистрации, в которую вносятся данные о доходах и расходах, а также об их источниках;
•  таблиц, в которых обрабатывается информация журнала регистрации.

Присвойте листу имя Журнал Регистрации и создайте на нем шапку таблицы, как на рис. 5.

Рис. 5. Доходы расходы семьи

Выделите диапазон A1:G1000 → Нажмите комбинацию клавиш [Ctrl+Shift+F3] → в диалоговом окне Создать имена отметьте опцию В строке выше → ОК. После этого диапазонам ячеек будут присвоены имена: столбец А – Дата, столбец В – Доход и т. д.

Для учета доходов и расходов необходимо определиться с категориями доходов и расходов. Откуда доходы: работа мужа, работа жены. Куда расходы: оплата жилья, машина, питание, одежда,  обувь. Конкретизировать доходы и расходы. Доходы: зарплата, гонорар, премия. Расходы: свет, газ, вода, телефон, бензин, обеды и т. д. Заполните таблицу (можно воспользоваться для заполнения формой: Данные → Форма).

Для анализа структуры расходов воспользуйтесь сводной таблицей. Данные → Сводная таблица → укажите В списке или базе данных Microsoft Excel → Далее → Укажите диапазон, в котором находится ваша таблица → Далее → Макет → Перетащите в поле Страница Откуда/Куда, в поле Строка – поле На что, в поле Столбец – поле Кто, в поле Данные – поле Расход (в случае, если в поле Данные получилось значение Количество по полю Расход, то дважды щелкните по этому полю и измените на Сумма) → ОК → Параметры → Проверьте, чтобы галочками были отмечены Общая сумма по строкам, Общая сумма по строкам, Автоформат → ОК → Готово. Просмотрите результат, проанализируйте, расходы, какие расходы занимают большую долю по сравнению с другими.

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

Лабораторная работа №4

Тема: Простые финансовые расчеты (4 часа)

Доходность вексельной сделки

Задание 1. В банке приобретен вексель, по которому через 1,5 года должно быть получено 42 млн. руб. (номинальная стоимость векселя). В момент приобретения цена векселя составляла 27 млн. руб. Определить доходность сделки (размер процентной ставки).

Задание 2. Номинальная стоимость векселя 28,5 тыс. руб. Срок ссуды 2,5 года Цена векселя составляла 15 тыс. руб. Определить размер процентной ставки.

Простые проценты без изменения ставки

Функция БС возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки.

Синтаксис

БС(ставка;кпер;плт;пс;тип)

Ставка – процентная ставка за период.

Кпер – это общее число периодов платежей по аннуитету.

Плт – это выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно плт состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если аргумент опущен, должно быть указано значение аргумента пс.

Пс – это приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.

Тип    — число 0 или 1, обозначающее, когда должна производиться выплата. Если аргумент «тип» опущен, то он полагается равным 0.

Тип	Когда нужно платить
	В конце периода
1	В начале периода

Задание 3. Выдан кредит в сумме 15 млн. руб. с 15.01.2005 г. по 15.03.2005 г. под 15% годовых. Рассчитать сумму погасительного платежа.

Задание 4. Рассчитать сумму погасительного платежа, если сумма кредита на 1.01.2007 г. составила 150000 руб., годовая процентная ставка 17%, дата возврата платежа 31.12.2009 г.

Сложные проценты без изменения ставки

Задание 5. Ссуда в 20000000 руб. дана на полтора года под ставку 17% годовых с ежеквартальным начислением. определить сумму конечного платежа.

Задание 6. Рассчитать будущее значение вклада 10000 руб. через 1, 2, 3, 4, 5 лет при годовых процентных ставках 6%, 8%, 10%, 12%, 14%. Дополнительные поступления и выплаты отсутствуют.

Примечание: Для решения задачи создайте таблицу:

	1	2	3	4	5
6%
8%
10%
12%
14%

в пустых ячейках должна быть формула для вычисления будущей стоимости.

Номинальная и эффективная ставки

Функция СТАВКА возвращает процентную ставку по аннуитету за один период.

Синтаксис

СТАВКА(кпер;плт; пс;бс;тип;предложение)

Кпер – общее число периодов выплат займа или инвестиции.

Плт – выплата, производимая в каждый период и не меняющаяся за все время выплаты займа или инвестиции.

Пс – приведенная (нынешняя) стоимость – общая сумма, равноценная на настоящий момент серии будущих платежей.

Бс – будущая стоимость, которую  нужно достичь после последней выплаты.

Тип – логические значение (0 или 1), обозначающее, должна ли производиться выплата в конце периода (0 или отсутствует) или в начале периода (1).

Предложение – предполагаемая величина ставки, если значение не указано, то оно принимается равным 0,1 (10%).

Задание 7. В долг на полтора года дана сумма 20000 руб. с условием возврата 30000 руб. Вычислить годовую процентную ставку.

Функция ЭФФЕКТ возвращает действующие ежегодные ставки.

Синтаксис

ЭФФЕКТ(номинальная_ставка;кол_пер)

Номинальная ставка – номинальная процентная ставка.

Кол_пер – количество периодов, составляющих год.

Задание 7. В долг на полтора года дана сумма 20000 руб. с условием возврата 30000 руб. Вычислить годовую процентную ставку.

Задание 9.Чему равна эффективная ставка: при номинальной ставке 100% и начислении ежеквартально.

Определение сумм выплат

Функция ПЛТ возвращает сумму периодических платежей для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.

Синтаксис

ПЛТ(ставка;кпер;пс;бс;тип)

Ставка – процентная ставка за период займа.

Кпер – общее число выплат по займу.

Пс — приведенная (нынешняя) стоимость – общая сумма, равноценная на настоящий момент серии будущих платежей.

Бс — будущая стоимость, которую  нужно достичь после последней выплаты.

Тип – логические значение (0 или 1), обозначающее, должна ли производиться выплата в конце периода (0 или отсутствует) или в начале периода (1).

Функция ОСНПЛАТ возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки.

Синтаксис

ОСНПЛАТ(ставка;период;кпер;пс;бс;тип)

Ставка – процентная ставка за период.

Период – период, должен быть в диапазоне от 1 до кпер.

Кпер – общее число периодов выплат инвестиции.

Пс — приведенная (нынешняя) стоимость – общая сумма, равноценная на настоящий момент серии будущих платежей.

Бс — будущая стоимость, которую  нужно достичь после последней выплаты.

Тип – логические значение (0 или 1), обозначающее, должна ли производиться выплата в конце периода (0 или отсутствует) или в начале периода (1).

Функция ПРПЛТ возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм платежей и постоянства процентной ставки.

Синтаксис

ПРПЛТ(ставка;период;кпер;пс;бс;тип)

Ставка – процентная ставка за период займа.

Период – период, для которго нужно определить сумму выплаты, должен быть в диапазоне от 1 до кпер.

Кпер – общее число выплат инвестиции.

Пс — приведенная (нынешняя) стоимость – общая сумма, равноценная на настоящий момент серии будущих платежей.

Бс — будущая стоимость, которую  нужно достичь после последней выплаты.

Тип – логические значение (0 или 1), обозначающее, должна ли производиться выплата в конце периода (0 или отсутствует) или в начале периода (1).

Задание 11. Банк выдал долгосрочный кредит в сумме $ 40 000 на 5 лет под 6 % годовых. Погашение кредита должно производится равными ежегодными выплатами в конце каждого года, включающими погашение основного долга и процентные платежи. Начисление процентов производится раз в год. Составить план погашения займа.

Задание 12. Банк выдал долгосрочный кредит в сумме 140 000 руб. на 5 лет под 17 % годовых. Погашение кредита должно производится равными ежегодными выплатами в конце каждого периода (месяца), включающими погашение основного долга и процентные платежи. Начисление процентов производится раз в месяц. Составить план погашения займа.

Расчеты на основе переменной процентной ставки.

Функция БЗРАСПИС возвращает будущее значение основного капитала после начисления сложных процентов.

Синтаксис

БЗРАСПИС(первичное;план)

Первичное – текущее значение.

План – массив применяемых процентных ставок.

Задача 13: По облигации номиналом 100 000 руб., выпущенной на 6 лет, предусмотрен следующий порядок начисления процентов: в первый год – 10%, в два последующих года – 20%, в оставшиеся три года – 25%. Рассчитаем будущую (наращенную) стоимость облигации по сложной процентной ставке.

Задача 14: По облигации номиналом 10 000 руб., выпущенной на 5 лет, предусмотрен следующий порядок начисления процентов: в первый год – 8%, в два последующих года – 16%, в оставшиеся три года – 20%. Рассчитать будущую (наращенную) стоимость облигации по сложной процентной ставке.

Лабораторная работа № 5 (4 часа)

Тема: Решение оптимизационных задач при помощи инструментов MS Excel

Задача 1. Кондитерская фабрика для производства трех видов карамели А, В и С использует три вида основного сырья: сахарный песок, патоку и фруктовое пюре. Нормы расхода сырья, общее количество сырья, прибыль от реализации указаны в таблице.

Вид сырья	Нормы расхода сырья, т на 1 т карамели	Общее количество сырья, т
	А	В	С
Сахарный песок	0,8	0,5	0,6	800
Патока	0,4	0,4	0,3	600
Фруктовое пюре	—	0,1	0,1	120
Прибыль от реализации 1 т продукции	108	112	126

Составьте математическую модель задачи.

Решение задач симплексным методом в MS Excel

Расположите исходные данные, как показано на рисунке

В ячейках B2:D4 расположены коэффициенты перед неизвестными в условиях. В ячейках Е2:Е4 – формулы: Е2=СУММПРОИЗВ(B2:D2;B6:D6), аналогично Е3, Е4. В ячейку В8 введите целевую функцию, роль неизвестных играет диапазон B6:D6. Затем меню Сервис  Поиск решения  заполните данными как показано на рисунке (ограничения вводятся при помощи кнопки Добавить)

Нажмите Выполнить  ОК.

Задача 2. Найти максимальное значение функции  при условиях

симплексным методом.

Задача 3. Для строительства четырех дорог используется гравий из трех карьеров. Запасы гравия в каждом карьере соответственно равны 120, 280, 160 у. е. Потребности в гравии для строительства каждой из дорог соответственно равны 130, 220, 60, 70 у. е. Известны тарифы перевозок 1 у. е. гравия из каждого из карьеров к каждой из строящихся дорог, которые заданы матрицей . Составить такой план перевозок, при котором потребности каждой из строящихся дорог были удовлетворены при наименьшей общей стоимости перевозок.

Задача 4. На три базы поступил однородный груз в количествах, соответственно равных 140, 180, 160 ед. Этот груз требуется перевезти в пять пунктов назначения в количествах 60, 70, 120, 130, 100 ед. Тарифы перевозок единицы груза с каждого пункта отправления в пункты назначения заданы матрицей . Составить такой план перевозок, при котором потребности в грузе были удовлетворены при наименьшей общей стоимости перевозок.

Лабораторная работа № 6 (2 часа)

Тема: Оптимизация. Подбор параметра

1.  Откройте книгу, переименуйте Лист и в Оптимизация.
2.  Решите задачу:

Дан набор точек (xi, yi): (0, 3), (1, 1), (2, 6), (3, 3), (4, 7). Найти коэффициенты m и b прямой линии y= mx + b, наилучшим образом аппроксимирующей эти данные по критерию наименьших квадратов.

1.  Разместите координаты точек в диапазоне А2:В6.
2.  В ячейках А9 и В9 поместите начальные значения коэффициентов m и b.
3.  В С2:С6 вычислите yi= mxi + b.
4.  В D2:D6  вычислите остатки.
5.  В ячейке D9 вычислите сумму квадратов остатков при помощи функции СУММКВ(диапазон).
6.  Если в меню Сервис нет команды Поиск решения, то установить ее: меню Сервис  Надстройки  Поставьте галочку Поиск решения  ОК.
7.  Выделить ячейку D9  меню Сервис  Поиск решения  минимальное значение  Изменяя ячейки А9; В9  Выполнить  ОК.
8.  Постройте диаграмму График.

1.  Аналогично решите задачу:

Для набора данных (3, 5), (5, 5), (9, -8), (11, -18), (15, -56) найти методом наименьших квадратов коэффициенты параболы ax2 + bx + c.

Подбор параметра – это решение задачи, когда известны окончательные результаты, а исходные нужно найти (подобрать). Примером такой задачи может быть планирование прибыли так, чтобы налог был равным заданной величине.

1.  Решите задачу:

В конце срока, равного 10 годам, на который была положена сумма в $1000 под 5% годовых. Вычислить размер первоначальной суммы, чтобы в конце срока вклада была получена сумма в $2000.

1.  В ячейку А1 введите Сумма вклада, в ячейку В1 — $1000.
2.  В ячейку А2 введите Срок вклада, в ячейку В2 – 10.
3.  В ячейку А3 введите Процентная ставка, в ячейку В3 – 5%.
4.  В ячейку А4 введите Коэффициент прироста суммы, в ячейку В4 – формулу (1+В5)^В4 (используем сложные проценты при начислении годовых).
5.  В ячейку А5 введите Итоговая выплата, в ячейку В5 введите формулу для вычисления итоговой суммы в конце срока вклада В1*В4.
6.  Поставьте курсор на ячейку В5  меню Сервис  Подбор параметра  в строке Установить в ячейке укажите адрес ячейки В5  в строке Значение установите 2000  в строке Изменяя значение ячейки введите значение адрес ячейки В1  ОК  ОК.
7.  Аналогично решите задачу: каким должен быть доход семьи в месяц, чтобы купить квартиру за 300000 через 5 лет. Начальные данные: зарплата жены 3000, зарплата мужа 4500, расход в месяц в среднем 5500.

Подберите значение начисленной заработной платы так, чтобы на руки  человек получил 5000,00, учитывая, что ставка налога 13%, и на иждивении он имеет одного ребенка (на работника необлагаемый минимум 400, на ребенка – 300 руб.). Начальные данные по начислению 4875,21.

Лабораторная работа № 7 (4 часа)

Тема: Применение встроенных функций MS Excel в финансовых расчетах

Анализ инвестиционного проекта

Рассмотрим поток платежей, совершаемых через одинаковые промежутки времени, но сами выплаты могут различаться по величине и по знаку. Задача состоит в том, чтобы по потоку платежей и процентной ставке вычислить современное значение, т. е. привести все платежи к начальному моменту времени и вычислить эффективную процентную ставку операции. Для этого предназначены две функции ЧПС – чистое приведенное значение (NPV – net present value) и ВСД – внутренняя доходность (IRR – internal rate return).

Синтаксис:

ЧПС(ставка;значение1;значение2;…)

Ставка – ставка дисконтирования на один период,

Значение1, значение2,… — от 1 до 29 выплат и поступлений равноотстоящих по времени и происходящих в конце каждого периода.

ВСД(значение;предположение)

Значение – массив или ссылка на ячейки, содержащие числа, по которым нужно вычислить внутреннюю ставку доходности,

Предположение – предполагаемая величина, близкая к результату ВСД, если не указана, принимается равной 10%.

Задача 1. Проект рассчитан на три года и требует начальных инвестиций  вразмере 10 млн. руб. и имеет предполагаемые денежные поступления в размере 3 млн. руб., 4 млн. руб., 7 млн. руб. Рассчитать NPV в предположении ставки 10% и определить IRR для этого проекта.

Задача 2. Предлагаются два инвестиционных проекта, которые характеризуются предполагаемыми потоками платежей:

Год	Проект А	Проект В
0	-100	-100
1	50	20
2	40	40
3	40	50
4	30	60

Сравнить проекты на основе NPV и IRR.