Электронное учебное пособие «Финансово-экономический анализ деятельности предприятия»

«Финансово-экономический анализ деятельности предприятия»

в системе электронной таблицы Microsoft Excel

ЕАДК, преподаватель: Неверова И.Ю.

Цель занятия : познакомиться с финансово-экономическим анализом деятельности предприятия

План :

• Технология вычислений в MS Excel Модели и методы финансово-экономических расчётов Выполнение расчётов с применением финансовых функций
• Технология вычислений в MS Excel
• Модели и методы финансово-экономических расчётов
• Выполнение расчётов с применением финансовых функций

• Описание аргументов финансовых функций Функции для расчёта операций по кредитам, ссудам и займам Функции для определения срока платежа и процентной ставки Функции для расчёта периодических платежей
• Описание аргументов финансовых функций Функции для расчёта операций по кредитам, ссудам и займам Функции для определения срока платежа и процентной ставки Функции для расчёта периодических платежей
• Описание аргументов финансовых функций
• Функции для расчёта операций по кредитам, ссудам и займам
• Функции для определения срока платежа и процентной ставки
• Функции для расчёта периодических платежей

Литература : Э.В. Фуфаев. Пакеты прикладных программ, стр. 127-271

1. Технология вычислений в Excel

• Табличные процессоры (электронные таблицы) представляют собой целое направление прикладных программных систем, предназначенных для автоматизации расчётно-аналитических задач ( SuperCalk , Lotus , QuatroPro , Excel ).
• Область применения ЭТ широка: от расчёта заработной платы до использования в научных исследованиях. Табличный процессор Excel имеет достаточное число встроенных функций, необходимых для выполнения расчётов финансовой деятельности предприятий и фирм.
• Принцип работы табличного процессора основан на использовании методов алгебраического представления математических действий и способов компьютерной обработки информации.
• При вводе формул в ячейку соблюдаются правила алгебраических выражений. Перед формулой требуется ввести знак равенства.
• Табличный процессор Excel содержит встроенные функции: математические, тригонометрические, статистические, логические, финансовые и др.

Основные элементы ЭТ Excel

• Книга – документ (файл), содержащий результаты работы с программой.

• Лист – это сетка таблицы, имеющая в книге своё название или номер. По умолчанию книга содержит 3 листа, добавление листов производится командами меню Вставка – Лист . Размер одного листа составляет 65 536 строк и 256 столбцов. Число листов в одной книге ограничивается ресурсом компьютера.
• Ячейка – часть таблицы, формируемая пересечением столбца со строкой. Каждая ячейка имеет имя, состоящее из имени (буквы) столбца и имени (цифры) строки. Начиная с 27 столбца (в латинском алфавите 26 букв) используется двойное обозначение (AA, AB , AC ). В ячейках могут располагаться текстовые и числовые данные, или числовые результаты, полученные через формулы или функции. При вводе в ячейку формулы (функции) сначала ставиться знак равенства (=), в противном случае они будут распознаваться программой как текст.

При выполнении расчёта его результаты и исходные данные могут вводиться в ячейки в различных форматах. Команды меню: Формат - Ячейки – вкладка Число.

• Общий - для ввода и вывода текстовых или числовых данных ,
• Числовой – для ввода и вывода положительных и отрицательных чисел с определённым количеством знаков после запятой ,
• Денежный – задаёт обозначение денежной единицы ,
• Финансовый – аналогично денежному, для выравнивания денежных величин по разделителю целой и дробной части,
• Дата – выбор вида обозначения даты ,
• Время – служит для обозначения времени ,
• Процентный – для чисел в виде процентов ,
• Дробный – для вывода результатов расчёта в дробном виде ,
• Экспоненциальный - для вывода результатов расчёта в экспоненциальном виде ,
• Текстовый – только для текстовых данных, с числами в этом формате никакие действия не производятся ,
• Дополнительный – для ввода данных типа почтового индекса или телефонов ,
• Все форматы – данные любого типа.

Мастер функций можно запустить командой меню: Вставка – Функция.

Существуют следующие способы для вычисления и обработки данных с применением встроенных функций:

• Непосредственный ввод функции в ячейку;
• Применение мастера функций;
• Применение мастера автоматического суммирования данных.
• Формат функции: = Имя функции (Имена ячеек – аргументы или числовые данные) .
• Аргументы перечисляются через точку с запятой, а числовой ряд через двоеточие.

Порядок выполнения расчётов

• подготовить на рабочем листе в отдельных ячейках значения основных аргументов функции;
• в отдельной ячейке вызвать « Мастер функций» с помощью команд: Вставка – Функция или кнопкой Функция ;
• в диалоговом окне выбрать категорию функций Финансовые, а в списке – имя функции или ввести имя функции в окно поиска; в нижней части диалогового окна выбранной категории функции имеется справка о назначении функции;
• при выборе финансовой функции появляется диалоговое окно для ввода аргументов; в поля ввода диалогового окна можно вводить ссылки на ячейки с данными или сами значения аргументов; в нижней части диалогового окна, при установке курсора ввода в строку, появляется справка о вводимом аргументе;
• после ввода аргументов нажать кнопку ОК или клавишу Enter;
• возможен также непосредственный ввод формулы, содержащей имена и параметры встроенных финансовых функций, без вызова мастера функций, при этом формулу начинают со знака равно.

Специфика задания значений аргументов финансовых функций

• Все аргументы (тип аргумента), означающие расходы денежных средств (ежегодные платежи), представляются отрицательными числами, а аргументы, означающие поступления (дивиденды) – положительными.
• Все даты как аргументы функции имеют числовой формат представления (дата берётся из ячейки в виде ссылки). Можно также использовать встроенную функцию ДАТА (нажать кнопку вызова, находящуюся перед полем ввода, выбрать категорию Дата , заполнить экран ввода даты по строкам ввода).
• Для аргументов типа Логические, возможен непосредственный ввод констант Истина или Ложь, или использовать встроенные функции.
• При непосредственном вводе формулы в ячейку необходимо следить за тем, чтобы каждый аргумент находился строго на своём месте. Если какие-то аргументы не используются, вместо них следует поставить соответствующее число разделительных знаков. Если это последние аргументы, то можно разделительные знаки опустить (аргументы Тип и Базис). Например. = ПЗ (12%;12;;500000)

2. Модели и методы финансово-экономических расчётов

Методы финансово-экономических расчётов

Количественный финансовый анализ предполагает применение унифицированных моделей и методов расчёта финансовых показателей.

Условно методы финансового расчёта подразделяют на две категории:

• Базовые методы финансовых расчётов :
• Базовые методы финансовых расчётов :

• Определение простых и сложных процентов, являющиеся основой операций, связанных с наращиванием или дисконтированием платежей (векселя до истечения срока); Расчёт последовательностей (потоков) платежей применительно к различным видам финансовых рент.
• Определение простых и сложных процентов, являющиеся основой операций, связанных с наращиванием или дисконтированием платежей (векселя до истечения срока); Расчёт последовательностей (потоков) платежей применительно к различным видам финансовых рент.
• Определение простых и сложных процентов, являющиеся основой операций, связанных с наращиванием или дисконтированием платежей (векселя до истечения срока);
• Расчёт последовательностей (потоков) платежей применительно к различным видам финансовых рент.

• Прикладные методы финансовых расчётов :
• Прикладные методы финансовых расчётов :

• Планирование и оценка эффективности финансово-кредитных операций; Расчёт страховых аннуитетов (ежегодный платёж); Планирование погашения долгосрочной задолженности; Планирование погашения ипотечных ссуд и потребительских кредитов; Финансовые расчёты по ценным бумагам (долгосрочная ссуда); Лизинговые, факторинговые и форфейтинговые банковские операции; Планирование и анализ инвестиционных проектов и др.
• Планирование и оценка эффективности финансово-кредитных операций; Расчёт страховых аннуитетов (ежегодный платёж); Планирование погашения долгосрочной задолженности; Планирование погашения ипотечных ссуд и потребительских кредитов; Финансовые расчёты по ценным бумагам (долгосрочная ссуда); Лизинговые, факторинговые и форфейтинговые банковские операции; Планирование и анализ инвестиционных проектов и др.
• Планирование и оценка эффективности финансово-кредитных операций;
• Расчёт страховых аннуитетов (ежегодный платёж);
• Планирование погашения долгосрочной задолженности;
• Планирование погашения ипотечных ссуд и потребительских кредитов;
• Финансовые расчёты по ценным бумагам (долгосрочная ссуда);
• Лизинговые, факторинговые и форфейтинговые банковские операции;
• Планирование и анализ инвестиционных проектов и др.

Финансовые расчёты

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

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

Процентные ставки

В финансовых расчётах различают следующие виды процентных ставок:

• В зависимости от базы для начисления процентов:

• простые сложные
• простые
• сложные

• По принципу расчёта:

• наращения ( декурсивную) учётную ( антисипативную ).
• наращения ( декурсивную)
• учётную ( антисипативную ).

• По постоянству значения в течение действия контракта:

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

Методы начисления процентов

Метод

Общее число периодов выплат

Ежегодный

Процентная ставка за период начисления, %

n

Полугодовой

2n

k

Квартальный

Месячный

k/2

4n

12n

k/4

Ежедневный

k/12

365n

k/365

3. Выполнение расчётов с применением финансовых функций

Описание аргументов финансовых функций:

• БЗ (БС) – будущая стоимость фиксированных периодических выплат или единой суммы (будущая стоимость инвестиций).
• Норма (Ставка) – процентная ставка или процентная норма за период, числовое значение которого должно быть больше -1.
• Кпер (Число_периодов) – общее число периодов выплат, по истечении которых требуется определить объём имеющихся средств, числовое значение которых должно быть больше нуля.
• Выплата – периодическая фиксированная выплата, производимая в каждый период.
• НЗ, ТС – начальное значение вклада или займа, текущая стоимость, или общая сумма всех будущих платежей с настоящего момента (постоянная величина, имеющаяся на счёте).
• Тип - если 1, то в начале периода платежей; если 0, то в конце; или по умолчанию равное 0.
• Период – период, для которого требуется найти выплату.
• Предположение – предполагаемое значение процентной ставки, по умолчанию равное 0,1.

Финансовые функции

I. Функции для расчёта операций по кредитам, ссудам и займам. Эти расчёты основаны на концепции временной стоимости денег. Эта группа функций предназначена для следующих расчётов:

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

1. Функции для расчёта операций по кредитам, ссудам и займам

• Функция БЗ (БС) – позволяет рассчитать будущую или наращенную стоимость серии фиксированных периодических платежей, а так же будущую стоимость текущего значения вклада или займа при постоянной процентной ставке: =БС (Норма; Число_периодов; Выплата; НЗ; Тип)
• Функция ПЗ (ПС) – предназначена для расчёта текущей стоимости единой суммы вклада (займа) и будущих фиксированных платежей (обратная БЗ): = ПС (Норма; Кпер; Выплата; БС; Тип)
• Функция НПЗ – предназначена для вычислений чистой текущей стоимости периодических платежей переменной величины как суммы ожидаемых доходов и расходов: = НПЗ (Норма; Сумма1; Сумма2; …; СуммаN)

Решение задач

Задача 1 : Рассчитать, какая сумма окажется на счете, если 27 000 рублей заложены на 33 года под 13,5 % годовых. Проценты начисляются каждые полгода. (=БЗ(13,5/2; 33*2;-27000)=2012 070 тыс)

Задача 2: Фирме потребуется 5 000 000 рублей через 12 лет. Определить сумму единого текущего вклада, которую необходимо положить на депозит, если процентная ставка по нему составляет 12% в год. (=ПЗ(12%;12;;5000000)=-1 283 380)

Задача 3: Инвестиции в проект к концу первого года его реализации составят 10 000 рублей. В последующие три года ожидаются годовые доходы по проекту 3 000, 4 200 и 6 800 рублей. Издержки привлечения капитала 10%. Рассчитать чистую текущую стоимость проекта.(Так как инвестиция 10 000 рублей относится не к начальному моменту, на который производится расчет, то это значение следует включить в список аргументов (=НПЗ(10%;-10000;3000;4200;6800)=1188,44)

Функции для расчёта операций по кредитам, ссудам и займам

4. Функция БЗРАСПИС используется, если процентная ставка меняется с течением времени.

=БЗРАСПИС(инвестиция, {ставка1; ставка2;…;ставка N})

Задача 1. По облигации номиналом 100 000 рублей, выпущенной на шесть лет, предусмотрен следующий порядок начисления процентов: первый год 10%, два последующих года 20%, оставшиеся три года 25%. Рассчитать будущую стоимость облигации по сложной процентной ставке. (В ячейки А1:А6 ввести процентные ставки БЗРАСПИС(100000;А1:А6)=309 380)

Задача 2. Исходя из плана начисления процентов, приведенного в предыдущей задаче, рассчитать номинал облигации, если известно, что ее будущая стоимость составит 1 546 880 рублей. (Использование команды Сервис – Подбор параметра в В1 БЗРАСПИС(В2; А1:А6), в ней же выполнить команду. Ответ: 500 000)

Функции для расчёта операций по кредитам, ссудам и займам

5. Функция ЧИСТНЗ . Позволяет рассчитывать чистую текущую стоимость нерегулярных переменных выплат и поступлений.

Задача. Инвестиция размером 10 млн. рублей от 1 июля 1998 г., принесет доходы: 2750 тыс. рублей 15 сентября 1998 г., 4250 тыс. рублей 1 ноября 1998 г., 5250 тыс. рублей 1 января 1999 г. Норма дисконтирования 9%. Определить чистую текущую стоимость инвестиции на 1 июля 1998 г. и на 1 июля 1999 г. (Поместим в В1:Е1 даты выплат и поступлений, а в В2:Е2 – суммы. Начальный платеж в В2=-10 000 в А1 1.07.1998 , тогда ЧИСТНЗ(9%,В2:Е2,В1:Е1)=1856,25 тыс. ЧИСТНЗ(9%,А2:Е1)=1702,99 тыс. руб.)

2. Функции для определения срока платежа и процентной ставки

Функции этой группы позволяют находить величины, расчёт которых весьма затруднён, если выполняется в ручную:

• Общее число периодов постоянных выплат, необходимых для достижения заданного будущего значения, и число периодов, через которое начальная сумма займа (вклада) достигнет заданного значения – функция КПЕР . Значение постоянной процентной ставки за один период для серии фиксированных периодических платежей и значение процентной ставки по вкладу или займу – функция НОРМА .
• Общее число периодов постоянных выплат, необходимых для достижения заданного будущего значения, и число периодов, через которое начальная сумма займа (вклада) достигнет заданного значения – функция КПЕР .
• Значение постоянной процентной ставки за один период для серии фиксированных периодических платежей и значение процентной ставки по вкладу или займу – функция НОРМА .

Функции для определения срока платежа и процентной ставки

• Функция КПЕР – предназначена для вычисления общего числа периодов выплат, как для единой суммы вклада, так и для периодических платежей на основе единой процентной ставки: = КПЕР (Норма; Выплата; НЗ; БС; Тип).
• Функция Норма (СТАВКА) - определяет значение процентной ставки за один расчетный период: = НОРМА(кпер; выплата; нз; бс; тип; предположение).

Решение задач

Задача1: Рассчитать, через сколько лет вклад размером 1 млн. рублей достигнет величины 1 млрд. рублей, если годовая процентная ставка по вкладу 16,79% и начисление процентов производится ежеквартально. (=КПЕР(16,79/4;;-1;1000)=168, т.е 168/4=42 года)

Задача2: Предположим, что компании «Ежик» потребуется 100 000 тыс. рублей через два года. Компания готова вложить 5 000 тыс. рублей сразу, а затем вкладывать по 2 500 тыс. рублей каждый месяц. Каким должен быть процент на инвестированные средства, чтобы получить необходимую сумму в конце второго года? (=НОРМА(24;-2500;-5000;100000)=3,28%).

Функции для определения срока платежа и процентной ставки

3. Функция ЭФФЕКТ предназначена для вычисления действующих (эффективных) ежегодных процентных ставок, если заданы номинальная годовая процентная ставка и число расчетных периодов за год. Синтаксис: ЭФФЕКТ(номинальная_ставка кол_пер)

Задача 1. Заем в 1000 тыс. рублей взят под номинальную процентную ставку 12% на три года. Весь заем и начисленные на него проценты должны быть выплачены единой суммой в конце срока займа. Какая сумма будет выплачена при начислении процентов:

а) полугодовом; б) ежеквартальном; в) месячном; г) ежедневном. (подсчитать в ячейках А1:А4)

а) ЭФФЕКТ(12%,2)=0,1236 б)ЭФФЕКТ(12%,4)=0,1255

в) ЭФФЕКТ(12%,12)=0,1268 г) ЭФФЕКТ(12%,365)=0,1275, затем в ячеку В1 введем БЗ(А1;3;;-1000) и получим 1418,52, 1425,76, 1430,77, 1433,24 тыс. рублей)

4. Функция НОМИНАЛ предназначена для вычисления номинальной годовой процентной ставки при известных эффективной ставке и числе расчетных периодов за года.

Задача 2. Эффективная ставка составляет 28%; начисление процентов производится ежемесячно. Рассчитать номинальную ставку. (НОМИНАЛ(28;,12)=0,2494 или 24,94%)

3. Функции для расчёта периодических платежей

Позволяют вычислять следующие параметры, связанные с периодическими выплатами:

• Периодические платежи, осуществляемые на основе постоянной процентной ставки и не меняющиеся за всё время расчёта ( ППЛАТ );
• Платежи по процентам за конкретный период ( ПЛПРОЦ );
• Сумму платежей по процентам за несколько периодов подряд ( ОБЩПЛАТ );
• Основные платежи по займу (за вычетом процентов) за конкретный период ( ОСНПЛАТ );
• Сумму основных платежей за несколько периодов подряд ( ОБЩДОХОД ).

Функции для расчёта периодических платежей

• Функция ППЛАТ предназначена для вычисления размера выплаты за один расчетный период на основе фиксированных периодических выплат и постоянной процентной ставки: = ППЛАТ(норма; кпер; нз; бс; тип)
• Функция ПЛПРОЦ вычисляет платежи по процентам за данный расчетный период на основе периодических постоянных выплат и постоянной процентной ставки: = ПЛПРОЦ(норма; период; кпер; тс; бс; тип)
• Функция ОСНПЛАТ вычисляет основной платеж по займу, который погашается равными частями в конце или начале каждого расчетного периода в течение заданного срока: = ОСНПЛАТ (норма; период; кпер; тс; бс; тип)

Функции для расчёта периодических платежей

Задача1: Требуется накопить 4000 тыс. рублей за три года, откладывая постоянную сумму в конце каждого месяца. Какой должна быть эта сумма, если процентная ставка по вкладу составляет 12% годовых.

(=ППЛАТ(12%/12;12*3;;4000)=-92,86 тыс. р)

Задача2:

А) Вычислить платежи по процентам за первый месяц от трехгодичного займа в 800 тыс. рублей из расчета 10% годовых. (=ПЛПРОЦ(10%/12;1;12*3;800)=-6,667 тыс. рублей)

Б) За счет ежегодных отчислений в течение шести лет был сформирован фонд в 5000 тыс. рублей. Определить, какой доход принесли вложения владельцу за последний год, если годовая процентная ставка составляла 17,5%. (=ПЛПРОЦ(17,5%;6;6;;5000)=-536,27 тыс. рублей)

Задача3 : Рассчитать сумму основного платежа за первый год, если взят займ в размере 70 000 на три года под 17% годовых. (=ОСНПЛАТ(17%;1;3;-70 000) =-19 780,16)

Функции для расчёта периодических платежей

4. Функция ОБЩПЛАТ вычисляет накопленный доход (сумму платежей по процентам) по займу, который погашается равными частями в конце или начале каждого расчетного периода, между двумя периодами выплат. Синтаксис:

=ОБЩПЛАТ(ставка; кол_пер;нз;нач_период;кон_период;тип)

Задача . Заем под недвижимость сделан на следующих условиях: процентная ставка 9% годовых; срок 30 лет, размер ссуды 125000 рублей, проценты начисляются ежемесячно. Найти сумму выплат по процентам за второй год и первый месяц займа. а) за второй год: =ОБЩПЛАТ(9%/12;30*12;125000;13;24;0)=-11135,23 б) за первый месяц ОБЩПЛАТ(9%/12;30*12;125000;1;1;0)= -937,5

5. Функция ОСНПЛАТ вычисляют основной платеж (выплату задолженности) по займу, который погашается равными частями в конце или начале каждого расчетного периода в течение заданного срока. Синтаксис: =ОСНПЛАТ(норма;период;кпер;тс;бс;тип )

Домашнее задание

Заполнить электронную форму по ссылке:

https://docs.google.com/forms/d/1l2SRLs2aV6qQg5OChd6cPG3QNDm1ocGjRS_IXCQDvbk/viewform