Построение сценариев

Сценарии входят в состав набора команд, которые называются средствами анализа гипотетических вариантов. При использовании сценариев выполняется анализ гипотетических вариантов, который включает изменение значений ячеек для выяснения того, как эти изменения повлияют на результаты выполнения формул на листе.

7. По данным рабочего листа Лист2 постройте сценарии решения задачи расчета значения прибыли за продукцию путем изменения параметров «Цена» и «Проценты накладных расходов».

8. Для построения каждого сценария необходимо:

· на вкладке Данные в группе Работа с данными выбрать команду Анализ “что-если”, а затем выбрать в списке пункт Диспетчер сценариев;

· в диалоговом окне Диспетчер сценариев нажать кнопку Добавить;

· в окне Добавления сценария ввести в поле Название сценария имя (например, «Изменение цены 1»);

· в поле Изменяемые ячейки ввести абсолютную ссылку на ячейку, содержащую значение изменяемого параметра (например, цены);

· нажать OK;

· в окне Значения ячеек сценария ввести значение изменяемого параметра (например, для цены ввести 175);

· нажать OK.

9. Повторите указанные в пункте 8 действия для добавления в список сценариев еще трех сценариев расчета прибыли, изменяя параметры «Цена» (200) и «Проценты накладных расходов» (20% и 40%);

10. Для просмотра сценариев в окне Диспетчер сценариев поочередно выбирайте сценарии из списка и щелкайте по кнопке Вывести. Excel заменит содержимое ячеек листа значениями из сценария и отобразит результаты на листе.

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

12. В окне Отчет по сценарию выберите тип отчета Структура, установите абсолютную ссылку на ячейку со значением результата (Прибыль за продукцию) и нажмите ОК.

13. Перейдите на новый рабочий лист и введите таблицу с упрощенным бюджетом предприятия на 2015 год и выполните прогнозирование бюджета на 2016, 2017 и 2018 годы, манипулируя темпами роста различных показателей. Подготовьте 4 сценария с различными прогнозами роста и создайте итоговый сравнительный отчет.

Бюджет предприятия на 2015 г. приведен в таблице: чистая прибыль

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

 

Для реализации поставленной задачи выполните следующие действия:

· присвойте имена ячейкам В13-В17 в соответствии с названиями показателей в столбце А. Для этого последовательно устанавливайте курсор на каждую ячейку диапазона В13-В17, на вкладке Формулы в группе Определенные имена выбирайте команду Присвоить имя и в окне Создание имени нажимайте ОК.

· присвойте имена ячейкам результата С11, D11, E11 – «Прибыль_2016», «Прибыль_2017», «Прибыль_2018»;

· введите расчетные формулы для вычисления показателей в ячейках С2:Е11:

Показатели в столбцах C,D,E вычисляются по схеме:

Объем продаж 2016 г = Объем продаж 2015 г *(1+% роста объема продаж)

Размер прибыли 2016 г = Размер прибыли 2015 г *(1+% роста размера прибыли)

Общая прибыль= Объем продаж * Размер прибыли в %

Расход=Аренда + Услуги + Выплаты

Чистая прибыль=Общая прибыль-Расход

и т.д;

· определите первый сценарий «Прогноз роста 1», выполнив команду Данные/ Работа с данными/Анализ “что-если”/ Диспетчер сценариев и введя в поле Изменяемые ячейки абсолютную ссылку на ячейки B13:B17;

· измените в окне Значения ячеек сценария проценты роста показателей в ячейках B13:B17;

· аналогично создайте еще три сценария («Прогноз роста 2» и т. п.), щелкая в диалоговом окне Диспетчера сценариев кнопку Добавить и меняя непосредственно в окне Значения ячеек сценария проценты роста показателей в ячейках B13:B17;

· создайте отчет по сценарию, выбрав тип отчета – структура и введя в поле Ячейки результата ссылки на диапазон ячеек C11:E11, содержащие значения чистой прибыли;

· создайте отчет по сценарию, выбрав тип отчета – сводная таблица;

· проанализируйте полученные результаты решения задачи.