Применение сценариев

Начинается с команды Сервис/ Сценарии.

Чтобы создать новый сценарий, в диалоговом окне Диспетчер сценариев нажмите кнопку Добавить. Появится окно Добавление сценария.

Введите необходимое имя в поле Название сценария. Введите ссылки на ячейки, которые необходимо изменить, в поле Изменяемые ячейки. Установите необходимые флажки в наборе флажков Защита. Нажмите кнопку OK.

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

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

!!! Добавьте свой сценарий в набор сценариев Excel

Флажок запретить изменения позволяет установить защиту сценария от изменений. При этом необходимо также защитить лист командой Сервис/ Защита/ Защитить лист. Прежде чем вносить изменения в сценарий, необходимо снять флажок запретить изменения.

С помощью флажка скрыть можно скрыть сценарии. При этом необходимо также защитить лист командой Сервис/ Защита/ Защитить лист.

Чтобы изменить существующий сценарий, выберите команду Сервис/ Сценарии, установите курсор на нужный сценарий и нажмите кнопку Изменить. Повторите действия описанные выше. (Изменение аналогично добавлению, только при этом не будет создан новый сценарий, а будет изменен старый).

Чтобы удалить существующий сценарий, выполните команду Сервис/ Сценарии, установите курсор на нужный сценарий и нажмите кнопку Удалить.

Чтобы воспользоваться сценарием (чтобы в указанные ячейки были помещены определенные в сценарии значения), выполните команду Сервис/ Сценарии, установите курсор на нужный сценарий и нажмите кнопку Вывести.

Обратите внимание! При использовании команды Вывести Excel подставляет в ячейки на листе те значения, которые были занесены в сам сценарий. Если до этого в этих ячейках были значения из другого сценария, то старый сценарий НЕ удаляется, а просто заменяются значения ячеек на листе. В этом и состоит главное назначение сценариев

!!! Ознакомьтесь с работой команды Вывести.

Сценарии автоматически сохраняются при записи файла на диск.

Рассмотрим применение сценариев на конкретном примере. Предприятию необходимо составить финансовый план на год, но годовой доход точно не известен. Для дохода определяются различные значения, а затем для каждого сценария выполняется анализ «что-если». В «худшем» варианте сценария объем реализации составит 200, 205, 201, 210 ед. продукции для I, II, III и IV кварталов соответственно. В «лучшем» варианте сценария – 220, 235, 250, 270 ед. продукции для I, II, III и IV кварталов соответственно. Цена одной единицы прогнозируется в размере 10 000, 11 000, 12 100, 17 000 руб. для I, II, III и IV кварталов соответственно. Необходимо рассчитать объемы финансирования подразделений предприятия и прибыль. Издержки подразделяются на постоянные и переменные. Заводоуправление требует постоянных затрат в размере 600 тыс. руб. ежеквартально, цех №1 требует постоянных затрат в размере 100 тыс. руб. ежеквартально и переменных затрат 3000 руб. с каждой ед. продукции, цех №2 требует постоянных затрат в размере 350 тыс. руб. ежеквартально и переменных затрат 2000 руб. с каждой ед. продукции.

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

1. Создайте пессимистичный сценарий для четырех ячеек (B2-E2) (как создать сценарий показано выше). Присвойте ячейкам (B2-E2) значения 200, 205, 201, 210 соответственно.

2. Создайте оптимистичный сценарий для тех же ячеек (B2-E2). Присвойте ячейкам значения 220, 235, 250, 270 соответственно.

3. Внесите в ячейки (B3-E3) значения 10; 11; 12,1; 17 соответственно.

4. Внесите в ячейки (B5-E5) значения 600, 600, 600, 600 соответственно.

5. Внесите в ячейки формулы для расчета необходимых значений:

Формула для расчета	Ячейки	Формула
валового дохода	B4-E4	=(объем продаж)*(цена ед.)
Затрат на цех №1	B6-E6	=100+3*(объем продаж)
Затрат на цех №2	B7-E7	=350+2*(объем продаж)
итого издержек	B8-E8	=(затраты заводоуправления)+(затраты на цех №1)+(затраты на цех №2)
прибыли	B9-E9	=(валовой доход)-(итого издержек)
итого за год: объем продаж валовой доход затраты на заводоуправление затрат на цех №1 затрат на цех №2 итого издержек прибыли	F2 F4 F5 F6 F7 F8 F9	=СУММ(B2:E2) =СУММ(B4:E4) =СУММ(B5:E5) =СУММ(B6:E6) =СУММ(B7:E7) =СУММ(B8:E8) =СУММ(B9:E9)

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

При оптимистичном варианте окно будет выглядеть следующим образом.

При пессимистичном варианте окно будет выглядеть следующим образом.

!!! Решите задачу предложенным способом, закрепите навыки использования сценариев

Просмотрите оборудование для лабораторной работы и порядок ее выполнения перейдя по ссылке.

Вопросы для самоконтроля

1. Какие типы таблиц данных можно создать с помощью команды Данные/ Работа с данными/ Анализ «что если» /Таблица данных и в чем их отличие?

2. Предприятие оценивает возможность покупки станка. Имеются предложения по цене 200, 210, 220, 245 и 250 тыс. руб. Срок службы всех станков 10 лет, остаточная стоимость 20 тыс. руб. Определите суммы амортизационных отчислений для станков с различной стоимостью при использовании метода равномерного списания.

3. У вас есть возможность вложить 10 000 руб. или 20 000 руб. в банк на 5 лет. Каждый год вы планируете помещать в банк 1000 руб. Определите накопленную сумму в конце пятого года при ставках банковского процента 5, 10, 15, 20, 25, 30 и 35%, если выплаты в конце года не производятся.

4. Создайте таблицу данных, которая вычисляет ежегодные выплаты по ссуде под закладную в 100 000 руб. для процентной ставки 5, 10, 15, 20 и 25 процента при сроках 10,15, 20 и 25 лет.

5. Создайте пессимистический, оптимистический и средний вариант сценариев при следующих условиях. Предприятию необходимо составить финансовый план на год, но годовой доход точно не известен. В «худшем» варианте сценария цена одной единицы прогнозируется в размере 1 000, 1 050, 1 200, 1 300 руб. для I, II, III и IV кварталов соответственно. В «лучшем» варианте сценария – 1100, 1200, 1300, 1400 руб. для I, II, III и IV кварталов соответственно. Для среднего варианта сценария цена составит 1050, 1100, 1250 и 1350 руб. для I, II, III и IV кварталов соответственно. Объем реализации составит 1000, 1100, 1200, 1400 ед. продукции для I, II, III и IV кварталов соответственно. Необходимо рассчитать объемы финансирования подразделений предприятия и прибыль. Издержки подразделяются на постоянные и переменные. Заводоуправление требует постоянных затрат в размере 400 тыс. руб. ежеквартально, цех №1 требует постоянных затрат в размере 100 тыс. руб. ежеквартально и переменных затрат 25 руб. с каждой ед. продукции, цех №2 требует постоянных затрат в размере 200 тыс. руб. ежеквартально и переменных затрат 15 руб. с каждой ед. продукции. Ставка налога на прибыль составляет 35%.

Итоговая таблица должна выглядеть следующим образом: