Диспетчер сценариев для анализа прогнозной модели

Признаком качественно выполненной прогнозной модели является наличие анализа чувствительности параметров модели. Как результирующий итог модели (например, внутренняя норма доходности – IRR или объем инвестиций), поведет себя при том или ином изменении исходных посылок? Если итог получен в результате сложных вычислений, то влияние отдельных параметров очень удобно оценивать с помощью анализа что если. Однако, этот инструмент удобен, когда нужно проанализировать влияние на результат одного или двух параметров. Если одновременно необходимо изучить влияние более чем двух параметров, воспользуйтесь диспетчером сценариев.[1] Диспетчер сценариев позволяет выполнить анализ чувствительности с возможностью изменения до 32 значений в ячейках с исходными данными.

Рис. 1. Данные, на которых основаны сценарии

Скачать заметку в формате Word или pdf, примеры в формате Excel

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

Рис. 2. Модель, на которой основаны сценарии

Для определения наиболее благоприятного сценария откройте вкладку ДАННЫЕ и в группе Работа с данными в раскрывающемся списке Анализ «что если» выберите инструмент Диспетчер сценариев. Нажмите кнопку Добавить и заполните поля в диалоговом окне Добавление сценария (рис. 3). Введите имя сценария и выберите ячейки В2:В4, как ячейки с исходными данными, содержащие определяющие сценарий значения. Нажмите кнопку OK и в открывшемся диалоговом окне Значения ячеек сценария заполните поля входными значениями, определяющими наиболее благоприятный вариант (рис. 4).

Рис. 3. Исходные данные для наиболее благоприятного сценария

Рис. 4. Определение исходных значений для наиболее благоприятного сценария

В диалоговом окне Значение ячеек сценария нажмите кнопку Добавить, и аналогичным образом введите данные для наиболее вероятного и наименее благоприятного сценариев. После ввода данных для всех трех сценариев в диалоговом окне Значение ячеек сценария нажмите ОК. Вы вернетесь в окно Диспетчер сценариев (рис. 5). Сейчас в нем отражены все три сценария. Нажмите кнопку Отчет. Выберите ячейки с конечными результатами, которые должны отображаться в отчетах по сценариям (рис. 6). Для отслеживания выбраны значения прибыли за каждый год после уплаты налогов (ячейки B18: F18) и значение чистой приведенной стоимости (ячейка B20). Так как ячейки с результатами B18:F18 и B20 находятся в несмежных диапазонах, их следует перечислить через точку с запятой. Также несколько диапазонов ячеек можно выбрать и внести при нажатой клавише <Ctrl>. Установите переключатель Тип отчета в положение структура, и нажмите кнопку OK. В книге Excel будет создан отчет Структура сценария (рис. 7).

Рис. 5. Диспетчер сценариев

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

Рис. 7. Отчет по сценариям

Обратите внимание, что в отчет включен столбец, помеченный как Текущие значения, для изначально указанных на листе значений. В наименее благоприятном сценарии компания несет убытки (в размере 13 346 долларов), в наиболее благоприятном — получает прибыль (в размере 226 893 долларов). Так как в наименее благоприятном сценарии цена ниже переменных затрат, компания теряет деньги каждый год.

Некоторые замечания

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

Рис. 8. Отчет по сценариям в виде сводной таблицы

Если в диалоговом окне Диспетчер сценариев выбрать один из сценариев и нажать кнопку Вывести, на листе с моделью (рис. 9) появятся значения входных ячеек для выбранного сценария, и все формулы будут автоматически пересчитаны для выбранного сценария. Этот инструмент отлично подходит для подготовки презентации. Ctrl+Z отменяет работу сценария, и возвращает лист в исходное состояние.

Рис. 9. На лист с моделью выведены расчет для наиболее благоприятного сценария

С помощью инструмента Диспетчер сценариев трудно создать много сценариев, поскольку приходится вводить значения для каждого сценария отдельно. Большое количество сценариев можно создать с помощью моделирования по методу Монте-Карло. При использовании метода Монте-Карло можно найти, например, вероятность того, что чистая приведенная стоимость денежных потоков проекта является неотрицательной. Это важный показатель, поскольку такая вероятность показывает, повышает ли проект стоимость компании.

Как и в любой структуре данных при нажатии на знак «минус» (–) в строках 5 и 9 отчета Структура сценария (см. рис. 7) строки с предполагаемыми значениями скрываются, а отображаются только результаты. При нажатии на знак «плюс» (+) отчет восстанавливается в полном объеме.

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

[1] Заметка написана на основе материалов из книги Уэйн Л. Винстон. Microsoft Excel 2013. Анализ данных и бизнес-моделирование, глава 18.