Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как провести анализ чувствительности с таблицей данных в Excel?

Как провести анализ чувствительности с таблицей данных в Excel?

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

Выполните анализ чувствительности с помощью таблицы Excel

Простое объединение нескольких листов/книг/ CSV файлов в один лист/книгу

Объединение десятков листов из разных книг в один может оказаться утомительным занятием. Но с помощью утилиты Combine (рабочие листы и книги) в Kutools for Excel, вы можете сделать это всего за несколько щелчков мышью! 30-дневная бесплатная пробная версия полнофункциональной версии!

  • Повторно использовать что угодно: добавьте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
  • Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
  • Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
  • Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
  • Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50% своего времени и уменьшите тысячи щелчков мышью каждый день!

Проведите анализ чувствительности с таблица данных в Excel

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

1 . Завершите таблицу отчета о прибылях и убытках, как показано на скриншоте ниже:
(1) В ячейке B11 введите формулу = B4 * B3 ;
(2) В ячейке B12 введите формулу = B5 * B3 ;
(3) В ячейке B13 введите формулу = B11-B12 ;
(4) В ячейке B14 введите формулу = B13-B6-B7 .

Читайте так же:
Как прокрутить указанный лист или все листы вверх в Excel?

. Подготовьте таблицу анализа чувствительности, как показано на скриншоте ниже:
(1) В диапазоне F2: K2 введите объемы продаж от 500 до 1750;
(2) В диапазоне E3: E8 введите цены от 75 до 200;
(3) В ячейке E2 введите формулу = B14

3 . Выберите диапазон E2: K8 и нажмите Данные >Анализ «что-если» >Таблица данных . См. Снимок экрана:

4 . В появившемся диалоговом окне Таблица данных, пожалуйста, (1) в поле Row input cell укажите ячейку с объемом продаж стульев (B3 в моем случае) , (2) в поле ввода Столбец укажите ячейку с ценой стула (B4 в моем случае), а затем (3 ) нажмите кнопку ОК . См. Снимок экрана:

5 . Теперь таблица анализа чувствительности создана, как показано на скриншоте ниже.

Вы можете легко выяснить, как изменяется прибыль при изменении объема продаж и цен. Например, если вы продали 750 стульев по цене $125, ваша прибыль изменится на -$3750, а если вы продали 1500 стульев по цене $100, ваша прибыль изменится на $15 000.

Диаграммы Excel «Торнадо» помогают анализировать данные и принимать решения. Как инструмент анализа чувствительности, он очень полезен для иллюстрации того, как зависимые переменные влияют друг на друга, то есть показывает взаимодействие между переменными. Вход влияет на выход одинаково.

График полезен для сравнения двух переменных.

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

График торнадо в Excel

Примеры диаграмм торнадо в excel

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

Пример # 1 — Сравнение двух переменных

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

Схема торнадо, пример 1

  • Расположите набор данных для первой переменной в порядке возрастания, сортируя их от наименьшего к наибольшему.
  • Выберите данные для вставки в график (A1: C7).
  • Выберите двумерную гистограмму с накоплением в разделе Графики вкладки Вставка.
Читайте так же:
Как проверить, находится ли значение ячейки между двумя значениями в Excel?

Таблица торнадо, пример 1-2

  • Выделите первую переменную и щелкните правой кнопкой мыши, чтобы выбрать Format Data Row.
  • На панели Формат данных выберите опцию Вторичная ось.

Пример 1-3

  • Выберите вторичную ось в диаграмме Excel, затем щелкните правой кнопкой мыши и выберите опцию Форматировать ось.

Пример 1-4

  • Установите минимальное значение параметра Axis Bounds в разделе Axis options как отрицательное значение максимального числа (минимальная и максимальная границы должны быть одинаковыми, но минимальное значение должно быть отрицательным, а максимальное положительным).

Пример 1-5

  • Кроме того, установите флажок Инвертировать значения в разделе Параметры оси панели Формат оси.

Пример 1-6

Пример 1-8

  • Теперь выберите основную ось и щелкните правой кнопкой мыши, чтобы выбрать опцию Форматировать ось.

Пример 1-9

  • Установите нижние границы оси на отрицательные значения, а верхние — на положительные (как указано выше).

Пример 1-10

Теперь нажмите на ось, показывающую название продукта (A, B, C…).

  • Выделите и щелкните правой кнопкой мыши, чтобы выбрать «Форматировать ось».

Пример 1-11

  • Выберите положение метки как «Низ» в выпадающем списке «Метки» в разделе «Параметры оси».

Пример 1-12

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

Выберите главную ось и удалите ее. Переименуйте диаграмму по своему усмотрению.

Пример 1-13

Это диаграмма торнадо в Excel.

Пример # 2 — Диаграмма торнадо в Excel (диаграмма бабочки)

Диаграмма торнадо в Excel также известна как диаграмма бабочки. Пример диаграммы бабочки показывает, как построить диаграмму.

  • Создайте набор данных в электронной таблице Excel с названием продукта и значениями
  • Просто добавьте в набор данных еще один столбец под названием «GAP» после столбца переменных.
  • В колонке «GAP» добавьте число 1000 для всех продуктов.

Диаграмма торнадо в Excel, пример 2

  • Создайте диаграмму в Excel с этими данными и добавьте столбец GAP
  • Выполните описанные выше шаги для создания диаграммы.
  • Убедитесь, что столбчатые рейки находятся на главной оси.

Диаграмма торнадо в Excel Пример 2-1

  • Щелкните правой кнопкой мыши на области графика и выберите «Выбрать данные».
  • Переместите колонку «GAP» в разделе «Легенда серии» в центр.

Диаграмма торнадо в Excel Пример 2-2

  • Щелкните правой кнопкой мыши на столбцах и выберите опцию Format Row (Формат строки).

Диаграмма торнадо в Excel Пример 2-3

  • Выберите опции «Без заливки» и «Без линии» в разделах «Заливка» и «Границы».
Читайте так же:
Как применить нечеткий поиск, чтобы найти примерный результат совпадения в Excel?

Диаграмма торнадо в Excel, пример 2-4

  • Дважды щелкните на легенде «GAP» и нажмите «Удалить», чтобы удалить название столбца с диаграммы.

Диаграмма торнадо в Excel, пример 2-5

Теперь вы можете переименовать график в график бабочки. Теперь график напоминает бабочку.

Диаграмма торнадо в Excel, пример 2-7

Пример # 3 — Анализ чувствительности

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

Маржа изменяется при увеличении или уменьшении значения.

Для построения диаграммы повторите шаги из предыдущих примеров.

В данном примере маржа положительная, а затраты отрицательные.

Таблица анализа чувствительности, пример 3

На графике выше вы можете увидеть влияние затрат на маржу. Диаграмма торнадо в Excel показывает, что увеличение затрат уменьшает маржу, а уменьшение затрат увеличивает маржу.

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

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

А. Операционные расходы

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

  • Сырье,
  • Расходные материалы,
  • Аренда офиса,
  • Услуги подрядчиков,
  • Зарплата сотрудников — показывает не только общую зарплату, но и зарплату по отдельным должностям с учетом индексации,
  • Маркетинг и продвижение — разбивка по рекламным каналам и детализация затрат по каждому из них. B. Внешние факторы.

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

  • Темпы инфляции,
  • Обменные курсы,
  • Налоговые ставки,
  • Цены на товары, арендную плату и продукцию.

В. Прогноз финансовых показателей

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

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

  • План продаж;
  • Прогноз по выручке;
  • Прогноз по среднему чеку;
  • Прогноз по стоимости бестселлера.

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

Оценка инвестиций

NPV (чистая текущая стоимость) — это сумма дисконтированных стоимостей всего потока платежей, имевших место «до настоящего момента».

Читайте так же:
Как ранжировать значения по группам в Excel?

Внутренняя норма доходности (I RR) — это процентная ставка, при которой чистая приведенная стоимость равна нулю. IRR не может быть рассчитана, как NPV, по достаточно простому алгоритму. Поэтому Excel использует для ее расчета достаточно простой метод подгонки. Кстати, вы также можете использовать значение по умолчанию — 0,1%.

Конечно, можно сделать вывод, что выполнить точный расчет IRR с помощью простых формул очень сложно, но в Excel есть очень полезный метод поиска решений различных задач с одним и тем же параметром — «Parameter fitting». Его можно найти через: Данные > Работа с данными > Анализ «что-если» > Подгонка параметров (применимо к Excel2007) или через Инструменты > Подгонка параметров (Excel 97-2003).

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

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

Скачать файл-образец showateli-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″)+1». Смысл этой формулы: подсчёт всех отрицательных значений и прибавление единицы даст номер года, в котором совокупный денежный поток станет неотрицательным.

Дисконтирование в Excel денежного потока: продолжение примера

Для листа «Инвестиционный проект» заполните строку «Дисконтированный денежный поток»: В ячейку B13 введите «=B10/STEP(1+B$5;B12)», растяните вправо. Для текущего года дисконт отсутствует, что отражается в 0 для инвестиционного периода. При номере два (2016 год) коэффициент дисконтирования составляет 0,82 (=1/(1+22%)). Каждый последующий год коэффициент дисконтирования уменьшается. Рассчитайте общий дисконтированный денежный поток в ячейке G13. В следующей таблице видно, что из-за довольно большой ставки дисконтирования значение NPV заметно отличается от общего недисконтированного денежного потока (ячейка F11). Поскольку значение положительное, проект привлекателен для инвесторов.

Читайте так же:
Как разбить данные на несколько листов на основе столбца в Excel?

Расчёт чистой приведённой стоимости (NPV) в Excel: продолжение примера

В Excel есть встроенная функция для расчёта NPVЧПС(ставка.дисконтирования; диапазон_денежных_потоков). Этой функцией пользоваться удобнее (не нужно рассчитывать дисконтированный поток), но у неё есть особенность: первый год проекта считается как будущий (с номером 1) и его денежный поток уже дисконтируется. Если проект планируется, начиная с текущего года, необходимо до применения функции ЧПС скорректировать значения денежных потоков каждого года, умножив их на коэффициент (1+ставка дисконтирования), покажем на примере.

На листе «Инвестиционный проект» заполните «Скорректированный денежный поток»: Введите формулу «=B10*(1+$B$5)» в ячейку B15 и растяните вправо. Теперь в ячейку B18 введите формулу «=$PP(B5;B15:F15)» и сравните ее с «вручную» рассчитанным значением в ячейке G13.

Расчёт внутренней нормы доходности (IRR) в Excel: окончание примера

Мы рассчитаем IRR в Excel двумя способами: вручную и автоматически.

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

В Excel есть функция для расчёта IRR — ВСД, которая работает с той же особенностью дисконтирования с первого года. В ячейке В19 введите функцию «=ВСД(B15:F15)», сравните с подобранным вручную.

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

Скачать Excel-файл с примером инвестиционного проекта можно по ссылке: pokazateli-investproekta

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector