Создание сценариев
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 вставит новый лист с итоговой таблицей исходных значений и результатов:
Создание отчета по сценарию в виде сводной таблицы
· На вкладке Данные выберите команду Анализ «что-если»/ Диспетчер сценариев …
· Нажмите кнопку Отчет.
· Установите переключатель в положение Сводная таблица.
5.1 В поле Ячейки результата введите ссылки на ячейки, значения которых были изменены с помощью сценариев – H12.
Нажмите ОК. Excel вставит новый лист со сводной таблицей:
Самостоятельная работа
Задача 1. По облигации номиналом 100 тыс. руб, выпущенной на 6 лет, предусмотрен следующий порядок начисления процентов: в первый год - 10%, в два последующих – 20%, в оставшиеся три года – 25%. Рассчитаем будущую (наращенную) стоимость облигации по сложной процентной ставке.
6.1 Подготовьте исходные данные на рабочем листе книги Сценарии2:
Функция БЗРАСПИС возвращает будущее значение основного капитала после начисления сложных процентов. Функция БЗРАСПИС используется для вычисления будущего значения инвестиции с переменной процентной ставкой.
Синтаксис: БЗРАСПИС(первичное; план)
Первичное – стоимость инвестиции на текущий момент.
План – это массив применяемых процентных ставок.
Пример: БЗРАСПИС(1;{0,09;0,11;0,1}) = 1,33089
В отдельный столбец (A2:A7) введите список значений процентов. Формат ячеек A2:A7 – процентный. В ячейку H4 введите функцию БЗРАСПИС.
Сравните результат:
Создайте три сценария:
· Исходный вариант (в первый год – 10%, в два последующих – 20%, в оставшиеся три года – 25%);
· Лучший вариант (в первый год – 10%, в два последующих – 25%, в оставшиеся три года – 30%);
· Худший вариант (в первый год – 10%, в два последующих – 12%, в оставшиеся три года – 15%).
Выведите все отчеты для сценариев.
Задача 2. Рассчитать стоимость разработки программных продуктов в некоторой фирме, занимающейся созданием программного обеспечения (ПО). Рабочий лист для выполнения таких расчетов показан ниже на рисунке.
6.2 Всем изменяемым ячейкам присвоены соответствующие имена. Например, ячейка В10 имеет имя Аттестация_1.
Таблица для расчета стоимости состоит из двух разделов.
· В диапазон A1:D3 внесены количества необходимых для выполнения программного проекта разработчиков ПО, руководителей групп разработчиков и менеджеров программных проектов, а также их почасовые ставки. Количество руководителей групп вычисляется в ячейке В2 таким образом, чтобы на четырех разработчиков приходилось бы не менее одного руководителя (=ОКРВВЕРХ(К_разр/4;1)).
Функция ОКРВВЕРХ округляет число до ближайшего целого или до ближайшего кратного указанному значению.
Синтаксис: ОКРВВЕРХ (число; точность)
Число – округляемое значение.
Точность – кратное, до которого требуется округлить.
· В диапазоне A5:D16 приведен список этапов программного проекта, для каждого этапа определены количество часов (на одного исполнителя) и исполнители, необходимые для выполнения этого этапа, а также вычисляется стоимость каждого этапа.
Создайте сценарии самого худшего случая разработки ПО, когда разработчиков не хватает, у них высокая почасовая ставка, и вследствие этого сокращено время выполнения отдельных этапов проекта:
К_разр: 3
К_мен: 1
Ст_разр: 100
Ст_рук: 120
Ст_мен: 150
Разр_спец: 2
Утв_спец: 1
Код_первое: 30
Код_второе: 20
Аттестация_1: 1
Код_третье: 10
Отладка: 10
Аттестация_2: 1
Подг_версии: 2
Утв_версии: 1
В наилучшем случае разработки программного продукта участвует достаточное число специалистов, они имеют относительно низкие почасовые ставки, а на выполнение этапов проекта отводится достаточное количество времени:
К_разр: 15
К_мен: 2
Ст_разр: 75
Ст_рук: 90
Ст_мен: 110
Разр_спец: 8
Утв_спец: 2
Код_первое: 100
Код_второе: 80
Аттестация_1: 10
Код_третье: 60
Отладка: 50
Аттестация_2: 10
Подг_версии: 15
Утв_версии: 5
Постройте на основе сценариев итоговые отчеты.