Бесплатные программы по финансовому анализу в Excel (скачать). Бесплатные программы по финансовому анализу в Excel (скачать) К ним относятся

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

Суть понятия коэффициента рентабельности

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

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

Если рентабельность растет:

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

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

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

Если рентабельность снизилась:

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

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

Как рассчитать рентабельность продаж в Excel

Международное обозначение показателя – ROS. Всегда рассчитывается коэффициент рентабельности продаж по прибыли от продаж.

Традиционная формула:

ROS = (прибыль/выручка) * 100%.

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

Формула валовой рентабельности продаж (маржи):

(Валовая прибыль / выручка от реализации) * 100%.

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

Информация для расчета:

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

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

Рентабельность продаж по операционной прибыли (EBIT):

(Операционная прибыль / выручка от реализации) * 100%.

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

((Стр. 2300 + стр. 2330) / стр. 2110) * 100%.

Данные для расчета:

Рассчитаем рентабельность по операционной прибыли – подставим в формулу ссылки на нужные ячейки:

Формула рентабельности продаж по чистой прибыли:

(Чистая прибыль / выручка) * 100%.

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

Покажем коэффициент рентабельности продаж на графике:

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

Нормой считается значение выше нуля. Более конкретный диапазон зависит от сферы деятельности. Каждое предприятие сравнивает свой коэффициент рентабельности продаж и нормативное значение по отрасли. Хорошо, если рассчитанный показатель практически не отличается от уровня инфляции.

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

Данные для расчета берутся из бухгалтерского баланса. Коэффициент рентабельности обозначается «ROE».

Экономический смысл финансового показателя «ROE»

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

Существует несколько коэффициентов доходности. Нас же будет интересовать рентабельность собственного капитала. То есть тех активов, которые принадлежат фирме на правах собственности.

Как оценивать результат расчетов:

Чем выше коэффициент, тем эффективнее использовались вложенные средства. Инвестиции более доходные.
Слишком высокий показатель – «страдает» финансовая устойчивость организации.
Коэффициент ниже нуля – целесообразность инвестиций в данное предприятие сомнительна.

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

Значение доходности собственного капитала.

Формула расчета показателя ROE в Excel

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

Формула для расчета коэффициента рентабельности собственных средств:

ROE = (Чистая прибыль / Средняя величина собственных средств) * 100%.

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

Средняя величина собственного капитала – формула расчета:

СК = (СК начала периода + СК конца периода) / 2.

Рентабельность собственного капитала – формула по балансу:

ROE = (стр. 2110 + стр. 2320 + стр. 2310 + стр. 2340) / ((стр. 1300 нг + стр. 1300 кг + стр. 1530 нг + стр. 1530 кг) / 2) * 100%.

В числителе – данные из отчета о финансовых результатах (форма 2). В знаменателе – из итогового баланса (форма 1).

Для расчета рентабельности средствами Excel введем данные для финансовой отчетности компании «Х»:

И отчет о финансовых результатах ("по-старому": о прибылях и убытках):

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

Коэффициент рентабельности за 2015 г.: = (6695 / 75000) * 100% = 8,9%.
Коэффициент рентабельности за 2014 г.: = (2990 / 65000) * 100% = 4,6%.

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

Формулы Excel для расчета рентабельности собственного капитала:

Выводы:

Наблюдается рост показателя рентабельности собственных средств с 4,6 процентов до 8,9 процентов.
Вкладывать свободные средства в акции компании «Х» не выгодно. Та же банковская ставка по депозитам в 2015 году составила 9,5%.
Целесообразно рассмотреть другие предложения от предприятий или положить деньги на депозит под проценты (в крайнем случае).

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

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

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

Пример финансового анализа предприятия в Excel

Задача – изучение результатов финансовой деятельности и состояния предприятия. Цели:

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

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

Анализ финансового состояния предприятия подразумевает

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

Рассмотрим приемы анализа балансового отчета в Excel.

Сначала составляем баланс (для примера – схематично, не используя все данные из формы 1).

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

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

Какие результаты дает аналитический баланс:

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

Статистический анализ данных в Excel

Для реализации статистических методов в программе Excel предусмотрен огромный набор средств. Часть из них – встроенные функции. Специализированные способы обработки данных доступны в надстройке «Пакет анализа».

Рассмотрим популярные статистические функции.

В примере большая часть данных выше среднего, т.к. асимметрия больше «0».

ЭКСЦЕСС сравнивает максимум экспериментального с максимумом нормального распределения.

В примере максимум распределения экспериментальных данных выше нормального распределения.

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

Рентабельность – показатель комплексный, его расчёт позволяет проводить сравнение эффективности субъектов хозяйственной деятельности вне зависимости от масштабов и отраслевой принадлежности предприятия.

В наиболее общем виде для определения коэффициента рентабельности используется формула:

(1) R = П/Пф

где R – показатель рентабельности;

П – прибыль (чистая) ;

Пф – производственный фактор .

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

Наибольшее распространение получил анализ:

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

Рассмотрение этих показателей базируется на расчёте соответствующих коэффициентов.

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

Ещё один распространённый вариант определения коэффициента общей рентабельности – расчёт его посредством оценки прибыльности работы хозяйствующего субъекта. В данном случае используется частное от деления прибыли, получаемой предприятием до налогообложения и выручки от реализации продукции или услуг. Показатель прибыли отражается в строке 140 формы 2 (Отчёт о прибылях и убытках), данные об объёме выручки содержатся в строке 010 формы 2.

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

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

Коэффициент рентабельности продаж даёт возможность узнать объём прибыли, которую компания получает на одну денежную единицу, поступающую от реализации товаров, работ или услуг.Этот показатель определяют как в целом, так и по отдельным товарным позициям.

(2) Крп = финансовый результат от реализации / нетто выручка от реализации

Финансовый результат от реализации товаров или услуг находит своё отражение в строке 050 Отчёта о прибылях и убытках, нетто выручка от реализации – в строке 010.

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

(3) Крпз = балансовая прибыль (убыток) / себестоимость

Значение балансовой прибыли содержится в строке 029 формы 2, себестоимость отражена в строке 020.

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

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

(4) ККАП = стр. ф№2 029 или 050 или 140 или 190 / [(стр. 300 нач. года + стр.300 кон. года)/2]

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

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

Анализ рентабельности не даёт возможности сделать вывод о рисках , сопровождающих деятельности предприятия. Высокая эффективность может быть достигнута путём принятия больших рисков (к примеру, работа «с колёс», без использования запасов).
Анализ рентабельности не учитывает фазу развития предприятия . При осуществлении масштабных инвестиций показатели рентабельности могут ухудшаться, но это не означает, что были приняты неверные управленческие решения.
Коэффициенты рентабельности в числителе учитывают показатели, носящие текущий характер (прибыль, выручку), а сумма капитала или активов – следствие деятельности, осуществляемой на протяжении более длительного отрезка времени. Следовательно, анализ рентабельности должен быть дополнен анализом оценки стоимости компании.

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

Порог рентабельности (аналог. BEP, breakeven point, точка безубыточности, критическая точка, порог прибыльности) – это объем продаж предприятия, при котором достигается минимальный уровень прибыли (равный нулю). Другими словами, предприятие функционирует на самоокупаемости своих затрат. Порог рентабельности предприятия иногда на практике называют .

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

При расчете порога рентабельности используют два вида затрат (издержек):

Постоянные затраты (англ. VA, Variable Costs) – вид издержек предприятия, размер которых не зависит от изменения объема производства и продаж продукции.
Переменные затраты (англ. FC, Fixed Costs) – вид издержек предприятия, размер которых напрямую зависит от объема производства и продажи продукции.

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

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

Сумма всех постоянных затрат формирует совокупные постоянные и переменные затраты предприятия (TVC, TFC).

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

BEP 1 (Breakeven point ) – порог рентабельности в денежном эквиваленте;

TR (Total Revenue ) – выручка от продаж продукции;

TFC (Total Fixed Costs ) – совокупные постоянные затраты;

TVC (Total Variable Costs ) – совокупные переменные затраты.

BEP 2 (Breakeven point ) – порог рентабельности выраженный в натуральном эквиваленте (объем производства);

P (Price ) – цена единицы проданного товара;

AVC (Average Variable Costs ) – средние переменные затраты на единицу товара.

★

Расчет порога рентабельности в Excel

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

Основные параметры для оценки порога рентабельности предприятия

На следующем этапе необходимо рассчитать как будет изменяться прибыль и затраты от объема реализации товара. Постоянные затраты, представлены в колонке «В», они не будут изменяться от объема производства. Переменные затраты на единицу продукции будут расти пропорционально производству (столбец «С»). Формулы расчета доходов и затрат будут следующие:

Переменные затраты предприятия =$C$5*A10

Общие затраты предприятия =C9+B9

Доход =A9*$C$6

Чистая прибыль =E9-C9-B9

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

Оценка порога рентабельности предприятия в Excel

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

Порог рентабельности в денежном эквиваленте =E26*B26/(E26-C26)

Порог рентабельности в натуральном эквиваленте =B26/(C6-C5)

Расчет уровня рентабельности по формулам в Excel

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

Определение порога рентабельности графически

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

Графический анализ доходов и затрат предприятия

Порог рентабельности и запас финансовой прочности предприятия

Определение минимально допустимого уровня объема продаж позволяет планировать и создавать запас финансовой прочности – это избыточный объем продаж или объем чистой прибыли, который позволяет предприятию устойчиво функционировать и развиваться. К примеру, если текущий объем производства (продаж) соответствует 17 шт., то запас финансовой прочности будет равен 240 руб. На графике ниже показана область запаса финансовой прочности предприятия при объеме продаж 17 шт.

Запас финансовой прочности предприятия

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

★
(расчет коэффициентов Шарпа, Сортино, Трейнора, Калмара, Модильянки бета, VaR)
+ прогнозирование движения курса

Резюме

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