Анализ чувствительности как его сделать в excel • отчет по сценариям

Как включить анализ данных в Excel 2010, 2007, 2013

При выполнении сложных аналитических задач по статистике (к примеру, корреляционного и дисперсионног…

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

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

Выясним, как включить анализ данных в Excel 2007, 2010, 2013.

Для Excel 2010, 2013

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

Включение блока инструментов

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

1. зайдите во вкладку «Файл», расположенную в верхней части ленты интерфейса;
2. с левой стороны открывающегося меню найдите раздел «Параметры Эксель» и кликните по нему;
3. просмотрите левую часть окошка, откройте категорию надстроек (вторая снизу в списке), выберите соответствующий пункт;
4. в выпавшем диалоговом меню найдите пункт «Управление», кликните по нему мышью;
5. клик вызовет на экран диалоговое окно, выберите раздел надстроек, если выставлено значение, отличное от «Надстройки Excel», поменяйте его на обозначенное;
6. нажмите на экранную кнопку «Перейти» в разделе надстроек. В правой части выпадет список надстроек, которые устанавливает программа.

Поиск пакета в надстройках Excel

Активация

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

1. В перечне надстроек, выпавшем после последовательного выполнения предыдущих операций, пользователю надлежит поставить знак птички напротив раздела «Пакет анализа».
2. Выбрав активацию пакета, необходимо нажать клавишу «Ок», расположенную в верхней правой части диалогового окна.
3. После нажатия кнопки пакет появляется на ленте функций. Для получения доступа к нему в интерфейсе программы выбирается вкладка «Данные». В правой части меню «Раздел анализа». Там пользователь найдет иконку опции «Анализ данных».

Выбор нужной надстройки

Запуск функций группы «Анализ данных»

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

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

Расположение функции «Анализ данных» на вкладке «Данные»

Чтобы применить ту или иную опцию, действуют по нижеприведенному алгоритму:

1. Нажать на кнопку анализа на ленте.
2. Кликнуть по названию необходимой пользователю функции.
3. Нажать клавишу «Ок», находящуюся рядом с правым верхним углом окошка.
4. В диалоговом окне указать массивы данных, используемые для решения текущей задачи.

Как включить анализ данных в Excel 2010

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

Для Excel 2007

Алгоритм, как включить анализ данных в Excel 2007, отличается от остальных тем, что в самом начале (для выхода на параметры Excel) вместо кнопки «Файл» пользователь нажимает четырехцветный символ Microsoft Office. В остальном же последовательность операций идентична приведенной для других версий.

Анализ данных в Excel 2007: как включить?

Какие бывают инвестиционные проекты

Существует огромное разнообразие инвестиционных проектов. Все проекты можно разделить на типы и виды.

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

Все проекты могут быть систематизированы по трем видам:

Цели вложений	Сроки вложений	Объем вложений
Вложения инвесторов могут потребоваться на: – развитие социальных проектов	Инвестиции подразделяются в зависимости от того, на какой срок они осуществляются: – долгосрочные  (свыше 3 лет )	Вложения инвесторов распределяются и в зависимости от их объемов: – крупные вложения, более 1 млн. американских долларов. Доступны только крупным предприятиям. Это инвестиции в совершенно новый способ производства. Такая продукция нужна как на внутреннем, так и на внешнем рынке
– улучшение качества продукции	– среднесрочные (от 1 до 3 лет)	– средние, до 1 млн. американских долларов. Такие вложения направлены в основном на обновление уже имеющихся производств. Обычно такое обновление производится в несколько этапов
– наращивание объемов	– краткосрочные (от 1 до 3 лет)	– малые, сумма инвестиций в этом случае составляет до 100 тыс. американских долларов. Такие вложения касаются обычно расширения ассортимента и производства
– повышение разнообразия ассортимента
– снижение затрат на производство

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

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

Определение

В широком смысле, анализ чувствительности оценивает степень изменчивости выходного параметра к изменению одного из входных параметров при условии, что остальные входные параметры остаются неизменными. При анализе чувствительности инвестиционного проекта, как правило, оценивается воздействие изменения объема продаж, переменных затрат, постоянных затрат, ставки дисконтирования, ставки налога на прибыль и т.п. на его чистую приведенную стоимость (англ. Net Present Value, NPV). Помимо чистой приведенной стоимости проекта в качестве выходного параметра могут также использоваться внутренняя норма доходности (англ. Internal Rate of Return, IRR), дисконтированный срок окупаемости (англ. Discounted Payback Period) и т.п. Проведения этого вида анализа позволяет оценить устойчивость проекта к факторам риска.

Анализ чувствительности: используемый алгоритм

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

Несмотря на то что процедура имеет множество преимуществ, важно помнить и о недостатках:

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

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

1. На первом этапе нужно определиться с цифрой, относительно которой будут осуществляться все расчёты чувствительности. Чаще всего в формуле используется NPV. В редких случаях аналитик может задействовать норму прибыли IRR.
2. После этого выбираются элементы, не внушающие уверенности. Например, внушительные финансовые траты на капстроительство, вложения в оборотные средства. Помимо этого, эксперт, определяя чувствительность, может рассматривать и чисто рыночные факторы — стоимость, объем реализации.
3. На этом этапе устанавливаются максимальные цифровые значения рискованных зон с обеих сторон.
4. Только когда все подготовительные работы для анализа выполнены, можно приступать к расчёту DPI, IRR и чувствительности.
5. На финальном этапе эксперт занимается построением графика чувствительности. В документации подробно описываются зависимости для каждой составляющей, за счёт чего можно наглядно выявить критичные точки в разработанном инвестпроекте.

Анализ чувствительности

В пределах анализ чувствительности выделяют два метода:

1. метод вариации параметров;
2. метод критических точек.

Анализ чувствительности (метод вариации параметров, метод критических точек) предполагает, что:

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

В данном случае под риском подразумевается степень чувствительности основных результирующих показателей проекта (например, ЧДД) к изменению основных условий функционирования (цена продукта, налоговые платежи и т.д.). В зависимости от вероятности наступления события устанавливают границы (изменение налогов, например, может иметь диапазон +/- 10-15%, рост курса доллара +100%), используя показатель эластичности – процентное изменение итогового показателя проекта к изменению параметра функционирования на 1 %.

Чем выше эластичность – тем выше зависимость от данного конкретного параметра (изменение выручки, цены товара и т.д.) и тем выше риск по данному параметру. По совокупной картине параметров можно сделать вывод о степени устойчивости проекта к изменению внешней среды. Метод критических точек – это, по сути, дальнейшая работа в этом направлении: получаем предельные значения каждого параметра, при которых проект эффективен (ЧДД положителен, либо иное условие)» – объясняет Логинов Александр Леонидович.

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

Чувствительность чистого дисконтированного дохода к ценам на продукцию и операционным затратам

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

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

Пример проекта, для оценки рисков которого применялся анализ чувствительности, привел Константин Шабалин, генеральный директор краудинвестинговой площадки StartTrack, созданной при поддержке Фонда Развития Интернет Инициатив: «Оценка проекта или молодой компании при принятии решения об инвестировании является самой важной задачей. Обычно проект оценивается по 4 главным критериям, в число которых входит:

1. Потенциальная оценка рынка и то место, которое компания может на нем занять;
2. Оценка бизнес модели;
3. Оценка команды;
4. Условия сделки.

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

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

Так, для проекта «Footyball» (сеть футбольных клубов для дошкольников), представленного на краудинвестинговой площадке StartTrack, важнейшим фактором влияния на бизнес является количество школ-участников. Чем больше школ откроется, тем выше будет цена бизнеса и наоборот. Анализ чувствительности является своего рода crush-тестом Вашего бизнеса. Он позволяет детально определить риски проекта, связанные с ключевыми показателями эффективности. И в дальнейшем объективно обосновать их, избегая “спекуляций” с размером ставки дисконтирования».

Ключевые особенности анализа

Основная задача анализа — максимально правильно провести взаимосвязи между переменными данных и их ключевыми величинами. Затем оценить влияние чувствительности на результат. Смысл анализа чувствительности критериев инвестпроекта состоит в переборе: подстановке возможных значений в имеющуюся схему в определённой последовательности. Например, специалистам нужно узнать, как будет меняться чувствительность в ракурсе итоговой рыночной стоимости компании, если себестоимость изготавливаемой продукции возрастёт на 55−85%.

К базовым целевым измерениям параметров чувствительности финмодели относятся:

1. IRR — показатель внутренней нормы по доходности. Этот параметр чувствительности всегда отображает максимально допустимое требование к доходу, исчисляемому за год на вложенные ранее финансы. Именно анализ IRR должен учитываться инвесторами, чтобы знать, сколько средств можно заложить в расчёты, дабы созданная схема стала прибыльным. Эффективность вкладов констатируется, когда IRR по рентабельности превышает ожидаемую доходность на вложения.
2. NPV — аббревиатура чистой приведённой стоимости. Этот параметр чувствительности отображает доходность инвестобъекта. В расчётах используется размер инвестиций, а также разность суммы дисконтированной прибыли. Аналитики используют NPV для потенциала компании, с экономической точки зрения.
3. DPI — индекс, отображающий дисконтированную доходность. Схема расчёта его чувствительности основана на том, чтобы учитывать, как чистая стоимость проекта относится к стартовым инвестициям. Когда итоговый показатель превышает единицу, то вклады считаются эффективным.
4. ROR / ROI. Его чувствительность рассматривается экспертами как отношение итоговой суммы прибыли к первоначальным вложениям.

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

https://youtube.com/watch?v=gLFJaojir_M

Пример расчета

Менеджмент компании «ХимТех ЛТД» проводит анализ чувствительности инвестиционного проекта, предполагающего запуск новой производственной линии. Реализация этого проекта предполагает осуществление первоначальной инвестиции в размере 500 000 у.е., посленалоговая стоимость привлекаемого капитала составляет 16%, а ставка налога на прибыль 30%. Основные показатели проекта для базового сценария представлены в таблице.

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

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

• постоянных затрат;
• объема продаж;
• цены единицы продукции;
• переменных затрат на единицу продукции.

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

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

NPV = -500 000 + 131 034 + 136 891 + 160 164 + 137 686 + 111 030 = 176 805 у.е.

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

NPV = -500 000 + 128 017 + 134 238 + 157 810 + 135 579 + 109 113 = 164 757 у.е.

Процентное изменение NPV = (164 757 — 176 805) ÷ 176 805 × 100% = -6,81%

Таким образом, чувствительность NPV к изменению постоянных затрат составит -1,362.

Чувствительность NPV = -6,81% ÷ 5% = -1,362

Это означает, что при увеличении постоянных затрат на 1% чистая приведенная стоимость проекта будет уменьшаться на 1,362%, и наоборот, при снижении постоянных затрат на 1% чистая приведенная стоимость проекта будет увеличиваться на 1,362%.

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

NPV = -500 000 + 138 879 + 144 902 + 169 246 + 145 573 + 117 545 = 216 145 у.е.

Процентное изменение NPV = (216 145 — 176 805) ÷ 176 805 × 100% = 22,25%

Чувствительность NPV = 22,25% ÷ 5% = 4,450

Таким образом, при росте объема продаж на 1% NPV проекта будет увеличиваться на 4,450%, и наоборот, при снижении объема продаж на 1% NPV проекта будет снижаться на 4,450%.

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

NPV = -500 000 + 152 155 + 157 491 + 183 170 + 157 896 + 128 277 = 278 989 у.е.

Процентное изменение NPV = (278 989 — 176 805) ÷ 176 805 × 100% = 57,79%

Чувствительность NPV = 57,79% ÷ 5% = 11,558

При росте цены единицы продукции на 1% NPV проекта будет увеличиваться на 11,558%, и наоборот, при снижении цены единицы продукции на 1% NPV проекта будет снижаться на 11,558%.

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

NPV = -500 000 + 117 759 + 124 301 + 146 240 + 125 363 + 100 298 = 113 961 у.е.

Процентное изменение NPV = (113 961 — 176 805) ÷ 176 805 × 100% = -35,54%

Чувствительность NPV = -35,54% ÷ 5% = -7,109

При росте переменных затрат на единицу продукции на 1% NPV проекта будет уменьшаться на 7,109%, и наоборот, при снижении переменных затрат на единицу продукции на 1% NPV проекта будет увеличиваться на 7,109%.

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

Таблицы данных Excel для анализа чувствительности с одной переменной

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

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

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

Откройте инструмент «Таблица данных» в меню «Данные» > «Анализ «что если»» > «Таблицы данных».

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

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

Интеграция Excel и Statistica

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

Объекты библиотек Statistica полностью открыты и доступны для
всех совместимых сред программирования Visual Basic в других приложениях

Если вы хотите запустить анализ Statistica из Visual Basic
другого приложения (например, Excel), вам сначала нужно загрузить
необходимые библиотеки Statistica (например, с помощью параметра
«References» в меню «Tools »
редактора Visual Basic приложения), и, во-вторых, создать объект типа
Statistica.Application.

Затем вы можете создавать объекты анализа, которые являются
частью объекта Statistica.Application (нейросети, регрессию, графики).

Данный вариант доступен при наличии лицензии Statistica
Enterprise.

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

В этом способе не требуется лицензия Statistica Enterprise.

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

Выбираем файл, в котором содержатся данные.

Далее нужно выбрать вариант Открыть как Рабочую книгу Excel.

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

Помимо этого, наверху отображается строка, которая
предоставляет доступ ко всем модулям Statistica.

Анализ чувствительности инвестиционного проекта в Excel

Метод анализа чувствительности в сфере инвестиций

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

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

Формула

Чувствительность вложения можно вычислить с помощью этой формулы: Изменение выходного параметра в % / Изменение входного параметра в %.

Выходным и входным параметром могут быть величины, описанные ранее.

1. Необходимо узнать результат при стандартных условиях.
2. Заменяем одну из переменных и следим за изменениями результата.
3. Вычисляем процентное изменение обоих параметров относительно установленных условий.
4. Вставляем полученные проценты в формулу и определяем чувствительность.

Пример анализа чувствительности инвестиционного проекта в Excel

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

10

1. Заполним таблицу, чтобы анализировать проект по ней.

11

1. Вычисляем денежный поток с помощью функции СМЕЩ. На начальном этапе поток равен вложениям. Далее применяем формулу: =ЕСЛИ(СМЕЩ(Номер;1;)=2;СУММ(Приток 1:Отток 1); СУММ(Приток 1:Отток 1)+$B$5)Обозначения ячеек в формуле могут быть другими, это зависит от размещения таблицы. В конце прибавляется значение из начальных данных – ликвидационная стоимость.

12

1. Определяем срок, за который проект окупится. Для начального периода используем эту формулу: =СУММЕСЛИ(G7:G17;»<0″). Диапазон ячеек – это столбец денежного потока. На дальнейших периодах применим эту формулу: =Начальный период+ЕСЛИ(Первый д.поток>0; Первый д.поток;0). Проект оказывается в точке безубыточности за 4 года.

13

1. Создаем столбец для номеров тех периодов, когда проект окупается.

14

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

15

1. Определяем коэффициент дисконтирования по этой формуле: =1/(1+Ставка диск.%) ^Номер.

16

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

17

1. Рассчитаем PI (индекс рентабельности). Приведенная стоимость в отрезке времени делится на вложения в начале развития проекта.

18

1. Определим внутреннюю норму прибыли с помощью функции ВСД: =ВСД(Диапазон денежного потока).

Пример анализа чувствительности проекта

1. Для начала необходимо определить значение NPV без учета влияния факторов:

руб.

2. Далее необходимо определить, какой будет чистый дисконтированный доход, если, например, цена увеличится на 20%. Все остальные факторы не меняются.

Цена (+20%) = руб.

руб.

3. Аналогично рассчитываются значения NPV для других отклонений факторов. Например, при изменении цены на -20%, -10%, +10%, +20%.

4. Далее аналогично определяется влияние других факторов на NPV.

а) чувствительность к изменению уровня цен

Из таблицы видно, что при увеличении цены всего на 20%, чистый дисконтированный доход проекта увеличивается на 378%. Аналогично снижение цены на 20% вызывает падение NPV на 378%. Таким образом, проект очень чувствителен к изменению цены

И инвестору необходимо уделять большое внимание этой проблеме

б) чувствительность к изменению уровня средних переменных затрат

в) чувствительность к изменению уровня общих постоянных затрат

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

Анализ чувствительности инвестиционного проекта в Excel

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

• ликвидационная стоимость – 0 руб.;
• отток денежных средств в каждом периоде – 20 тыс. руб. (со знаком «–»);
• прединвестиционные затраты – 400 тыс. руб. (в таблице нужно показать со знаком «минус»);
• приток денежных средств в каждом периоде – 90 тыс. руб.;
• ставка дисконтирования – 10 процентов.

Ниже приведем пошаговую инструкцию.

Шаг 1. Денежный поток

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

=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;;)

Формула для нашего примера (первый период):

=ЕСЛИ(СМЕЩ(A3;1;)=2;СУММ(C3:D3);СУММ(C3:D3)+’Исходные данные’!$B$5)

Здесь «Исходные данные» — стартовая информация для вычислений, которую программа подтягивает с соседнего листа. Сведения можно разместить на той же странице, что и вычисления. Тогда вместо наименования листа будет стоять номер конкретной ячейки.

Шаг 2. Точка безубыточности (в денежном эквиваленте)

Чтобы узнать, при каком объеме производства доходы организации будут равны ее расходам, используем функцию «СУММЕСЛИ». Но формула подойдет только для нулевого периода:

=СУММЕСЛИ(E2:E12;»<0″)

Далее необходимо будет применить другую формулу. Например, для первого периода в нашем примере ее можно представить так:

=F2+ЕСЛИ(E3>0;E3;0)

Шаг 3. Срок окупаемости

Проект из нашего примера начнет приносить прибыль компании в шестом периоде. Установить это удалось с помощью функции «ЕСЛИ»:

=ЕСЛИ(F8>=0;$A8;»»)

Шаг 4. Рентабельность инвестиций (ROR)

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

=СУММ($F$2;F3)/-$F$2

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

Шаг 5. Коэффициент дисконтирования

Рассчитаем коэффициент без учета дат платежей:

 =1/(1+’Исходные данные’!$B$1)^A2, где:

• ‘Исходные данные’!$B$1 – ставка дисконтирования;
• А2 – период;

Шаг 6. Приведенная (дисконтированная) стоимость

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

=E2*I2 (для нулевого периода)

Шаг 7. Индекс рентабельности (PI)

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

=J3/-$E$2

Шаг 8. Внутренняя норма доходности (IRR)

Для вычисления будем использовать формулу «ВСД», так как не учитываем даты платежей. В противном случае мы бы воспользовались функцией «ЧИСТВНДОХ». Для расчета нужно знать лишь денежный поток.

=ВСД(E2:E12)

Шаг 9. Прочие показатели

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

=(C13+СУММ(D2:D12))/B15

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

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

=ЕСЛИ(J13<0;»не окупается»;(ПОИСКПОЗ(ВПР(0;F2:F12;1;ИСТИНА);F2:F12))-((ИНДЕКС(F2:G12;ПОИСКПОЗ(ВПР(0;F2:F12;1;ИСТИНА);F2:F12)+1;1))/((ABS(ВПР(0;F2:F12;1;ИСТИНА)))+ИНДЕКС(F2:F12;ПОИСКПОЗ(ВПР(0;F2:F12;1;ИСТИНА);F2:F12)+1;1))))

Шаг 10. Вывод

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

Таблица. Итоги анализа чувствительности проекта

Срок проекта	10 лет
Чистый дисконтированный доход	30 120 руб.
Дисконтированный индекс рентабельности	1,08
Рентабельность инвестиций	-43%
Внутренняя норма доходности	12%
Срок окупаемости	5,7 лет

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

Что такое инвестиционный проект

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

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс “Оценка инвестиционных проектов с нуля в Excel” от Ждановых. Получить доступ

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

1. Конкретная цель проекта, то есть что именно будет сделано на полученные деньги
2. Точная сумма привлекаемых средств, с пояснениями и расшифровками
3. Величина отдачи от проекта
4. В какие сроки проект будет осуществлен и окупится

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

Например. Организация решила производить эко продукты. Для такого начинания компании требуется 5 млн. рублей. Первая партия продуктов будет произведена через 2 месяца, реализация произойдет в те же сроки. Это информация получена на основе маркетингового исследования рынка. Инвестор будет получать 2% с единицы товара.

Так может выглядеть инвестиционный проект в нескольких предложениях.

Оценка стоимости бизнеса	Финансовый анализ по МСФО	Финансовый анализ по РСБУ
Расчет NPV, IRR в Excel	Оценка акций и облигаций

Жизненный цикл инвестиционного проекта

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

Всего у каждого проекта можно выделить три этапа:

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

Каждый проект проходит такой жизненный цикл.

Расчет эффективности инвестиционного проекта по балансу

Для расчета эффективности проекта можно использовать данные баланса.

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

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

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

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