Бизнес моделирование Excel Анализ «Что – Если»
Инструменты анализа «Что – Если»
Основной принцип, лежащий в анализе «что – если» состоит в следующем. Вначале вычисляется значение по заданной формуле, использующей данные в ячейках. После этого задается вопрос: что было бы если бы значение в какой-либо ячейке, входящей в формулу, было бы иным?
Для подобного анализа в Excel существует множество инструментов. Например, используем функцию БС() из категории финансовых функций (Просмотреть справку!).
Задание 1.
Введите исходные данные (ячейки В2:С6 ), как показано на рисунке.
Ячейку С9 заполните, используя функцию БС().
На новом листе получите результат, ответив на вопросы: что будет, если бы процентная ставка равнялась 7%? Если сумма ежегодных инвестиций составит 8000? Или 12000? Как изменится результат, если уменьшится сумма первоначальных инвестиций?
Задание 2.
Если вы хотите проанализировать, как повлияет на формулу ряд различных значений, необходимо создать таблицу данных. Например, в примере со стоимостью инвестиций может быть необходимо узнать, как изменится будущая стоимость инвестиций, если ежегодная сумма вклада изменяется от 7000 до 13000. Эти значения можно ввести, в строку или в столбец, а затем создать соответствующие формулы. Тем не менее, намного проще создать таблицу данных, как показано ниже.
Введите на рабочий лист значения, которые вы хотите использовать в формуле. Значения можно разместить двумя способами.
Если вы хотите ввести значения в строку, начните строку на одну ячейку выше и правее формулы.
Если вы хотите ввести значения в столбец, начните столбец на одну ячейку ниже и левее ячейки, которая содержит формулу (см. рис.).
Выделите диапазон, который включает исходные значения и формулу (на рис. это диапазон В9:С16). Ячейки С10:С16 не заполнять!
Выберите команду Данные → Анализ "что - если" → Таблица данных. Ехсеl отображает диалоговое окно Таблица данных.
Выбор параметров в этом диалоговом окне зависит от того, как была задана таблица данных.
Если исходные значения введены в строку, используйте текстовое поле «Подставлять значения по столбцам в», чтобы ввести адрес ячейки с исходным значением.
Если исходные значения введены в столбец, введите адрес исходной ячейки в текстовое поле «Подставлять значения по строкам в».
В примере с инвестициями в ячейке «Подставлять значения по строкам в» введено значение С4.
Щелкните на кнопке ОК. Ехсеl помещает каждое из исходных значений в ячейку ввода; после этого Ехсеl отображает результаты в таблице данных.
Задание 3.
Добавление дополнительных формул в таблицу данных
В таблицах данных можно вводить не только одну, но и несколько формул. Если вы хотите проанализировать эффект от изменения исходных значений на разных формулах, то их можно легко добавить в таблицу данных. Например, для расчета будущей стоимости инвестиций будет интересно учесть влияние инфляции, чтобы посмотреть, какова будет стоимость инвестиций в перерасчете на текущий уровень цен. На рисунке показана доработанная таблица с новой переменной инфляции (ячейка С7) и формула, которая преобразует полученную будущую стоимость в сегодняшние цены (ячейка В9).
Примечание
Формула для преобразования будущей стоимости инвестиций в текущий уровень цен выглядит следующим образом:
Будущая_стоимость / (1+Ставка__инфляции) ^Период
Здесь Период - это количество лет от текущего момента до конца будущей стоимости.
Выполните описанные выше действия.