Лабораторная работа №4. Финансово-экономические расчеты в Excel
Цель работы: научиться использовать финансовые функции Excel при проведении сложных финансово-экономических расчетов
Постановка задачи:
Сколько лет потребуется, чтобы платежи размером 1 млн. руб. в конце каждого года достигли значения 10,897 млн. руб., если ставка процента 14,5% годовых.
Ход выполнения работы:
Воспользуемся формулой КПЕР.
Возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.
Синтаксис
КПЕР(ставка;плт;пс;бс;тип)
Более полное описание аргументов функции КПЕР и более подробные сведения о функциях платежей по ссуде см. в разделе, посвященном функции ПС.
Ставка — процентная ставка за период.
Плт — выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно платеж состоит из основного платежа и платежа по процентам и не включает налогов и сборов.
Пс — приведенная к текущему моменту стоимость, т. е. общая сумма, которая на текущий момент равноценна ряду будущих платежей.
Бс — значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если аргумент «бс» опущен, предполагается, что он равен 0 (например, бс для займа равна 0).
Тип — число 0 или 1, обозначающее срок выплаты.
0 или опущен – выплата в конце периода
1 – выплата в начале периода
Так будем иметь=КПЕР(B1;B2;B3),
где В1 – ставка процента, 14,5%;
B2 – величина платежа, 1 млн. руб.;
B3 – будущая стоимость, 10,897 млн. руб.
В итоге получим, что потребуется 7 лет – см. лист «ЛР4».
Выводы: в ходе выполнения работы изучили возможности Excel для решения финансово-экономических задач.
Лабораторная работа №5. Анализ затрат, связанных с выплатой заработной платы. Анализ банков.
Цель работы: Проанализировать заработную плату продавцов магазина «Вечевой» за 1 месяц, чтобы показать специфику ее начисления в зависимости от количества отработанного времени.
Провести анализ банков и выбрать банк с наиболее выгодными условиями кредитования.
Постановка задачи:
В магазине работает 8 продавцов. Имеются следующие данные о количестве отработанного ими времени – см. таблицу 5.1.
Таблица 5.1 – Ведомость для начисления заработной платы
|
№№ |
Фамилия Имя Отчество |
Количество отработанных часов за месяц |
|
1 |
Амплиева Мария Викторовна |
240 |
|
2 |
Аникеева Валерия Валерьевна |
198 |
|
3 |
Барсуков Иван Николаевич |
236 |
|
4 |
Воронков Сергей Григорьевич |
211 |
|
5 |
Козюков Александр Николаевич |
190 |
|
6 |
Прокофьев Михаил Викторович |
150 |
|
7 |
Романова Ирина Владимировна |
240 |
|
8 |
Юдинкова Кристина Николаевна |
240 |
Минимальный размер заработной платы равен 900 руб. В рассматриваемом магазине установлены следующие критерии для начисления заработной платы. Если человек отработал 230-240 часов – по максимуму, то минимальная зарплата умножается на 10. Если 200-230 часов – на 8, 150-200 – на 5.
Поэтому всех продавцов можно разделить на 4 группы, далее для каждой группы можно определить фонд оплаты труда, а также фонд оплаты труда по всей совокупности продавцов.
Ход выполнения работы:
Произведем ввод исходных данных – см. таблицу 5.1. Для каждого продавца в отдельности заполним таблицу соотнесения его к какой-либо группе по величине отработанных часов. Так сотрудник №1 относится к группе №1 (количество отработанных часов от 230-240).
Далее определим количество продавцов в каждой группе. Так для группы «230-240» введем в ячейку D11 формулу =СУММ(D3:D10). В нашем случае получим 4. Аналогичные формулы введем в ячейки E11:G11.
В ячейке L3 представлен МРОТ (минимальный размер оклада труда), равный 900 руб.
В ячейках H3 введем формулу =D3*$L$3*10, которая отражает величину заработной платы сотрудника в группе «230-240». Продлим формулу на диапазон H4:H10.
В ячейках I3 введем формулу =E3*$L$3*8, которая отражает величину заработной платы сотрудника в группе «200-230». Продлим формулу на диапазон I4:I10.
В ячейках J3 введем формулу =F3*$L$3*5, которая отражает величину заработной платы сотрудника в группе «150-200». Продлим формулу на диапазон J4:J10.
В ячейках K3 введем формулу =G3*$L$3, которая отражает величину заработной платы сотрудника в группе «менее 150». Продлим формулу на диапазон K4:K10.
Далее по каждой группе произведем подсчет суммарной величины заработной платы. Так в ячейку H11 введем формулу =СУММ(H3:H10). Аналогично для ячеек I11, J11, K11.
В ячейке L11 введем формулу =СУММ(H11:K11), которая содержит итоговый фонд оплаты труда всех сотрудников.
Так согласно представленной таблице в данном магазине 4 человека отработало по максимуму и будут получать 10 МРОТ, один сотрудник получит 8 МРОТ и 3 сотрудника 5 МРОТ – см. таблицу 5.2.
Таблица 5.2 – Подсчет количества сотрудников по каждой из выделенных групп
|
Группа | |||
|
230-240 |
200-230 |
150-200 |
менее 150 |
|
1 |
0 |
0 |
0 |
|
0 |
0 |
1 |
0 |
|
1 |
0 |
0 |
0 |
|
0 |
1 |
0 |
0 |
|
0 |
0 |
1 |
0 |
|
0 |
0 |
1 |
0 |
|
1 |
0 |
0 |
0 |
|
1 |
0 |
0 |
0 |
|
4 |
1 |
3 |
0 |
Фонд оплаты труда сотрудников в каждой группе и в целом по магазину представлен в таблице 5.3.
Таблица 5.3 – Ведомость начисления заработной платы для продавцов
|
№№ |
Фамилия Имя Отчество |
Зарплата |
МРОТ | |||
|
1 |
Амплиева Мария Викторовна |
9000 |
0 |
0 |
0 |
900 |
|
2 |
Аникеева Валерия Валерьевна |
0 |
0 |
4500 |
0 |
|
|
3 |
Барсуков Иван Николаевич |
9000 |
0 |
0 |
0 |
|
|
4 |
Воронков Сергей Григорьевич |
0 |
7200 |
0 |
0 |
|
|
5 |
Козюков Александр Николаевич |
0 |
0 |
4500 |
0 |
|
|
6 |
Прокофьев Михаил Викторович |
0 |
0 |
4500 |
0 |
|
|
7 |
Романова Ирина Владимировна |
9000 |
0 |
0 |
0 |
|
|
8 |
Юдинкова Кристина Николаевна |
9000 |
0 |
0 |
0 |
|
|
|
Итого |
36000 |
7200 |
13500 |
0 |
56700 |
Общий фонд оплаты труда составит 56 700 руб.
Дополнительное задание представляет собой сравнительный анализ банков (№1, №2 и №4) – см. таблицу 5.4, объем кредита составляет 300 тыс. руб., срок кредитования 4 года. Данные об условиях кредита в каждом из банков представлены в таблице 5.4.
Таблица 5.4 – Сравнительный анализ банков
|
Банк |
Объем кредита, руб. |
Выдача, % |
Плата за оформление, руб. |
Ставка процента, % |
Срок, лет |
Получено, руб. |
Дизажио, руб. |
Выплата (годовая), руб. |
Выплата/Получено, руб. |
|
№1 |
300 000 |
95 |
300 |
12 |
4 |
284 700 |
15 000 |
93 733,04 |
0,33 |
|
№2 |
300 000 |
96 |
250 |
13,5 |
4 |
287 750 |
12 000 |
97 746,72 |
0,34 |
|
№4 |
300 000 |
96 |
300 |
14 |
4 |
287 700 |
12 000 |
110 364,36 |
0,38 |
Используются следующие формулы для вычислений:
Дизажио = Объем кредита * (1 – Выдача)
Получено – какую сумму вы получите фактически (за вычетом дизажио и платой за оформление)
Выплата – сумма годового платежа, который будет включать в себя как погашение основного долга, так и процентные платежи – будем вычислять с помощью функционала Excel Поиск решения.
Выплата/Получено – отношение годовой выплаты к полученной сумме.
В таблице 5.5 представлен план погашения кредита в банке №2
Таблица 5.5 – План погашения кредита в банке №2
|
Год |
Погашение долга |
Выплата по процентам |
Остаток долга |
|
1 |
58900,47 |
38846,25 |
228849,53 |
|
2 |
66852,03 |
30894,69 |
161997,51 |
|
3 |
75877,05 |
21869,66 |
86120,45 |
|
4 |
86120,45 |
11626,26 |
0,00 |
|
|
Итого: |
103236,9 |
|
Для расчетов были использованы следующие формулы – см. таблицу 5.6.
Таблица 5.6 – Формулы для расчета плана погашения кредита в банке №2
|
Год |
Погашение долга |
Выплата по процентам |
Остаток долга |
|
1 |
=$I$4-C10 |
=G4*E4% |
=G4-B10 |
|
2 |
=$I$4-C11 |
=D10*E4% |
=D10-B11 |
|
3 |
=$I$4-C12 |
=D11*$E$4% |
=D11-B12 |
|
4 |
=$I$4-C13 |
=D12*$E$4% |
0 |
|
|
Итого: |
=СУММ(C10:C13) |
|
Аналогичным образом, рассчитываются планы погашения кредитов в банке №1 и банке №4 – см. таблицы 5.7-5.10.
Таблица 5.7 – План погашения кредита в банке №1
|
Год |
Погашение долга |
Выплата по процентам |
Остаток долга |
|
1 |
59569,04 |
34164,00 |
225130,96 |
|
2 |
66717,33 |
27015,71 |
158413,63 |
|
3 |
74723,41 |
19009,64 |
83690,22 |
|
4 |
83690,22 |
10042,83 |
0,00 |
|
|
Итого: |
90232,18 |
|
Таблица 5.8 – Формулы для расчета плана погашения кредита в банке №1
|
Год |
Погашение долга |
Выплата по процентам |
Остаток долга |
|
1 |
=$I$3-C19 |
=G3*$E$3% |
=G3-B19 |
|
2 |
=$I$3-C20 |
=D19*$E$3% |
=D19-B20 |
|
3 |
=$I$3-C21 |
=D20*$E$3% |
=D20-B21 |
|
4 |
=$I$3-C22 |
=D21*$E$3% |
0 |
|
|
Итого: |
=СУММ(C19:C22) |
|
Таблица 5.9 – План погашения кредита в банке №4
|
Год |
Погашение долга |
Выплата по процентам |
Остаток долга |
|
1 |
70086,36 |
40278,00 |
217613,64 |
|
2 |
63267,13 |
30465,91 |
154346,50 |
|
3 |
72124,53 |
21608,51 |
82221,97 |
|
4 |
82221,97 |
11511,08 |
0,00 |
|
|
Итого: |
103863,5 |
|
Таблица 5.10 – Формулы для расчета плана погашения кредита в банке №4
|
Год |
Погашение долга |
Выплата по процентам |
Остаток долга |
|
1 |
=$I$5-C28 |
=G5*$E$5% |
=G5-B28 |
|
2 |
=$I$3-C29 |
=D28*$E$5% |
=D28-B29 |
|
3 |
=$I$3-C30 |
=D29*$E$5% |
=D29-B30 |
|
4 |
=$I$3-C31 |
=D30*$E$5% |
=D30-B31 |
|
|
Итого: |
=СУММ(C28:C31) |
|
Для вычисления величины ежегодного платежа по кредиту (для каждого банка) в отдельности был выполнен расчет с помощью Поиск решения. Так, например, для банка №4 мы использовали Поиск решения со следующими параметрами – см. рис. ниже. То есть мы искали такое значение величины ежегодных платежей (ячейка I5), чтобы значение D31 (остаток на конец периода кредитования) стало равным нулю.
Далее следует провести анализ выплат по трем банкам, который позволит выбрать банк с лучшими условиями кредитования для предпринимателя. Так не сложно заметить, что более выгодные условия представляет банк №1 – проценты за 4 года составят 90232,18 руб. (тогда как в банке №2 будет 103236,90, а в банке №4 – 103863,50 руб.). Если же будет принято решение взять кредит в банке №2, то потери предпринимателя составят 9954,68 руб. Действительно, разница между суммой процентных денег составит 103236,90 – 90232,18 = 13004,68 руб., плата за оформление кредита в банке №2 меньше, чем в банке №1 на 50 руб., а дизажио в банке №2 меньше на 3000, чем в банке №1).
Выводы: Провели анализ фонда оплаты труда сотрудников магазина, изучили специфику начисления заработной платы в зависимости от количества отработанного времени. Провели анализ банков и выбрали банк с наиболее выгодными условиями кредитования.