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

Создание сценариев

 

1.1 Постройте модель прогноза продаж на несколько лет. Создайте на листе Сценарии таблицу:

 

 

1.2 Проверьте результат:

 

1.3 Определите имя для значения ячейки Общий_доход, которое понадобиться в дальнейшем. Действия: вызовите контектное меню ячейки H12; команда Имя диапазона…;в поле Имя: диалогового окна Создание имени введите Общий_доход; ОК.

 

Ход решения

 

Предположим, необходимо создать три сценария для приведенной модели: наилучший прогноз, наилучший результат, наихудший результат. Эти оценки дадут общую картину возможностей в будущем.

 

2.1 Рассмотрим наихудший результат: 12% для Продаж, 14% для Стоимости проданных товаров, 18% для Общих и административных расходов и 20 % для Маркетинга.

На вкладке Данные выберите команду Анализ «что-если»/ Диспетчер сценариев…. Появится диалоговое окно Диспетчер сценариев:

 

Нажмите кнопку Добавить. Появится диалоговое окно Добавление сценария:

Введите необходимое имя Наихудший результат в поле Название сценария.

 

Введите ссылки на ячейки $D$15:$D$18, которые необходимо изменить, в поле Изменяемые ячейки.

 

Нажмите кнопку OK. Появится диалоговое окно Значения ячеек сценария. Введите необходимые значения в диалоговом окне Значения ячеек сценария:

 

 

Чтобы создать сценарий, нажмите кнопку OK.

 

2.2 Для создания дополнительных сценариев нажмите кнопку Добавить и повторите действия:

 

Создайте сценарий Наилучшая оценка (15% для Продаж, 15% для Стоимости проданных товаров, 12% для Общих и административных расходов и 17 % для Маркетинга) и Наилучший результат (20% для Продаж, 18% для Стоимости проданных товаров, 18% для Общих и административных расходов и 19 % для Маркетинга).

 

В окне Диспетчера сценариев будут перечислены три сценария.

 

После завершения создания сценариев нажмите кнопку OK, а затем — кнопку Закрыть в диалоговом окне Диспетчер сценариев.

 

Просмотр сценария

 

3.1 При просмотре сценария изменяют ячейки, сохраненные как часть этого сценария.

 

На вкладке Данные выберите команду Анализ «что-если»/ Диспетчер сценариев…

 

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

 

Нажмите кнопку Вывести. На экране измененные значения столбца $D$15:$D$18 и пересчитанная исходная таблица.

 

 

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

4.1 На вкладке Данные выберите команду Анализ «что-если»/ Диспетчер сценариев…

 

Нажмите кнопку Отчет. Появится диалоговое окно Отчет по сценарию

 

 

Установите переключатель в положение Структура.

 

В поле Ячейки результата введите ссылки на ячейки, значения которых были изменены с помощью сценариев H12. В качестве разделителя ссылок используется запятая.

 

Нажмите ОК. Excel вставляет новый лист с итоговой таблицей исходных значений и результатов: