Сделать финансовый анализ состояния предприятия в Excel за 5 минут вам поможет программа QFinAnalysis.

Из данной статьи вы узнаете:

• О задачах, которые вы сможете решить с помощью программы;
• О возможностях финансового анализа в QFinAnalysis;
• Как работать с программой;

А также сможете скачать Excel файл с примером расчета финансового состояния с помощью QFinAnalysis.

С помощью QFinAnalysis вы сможете решить следующие задачи финансового анализа предприятия :

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

Программа QFinAnalysis поможет вам сделать:

• Диагностику вероятности банкротства - 23 современные методики расчета вероятности банкротства , включая методики с использованием аппарата искусственного интеллекта;
• Анализ финансовых показателей – около 20 финансовых коэффициентов (ликвидности, деловой активности, фин.устойчивости, рентабельности и др.);
• Оценку платежеспособности компании – регламентированный анализ платежеспособности по методике Правительства РФ ;
• Расчет чистых активов предприятия;

Анализ ликвидности баланса предприятия ;

Горизонтальный вертикальный анализ баланса компании;

Прогнозирование банкротства предприятия по наиболее популярным западным методикам (Альтмана, Бивера, Таффлера и др.) и адаптированным отечественным методикам (ИГЭА, Казанской школы, Зайцевой, Савицкой и др.).

Как работать с программой?

Открыв QFinAnalysis, мы попадаем в лист с меню, из которого мы можем перейти в любой лист с программой. нажимая на ссылки соответствующих разделов:

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

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

Баланс предприятия в старой форме вводим в лист "Баланс":

Баланс в новой форме вводим в лист "Новый баланс":

Переходим по страницам и получаем:

1. Анализ динамики баланса и доли по статьям в листе "аналит. баланс"

2. Коэффициенты анализа предприятия для:

• Оценки финансовой устойчивости;
• Оценки ликвидности;
• Оценки рентабельности;
• Оценки деловой активности;

– лист "коэф.":

3. Оценку ликвидности - лист "анализ ликв.":

4. Модели множественного дискриминантного анализа (MDA-модели) оценки риска банкротства предприятия:

• Двухфакторная модель Альтмана
• Двухфакторная модель Федотовой
• Пятифакторная модель Альтмана
• Модифицированная модель Альтмана для России
• Четырехфакторная модель Таффлера
• Четырех факторная модель Лиса
• Четырехфакторная модель Спрингейта
• Четырех факторная модель ИГЭА
• Модель Сайфулина-Кадыкова
• Модель Пареной-Долголаева
• Модель Республики Беларусь
• Модель Савицкой

5. Логистические модели (Logit-модели) оценки риска банкротства предприятия показывают вероятность банкротства в процентах

• Модель Альтмана-Сабато (2007)
• Модель Лина-Пьессе (2004)
• Модель ДжуХа-Техонга (2000)
• Модель ДжуХа-Техонга (2000)
• Модель Грузчинского (2003)
• Модель Грузчинского (2003)

6. Экспертная модель "Зайцевой", в которой каждому показателю можно задавать вес вручную, - лист "экспертн. модели"

• Модель Казанского Государственного Технологического Университета (КГТУ) для предприятий промышленности (которая позволяет определить класс кредитоспособности);
• Модель Бивера;

8. Модель правительства РФ (оценка платежеспособности и структуры баланса предприятия):

9. Расчет чистых активов предприятия - лист "ЧА":

• Novo Forecast Lite - автоматический расчет прогноза в Excel .
• 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
• Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

• Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

Финансовый анализ в Excel

Финансовый анализ в Excel Назначение.

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

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

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

Виды финансовых функций.

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

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

Функции для анализа обыкновенных аннуитетов.

Функции для анализа эффективности инвестиционных проектов.

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

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

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

балансовая стоимость актива на начало периода эксплу­атации;

остаточная (ликвидационная) стоимость актива;

срок эксплуатации (полезной жизни) актива;

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

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

Название метода амортизации	Функция Excel, реализующая метод и ее синтаксис
1. Равномерный (линейный)	АПЛ (начальная стоимость актива; ликвидационная стоимость актива; время эксплуатации)
2. По сумме лет полезного использования	АСЧ (начальная стоимость актива; ликвидационная стоимость актива; время эксплуатации; период для расчета величины отчислений)
3. Метод двойного списания (ускоренная амортизация)	ДДОБ (начальная стоимость актива; ликвидационная стоимость актива; время эксплуатации; понижающий коэффициент) Если коэффициент не задан, то полагается, что он равен 2
4. Метод уменьшаемого остатка	ФУО (начальная стоимость актива; ликвидационная стоимость актива; время эксплуатации; период для расчета величины отчислений; кол-во месяцев эксплуатации в первом году)

Основные методы амортизации активов и функции их расчетов в MS Excel

Функция АПЛ() вычисляет размер ежегодных отчислений при использовании метода равномерного списания износа актива.

Функции АСЧ(), ФУО(), ДДОБ() реализуют применение методов ускоренной амортизации , которые позволяют списать основную часть стоимости активов в начальные периоды их эксплуатации, когда они используются с максимальной интенсивностью, создавая тем самым резерв для их своевременной замены в случае физического износа или морального устаревания. Методы ускоренной амортизации позволяют также снизить налогооблагаемую базу предприятия.

В качестве описания практического применения вышеперечисленных функций приведем следующий пример.

Предположим, для обеспечения производственной деятельности своего предприятия Вы приобрели некоторое оборудование. На момент ввода данного актива в эксплуатацию его первоначальная стоимость составила 10000 тыс. руб. Полезный срок эксплуатации оборудования составляет 6 лет. Любой вид долгосрочного актива (функционирующего более 1 года) имеет такие свойства, как физическое и моральное устаревание. Таким образом, в конце срока эксплуатации (срока полезной службы) данного актива его ликвидационная стоимость предположительно составит 1000 тыс.руб.

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

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

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

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

Введите в любую ячейку созданной таблицы формулу для расчета суммы амортизации при равномерном списании износа - функцию АПЛ().

Функции в Excel вводятся с помощью «Мастера функций» , который вызывается по команде Вставка/ Функция… или путем нажатия кнопки Стандартной панели инструментов. Для ввода функции в ячейку, выберите категориюФинансовые в появившемся окне из списка слева и необходимую функцию из соответствующего списка справа.

Нажмите кнопку ОК. На рабочем листе появится диалоговое окно с названием выбранной функции в строке формул и описанием обязательных и необязательных аргументов. После имени каждой функции в скобках задаются аргументы. Если функция не использует аргументов, то за ее именем следуют пустые скобки (), без пробела между ними. Аргументы отделяются точкой с запятой (;) . В качестве элемента формулы может выступать адрес ячейки в виде абсолютной или относительной ссылки, т.е. в вычислении участвует содержимое данной ячейки.

Синтаксис всех функций можно просмотреть там же в мастере функций. Синтаксис функций для расчет амортизационных отчислений приведен в таблице “Основные методы амортизации активов и функции их расчетов в MS Excel”.

При вычислении суммы амортизации по методу равномерного списания амортизации используется соотношение вида:

Для любого i -го периода срока эксплуатации актива сумма начисления амортизации АПЛ i одинакова.

Диапазон ячеек С10:С15 содержит формулу расчета амортизации в соответствии с синтаксисом Excel:

=АПЛ(10000;1000;6) (Возвращаемый результат: 1500,00).

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

Функция АСЧ() использует метод суммы лет при начислении величины амортизации, рассчитываемой как отношение остатка срока эксплуатации актива к сумме лет, умноженная на разность начальной и остаточной стоимости. Алгебраически формула расчета амортизации актива за конкретный период выглядит следующим образом:

где: начальная стоимость – начальная стоимость актива;

ликвидационная стоимость – ликвидационная стоимость актива;

срок – срок эксплуатации актива;

период – порядковый номер периода начисления амортизации;

Таким образом, за два последовательных периода (например, за 1-й и 2-й) сумма амортизации составит соответственно:

= АСЧ(10000;1000;6;1) (Результат: 2571,43)

= АСЧ(10000;1000;6;2) (Результат: 2142,86)

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

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

где: накопленная амортизация – накопленная амортизация за предшествующие периоды эксплуатации актива;

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

,

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

Особым случаем в использовании функции ДОБ() является расчет амортизации за первый и последний периоды эксплуатации актива.

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

Для последнего периода функция ДОБ() использует иную формулу:

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

Таким образом, за 1-й и 2-й периоды амортизация составит:

= ФУО(10000;1000;6;1) (Результат: 3190,00)

= ФУО(10000;1000;6;2) (Результат: 2172,39)

Метод двойного списания основан на применении ускоренной нормы ежегодной амортизации. В качестве последней обычно принимается норма, используемая при равномерном списании, умноженная на некоторый коэффициент. В Excel данный метод реализует функция ДДОБ(), позволяющая использовать в качестве коэффициента любое положительное число. По умолчанию значение коэффициента принимается равным 2.

Сумма износа за i -й период определяется из следующего соотношения:

где: коэффицент – коэффициент, задающий норму снижения балансовой стоимости (ускорения начисления амортизации).

При исчислении износа используя функцию ДДОБ() величина амортизации максимальна в первый период и снижается в последующие периоды.

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

Для нашего примера амортизация за 1-й и 2-й периоды составит:

= ДДОБ(10000;1000;6;1) (Результат: 3333,33)

= ДДОБ(10000;1000;6;2) (Результат: 2222,22)

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

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

Синтаксис функции:

=ПУО (начальная стоимость; ликвидационная стоимость; период амортизации; начальный период; конечный период; коэффициент; без переключения),

где: начальный период – начальный период, для которого вычисляется амортизация;

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

Начальный и конечный период должны быть выражены в тех же единицах, что и срок эксплуатации актива.

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

Н
апример:

а) за период с 6 по 12 месяцы эксплуатации:

=ПУО(10000;1000;6*12;6;12) (Результат: 1313,28)

где: 6 лет * 12 месяцев – общее количество месяцев эксплуатации данного актива;

6,12 – порядковые номера периода начисления износа.

б) с 1 по 200 день эксплуатации (с точным числом дней в году):

=ПУО(10000;1000;6*365;1;200) (Результат: 1660,95)

Электронные таблицы Excel финансового анализа Репина В.В. вычисляют движения денежных средств, прибыль-убыток, изменения задолженности, изменения запасов, динамику изменения статей баланса, финансовые показатели в формате GAAP . Позволят провести коэффициентный финансовый анализ предприятия .

Таблицы Excel для проведения финансового анализа от Малахова В.И. позволяют рассчитать баланс в процентной форме, оценку эффективности управления, оценку финансовой (рыночной) устойчивости, оценку ликвидности и платежеспособности, оценку рентабельности, деловой активности, положения предприятия на РЦБ, модель Альтмана. Строятся диаграммы актива баланса, динамики выручки, динамика валовой и чистой прибыли, динамика задолженности.

Таблицы Excel Попова А.А. позволят провести финансовый анализ : рассчитать деловую активность, платежеспособность, рентабельность, финансовую устойчивость, агрегированный баланс, провести анализ структуры активов баланса, коэффициентный и динамический анализ на основе 1 и 2 формы бухгалтерской отчетности предприятия.

Таблицы Excel финансового анализа предприятия Зайковского В.Э. позволяют на основе 1 и 2 форм внешней бухгалтерской отчетности произвести расчет банкротства предприятия по модели Альтмана, Таффлера и Лиса, оценить финансовое состояние предприятия по показателям ликвидности, финансовой устойчивости, состоянию основных средств, оборачиваемости активов, рентабельности. Помимо этого находят связь между неплатежеспособностью предприятия с задолженностью государства перед ним. Имеются графики изменения активов и пассивов предприятия во времени.

Анализ в Excel Маслова В.Г. позволит провести спектр балльного анализа финансового состояния . Спектр балльный метод является наиболее надежным методом финансово-экономического анализа . Его суть заключается в проведении анализа финансовых коэффициентов путем сравнения полученных значений с нормативными величинами, используется при этом система «разнесения» этих значений по зонам удаленности от оптимального уровня. Анализ финансовых коэффициентов производится путем сравнения полученных значений с рекомендуемыми нормативными величинами, играющими роль пороговых нормативов. Чем удаленнее значение коэффициентов от нормативного уровня, тем ниже степень финансового благополучия и выше риск попадания в категорию несостоятельных предприятий.

Модуль финансовый анализ для MS Excel

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

Оценка финансово-хозяйственной деятельности

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.

Размещено на http://www.allbest.ru/

ФИНАНСОВЫЙ АНАЛИЗ В EXCEL

Задание № 1

Описание функции ПЛТ или ППЛАТ (ставка; кпер; пс; бс; тип)

Возвращает сумму периодического платежа на основе постоянства сумм платежей и постоянства процентной ставки.

ставка -- процентная ставка по ссуде.

кпер -- общее число выплат по ссуде.

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

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

Рассмотрим пример расчета 30-летней ипотечной ссуды со ставкой 8% годовых при начальном взносе 20% и ежемесячной (ежегодной) выплате с помощью функции ППЛАТ

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

Отметим, что очень важно быть последовательным в выборе единиц измерения для задания аргументов СТАВКА и КПЕР. Например, если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12% годовых, то для задания аргумента СТАВКА используйте 12%/12, а для задания аргумента КПЕР - 4*12. Если вы делаете ежегодные платежи по тому же займу, то для задания аргумента СТАВКА используйте 12%, а для задания аргумента КПЕР - 4.

Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое функцией ППЛАТ значение на величину КПЕР. Интервал выплат - это последовательность постоянных денежных платежей, осуществляемых за непрерывный период.

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

Например, депозит в банк на сумму 1000 рублей представляются аргументом -1000, если вы вкладчик, и аргументом 1000, если вы представитель банка.

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вычислить n-годичную (общее число периодов выплат) ипотечную ссуду покупки квартиры за Р руб. с годовой ставкой i % и начальным взносом A%, . Cделать расчет для ежемесячных и ежегодных выплат. Найти суммы периодических ежемесячных и ежегодных выплат, общие суммы ежемесячных и ежегодных выплат, общие суммы ежемесячных и ежегодных комиссионных.

Для выполнения задания заполните таблицу своими исходными данными:

Стоимость квартиры - Р

Годовая ставка i%

Срок погашения ссуды n

Начальный взнос A%

Начальный взнос в денежном выражении рассчитывается по формуле:

стоимость квартиры*А%

Ежегодные выплаты рассчитываются по функции

(ПЛТ(ставка; кпер; пс; бс; тип) либо ППЛАТ(ставка; срок; -ссуда);

ежемесячные выплаты

ППЛАТ(ставка/12; срок*12; -ссуда)), либо ПЛТ(ставка/12; срок*12; -ссуда)

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

Общие ежемесячные = ежемесячные*срок*12

Общие ежегодные = ежегодные*срок

Ежемесячные комиссионные = общие ежемесячные - ссуда

Ежегодные комиссионные = общие ежегодные - ссуда

ВАРИАНТЫ ЗАДАНИЙ

ЗАДАНИЕ №2

ЧПС (ставка; значение1; значение2; ...) или НПЗ (ставка; значение1; значение2; ...)

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

ставка -- ставка дисконтирования за один период.

Значение1, значение2,... -- от 1 до 29 аргументов, представляющих расходы и доходы.

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

ЧПС использует порядок аргументов значение1, значение2, ... для определения порядка поступлений и платежей. Убедитесь в том, что ваши платежи и поступления введены в правильном порядке.

Пример 1

	Описание
	Годовая ставка дисконтирования
	Начальные затраты на инвестиции за один год, считая от текущего момента
	Доход за первый год
	Доход за второй год
	Доход за третий год
	Описание (результат)
ЧПС(A2; A3; A4; A5; A6)	Чистая приведенная стоимость инвестиции (1 188,44)

В примере начальные затраты в 10 000 руб. были включены как одно из значений, поскольку выплата производилась в конце первого периода.

Пример 2

	Описание
	Годовая ставка дисконтирования. Она может представлять собой темп инфляции или процентную ставку по конкурирующим инвестициям.
	Начальные затраты на инвестиции
	Доход за первый год
	Доход за второй год
	Доход за третий год
	Доход за четвертый год
	Доход за пятый год
	Описание (результат)
ЧПС(A2; A4:A8)+A3	Чистая приведенная стоимость этой инвестиции (1 922,06)
ЧПС(A2; A4:A8; -9000)+A3	Чистая приведенная стоимость этой инвестиции с потерей 9000 на шестом году (-3 749,47)

В этом примере начальные затраты в 40 000 руб. не были включены как одно из значений, поскольку выплата производилась в начале первого периода.

Рассмотрим следующую задачу. Вас просят дать в долг 10 000 рублей и обещают вернуть через год 2 000 рублей, через два года - 4 000 рублей. Через три года - 7 000 рублей. При какой годовой процентной ставке эта сделка выгодна?

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

=НПЗ (В6; В2:В4)

Первоначально в ячейку В6 вводится произвольный процент, например 3%. После этого выбираем команду Сервиз, Подбор параметра и заполняем открывшееся диалоговое окно Подбор параметра.

В поле Установить в ячейке даем ссылку на ячейку В7, в которой вычисляется чистый текущий объем вклада по формуле

=НПЗ (В6; В2:В4)

В поле Значение указываем 10000 - размер ссуды. В поле Изменяя значение ячейки даем ссылку на ячейку В6, в которой вычисляется годовая процентная ставка. После нажатия кнопки ОК средство подбора параметров определит, при какой годовой процентной ставке чистый текущий объем вклада равен 10000 рублей. Результат вычисления выводится в ячейку В6.

В нашем случае годовая учетная ставка равна 11,79%.

Вывод: если банки предлагают большую годовую процентную ставку, то предлагаемая сделка не выгодна.

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ: Вас просят дать в долг Р рублей и обещают вернуть Р1 руб. через год, Р2 руб. - через два года и т.д. и, наконец, РН руб. через Н лет. При какой годовой процентной ставке эта сделка имеет смысл? (ЧПС(ставка; значение1; значение2; ...). Для уточнения процентной ставки использовать метод подбора параметра.

ЗАДАНИЕ №3

ПС(ставка; кпер; плт; бс; тип) или ПЗ(ставка; кпер; плт; бс; тип)

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

Например, если получена ссуда на автомобиль под 10 процентов годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10%/12 или 0,83%. В качестве значения аргумента ставка нужно ввести в формулу 10%/12 или 0,83% или 0,0083.

Например, если получена ссуда на 4 года под автомобиль и делаются ежемесячные платежи, то ссуда имеет 4*12 (или 48) периодов. В качестве значения аргумента кпер в формулу нужно ввести число 48

плт -- выплата, производимая в каждый период и не меняющаяся за все время выплаты ренты. Обычно выплаты включают основные платежи и платежи по процентам, но не включают других сборов или налогов. Например, ежемесячная выплата по четырехгодичному займу в 10 000 руб. под 12 процентов годовых составит 263,33 руб. В качестве значения аргумента выплата нужно ввести в формулу число -263,33.

бс -- требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0). Например, если предполагается накопить 50000 руб. для оплаты специального проекта в течение 18 лет, то 50 000 руб. это и есть будущая стоимость.

Пример

Результат получается отрицательный, поскольку он представляет деньги, которые необходимо выплатить, исходящий денежный поток. Если бы за аннуитет требовалось заплатить 60 000, эта инвестиция была бы не выгодной, так как приведенная стоимость (59 777,15) аннуитета меньше данной суммы.

· Примечание. Чтобы получить месячную процентную ставку, разделите годовую ставку на 12. Чтобы узнать количество выплат, умножьте количество лет кредита на 12.

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

В функциях, связанных с аннуитетами, выплачиваемые денежные средства, такие как депозит на сбережения, представляются отрицательным числом; полученные денежные средства, такие как чеки на дивиденды, представляются положительным числом. Например, депозит в банк на сумму 1000 руб. представляется аргументом -1000 -- для вкладчика и аргументом 1000 -- для банка.

Рассмотрим следующую задачу. Допустим, что у вас просят в долг 10000 рублей и обещают возвращать по 2000 рублей в течение 6 лет. Будет ли выгодна эта сделка при годовой ставке 7%?

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

=ПЗ(В4; В2; -В3)

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

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вас просят дать в долг Р руб. и обещают возвращать по А руб. ежегодно в течении Н лет. При какой процентной ставке эта сделка имеет смысл.

Для решения задачи использовать функцию

(ПС(ставка; кпер; плт; бс; тип) либо ПЗ(ставка; срок; -ежегод.выплаты)). В функции сначала берется произвольная ставка, затем уточняется методом подбора параметра.

ЗАДАНИЕ № 4

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

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

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

период -- это период, для которого требуется найти платежи по процентам; должен находиться в интервале от 1 до «кпер».

кпер -- общее число периодов платежей по аннуитету.

Пример

	Описание
	Годовая процентная ставка
	Период, для которого требуется найти проценты
	Срок займа (в годах)
	Стоимость займа на текущий момент
	Описание (результат)
ПРПЛТ (A2/12; A3*3; A4; A5)	Выплаты по процентам за первый месяц на приведенных выше условиях (-22,41)
ПРПЛТ (A2; 3; A4; A5)	Выплаты по процентам за последний год на приведенных выше условиях (начисления процентов производятся ежегодно) (-292,45)

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

Возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки.

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

период -- задает период, значение должно быть в интервале от 1 до «кпер».

кпер -- общее число периодов платежей по аннуитету.

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

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

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

Заметки

Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов «ставка» и «кпер». Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента «ставка» и 4*12 для задания аргумента «кпер». Если вы делаете ежегодные платежи по тому же займу, то используйте 12% для задания аргумента «ставка» и 4 для задания аргумента «кпер».

Пример

Рассмотрим пример вычисления основных платежей, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды 1000000 рублей на срок 5 лет при годовой ставке 2%.

еxcel таблица формула

ежегодная плата вычисляется в ячейке В3 по формуле

=ППЛАТ(процент; срок; - размер ссуды)

За первый год плата по процентам в ячейке В7 вычисляется по формуле

=D6*$B$1

Основная плата $B$3-B7

Остаток долга в ячейке D7 вычисляется по формуле

=D6-C7

В оставшиеся годы эти платы определяются с помощью протаскивания маркера заполнения выделенного диапазона В7:D7 вниз по столбцам.

Отметим, что основную плату и плату по процентам можно было непосредственно найти с помощью фукций ОСНПЛАТ и ПЛПРОУ соответственно.

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вычислить ежегодные основные платежи, плату по процентам, общую годовую выплату и остаток долга на примере ссуды Р руб. под годовую ставку i% на срок Н лет.

Использовать функции

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

ППЛАТ(ставка; срок; -ссуда), ПЛПРОЦ(ставка; период; срок; - ссуда), ОСНПЛАТ(ставка; период; срок; -ссуда).

Остаток долга = долг - ОСНПЛАТ

ЗАДАНИЕ № 5

КПЕР (ставка; плт; пс; бс; тип)

Возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.

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

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

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

бс -- требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент бс опущен, то он полагается равным 0 (например, бзс для займа равно 0).

Например, если вы берете в долг 1000 рублей при годовой ставке 1% и собираетесь выплачивать по 100 рублей в год, то число выплат вычисляется следующим образом:

КПЕР(1%; -100; 1000)

В результате получаем ответ: 11.

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вы берете в долг Р руб. под годовую ставку i% и собираетесь выплачивать по А руб. в год. Сколько лет займут эти выплаты? Найти 2 способами

1 - й способ - использовать функции

ПС(ставка; кпер; плт; бс; тип) либо ПЗ(ставка; срок; - ежегод.вклад)

2 - й способ - использовать функцию КПЕР(ставка; -ежег.вклад; ссуда)

ЗАДАНИЕ №6

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

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

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

кпер -- это общее число периодов платежей.

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

пс -- это приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент нз опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента плт.

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

Примечание. Годовая процентная ставка делится на 12, т. к. начисление сложных процентов производится ежемесячно.

Приведем пример использования функции БЗ. Предположим, вы хотите зарезервировать деньги для специального проекта, который будет осуществлен через год. Предположим, вы собираетесь вложить 1000 рублей при годовой ставке 6%. Вы собираетесь вкладывать по 100 рублей в начале каждого месяца в течение года. Сколько денег будет на счете в конце 12 месяцев?

С помощью формулы

БЗ(6%/12; 12; -100; -1000; 1)

получаем ответ 2 301,4 руб.

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вы собираетесь вкладывать по А у. е. в течении Н лет при годовой ставке I%. Сколько денег будет на счете через n лет?

Использовать функцию

БС(ставка; кпер; плт; пс; тип)) либо БЗ(ставка; срок; - выплата)

ЗАДАНИЕ №7

Составить отчетную ведомость реализации товаров N магазинами с месяца А по месяц В. Найти место магазина по суммарной выручке (функция РАНГ()), среднюю выручку магазина в месяц (СРЗНАЧ(массив выручки по месяцам)), процент прибыли магазина в общей выручке(суммарную выручку магазина/суммарную выручку всех магазинов). Построить 2 диаграммы (1 - процент прибыли к общей выручке, 2 - объемы реализации продукции).

Стоимость товара для каждого магазина разная.

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

				Стоимость товаров	Объемы реализации (тыс. шт.)
		сентябрь		44,45,46,47,201,202	24,25,26,27,36,38
	сентябрь
				39,38,40,41,49, 36	25,27,28,22,23,29
		сентябрь
				201,205,305,205,11,14,22	70,71,72,73,74,99,85
	сентябрь
	сентябрь			420,430,401,400, 300
		сентябрь

Размещено на Allbest.ru

Подобные документы

Составление отчетной ведомости "Магазины" в Excel 2013. Работа с таблицами семейства Microsoft Office. Построение круговой диаграммы и гистограммы, графиков. Разработка процедур для табулирования функций. Программирование функций пользователя на VBA.

курсовая работа , добавлен 03.04.2014


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

контрольная работа , добавлен 05.07.2010


Создание электронных таблиц в MS Excel, ввод формул при помощи мастера функций. Использование относительной и абсолютной ссылок в формулах. Логические функции в MS Excel. Построение диаграмм, графиков и поверхностей. Сортировка и фильтрация данных.

контрольная работа , добавлен 01.10.2011


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

курсовая работа , добавлен 21.11.2012


Вычисления в Excel. Формулы и функции: Использование ссылок и имен, перемещение и копирование формул. Относительные и абсолютные ссылки. Понятиеи и типы функций. Рабочая книга Excel. Связь между рабочими листами. Построение диаграмм в EXCEL.

лабораторная работа , добавлен 28.09.2007


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

курсовая работа , добавлен 25.04.2013


Процессор электронных таблиц Microsoft Excel - прикладная программа, предназначенная для автоматизации процесса обработки экономической информации, представленной в виде таблиц; применение формул и функций для производства расчетов; построение графиков.

реферат , добавлен 03.02.2013


Возможность использования формул и функций в MS Excel. Относительные и абсолютные ссылки. Типы операторов. Порядок выполнения действий в формулах. Создание формулы с вложением функций. Формирование и заполнение ведомости расхода горючего водителем.

контрольная работа , добавлен 25.04.2013


Создание таблицы "Покупка товаров с предпраздничной скидкой". Понятие формулы и ссылки в Excel. Структура и категории функций, обращение к ним. Копирование, перемещение и редактирование формул, автозаполнение ячеек. Формирование текста функции в диалоге.

лабораторная работа , добавлен 15.11.2010


Функции, применяемые для вычисления параметров ссуды и анализа кредитов и вкладов, представленные в Microsoft Excel. Анализ экономической проблемы, для решения которой используется финансовая функция КПЕР. Описание примеров возможного ее использования.

В Excel, и касаться она будет анализа эффективности компании суммарно по показателям БДР, ОПУ и ОДДС. В отличие от всех остальных разделов анализа, здесь практически не будет никаких исходных данных – сугубо финансовые расчеты. В качестве примера используем реальный проект, который уже работает в виде бизнеса, однако цифры там будут условные.

Начнем с того, что определим, какие показатели мы хотим выделить и рассчитать. В даном случае это: EBIT, активов, чистая , активов, рентабельность персонала, рентабельность основных средств и рентабельность продаж. Все эти показатели нужно будет отдельно рассчитать – подчеркивем, именно рассчитать, т.к. исходные данные из имеющейся отчетности по EBIT и EBITDA не отражают точную картину.

Итак, создаем таблицу в листе фин анализ в Excel, и вписываем туда все перечисленные показатели.

Далее по порядку все рассчитываем. Рентабельность продаж рассчитывается просто: прибыль за вычетом налога на прибыль деленная на выручку. Этот показатель дает понимание эффективности бизнеса в целом – с учетом всех издержек. Вообще надо сказать, что показателей рентабельности всего несколько сотен, но в нашем случае достаточно взять только самые основные, чтобы можно было дать общую оценку актуальности инвестиций.

Следующий показатель – рентабельность основных средств (ROFA) рассчитывается как отношение стоимости основных средств труда к чистой прибыли. То есть, если речь идет, например, об автопроизводителе, то в качестве основных средств могут быть – оборудование, патенты, технологии и т.д. Не путайте с показателем рентабельности активов (ROA) – данная категория рассчитывается уже исходя из отношения чистой прибыли и стоимости всех активов, находящихся на балансе предприятия.

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

Теперь рассчитываем оборачиваемость активов. Этот показатель очень важен в оценке бизнеса, так как указывает на мобильность имеющихся активов и то, как часто они используются. Чем выше оборачиваемость капитала, тем более эффективным считается . Формула расчета простая: EBIT/Total assets. Отдельно стоит рассчитать показатели EBIT и EBITDA. В качестве первого можно применить показатель чистой прибыли минус налоги, EBITDA рассчитывается еще проще: EBIT+Depreciation+Interests. Лучше оба этих показатели сделать в этой же таблице – для удобства дальнейших расчетов. Наконец, для расчета чистой прибыли можно отнять от EBIT все налоговые вычеты. В итоге, все финансовые показатели должны быть собраны в таблице, показанной выше.

Основные показатели мы рассчитали, теперь перейдем к расчету точки безубыточности и NPV. Точка безубыточности указывает на минимально необходимый объем выручки при имеющихся затратах, чтобы обеспечить нулевую . Для его расчета создаем отдельный лист, делаем таблицу, на котором указываем: процент загрузки, выручка, постоянные и , налоги, и итоговый результат. Группируем показатели которые относятся к переменным и постоянным затратам, применяем стандартную формулу для расчета налогов, и наконец проводим все оставшиеся расчеты. В итоге должна получится таблица, которая показана на рисунке выше. Эта таблица показывает, при каком уровне загрузки бизнеса будет достигнута нулевая рентабельность. В нашем случае получилась загрузка 16%.

Ну и наконец, осталось рассчитать NPV компании, чтобы было понимание о какой-либо актуальности в инвестициях вообще. В начале определимся со ставкой дисконтирования – нужно ли ее рассчитывать отдельно через модель CAPM или же достаточно просто взять норму альтернативной доходности, в качестве которой вполне может сгодиться, например, государственная облигация. На настоящее время доходность по ОФЗ составляет около 10%. Конкретно в этом проекте общий NPV получился на 2.2 млрд. рублей, срок дисконтированной окупаемости инвестиций – 31 месяц, и таким образом доходность получилась в районе 40% в годовом выражении, что указывает на то, что инвестировать в данную компанию – целесообразно.

На этом собственно и все. Это была последняя статья из серии про финансовый анализ в Excel.

Будьте в курсе всех важных событий United Traders - подписывайтесь на наш