Как сделать анализ чувствительности проекта в excel?

Кадр из кинофильма «Чапаев». Реж. братья Васильевы

Термин «Анализ чувствительности» для неопытных аналитиков регулярно становится камнем преткновения. Часто начинающие аналитики даже не могут понять, о чем их просят.

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

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

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

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

Возможен и обратный метод, путем подгонки результата модели при контроле за изменением параметров на входе – например, таким способом:

• — Василий Иванович, а ты армией командовать могёшь?
• — Могу.
• — А фронтом?
• — Могу, Петька, могу.
• — А всеми вооружёнными силами Республики?
• — Малость подучиться, смогу и вооружёнными силами.

— Ну, а… в мировом масштабе, Василий Иванович, совладаешь?

— Нет, не сумею, языков не знаю.

Герой классического кинофильма С.

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

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

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

Можно также сделать производные показатели и анализировать чувствительность к ним.

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

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

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

Но насколько быть уверенным в этом значении и что, если мы определили его ошибочно? Анализ чувствительности отвечает на этот вопрос: мы можем оценить, как меняется стоимость фирмы при изменении цены продукта в границах от, предположим, 50 до 150 и себестоимости от 65% до 85%.

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

Предположим, что инвестор ожидает доходность индивидуальной инвестиции в диверсифицированном портфеле за 5 периодов не менее чем x10 в дополнение к возврату стоимости собственного капитала на уровне, допустим, 15% (о роли мультипликаторов и диверсификации см. раздел «Портфель венчурного фонда: Какие стартапы нужны профессиональным инвесторам»).

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

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

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

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

Последовательность создания матрицы инструментом Data Table следующая:

1. Поместить в верхнюю левую ячейку будущей ячейки вызов целевого значения модели (в нашем случае =B32);
2. Поместить по горизонтали от вызова целевого значения модели ряд значений первой переменной, которые вы хотите перебрать в модели (в нашем случае, фактор себестоимости отложен по горизонтали);
3. Поместить по вертикали от вызова целевого значения модели ряд значений второй переменной, которые вы хотите перебрать в модели (в нашем случае, цена единицы продукта отложена по вертикали);
4. Если вы проводите анализ только по одной переменной, вы ограничиваетесь либо пунктом 3, либо пунктом 4. Последовательность переменных не важна, выбирать, какую из них откладывать по горизонтали, а какую по вертикали, имеет смысл только с учетом числа шагов каждой переменной – по вертикали их помещается больше;
5. Выделить весь массив будущей матрицы чувствительности;
6. Вызвать функцию командой меню Data-Table или выделенной иконкой на панели или ленте;
7. Ввести в первое окно диалога «переменную ряда» – то есть ту ячейку, откуда модель, а не таблица данных, считывает переменную «фактор себестоимости» (в нашем случае, B4)
8. Ввести вo второе окно диалога «переменную колонки» – то есть ту ячейку, откуда модель, а не таблица данных, считывает переменную «фактор себестоимости» (в нашем случае, B4)
9. Если вы проводите анализ только по одной переменной, вводите адрес только для той переменной модели, ряд переменных значений которой была вами отложена по горизонтали – для горизонтального ряда в окно «ряд», для вертикальной колонки в окно «колонка»
10. Нажмите OK. Выделенное пространство будет заполнено значениями целевого показателя модели, рассчитанными для данной пары значений переменных при прочих равных (при расчете по одной переменной, вы получите ряд значений целевого показателя для значений одной переменной при прочих равных). В нашем примере, значение 6.38 в ячейке С37 означает, что мультипликатор доли инвестора при цене продукта 50 и себестоимости в 65% от продаж составит 6.38.

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

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

Мы использовали здесь команду «Условное форматирование», позволяющей изменить стиль ячейки в зависимости от того, отвечает ли ее содержание заданному критерию В данном случае, мы сравниваем значение ячейки с значением именованного массива mult, содержащего целевое значение инвестиционного мультипликатора, по следующему алгоритму:

Отклонения от целевого значения мультипликатора более чем на 1 в большую сторону отмечаются ЗЕЛЕНОЙ заливкой – это пространство, где решение можно уверенно принять Отклонения от целевого значения мультипликатора не более чем на ±1 отмечаются ЖЕЛТОЙ заливкой – это пространство, где могут возникнуть колебания, стоит или не стоит принимать решение Отклонения от целевого значения мультипликатора более чем на 1 в меньшую сторону отмечаются КРАСНОЙ заливкой – это пространство, где решение можно уверенно отклонить.

Настройка цвета выполняется диалогом Format-Conditional Formatting:

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

Финансовая модель инвестиционного проекта в excel

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

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

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

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

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

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

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

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

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

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

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

Чем выше NPV проекта, тем он привлекательнее (при учете рисков).

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

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

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

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

Заполните таблицу «Денежные потоки»:

• в ячейку В9 введите значение первоначальных инвестиций,
• в ячейку В10 — формулу «=B8-B9»
• в ячейку С8 введите сумму поступлений в первый год,
• в D8 – формулу «=C8*1,3»,
• в С9 — «=C8*0,8»,
• протяните формулу из ячейки D8 вправо до 2019 года, рассчитайте итоговое значение;
• протяните вправо формулы из ячеек С9 и В10,
• протяните формулу из ячейки G8 на две ячейки вниз.
• В ячейку В11 формулу «=B10», в ячейку С11 формулу =B11+C10, протяните ячейку С11 вправо до F11, сверьте значение в ячейке F11 cо значением в G10.

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

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

Для расчёта срока окупаемости в примере Excel введите в ячейку В17 формулу «=СЧЁТЕСЛ�?(B11:G11;»

Анализ чувствительности инвестиционного проекта. Формулы. Расчет по балансу

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В процессе анализа определяется эффективность проекта.

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

ВАЖНО! При рассмотрении проектов предпочтение отдается самым прибыльным и безрисковым. Кроме того, большое значение имеют социальное и экологическое значение инвестиционных проектов

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

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

Серьезный анализ инвестиционного проекта помогает обезопасить инвестора.

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

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

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

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

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

Показатель	Расчет	Значение
Дисконтированный доход (NPV)	Делим всю сумму денег за конкретный период на ставку дисконтирования плюс 1 за тот же период	Больше 0 – выгодный проект, прибыль Равно 0 – нет ни прибыли, ни убытка Меньше 0 – не выгодно, убытки
Индекс доходности (PI)	Это частное от деления первого показателя на сумму первоначальных вложений	Меньше 1 – не рассматриваем проект Равно 1 – нужна доработка Больше 1 – рассматриваем проект дальше
Период окупаемости (РР)	Самый простой способ – разделить величину инвестиций на чистую прибыль по итогам года	Инвестор сам определяет подходит ли ему получившееся значение
Рентабельность (ROI)	Вычитаем из дохода себестоимость и делим полученную сумму на величину инвестиций. Показатель выражаем в процентах	Больше 100% – получена прибыль Меньше 100% – убыток

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

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

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

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

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

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

Пример анализа показателей проекта

Рассмотрим проект, касающийся реконструкции производственного оборудования.

Показатель	До усовершенствования	После усовершенствования
Выпуск продукции, т	290000	300000
Себестоимость, т.р.	21686,67	20913,19
Цена, р	2360	23600
Прибыль, т.р	555155,70	806043

Ставка дисконтирования 11%.

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

Анализ не выявил чувствительности к изменению цены на 10%, а при снижении на 20% показатель «ушел в минус». Нужно найти свою нишу для регулирования цены. Нет чувствительности к понижению величины реализации.

Так проводится анализ влияния каждого первичного фактора на эффективность.

Вопросы по рассматриваемой теме

1. В какой ситуации можно говорить об удовлетворительных показателях баланса?
2. Должны соблюдаться следующие условия:
3. – более 10% собственных средств в активах
4. – собственные средства составляют около половины валюты баланса
5. – дебиторская и кредиторская задолженность должны расти в одинаковом темпе
6. – рост активов должен превышать рост пассивов
7. – по разделам должны выполняться неравенства:
8. 3>1
9. Если 31
10. (3-1)>0,1*2
11. 2>5

Финансы в Excel

Подробности Создано 12 Ноябрь 2015

Загрузки Магазин FAQ

Программа Мастер проектов. Предварительная оценка. Версия 5 — это совместная разработка интернет-проекта Финансы в Excel и Консультационной группы «Воронов и Максимов». Программа предназначенную для быстрой оценки эффективности инвестиционных проектов на стадии предварительных исследований.

Рабочий файл программы представляет собой книгу электронных таблиц с набором диаграмм. Функционирует в среде Microsoft Excel версии 2000 и более поздних, как в среде Windows, так и под управлением MacOS (Excel for Mac 2011 и старше).

Введение

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

• Экспресс-анализ инвестиционных альтернатив.
• Комплексная оценка эффективности инвестиций на основании минимума необходимой исходной информации.
• Предварительная экспертиза проектов.

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

В программе Мастер проектов. Предварительная оценка достигнуто уникальное сочетание оперативности и точности оценки инвестиционной привлекательности проектов и коммерческих предложений. В частности:

• Полный цикл анализа занимает от 5 до 20 минут, включая подготовку и распечатку краткого отчета-резюме, иллюстрированного диаграммами.
• Четко структурированный блок подготовки исходных данных состоит из пяти ключевых разделов. Таким образом, окончательный результат может быть получен на основании всего лишь нескольких цифр, описывающих проект.
• Расхождение с результатами более детального анализа в большинстве случаев не превышает 10-15 процентов.

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

Показатели, рассчитываемые без учета стоимости капитала:

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

Показатели, рассчитываемые с учетом стоимости капитала:

• Чистая современная ценность инвестиций (NPV).
• Индекс доходности инвестиций (PI).
• Дисконтированный срок окупаемости инвестиций.
• Внутренняя ставка доходности инвестиций (IRR).
• Максимальная ставка кредита.

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

• Объем продаж.
• Цены на продукцию.
• Постоянные инвестиционные затраты.
• Переменные производственные затраты.
• Постоянные производственные затраты.

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

Установка программы

Демонстрационная и коммерческая версии программы работают в операционных системах Windows и MacOS, с предварительно установленным Microsoft Excel (2000-2016 для Windows или Excel for Mac 2011).

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

• mptr5.demo.xls — рабочий файл в формате Excel

Демонстрационная версия работает в всех версиях Excel 2000 и старше. При желании можно сохранить файл в формате XLSM (Excel 2007 с поддержкой макросов).

Скачайте файл на персональный компьютер откройте его в Excel.

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

Запуск программы

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

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

После успешного запуска программы откроется Excel-файл программы. Откроется основной лист с информацией о версии программы и авторских правах.

Для нормальной работы программы необходимо подключить макросы Excel.

Состав файла

Программа представляет собой файл формата Excel с набором макросов (программой на VBA).

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

Файл включает один рабочий лист и три диаграммы:

• MAIN — главный лист модели.
• DIAG1 — диаграмма.
• DIAG2 — диаграмма.
• DIAG3 — диаграмма.

На листе MAIN данные сгруппированы в отдельные блоки-таблицы. Для отображения итогов таблиц можно использовать страндартное интерфейсное средство показа и скрытия строк — кнопки «+/-» в блоке структуры листа.

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

Главный лист (MAIN)

Главным листом программы Мастер проектов. Предварительная оценка является лист MAIN.На этом листе расположены семь основных таблиц модели:

1. Информация о проекте
2. Информация для настройки модели
3. Исходная информация
4. Промежуточные результаты
5. Оценка проекта без учета стоимости капитала
6. Оценка проекта с учетом стоимости капитала
7. Анализ чувствительности

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

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

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

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

Параметры

В таблице I рабочего файла программы вводится информация о выполеняемом расчете:

• Организация
• Автор проекта
• Название, версия проекта
• Дополнительное описание

В таблице II настраиваются параметры, влияющие на алгоритм расчета:

• Денежная единица — расчетная валюта
• Интервал планирования — месяц, квартал, полугодие, год или другой. Для последнего возможен ввод произвольной Продолжительности интервала в днях.
• Количество интервалов планирования — настройка автоматически изменяет Горизонт исследования*

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

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

• Постоянные инвестиционные затраты.
• Выручка от реализации.
• Переменные производственные затраты.
• Постоянные производственные затраты.
• Потребность в чистом оборотном капитале.

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

Некоторые исходные данные могут вводиться в нескольких строках. Для настройки количества таких справочников имеются специальные ячейки в столбце «C». Настраиваются следующие параметры проекта:

• Количество видов оборудования
• Количество видов продукции
• Количество видов переменных затрат
• Количество видов прочих постоянных производственных затрат

Во всех настраиваемых параметрах допускается изменять наименование элемента в столбце «В».

Результаты

Результаты расчета инвестиционного проекта сгруппированы в трех таблицах:

IV. Промежуточные результатыV. Оценка проекта без учета стоимости капитала

• VI. Оценка проекта с учетом стоимости капитала
• Ставка дисконтирования для оценки инвестиций с учетом стоимости капитала вводится в соответствующую ячейку таблицы VI.

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

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

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

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

• Дисконтированный срок окупаемости инвестиций равен горизонту исследования
• Чистая современная ценность инвестиций равна нулю

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

Кроме того, вызов макроса выполнения анализа чувствительности может осуществляться с помощью кнопки √, в правой части заголовка таблицы VII.*

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

Диаграммы

В состав программы Мастер проектов. Предварительная оценка входят три диаграммы, расположенные каждая на отдельном листе. Название листа состоит из слова «DIAG» и порядкового номера диаграммы:

1. «Чистые денежные потоки».
2. «Зависимость чистой современной ценности инвестиций от ставки сравнения» или «Зависимость дисконтированного срока окупаемости инвестиций от ставки сравнения».
3. «Зависимость чистой современной ценности инвестиций от ключевых исходных параметров проекта» или»Зависимость дисконтированного срока окупаемости инвестиций от ключевых исходных параметров проекта».

Название второй и третьей диаграммы зависит от выбранного условия анализа чувствительности.

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

Об авторах

Консультационная группа «Воронов и Максимов» создана в 1996 году специалистами, имеющими значительный опыт работы в консалтинговом бизнесами, авторами-разработчиками известных в России программных продуктов.Основные направления деятельности группы — консалтинг в сфере финансового менеджмента, разработка корпоративных информационных и аналитических систем, обучение современным методам управления предприятиями.

Группой разработаны уникальные компьютерные программы: для финансового анализа и планирования — «МАСТЕР ФИНАНСОВ», для планирования и оценки инвестиционных проектов — «МАСТЕР ПРОЕКТОВ», а также ряд эксклюзивных систем различного профиля.

* В демонстрационной версии программы эта операция отключена.

Загрузки Магазин FAQ

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

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

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

Алгоритм определения устойчивости достаточно прост: входные параметры по отдельности или их основная часть в проекте (объём реализации продукции; ее цена; размер инвестиций; сроки строительства; операционные затраты; уровень инфляции; барьерная ставка) изменяются в заданных пределах в бизнес-плане (раздел Анализ чувствительности проекта) и анализируются полученные результаты. Если результирующие показатели демонстрируют небольшие и некритичные изменения параметров эффективности, то устойчивость инвестиционного проекта признается допустимой. Одновременно анализ выявляет наиболее воздействующие на результирующие показатели входные параметры.

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

Наиболее сложным является имитационное моделирование инвестиционного процесса, так называемый метод Монте-Карло.

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

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

Для этого выбираем конкретный входной параметр проекта и изменяем его с определенным шагом в допустимых пределах. Эти допустимые пределы определяют эксперты.

Например, цена на производимую продукцию не может падать более чем на 50% от запланированной и не быть более чем плановая на 40%.

Далее рассчитываем финансовую модель проекта с каждым из этих измененных параметров. Полученные результаты NPV, IRR, PB, DPB или другие показатели размещаем в таблицу такого вида:

Показатели эффективности	Изменение цены от планового уровня, в %
-50	-40	-30	-20	-10	10	20	30	40
NPV, млн. руб.	-1150	-531	64	641	1206	1765	2314	2857	3390	3923
NPV, в %	-165	-130	-96	-64	-32	31	62	92	122
IRR, в %	5	12	19	25	31	36	42	47	52	56
PB, лет	6,17	5,33	4,92	4,33	4,33	3,92	3,92	3,42	3,33	3,33
DPB, лет	—-	—-	6,5	5,92	5,33	4,92	4,33	4,33	3.92	3,92

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

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

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

Так же интересно будет узнать про инвестиционный анализ.

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

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

Проект по строительству теплоэлектростанции в пос. Томилино.

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

Определим чувствительность проекта к изменению цены на рынке электроэнергии. Цена единицы продукции в первый год работы станции равна 3,76 рубля за 1 киловатт/час.

Изменение цены от планового уровня, в %
-50	-40	-30	-20	-10	10	20	30	40
1,88	2,26	2,63	3,01	3,38	3,76	4,14	4,51	4,89	5,26

Проводим расчеты в бизнес-плане по каждой цене киловатт/часа и сводим данные в таблицу:

Показатели эффективности	Изменение цены от планового уровня, в %
-50	-40	-30	-20	-10	10	20	30	40
NPV, млн. руб.	-22	231,27	478	731,64	978,5	1 232,02	1 485,55	1 732,40	1 985,92	2 232,78
NPV, в % от запланированного	-101	-81	-61	-40,7	-20,6	20,5	40,6	61,1	81,2
IRR, в %	-0,4	3,8	7,4	10,70	13,6	16,40	19,00	21,40	23,80	26,00
PB, лет	11	9	8	7	7	6	6	6	5	5

Из данных таблицы получаем критическую точку проекта 1, 88 руб. за 1 киловатт/час. Она является критичной еще в связи с IRR близкой к 0. Для данного проекта минимально допустимой является цена 1 киловатт/часа в 1, 88 рубля при которой NPV=0.

Изменение цены от планового уровня, в %
-80	-70	-60	-50	-40	-30	-20	-10
25,6	38,4	51,2	64	77	90	102	115	128

Проводим расчеты в бизнес-плане по каждому размеру годового объема выработки электроэнергии и сводим данные в таблицу:

Показатели эффективности	Изменение годового объема выработки электроэнергии при выходе на плановый уровень, в %
-80	-70	-60	-50	-40	-30	-20	-10	10	20
NPV, млн. руб.	-14	104,5	223	415	660	823	905,21	1068	1 232,02	1 485,55	1 732,40
NPV, в % от запланированного	-100	-91,2	-82	-66	-46,4	-33,2	-26,5	-13,3	20,5	40,6
IRR, в %	-0,3	2	4,9	7,4	10,6	12,4	13,30	14,9	16,40	19,00	21,40
PB, лет	11	10	9	8	7	7	7	6	6	5	5

По данному расчету чувствительности критическая точка находится при загрузке оборудования в 20% от плановой ее загрузки в 80%, т.е в 16% реальной загрузки. Надо отметить что к данному показателю NPV менее чувствительна в сравнении с ценой реализации электроэнергии( киловатт/часа).

Наконец третий входной параметр. Процентная ставка кредита в проект составляет 11% в год, рассчитаем изменение NPV при ее изменении с шагом в 1%.

Показатели эффективности	Изменение процентной ставки по кредиту, в %
16	15	14	13	12	11	10	9	8	7
NPV, млн. руб.	775	864	959	1 062	1294	1 232	1 423	1 563	1 715	1 880
NPV, в % от запланированного	-37	-30	-22	-13,8	-5,6	15,5	27	39,2	52,6
IRR, в %	12,3	13,2	14,2	15,20	17,3	16,4	18,30	19,40	20,50	21,60
PB, лет	7	7	6	6	6	6	6	6	6	6

Критическая точка находится в районе кредитной ставки на уровне 25% годовых.

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

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

Читайте так же про анализ анвестиционной деятельности предприятия.

• Читайте далее
• Что такое проектное финансирование и какие организации им занимаются.
• Все о денежном потоке инвестиционного проекта.
• Паспорт инвестиционного проекта, его пример и экспертиза.
• Инвестиционные проекты и бизнес-план: главные особенности.

]]>
Как сделать анализ чувствительности проекта в excel?

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

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

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

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

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

По своей сути метод анализа чувствительности – это метод перебора: в модель последовательно подставляются значения параметров. К примеру, мы хотим узнать, как изменится стоимость фирмы при изменении себестоимости продукции в пределах 60-80%.

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

Основные целевые измеримые показатели финансовой модели:

1. NPV (чистая приведенная стоимость). Основной показатель доходности инвестиционного объекта. Рассчитывается как разность общей суммы дисконтированных доходов и размера самой инвестиции. Представляет собой прогнозную оценку экономического потенциала предприятия в случае принятия проекта.
2. IRR (внутренняя норма доходности или прибыли). Показывает максимальное требование к годовой прибыли на вложенные деньги. Сколько инвестор может заложить в свои расчеты, чтобы проект стал привлекательным. Если внутренняя норма рентабельности выше, чем ожидаемый доход на капитал, то можно говорить об эффективности инвестиций.
3. ROI/ROR (коэффициент рентабельности/окупаемости инвестиций). Рассчитывается как отношение общей прибыли (с учетом коэффициента дисконтирования) к начальной инвестиции.
4. DPI (дисконтированный индекс доходности/прибыльности). Рассчитывается как отношение чистой приведенной стоимости к начальным инвестициям. Если показатель больше 1, вложение капитала можно считать эффективным.

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

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

Задача – проанализировать основные показатели эффективности инвестиционного проекта. Для примера возьмем условные цифры.

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

1. Рассчитаем денежный поток. Так как у нас динамический диапазон, понадобится функция СМЕЩ. При расчете учитываем ликвидационную стоимость (в нашем примере – 0, неизвестна). Расчет будем производить «без дат». То есть они не повлияют на результаты. Денежный поток в «нулевом» периоде равняется предынвестиционным вложениям. В последующих периодах: .
2. Для расчета срока окупаемости инвестиционного проекта (РР) создаем дополнительный столбец. В инвестиционный период будут суммироваться все дополнительные инвестиции за вычетом прибыли от суммы вложенных финансовых средств. Формула для «нулевого» периода: =СУММЕСЛИ(G7:G17;» 0;G8;0). Где Н7 – это прибыль предыдущего периода (значение в ячейке выше). G8 – денежный поток в данном периоде (значение ячейки слева).
3. Теперь найдем, когда проект начнет приносить прибыль. Или точку безубыточности: =ЕСЛИ(H7>=0;$C7;»»), где Н7 – это прибыль в текущем периоде (значение ячейки слева). С7 – это номер текущего периода (первый столбец).
4. Найдем рентабельность инвестиций. Это отношение прибыли в текущем периоде к предынвестиционным вложениям. Формула в Excel: =СУММ($H$7;H8)/-$H$7.
5. Рассчитаем коэффициент дисконтирования. Формула для нашего примера (где даты не учитываются): =1/(1+$B$1)^C7. В1 – ячейка с процентным выражением ставки дисконтирования. С7 – номер периода.
6. Найдем дисконтированную (приведенную) стоимость. Это произведение значения денежного потока в текущем периоде и коэффициента дисконтирования. Формула: =G7*K7.
7. Найдем индекс рентабельности (или дисконтированный индекс рентабельности). Аббревиатура – PI. Это отношение дисконтированной стоимости к начальным вложениям. Формула в Excel: =L8/-$G$7.
8. Найдем внутреннюю норму прибыли (IRR). Если даты не учитываются (как в нашем примере), воспользуемся встроенной функцией ВСД. Функция: =ВСД(G7:G17). Если даты учитываются, то подойдет функция ЧИСТВНДОХ. Посчитаем РР – срок окупаемости проекта. Для этой цели используем вложенные функции: . Или возьмем данные из таблицы.

• срок проекта – 10 лет;
• чистый дисконтированный доход (NPV) – 107228р. (без учета даты платежей, принимая все периоды равными);
• для нахождения данного значения возможно использование встроенных функций ЧПС и ПС (для аннуитетных платежей);
• дисконтированный индекс рентабельности (PI) – 1,54;
• рентабельность инвестиций (ROR) – 25%;
• внутренняя норма доходности (IRR) – 21%;
• срок окупаемости (РР) – 4 года.

Можно еще найти среднегодовую чистую (за вычетом оттоков) прибыль без учета инвестиций и процентной ставки: =(E18+СУММ(F7:F17))/C20. Где Е18 – сумма притоков денежных средств, диапазон F7:F17 – оттоки; С20 – срок инвестиционного проекта.

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