Во многих экономических задачах результат расчета зависит от нескольких параметров, которыми можно управлять. Часто бывает удобно проанализировать ситуацию для нескольких возможных параметров.
Диспетчер сценариев MS Excel позволяет автоматически выполнить анализ "что-если" для различных моделей. Можно создать несколько входных наборов данных (изменяемых ячеек) для любого количества переменных и присвоить имя каждому набору.
По имени выбранного набора данных MS Excel сформирует на рабочем листе результаты анализа. Кроме этого, диспетчер сценариев позволяет создать итоговый отчет по сценариям, в котором отображаются результаты подстановки различных комбинаций входных параметров.
Диспетчер сценариев открывается командой Сервис | Сценарии (рис. 29).
В окне диспетчера сценариев с помощью соответствующих кнопок можно добавить новый сценарий, изменить, удалить или вывести существующий, а также— объединить несколько различных сценариев и получить итоговый отчет для существующих сценариев.
Задание 1. Рассчитать внутреннюю скорость оборота инвестиций
Исходные данные: затраты по проекту составят 700 млн руб. Ожидаемые доходы в течение последующих 5 лет составят, соответственно, 70, 90,300,250, 300 млн руб. Оценить экономическую
целесообразность проекта по скорости оборота инвестиции, если рыночная норма дохода 12%. Рассмотреть также следующие варианты (затраты на проект представлены числом со знаком минус):
• -600; 50;100; 200; 200; 300;
• -650; 90;120;200;250; 250;
• -500, 100,100, 200, 250, 250.
Рис. 29 Окно Диспетчер сценариев
Решение
Для вычисления внутренней скорости оборота инвестиции (внутренней нормы доходности) используется функция ВСД.:
ВСД (Значения; Предположения)
В нашем случае функция для решения задачи использует только аргумент Значения, один из которых обязательно отрицателен (затраты по проекту). Если внутренняя скорость оборота инвестиций окажется больше рыночной нормы доходности, то проект считается экономически целесообразным. В противном случае проект должен быть отвергнут.
1. Решение приведено на рис. 30. Формулы для расчета:
• в ячейке В11: =ВСД(В75:В80)
• в ячейке С11: =ЕСЛИ(В84>В82;"Проект экономически целесообразен"; "Проект необходимо отвергнуть")
Рис. 30. Расчет внутренней скорости оборота инвестиций
2. Рассмотрим этот пример для всех комбинаций исходных данных. Для создания сценария следует использовать команду Сервис | Сценарии | кнопка Добавить (рис. 31).
Рис. 31. Добавление сценария для первой комбинации исходных данных
После нажатия на кнопку ОК появляется возможность внесения новых значений для изменяемых ячеек (рис. 32).
Рис. 32. Окно для изменения значений ячеек сценария
Для сохранения результатов по первому сценарию нет необходимости редактировать значения ячеек— достаточно нажать кнопку ОК для подтверждения значений, появившихся по умолчанию, и выхода в окно Диспетчер сценариев (рис. 33).
Рис. 33 Окно Диспетчер сценариев с первым сохраненным сценарием
3. Для добавления к рассматриваемой задаче новых сценариев достаточно нажать кнопку Добавить в окне Диспетчер сценариев и повторить вышеописанные действия, изменив значения в ячейках исходных данных (рис. 34).
На рис. 34 сценарий Скорость_оборота_1 соответствует данным (-700; 70; 90; 300; 250; 300), сценарий Скорость_оборота_2 — данным (-600; 50; 100; 200; 200; 300), сценарий Скорость_оборота_З— данным (-650; 90; 120; 200; 250; 250), сценарий скорость_оборота_4— данным (-500, 100, 100, 200,
250,250).
Нажав кнопку Вывести, можно просмотреть на рабочем листе результаты расчета для соответствующей комбинации исходных значений.
Рис. 34. Окно Диспетчер сценариев с добавленными сценариями
по расчету скорости оборота инвестиций
Рис. 4.31. Добавление ячеек результата в окно Отчет по сценарию
4. Для полученияитогового отчета по всем добавленным сценариям следует нажать кнопу Отчет в окне диспетчера сценариев. В появившемся окне Отчет по сценарию (рис. 35) выбрать необходимый тип отчета и дать ссылки на ячейки, в которых вычисляются результирующие функции. При нажатии на кнопку ОК на соответствующий лист рабочей книги выводится отчет по сценариям (рис. 36 и рис. 37)
Рис. 36 Отчет типа Структура по сценариям расчета скорости оборота инвестиций
Рис. 37 Отчет типа Сводная таблица по сценариям расчета скорости оборота инвестиций