Как сделать рентабельность в экселе

Добавил пользователь Владимир З.
Обновлено: 29.08.2024

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

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

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

Для ответа на вышеприведённые вопросы используют следующие показатели эффективности инвестиционного проекта:

срок окупаемости проекта (обычно в месяцах)
чистая приведённая стоимость (net present value, NPV)
внутренняя норма доходности (IRR).

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

Недостаток этого показателя – игнорирование факта изменения стоимости денег во времени (дисконтирования). Дисконтирование — это приведение будущих денежных потоков к текущему периоду с учетом изменения стоимости денег с течением времени. Дисконтирование производится путём умножения значений будущих потоков на понижающий коэффициент:

Кд = 1 / (1 + Ставка дисконтирования)^Номер периода

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

стоимостью привлекаемого капитала инвестора;
прогнозной инфляцией;
премией за риск проекта.

Коэффициент дисконтирования используется для расчёта показателя Чистая приведённая стоимость (net present value -NPV), который по сути является совокупным дисконтированным денежным потоком. Проект считается экономически выгодным, если его NPV не отрицательна. Нулевое значение NPV говорит о том, что проект принесет прибыль, достаточную для выплаты процентов по привлечённому капиталу с учётом инфляции. Чем выше NPV проекта, тем он привлекательнее (при учете рисков).

Для того чтобы получить более универсальную оценку привлекательности инвестиционного проекта, можно рассчитать третий показатель: внутреннюю норму доходности (IRR) – значение ставки дисконтирования, при которой NPV равен нулю (то есть проект отобъёт вложенные в него средства). Считается, что проект приемлем, если расчётное значение IRR больше ставки дисконтирования. Кроме того, этот показатель удобно использовать при сравнении альтернативных инвестиционных проектов: для каждого рассчитывается показатель IRR и предпочтение отдаётся проекту с наибольшим IRR.

Пример расчёта инвестиционного проекта в Excel

Скачайте файл с примером pokazateli-investproekta, ознакомьтесь с заданием. Первый шаг инвестиционного планирования – составление прогноза денежных потоков.

Прогнозирование денежного потока в Excel

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

Срок окупаемости в Excel: пример расчёта

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

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

Разъяснение внутренней нормы прибыли (IRR)

IRR — это ставка дисконтирования, которая используется для измерения доходности инвестиций на основе периодических доходов .

IRR отображается в процентах и может использоваться для определения прибыльности проекта (инвестиции) для компании.

Позвольте мне объяснить IRR на простом примере.

Предположим, вы планируете купить компанию за 50 000 долларов, которая будет приносить 10 000 долларов ежегодно в течение следующих 10 лет. Вы можете использовать эти данные для расчета IRR этого проекта, которая представляет собой норму прибыли, которую вы получаете от своих инвестиций в размере 50 000 долларов США.

В приведенном выше примере IRR составляет 15% (мы увидим, как рассчитать это позже в руководстве). Это означает, что вы вкладываете свои деньги со ставкой 15% или доходом в течение 10 лет.

Получив значение IRR, вы можете использовать его для принятия решений. Поэтому, если у вас есть какой-либо другой проект, в котором IRR составляет более 15%, вам следует вместо этого инвестировать в этот проект.

Или, если вы планируете взять ссуду или привлечь капитал и купить этот проект за 50 000 долларов, убедитесь, что стоимость капитала составляет менее 15% (иначе вы заплатите больше как стоимость капитала, чем вы зарабатываете из проект).

Функция IRR в Excel — синтаксис

Excel позволяет рассчитать внутреннюю норму прибыли с помощью функции IRR. Эта функция имеет следующие параметры:

values — массив ячеек, содержащих числа, для которых вы хотите рассчитать внутреннюю доходность.
guess — число, которое, по вашему мнению, близко к результату IRR (это не обязательно, по умолчанию 0,1 — 10%). Это используется, когда есть возможность получить несколько результатов, и в этом случае функция возвращает результат, наиболее близкий к значению аргумента предположения.

Вот некоторые важные предпосылки для использования функции:

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

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

Расчет IRR для различных денежных потоков

Предположим, у вас есть набор данных, показанный ниже, в котором у нас есть начальные инвестиции в размере 30 000 долларов США, а затем изменяющийся денежный поток / доход от них в течение следующих шести лет.

Для этих данных нам нужно рассчитать IRR, что можно сделать, используя следующую формулу:

Результат функции — 8,22% , что представляет собой IRR денежного потока через шесть лет.

Узнайте, когда инвестиции принесут положительную внутреннюю норму доходности

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

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

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

Для этого вместо расчета IRR для всего проекта мы найдем IRR для каждого года.

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

Как видите, IRR после года 1 (значения D2: D3) составляет -80%, после года 2 (D2: D4) -52% и т. Д.

Этот обзор показывает нам, что инвестиции в размере 30 000 долларов США с заданным денежным потоком имеют положительную внутреннюю норму доходности после пятого года.

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

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

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

Использование функции IRR для сравнения нескольких проектов

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

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

Чтобы получить лучший проект (с самым высоким IRR, нам нужно будет рассчитать IRR для каждого проекта, используя простую формулу IRR:

Приведенная выше формула даст IRR для проекта 1. Аналогичным образом вы можете рассчитать IRR для двух других проектов.

Проект 1 имеет IRR 5,60%.
Проект 2 имеет IRR 1,75%.
Проект 3 имеет IRR 14,71% .

Если мы предположим, что стоимость капитала составляет 4,50%, мы можем сделать вывод, что вложение 2 неприемлемо (так как приведет к убыткам), в то время как вложение 3 является наиболее прибыльным с самой высокой внутренней нормой доходности.

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

Определение : если вам интересно, какова стоимость капитала, то это деньги, которые вам придется заплатить, чтобы получить доступ к деньгам. Например, если вы берете в долг 100 тыс. Долларов под 4,5% годовых, стоимость капитала составляет 4,5%. Точно так же, если вы выпускаете привилегированные акции, обещая доходность 5% для получения 100 тыс., Ваша стоимость капитала составит 5%. В реальных сценариях компания обычно привлекает деньги из различных источников, а ее стоимость капитала представляет собой средневзвешенное значение всех этих источников капитала.

Расчет IRR для нерегулярных денежных потоков

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

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

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

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

Функция XIRR принимает денежные потоки, а также даты, что позволяет ей учитывать нерегулярные денежные потоки и давать корректирующую IRR.

В этом примере IRR можно рассчитать по следующей формуле:

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

IRR против NPV — что лучше?

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

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

Если это значение окажется выше вашего первоначального оттока, значит, проект прибыльный, иначе проект не прибыльный.

С другой стороны, когда вы рассчитываете IRR для проекта, он сообщает вам, какой будет норма прибыли для всего будущего денежного потока, чтобы вы получили сумму, эквивалентную текущему оттоку. Например, если вы тратите сегодня 100 тысяч долларов на проект с IRR или 10%, это говорит вам, что если вы дисконтируете все будущие денежные потоки по ставке дисконтирования 10%, вы получите 100 тысяч долларов.

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

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

В целом метод IRR имеет ряд недостатков, которые делают метод NPV более надежным:

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

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

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

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

В статье подробно написано о создании такого отчета, как на рисунке ниже.

Прежде чем начать читать, скачайте отчет и данные.

Исходные данные

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

Итак, нам нужен отчет в виде таблицы с такими столбцами:

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

Проанализируем продажи, представив все показатели отчета в понятном виде и на одном листе.

Структура отчета

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

Элементы управления

Чтобы управлять данными отчета, сверху разместим элементы управления – срезы и временную шкалу.

Карточки с показателями

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

Графики и диаграммы

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

Сводная таблица с деталями

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

Итак, в интерактивном отчете четыре блока:

А теперь подробно, по шагам. Начнем создавать отчет со сводной таблицы.

Сводная таблица

Если вы раньше никогда не создавали сводные таблицы в Excel, то прочитать об этом можно здесь: “Как построить сводную таблицу в Excel”.

Откройте отчет о валовой прибыли в Excel и удалите из него сроку с итогами – мы рассчитаем их с помощью сводных таблиц. Еще лучше будет, если использовать форматированные (smart – умные) таблицы Excel. Для этого выделите мышкой любую ячейку отчета и перейдите в меню Главная -> Форматировать как таблицу -> выберите любой понравившийся вид таблицы.

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

Отформатируйте сводную таблицу. Щелкните правой кнопкой мышки по любой ячейке с цифрами таблицы, в появившемся меню выберите пункты:

После всех манипуляций должна получиться такая таблица:

Сводные диаграммы и графики

Кстати, из Power Pivot сводные диаграммы можно добавить без сводных таблиц.

Всего в отчете будет пять графиков и диаграмм:

1. График с динамикой продаж по периодам

Измените тип диаграммы: щелкните по графику мышкой и перейдите на вкладку Конструктор -> Изменить тип диаграммы -> выберите График.

После выполнения этих шагов должен получиться такой график:

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

2. Гистограмма продаж по покупателям

Создаем следующий график. Вставьте сводную диаграмму: выделите отчет с данными из 1С (или любую его ячейку), перейдите в меню Вставка -> Сводная диаграмма. На самом деле, способов добавить сводную диаграмму несколько. Мы с вами рассматриваем самый очевидный – аналогичный добавлению простой (не сводной) диаграммы.

Отформатируйте цифры в таблице — задайте числовой формат (выделить ячейки правой кнопкой мышки -> Числовой формат). Должна получиться такая диаграмма:

Задайте параметры ряда – перекрытие столбцов 100%, боковой зазор 40%.

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

Читайте также: