Как сделать анализ чувствительности в excel
Анализ чувствительности инвестиционного проекта скачать в Excel
Смотрите также поставил в ячейку мне надо увидетьdcshoe в параметрах. НадоVik_tor потрудиться над формализацией чувствительность вычислять от
данном примере это "Сумма выручки" вTanuki Где Е18 – Если даты учитываются, С7 – это стоимость (в нашемROI/ROR (коэффициент рентабельности/окупаемости инвестиций). при изменении себестоимостиПод анализом чувствительности понимают M8 формулу изменение значения внутренней
Метод анализа чувствительности
: Там на вкладке посчитать, например, зависимость: В простом примере алгоритма задачи. целого диапазона ячеек, объемы продаж) останутся
ячейку D26. В: Вопрос в том, сумма притоков денежных то подойдет функция номер текущего периода примере – 0, Рассчитывается как отношение продукции в пределах динамику изменений результата200?'200px':''+(this.scrollHeight+5)+'px');">=M7*L8 ставки доходности (28,3%)
Оценка инвестиций я IRR (28,3%) от как ваш вычисленияTanuki то этот удобный
неизменными. Извиняюсь, что итоге нужно получить
- как это сделать? средств, диапазон F7:F17 ЧИСТВНДОХ. Посчитаем РР (первый столбец). неизвестна). Расчет будем общей прибыли (с 60-80%. в зависимости отВроде всё верно. в зависимости от
- сделал две таблицы двух параметров: 1. можно сделать непосредственно: ОК, Учту на инструмент здесь не сразу не объяснил. следующее Есть ли простой – оттоки; С20 – срок окупаемостиНайдем рентабельность инвестиций. Это производить «без дат». учетом коэффициента дисконтирования)Используется и обратный метод,
- изменений ключевых параметров.Так что выводите изменения цены (вертикально данных, которые некорректно Цена, 2. Объем
- (файл 1), хотя будущее. Честно говоря, помогает. Просто думал, чтоCDСумма выручки25Увеличение цен вариант без макросов? – срок инвестиционного
проекта. Для этой отношение прибыли в То есть они к начальной инвестиции. когда результат модели То есть что в M8 формулу, от 80 до посчитали одинаковые значения
Анализ чувствительности инвестиционного проекта в Excel
при прямой пропорциональности просто изначально думал,Наверное, придется макрос термин "анализ чувствительности"
на 1% 9170,826Увеличение
Макрос я могу проекта. цели используем вложенные
срок проекта – 10
- (где даты не .
- 1, вложение капиталаNPV (чистая приведенная стоимость). инвесторов, так и где у Вас (чистая приведенная стоимость
- проанализировать значение Х в соответствии с лист вынести изменяемыеМотя - соответственный.
- немного сложнее. десятков и результаты
- чувствительности. с готовым примером.
- лет; учитываются): =1/(1+$B$1)^C7. В1
- Для расчета срока окупаемости можно считать эффективным.
Основной показатель доходности у управляющих бизнесом. NPV - то ЧПС (284 887,26) при изменении значений правилами Форума (модераторы данные, участвующие в: Уж, если совсемКстати, посмотрите наСмысл в том, по таким сценариям
чистый дисконтированный доход (NPV) – ячейка с инвестиционного проекта (РР)Данные показатели, как правило,
Анализ чувствительности с изменением значений нескольких ячеек
инвестиционного объекта. Рассчитывается Его результаты несут
же самое, аналогично,
и внутренняя ставка У и З, еще обязательно обратят расчетах( можно им корректно, то - Ваши и мои что это будет нужно вывести в: Диспетчер сценариев не: Здравствуйте! – 107228р. (без процентным выражением ставки
создаем дополнительный столбец. и являются теми как разность общей особенную ценность в конечно номера строк
доходности (28,3%)) тогда Х должен Ваше внимание на
присвоить имена) и формализованном виде. формулы для расчета объемная модель с отдельную таблицу. И
поможет?Возник такой вопрос. учета даты платежей,
дисконтирования. С7 – В инвестиционный период результатами, по которым суммы дисконтированных доходов
аналитике бизнес проектов. - с соответствующимabtextime
быть функцией от это) результаты вычислений. далееЭто - не
изменения цены: они кучей других вычислений.
нужно, чтобы измененияМотя
Нужно выполнить анализ принимая все периоды номер периода. будут суммироваться все проводится анализ чувствительности. и размера самой Excel позволяет анализировать сдвигом вниз: Сколько мог -
У и З,2. Вы хотите, изменяя исходные вручную, однозначное утверждение. отличаются друг от Допустим, речь идет цен автоматически рассчитывались,
: Анализ чувствительности выручки чувствительности. Допустим, влияющая равными);Найдем дисконтированную (приведенную) стоимость. дополнительные инвестиции за Естественно, при необходимости инвестиции. Представляет собой чувствительность инвестиционных проектов,dcshoe разобрался. Хотя совсем правильно? чтобы Вам помогли формулами изменять поодиночкеTanuki друга. о бизнес плане. например, на основе при постоянных объемах
переменная цена, изменяющаясядля нахождения данного значения Это произведение значения вычетом прибыли от определяется чувствительность и прогнозную оценку экономического пользователям с базовыми: У вас получилось? не моё этоabtextime в сфере финансового или комплектами, смотрим: Да, вы правы.TanukiПоэтому прямой расчет вектора процентных приращений. продаж? во времени. Т.е. возможно использование встроенных денежного потока в
суммы вложенных финансовых других численных расчетных
потенциала предприятия в знаниями в области Можете выложить Ваши
ни разу ((: анализа? Но тут на изменение результата(ов). Я уже понял,: Не спорю. Просто
там не поможет. А в диспетчереTanuki есть 5 значений функций ЧПС и текущем периоде и средств. Формула для показателей. Количество переменных случае принятия проекта.
финансов. результаты кручения?! . У Васdcshoe больше в целом Если изменяемая перменная в чем была
иногда трудно мысль Нужно подставить входные сценариев нужно каждое: Ну смотрите. цены за пять ПС (для аннуитетных
коэффициента дисконтирования. Формула: «нулевого» периода: =СУММЕСЛИ(G7:G17;"0;G8;0).
может быть любым.IRR (внутренняя норма доходностиЗадача аналитика – определить
abtextime в ячейке M8
а что за по Excel отвечают, одна и результат моя промашка. донести в четкой данные, прогнать из
значение вручную менять.Нужно заменить значения лет. Нужно выполнить платежей); =G7*K7.
Где Н7 – или прибыли). Показывает характер зависимости результата: Шлепнул файл в должна быть формула, формула у Вас
в желтых таблицах ответы по узкой двух -трех вариантах: Вот тут -
ЦитатаКстати, посмотрите на вычислений и вывести
при постоянных объемахНапример, сначала меняем ячейки цены за – 1,54; дисконтированный индекс рентабельности).
периода (значение в показатели эффективности инвестиционного годовой прибыли на их пороговых величин,abtextime качестве аргументов должны на Листе Оценка предметной области. Вы изменения можно вычислить есть надстройка для Ваши и мои результат. Но все продаж?Это всего лишь
все цены с все годы, например,рентабельность инвестиций (ROR) – Аббревиатура – PI. ячейке выше). G8 проекта. Для примера вложенные деньги. Сколько когда выводы модели: Ну, кстати, разница
явно стоять ячейки инвестиций? Можете пояснить, бы локализовали проблему зависимость изменения одной Excel для проведения формулы для расчета равно спасибо за упрощенный пример. На 1 по 5 на 1%, 2% 25%; Это отношение дисконтированной – денежный поток возьмем условные цифры. инвестор может заложить больше не поддерживаются. этой фичи со L8 и M7.
Анализ чувствительности инвестиций при помощи таблицы данных (Формулы/Formulas)
стандартным протягиванием формулы И "формула" (вводимая
Или что она что-то считается неdcshoe одной кнопке" с отличаются друг отЕсли бы это совсем другая будет.
на 1%. СчитываемТаблица данных не – 21%; вложениям. Формула в (значение ячейки слева).Начинаем заполнять таблицу для чтобы проект стал анализа чувствительности – по прямоугольнику, на через Данные-Анализ "Что-если"-Таблица должна была бы так, как Вы: Всем привет! Мучаюсь построением диаграммы и друга.Спасибо. Это я была цена в Но пока подразумевается
результат "Сумма выручки" подходит, поскольку в
срок окупаемости (РР) – Excel: =L8/-$G$7.Теперь найдем, когда проект анализа чувствительности инвестиционного привлекательным. Если внутренняя это метод перебора: первый (возможно, не данных), должна иметь делать ждете. А то
с анализом чувствительности, таблицы данных. Если все знаю. одной ячейке, то изменение одной переменной. и помещаем его ней можно изменять 4 года.
Найдем внутреннюю норму прибыли начнет приносить прибыль. проекта:
норма рентабельности выше, в модель последовательно профессиональный) взгляд, достаточно вид dcshoe я, к примеру. пытаюсь построить при переменных не много,Мотя
можно было бы Т.е. нужно узнать, в отдельную ячейку, значение только однойМожно еще найти среднегодовую (IRR). Если даты Или точку безубыточности:Рассчитаем денежный поток. Так чем ожидаемый доход подставляются значения параметров. эфемерна.Сейчас покручу еще,: В желтых таблицах из файла не помощи таблицы данных. то можно бесплатно: В формализованной форме. воспользоваться Данные - как будет изменяться например, D25.
ячейки. чистую (за вычетом не учитываются (как =ЕСЛИ(H7>=0;$C7;""), где Н7 как у нас на капитал, то К примеру, мыТе же результаты пока мэтров нет это и есть понял. Но почему-то не воспользоваться их демо-версией.Для того, чтобы Анализ "что-если" - сумма выручки при
Затем увеличиваем ценыIvan.kh
оттоков) прибыль без в нашем примере), – это прибыль динамический диапазон, понадобится
можно говорить об
хотим узнать, как
можно получить иabtextime применение функции ТаблицаПрям с языка считает, где-то что-то
На видеоролике все получить реальную помощь, Таблица данных. изменении цен, если на 2% и: А вопрос то учета инвестиций и воспользуемся встроенной функцией
в текущем периоде функция СМЕЩ. При эффективности инвестиций. изменится стоимость фирмы
более традиционным средствами: Вот, к примеру, данных. От нее
снял. Полностью поддерживаю. не то ввожу показано. Вам следует хорошоА поскольку нужно остальные переменные (в считываем записываем результат в чем?
процентной ставки: =(E18+СУММ(F7:F17))/C20. ВСД. Функция: =ВСД(G7:G17). (значение ячейки слева).
Excel – одна из лучших программ для аналитика данных. А почти каждому человеку на том или ином этапе жизни приходилось иметь дело с цифрами и текстовыми данными и обрабатывать их в условиях жестких дедлайнов. Если вам и сейчас нужно это делать, то мы опишем техники, которые помогут существенно улучшить вам жизнь. А чтобы было более наглядно, покажем, как их воплощать, с помощью анимаций.
Анализ данных через сводные таблицы Excel
Сводные таблицы – один из самых простых способов автоматизировать обработку информации. Он позволяет свести в кучу огромный массив данных, которые абсолютно не структурированы. Если его использовать, можно почти навсегда забыть о том, что такое фильтр и ручная сортировка. А чтобы их создать, достаточно нажать буквально пару кнопок и внести несколько несложных параметров в зависимости от того, какой способ представления результатов нужен конкретно вам в определенной ситуации.
Существует множество способов автоматизации анализа данных в Excel. Это как встроенные инструменты, так и дополнения, которые можно скачать на просторах интернета. Также есть дополнение «Пакет анализа», которое было разработано компанией Майкрософт. Она имеет все необходимые возможности, чтобы вы могли получать все необходимые результаты в одном файле Excel.
Пакет анализа данных, разработанный Майкрософт, можно использовать исключительно на едином листе в одну единицу времени. Если он будет обрабатывать информацию, расположенную на нескольких, то итоговая информация будет отображаться исключительно на одном. В других же будут показываться диапазоны без какой-либо значений, в которых есть исключительно форматы. Чтобы осуществить проанализировать информацию на нескольких листах, нужно использовать этот инструмент по отдельности. Это очень большой модуль, который поддерживает огромное количество возможностей, в частности, позволяет выполнять следующие типы обработки:
- Дисперсионный анализ.
- Корреляционный анализ.
- Ковариация.
- Вычисление скользящего среднего. Очень популярный метод в статистике и в трейдинге.
- Получать случайные числа.
- Выполнять операции с выборкой.
Эта надстройка не активирована по умолчанию, но входит в стандартный пакет. Чтобы ею воспользоваться, необходимо ее включить. Для этого сделайте следующие шаги:
- Перейдите в меню «Файл», и там найдите кнопку «Параметры». После этого перейдите в «Надстройки». Если же вы установили 2007 версию Эксель, то нужно нажать на кнопку «Параметры Excel», которая находится в меню Office.
- Далее появляется всплывающее меню, озаглавленное словом «Управление». Там находим пункт «Надстройки Excel», нажимаем на него, а потом – на кнопку «Перейти». Если же вы используете компьютер Apple, то достаточно открыть вкладку «Средства» в меню, а потом в раскрывающемся перечне найти пункт «Надстройки для Excel».
- В том диалоге, который появился после этого, нужно поставить галочку возле пункта «Пакет анализа», после чего подтвердить свои действия, нажав кнопку «ОК».
В некоторых ситуациях может оказаться так, что этого дополнения найти не удалось. В этом случае его не будет в перечне аддонов. Для этого надо нажать на кнопку «Обзор». Может также появиться информация о том, что пакет полностью отсутствует на этом компьютере. В этом случае необходимо его установить. Для этого нужно нажать на кнопку «Да».
Перед тем, как включить пакет анализа, необходимо сначала активировать VBA. Для этого его нужно загрузить таким же способом, как и саму надстройку.
Как работать со сводными таблицами
Первоначальная информация может быть какой-угодно. Это могут быть сведения о продажах, доставке, отгрузках продукции и так далее. Независимо от этого, последовательность шагов будет всегда одинаковой:
- Откройте файл, в котором содержится таблица.
- Выделите диапазон ячеек, которые мы будем анализировать с помощью сводной таблицы.
- Откройте вкладку «Вставка, и там надо найти группу «Таблицы», где есть кнопка «Сводная таблица». Если же используется компьютер под операционной системой Mac OS, то нужно открыть вкладку «Данные», и эта кнопка будет находиться во вкладке «Анализ».
- После этого откроется диалог с заголовком «Создание сводной таблицы».
- Затем выставите такое отображение данных, которое соответствует выделенному диапазону.
Мы открыли таблицу, информация в которой никоим образом не структурирована. Чтобы это сделать, можно воспользоваться настройками полей сводной таблицы в правой стороне экрана. Например, отправим в поле «Значения» «Сумму заказов», а информацию про продавцов и дату продажи – в строки таблицы. Исходя из данных, которые содержатся в этой таблице, автоматически определились суммы. Если есть необходимость, можно открыть информацию по каждому году, кварталу или месяцу. Это позволит получить детальную информацию, которая надо в конкретный момент.
От того, сколько колонок есть, будет отличаться и набор имеющихся параметров. Например, общее число столбцов – 5. И нам надо просто разместить и выбрать их верным образом, а показать сумму. В таком случае выполняем действия, показанные на этой анимации.
Можно сводную таблицу конкретизировать, указав, например, страну. Для этого мы включаем пункт «Страна».
Можно также посмотреть информацию про продавцов. Для этого мы заменяем колонку «Страна» на «Продавец». Результат получится следующий.
Анализ данных с помощью 3D-карт
Данный метод визуального представления с географической привязкой дает возможность искать закономерности, привязанные к регионам, а также анализировать информацию этого типа.
Преимущество этого способа в том, что нет необходимости отдельно прописывать координаты. Необходимо просто правильно написать географическое положение в таблице.
Как работать с 3D-картами в Excel
Последовательность действий, которую вам необходимо выполнить, чтобы работать с 3Д-картами, следующая:
- Откройте файл, в котором есть интересующий диапазон данных. Например, таблица, где есть колонка «Страна» или «Город».
- Информацию, которая будет показываться на карте, нужно сначала отформатировать, как таблицу. Для этого надо найти соответствующий пункт на вкладке «Главная».
- Выделите те ячейки, которые будут анализироваться.
- После этого переходим на вкладку «Вставка», и там находим кнопку «3Д-карта».
Затем показывается наша карта, где города в таблице представлены в виде точек. Но нам не особо нужно просто наличие информации о населенных пунктах на карте. Нам гораздо важнее видеть ту информацию, которая привязана к ним. Например, те суммы, которые можно показать, как высоту столбика. После того, как мы выполним действия, указанные на этой анимации, при наведении курсора на соответствующий столбик будут отображаться привязанные к нему данные.
Также можно воспользоваться круговой диаграммой, которая является намного более информативной в некоторых случаях. От того, какая общая сумма по величине, зависит размер круга.
Лист прогноза в Excel
Нередко бизнес-процессы зависят от сезонных особенностей. И такие факторы надо обязательно принимать в учет на этапе планирования. Для этого существует специальный инструмент Excel, который понравится вам своей высокой точностью. Он значительно более функциональный, чем все описанные выше методы, какими бы отличными они ни были. Точно так же, очень широкой является сфера его использования – коммерческие, финансовые, маркетинговые и даже государственные структуры.
Важно: чтобы рассчитать прогноз, необходимо получить информацию за предыдущее время. От того, насколько долгосрочные данные, зависит качество прогнозирования. Рекомендуется иметь данные, которые разбиты по одинаковым интервалам (например, поквартально или помесячно).
Как работать с листом прогноза
Чтобы работать с листом прогноза, необходимо выполнять следующие действия:
- Откройте файл, в котором содержится большой объем информации по тем показателям, которые нам надо проанализировать. Например, в течение прошлого года (хотя чем больше, тем лучше).
- Выделите две строки с информацией.
- Перейдите в меню «Данные», и там кликните по кнопке «Лист прогноза».
- После этого откроется диалог, в котором можно выбрать тип визуального представления прогноза: график или гистограмма. Выберите тот, который подходит под вашу ситуацию.
- Установите дату, когда прогноз должен закончиться.
В приводимом нами ниже примере даются сведения за три года – 2011-2013. При этом рекомендуется указывать временные промежутки, а не конкретные числа. То есть, лучше писать март 2013, а не конкретное число типа 7 марта 2013 года. Чтобы исходя из этих данных получить прогноз на 2014 год необходимо получить данных, расположенные в рядах с датой и показателями, которые были на этот момент. Выделяем эти строки.
Затем переходим на вкладку «Данные» и ищем группу «Прогноз». После этого переходим в меню «Лист прогноза». После этого появится окно, в котором снова выбираем способ представления прогноза, а затем устанавливаем дату, к которой прогноз должен быть закончен. После этого нажимаем на «Создать», после чего получаем три варианта прогноза (показываются оранжевой линией).
Быстрый анализ в Excel
Предыдущий способ действительно хорош, потому что позволяет составлять реальные прогнозы, основываясь на статистических показателях. Но этот метод позволяет фактически проводить полноценную бизнес-аналитику. Очень классно, что эта возможность создана максимально эргономичной, поскольку для достижения желаемого результата необходимо совершить буквально несколько действий. Никаких ручных подсчетов, записи каких-либо формул. Достаточно просто выбрать диапазон, который будет анализироваться и задать конечную цель.
Есть возможность прямо в ячейке создавать самые разные диаграммы и микрографики.
Как работать
Итак, чтобы работать, нам надо надо открыть файл, в котором содержится тот набор данных, который надо анализировать и выделить соответствующий диапазон. После того, как мы его выделим, у нас автоматически появится кнопка, дающая возможность составить итоги или же выполнить набор других действий. Называется она быстрым анализом. Также мы можем определить суммы, которые автоматически будут проставлены внизу. Более наглядно посмотреть, как это работает, можете на этой анимации.
Функция быстрого анализа позволяет также по-разному форматировать получившиеся данные. А определить, какие значения больше или меньше, можно непосредственно в ячейках гистограммы, которая появляется после того, как мы настроим этот инструмент.
Также пользователь может поставить самые разные маркеры, которые обозначают большие и меньшие значения относительно тех, которые есть в выборке. Так, зеленым цветом будут показываться самые большие значения, а красным – наиболее маленькие.
Очень хочется верить, что эти приемы позволят вам значительно повысить эффективность вашей работы с электронными таблицами и максимально быстро добиться всего, что вы желаете. Как видим, эта программа для работы с электронными таблицами дает очень широкие возможности даже в стандартном функционале. А что уже говорить про дополнения, которых очень много на просторах интернета. Важно только обратить внимание, что все аддоны должны быть тщательно проверены на вирусы, потому что модули, написанные другими людьми, могут содержать вредоносный код. Если же надстройки разработаны компанией Майкрософт, то ее можно использовать смело.
Пакет анализа от Майкрософт – очень функциональная надстройка, которая делает пользователя настоящим профессионалом. Она позволяет выполнить почти любую обработку количественных данных, но она довольно сложная для начинающего пользователя. На официальном сайте справки Майкрософт есть детальная инструкция по тому, как использовать разные виды анализа с помощью этого пакета.
Этот метод будет применять функцию таблицы данных для анализа чувствительности в Excel. Пожалуйста, сделайте следующее:
1. Завершите таблицу отчета о прибылях и убытках, как показано на скриншоте ниже:
(1) В ячейке B11 введите формулу = B4 * B3;
(2) В ячейке B12 введите формулу = B5 * B3;
(3) В ячейке B13 введите формулу = B11-B12;
(4) В ячейке B14 введите формулу = B13-B6-B7.
2. Подготовьте таблицу анализа чувствительности, как показано на скриншоте ниже:
(1) В диапазоне F2: K2 введите объемы продаж от 500 до 1750;
(2) В диапазоне E3: E8 введите цены от 75 до 200;
(3) В ячейке E2 введите формулу = B14
3. Выберите Range E2: K8 и нажмите Данные > Что-Анализ > Таблица данных. Смотрите скриншот:
4. В появившемся диалоговом окне Таблица данных, пожалуйста (1) в Ячейка ввода строки в поле укажите ячейку с объемом продаж стульев (в моем случае B3), (2) в Ячейка ввода столбца укажите ячейку с ценой стула (в моем случае B4), а затем (3) нажмите OK кнопка. Смотрите скриншот:
5. Теперь таблица анализа чувствительности создана, как показано на скриншоте ниже.
Вы можете легко узнать, как изменяется прибыль при изменении объема продаж и цен. Например, если вы продали 750 стульев по цене 125.00 долларов, прибыль изменится до -3750.00 долларов; тогда как когда вы продали 1500 стульев по цене 100.00 долларов, прибыль изменилась до 15000.00 долларов.
Под анализом чувствительности понимают динамику изменений результата в зависимости от изменений ключевых параметров. То есть что мы получим на выходе модели, меняя переменные на входе.
Данный анализ вызывает особый интерес, как у инвесторов, так и у управляющих бизнесом. Его результаты несут особенную ценность в аналитике бизнес проектов. Excel позволяет анализировать чувствительность инвестиционных проектов, пользователям с базовыми знаниями в области финансов.
Метод анализа чувствительности
Задача аналитика – определить характер зависимости результата от переменных и их пороговых величин, когда выводы модели больше не поддерживаются.
По своей сути метод анализа чувствительности – это метод перебора: в модель последовательно подставляются значения параметров. К примеру, мы хотим узнать, как изменится стоимость фирмы при изменении себестоимости продукции в пределах 60-80%.
Используется и обратный метод, когда результат модели на выходе «подгоняется» к изменению значений на входе.
Основные целевые измеримые показатели финансовой модели:
- NPV (чистая приведенная стоимость). Основной показатель доходности инвестиционного объекта. Рассчитывается как разность общей суммы дисконтированных доходов и размера самой инвестиции. Представляет собой прогнозную оценку экономического потенциала предприятия в случае принятия проекта.
- IRR (внутренняя норма доходности или прибыли). Показывает максимальное требование к годовой прибыли на вложенные деньги. Сколько инвестор может заложить в свои расчеты, чтобы проект стал привлекательным. Если внутренняя норма рентабельности выше, чем ожидаемый доход на капитал, то можно говорить об эффективности инвестиций.
- ROI/ROR (коэффициент рентабельности/окупаемости инвестиций). Рассчитывается как отношение общей прибыли (с учетом коэффициента дисконтирования) к начальной инвестиции.
- DPI (дисконтированный индекс доходности/прибыльности). Рассчитывается как отношение чистой приведенной стоимости к начальным инвестициям. Если показатель больше 1, вложение капитала можно считать эффективным.
Данные показатели, как правило, и являются теми результатами, по которым проводится анализ чувствительности. Естественно, при необходимости определяется чувствительность и других численных расчетных показателей. Количество переменных может быть любым.
Анализ чувствительности инвестиционного проекта в Excel
Задача – проанализировать основные показатели эффективности инвестиционного проекта. Для примера возьмем условные цифры.
Начинаем заполнять таблицу для анализа чувствительности инвестиционного проекта:
- Рассчитаем денежный поток. Так как у нас динамический диапазон, понадобится функция СМЕЩ. При расчете учитываем ликвидационную стоимость (в нашем примере – 0, неизвестна). Расчет будем производить «без дат». То есть они не повлияют на результаты. Денежный поток в «нулевом» периоде равняется предынвестиционным вложениям. В последующих периодах: .
- Для расчета срока окупаемости инвестиционного проекта (РР) создаем дополнительный столбец. В инвестиционный период будут суммироваться все дополнительные инвестиции за вычетом прибыли от суммы вложенных финансовых средств. Формула для «нулевого» периода: =СУММЕСЛИ(G7:G17;" 0;G8;0). Где Н7 – это прибыль предыдущего периода (значение в ячейке выше). G8 – денежный поток в данном периоде (значение ячейки слева).
- Теперь найдем, когда проект начнет приносить прибыль. Или точку безубыточности: =ЕСЛИ(H7>=0;$C7;""), где Н7 – это прибыль в текущем периоде (значение ячейки слева). С7 – это номер текущего периода (первый столбец).
- Найдем рентабельность инвестиций. Это отношение прибыли в текущем периоде к предынвестиционным вложениям. Формула в Excel: =СУММ($H$7;H8)/-$H$7.
- Рассчитаем коэффициент дисконтирования. Формула для нашего примера (где даты не учитываются): =1/(1+$B$1)^C7. В1 – ячейка с процентным выражением ставки дисконтирования. С7 – номер периода.
- Найдем дисконтированную (приведенную) стоимость. Это произведение значения денежного потока в текущем периоде и коэффициента дисконтирования. Формула: =G7*K7.
- Найдем индекс рентабельности (или дисконтированный индекс рентабельности). Аббревиатура – PI. Это отношение дисконтированной стоимости к начальным вложениям. Формула в Excel: =L8/-$G$7.
- Найдем внутреннюю норму прибыли (IRR). Если даты не учитываются (как в нашем примере), воспользуемся встроенной функцией ВСД. Функция: =ВСД(G7:G17). Если даты учитываются, то подойдет функция ЧИСТВНДОХ. Посчитаем РР – срок окупаемости проекта. Для этой цели используем вложенные функции: . Или возьмем данные из таблицы.
- срок проекта – 10 лет;
- чистый дисконтированный доход (NPV) – 107228р. (без учета даты платежей, принимая все периоды равными);
- для нахождения данного значения возможно использование встроенных функций ЧПС и ПС (для аннуитетных платежей);
- дисконтированный индекс рентабельности (PI) – 1,54;
- рентабельность инвестиций (ROR) – 25%;
- внутренняя норма доходности (IRR) – 21%;
- срок окупаемости (РР) – 4 года.
Можно еще найти среднегодовую чистую (за вычетом оттоков) прибыль без учета инвестиций и процентной ставки: =(E18+СУММ(F7:F17))/C20. Где Е18 – сумма притоков денежных средств, диапазон F7:F17 – оттоки; С20 – срок инвестиционного проекта.
Таблицу Excel с примером и формулами можно посмотреть, скачав файл с готовым примером.
Excel с успехом справляется с задачами формирования вариантов изменения выходного параметра проекта от изменения одного или нескольких входных параметров (анализ чувствительности). В данной статье рассмотрим инструмент Excel Таблицы данных, а также команды надстройки SubEx, позволяющие быстро решать данные задачи.
Рассмотрим элементарную экономическую модель в Excel по формированию прибыли от реализации продукции:
Условно выделим 3 варианта анализа чувствительности, доступные в Excel:
- Чувствительность 1 показателя к изменению 1 параметра
Просчитаем варианты изменения прибыли в зависимости от изменения цены. Для этого построим таблицу следующего вида, где в 10 строке укажем желаемые значения цены, а в ячейке B11 зададим ссылку на целевой показатель (прибыль):
Выделив указанный фрагмент запустим инструмент Таблицы данных на вкладке Данные
После чего укажем ячейку B1 в качестве переменной, значения которой приведены в столбцах, т.е. цену:
После нажатия ОК получаем сформированные варианты:
Т.е. при цене 5 руб прибыль составит 10 рублей, при цене 6 рублей — 20 рублей и так далее.. Самое время построить диаграмму:
2. Чувствительность нескольких показателей к изменению 1 параметра
Аналогичным образом можно анализировать изменение нескольких показателей к изменению 1 переменной, для этого их нужно расположить в соседних строках и выделить всю область при активации Таблиц данных:
В данном случае программа просчитает как варианты прибыли, так и варианты выручки при изменении цены:
3. Чувствительность 1 показателя к изменению 2 параметров
Таблицы данных могут также успешно применяться для 2 переменных. Например, определим зависимость прибыли от изменения одновременно цены и объема продаж. Для этого построим следующую таблицу:
По аналогии с предыдущими примерами, в ячейке B8 должна находиться ссылка на анализируемый показатель, т.е. на ячейку B5 (прибыль).
Выделяем сформированную таблицу, запускаем Таблицы данных, задаем соответствующие параметры:
Получаем следующий результат. Здесь, кстати, хорошо подходит применение условного форматирования:
При цене 6 руб и объеме продаж 11 ед прибыль будет равна 22 руб, при цене 7 руб и объеме продаж 12 ед прибыль будет равна 36 руб и так далее.
С визуализацией здесь несколько сложнее, для отдельных случаев подойдет вид нескольких графиков:
Кстати, в последней версии нашей надстройки SubEx 3.7 для Excel включен мастер анализа чувствительности на основе Таблиц данных.
В надстройке вы найдете встроенные схемы и все необходимые подсказки, чтобы еще быстрее и удобнее использовать данный функционал Excel:
Читайте также: