3. Применение диспетчера сценариев для анализа данных.

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

З адание; на основе таблицы проанализировать влияние на величину прибыли следующих параметров: объем реализации за месяц, зарплата, аренда, реклама.

Для создания таблицы использовать лист Сценарий. В таблицу заносятся следующие формулы: в ячейку F3: =F1–F2: в ячейку F5: =F3*F4; в ячейку F13: =CУMM(F7:F12); в ячейку F15: =F5–F13.

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

F 4 – Объем , F7 – Зарплата, F9 – Аренда, F10 – Реклама.

Для присвоения имени ячейки необходимо:

1. Выбрать ячейку, которой нужно присвоить имя.

2. Щелкнуть мышкой в поле Имя, которое расположено слева в строке формул.

3. Ввести имя ячейки.

4. Нажать клавишу Enter

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

Анализ прибыли будем проводить при следующих сочетаниях изменения исходных параметров:

Изменяемые параметры 1-ый_вариант 2-ой вариант 3-ий вариант

объем реализации не меняется +10% – 10%

зарплата. 14 000 000 +10%

аренда, 15 000 000 +10%

реклама 10 000 000 +10%

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

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

Порядок действий при создании сценария с исходными значениями:

• сделать текущей ячейку Объем;

• выполнить команду Сценарии пункта меню Сервис, после чего появится диалоговое окно Диспетчер Сценариев;

• щелкнуть кнопку Добавить для создания нового сценария. Выводится окно диалога Добавление сценария;

• в поле Название сценария ввести имя сценария Исходный;

• в поле Изменяемые ячейки необходимо ввести абсолютные адреса ячеек с изменяемыми параметрами. При этом адрес первой изменяемой ячейки уже имеется за счет того, что она была установлена текущей. После адрес этой ячейки необходимо ввести ; (точка с запятой). Адреса остальных ячеек ($F$7;$F$9;$F$10) можно вводить либо вручную, либо щелкая мышкой по ячейкам при нажатой клавише Ctrl. В результате окно диалога Добавление сценария будет выглядеть следующим образом:

• в ноле Примечание ввести дату создания сценария и имя пользователя, создавшего сценарий;

• щелкнуть на клавише ОК.

Появится окно Значения ячеек сценария, в котором текущие значения изменяемых ячеек на рабочем листе предлагаются как варианты по умолчанию. Следует щелкнуть на клавише ОК или нажать клавишу Enter. После этого происходит возврат в окно Диспетчер сценариев, где в поле Сценарии присутствует имя Исходный, Окно Диспетчер сценариев не закрывается.

Порядок действий при создании сценария со значениями 1-го варианта:

• щелкнуть кнопку Добавить для создания нового сценария;

• в поле Имя Сценария ввести имя сценария Вариант 1;

• в поле Изменяемые ячейки сохраняются абсолютные адреса ячеек с изменяемыми параметрами.

• в поле Примечание ввести дату создания сценария и имя пользователя, создавшего сценарий.

• щелкнуть на клавише ОК .

• в появившемся окне Значения ячеек сценария, в полях Зарплата, Аренда. Реклама ввести новые числовые значения;

• щелкнуть на клавише ОК или нажать клавишу Enter.

Порядок действий при создании сценария со значениями 2-го варианта:

• щелкнуть кнопку Добавить для создания нового сценария;

• в поле Имя Сценария ввести имя сценария Вариант 2;

• в поле Изменяемые ячейки сохраняются абсолютные адреса ячеек с изменяемыми параметрами;

• в поле Комментарий ввести дату создания сценария и имя пользователя, создавши сценарий;

• щелкнуть на клавише ОК;

• в появившемся окне Значения ячеек сценария, в поле Объем следует ввести формулу, показывающую изменение объема реализации:

=1.1*12000 • щелкнуть на кнопке ОК или нажать клавишу Enter.;

Внимание: разделитель целой и дробной части числа может быть точка или запятая (в зависимости о настроек конкретной машины). Если после щелчка на кнопке ОК Excel выдает сообщение Ошибка в формуле

с ледует щелкнуть на кнопке ОК. Снова появится окне Значения ячеек сценария, в котором будет выделено число с дробной частью.

Это означает, что использован неправильный разделитель. Необходимо его заменить на другой (точку на запятую или наоборот).

• при сообщении Excel о преобразовании результата вычисления формулы в значение щелкнуть на клавише ОК или нажать клавишу Enter.

Создание сценария со значениями 3-го варианта производится аналогично выше рассмотренному, имя сценария для него - Вариант 3. Формулы для расчета результатов будут иметь следующий вид:

Объем =0.9*12000, Зарплата =1.1*12000000, Аренда =1.1*12000000, Реклама =1.1*8500000